How to Declare a Global Variable in VBAby Jaime Avelar
Declaring global variables in your VBA application can facilitate the sharing of information among different code modules. In a VBA code module, variables can have different scopes, such as procedure-level, module-level, and global-level. The declaration for each variable is different, depending on its scope. Variables should always be defined with the smallest scope possible to avoid adding complexity to the code. Define your global variables in one module only to quickly get to them when you need to.
Launch Microsoft Excel, click the "Developer" tab, and click "Visual Basic." Click the "Insert" menu, and click "Module" to insert a new code module.
Add the following code to declare a global variable: Public myGlobalVar As String
Click the "Insert" menu and click "Module" to insert a second code module. Add the following code to give the global variable a value: Public Sub defineVal() myGlobalVar = "this is a global variable" End Sub
Click the "Insert" menu and click "Module" to insert a third code module. Add the following code, call the sub-procedure that gives the variable a value, and display the variable value through a message box: Private Sub showGlobalVar() Call defineVal MsgBox (myGlobalVar) End Sub
Click inside the "showGlobalVar()" sub-procedure, and press "F5" to run the program.