1
00:00:00,410 --> 00:00:06,150
Guys, and welcome back to the class of our course about the complete introduction to Microsoft Excel

2
00:00:07,060 --> 00:00:07,550
VBA.

3
00:00:08,340 --> 00:00:13,440
So basically in the past few classes, we talked about plenty of things and we talked about how to program

4
00:00:13,440 --> 00:00:20,790
and VBA, so how to write down in macro how to work with Imakura and basically, well, all the things

5
00:00:20,790 --> 00:00:22,020
that are around in micro.

6
00:00:22,420 --> 00:00:23,010
Absolutely.

7
00:00:23,040 --> 00:00:28,340
What we are going to talk about will be also inside of the code, basically in the programming part.

8
00:00:28,710 --> 00:00:31,710
And what I want to teach you is really a little bit more advanced.

9
00:00:31,710 --> 00:00:36,300
So it's how to correct the record in case that you guys made a mistake somewhere.

10
00:00:37,230 --> 00:00:43,020
But you are not sure where exactly is the mistake or you simply want to change something inside of your

11
00:00:43,020 --> 00:00:43,310
code.

12
00:00:43,980 --> 00:00:49,380
So instead of just playing around and trying to find it, you can correct it directly.

13
00:00:49,590 --> 00:00:52,290
So basically, in my opinion, it's pretty simple.

14
00:00:53,220 --> 00:00:54,750
So how exactly will do this?

15
00:00:54,770 --> 00:00:55,680
So it's pretty simple.

16
00:00:55,710 --> 00:01:02,940
So for the purpose of this course, what we'll do, we are going to create a small, basic micro and

17
00:01:02,940 --> 00:01:04,950
we are going to learn how to use it.

18
00:01:05,640 --> 00:01:09,480
So pretty simple will start by simply going somewhere right there.

19
00:01:09,780 --> 00:01:14,090
So open up your Excel spreadsheet and then we are going to record.

20
00:01:14,460 --> 00:01:19,930
So basically inside of a macro, we are going to have the letter.

21
00:01:20,160 --> 00:01:24,770
So basically it's going to be ABCDE or just a doesn't really matter.

22
00:01:25,650 --> 00:01:29,790
Then after that we are going to have two numbers and we are going to make an addition of those numbers,

23
00:01:29,790 --> 00:01:32,160
any subtraction of those numbers.

24
00:01:32,190 --> 00:01:34,000
So you can see it's pretty simple.

25
00:01:34,770 --> 00:01:35,820
So let's start.

26
00:01:36,360 --> 00:01:40,710
So as I said, first thing that we want to do is writing down our letter.

27
00:01:44,190 --> 00:01:48,210
Just don't forget to record the Microsoft usability reference and record the macro.

28
00:01:49,200 --> 00:01:51,000
So call it a test.

29
00:01:52,960 --> 00:01:59,710
All right, so step one will be writing down the letter then the step two is pretty simple.

30
00:01:59,710 --> 00:02:08,750
As I said, it's going to be writing down number one, then writing down number two, then writing down

31
00:02:08,800 --> 00:02:16,060
addition, then writing down substraction because we're going to perform an addition and subtraction.

32
00:02:17,290 --> 00:02:20,320
Then next step is pretty simple.

33
00:02:20,320 --> 00:02:21,850
We are coming back right there.

34
00:02:22,030 --> 00:02:24,040
Or letter in this case will be a.

35
00:02:25,220 --> 00:02:29,990
Then for the first number, we are going to use a function, so we are going to generate a random number

36
00:02:30,720 --> 00:02:33,590
that will be between one and 10 in this case.

37
00:02:34,880 --> 00:02:35,840
So pretty simple.

38
00:02:36,650 --> 00:02:39,750
Second number, pretty much the same thing.

39
00:02:39,770 --> 00:02:45,740
So we are going to generate a random number that will be between one and 10.

40
00:02:47,810 --> 00:02:53,120
And finally, for the addition, what exactly we are going to do, we are going to make an addition

41
00:02:53,120 --> 00:02:56,690
of those two numbers to basically seven plus nine in this case.

42
00:02:57,770 --> 00:03:00,650
And for the substraction, pretty much the same thing.

