All procedures, functions, variables, and constants in VBA have their own scope. This means that they can only be used in a specific place in the program code—precisely where they are defined. For example, if variable A is declared with the Dim statement inside the body of the procedure Pro1(), then that procedure is its scope. Thus, if there is another procedure Pro2(), you cannot use the same variable inside it. If you attempt to do so, you will either receive an error message due to the use of an undeclared variable (in case the previously mentioned Option Explicit statement is active), or you will simply get a different variable—with the same name, but completely unrelated to the variable of the same name from the first procedure.
There are three types of variable scope:
- Procedure-level variables are recognized only within the procedure in which they are declared using the Dim or Static statement. Such variables are called local variables.
- Module-level variables are used only within the module in which they are declared, but not in other modules of the given project. They are declared with Dim or Private in the module declaration area, i.e., before the description of procedures.
- Module-level variables declared with Public are accessible to all procedures in the project. Such variables are called public variables.
A private variable retains its value only while the procedure in which it is declared is running. Once the procedure ends, the variable’s value is lost, and it must be reinitialized when the procedure is run again. Variables declared with the Static statement retain their value after the procedure ends, as long as the program continues to run.
Now let us consider the scope of procedures and functions. Procedures and functions have only two levels of scope: module-level and project-level. By default, project-level scope is applied. Thus, a procedure or function can be called by any other procedure or function within the same project. When describing procedures and functions at the project level, the optional keyword Public may also be used. The presence or absence of this word has no effect on the procedure.
If you want to define a procedure that is used only at the module level, the keyword Private is applied. Note that such a declaration not only restricts the scope of the procedure but also prohibits its use as a stand-alone procedure—it can only be called from another procedure.
Finally, when describing procedures or functions, the keyword Static can also be used. It does not affect the scope of the procedure, but it influences all variables declared within that procedure or function. In this case, all local variables acquire the status of Static, which means they remain in memory after the procedure ends and retain their previous values when the procedure is called again.
Let us now look at an example of a module.
Example of a Module
Public A1 As String Private A2 As Integer Dim A3 As Single Sub Pro1() Dim A4 As Integer Static A5 As Integer A1 = "Text string 1" A2 = 2 A3 = 3.14 A4 = A4 + 4 A5 = A5 + 5 MsgBox A4 MsgBox A5 End Sub Sub Pro2() Pro1 MsgBox A1 MsgBox A2 MsgBox A3 MsgBox A4 MsgBox A5 Proc1 End Sub
In this example, variable A1 is defined at the project level (the keyword Public is used), variables A2 and A3 are defined at the module level, variable A4 is defined at the procedure level inside Pro1(), and variable A5, although defined inside the body of Pro1(), is declared as static.
When procedure Pro2() is called, the following happens: from this procedure, Pro1() is called, which assigns values to all five variables (A1, A2, A3, A4, and A5), and then displays the current values of variables A4 and A5 in message boxes.
After Pro1() ends, the current values of variables A1–A5 are displayed. At this point, it turns out that variables A1–A3 have retained their values, since they are defined at the module level, while variables A4 and A5 have empty values, because their scope is limited to the procedures in which they are used. Any changes to these variables inside one procedure have nothing to do with similarly named variables in another procedure—these are, in fact, different variables that just happen to have the same names.
After that, Pro1() is called once again, and it again modifies and displays the values of variables A4 and A5. In this case, variable A4 once more takes the value 4, because each time the procedure is called, memory is newly allocated for this variable and it is initialized with an empty value. Unlike A4, the variable A5, declared as static, retains its previous value from the earlier call of this procedure. As a result, upon the second call its value becomes 10.