1
00:00:00,180 --> 00:00:05,610
Hello, guys, and welcome back to another class, of course, about the complete introduction to VBA

2
00:00:05,610 --> 00:00:06,760
with Excel.

3
00:00:07,320 --> 00:00:12,630
So in the best class, we talked about a few things that you guys can do with macros.

4
00:00:13,050 --> 00:00:19,970
And in today's class, what we are going to learn is how to use macros in multitasking.

5
00:00:19,980 --> 00:00:27,090
So basically, you will instead of just having one step instead of your micro, we are going to practice

6
00:00:27,090 --> 00:00:31,930
the use of macros to be able to make more than one calculation.

7
00:00:32,460 --> 00:00:36,900
So basically so basically what we are going to do to be able to do this is pretty simple.

8
00:00:37,770 --> 00:00:41,640
So what I suggest you do is, first of all, just write down letters.

9
00:00:41,640 --> 00:00:48,420
So as a root right here, then you can use the read function, the random function to be able to generate

10
00:00:48,420 --> 00:00:52,290
a random number between one and thirty thousand, like with it in the past class.

11
00:00:53,190 --> 00:00:58,200
And finally, we will have a random percentage that will be generated.

12
00:00:58,200 --> 00:01:01,710
And once again, this percentage will be between zero and one.

13
00:01:01,710 --> 00:01:04,620
So you can use the round function to be able to answer.

14
00:01:05,670 --> 00:01:09,510
So let's say I have this database that I receive each and every day.

15
00:01:09,870 --> 00:01:15,750
And what I want to calculate is what I want to have is, first of all, I want to have the percentage

16
00:01:15,750 --> 00:01:17,330
like the real percentage.

17
00:01:17,340 --> 00:01:23,250
So here I want to have seven, eight percent, for example, and I want to have the amount of this percentage.

18
00:01:23,520 --> 00:01:28,920
Then I want to have the percentage that is left and I want to have the amount of that percentage.

19
00:01:29,790 --> 00:01:34,500
So it sounds a little bit complicated, but we're going to do it together and you'll see it's going

20
00:01:34,500 --> 00:01:35,850
to be pretty simple.

21
00:01:36,540 --> 00:01:40,390
So all this will be stored inside of a macro.

22
00:01:40,590 --> 00:01:47,850
So instead of having to do all those steps each and every time, we will be able to do it well by just

23
00:01:47,850 --> 00:01:49,080
using one macro.

24
00:01:49,110 --> 00:01:51,660
So it's going to be pretty interesting what we are going to do.

25
00:01:52,740 --> 00:01:56,880
So take a few moments just to create your table so we can post the video.

26
00:01:57,360 --> 00:02:00,410
And when you guys are done, we can start it at.

27
00:02:01,440 --> 00:02:07,740
All right, so pretty simple, what we're going to do at first is go to the developer tab and want to

28
00:02:07,740 --> 00:02:09,240
do at first is pretty simple.

29
00:02:09,240 --> 00:02:11,780
We want to have this percentage right here.

30
00:02:11,790 --> 00:02:15,580
So we are going to name our our cells at first.

31
00:02:16,050 --> 00:02:19,110
So if you have first.

32
00:02:21,150 --> 00:02:21,840
Percentage.

33
00:02:26,420 --> 00:02:36,560
Then we will have right here the first number, then percentage left

34
00:02:39,260 --> 00:02:41,150
and number with.

35
00:02:43,010 --> 00:02:43,320
Great.

36
00:02:43,730 --> 00:02:48,510
So first of all, how exactly do we find the person so we'll start by recording democracy.

37
00:02:48,530 --> 00:02:54,490
As always, we're going to use a relative reference because we want to be able to work with our macro

38
00:02:54,500 --> 00:02:54,990
everywhere.

39
00:02:55,580 --> 00:02:56,420
So really important.

40
00:02:56,430 --> 00:03:00,370
Start by clicking on the case right here, because this is where we are going to start.

41
00:03:00,740 --> 00:03:05,840
Then we're going to record our macro will call it, let's say, I don't know.

42
00:03:05,840 --> 00:03:09,780
So we'll call it practice here.

43
00:03:10,550 --> 00:03:13,490
So if you guys want to add a shortcut, keep it's up to you.

