Automating Microsoft Access with VBA

Earlier, you saw that a procedure-level's variable has no life outside of that procedure. But there's an exception to this rule: you can extend a procedure-level's variable beyond the procedure by declaring the variable as a static variable using the Static statement in the form

Static variablename [As datatype]

A static variable is a procedure-level variable that retains its value between calls to the procedure that declares it. In that way it's similar to a module-level variable. If you don't specify datatype, VBA makes it a variant. Thus, Static works just like Dim except that static variables have application lifetime.

CAUTION

Although a static value persists between calls to the procedure, it doesn't have scope outside of the procedure that declares the static variable.

The following procedure is a simple example of a static variable:

Function StaticVariableTest() Dim intValue As Integer intValue = intValue + 1 Debug.Print intValue End Function

You can probably guess what the Debug.Print statement prints to the Immediate window the first time you execute this statement. Figure 9.9 shows the results the first time around. The procedure prints the value 1 because intValue = 0 until the statement

intValue = intValue + 1

evaluates to 1.

Run the procedure a second time, and the procedure still prints the value 1 to the Immediate window. That's because intValue loses its value after the procedure is done. When you execute the procedure a second time, intValue equals 0 until the same statement as before evaluates to 1. As is, this statement will never return any value other than 1.

Now, change the Dim keyword to Static and run the procedure. Again, the procedure returns the value 1. Execute the procedure a second time using the static variable. This time, the procedure prints the value 2, as shown in Figure 9.10. That's because intValue retains its value between the two calls. If you execute the procedure again, intValue will equal 3, and so on.

NOTE

After creating an object variable and then defining it with a reference to an object, you might think you're done, but you're not. Always include code that sets all object variables to Nothing using the syntax

Set objectvariable = Nothing

Include a set-to-nothing statement in code when you're done with the variable, or add all such statements to the end of your procedure. In addition, be sure to include a set of these statements to any error-handling routine. That way if the procedure exits early because of an error, the object variables are still handled correctly.

The Nothing keyword is used only with object variables to determine whether the variable has a valid object reference.