1
00:00:00,900 --> 00:00:03,540
Hello and welcome back to another class, of course.

2
00:00:03,570 --> 00:00:08,030
But the complete introduction to VBA with Microsoft Excel.

3
00:00:08,670 --> 00:00:12,150
So in this class, what we are going to do is pretty simple.

4
00:00:12,160 --> 00:00:20,610
We are going to talk about the VBA code a little bit more in depth and you'll see it's not that complicated

5
00:00:20,610 --> 00:00:21,520
to understand.

6
00:00:21,750 --> 00:00:27,490
So in the past few classes, what we saw is the micro creation and we talked about the visual basic

7
00:00:27,510 --> 00:00:32,640
studio main page and what it looks like and basically how it works and all those things.

8
00:00:33,030 --> 00:00:38,910
So in this class, what we're going to do, we are going to look at the code behind the micro and we

9
00:00:38,910 --> 00:00:44,940
are even going to modify this code a little bit and play with it to be able to create our own macros.

10
00:00:45,210 --> 00:00:47,100
So you'll see it's going to be pretty cool.

11
00:00:47,910 --> 00:00:48,650
So let's start.

12
00:00:49,080 --> 00:00:49,420
All right.

13
00:00:49,740 --> 00:00:51,960
So first thing they'll ask you to do is pretty simple.

14
00:00:51,960 --> 00:00:55,230
Just write down, for example, somewhere.

15
00:00:56,130 --> 00:00:56,550
So.

16
00:00:58,070 --> 00:01:04,140
Here, let's say, I want to write down, for example, the next thing that I want to do is simply putting

17
00:01:04,160 --> 00:01:06,580
it into a lot of cases.

18
00:01:07,400 --> 00:01:07,960
So here we go.

19
00:01:07,970 --> 00:01:10,040
I have my example pretty much everywhere.

20
00:01:10,520 --> 00:01:12,800
And what I want to do right now is pretty simple.

21
00:01:12,800 --> 00:01:19,280
Let's say I want to take all those cases and reduce their sizes well, not reduce make it a little bit

22
00:01:19,280 --> 00:01:19,570
bigger.

23
00:01:19,580 --> 00:01:22,040
So from 14, I'll go to, let's say, 16.

24
00:01:23,040 --> 00:01:24,630
So how exactly do this?

25
00:01:25,040 --> 00:01:25,880
So pretty simple.

26
00:01:26,420 --> 00:01:29,070
First thing that I want to do is go into my developer type.

27
00:01:29,120 --> 00:01:35,920
Then I'll record my macro and let's call it text size.

28
00:01:36,980 --> 00:01:37,800
So here we go.

29
00:01:38,600 --> 00:01:40,030
So I have my Mac right there.

30
00:01:40,040 --> 00:01:47,840
And what I want to do is select my cells that I will use, then simply increase their size and I can

31
00:01:47,840 --> 00:01:51,440
even make it make a boom, for example.

32
00:01:51,890 --> 00:01:53,270
So make it good as well.

33
00:01:53,960 --> 00:01:54,860
And then that's it.

34
00:01:54,890 --> 00:01:56,920
So I stopped recording my microphone.

35
00:01:58,190 --> 00:02:03,480
So basically, as I explained, all this is converted by Excel into a code.

36
00:02:03,860 --> 00:02:09,230
So this is why it's pretty cool using macros, because all everything that you do is converted into

37
00:02:09,230 --> 00:02:10,850
a code and you will see what it looks like.

38
00:02:10,880 --> 00:02:15,720
So basically, first thing is to be able to have this, you need to click on Visual Baeza.

39
00:02:16,700 --> 00:02:17,600
So what happened?

40
00:02:17,600 --> 00:02:19,300
Is that what you will see in modules?

41
00:02:19,850 --> 00:02:24,810
So basically, if we go there inside of our modules, this code right here have been created and that

42
00:02:24,860 --> 00:02:26,660
basically this code is pretty simple.

43
00:02:26,660 --> 00:02:31,630
It explains what exactly happened and how exactly the macro will work.

