Variable Scope in VBA for MS Access

[MMcCarthy]

We often get questions on this site that refer to the scope of variables and where and how they are declared. This tutorial is intended to cover the basics of variable scope in VBA for MS Access. For the sake of brevity I am sticking to common usage.

Wherever the term procedure is used in this tutorial it actually refers to a subroutine or function.

Definition of Scope
The scope of a variable where this variable can be seen or accessed from.

The levels of scope for a variable can be broken down as follows:

Procedure Scope

When a variable is declared inside a procedure it is only available within one instance of that procedure. Variables that are intended to have scope throughout a procedure should all be declared at the beginning of the procedure. Variables declared within a procedure can be declared using Dim or Static (static will be explained further in this tutorial).

Module Scope

When a variable is declared within a module but outside of any procedure then it is available thoughout the module. For Form modules the scope is limited to the code region of the form. For standard modules the limit of scope will depend on the type of declaration.

Private
If you Dim a variable in a module it will default to Private. This limits the scope of the variable to the module in which it is declared (when declared outside any procedure).

Dim str As String

Public
If you explicitly declare a variable Public (Dim is optional), this variable is available throughout the database. When used in a standard module there are no restrictions. This replaces the old use of Global as a variable declaration. If the module concerned is a Form module, the variable is restricted to the code region of the form in which it is declared. You cannot declare a public variable within a procedure. This will give an error. Furthermore, I've found that Public (module) variables are inaccessible to forms / reports in Access 2003.

Public str As String

Static
If a variable is declared Static (Dim is optional) it remains in existance and retains it's value even after the instance of the procedure in which it is declared, terminates. You can only declare Static variables within a procedure. A Static variable has a longer lifetime than an instance of the procedure in which it is declared. It remains in existance until the project terminates. The project can terminate or be reset without closure of the database.

Static str As String

NOTE:
All variables should be declared at the beginning of the code region in which they appear whether that is a module, procedure or block.