43
00:03:00,650 --> 00:03:04,460
We are going to subtract six will treat from six.

44
00:03:07,110 --> 00:03:08,610
So here we go.

45
00:03:09,090 --> 00:03:13,820
All right, so right now we have our macro that works perfectly so we can make it a little bit bigger.

46
00:03:13,830 --> 00:03:18,300
So take everything that we have right there and just bring it down there.

47
00:03:19,340 --> 00:03:21,750
And we can even change the letters if we want to.

48
00:03:21,770 --> 00:03:28,880
So basically we can write down there, see there the e f g h.

49
00:03:30,820 --> 00:03:40,330
I got kids there, so we have everything ready and set up, so right now, if we try to run our Macroom,

50
00:03:41,650 --> 00:03:43,770
so we have our test artist when we run it.

51
00:03:44,290 --> 00:03:47,350
So it works perfectly for a test that we can simply try it out.

52
00:03:47,500 --> 00:03:48,450
So everything works fine.

53
00:03:48,460 --> 00:03:49,180
Everything is perfect.

54
00:03:50,110 --> 00:03:50,500
All right.

55
00:03:51,220 --> 00:03:53,080
So what exactly is the next step?

56
00:03:53,080 --> 00:03:56,920
The next step will be checking out the code of what we have just created.

57
00:03:57,490 --> 00:04:04,780
So if we go right there, pretty simple right here, we have all the code that we well, we have all

58
00:04:04,780 --> 00:04:08,340
the code that we have created and what exactly we can do right now.

59
00:04:08,350 --> 00:04:09,220
Well, it's pretty simple.

60
00:04:09,220 --> 00:04:17,470
We can check the code well as it is right here and basically checking if there are some things that

61
00:04:17,470 --> 00:04:21,310
we need, some other things that we don't need necessarily maybe deleting them.

62
00:04:21,670 --> 00:04:27,790
But in my opinion, I'm looking at the code right now, and I'm pretty sure that we need absolutely

63
00:04:27,790 --> 00:04:28,120
everything.

64
00:04:28,120 --> 00:04:32,680
Well, the majority or the majority of those, we need them because it's a text.

65
00:04:32,680 --> 00:04:32,930
Ready.

66
00:04:34,030 --> 00:04:34,380
All right.

67
00:04:35,200 --> 00:04:37,720
So this would be the first step.

68
00:04:37,990 --> 00:04:45,070
So what we'll do right now to understand a little bit more the code, we are going to use a very simple

69
00:04:45,070 --> 00:04:49,240
tool and that will allow us to check line by line what's happening.

70
00:04:49,240 --> 00:04:53,230
So instead of running the whole macro, we are going to run our text.

71
00:04:53,230 --> 00:04:56,950
Well, not our ethics, but our macro line by line.

72
00:04:58,000 --> 00:05:02,980
Basically, if we want to bring some modifications, we are going to be able to bring those modifications.

73
00:05:02,980 --> 00:05:08,350
And at the same time, if someone committed a mistake somewhere, you guys will be able to find your

74
00:05:08,350 --> 00:05:09,940
mistake pretty simply.

75
00:05:10,570 --> 00:05:11,710
So let's start.

76
00:05:12,910 --> 00:05:17,850
So first of all, we'll just delete one of those things so we don't necessarily need it.

77
00:05:19,090 --> 00:05:23,450
And right now what's going to happen is that we are going to generate our Maku right there.

78
00:05:23,860 --> 00:05:27,370
So how exactly we're going to generate it, I'm pretty simple.

79
00:05:27,940 --> 00:05:29,770
We are going to run it line by line.

80
00:05:30,220 --> 00:05:33,850
So for the purpose of this course, we are going to need two more buttons.

81
00:05:34,150 --> 00:05:36,560
And basically those buttons are pretty useful.

82
00:05:36,580 --> 00:05:40,720
So the first one will be the step into and the breaking point.

83
00:05:41,780 --> 00:05:44,140
Those are two really useful buttons.

84
00:05:44,870 --> 00:05:51,590
Basically, you can find them in the debugging part right there, but you can also add them this way.

85
00:05:51,610 --> 00:05:54,970
So basically, it's the two buttons that the two buttons that you can see right there.