44
00:02:31,970 --> 00:02:37,890
But the problem with this code is that there is a little bit too much things inside of it for nothing.

45
00:02:37,910 --> 00:02:40,260
So basically things that we don't necessarily need.

46
00:02:41,690 --> 00:02:42,440
So pretty simple.

47
00:02:43,020 --> 00:02:46,150
Let's talk about this code and try to understand it a little bit more.

48
00:02:46,430 --> 00:02:51,000
So if you look at your code, first thing that you can see is subtext.

49
00:02:51,240 --> 00:02:53,450
So here will be the name of your micro.

50
00:02:53,750 --> 00:02:58,460
So your code starts with sub and ends with and some pretty simple.

51
00:02:59,000 --> 00:03:01,790
And as I said, it's the name of the macro.

52
00:03:01,800 --> 00:03:02,770
So we have text size.

53
00:03:03,170 --> 00:03:07,190
So if you want to modify the name of the macro and modify it right there automatically.

54
00:03:08,530 --> 00:03:17,080
Then hear what we can find is comments for those of you who have a basic prior programming programming

55
00:03:17,170 --> 00:03:20,210
experience, you know, pretty much comments.

56
00:03:20,260 --> 00:03:26,620
Why are they things so common in programming, VBA, Python or any other programming language are there

57
00:03:26,620 --> 00:03:29,680
to give you a hint on why your code is there.

58
00:03:29,690 --> 00:03:35,470
So basically, in this case, we can say that that's Imakura that is used to, I don't know, to make

59
00:03:35,470 --> 00:03:36,780
the text a little bit bigger.

60
00:03:37,600 --> 00:03:42,570
And what's cool about comments is that they are not taking consideration when you guys are running your

61
00:03:42,790 --> 00:03:42,940
show.

62
00:03:42,940 --> 00:03:45,250
Basically, the comments are just there to describe your.

63
00:03:46,270 --> 00:03:47,620
So how to write down the comments?

64
00:03:47,630 --> 00:03:48,250
Pretty simple.

65
00:03:48,250 --> 00:03:51,220
You need to start with this little apostrophe right there.

66
00:03:51,610 --> 00:03:55,870
And what you can do is simply write a description about what we just did.

67
00:03:55,880 --> 00:03:58,930
So basically we can say it's

68
00:04:01,930 --> 00:04:06,100
well, let's make it simple, simpler, so.

69
00:04:07,520 --> 00:04:11,330
Text production, that's it.

70
00:04:12,110 --> 00:04:14,810
So here we go, we have our comment that have been created.

71
00:04:14,820 --> 00:04:16,910
So basically it's just that text reduction.

72
00:04:18,410 --> 00:04:22,300
My crew for my crew, for tax reduction.

73
00:04:22,760 --> 00:04:26,090
All right, so here we go, we have our crew for tax reduction right there.

74
00:04:26,630 --> 00:04:29,600
And basically this is what it does.

75
00:04:29,610 --> 00:04:31,060
Basically, this is what does this matter?

76
00:04:31,580 --> 00:04:35,000
Next year we have our basically macro.

77
00:04:35,420 --> 00:04:37,550
So you can see first thing that you guys can see is the range.

78
00:04:37,560 --> 00:04:44,600
So the range in this case, what it is, is what are the cells that will be affected by the macro.

79
00:04:44,600 --> 00:04:51,920
So basically it starts from the cell G five, which is there, and finishes at the cell 18, which is

80
00:04:51,930 --> 00:04:52,730
right here.

81
00:04:52,760 --> 00:04:56,720
So if we look at it G five to 18.

82
00:04:57,080 --> 00:04:59,810
So basically this is the range of our macro.

83
00:05:00,920 --> 00:05:01,400
All right.

84
00:05:02,330 --> 00:05:04,820
Next thing will be this part of the code.

85
00:05:04,820 --> 00:05:06,590
So basically, this is our micro.

86
00:05:06,830 --> 00:05:10,230
And what you can see, first of all, will be the policy.

87
00:05:10,260 --> 00:05:13,290
So in this case, the palace name is Caliburn.

88
00:05:13,670 --> 00:05:14,590
So we have it right there.

