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 subroutines/functions. When the procedure end, VBA frees the memory and the variable is no longer visible.
Example 1:
|
1 2 3 4 |
Sub procedureLevel1() Dim myVar As Integer myVar = 5 End Sub |
|
1 2 3 4 |
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:
|
1 2 3 4 5 6 7 8 9 10 |
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 the 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.
|
1 |
Public myVar As Integer |