1
00:00:00,640 --> 00:00:05,920
In this video, we will be covering how to set up macros and subroutines in your Excel file.

2
00:00:06,580 --> 00:00:12,340
So first, we'll need to navigate to our developer tab and open up the VBA window by clicking on visual,

3
00:00:12,340 --> 00:00:13,810
basic and the code section.

4
00:00:15,990 --> 00:00:17,160
Now under insert.

5
00:00:18,240 --> 00:00:21,030
We see we have several different options to choose from.

6
00:00:21,600 --> 00:00:27,150
First is user form, which we can build as a user interface for users to enter data or interact with

7
00:00:27,150 --> 00:00:27,400
data.

8
00:00:27,480 --> 00:00:28,950
Andrew code indirectly.

9
00:00:29,580 --> 00:00:35,790
Second is a module which acts as a collection of various codes and macros for you to use throughout

10
00:00:35,790 --> 00:00:36,420
your file.

11
00:00:37,110 --> 00:00:42,900
Third is a class module which allows you to build your own objects and assign them properties to use

12
00:00:42,900 --> 00:00:43,650
in your code.

13
00:00:44,340 --> 00:00:49,740
Class modules are fairly complex and you can accomplish most simple goals with a use of regular modules.

14
00:00:50,070 --> 00:00:52,530
So let's go ahead and insert a module as an example.

15
00:00:53,610 --> 00:00:58,740
As a note, you can't insert multiple modules to help you organize your code, which I strongly recommend

16
00:00:58,740 --> 00:00:59,280
that you do.

17
00:01:00,160 --> 00:01:04,950
So now we're going to talk a little bit about how to actually set up your VBA code from this point forward

18
00:01:05,010 --> 00:01:09,780
in your module or in your form or whatever you happen to be writing code in your code.

19
00:01:09,780 --> 00:01:15,150
And VBA is actually going to work in subroutines which are basically little bits and pieces of code

20
00:01:15,210 --> 00:01:17,070
that actually run on your file.

21
00:01:17,430 --> 00:01:21,450
And you can put a whole bunch of subroutines in a single module and string them all together to make

22
00:01:21,450 --> 00:01:23,340
something awesome happen in your file.

23
00:01:23,760 --> 00:01:25,950
There are two ways to start off your subroutine.

24
00:01:26,490 --> 00:01:27,930
The first is public sub.

25
00:01:28,710 --> 00:01:30,090
So we will type public.

26
00:01:31,660 --> 00:01:34,420
Sub and then whatever you want to name your sub.

27
00:01:34,500 --> 00:01:36,240
So we'll call this example.

28
00:01:37,710 --> 00:01:41,010
Open parentheses, close parentheses and hit enter.

29
00:01:42,660 --> 00:01:47,670
Now, when we do this, you can see the system will automatically put an end sub here for us.

30
00:01:48,300 --> 00:01:52,230
That means everything between the public sub and the end sub is the code.

31
00:01:52,290 --> 00:01:55,500
And all of this code is the name of the routine in this case.

32
00:01:55,590 --> 00:01:56,220
Example.

33
00:01:57,690 --> 00:02:04,620
The second type of subroutine we can have is called a private sub, so we'll tape private sub and let's

34
00:02:04,620 --> 00:02:07,970
name this one private example.

35
00:02:09,680 --> 00:02:12,350
Open parentheses, close parentheses and hit enter.

36
00:02:13,490 --> 00:02:16,130
So these are two entirely different subroutines.

37
00:02:16,190 --> 00:02:17,900
This first one is called example.

38
00:02:17,960 --> 00:02:19,910
And the second one is called private.

39
00:02:20,000 --> 00:02:25,700
Example, the primary difference between a public sub and a private sub is that your public sub can

40
00:02:25,700 --> 00:02:27,320
be called from anywhere else.

41
00:02:27,350 --> 00:02:30,230
So we can call public subs from other subroutines.

42
00:02:30,500 --> 00:02:34,670
We can call them from our Excel file, but we can't do that with private subs.

43
00:02:35,240 --> 00:02:39,920
Private subs live within this particular module that we've declared them in, and they can be called

44
00:02:39,920 --> 00:02:44,510
from other subs within this module, but they cannot be called from the Excel file.

45
00:02:45,170 --> 00:02:51,200
As an example, if we go back to our Excel file click macros to see our list of macros available for

46
00:02:51,200 --> 00:02:57,110
us to run, and we can see only the example macro shows up here, which is the one that we declared

47
00:02:57,110 --> 00:02:57,800
publicly.

48
00:02:58,300 --> 00:03:00,980
Our private example macro does not show up.

49
00:03:03,220 --> 00:03:08,710
The parentheses that follow the name of your subroutine can be used to input particular variables or

50
00:03:08,710 --> 00:03:11,530
values into that subroutine from the very beginning.

51
00:03:12,100 --> 00:03:13,620
This will be covered in the later videos.

52
00:03:13,690 --> 00:03:14,920
We won't get into it here.

53
00:03:15,520 --> 00:03:19,990
You may have noticed that when you type certain words, they automatically become blue, such as our

54
00:03:19,990 --> 00:03:24,520
private sub or M sub, whereas other words remain black, such as private.

55
00:03:24,570 --> 00:03:25,120
Example.

56
00:03:26,160 --> 00:03:32,010
Words that become blue are words that VBA has recognized as key words that mean something to the code,

57
00:03:32,460 --> 00:03:35,260
they tell the code to perform an action or to create a loop.

58
00:03:35,310 --> 00:03:37,560
Or in this case, create a subroutine.

59
00:03:38,190 --> 00:03:42,510
The words that remain in black are names or specific action sets that we're creating.

60
00:03:43,410 --> 00:03:45,840
Let's also talk for a moment about green text.

61
00:03:46,500 --> 00:03:49,620
If we type an apostrophe followed by some text.

62
00:03:51,800 --> 00:03:56,020
And click, enter, we can see the text becomes green.

63
00:03:56,500 --> 00:04:02,470
This is a comment line, comment line, start with apostrophes and end when you hit enter the system

64
00:04:02,470 --> 00:04:08,080
will recognize this line as a comment or note for developer use and we'll skip over that line in the

65
00:04:08,080 --> 00:04:08,500
code.

66
00:04:08,980 --> 00:04:13,870
This is really helpful for annotating your code or organizing your code, both of which I encourage

67
00:04:13,870 --> 00:04:14,440
you to do.

68
00:04:15,400 --> 00:04:21,280
So in this video, we talked about public subs versus private subs, how to create them and the difference

69
00:04:21,280 --> 00:04:23,980
between blue, black and green text.