Sometimes you need a variable to be seen by all the procedures within your project, while other variables should only be available within a particular procedure. The visibility of a variable is known as its scope. Closely related to a variable's scope is its lifetime, or the period of program execution when the variable is live and available for use. Precisely where you declare a variable or constant in a program determines its scope and its lifetime.
In a nutshell, variables declared in the declarations section of a module using the Private keyword can be accessed by all the procedures within the module. Variables declared in the declaration section of a code module using the Public keyword can be accessed by the whole project. Variables declared in the declaration section of a class module using the Public keyword can be accessed by the whole project once an object reference has been made to the class. And variables declared using the Dim statement within a subroutine or function can only be accessed in the procedure in which they've been declared.
A variable that is declared within an individual procedure (that is, within a subroutine or a function) can only be used within that procedure, and is therefore said to have procedure-level scope. You can therefore define different variables that use the same name in different procedures (like the simple x variable commonly used in the For...Next loop). You can even use the same variable names in a calling procedure and in a procedure that it calls, and they will be treated as two separate variables.
The lifetime of a procedure-level variable ends when the End Sub or End Function statement is executed. As soon as the procedure is complete, references to the variables defined within that procedure are erased from the computer's memory. This makes procedure-level variables ideal for temporary, localized storage of information.
There is also a special type of variable that has procedure-level scope, called a static variable. A static variable is defined within a procedure, and although it has procedure-level scope, it has module-level lifetime. In practice, this means that you can only use the variable within the procedure in which it's defined, but its
46 Chapter 3 - VBA Variables and Data Types value is maintained between calls to the procedure. To declare a static variable, you use the Static keyword in a procedure; for example:
You can also declare a procedure as Static, in which case all variables declared within the procedure are treated as static, and their values are preserved between calls to the procedure. For example:
Static Procedure MyProcedure() Dim iCtr As Integer
Declaring a variable within a procedure must be done using the Dim or Static statement; you can't declare a variable or constant as Public, Private, or Friend within a procedure.
A variable has module-level scope when it can be accessed by all the subroutines and functions contained in a particular module. Variables and constants that have module-level scope also reside in memory for the lifetime of the module. That is to say, as long as the module remains in memory, its module-level variables and constants also remain in memory. To create a variable with module-level scope, you must declare it in the module's Declarations section (that is to say, outside of any subroutine or function) by using either the Dim or Private statement.
The Friend keyword can only be used for variables and procedure declarations within an object module, such as a class or a form module. Friend scope gives other object modules within the project access to the variable or method without requiring that it be declared as Public, which would include it in the class type library, thereby making it accessible by software objects outside the project.
Used outside of a procedure in place of the Dim statement, Public allows a variable to be seen by all procedures in all modules in the current project. If used in the context of a Class module, its scope is extended beyond the boundaries of the current project. The automatic creation of a COM interface for any public procedure or property means that it can be called by other software components as a method or property of the class in which it's defined.
Was this article helpful?