In this video, we will be covering how to set up macros and subroutines in your Excel file.
So first, we'll need to navigate to our developer tab and open up the VBA window by clicking on visual,
basic and the code section.
Now under insert.
We see we have several different options to choose from.
First is user form, which we can build as a user interface for users to enter data or interact with
data.
Andrew code indirectly.
Second is a module which acts as a collection of various codes and macros for you to use throughout
your file.
Third is a class module which allows you to build your own objects and assign them properties to use
in your code.
Class modules are fairly complex and you can accomplish most simple goals with a use of regular modules.
So let's go ahead and insert a module as an example.
As a note, you can't insert multiple modules to help you organize your code, which I strongly recommend
that you do.
So now we're going to talk a little bit about how to actually set up your VBA code from this point forward
in your module or in your form or whatever you happen to be writing code in your code.
And VBA is actually going to work in subroutines which are basically little bits and pieces of code
that actually run on your file.
And you can put a whole bunch of subroutines in a single module and string them all together to make
something awesome happen in your file.
There are two ways to start off your subroutine.
The first is public sub.
So we will type public.
Sub and then whatever you want to name your sub.
So we'll call this example.
Open parentheses, close parentheses and hit enter.
Now, when we do this, you can see the system will automatically put an end sub here for us.
That means everything between the public sub and the end sub is the code.
And all of this code is the name of the routine in this case.
Example.
The second type of subroutine we can have is called a private sub, so we'll tape private sub and let's
name this one private example.
Open parentheses, close parentheses and hit enter.
So these are two entirely different subroutines.
This first one is called example.
And the second one is called private.
Example, the primary difference between a public sub and a private sub is that your public sub can
be called from anywhere else.
So we can call public subs from other subroutines.
We can call them from our Excel file, but we can't do that with private subs.
Private subs live within this particular module that we've declared them in, and they can be called
from other subs within this module, but they cannot be called from the Excel file.
As an example, if we go back to our Excel file click macros to see our list of macros available for
us to run, and we can see only the example macro shows up here, which is the one that we declared
publicly.
Our private example macro does not show up.
The parentheses that follow the name of your subroutine can be used to input particular variables or
values into that subroutine from the very beginning.
This will be covered in the later videos.
We won't get into it here.
You may have noticed that when you type certain words, they automatically become blue, such as our
private sub or M sub, whereas other words remain black, such as private.
Example.
Words that become blue are words that VBA has recognized as key words that mean something to the code,
they tell the code to perform an action or to create a loop.
Or in this case, create a subroutine.
The words that remain in black are names or specific action sets that we're creating.
Let's also talk for a moment about green text.
If we type an apostrophe followed by some text.
And click, enter, we can see the text becomes green.
This is a comment line, comment line, start with apostrophes and end when you hit enter the system
will recognize this line as a comment or note for developer use and we'll skip over that line in the
code.
This is really helpful for annotating your code or organizing your code, both of which I encourage
you to do.
So in this video, we talked about public subs versus private subs, how to create them and the difference
between blue, black and green text.