89
00:05:14,600 --> 00:05:19,980
So if you want to change something in your macro and change it automatically here and then the tax size

90
00:05:20,000 --> 00:05:21,830
will be sixteen in this case.

91
00:05:22,310 --> 00:05:24,590
So how exactly we can write down this goal.

92
00:05:24,590 --> 00:05:28,090
You can write down what you can delete the with and with it.

93
00:05:28,130 --> 00:05:34,700
You can write down a selection that fund that, for example, name and change decollete or dot size

94
00:05:35,030 --> 00:05:37,190
and change the size of the size of your tax.

95
00:05:37,190 --> 00:05:42,890
But instead of in this case, writing down each time selection, that font and the thing that you want

96
00:05:42,890 --> 00:05:48,620
to modify, you can write it with a with a with selection that font and write down all the attributes

97
00:05:48,620 --> 00:05:53,350
that you want to work with and in this case, add them or delete them.

98
00:05:54,480 --> 00:06:01,950
So in the case of our example, we only need Calibra Insights and all the rest have been has been generated

99
00:06:02,550 --> 00:06:06,320
by Excel in the case that we are going to use it.

100
00:06:06,330 --> 00:06:12,600
So, for example, strike through superscripts, subscript, outline, phones, shadow and all the other

101
00:06:12,600 --> 00:06:12,900
things.

102
00:06:12,900 --> 00:06:16,510
We are not necessarily using them, so we don't need them.

103
00:06:16,530 --> 00:06:17,820
So what we can do is pretty simple.

104
00:06:17,820 --> 00:06:23,570
We can delete all those right now and we will have and with.

105
00:06:23,610 --> 00:06:29,670
So basically our code would work properly even if we only have this part of the code so we can try to

106
00:06:29,670 --> 00:06:30,750
run it once again.

107
00:06:31,680 --> 00:06:33,870
So what we'll do exactly is pretty simple.

108
00:06:33,870 --> 00:06:40,350
We're just going to go back to delete the bold and the make make the text at 14.

109
00:06:41,160 --> 00:06:43,720
So what we'll do after that, we're going to run our macros.

110
00:06:43,720 --> 00:06:47,120
So how exactly we run our macros simply by clicking right there on the run.

111
00:06:47,550 --> 00:06:50,160
So you can see everything has been changed.

112
00:06:51,360 --> 00:06:56,610
All right, let's part of the code selection that fundable, but it's pretty much the same thing as

113
00:06:56,610 --> 00:06:57,090
right there.

114
00:06:57,310 --> 00:07:00,550
It's just written well in a complete sentence.

115
00:07:00,550 --> 00:07:02,650
So the same way that here.

116
00:07:03,810 --> 00:07:08,280
So if I, for example, write down falls right there, so false.

117
00:07:08,310 --> 00:07:12,270
What's going to happen is when I run my micro.

118
00:07:14,030 --> 00:07:21,970
We go just run it there, as you can see, the both part have has been deleted so you can modify my

119
00:07:21,970 --> 00:07:24,260
macro as I want right here.

120
00:07:24,640 --> 00:07:24,960
All right.

121
00:07:24,970 --> 00:07:28,450
So now that you understand the basics, let's create a really basic module.

122
00:07:28,600 --> 00:07:29,290
What module?

123
00:07:29,320 --> 00:07:32,850
Really basic macro based on this part of the code.

124
00:07:33,160 --> 00:07:34,520
So something that is pretty simple.

125
00:07:34,540 --> 00:07:39,500
So first thing that I'll ask you guys to do will be insert and insert eight module.

126
00:07:39,670 --> 00:07:45,320
So then what we'll do, we are going to create a macro that is based on this one right here.

127
00:07:45,340 --> 00:07:49,770
So we'll just copy all the code that we have here and we are going to modify it.

128
00:07:50,740 --> 00:07:54,620
So then we are going to come back in our module number two in there.

129
00:07:55,030 --> 00:07:59,400
So first thing that we want to change will be the title.

130
00:07:59,410 --> 00:08:03,970
So we'll call it text size part to.

