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

Introduction

This article describes how to declare and use variables in Excel sub procedures in Visual Basic for Applications (VBA), including variable scope and lifetime.

Variables in a sub procedure

Variables store values that can be used or changed while your VBA code runs. This article explains:

How to declare variables.
How variable scope determines where a variable can be used.
How long a variable retains its value.

Declare a variable in a macro

Use the Dim statement to declare a variable in a macro. The following example declares two variables as Integer:

Dim x As Integer, y As Integer

Because both variables are declared as Integer, x and y can store whole numbers from -32,768 through 32,767.

Note

When you declare multiple variables in one Dim statement, specify the data type for each variable.

If you don't specify the data type for each variable, only the variable immediately followed by As Integer is declared as Integer. In the following example, x is a Variant and y is an Integer:

Dim x, y As Integer

For more information, see the Variant data type section below.

To perform a variable test:

Open a workbook in Excel.
Press Alt+F11 to open the Visual Basic Editor.
On the Insert menu, select 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
Select OK.
In the Variable_Test macro, change the following line:

x = 10
to:

x = "error"
Run the Variable_Test macro.

A run-time error occurs because "error" is a string value and can't be assigned to the Integer variable x.

Data type summary

These are the common variable data types:

Screenshot that shows 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 assigns the Variant data type to the 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.

To see how this works in the Variable_Test macro:

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.

The macro runs without a type mismatch because x and y are Variant variables and can contain different types of data.

Scope of a variable

A variable's scope determines where it can be used in your VBA project. A variable can have procedure-level, private module-level, or public module-level scope, depending on where and how it's declared.

Procedure-level scope

A procedure-level variable can be used only within the procedure where it's declared. Other procedures can't access its value.

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

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
Select OK, and you'll get the following message:

x, as seen by Macro2 is
Select 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 declare variables in the Declarations section at the top of a module, before any procedures. Use Public to make a variable available to procedures throughout the project. Use Dim or Private at the module level to make the variable available only to procedures in that module.

To see the difference between the Public statement and the Dim statement:

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 uses an uninitialized value of zero).
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 when you use the Private statement as when you use the Dim statement. The variable x has the same scope: it's private to the module where it's declared.

Tip

At the module level, Dim and Private give a variable the same scope. Use Private when you want the intended scope to be explicit.

Lifetime of a variable

A variable's lifetime is the period during which it retains a value. When a variable loses scope, it no longer has a value.

Initialize the value of a variable

When a procedure starts, its variables are initialized. Numeric variables are initialized to zero, variable-length strings to a zero-length string (""), fixed-length strings with the character represented by ASCII character code 0, and Variant variables to Empty. An Empty Variant is represented as zero in a numeric context and as a zero-length string in a string context.

Procedure-level variables

A procedure-level variable declared with Dim retains its value until the procedure finishes running. If the procedure calls another procedure, the variable retains its value while the called procedure runs, but it isn't available to the called procedure.

To demonstrate how procedure-level variables work:

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
Select OK, and you'll get the message:

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

x, as seen by Macro2 is
Select 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
Select OK.
Run Macro1.

When Macro1 finishes running, the value of the procedure-level variable x is lost. Therefore, when you run Macro1 again, x is initialized to zero.

Static keyword

If a procedure-level variable is declared by using the Static keyword, the variable retains its value as long as code is running. 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:

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
Select OK, and you'll get the message:

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

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

x is 20
Select 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

A module-level variable and a static procedure-level variable have similar lifetimes, but their scopes are different.

To verify the lifetime of a module-level variable:

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
Select OK, and you'll get the following message:

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

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

x is 20
Select OK.

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

Reset a project to reset variables

To reset static and module-level variables, reset the VBA project in the Visual Basic Editor.

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