Microsoft KB Archive/843144

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

Article ID: 843144

Article Last Modified on 12/13/2006

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



Contents

 * INTRODUCTION
 * MORE INFORMATION
 * Variables in a sub procedure
 * Declare a variable in a macro
 * Data type summary
 * Variant data type
 * Scope of a variable
 * Procedure-level scope
 * Private and public module-level scope
 * Lifetime of a variable
 * Initialize the value of a variable
 * Procedure-level variables
 * Static keyword
 * Reset a project to reset variables



INTRODUCTION
This article describes how to use variables in Microsoft Excel sub-procedures in Microsoft Visual Basic for Applications.

back to the top



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.

back to the top

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, x and y, 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 the &quot;Variant data type&quot; section.

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 &quot;the value of x is &quot; & x & _ Chr(13) & &quot;the value of y is &quot; & 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 = &quot;error&quot; </li> Run the Variable_Test macro.</li></ol>

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

back to the top

Data type summary
The following table lists common variable data types:

back to the top

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: <ol>  Change the code in the Variable_Test macro to:

Sub Variable_Test Dim x, y x = &quot;string&quot; y = 1.23 MsgBox &quot;the value of x is &quot; & x & _ Chr(13) & &quot;the value of y is &quot; & y End Sub </li> Run the Variable_Test macro.</li></ol>

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

back to the top

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.

back to the top

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: <ol> Insert a new module into your project.</li>  Type both of the following macros into this module:

Sub Macro1 Dim x As Integer x = 10 MsgBox &quot;x, as seen by Macro1 is &quot; & x 'the next line runs Macro2 Macro2 End Sub Sub Macro2 MsgBox &quot;x, as seen by Macro2 is &quot; & x End Sub </li> Run Macro1.

You receive the following message:

x, as seen by Macro1 is 10

</li> Click OK.

You receive the following message:

x, as seen by Macro2 is

</li> Click OK.</li></ol>

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

back to the top

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: <ol> Save and close any open workbooks and then open a new workbook.</li> Start the Visual Basic Editor.</li> Insert a module into your project.</li>  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 </li> Insert another module into your project.</li>  Type the following code into this module:

Sub Macro2 x = x * 3 MsgBox x End Sub </li> Run the Macro_1a macro in the first module.

With the variable x declared as &quot;Public x As Integer&quot;, 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).</li> <li> Change the declaration line in the first module from:

Public x As Integer

to:

Dim x As Integer </li> <li>Run the Macro_1a macro.

With the variable x declared as &quot;Dim x As Integer&quot;, 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).</li> <li> Change the declaration line in the first module from:

Dim x As Integer

to:

Private x As Integer </li> <li>Run the Macro_1a macro.</li></ol>

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 Private statement 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.

back to the top

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.

back to the top

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 (&quot;&quot;), 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 (&quot;&quot;) in a string context.

back to the top

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: <ol> <li>Insert a new module into your project.</li> <li> Type both of the following macros into this module:

Sub Macro1 'set x as a procedure level variable Dim x As Integer MsgBox &quot;the initialized value of x is &quot; & x x = 10 MsgBox &quot;x is &quot; & x 'the next line runs Macro2 Macro2 MsgBox &quot;x is still &quot; & x End Sub Sub Macro2 MsgBox &quot;x, as seen by Macro2 is &quot; & x End Sub </li> <li>Run Macro1.

You receive the following message:

the initialized value of x is 0

</li> <li>Click OK.

You receive the following message:

x is 10

</li> <li>Click OK.

You receive the following message:

x, as seen by Macro2 is

</li> <li>Click OK.

Macro2 does not display a value for the variable x because the variable x is local to Macro1. You receive the following message:

x is still 10

</li> <li>Click OK.</li> <li>Run Macro1.</li></ol>

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).

back to the top

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: <ol> <li> Change the code in Macro1 to:

Sub Macro1 'set x as a procedure level variable Static x As Integer MsgBox &quot;the initialized value of x is &quot; & x x = x + 10 MsgBox &quot;x is &quot; & x End Sub </li> <li>Run Macro1.

You receive the following message:

the initialized value of x is 0

</li> <li>Click OK.

You receive the following message:

x is 10

</li> <li>Click OK.</li> <li>Run Macro1.

You receive the following message:

the initialized value of x is 10

</li> <li>Click OK.

You receive the following message:

x is 20

</li> <li>Click OK.</li></ol>

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: <ol> <li> Change the code in the module that contains Macro1 to the following:

Dim x As Integer 'create a module-level variable Sub Macro1 MsgBox &quot;the initialized value of x is &quot; & x x = x + 10 MsgBox &quot;x is &quot; & x End Sub </li> <li>Run Macro1.

You receive the following message:

the initialized value of x is 0

</li> <li>Click OK.

You receive the following message:

x is 10

</li> <li>Click OK.</li> <li>Run Macro1.

You receive the following message:

the initialized value of x is 10

</li> <li>Click OK.

You receive the following message:

x is 20

</li> <li>Click OK.</li></ol>

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.

back to the top

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

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

843145 Description of Excel sub-procedures in Visual Basic for Applications (Arrays)

back to the top

Additional query words: sub procedures VBA variables var xl2007 xl2003 xl2000 xl2002 xl

Keywords: kbvba kbprogramming kbinfo KB843144

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.