VBA Variable Scope and Lifetime

What is Scope? | Procedure Level | Module Level | Project Level | Lifetime

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.

1. Procedure Level
2. Module Level
3. Project Level

3 Levels of Scope

Procedure Level

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

Result:

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.

Module Level

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

Result:

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

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.

Module1

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

Module2

Sub ScopeDemo3()
Range("A3") = strCollege
End Sub

Setup:

Result:

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:

Result:

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.

Sub StaticDemo()
Static iCount As Integer
iCount = iCount + 1
MsgBox (iCount)
End Sub

Result after first run:

Result after second run:

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.

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.