How to use variables in Excel sub-procedures in Visual Basic for Applications

Variables in a sub procedure

A powerful feature of programming languages is the ability to store something in a variable so that the contents of the variable can be used or can be changed later in the procedure. This document discusses the following use of variables in Visual Basic:

How variables are declared.
The procedures and the projects that can use the variable.
The lifetime of a variable.

Declare a variable in a macro

The simplest way to declare a variable in a macro is to use the Dim statement. The following line declares two variables, as integers:

Dim x As Integer, y As Integer

With x and y specified as integers, you are telling Visual Basic to set aside sufficient memory for an integer variable (2 bytes each for x and y) and that the information that is stored in either x or y is a whole number between -32768 and 32767.

NOTE: If you declare more than one variable by using a single Dim statement, you must specify the data type for each variable.

If you do not specify the data type for each variable, as in the following Visual Basic code, only the variable y is set up as an integer variable. The variable x will be a variant type:

Dim x, y As Integer

For additional information, see Variant data type below.

To perform a variable test, follow these steps:

Save and close any open workbooks, and then open a new workbook.
Start the Visual Basic Editor (press ALT+F11).
On the Insert menu, click Module.
Type the following code:

Sub Variable_Test()
Dim x As Integer, y As Integer
x = 10
y = 100
MsgBox "the value of x is " & x & _
Chr(13) & "the value of y is " & y
End Sub
Run the Variable_Test macro. You receive the following message:

the value of x is 10
the value of y is 100
Click OK.
In the Variable_Test macro change the following line:

x = 10
to:

x = "error"
Run the Variable_Test macro.

You will receive a run-time error because "error" is not an integer, and you are trying to assign this string value to the integer variable x.

Data type summary

These are the common variable data types:

common variable data types

Variant data type

If you do not specify a data type when you declare a variable, or you do not declare a variable at all, Visual Basic automatically specifies the variant data type for this variable. The following are the advantages of variables that are declared as this data type:

The variables can contain string, date, time, Boolean, or numeric values.
The variables can convert the values that they contain automatically.

The disadvantage is that variant variables require at least 16 bytes of memory. 16 bytes of memory can be significant in large procedures or in complex modules.

To see how this works in the Variable_Test macro, follow these steps:

Change the code in the Variable_Test macro to:

Sub Variable_Test()
    Dim x, y
    x = "string"
    y = 1.23
    MsgBox "the value of x is " & x & _
    Chr(13) & "the value of y is " & y
End Sub

Run the Variable_Test macro.

You will not receive an error because you can assign anything to the variant variables x and y.

NOTE: You can also leave out the following line and the macro will still work as the variables x and y are treated as Variant data types:

Dim x, y

Scope of a variable

When you declare a variable, it may or may not be seen by other macros in the same module, in other modules, or in other projects. This availability of a variable in modules is referred to as scope. The three types of scope are procedure-level, private module-level, and public module-level. The scope depends on how and where you declare your variable or variables.

Procedure-level scope

A variable with procedure-level scope is not seen outside the procedure where it is declared. If you set the value of a variable that has procedure-level scope, that variable's contents will not be seen by other macros.

To verify that a variable with procedure-level scope is not seen outside the procedure where it is declared, follow these steps:

Insert a new module into your project.
Type both of the following macros into this module:
Sub Macro1() Dim x As Integer x = 10 MsgBox "x, as seen by Macro1 is " & x 'the next line runs Macro2 Macro2 End Sub Sub Macro2() MsgBox "x, as seen by Macro2 is " & x End Sub
Run Macro1, and you'll get the following message:

x, as seen by Macro1 is 10
Click OK, and you'll get the following message:

x, as seen by Macro2 is
Click OK.

Macro2 does not display a value for the variable x because the variable x is local to Macro1.

Private and public module-level scope

You can define variables in the declarations section of a module (at the top of a module, above all sub procedures), and set the scope of your variable by using the Public statement, the Dim statement, or the Private statement. If you put the Public statement in front of your variable, your variable will be available to all the macros in all the modules in the project. If you put either the Dim statement or the Private statement in front of your variable, your variable is available only to macros in the module where it is being declared.

To see the difference between the Public statement and the Dim statement, follow these steps:

Save and close any open workbooks and then open a new workbook.
Start the Visual Basic Editor.
Insert a module into your project.

Type the following code into this module:

Public x As Integer
Sub Macro_1a()
    x = 10
    MsgBox x
    Macro_1b
End Sub
Sub Macro_1b()
    x = x * 2
    MsgBox x
    Macro2
End Sub

Insert another module into your project.
Type the following code into this module:

Sub Macro2()
x = x * 3
MsgBox x
End Sub
Run Macro_1a macro in the first module.
With the variable x declared as "Public x As Integer", all three macros in the project have access to the value of x. The first message box displays a value of 10. The second message box displays a value of 20 (because x is multiplied by 2 in Macro_1b). The third message box displays a value of 60 (because the value of x was changed to 20 in Macro_1b and then it was multiplied by 3 in Macro2).
Change the declaration line in the first module from:

