How to Declare a Global Variable in VBA

By 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.

Step 1

Launch Microsoft Excel, click the "Developer" tab, and click "Visual Basic." Click the "Insert" menu, and click "Module" to insert a new code module.

Step 2

Add the following code to declare a global variable:

Public myGlobalVar As String

Step 3

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

Step 4

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

Step 5

Click inside the "showGlobalVar()" sub-procedure, and press "F5" to run the program.

References

Writer Bio