Part II: Programming Microsoft Access
434
Consider two variables named MyVariable. One of these variables is global (public) in scope,
while the other is a module-level variable declared with the Private keyword. In any procedure
Access uses one or the other of these variables. In a module where MyVariable is not declared,
Access uses the public variable. The private variable is used only within the module contain-
ing its declaration.
The problem comes when multiple procedures use a variable with the same name as the multiply-
declared MyVariable. Unless the developer working on one of these procedures has diligently
determined which variable is being used, a serious error may occur. All too easily, a procedure
might change the value of a public variable that is used in dozens of places within an applica-
tion. If even one of those procedures changes the public variable instead of a more local variable,
a very difficult-to-resolve bug occurs.
A bit farther down in Figure 11.8, you see two procedures (A and B). Each procedure declares a
variable that is usable only from within the procedure. In Procedure A, you see a variable named
X2 declared as an integer and assigned the value 99. Just below this assignment is a reference to
the Y1 variable defined at the top of the module. This is the variable that is accessible only from
within the module. Procedure B defines an integer variable named Y2 and assigns it a value of 55.
The X1 variable in Procedure B that is assigned the 19 value is the public X1 variable declared at
the top of the module.
Determining a variable’s lifetime
Variables are not necessarily permanent citizens of an application. Just as their visibility is deter-
mined by the location of their declaration, their lifetime is determined by their declaration as well.
A variable’s lifetime determines when it is accessible to the application.
By default, procedure-level variables exist only while the procedure is executing. As soon as the
procedure ends, the variable is removed from memory and is no longer accessible. As already dis-
cussed, the scope of procedure-level variables is limited to the procedure and cannot be expanded
beyond the procedure’s boundaries.
A variable declared in the declarations section of a form’s module exists as long as the form is
open. All the procedures within the form’s module can use the module-level variables as often as
they need, and they all share the value assigned to the variable. When the form is closed and
removed from memory, all its variables are removed as well.
The greatest variable lifetime is experienced by the variables declared in public (standard) mod-
ules. These variables are available as soon as the VBA application starts up, and they persist until
the program is shut down and removed from memory. Therefore, public variables retain their val-
ues throughout the application and are accessible to any of the procedures within the program.
Private variables (declared with the Private keyword) declared at the top of standard modules
endure throughout the application, but following the rules of variable scope, they’re accessible only
from within the module.
There is one major exception to the general rule that procedure-level variables persist only as long
as the procedure is running. The Static keyword makes a procedure-level variable persist