131
00:08:05,970 --> 00:08:08,670
Let's just call it Texas to it's going to be simpler.

132
00:08:10,770 --> 00:08:14,290
So here we are coming to exercise, too.

133
00:08:14,910 --> 00:08:15,300
All right.

134
00:08:15,300 --> 00:08:19,400
So first thing we want to do is change the range.

135
00:08:19,430 --> 00:08:24,840
So in this case, what I want to do with this macro is instead of having this range, I want to have

136
00:08:24,840 --> 00:08:25,960
another range.

137
00:08:25,980 --> 00:08:30,320
So in this case, what range I want to use is pretty it's pretty simple.

138
00:08:30,330 --> 00:08:32,250
I want to use this range right there.

139
00:08:32,580 --> 00:08:33,720
But the problem is.

140
00:08:34,440 --> 00:08:41,020
Well, it's all well, it's right now it works on all this range right here.

141
00:08:41,550 --> 00:08:43,520
So if I run it, as you can see, works on Auldridge.

142
00:08:44,010 --> 00:08:48,510
So first thing that I want to do is simply modify the range to what I want as a range.

143
00:08:48,510 --> 00:08:54,160
I want only this, so I want it from age six to G 17.

144
00:08:54,180 --> 00:08:55,580
So this is exactly what I will do.

145
00:08:55,620 --> 00:09:00,600
I will change the range to age six to G

146
00:09:03,210 --> 00:09:03,900
17.

147
00:09:04,950 --> 00:09:05,310
All right.

148
00:09:05,320 --> 00:09:06,850
So we have everything that we need.

149
00:09:06,870 --> 00:09:13,260
So we have a range that is really so next thing that we want to change will be the type of poly.

150
00:09:13,300 --> 00:09:15,830
So in this case, we want we don't want to have calibrator.

151
00:09:16,320 --> 00:09:18,120
We want to have Ahran.

152
00:09:18,120 --> 00:09:23,490
Let's say we want to have aerial so you can choose any policy that you guys want.

153
00:09:23,490 --> 00:09:28,500
Some will say Irial instead of Calibra, we were going to write down Aryal.

154
00:09:29,860 --> 00:09:33,730
And for the size instead of 16, we want to have 14.

155
00:09:34,630 --> 00:09:36,720
Well, no, let's say 20, 20, 30.

156
00:09:37,510 --> 00:09:41,080
And finally here, so for the selection board, we want to have it in bold.

157
00:09:41,080 --> 00:09:41,840
So we'll write down.

158
00:09:41,940 --> 00:09:42,310
True.

159
00:09:42,880 --> 00:09:49,640
But not only want to have the bold part, we also want to have the italic part right there.

160
00:09:49,660 --> 00:09:52,590
So what we'll do is simply write down pretty much the same thing.

161
00:09:53,020 --> 00:09:59,280
So selection that font and what we want to add will be italic.

162
00:09:59,440 --> 00:10:04,910
So in this case, it telic we want Urtext to be in italic as well.

163
00:10:04,930 --> 00:10:09,130
So we have and there we want it to be true.

164
00:10:10,780 --> 00:10:12,670
All right, so we have everything that we need.

165
00:10:13,420 --> 00:10:18,970
Next thing is pretty simple, it's just testing out our map so we can run it and we can see everything

166
00:10:18,970 --> 00:10:19,410
works fine.

167
00:10:19,420 --> 00:10:27,440
So basically, our Maku is what we have are my crew right there that is only for this part and from

168
00:10:27,460 --> 00:10:29,650
our other my crew that is here.

169
00:10:30,630 --> 00:10:37,560
It's going to be applied on all the text right there, which is pretty cool, and if we want to get

170
00:10:37,560 --> 00:10:40,680
rid of the italic part, we can add.

171
00:10:41,160 --> 00:10:43,470
Well, we can go our model number two.

172
00:10:44,890 --> 00:10:53,040
Take our selection, that font, that italic, and instead of having it as true, we will write it down

173
00:10:53,050 --> 00:10:53,690
false here.

174
00:10:54,070 --> 00:10:56,930
So what it will do, it will also delete the italic part.

