1
00:00:00,240 --> 00:00:06,720
Hello and welcome back to another class of our course, but the complete introduction to VBA with Excel.

2
00:00:07,350 --> 00:00:12,000
So in today's class, we are going to have a busy introduction to Mackerels.

3
00:00:12,210 --> 00:00:18,510
And what exactly are macros, how all this works and basically everything that is around macros.

4
00:00:18,690 --> 00:00:22,910
And what we are going to do is that we are going to record our first macros.

5
00:00:23,250 --> 00:00:24,660
So how exactly I want to do this.

6
00:00:24,960 --> 00:00:25,610
Pretty simple.

7
00:00:25,620 --> 00:00:30,830
So we are going to record a basic macro and then we are going to do well, make it a bit more complicated.

8
00:00:30,840 --> 00:00:37,470
So I'll try to make it in three well, in three different examples and basically you'll see how exactly

9
00:00:37,470 --> 00:00:38,100
backwards.

10
00:00:39,240 --> 00:00:39,650
All right.

11
00:00:39,990 --> 00:00:42,930
So basically, what exactly is a macro?

12
00:00:43,320 --> 00:00:48,770
So let's say, for example, that you do the same task the day after day after day after day.

13
00:00:49,650 --> 00:00:55,860
So instead of doing a certain task, you can automate this task directly with the Excel.

14
00:00:55,890 --> 00:01:01,680
So basically, we're not going to write any line of code because basically the macro feature will simply

15
00:01:01,680 --> 00:01:04,330
convert our task into code.

16
00:01:05,230 --> 00:01:13,020
So the only thing that we need to do is simply record a certain task that we want to do on a daily basis.

17
00:01:14,310 --> 00:01:15,110
So pretty simple.

18
00:01:15,120 --> 00:01:19,270
What I would like to suggest you to do as an example is pretty simple.

19
00:01:19,980 --> 00:01:23,840
So what we do at first is we are going to create a small report.

20
00:01:23,850 --> 00:01:29,820
So let's say, for example, you guys are working for a business and this business asks you what sends

21
00:01:29,820 --> 00:01:35,210
you a list of, let's say, names, numbers and numbers to name, age and number.

22
00:01:36,120 --> 00:01:39,590
And basically what you want to do is simply put, all this together.

23
00:01:39,600 --> 00:01:41,780
So you receive, let's say, 10 names per day.

24
00:01:42,810 --> 00:01:44,880
And basically that's pretty simple.

25
00:01:44,890 --> 00:01:46,820
You need to work with those 10 names.

26
00:01:46,830 --> 00:01:48,090
It just do something with.

27
00:01:48,600 --> 00:01:51,000
So what exactly we are going to start with.

28
00:01:52,740 --> 00:01:56,050
So we are going to start by giving a name to our report.

29
00:01:56,070 --> 00:01:57,920
So here we are going to give it a name.

30
00:01:58,650 --> 00:02:02,010
So pretty simple will go to whom will merge the sales.

31
00:02:02,250 --> 00:02:03,950
So here we will have our name.

32
00:02:04,140 --> 00:02:07,710
So we'll name it was just name.

33
00:02:08,430 --> 00:02:10,020
So here will have our name here.

34
00:02:10,030 --> 00:02:17,670
Will have the name of our employees here will have their age and here will have their number once again,

35
00:02:17,760 --> 00:02:20,370
just for the purpose of the example, it's all pretty simple.

36
00:02:22,260 --> 00:02:28,580
So basically then what we want to do, we said we need 10 steps from here, from five to 15.

37
00:02:28,590 --> 00:02:31,010
So basically we are going to receive 10 names per day.

38
00:02:31,620 --> 00:02:33,480
So here will have are some.

39
00:02:34,650 --> 00:02:38,570
Basically, we are going to some of the ages and all the numbers, so pretty simple.

40
00:02:40,530 --> 00:02:40,950
All right.

41
00:02:40,980 --> 00:02:42,430
So this is the first.

42
00:02:42,450 --> 00:02:45,510
Well, the first thing that we want to do is simply to integrate the name.

43
00:02:45,540 --> 00:02:49,530
So basically, what I want to do right now is give a name to my report.

44
00:02:49,530 --> 00:02:51,300
Let's say it's going to be a report.

45
00:02:51,630 --> 00:02:54,180
And I want to have my report looking something like this.

