Using VBA Static Variables

In this post we are going to look at what static variables are, how they work, and how they are different from ordinary local variables.  In a future post we will build upon this and consider the advantage of  using static variables over locals, and look at an example of how they may be used in practice.

The Difference between Local and Static Variables

So what are static variables exactly? To understand this, let's begin by examining how ordinary local variables work. All variable types have there own particular scope and lifetime.  The first of these determines the locations within the program from which the variable can be accessed, and the latter determines how long the variable is active.  

Local Variables

Local variables have a scope which is limited to the sub procedure or function from which it is declared. For example:

Private Sub testThis()
    Dim intExample As Integer
    intExample = 10
    MsgBox ("The value of our test variable is " & intExample)
End Sub

Here we have declared an integer variable called intExample within a sub procedure called testThis().  Since the variable was declared within the sub procedure, it is local in scope - the value of 10 it  subsequently contains can only be accessed within the testThis() sub after the declaration statement. So if the MsgBox Statement  referencing the intExample variable is used outside the sub, the variable is not recognised. 

Furthermore, once code in the sub procedure completes execution, the intExample variable ceases to exist, and it's value of 10 is lost. The next time the testThis() sub executes, intExample's value will need to be set anew. As such ordinary local variables are limited to the length of time the sub procedure or function is executing. This is an example what is meant by a variables lifetime.

Static Variables

Now let's take a look at how the scope and lifetime of a static variable works in contrast to an ordinary local variable.  Consider the following code snippet:

Private Sub testThis()
    Static intExampleStatic As Integer
    Dim intExampleLocal As Integer
    intExampleStatic = intExampleStatic + 1
    intExampleLocal = intExampleLocal + 1
    response = MsgBox("The value of intExampleStatic is " & intExampleStatic & _
        ", whereas the value of intExampleLocal is " & intExampleLocal & ".",                     vbInformation, "Static & Local Variable Output")
End Sub

Here we have created another sub procedure called testThis(), and declared a static variable and an ordinary local variable, intExampleStatic and intExampleLocal respectively.  Notice how the declaration statement for the static variable uses the Static keyword rather than Dim ie: 

Static intExampleStatic As Integer

in contrast to ... 

Dim intExampleLocal As Integer   

Since both variables are declared as Integer, they are automatically initialized with a value of 0.  Then in the following lines of code we increment the value of  each of the variables by 1:

intExampleStatic = intExampleStatic + 1
intExampleLocal = intExampleLocal + 1

... and display both values using a message box statement: 

response = MsgBox("The value of intExampleStatic is " & intExampleStatic & _
        ", whereas the value of intExampleLocal is " & intExampleLocal & ".", vbInformation, "Static & Local Variable Output")

Now the scope of both kinds of variable, local and static, work in exactly the same way.  That is to say, static variables, along with ordinary local variables, can only be accessed from within the same sub procedure or function from which they were declared.  Where they differ is in their respective lifetimes.  Whereas a local variable ceases to exist after the code within the same sub or function completes execution, the value contained in a static variable will be retained - at least until the codes'  underlying form or report is closed, or in the case of a global module, until the database itself is closed. To demonstrate this in action, lets see what happens when the testThis() sub procedure starts being called.

The first time testThis() is called the message box output shows that the intExampleStatic and intExampleLocal variables have the same value ie 1:

It is not until the second and subsequent calls that we see the difference in lifetime between static and local variables taking effect.  This is because the value stored in the local variable is lost each time the sub procedure completes, and then re-initialized the next time it is called.  The static variable, on the other hand, retains its value from the previous call.  Hence, the output after the second call shows the static variable to have a value of 2, but the local variable again has a value of 1:

This pattern continues each time the sub procedure is called: the value of the static variable is retained and any increments are added to this retained value, whereas the local variable is created anew and any increments are applied to its initialization value of 0.  Hence the third time testThis() is called, the value of the static variable is increased to 3, and the value of the local variable remains at 1:

... and so on until the sub procedures's underlying form or report is closed.  It is not until the next time the form or report is reopened that the static variable's value is re-initialized to 0.

So this has shown us how static variables work and how they differ from ordinary local variables.  In the next blog post we are going to consider the advantages of using static variables over local variables, and will demonstrate this by creating a a simple order counter control where a user clicks two buttons to increment or decrement the quantity of an item to be ordered without having to type the actual number.