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.
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.
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.
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.
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.
You cannot assign a value to a variable at this level. You have to do it inside procedures.
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