46
00:02:54,810 --> 00:02:55,710
So pretty simple.

47
00:02:55,710 --> 00:02:58,160
I want what my first macro what I will do.

48
00:02:58,170 --> 00:03:03,150
I will simply record how I'm writing a report right here and I want my macro to be right there.

49
00:03:03,540 --> 00:03:05,890
So pretty simple will start with the beginning.

50
00:03:05,910 --> 00:03:08,180
So we are going to make cells.

51
00:03:08,550 --> 00:03:10,960
We're going to go into the development.

52
00:03:11,850 --> 00:03:19,530
So from here, we are going to do we are going to record ourselves well doing this.

53
00:03:19,860 --> 00:03:24,090
So first thing that you guys want to do is simply go on the developer tab and the right here you will

54
00:03:24,090 --> 00:03:26,010
be able to record your macros.

55
00:03:26,280 --> 00:03:30,480
So we are going to record our macro and we are going to name it in this case.

56
00:03:31,560 --> 00:03:40,620
Well, let's call it a name of a report or simply step one, because that's the step one.

57
00:03:40,950 --> 00:03:47,020
Step one, then we want to do is maybe give if you guys want to give it a shortcut.

58
00:03:47,040 --> 00:03:53,430
So basically, the second that you guys are going to write down a certain key, it's going to appear.

59
00:03:53,850 --> 00:03:59,250
So let's say, for example, I want to I want to have my extend that I type control, let's say, OK,

60
00:03:59,700 --> 00:04:03,450
I will have my well, this macro that will automatically run.

61
00:04:03,450 --> 00:04:04,980
But I don't want to use shortcuts.

62
00:04:05,340 --> 00:04:07,400
Then finally, where exactly you want to store it.

63
00:04:07,410 --> 00:04:11,990
So if you store it in this workbook, you will only be able to use it on this workbook.

64
00:04:12,030 --> 00:04:17,310
If you store it on your personal micro workbook, well, you will be able to use it on all Excel files.

65
00:04:17,460 --> 00:04:19,380
So we are going to keep it on this workbook.

66
00:04:19,380 --> 00:04:19,930
Pretty simple.

67
00:04:20,880 --> 00:04:21,290
All right.

68
00:04:21,300 --> 00:04:26,100
So we named our macro step one, so we're just going to click on running it.

69
00:04:26,100 --> 00:04:29,600
The next thing that we are going to do, we are going to go here.

70
00:04:29,760 --> 00:04:31,260
So we are going to click two times.

71
00:04:31,260 --> 00:04:36,720
It's really important and simply select all those while all those things right here.

72
00:04:36,730 --> 00:04:40,470
So all the cells right here then will come back to home and we do what want to do.

73
00:04:40,470 --> 00:04:46,030
So basically we are going to merge the cells and we are going to give in to our report.

74
00:04:46,230 --> 00:04:48,900
So in this case, let's call it, I don't know.

75
00:04:49,860 --> 00:04:55,700
So let's call it the numbers, numbers and names.

76
00:04:56,490 --> 00:04:56,770
Good.

77
00:04:56,940 --> 00:04:59,610
So we have a name to our report now.

78
00:04:59,610 --> 00:05:01,740
We want to put it inside of a table.

79
00:05:01,740 --> 00:05:03,530
So this is exactly what we're going to do.

80
00:05:03,990 --> 00:05:04,590
So here we go.

81
00:05:04,590 --> 00:05:06,720
We have a report that is numbers and names.

82
00:05:07,030 --> 00:05:12,060
Then last thing really important, saving our macros and stopping stopping the recording part.

83
00:05:12,090 --> 00:05:13,440
So right now, we stop the recording.

84
00:05:14,220 --> 00:05:14,720
All right.

85
00:05:14,730 --> 00:05:17,850
So basically what we have just right now is pretty simple.

86
00:05:17,850 --> 00:05:22,590
We have created in macros, so we are going to rerun this macro on this page right here.

87
00:05:24,210 --> 00:05:25,170
So pretty simple.

88
00:05:25,560 --> 00:05:30,630
We're just going to go in on our macros and run Democrat step one.

89
00:05:31,200 --> 00:05:31,970
So here we go.

90
00:05:31,980 --> 00:05:34,200
If we run it here we have our macro.

