423,846 Members | 2,048 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Variable Scope in VBA for MS Access

MMcCarthy
Expert Mod 10K+
P: 14,534
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).

Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 5 '07 #1
Share this Article
Share on Google+