In this lesson you will learn what is the purpose of using variables and how to use them. You will also learn how to declare variables and why is it so advisable to do it.

The lesson is divided into the following parts:

• How to use variables?
• Why is it important to declare variables?
• How to force variables to be declared? (Option Explicit statement)

Let's recall the example from the previous lesson, where the consecutive powers of 2 and 3 were printed into a worksheet:

1
2
3
4
5
6
7
8
9
10
11
12


Sub printPowers()
    Worksheets("Sheet1").Cells(1, 1) = 1
    Worksheets("Sheet1").Cells(2, 1) = 2
    Worksheets("Sheet1").Cells(3, 1) = 4
    Worksheets("Sheet1").Cells(4, 1) = 8
    Worksheets("Sheet1").Cells(5, 1) = 16
    Worksheets("Sheet2").Cells(1, 1) = 1
    Worksheets("Sheet2").Cells(2, 1) = 3
    Worksheets("Sheet2").Cells(3, 1) = 9
    Worksheets("Sheet2").Cells(4, 1) = 27
    Worksheets("Sheet2").Cells(5, 1) = 81
End Sub

In the example above, the value of each power has been calculated manually and just typed in VBA commands.

Of course, it is unacceptable to program this way when developing real applications. The most important reason is that it is very easy to make a mistake. In addition, you would have to use calculator when calculating powers of higher numbers and what is the use of a computer program when you still need a calculator when using it?

You can easily get around these problems by using variables.

Variable is the part of a computer program with his own unique name, to which you can assign certain value.

After you analyze the example below, you will see how variables work and how to use them.

First, modify the code from the previous lesson to the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35


Sub printPowers()
    Dim power As Long

    power = 1
    Worksheets("Sheet1").Cells(1, 1) = power

    power = power * 2
    Worksheets("Sheet1").Cells(2, 1) = power

    power = power * 2
    Worksheets("Sheet1").Cells(3, 1) = power

    power = power * 2
    Worksheets("Sheet1").Cells(4, 1) = power

    power = power * 2
    Worksheets("Sheet1").Cells(5, 1) = power

    '========================

    power = 1
    Worksheets("Sheet2").Cells(1, 1) = power

    power = power * 3
    Worksheets("Sheet2").Cells(2, 1) = power

    power = power * 3
    Worksheets("Sheet2").Cells(3, 1) = power

    power = power * 3
    Worksheets("Sheet2").Cells(4, 1) = power

    power = power * 3
    Worksheets("Sheet2").Cells(5, 1) = power
End Sub

At first glance, the modified code seems to be much longer and more complex than before (indeed, the code is longer, but it is partially because of empty lines inserted for better legibility).