44
00:03:14,510 --> 00:03:14,860
Do it.

45
00:03:15,560 --> 00:03:18,680
So just click on OK when it's already right.

46
00:03:18,890 --> 00:03:22,220
So first step, pretty simple.

47
00:03:22,220 --> 00:03:25,340
What we want to do at first is generate the percentage.

48
00:03:25,350 --> 00:03:26,420
So the real percentage.

49
00:03:26,420 --> 00:03:27,450
How exactly do we do this?

50
00:03:27,470 --> 00:03:28,160
Pretty simple.

51
00:03:28,610 --> 00:03:33,430
We're just going to click equal and we want to have this number right here.

52
00:03:33,770 --> 00:03:35,400
So this is the first step.

53
00:03:35,960 --> 00:03:39,640
So when we have this number, next thing to do is simply click on the percentage.

54
00:03:39,650 --> 00:03:41,370
So here it was done automatically.

55
00:03:42,440 --> 00:03:47,360
So if it's not done automatically, for example, you have it as a general number we want to do is simply

56
00:03:47,360 --> 00:03:48,710
click on the percentage.

57
00:03:49,470 --> 00:03:53,390
Then next step is calculating the first number right there.

58
00:03:53,600 --> 00:03:55,940
So what exactly would be the first number?

59
00:03:56,510 --> 00:03:57,410
So pretty simple.

60
00:03:57,410 --> 00:04:00,560
Just click click on the equal button.

61
00:04:00,560 --> 00:04:07,670
And basically the first number is simply this set percentage multiplied by this number right here.

62
00:04:07,880 --> 00:04:09,370
So this would be our phone number.

63
00:04:09,590 --> 00:04:12,460
So we are just going to multiply this number.

64
00:04:13,460 --> 00:04:16,940
So this percentage by this number right there.

65
00:04:17,490 --> 00:04:18,170
And here we go.

66
00:04:18,830 --> 00:04:20,180
So right now it's in percentage.

67
00:04:20,180 --> 00:04:21,900
We're just going to put it into general.

68
00:04:22,220 --> 00:04:25,820
So basically 40 percent of this number will be this number right there.

69
00:04:27,430 --> 00:04:32,860
Next step is calculating the percentage left, so pretty simple how exactly we calculate this, it's

70
00:04:32,860 --> 00:04:36,550
simply going to be one minus this percentage.

71
00:04:38,350 --> 00:04:44,200
And finally, the last step is simply calculating the number that is left so we can calculate it in

72
00:04:44,200 --> 00:04:49,330
two ways, so we can simply multiply this percentage by this number or we can just subtract this number

73
00:04:49,330 --> 00:04:49,960
by this number.

74
00:04:49,990 --> 00:04:51,370
So you decide how you want to do it.

75
00:04:51,850 --> 00:04:53,820
I'm going to go for a multiplication.

76
00:04:53,830 --> 00:04:54,550
So pretty simple.

77
00:04:54,550 --> 00:04:56,350
I'm going to multiply this number.

78
00:04:57,620 --> 00:04:59,850
By this number right there.

79
00:05:01,430 --> 00:05:04,550
All right, once again, really important to put it in general.

80
00:05:05,270 --> 00:05:07,500
So I have what I want right now.

81
00:05:07,520 --> 00:05:09,020
So basically, I have my first number.

82
00:05:09,020 --> 00:05:12,770
That is twenty nine percent of this number right here and it's there.

83
00:05:13,160 --> 00:05:17,430
My second number is seventy one percent of this number right here and it's right there.

84
00:05:17,720 --> 00:05:23,500
So what I want to do right now is simply put, everything in to this case.

85
00:05:23,510 --> 00:05:24,740
So those cases right there.

86
00:05:26,510 --> 00:05:26,840
Right.

87
00:05:26,840 --> 00:05:28,400
And when I'm all done, it's pretty simple.

88
00:05:28,400 --> 00:05:32,270
I can just stop the recording process, so I'll stop recording.

89
00:05:33,640 --> 00:05:37,820
All right, next step, what I want to do right now is simply generating it everywhere I need it.

90
00:05:37,830 --> 00:05:39,940
So let's say, for example, I want to have this Mike.

