This Excel VBA tutorial explains how to declare private variable, global variable / public variable in VBA, and explain the scope of variable.
You may also want to read:
Scope of local variable / global variable / private variable
When you declare a variable inside a Sub Procedure or Function (local variable), the variable can only be used within them.
For example, in Sub Procedure1, you have declared variableA = 1. In Sub Procedure2, if you use variableA, it will have value 0 (uninitiated).
Public Sub Procedure1() Dim variableA as integer variableA = 1 End Sub Public Sub Procedure2() MsgBox (variableA) 'return no value End Sub
If you want to use variableA for all Procedures within the same Module, you can declare it as Private Variable on top of the Module. The value of Private variable will preserve until Workbook is closed or reset the Project. Because variableA is not initialized until Procedure1 is run, you have to run Procedure1 using Call in Procedure2.
Private variableA as Integer Public Sub Procedure1() variableA = 1 End Sub Public Sub Procedure2() Call Procedure1 MsgBox (variableA) 'return 1 End Sub
If you want to use variableA in all Modules, you can declare it as Global Variable. Using Public or Global in declaration on top of any Module. The value of Global variable will preserve until Workbook is closed or reset the Project.
Public variableA as Integer Public Sub Procedure1() variableA = 1 End Sub Public Sub Procedure2() Call Procedure1 MsgBox (variableA) 'return 1 End Sub
Managing Global Variable
If you have created many Modules, and in each Module you have defined Global Variables, it is better to create a Module specifically for declaration of Global Variable, so that you can easily follow the underlying value of the global variables.
It is also a good practice to name all Global Variable with the same prefix, for example, you can call it global_variableA, or g_variableA.
Because you only need to initialize Global Variable once instead of calling them in every Procedure, you can consider initializing them in Workbook_Open Event.
There is not way to destroy / uninitialize a variable inside the Procedure. It can only be destroy when
- Variable is out of scope. For example, local variable will end when it exists the Procedure
- Reset the VBA Project
- Use End Statement in the Procedure, but this will also end the subsequent Procedure.
Public Sub Procedure1() variableA = 1 End End Sub