91
00:05:34,200 --> 00:05:36,180
There is numbers and names.

92
00:05:36,300 --> 00:05:41,940
So pretty simple simply by clicking on our macro, it automatically appeared right here.

93
00:05:41,950 --> 00:05:42,720
So pretty simple.

94
00:05:42,870 --> 00:05:43,890
I don't need all this.

95
00:05:43,890 --> 00:05:50,060
I'll just delete it so you can see pretty simple and pretty cool as well.

96
00:05:50,490 --> 00:05:50,930
All right.

97
00:05:50,940 --> 00:05:54,150
Next thing that I said that we want to do is pretty simple.

98
00:05:54,150 --> 00:05:57,030
We want to continue the creation of our report.

99
00:05:57,300 --> 00:06:03,150
So basically what's going to happen is that we are going to receive names, ages and numbers, and I'm

100
00:06:03,150 --> 00:06:04,890
going to use random numbers to do it.

101
00:06:04,890 --> 00:06:05,850
So don't worry about it.

102
00:06:06,240 --> 00:06:11,790
So what we want to do right now is we want to create what we want to put name on the cell, each on

103
00:06:11,790 --> 00:06:14,010
this cell and number in this cell right here.

104
00:06:14,490 --> 00:06:20,520
Add the sum, put all this inside of a table and calculate the sum of all ages.

105
00:06:21,270 --> 00:06:24,600
So what will be the format of what we are going to receive on a daily basis?

106
00:06:24,930 --> 00:06:26,080
It will look something like this.

107
00:06:26,080 --> 00:06:31,440
So we are going to receive the ABC, the H.

108
00:06:36,430 --> 00:06:40,660
And for the ages and numbers, we're going to use random numbers.

109
00:06:40,690 --> 00:06:48,790
So basically it's a function so random between, let's say one to receive a number that is between 18

110
00:06:49,150 --> 00:06:51,670
and, I don't know, 50.

111
00:06:53,020 --> 00:06:53,720
All right.

112
00:06:53,740 --> 00:06:55,090
Same thing for numbers.

113
00:06:55,600 --> 00:06:57,490
And we simply put everything together.

114
00:06:58,150 --> 00:06:58,480
All right.

115
00:06:58,480 --> 00:06:58,930
Pretty cool.

116
00:06:59,200 --> 00:07:01,130
So right now, we have our numbers and age.

117
00:07:01,160 --> 00:07:04,570
So basically what we receive on a daily basis will be on this format.

118
00:07:04,570 --> 00:07:05,830
So we have absolutely nothing.

119
00:07:05,830 --> 00:07:10,590
And we are going to create the numbers, the name age numbers and then the sum of the calculation.

120
00:07:11,560 --> 00:07:11,980
So pretty.

121
00:07:12,700 --> 00:07:13,090
All right.

122
00:07:13,810 --> 00:07:15,970
Next thing that we want to do is pretty simple.

123
00:07:15,970 --> 00:07:18,070
It's just calculating the sum of all this.

124
00:07:18,100 --> 00:07:20,070
So basically we're calculating the sum of all this.

125
00:07:20,380 --> 00:07:23,710
So we are going to add all this inside of our macros.

126
00:07:23,710 --> 00:07:24,550
So pretty simple.

127
00:07:25,660 --> 00:07:27,880
So right now, let's simply do it.

128
00:07:28,510 --> 00:07:30,370
So how exactly we are going to do this?

129
00:07:31,120 --> 00:07:31,660
Pretty simple.

130
00:07:31,670 --> 00:07:33,790
We are going to record another micro.

131
00:07:33,820 --> 00:07:35,910
So once again, we are going to give it a name.

132
00:07:36,700 --> 00:07:38,410
So let's call it step two.

133
00:07:40,270 --> 00:07:40,650
All right.

134
00:07:40,660 --> 00:07:44,260
Now, what will be exactly in the step to show in the step two?

135
00:07:44,260 --> 00:07:52,310
First thing that we are going to do, we are going to select this top right here and call it name Shirou.

136
00:07:52,330 --> 00:07:53,640
So we have names here.

137
00:07:54,520 --> 00:07:57,670
Then we are going to add this then.

138
00:07:57,670 --> 00:07:59,110
Same thing for the top each.

139
00:08:01,420 --> 00:08:05,290
Age and what we are going to do, we are doing this as well.