86
00:05:55,750 --> 00:06:00,850
So you can just customize your ribbon if you want to make them appear right here and you simply go here

87
00:06:00,850 --> 00:06:05,440
into the bag, then you simply take the buttons that you need.

88
00:06:05,470 --> 00:06:09,620
So in this case, we have my button right there and my button right there.

89
00:06:10,090 --> 00:06:10,900
So pretty simple.

90
00:06:10,900 --> 00:06:14,470
It's going to be step into so we can take it and bring it right there.

91
00:06:14,900 --> 00:06:18,570
So simply by taking it and adding it to the ribbon.

92
00:06:18,850 --> 00:06:22,960
And you can do pretty much the same thing with the tool breakpoint.

93
00:06:22,990 --> 00:06:25,730
So simply taking it and bring it right there.

94
00:06:27,220 --> 00:06:29,370
So basically, let me explain those two buttons.

95
00:06:29,410 --> 00:06:33,500
So first of all, the step into button is pretty simple.

96
00:06:33,520 --> 00:06:40,130
What it does, it will allow you to jump and check out each and every line of your code.

97
00:06:40,150 --> 00:06:44,650
So basically, line by line, you'll be able to check out each and every line of your code.

98
00:06:44,680 --> 00:06:46,930
There is another button that is step out.

99
00:06:46,930 --> 00:06:50,730
So basically it will allow you to just step out if you don't need to check it anymore.

100
00:06:51,460 --> 00:06:55,810
But basically, this button, my opinion is really important because once again, you'll be able to

101
00:06:55,810 --> 00:06:56,920
check your code line by line.

102
00:06:57,190 --> 00:07:03,160
And if there is a mistake somewhere, you will be able to mark it out with this button right there,

103
00:07:03,160 --> 00:07:04,880
which is the toggle breakpoint.

104
00:07:05,830 --> 00:07:06,960
So basically, let's start.

105
00:07:07,000 --> 00:07:09,030
So right now, you guys should have your buttons right there.

106
00:07:09,370 --> 00:07:14,140
You should have your macro created and now it's time to generate or I couldn't see what it looks like.

107
00:07:14,830 --> 00:07:18,930
So first of all, we'll go right there and we'll start with our step into.

108
00:07:19,690 --> 00:07:22,510
So right here we have nothing that important.

109
00:07:22,540 --> 00:07:25,530
So this is the name of our macro, which is test.

110
00:07:25,550 --> 00:07:28,090
So if you want to change the name of our macro, we can do it.

111
00:07:28,510 --> 00:07:34,060
So you can see it's not going to check out well, it's not going to check out the comments.

112
00:07:35,900 --> 00:07:41,060
So first thing that happened, it generated letters, so basically this is the first line that have

113
00:07:41,060 --> 00:07:45,130
been that have been what basically that have been created.

114
00:07:45,530 --> 00:07:48,680
So we have letters that have been generated right here.

115
00:07:52,570 --> 00:07:53,050
Right.

116
00:07:54,420 --> 00:08:00,510
So if we continue, next thing that will be generated will be, well, this thing right there, so it's

117
00:08:00,510 --> 00:08:01,920
going to jump one cell.

118
00:08:03,450 --> 00:08:10,530
And then no one will be treated, so in this case, we can write it down so we can see no one have been

119
00:08:10,530 --> 00:08:10,880
created.

120
00:08:11,340 --> 00:08:12,810
So next step, pretty simple.

121
00:08:12,810 --> 00:08:17,150
We are jumping one one step again.

122
00:08:17,160 --> 00:08:18,620
So basically one cell.

123
00:08:18,630 --> 00:08:22,200
We're going one cell and another number will be created.

124
00:08:22,210 --> 00:08:26,180
So we have jumped one cell and now number two will be created.

125
00:08:27,300 --> 00:08:31,920
So zero one means from well, it moved from zero.

126
00:08:32,130 --> 00:08:32,790
And that one.

127
00:08:34,610 --> 00:08:42,890
All right, so next thing we are going to write down, Ed. So basically you can move it right here and

128
00:08:43,010 --> 00:08:47,870
what's happening in addition, right there, we can change, for example, instead of writing down Ed.

