What is a Scope?
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.
- Procedure Level
- Module Level
- Project Level
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.
Sub ScopeDemo1() Dim strCollege As String strCollege = "Florida Gators" Range("A1") = strCollege Call ScopeDemo2 End Sub Sub ScopeDemo2() Range("A2") = strCollege End Sub
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
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.
Dim strCollege As String Sub ScopeDemo1() strCollege = "Florida Gators" Range("A1") = strCollege Call ScopeDemo2 End Sub Sub ScopeDemo2() Range("A2") = strCollege End Sub
You see how Range “A2” now has a value? That’s because the variable
Dim statement above your first procedure.
Note: You can also replace the keyword
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.
Public strCollege As String Sub ScopeDemo1() strCollege = "Florida Gators" Range("A1") = strCollege Call ScopeDemo2 Call Module2.ScopeDemo3 End Sub Sub ScopeDemo2() Range("A2") = strCollege End Sub
Sub ScopeDemo3() Range("A3") = strCollege End Sub
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
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.
Sub StaticDemo() Static iCount As Integer iCount = iCount + 1 MsgBox (iCount) End Sub
Result after first run:
Result after second run:
The Static Variable
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.
If you really want to learn VBA, grab a copy of our Ultimate VBA Training Bundle before reading our next tutorial. We specifically created these cheat sheets to help you get the most out of our upcoming lessons. Together, the set has over 200 practical tips covering the 125 most important topics in Excel VBA. We also stuffed it with 140 helpful macro examples.