Measuring the Lifetime of a Variable or Constant

Scope determines which procedures can access a variable or procedure. A variable's lifetime determines when and for how long that variable is live (active in memory) and can therefore be accessed. To help clarify lifetime, within the context of a variable, here are a few guidelines that will help:

• Lifetime refers to the time during which a variable retains its value.

• A variable's value can change over its lifetime, but the variable retains some value.

• When a variable loses scope (that is, when the scope where it is defined vanishes) it loses its value, so there is a relationship between scope and lifetime, but they aren't the same thing.

When a procedure is executed, VBA initializes all the variables within that procedure's scope. That means each variable has a value, as listed in Table 9.1, even before the code assigns one. These initial values remain intact until the code explicitly assigns a value to the variable.

Table 9.1. Value of Initialized Variables

Data Type	Initialized Value
Numeric	0
Variable-length string	"" (zero-length string)
Fixed-length string	The result of Chr(0), which is a non-printable character
Variant	Empty
Object	Nothing

Now, let's look at the relationship between a variable's scope and its lifetime:

• A procedure-level variable that's declared with the Dim keyword has a lifetime that equals the procedure's lifetime. In other words, the variable retains its value only as long as the procedure is running.

• A module-level variable defined in a standard module retains its value until the database is closed and all code stops running. This is called the application lifetime.

• A module-level variable defined in a class module retains its value in an instance of the class as long as the instance remains open. This is called the object lifetime.

• A public variable retains its value until code stops running. Public variables always have application lifetime.

The Lifetime of a Procedure-Level Variable

You've already experienced a procedure-level variable's lifetime it's the same as the procedure's scope. When you execute the procedure, VBA initializes the variables. When the procedure quits, the variables lose their value. At procedure level, the scope is limited to the procedure itself and the lifetime lasts only as long as the procedure is running.

The following procedure shows a variable that's changing values throughout the lifetime of the procedure (see Figure 9.6):

 Function ProcedureLT1()   Dim strPLT As String   strPLT = "Variable is alive"   Debug.Print strPLT   strPLT = strPLT & " and well"   Debug.Print strPLT End Function 

Figure 9.6. Procedure-level variables maintain a value as long as the procedure is running.

This example defines and then redefines the strPLT variable. Notice that the variable doesn't disappear after it's used. You don't have to redeclare it each time you define it or modify it. In fact, you can redefine the variable as many times as you like. It's available as long as the code's running.

The Lifetime of a Module-Level Variable

Now let's take the next step and examine the lifetime of a module-level variable. Let's use the existing procedures in the Chapter 9 sample module as follows:

1. Enter the following declaration statement in the Chapter 9 sample module's General Declarations section:
   
   

   
   

    Dim intMLT As Integer 

   
   

2. Enter the following functions in the Chapter 9 sample module:
   
   

   
   

    Function ModuleLT1()   Debug.Print intMLT End Function Function ModuleLT2()   intMLT = 100 * 3   Debug.Print intMLT End Function 

   
   

3. Position the insertion point inside ModuleLT1 and press F5. The procedure prints the value 0 to the Immediate window because intMLT equals 0 after it's initialized but before a value is assigned to it.
   
   

4. Execute ModuleLT2 to print the value 300 to the Immediate window.
   
   

5. Execute ModuleLT1 a second time. This time, as shown in Figure 9.7, the procedure prints the value 300, and not 0, to the Immediate window. Not only is the variable accessible to both procedures, the variable retains its value between calling procedures. The variable doesn't die in between calls as a procedure-level variable does.
   
   

   Figure 9.7. The module-level variable is alive and well as long as code is running.

   

   
   

If you try to access intMTL from another module, VBA will not see it and will return a runtime error. However, the variable is still alive and useable within the scope of its own module. Don't confuse this behavior with the variable's lifetime. This behavior is a scoping issue and not a matter of the variable's life or death.

The Lifetime of a Public Variable

Public variables are accessible by any procedure. After they are initialized, they live until destroyed. You can change the variable's value, but the variable itself still returns a value.

To illustrate the long life of a public variable, change the declaration for intMTL in the General Declarations section of your Chapter 9 module to a Public declaration. Then, enter the following procedure in your second Chapter 9 sample module (the one with just PublicVariableTest):

 Function PublicLTTest()   Debug.Print intMLT End Function 

Figure 9.8 shows the result of executing PublicLTTest. Because the variable is public, PublicLTTest can refer to it from your second module. In addition, the variable's value remains intact. A module-level variable and a public variable can have the same lifetime, but not share the same scope. That means a variable might not be available to all procedures in the application, even though it is residing alive and well in memory.

Figure 9.8. A public variable lasts as long as the code is running.