140
00:08:05,320 --> 00:08:09,290
And finally, the last chapter, that is the number that will do the exact same thing.

141
00:08:09,310 --> 00:08:13,530
We'll write down numbers and we'll do this the exact same way.

142
00:08:14,290 --> 00:08:17,800
Then what I do is simply go down here.

143
00:08:19,410 --> 00:08:21,150
And do the same thing for some.

144
00:08:21,330 --> 00:08:28,530
So basically we are going to write down some so really important click a few times just to be sure that

145
00:08:29,400 --> 00:08:30,970
it automatically selects itself.

146
00:08:31,530 --> 00:08:36,360
And finally, the last thing that we want to do is simply calculate the sum of all the ages and all

147
00:08:36,360 --> 00:08:36,870
the numbers.

148
00:08:36,900 --> 00:08:38,790
So basically, this is exactly what we are going to do.

149
00:08:38,800 --> 00:08:51,450
So we are going to write down the sum function and calculate all the ages, as well as the sum of all

150
00:08:51,570 --> 00:08:52,110
numbers.

151
00:08:54,060 --> 00:08:58,620
So here we go again, see, it's pretty simple what to do right now is pretty simple.

152
00:08:58,620 --> 00:08:59,790
Just select the cell.

153
00:09:01,230 --> 00:09:01,650
Here we go.

154
00:09:02,040 --> 00:09:08,770
And finally, the last thing that we want to do is simply take all this and put it inside of a table

155
00:09:08,820 --> 00:09:09,250
like this.

156
00:09:09,750 --> 00:09:13,830
So what we'll do at the end will just go in our micro and we'll stop recording.

157
00:09:14,670 --> 00:09:19,650
So if we go inside of our sheet number two, what's going to happen is that we are going to run our

158
00:09:19,650 --> 00:09:20,070
micro.

159
00:09:20,370 --> 00:09:21,810
So here we have our step two.

160
00:09:21,900 --> 00:09:22,440
We run it.

161
00:09:22,710 --> 00:09:26,070
And as you can see, we have automatically a table that have been generated.

162
00:09:26,400 --> 00:09:28,380
So right now we have recorded two macros.

163
00:09:28,860 --> 00:09:32,940
Now, the only thing that is left is simply receiving all the names and all the ages.

164
00:09:33,810 --> 00:09:34,220
All right.

165
00:09:34,950 --> 00:09:40,110
Finally, the last thing that I want to do is create another table that is well, that will look pretty

166
00:09:40,110 --> 00:09:41,250
much the same thing.

167
00:09:41,460 --> 00:09:47,940
And inside of this table, I will have my names and my numbers as well, my names and the sum of my

168
00:09:47,940 --> 00:09:49,090
age and my numbers.

169
00:09:49,110 --> 00:09:49,860
So pretty simple.

170
00:09:49,860 --> 00:09:52,730
I'll do it next to this table right here.

171
00:09:53,580 --> 00:09:58,180
So I'll have my names right there and here.

172
00:09:58,200 --> 00:10:00,120
I will have the some of those things.

173
00:10:00,120 --> 00:10:00,810
So I'll do it.

174
00:10:01,080 --> 00:10:02,610
I'll record the macro automatically.

175
00:10:02,640 --> 00:10:03,330
So pretty simple.

176
00:10:04,230 --> 00:10:07,170
I'll go here and we'll just record the macro.

177
00:10:07,170 --> 00:10:10,170
So I'll click on the recording macro and I will do it.

178
00:10:10,200 --> 00:10:12,970
So here we have our step three.

179
00:10:13,860 --> 00:10:14,730
So pretty simple.

180
00:10:14,740 --> 00:10:16,020
This is exactly what we're going to do.

181
00:10:16,530 --> 00:10:20,460
So here we want our names will be equal to name.

182
00:10:21,960 --> 00:10:25,470
And in this table it's going to be equal to number.

183
00:10:27,970 --> 00:10:28,430
There you go.

184
00:10:29,230 --> 00:10:32,500
So just write down, I'm sure.

185
00:10:33,070 --> 00:10:34,270
All right, then.

186
00:10:34,270 --> 00:10:35,500
The name is pretty simple.

187
00:10:35,500 --> 00:10:38,850
So our name will be equal to the letter that we have right here.