91
00:05:40,060 --> 00:05:41,580
I want to generate this Maku right there.

92
00:05:41,590 --> 00:05:44,530
So I'm going to go to my macro's and just run my practice micro.

93
00:05:44,980 --> 00:05:46,660
As you can see, it works perfectly fine.

94
00:05:46,660 --> 00:05:50,590
And each time that I refresh the page, it will automatically refresh the answers.

95
00:05:52,090 --> 00:05:54,780
It's pretty simple to do and to understand.

96
00:05:55,390 --> 00:05:57,900
So same thing right here and we can run it.

97
00:05:58,180 --> 00:06:03,510
And once again, if I try to run my macro, well, it works pretty fine.

98
00:06:03,880 --> 00:06:04,220
All right.

99
00:06:04,240 --> 00:06:09,640
So right now that you guys have this, as you can see, this is exactly how well it's possible to basically

100
00:06:09,910 --> 00:06:12,120
make a multi-step macro.

101
00:06:12,160 --> 00:06:15,770
It's possible to make a multi calculation's micro into just one macro.

102
00:06:16,380 --> 00:06:16,860
All right.

103
00:06:16,870 --> 00:06:18,400
What we can do right now is pretty simple.

104
00:06:18,410 --> 00:06:20,140
So I want to do another practice with you guys.

105
00:06:20,470 --> 00:06:23,560
And basically we are going to use that if function to do so.

106
00:06:23,590 --> 00:06:27,430
So basically, we're going to do pretty much the same thing that we did in the first.

107
00:06:28,690 --> 00:06:30,500
So what exactly we're going to do?

108
00:06:30,610 --> 00:06:31,390
So pretty simple.

109
00:06:31,390 --> 00:06:34,450
We're going to use those numbers right there.

110
00:06:34,600 --> 00:06:38,220
And what exactly we are going to do, we are going to add a function to it.

111
00:06:38,530 --> 00:06:40,270
So we're simply going to write down.

112
00:06:40,660 --> 00:06:44,830
So if this number is greater than this number, we're going to return?

113
00:06:44,830 --> 00:06:45,220
Yes.

114
00:06:45,310 --> 00:06:47,020
If not, we're going to return, no.

115
00:06:47,380 --> 00:06:49,530
So something that is pretty simple to do.

116
00:06:50,350 --> 00:06:51,630
And so we'll try it out.

117
00:06:51,640 --> 00:06:53,490
So we'll just write down.

118
00:06:53,500 --> 00:06:53,800
Right.

119
00:06:53,830 --> 00:06:55,570
So here we'll have our test.

120
00:06:56,710 --> 00:06:57,870
This would be the test step.

121
00:06:58,210 --> 00:07:00,940
And here we are going to record our other macro.

122
00:07:01,150 --> 00:07:02,030
So just in.

123
00:07:02,050 --> 00:07:07,240
So, first of all, we're going to use the relative reference on our test and then we're going to record

124
00:07:07,240 --> 00:07:08,120
our macro.

125
00:07:08,230 --> 00:07:09,040
So pretty simple.

126
00:07:09,040 --> 00:07:12,340
We'll call it practice to.

127
00:07:15,230 --> 00:07:16,700
All right, so let's do it.

128
00:07:16,850 --> 00:07:22,280
So pretty simple, we'll start articulation by writing down the if function and when what exactly we

129
00:07:22,280 --> 00:07:22,470
want.

130
00:07:22,520 --> 00:07:28,300
Well, first thing inside of our functional logical test and here the logical test is pretty simple.

131
00:07:28,580 --> 00:07:31,340
We want to know if this number is greater than this number.

132
00:07:31,370 --> 00:07:38,560
So basically, if this number is greater than this number, we want Excel to return.

133
00:07:38,720 --> 00:07:39,310
Yes.

134
00:07:40,970 --> 00:07:43,160
Otherwise we want Excel to return.

135
00:07:43,320 --> 00:07:44,850
No, no.

136
00:07:44,900 --> 00:07:46,630
You can see it's pretty simple.

137
00:07:46,640 --> 00:07:50,550
And if we run it, it works pretty fine.

138
00:07:50,570 --> 00:07:55,030
So it's yes, because this number is greater than this number.