Public x As Integer

to:

Dim x As Integer
Run the Macro_1a macro.
With the variable x declared as "Dim x As Integer", only the macros in the first module have access to the value of x. So the first message box displays a value of 10, the second message box displays a value of 20, (because x is multiplied by 2 in Macro_1b) and the third message box displays a value of 0 (because Macro2 does not see the value of x and the uninitialized value of zero is used by Macro 2).
Change the declaration line in the first module from:

Dim x As Integer

to:

Private x As Integer
Run the Macro_1a macro.
The same message boxes are displayed by using the Private statement scope as they were using the Dim statement. The variable x has the same scope, private to the module where it is declared.

NOTE: If you want the scope of your variable to be limited to the module where it is declared, use the Privatestatement instead of the Dim statement. They both achieve the same effect, but the scope is clearer when you read the code if you use the Private statement.

Lifetime of a variable

The time during which a variable retains its value is known as its lifetime. The value of a variable may change over its lifetime but it will retain a value. Also, when a variable loses scope, it no longer has a value.

Initialize the value of a variable

When you run a macro, all the variables are initialized to a value. A numeric variable is initialized to zero, a variable length string is initialized to a zero-length string (""), and a fixed length string is filled with the ASCII code 0. Variant variables are initialized to Empty. An Empty variable is represented by a zero in a numeric context and a zero-length string ("") in a string context.

Procedure-level variables

If you have a variable that is declared in a macro by using the Dim statement, the variable retains its value as long as the macro is running. If this macro calls other macros, the value of the variable is retained (not available to the other macros though) as long as these other macros are also running.

To demonstrate how procedure-level variables work, follow these steps:

Insert a new module into your project.
Type both of the following macros into this module:

Sub Macro1()
    'set x as a procedure level variable
   Dim x As Integer
   MsgBox "the initialized value of x is " & x
   x = 10
    MsgBox "x is " & x
    'the next line runs Macro2
    Macro2
    MsgBox "x is still " & x
End Sub
Sub Macro2()
    MsgBox "x, as seen by Macro2 is " & x
End Sub
Run Macro1.
You receive the following message:

the initialized value of x is 0
Click OK, and you'll get the message:

x is 10
Click OK, and you'll get the following message:

x, as seen by Macro2 is
Click OK.
Macro2 does not display a value for the variable x because the variable x is local to Macro1. You get the following message:

x is still 10
Click OK.
Run Macro1.

You receive the same messages that are described in steps 3 through 6 because as soon as Macro1 stopped running in Step 6, the value of the variable x was lost. Therefore, when you rerun Macro1 in Step 7, the first message shows the value of x as zero (the initialized value).

Static keyword

If a procedure-level variable is declared by using the Static keyword, the variable retains its value until your project is reset. Therefore, if you have a static variable, the next time that you call your procedure, the static variable is initialized to its last value.

To see how the Static keyword works, follow these steps:

Change the code in Macro1 to:

Sub Macro1()
    'set x as a procedure level variable
    Static x As Integer
       MsgBox "the initialized value of x is " & x
    x = x + 10
    MsgBox "x is " & x
End Sub

Run Macro1.
You'll get the message:

the initialized value of x is 0
Click OK, and you'll get the message:

x is 10
Click OK.
Run Macro1, and you get the following message:

the initialized value of x is 10
Click OK, and you'll get the following message:

x is 20
Click OK.

The values that appear in the messages are different the second time because the variable x is declared as a static variable and the variable retains its value after you run Macro1 the first time.

NOTE: If you have a module-level variable, its lifetime is the same as if it were a static procedure-level variable.

To verify the lifetime of a module-level variable, follow these steps:

Change the code in the module that contains Macro1 to the following:

Dim x As Integer  'create a module-level variable
Sub Macro1()
   MsgBox "the initialized value of x is " & x
   x = x + 10
   MsgBox "x is " & x
End Sub

Run Macro1, and you'll get the following message:

the initialized value of x is 0
Click OK, and you'll get following message:

x is 10
Click OK,
Run Macro1, and you'll get this message:

the initialized value of x is 10
Click OK.
You receive the following message:

x is 20
Click OK.

The values that appear in the messages are different the second time because the variable x is declared as a static variable and it retains its value after you run Macro1 the first time.

Reset a project to reset variables

If you want to reset the value for a static variable or for a module-level variable, click the Reset button on the
Standard toolbar, or click Reset on the Run menu.

If you do this for the Macro1 project and then rerun Macro1, the value of the variable x is initialized back to zero and you receive the first message:

the initialized value of x is 0

How to use variables in Excel sub-procedures in Visual Basic for Applications

Introduction

More Information