Static Variables

In addition to scope, variables have a lifetime, the period of time during which they retain their value. The values in module-level and public variables are preserved for the lifetime of your application. However, local variables declared with Dim exist only while the procedure in which they are declared is executing. Usually, when a procedure is finished executing, the values of its local variables are not preserved and the memory used by the local variables is reclaimed. The next time the procedure is executed, all its local variables are reinitialized.

However, you can preserve the value of a local variable by making the variable static. Use the Static keyword to declare one or more variables inside a procedure, exactly as you would with the Dim statement:

Static Depth

For example, 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 ApplesSold
   ApplesSold = ApplesSold + num
   RunningTotal = ApplesSold
End Function

If ApplesSold was declared with Dim instead of Static, the previous accumulated values would not be preserved across calls to the function, and the function would simply return the same value with which it was called.

You can produce the same result by declaring ApplesSold in the Declarations section of the module, making it a module-level variable. Once you change the scope of a variable 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 code would be more difficult to maintain.

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. For example:

Static Function RunningTotal(num)

This makes all the local variables in the procedure static regardless of whether they are declared with Static, Dim, Private, or declared implicitly. You can place Static in front of any Sub or Function procedure heading, including event procedures and those declared as Private.