In this video we'll be covering how to declare variables in VBA as well as the different methods of
declaring variables and the different declaration types.
So the first thing we'll do is go over to our develop a tab and open up the VBA window by clicking on
Visual Basic under the code section.
Next we're going to go ahead and insert a module by clicking the insert dropdown and selecting module
and then we can create a temporary subroutine.
So we'll just say Public Sub example declarations with our open closed parentheses and hit enter the
first thing we're going to talk about is declaration method.
Variables can be implicitly declared or explicitly declared explicit declarations.
Means we're going to tell the code the variable name and the variable type.
And then we can store value into that variable.
For example we'll type dim which means declare followed by the variable name we'll use x bar with the
E and V capitalized and then we tell the system what type of variable this is by typing as.
And in this case we'll say this is an integer variable.
Press enter to go ahead and finish that declaration statement.
So now the system knows we have a variable named x bar that we will be using to store integers.
The system will only accept data in integer form into that particular variable.
If we try to feed that variable any other type of data the system is going to return an error message.
So we're gonna go ahead and set this variable type by typing X far equals 4.
Now one thing to notice is that after we've declared variables the system will automatically update
your letters to upper or lower case to match the variable which is a good way to check yourself and
make sure you don't make any typos.
So in this case we can see I typed x var all lowercase when I press enter the E and V are automatically
updated to uppercase so I know I haven't made any typo if we were to use implicit declaration.
However it might look something like this.
I will type in var equals nine.
In this case we haven't told the system we're declaring anything.
We didn't write them.
We also didn't tell the system what type of variable in var is we let the system determine the variable
type by telling it we want it to store the number nine in the implicit declarations.
The system will default to variant type variables which are sort of chameleon variables that will change
their type to match the data.
While this seems like a good thing at first it can actually be really inefficient and will slow down
your code.
So we typically want to avoid variant type variables unlike the auto case updates with explicit variables.
If you later type the same implicit variable but the letters are different cases the system assumes
you want to update all of that implicit variable throughout your code to be what you most recently typed
so previous variables will change but the one you just typed won't.
For example if we type Inbar all lowercase and change it to be something else we'll say 8 and hit enter.
We can see all of my Inbar variables are now lowercase which is what I just typed.
We'll go ahead and get rid of that line again.
Because we want to avoid variant type variables it's best practice to explicitly declare your variables
and there's actually setting for this that we can get to by going to tools and then options this checkbox
right here require a variable declaration means the system will not allow implicit variable declaration.
It will require you to declare all of your variables which is a good way to force yourself into explicit
variable declaration.
So I recommend you usually check this box but for our examples we'll leave this unchecked and click
ok.
Now to test these variables out we're going to create a little output on our sheet in Excel.
I'm going to type cells two comma one dot value equals x bar and sells two comma two dot value equals
in bar
talking through this briefly since it's covered in more depth in a future video sells tells the system
that we are about to refer to a specific cell in our excel file the first number represents the row
and the second number represents the column.
So here we're looking at second row first column and second row.
Second column which equates to rows to cells a two and B two on our excel file to help us see which
variables which will type explicit and implicit and now we'll know which of these cells a 2 or b 2 applies
to which variable type that we've declared.
Now we're gonna go ahead and test run this code by clicking on macros making sure example declarations
is highlighted and clicking run.
Now we can see our explicit variable came up as for and our implicit variable came up as 9.
If we go back into our code we can double check this and we see this makes sense.
So everything has run correctly.
Now we're gonna try something a little different.
We're gonna change the values that we put into each of these variables.
So instead of storing the number four in my x bar I'm going to try to store text which we do by putting
quote a quote.
So now we're going to try to store the letter A into our X FA which we told the system is supposed to
be an integer.
So if we go ahead and try to run this
we see we get a mismatch error.
If we click debug it will take us to the line that Excel sees as an error and we can see it's brought
us to this x bar equals line which means the system has recognized that this is text that we're trying
to store in this variable but we've told the system to expect integers into this variable.
Go ahead and click the stop button up here to end your code we're going to reset this to before to get
rid of that error message and instead we're going to set a into our m of our variable.
Go ahead and run the code again by clicking on macros highlighting the code and clicking run and we
can see this is updated to show that our implicit variable correctly store the value a.
Again this is because our implicit variable here is stored as a variant type variable which means it
will change its type to fit the data that we try to feed it.
So in summary there is a difference between explicit and implicit variables.
And generally speaking it's best practice to use explicit variables when you declare variables.
The next thing we're going to talk about is the method of declaration.
There's two different types.
There's global and there's local variables these different declarations we have here are both local
declarations meaning these variables only live within this subroutine that we've created.
If we were to create a second subroutine we'll come down here and say public sub second the system won't
recognize X FA or Inbar if we try to put them into this second subroutine.
As an example we'll say cells 3 1 value equals x bar and cells 3 2 dot value equals in 4.
Now we know that we've already declared these variables up in our first subroutine and x bar is explicitly
declared within the subroutine whereas in var is implicitly declared If we go back to our excel file
and use macros select second and try to run this we can see nothing shows up into these cells.
This is because if we go back to our code we see that X foreign in LA are only used in this top subroutine
and not in the second subroutine.
So X foreign Inbar whenever set values in our second subroutine.
Now let's say that we want to pass these values between these subroutines.
So if I set X far up in this first one I want it to be the same in my second one and I don't want to
have to continue to re declare my variables and reset the variable values.
This is a case where you would use something called a global variable.
So we'll go up to the very top give ourselves a couple lines and we're going to declare a third new
global variable in order to do this.
We're going to type public instead of dead.
We'll create a global var and we'll tell it that this global VAR is an integer.
Now you'll note that I actually moved this declaration to be outside of either my subroutines.
This is because I want to be able to use this global variable throughout any subroutines I have.
Not just these two local subroutines.
So now I've declared a global var as an integer using the word public instead of dim if I move down
into this code and I then set a value to my global var and we'll say this equals 7.
I'll add a new line here to show what my global war value is
and then down into the second subroutine I'll leave these two here since they're still local variables
and we still don't have a value for them in our second subroutine but we are going to add a third line
and we'll set this equal to our global var OK.
Now I'm going to go back to my excel file I'll take global into the cell so we can tell which variable
is global.
And now I am first going to run our example declarations again because this is where we told the system
initially what that global variable is going to be.
So we'll click Run and we can see my global variable has showed up here as being seven.
Now we're going to run the second macro we created click Run and we can see again my explicit and implicit
variables belong solely to the first macro because that's where we declared to them and we declared
them locally.
But our global variable which we declared publicly outside of our subroutines can be passed between
all of our subroutines once it's been set.
So it appeared in our second subroutine as its own value.
So now we understand the different types and methods of declaring variables and we can move on with
our Excel VBA coding tutorials.