129
00:08:47,870 --> 00:08:49,180
We can write down something else.

130
00:08:49,190 --> 00:08:51,300
So we going to mark this place right here.

131
00:08:51,530 --> 00:08:55,590
So if we want to change it in the future, we can market and change it in the future.

132
00:08:56,630 --> 00:08:59,580
So we have additions that have been written down right here.

133
00:09:02,630 --> 00:09:07,750
Then once again, jumping one cell and we are going to write down substraction.

134
00:09:07,760 --> 00:09:09,630
So as you can see right here, we have substraction.

135
00:09:10,550 --> 00:09:12,540
So substraction have been written down.

136
00:09:13,610 --> 00:09:17,900
So once again, if we don't want to change additions anymore, we can come back here and deliver the

137
00:09:17,990 --> 00:09:19,250
same thing for substraction.

138
00:09:19,260 --> 00:09:21,710
Let's say you want to change substraction from forceable.

139
00:09:22,670 --> 00:09:24,390
I can come back here and delete it.

140
00:09:25,820 --> 00:09:26,290
All right.

141
00:09:26,300 --> 00:09:33,430
So next thing that happens, we are going to jump four cells in the back and the one cell will don't.

142
00:09:35,230 --> 00:09:40,060
So if we run the code, as you can see, this is what happens before the letter happening, the first

143
00:09:40,060 --> 00:09:41,350
letter is the letter A..

144
00:09:41,920 --> 00:09:45,970
So if, for example, they want to change letter H, I'm able to do it so I can, for example, change

145
00:09:45,970 --> 00:09:47,460
a letter A for the letter.

146
00:09:47,470 --> 00:09:48,540
I don't know the letter.

147
00:09:49,900 --> 00:09:51,520
A capitally.

148
00:09:52,540 --> 00:09:53,560
And we can continue.

149
00:09:54,010 --> 00:09:58,060
So here we can see the capital letter that have been generated.

150
00:10:00,250 --> 00:10:02,980
So right now, next thing, once again, we jump one cell.

151
00:10:04,080 --> 00:10:04,920
And we continue.

152
00:10:07,660 --> 00:10:08,730
Then what's happening?

153
00:10:08,770 --> 00:10:10,110
We are going to generate.

154
00:10:10,270 --> 00:10:13,700
We are going to write down this function to between one and 10.

155
00:10:14,440 --> 00:10:19,390
So basically, if, for example, instead of writing down the function random between one and 10, we

156
00:10:19,390 --> 00:10:21,500
got, we want to write down another function.

157
00:10:22,390 --> 00:10:23,090
We can change it.

158
00:10:23,110 --> 00:10:27,550
So basically right here, as you can see, selection, that formula formula have been created.

159
00:10:27,580 --> 00:10:35,740
So instead, let's say we want to generate a number that is between, I don't know, one and five instead

160
00:10:35,740 --> 00:10:36,570
of one in ten.

161
00:10:36,940 --> 00:10:38,200
We can do it right there.

162
00:10:38,260 --> 00:10:38,920
So here we go.

163
00:10:38,920 --> 00:10:40,580
We have changed our code once again.

164
00:10:41,350 --> 00:10:43,360
So here we'll have a number that will be generated.

165
00:10:43,360 --> 00:10:45,040
That is between one and five.

166
00:10:46,470 --> 00:10:50,910
So we can continue here pretty much the same thing, let's say we want to generate the number between

167
00:10:50,910 --> 00:10:51,840
one and seven.

168
00:10:53,620 --> 00:10:59,350
Great, so we can continue once again, once again, we're jumping one sale, so this is the code that

169
00:10:59,350 --> 00:11:04,550
is saying to us that we have jumped one cell and we can continue right now.

170
00:11:04,750 --> 00:11:08,050
So what's happening right now is that we are going to make an addition.

171
00:11:12,210 --> 00:11:18,600
So this is the addition that we are making, so from this cell right here, we are going back to cells.

172
00:11:18,810 --> 00:11:22,380
So this cell right there, plus this cell right there.

173
00:11:22,390 --> 00:11:28,980
So basically from the first cell, we are going back to cells in the back then, plus one cell in the

