Variable Scope is an intimidating topic for some, but all I want you to do is remember this one question: Where do I want to use my variable? That’s the only question you need to answer to determine how you should declare your variables, since scope is defined when you declare your variable.
For a given workbook, there are 3 levels of scope, defined below from least accessible to most accessible.
3 Levels of Scope
If you’re writing quick scripts, you’ll most likely use Procedure Level variables. As a matter of fact, Procedure Level variables are the only type of variable we have used thus far in our Excel tutorials.
A Procedure Level variable is only visible (i.e., you can only use it) within the procedure it’s declared in. Recall, a procedure can be a Sub or a Function.
Let’s play around with this. Paste the following code and run ScopeDemo1.
The Call statement asks the compiler to run the procedure titled ScopeDemo2. As this demonstration shows, a Procedure Level variable is declared inside the procedure and is only accessible in that procedure. Hence, Range(“A2”) never gets filled with the string Florida Gators.
A Module Level variable, also called a Private Module Level variable, can be used by any procedure within the same module. It must be declared outside of your procedures, at the top of your module. To test this, insert a module, paste the following code and run ScopeDemo1.
You see how Range “A2” now has a value? That’s because the variable strCollege is a Module Level variable, and, therefore, is accessible to all procedures within the module. It’s a very subtle change; just move the Dim statement above your first procedure.
Note: You can also replace the keyword Dim with Private in Module Level variables. Hence, the alternate title of Private Module Level.
Project Level is sometimes referred to as Public Module Level. The name will make sense once you see the example. Start by making two modules in your Visual Basic Editor, paste the Module1 code in your first module and the Module2 code in your second module. Run ScopeDemo1.
The only difference between the Module Level and the Project Level code is that the keyword Dim in the Variable Declaration is replaced with the keyword Public. That’s why this type of variable is often called a Public Module Level variable. The resulting variable is available to all procedures across all modules in the workbook.
The following color-coded graphic puts it all together:
Lifetime of a Variable
The Lifetime of a variable is how long a variable can retain its value. A variable only retains its value for as long as it has scope. You can extend the scope, and thus the lifetime, by declaring your variable with the Static keyword.
If you use the Static keyword to declare a variable, the variable will retain its value even after all macros are finished! This is extremely handy for many applications, including codes requiring initial user setup or custom undo/redo buttons. To try a Static declaration, copy the example below and run your macro twice.
The Static Variable iCount retains its value after each execution, so each time you run your macro, iCount will increase by 1. If you were to declare iCount with the Dim statement, the result would always be a message box with a printed value of “1.” Go ahead, test it out.
Today was a big day for you. You mastered some pretty advanced topics. You learned what Scope was and you learned the three levels of VBA scope: Procedure, Module and Project. In addition, you now know how to extend the Lifetime of a variable by declaring it with the Static keyword.