Excel: Variable Scope

In VBA, there are three different scopes of variables: procedure level, module level and public level. Each scope determines the place where the variable is visible. 

Procedure level

When you declare a variable inside a subroutine or a function, it will only be visible in those subroutine/function. When the procedure end, VBA frees the memory and the variable is no longer visible.

Example 1:

Sub procedureLevel1()
Dim myVar As Integer
myVar = 5
End Sub
Sub procedureLevel2()
Dim myVar As Double
myVar = 5.5
End Sub

In the procedureLevel1 subroutine, the myVar variable will only be visible inside that subroutine and will be treated as Integer.

In the procedureLevel2, myVar will be treated as Double and will only be visible inside that subroutine.

Module level

If you want to create the variable and use it inside multiple procedures in the same module, you need to declare it at the module level, at the beginning of your code, before the first use in a procedure. 

Example 2:

Dim myVar As Integer
myVar = 4

Sub moduleLevel1()
myVar = 5
End Sub

Sub moduleLevel2()
myVar = 6
End Sub

At the beginning of the code there is the myVar variable, declared as Integer. This variable is visible inside the module. That means that the variable in moduleLevel1 and moduleLevel2 is the same variable. 

CAUTION

You cannot assign a value to a variable at this level. You have to do it inside procedures. 

Public level

At this level your variables will be visible to any procedures, even those that are present inside other modules in the same workbook. To create such a variable you need to declare it the same way as you did at the module level, but this time add the Public word instead of Dim.

Public myVar As Integer