174
00:11:28,980 --> 00:11:29,200
back.

175
00:11:29,220 --> 00:11:30,980
So this cell plus this cell.

176
00:11:30,990 --> 00:11:32,220
So pretty simple to understand.

177
00:11:32,940 --> 00:11:38,250
So if, for example, we want to make an addition of, I don't know those numbers right there.

178
00:11:38,260 --> 00:11:43,800
So those two numbers, it's possible, but we will have to jump really, really far in the back.

179
00:11:43,810 --> 00:11:50,450
So we'll need to jump one, two, three, four, five, six, seven, eight, nine cells.

180
00:11:50,460 --> 00:11:55,830
So we are going to write down minus nine here in minus eight there to be able to make an addition of

181
00:11:55,830 --> 00:11:56,430
those two numbers.

182
00:11:56,430 --> 00:11:59,610
But it's possible this is how basically Excel works.

183
00:12:01,030 --> 00:12:03,610
All right, so when it's done, we can continue.

184
00:12:03,640 --> 00:12:06,130
So basically here, everything works perfectly fine.

185
00:12:07,120 --> 00:12:09,730
We'll do the same thing for the substraction.

186
00:12:10,570 --> 00:12:13,300
So once again, for the substraction, what happens here?

187
00:12:13,300 --> 00:12:14,850
We can see minus three, minus two.

188
00:12:15,610 --> 00:12:17,290
So what's happening is pretty simple.

189
00:12:17,710 --> 00:12:23,320
We are jumping once we're jumping three cells in the bag, then two cells in the back.

190
00:12:23,680 --> 00:12:25,630
And that basically this is what happens.

191
00:12:25,990 --> 00:12:27,250
So let's say we want to change it.

192
00:12:27,250 --> 00:12:28,540
We are able to change it.

193
00:12:28,540 --> 00:12:30,730
We can work it and change it a little bit later.

194
00:12:31,960 --> 00:12:32,850
And will continue.

195
00:12:33,760 --> 00:12:38,790
So then we are going to hear, as you can see, this is the formula for Autofill.

196
00:12:39,070 --> 00:12:46,060
So basically we are going to autofill our well, all this other thing that we have created and we're

197
00:12:46,060 --> 00:12:48,400
going to autofill it for all this part right there.

198
00:12:48,400 --> 00:12:50,920
And then we are going to change our letters.

199
00:12:53,410 --> 00:12:54,360
So let's continue.

200
00:12:57,060 --> 00:12:59,850
So pretty simple, as you can see, it has been filled.

201
00:13:01,050 --> 00:13:02,250
Then next thing.

202
00:13:08,530 --> 00:13:13,060
Next thing that's happening, we are changing out our letters, so basically we're going down AF1 each

203
00:13:13,060 --> 00:13:15,670
time and we're changing out our letters.

204
00:13:16,560 --> 00:13:22,490
So we can check it out, so here, for example, we have a small bit, we can make it a capital B. So

205
00:13:22,500 --> 00:13:28,410
once again right now, we're going to have a small bit, but we can make it a capital B instead.

206
00:13:28,420 --> 00:13:29,310
So we are deciding.

207
00:13:29,310 --> 00:13:34,690
So each time that we are moving in the coop, we can change some parts of the code.

208
00:13:36,000 --> 00:13:37,650
So pretty simple.

209
00:13:38,590 --> 00:13:44,080
So same thing right here for for the for e OK, let's say if I want to change it, we're going to market

210
00:13:44,080 --> 00:13:45,090
and change it at the end.

211
00:13:46,710 --> 00:13:51,780
Great h i g and let's say we want to do the same thing for Kate.

212
00:13:53,150 --> 00:13:56,470
She would go want to change our kitty, not this one.

213
00:13:57,710 --> 00:13:58,970
This one right here.

214
00:14:00,770 --> 00:14:01,730
So if we continue.

215
00:14:04,190 --> 00:14:08,550
Right, and this is for our well, basically this is for our family.

216
00:14:08,990 --> 00:14:13,490
So, as I said, we wanted to change some parts of the culture, so basically wanted to change this

217
00:14:13,610 --> 00:14:14,570
part right here.