188
00:10:39,130 --> 00:10:42,480
So we have A, B, C until the last name right here.

189
00:10:44,200 --> 00:10:48,740
And finally here inside of this table right here will have our son.

190
00:10:48,760 --> 00:10:53,470
So basically simply equal to this right there.

191
00:10:54,280 --> 00:10:54,650
All right.

192
00:10:55,360 --> 00:10:58,850
Next thing that we want to do is calculate the sum of those two numbers.

193
00:10:58,870 --> 00:10:59,690
So pretty simple.

194
00:10:59,700 --> 00:11:03,580
Will just click on Arcel and calculate the sum of those two numbers.

195
00:11:03,610 --> 00:11:07,000
So basically this one plus this one right here.

196
00:11:08,380 --> 00:11:10,210
And what I want to do next is pretty simple.

197
00:11:10,420 --> 00:11:11,700
Just put everything together.

198
00:11:12,910 --> 00:11:18,970
And finally, last thing that we want to do is calculate the sum of all those numbers.

199
00:11:19,450 --> 00:11:24,700
So once again, right here, what I'm trying to do is just teaching you how the macros work by doing

200
00:11:24,700 --> 00:11:25,770
multiple operations.

201
00:11:26,830 --> 00:11:33,490
Then next thing that what I do is pretty simple, just add some tables because you can see pretty simple.

202
00:11:33,730 --> 00:11:38,530
It's for the esthetic and we put everything like this.

203
00:11:38,750 --> 00:11:40,590
So everything is beautiful right now.

204
00:11:40,870 --> 00:11:42,100
So we have everything that we need.

205
00:11:42,430 --> 00:11:46,360
So what we want to do right now is simply go back here and stop recording.

206
00:11:47,440 --> 00:11:52,540
So right now we'll go on our sheet number two and what we're going to do, pretty simple, just going

207
00:11:52,540 --> 00:11:53,540
to run our macros.

208
00:11:53,540 --> 00:11:56,770
So we'll go here and you want to run our macro number three.

209
00:11:56,950 --> 00:11:59,590
So we have everything that we need right now.

210
00:11:59,590 --> 00:12:05,290
Each time that we receive our names and basic well, each end that we receive our names, ages and everything

211
00:12:06,300 --> 00:12:08,080
will be able to simply run our micro.

212
00:12:08,560 --> 00:12:13,590
So if we copy all the names, ages and stuff and we pass that right here, everything works fine.

213
00:12:13,600 --> 00:12:18,990
So basically we have achieved everything that we wanted to achieve simply with the use of macros.

214
00:12:19,210 --> 00:12:25,120
So if we try it all again, so we'll delete absolutely everything.

215
00:12:28,050 --> 00:12:28,710
Here we go.

216
00:12:29,580 --> 00:12:36,550
We'll take once again our numbers, so let's say the day begins and we receive our numbers right here.

217
00:12:36,630 --> 00:12:43,320
So we just received this and want to do right now is pretty simple, is just running.

218
00:12:43,320 --> 00:12:49,360
Our Macross will go inside of our macro's that are right here, will go with step one, will run it.

219
00:12:49,830 --> 00:12:51,690
So here we have numbers and names.

220
00:13:01,040 --> 00:13:01,550
Here we go.

221
00:13:02,390 --> 00:13:05,060
Next thing I want to run the step number two.

222
00:13:07,120 --> 00:13:13,780
We have the name age and numbers, so everything have been calculated and finally the last step, which

223
00:13:13,780 --> 00:13:15,360
is the step number two.

224
00:13:16,870 --> 00:13:21,670
So inside of our macros, if we're only step number three, as you can see, we have our table right

225
00:13:21,670 --> 00:13:21,880
here.

226
00:13:22,200 --> 00:13:24,060
As you can see, it's pretty simple to understand.

227
00:13:24,070 --> 00:13:29,890
And basically you can automate any tasks that you want simply with the use of macros, which in my opinion

228
00:13:29,890 --> 00:13:31,210
is really amazing.

229
00:13:31,510 --> 00:13:37,150
So right now, guys, you know how to create your own macros and automate your own tasks without even

230
00:13:37,150 --> 00:13:38,550
coding in VBA.

231
00:13:38,560 --> 00:13:39,720
So pretty simple.

232
00:13:40,000 --> 00:13:42,820
So that's a task that goes into all in our next class.