175
00:10:56,950 --> 00:11:00,240
So if we run it, as you can see, it's not italic anymore.

176
00:11:00,250 --> 00:11:02,020
And if we are module number two.

177
00:11:03,690 --> 00:11:09,540
Well, as you can see, it works perfectly fine, and right now what we have just did, we have created

178
00:11:09,540 --> 00:11:13,050
a macro that is based on our past macro that is right there.

179
00:11:13,080 --> 00:11:14,420
So pretty simple right now.

180
00:11:14,430 --> 00:11:19,140
Well, let's let's recapitulate everything to be sure that you guys understand how it works.

181
00:11:20,130 --> 00:11:23,390
So first part of our code, pretty simple.

182
00:11:24,210 --> 00:11:25,890
You need to have your name.

183
00:11:25,890 --> 00:11:27,570
So you start your code with writing down.

184
00:11:27,580 --> 00:11:34,950
So then the name of your macros in this in this case, it's text size and that's it.

185
00:11:35,200 --> 00:11:36,510
That's the name of your macro.

186
00:11:36,600 --> 00:11:40,140
Then here you have comments, but those comments are not mandatory.

187
00:11:40,150 --> 00:11:41,510
You can delete them if you want.

188
00:11:41,520 --> 00:11:44,060
We can add them whenever you want in the text.

189
00:11:44,070 --> 00:11:46,010
Well, in your code, it doesn't matter.

190
00:11:46,650 --> 00:11:52,600
Then next thing, the range of where exactly your macros will what your macro will be effective.

191
00:11:52,620 --> 00:11:55,890
So in this case, it's effective from five to 18.

192
00:11:56,490 --> 00:11:58,800
And then you write down your macros once again.

193
00:11:58,800 --> 00:12:01,320
This is just for our basic example.

194
00:12:02,250 --> 00:12:04,820
So here I'm writing down my macros for selection, that font.

195
00:12:05,430 --> 00:12:07,600
I want to modify the type of policy.

196
00:12:07,600 --> 00:12:08,970
So in this case, it's Dutney.

197
00:12:09,030 --> 00:12:11,010
I want to modify the size of my text.

198
00:12:11,010 --> 00:12:12,240
So it's going to be that size.

199
00:12:12,750 --> 00:12:15,430
I want to change it to be bold as well.

200
00:12:15,450 --> 00:12:18,480
So in this case, I want it to be bold.

201
00:12:18,490 --> 00:12:21,290
So here I have I should write down the two.

202
00:12:21,330 --> 00:12:25,050
If I want it to be more and italic, I should write down to write this.

203
00:12:25,100 --> 00:12:26,610
So basically, that's our first model.

204
00:12:26,610 --> 00:12:30,950
So it's for text size, also less thing.

205
00:12:30,970 --> 00:12:35,970
So basically, as I explained at the beginning, the way it's going to be written.

206
00:12:36,000 --> 00:12:41,810
So here I have with selection that font and instead of writing down selection that from that name.

207
00:12:41,820 --> 00:12:46,920
So the same way that it's written right there, I just write down a selection, that font here I can

208
00:12:46,920 --> 00:12:52,890
write down that name, that size and all the other attributes that I want to give to my macro instead

209
00:12:52,890 --> 00:12:56,140
of writing down selection that font each and every time.

210
00:12:57,090 --> 00:12:58,650
So it's a little bit more simpler.

211
00:12:58,860 --> 00:13:01,500
And this is why we're using that with right here.

212
00:13:01,500 --> 00:13:03,010
So we have with and with.

213
00:13:03,720 --> 00:13:07,080
So I hope you guys right now understand a little bit more.

214
00:13:07,080 --> 00:13:10,610
So you can see it's not that complicated and it's pretty simple to understand.

215
00:13:10,620 --> 00:13:12,660
But once again, that's just the basics.

216
00:13:12,690 --> 00:13:18,990
So right now you can practice by yourself, by creating little small world, by by creating small macros

217
00:13:19,740 --> 00:13:22,230
that do some basic stuff like this one.

218
00:13:22,860 --> 00:13:26,400
So that's a first class goes into our next class.