139
00:07:55,550 --> 00:07:58,780
So we have our Macoute that is ready and waiting fine.

140
00:07:59,150 --> 00:08:04,310
So we can try to run it pretty much anywhere we want so we can, for example, go here and run on my

141
00:08:04,310 --> 00:08:04,970
crew right there.

142
00:08:05,750 --> 00:08:11,000
And it's going to be no, if we run it in a place where there is no test that has been done, it's going

143
00:08:11,000 --> 00:08:12,110
to run anyways.

144
00:08:12,380 --> 00:08:14,150
So we can, for example, run it right here.

145
00:08:15,560 --> 00:08:19,320
And as you can see, it's going to be no, because once again, it does the same calculation.

146
00:08:19,340 --> 00:08:23,580
So basically this number is not greater than this number because zero is not greater than zero.

147
00:08:24,110 --> 00:08:25,260
So it works pretty fine.

148
00:08:26,330 --> 00:08:28,640
So finally, what exactly we can do is pretty simple.

149
00:08:28,640 --> 00:08:31,460
We can try to put those two macros together.

150
00:08:31,460 --> 00:08:33,270
So it's possible to use one.

151
00:08:33,300 --> 00:08:39,090
Well, it's possible to have two macro's to do it, but we can also put those two macros together.

152
00:08:39,410 --> 00:08:42,350
So this is exactly what we are going to do right now.

153
00:08:42,830 --> 00:08:43,640
So pretty simple.

154
00:08:44,360 --> 00:08:48,510
As I said, the first the first step will be simply calculating the percentage.

155
00:08:49,460 --> 00:08:53,900
Then we're going to find out the first numbers simply by multiplying this number, by this number.

156
00:08:54,350 --> 00:08:59,210
And then we're going to calculate the percentage left, the number, the number that is left, and finally

157
00:08:59,210 --> 00:09:00,730
we'll do the logical test.

158
00:09:01,250 --> 00:09:03,890
So if you guys are ready, let's do it.

159
00:09:03,920 --> 00:09:04,700
So pretty simple.

160
00:09:05,450 --> 00:09:07,110
Let's go in our micro recorder.

161
00:09:07,130 --> 00:09:09,710
We are going to use a relative reference to do so.

162
00:09:10,220 --> 00:09:17,060
And we're going to record our macro will call it final practice.

163
00:09:19,840 --> 00:09:21,820
And we can run it, right?

164
00:09:21,850 --> 00:09:28,100
So, as I said, the first step is pretty simple is simply finding out the first number right there.

165
00:09:28,120 --> 00:09:32,090
So we want to be equal to this one right here.

166
00:09:32,620 --> 00:09:33,510
This number right there.

167
00:09:34,690 --> 00:09:36,250
So it's going to be twenty seven percent.

168
00:09:36,460 --> 00:09:38,350
It makes sense then.

169
00:09:38,530 --> 00:09:39,370
Same thing.

170
00:09:39,370 --> 00:09:43,410
We want to multiply our twenty seven percent by this number right there.

171
00:09:43,630 --> 00:09:44,860
So we are going to do so.

172
00:09:45,160 --> 00:09:52,430
So it will be equal to twenty seven percent multiplied by this number right here.

173
00:09:54,190 --> 00:09:54,540
Great.

174
00:09:54,550 --> 00:09:55,390
It works pretty fine.

175
00:09:55,750 --> 00:09:57,360
But we have one problem here.

176
00:09:57,370 --> 00:10:00,040
It's in percentage that we are going to put it in general.

177
00:10:00,760 --> 00:10:03,520
Then next step, calculating the percentage left.

178
00:10:03,910 --> 00:10:05,680
So pretty simple how exactly we do this.

179
00:10:05,710 --> 00:10:11,430
It's going to be one minus this percentage right here and we can run it.

180
00:10:11,650 --> 00:10:19,510
And finally, the last step of the calculation will simply be calculating what basically calculating

181
00:10:19,510 --> 00:10:20,870
the number that is left.

182
00:10:20,890 --> 00:10:24,190
So we are going to multiply this 70 percent by this number right here.

183
00:10:24,790 --> 00:10:25,510
So pretty simple.

184
00:10:25,510 --> 00:10:28,720
It's going to be equal to the 70 percent.

