Specifying Variable Scope and Lifetime

When you declare a variable within a procedure, only code within that procedure can access or change the value of that variable — that is, the variable has a scope that's local to that procedure. Sometimes, however, you need to use a variable with a broader scope, such as one whose value is available to all the procedures within the same module, or even to all the procedures in your entire application. Visual Basic allows you to specify the scope of a variable when you declare it.

Depending on how it's declared, a variable is scoped in one of three ways, as shown in the following table.

Tip

It's generally a good idea to use the narrowest possible scope for your variables. For example, if you're using a temporary variable inside a procedure, use procedure (local) scope instead of private scope or public scope. Limiting the scope of your variables makes your macros more modular and helps reduce bugs.

A variable's lifetime is the time during which Visual Basic preserves the value in the variable. The values in private and public variables are preserved while the workbook is open, unless you edit a Visual Basic module in that workbook. When you edit a Visual Basic module, all modules in that workbook are recompiled and all variables are reset.

Although variables declared inside a procedure have the same scope level whether they're declared with Dim or Static, they have different lifetimes. Local variables declared with Static exist the entire time that the workbook where they're declared is open; that is, the variables retain their values between calls to the procedure. Local variables declared with Dim exist only while the procedure where they're declared is running. When a procedure ends, the values of its local variables aren't preserved and the memory used by the local variables is reclaimed. The next time the procedure runs, all its local variables are reinitialized.

Procedure-Level Variables

Procedure-level (or local) variables are recognized only in the procedure where they're declared. Local variables are a good choice for any kind of temporary calculation. All implicitly declared variables have local scope. You declare local variables explicitly by using the Dim or Static keyword inside the procedure, as shown in the following example.

Sub DoTheWork
    Dim intTemp As Integer
    Static intPermanent As Integer
.
.
.
End Sub

Tip

Although you can use local variables with the same name in several procedures, it's usually best to use unique variable names. Using the same name in several procedures can make your macro harder to debug.

Preserving the Value of a Local Variable

If you want to preserve the value stored in a local variable between calls to the procedure that contains it, you should declare the variable using the Static keyword, as in the following example.

Static depth

The following function calculates a running total by adding a new value to the total of previous values stored in the static variable accumulate.

Function RunningTotal(num)
    Static accumulate
    accumulate = accumulate + num
    RunningTotal = accumulate
End Function

If accumulate were declared with Dim instead of Static, the previously accumulated values would not be preserved across calls to the function, and the function would simply return the value it was called with.

You could produce the same result by declaring accumulate in the declarations section of the module, making it available to any procedure in the module. After you've changed the scope of a variable in this way, however, the procedure no longer has exclusive access to it. Because other procedures can access and change the value of the variable, the running totals might be unreliable and the macro is more difficult to maintain.

Tip

Use static variables if you need to preserve the value of a local variable each time a procedure is called. For example, you could use a static variable to record the number of times the procedure has been called or to record a running total, as shown in the preceding example.

Declaring All Local Variables as Static

To make all local variables in a procedure static, place the Static keyword at the beginning of a procedure heading, as in the following example.

Static Function RunningTotal(num)

This makes all local variables in the procedure static, regardless of whether they're declared explicitly with Static or Dim or declared implicitly. You can place Static in front of any Sub or Function procedure heading.

Note

After you edit a module, Visual Basic recompiles all modules in the workbook and resets all variables the next time you run any Visual Basic procedure or function. If you need to preserve values when the workbook is compiled or saved, use a hidden worksheet to store the values.

Private Variables

Private variables are available to all the procedures in the module where they're declared, but not to code in other modules. You create private variables by declaring them with Dim or Private in the declarations section of a module (the top section, above procedure definitions), as in the following example.

Dim intTemp As Integer
Private intTemp2 As Integer

Tip

Because variables declared at the top of a module are private by default, there is no difference between variables declared with Private and those declared with Dim. However, Private is preferred because it readily contrasts with Public and makes your macros easier to read. You cannot declare variables in a procedure using the Private keyword.

Public Variables

Public variables are available to every procedure in every module in the workbook where they're declared, as well as in all other workbooks that contain a reference link to the workbook where those variables are declared. (For information about creating a reference link to a workbook, see Chapter 1, "Modules and Procedures.")

You use the Public keyword to declare a public variable. Like private variables, public variables are declared in the declarations section at the top of the module; you cannot declare private or public variables inside a procedure.

Public intX As Integer

A public variable exists and retains its value from the time a value is assigned to the variable until the workbook closes (or until you edit a procedure, thus causing Visual Basic to recompile).

Note

To create a variable that's available to all modules in a given workbook but not to any other workbooks, make the variable public by declaring it with the Public keyword, but make the module itself private to the workbook that contains it by using the Option Private Module statement in the module.