If you examine the procedure carefully, you can see that most of the lines are very similar or even identical to each other. (Repeating the code is a very bad practice. However, at this stage you don't know yet how to avoid it. In the next few lessons you will learn more complex VBA statements (i.e. functions and loops) that will allow you to shorten the procedure above to a few lines).

As you have already learned, the first and the last line of the code are responsible for opening and closing the whole subroutine, respectively.

Our journey with variables starts in the second line of code:

2

Dim power As Long

All this command does is simply declare the usage of a variable called power in this subroutine.
The term variable declaration means you are telling the VBA compiler that variable with the name (power) and type (Long) defined in the declaration row will be used in the current subroutine (it is also possible to declare a variable that can be used in a wider scope, even in the whole application. You will learn how to do that later in this tutorial).

Generally speaking, VBA compiler can be imagined as a code flow and memory manager. When you declare a variable, it allocates some space in a computer memory to store the name, type and value of this variable.

The general form of the declaration command looks as follows:

 

Dim name [As variable_type]

The keyword used to declare a variable is Dim. After that keyword you should type the name of the variable being added. The convention for naming variables is identical to this one for naming functions and subroutines that we have discussed in the previous lesson.

After the name of the variable you should type another keyword - As and then specify the type of this variable. All the available data types will be discussed in details in the next lesson.

All the variables in this lesson will be declared with Long type, what means they are numbers from the range -2 147 483 648 to 2 147 483 647.

It is possible to declare a variable without specifying the type of this variable:

 

Dim variable

As you can see in the code listing below, it is also possible to declare more than one variable in a single line:

 

Dim variable1 As Long, variable2 As Long, variable3 As Long

If you modify the declaration statement as follows:

 

Dim variable1, variable2, variable3 As Long

variables variable1 and variable2 seem to be declared as Long, but they are not.
Every time you define the type of variable, it applies only for the variable directly preceding this declaration (variable3 in our example). Variables variable1 and variable2 are in fact declared without the type specified. As it was already mentioned before, in this case they are declared by default with Variant type.

Let's get back to the analysis of the code.

After the first three lines of the code have been executed, the compiler already knows about variable power and its type (variable power has been declared in line 3). Note that no value has been assigned to this variable yet except for the default value (0) that has been set to this variable when it was being created. In the line 4 of the code, the variable power is finally being given the value:

4

power = 1

The compiler is writing in the memory that variable power is equal to 1 now.
From now on, if the compiler encounters variable power it interprets it as 1 (until another value is assigned to it).

As you can see, it is very easy to assign a value to a variable - just type the name of the variable, add the equals sign and follow that with the value.

The next line of code (5) contains the statement to insert values into the cells of an Excel worksheet. It should look familiar to you since we have already used this statement in the previous lesson.

This command makes the compiler insert the value from the right side of the equal sign into the cell A1 o the worksheet Sheet1. Note, that there is a variable now after the equal sign instead of a plain number (like in the previous lesson).

At first it may seem that the VBA compiler will insert the text power into the Excel worksheet, but it won't, since the value on the right side of the equal sign is not a text, but variable.
There is a difference in the notation between a variable and a text string - strings are enclosed in double quotes while variables are notated without any special characters.
Variables and strings are interpreted by the compiler in the different way. The value of a string is just the content of this string. The value of a variable is usually completely different than its name that appears in the code. Moreover, a variable can store not only text but also other data types.

If you would like the text power to be inserted into an Excel worksheet, you should enclose it in double quotes, like in the listing below:

 

Worksheets("Sheet1").Cells(1, 1) = "power"

In our example, however, the word power is not enclosed in quotes, so it refers to the variable with the same name. From the point of view of the VBA compiler it is equivalent to the value stored in this variable at the particular moment.
Currently, value 1 is stored in the variable, that has been assigned to it in the line 4 of the code and this value will be inserted into the cell A1 of the worksheet Sheet1.

Now we would like to display the next power of two in the second row of the worksheet. The first thing to do is calculate the next power of two and assign it to the variable power (in our example it is done by the line 7 of the code):

7

power = power * 2

All we have to do is to multiply the current value of the variable power (i.e. 1) by two and assign the result of the operation (i.e. 2) back to the variable power.

Now we can display this value in the worksheet by using the already well-known command:

8

    Worksheets("Sheet1").Cells(2, 1) = power

In the next few rows (8-18), the variable power is being repeatedly multiplied by two and printed into the worksheet.

After we have printed five consecutive powers of two, it is time to insert into the second worksheet (Sheet2) the first few powers of three. Note that the variable power is equal to 16 at this moment and we have to reset it back to 1 if we want to use it for further calculations (row 19).

The rest of our example macro (rows 21-34) calculates out the first five powers of three and prints them in the worksheet Sheet2 (in the same way as it was done for the powers of two).

In the previous chapter, it was mentioned that it is possible to just use the variables in the code, without declaring them before, but doing this is asking for trouble.

Look at the example below. The code seems to be identical to the one from the previous chapter.

However, if you examine the code listing carefully, you will see there is a typo in the line 10 - the variable is misnamed pwoer instead of power.

What happens in such case?

In the second line of the code, the variable power of a Long type has been declared and the compiler has allocated some memory to store its value. At this time the variable power is equal to 0 (this is the default value for all numeric data types and every numeric variable is given this value when it is created).

In the next line (4) the value 1 has been assigned to the variable power.

In the next few lines (5-9) the code works in exactly the same way as in the previous example - the value of the variable power is multiplied by two and printed in the worksheet Sheet1. When the compiler gets to the line 9, the variable power is equal to 2.

Obviously, the intention of the developer was to repeat this operation several times in order to calculate out the next few powers of two.

Everything goes smoothly until the code execution reaches line (10). In this line we tell the compiler to use the variable pwoer for further calculations instead of power. It is quite obvious that this is just a typo and the variable power is the one we wanted to use. However, the compiler is not that smart and it considers them as completely different variables. Since this is the first time the variable pwoer is used in the code, it is automatically created and is given the default value - 0. Therefore, when the compiler reaches this line of code:

10

power = pwoer * 2

it multiplies 0 (the current value of variable pwoer) by 2 and assigns the result (0) to the variable power.

Afterward, the variable power is equal to 0, while the expected value was 4. And even though there are no more typos nor any other mistakes in the code, all the values returned after code line 10 are incorrect, making the macro completely useless.

A small typo was enough to make the macro works completely different than expected. Of course, this is just a simple example, where it is very easy to find the typo and fix it. However, in the more complex applications with thousands of lines of code, a bug like this is very difficult to catch and you can spend quite a long time trying to find it.

As you could see in the previous section, it is quite easy to jeopardize your application just by making a simple typo in the name of a variable. Fortunately, VBA provides us with the command Option Explicit - the easy way to protect the code from the risk of using the undeclared variables accidentally.

Option Explicit is very easy to be implemented. You just have to put it on the top of the module you want to protect. If the compiler sees the module starts with Option Explicit statement, it will not allow to use any undeclared variable in this module.

If you add Option Explicit statement to the top of the code from the previous section (the one with a typo) and try to run this macro, the error message will appear saying that there is an undeclared variable in your code and the name of this variable will be highlighted, as it is shown on the picture below.

Now you can see straight away that you have made a mistake and where it is located in your code.

Another good news is that it is possible to make VBA editor add Option Explicit statement by default to each new module. It will allow you to save a couple of seconds and, more important, ensure you don't forget to put it into the code.

If you want your VBA editor to insert Option Explicit statement automatically into each new module, choose the following options on the VBA editor menubar:

• Tools
• Options...

Select Require Variable Declaration checkbox in the window that appears on the screen.

It's done. Let's check if it works. Add a new module to the current file and open it in the VBA editor. As you can see, the module has been initialized with Option Explicit already put inside.