218
00:14:15,530 --> 00:14:15,980
So.

219
00:14:17,070 --> 00:14:20,130
Wanted to do what, not this one, I mean, this one.

220
00:14:21,000 --> 00:14:24,900
So basically wanted to do is pretty simple instead of minus three.

221
00:14:24,900 --> 00:14:26,820
We wanted to do what?

222
00:14:26,850 --> 00:14:27,690
Let's do minus three.

223
00:14:27,690 --> 00:14:28,260
Minus one.

224
00:14:28,770 --> 00:14:35,940
So what's going to happen is that we will have a substraction of this number minus this number right

225
00:14:35,940 --> 00:14:36,160
there.

226
00:14:36,300 --> 00:14:38,290
So it's going to be something that is pretty cool.

227
00:14:38,310 --> 00:14:42,480
Well, we can subtract the additions minus the first number.

228
00:14:42,810 --> 00:14:46,350
So in this case, it's going to be minus one and minus two.

229
00:14:46,920 --> 00:14:48,480
So we can write it down right there.

230
00:14:48,490 --> 00:14:52,880
So our C minus one in here, minus three.

231
00:14:53,960 --> 00:14:59,120
So with this, we will have always positive numbers and same things here, wanted to change our F for

232
00:14:59,120 --> 00:15:00,110
A capital F.

233
00:15:02,380 --> 00:15:09,190
And our key for a capital case, we can delete this and then change key for a capital case.

234
00:15:10,170 --> 00:15:16,590
And the same thing we are going to take this race right now, as you can see, is we have created a

235
00:15:16,590 --> 00:15:22,740
macro that is well, it's not that complicated, but once again, we have created a macro that well

236
00:15:22,740 --> 00:15:24,060
is a little bit complicated.

237
00:15:24,300 --> 00:15:28,830
And we have worked to change all the parts of the code that we wanted to change.

238
00:15:29,560 --> 00:15:31,540
So let's try our micro right now.

239
00:15:31,560 --> 00:15:37,770
So what's going to happen is that in substraction numbers, we are no longer going to have, well,

240
00:15:37,770 --> 00:15:38,800
negative numbers.

241
00:15:38,820 --> 00:15:44,910
So it's all going to be positive numbers because we did we subtracted the additional minus the first

242
00:15:44,910 --> 00:15:45,330
numbers.

243
00:15:45,330 --> 00:15:48,740
So basically the addition will automatically be greater than the first number.

244
00:15:49,110 --> 00:15:55,980
So we are going to be sure that we have only only in this case, positive numbers.

245
00:15:57,610 --> 00:15:59,890
So let's try it out, so pretty simple.

246
00:15:59,920 --> 00:16:06,460
We're going to go inside of our developer tab and we are going to run or Microsoft will go here and

247
00:16:06,460 --> 00:16:08,100
we're going to run our test micro.

248
00:16:08,440 --> 00:16:11,230
And as you can see, everything works perfectly fine.

249
00:16:11,980 --> 00:16:13,340
So we have our letters.

250
00:16:13,360 --> 00:16:14,650
Some of our letters are kept.

251
00:16:14,650 --> 00:16:16,870
The letters are numbers right there are there.

252
00:16:17,110 --> 00:16:19,960
And finally, these abstractions are positive.

253
00:16:19,960 --> 00:16:22,090
And this is exactly what we need.

254
00:16:23,050 --> 00:16:25,630
So I hope you guys understand this part of the class.

255
00:16:26,320 --> 00:16:27,100
So pretty simple.

256
00:16:27,100 --> 00:16:30,100
What we have what we saw today are those two buttons.

257
00:16:30,100 --> 00:16:34,120
So we have this button right there and this button right there.

258
00:16:34,120 --> 00:16:40,330
So the step into and the toggle breakpoint button, those two buttons are pretty important, in my opinion,

259
00:16:40,330 --> 00:16:47,440
are really, really useful in the case that you want to make analysis of your VBA code and be able to

260
00:16:47,440 --> 00:16:53,440
take away all the little bugs or simply run your code step by step to be able to play inside of it and

261
00:16:53,440 --> 00:16:59,440
change some parts of the code so that first class guys all in the next class.