185
00:10:30,330 --> 00:10:37,860
Multiplied by this number right there, great, and what I want to do now is simply put everything into

186
00:10:38,820 --> 00:10:44,440
next, we are going to add those cases so we have our cases right here.

187
00:10:45,300 --> 00:10:49,590
And finally, the last step is writing down our logical test.

188
00:10:50,670 --> 00:10:53,420
So we're going to hear and write down our logical test.

189
00:10:53,940 --> 00:10:54,870
So it's pretty simple.

190
00:10:54,990 --> 00:11:07,560
We're going to write down if in this case, this number is greater than this number we want to return.

191
00:11:07,890 --> 00:11:08,390
Yes.

192
00:11:10,320 --> 00:11:12,120
Otherwise we want to return.

193
00:11:12,450 --> 00:11:12,900
No.

194
00:11:16,510 --> 00:11:21,890
And then once again, we can run our logical test and we can even at this little thing right here.

195
00:11:22,630 --> 00:11:27,510
So right now we should have our all our full my crew that is ready and should work pretty fine.

196
00:11:27,880 --> 00:11:31,460
So we'll start with the recording process and we will test our macro.

197
00:11:31,480 --> 00:11:33,540
So right now, our macro, what exactly do we have?

198
00:11:33,940 --> 00:11:38,260
We have our calculations right here, this declaration right there, this calculation, this calculation,

199
00:11:38,260 --> 00:11:40,450
and finally, the logical test.

200
00:11:41,410 --> 00:11:43,260
So this is exactly what we are going to do.

201
00:11:43,690 --> 00:11:45,290
We're going to run our micro.

202
00:11:45,310 --> 00:11:46,860
So, as you can see, it works pretty fine.

203
00:11:47,170 --> 00:11:48,180
We can even test it.

204
00:11:48,190 --> 00:11:50,210
So you can see everything works perfectly.

205
00:11:51,580 --> 00:11:55,440
So right now, what we have done is basically what we have in the built.

206
00:11:55,540 --> 00:12:01,580
In both cases, what we have done is we have used multiple steps to be able to achieve a certain task.

207
00:12:01,960 --> 00:12:07,180
But what I tried to show you in this class, pretty simple, is that you guys can basically put all

208
00:12:07,180 --> 00:12:09,550
this together inside of one macro.

209
00:12:10,330 --> 00:12:11,820
So it works pretty fine.

210
00:12:12,160 --> 00:12:15,430
So basically, let's say you guys want to use this macro somewhere right here.

211
00:12:15,820 --> 00:12:21,370
So I'm just going to take all this table copied and pasted, I don't know, test it.

212
00:12:22,660 --> 00:12:23,910
Let's say I passed that right there.

213
00:12:25,180 --> 00:12:29,110
So I have my all my full database right here and I want to generate my macros.

214
00:12:29,110 --> 00:12:30,690
So basically, this is exactly what I will do.

215
00:12:31,000 --> 00:12:33,590
I will generate my macros somewhere right here.

216
00:12:33,880 --> 00:12:35,400
Well, it's possible for me to do this.

217
00:12:35,620 --> 00:12:36,470
So pretty simple.

218
00:12:36,490 --> 00:12:37,450
Just go right here.

219
00:12:37,450 --> 00:12:42,220
I'll go inside of my macro and I can run my microphone, as you can see, works pretty fine.

220
00:12:42,280 --> 00:12:44,980
And basically it's going to use the numbers that are right there.

221
00:12:45,010 --> 00:12:46,390
So everything works perfectly.

222
00:12:48,390 --> 00:12:53,340
So as you can see, macros are something that is really, really useful right now, we did an example

223
00:12:53,340 --> 00:12:58,710
that, well, maybe doesn't make that much sense because we just did some calculations.

224
00:12:59,100 --> 00:13:02,960
But you can apply this in your well, in your everyday life.

225
00:13:02,970 --> 00:13:09,090
And if you were in management or any other business field, you'll see it's really, really important.

226
00:13:09,090 --> 00:13:12,420
What the microbes are really, really important can be really, really useful.

227
00:13:12,840 --> 00:13:13,410
So that's it for us.

228
00:13:13,450 --> 00:13:15,990
Thanks, guys, and see you in our next class.