Excel VBA declare Global Variable and variable scope

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:

Function, Sub, Method, Property

Difference between Dim and Set

Scope of local variable / global variable / private variable

Procedure Level

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

Module Level

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

Project Level

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.

Destroy Variable

There is not way to destroy / uninitialize a variable inside the Procedure. It can only be destroy when

1. Variable is out of scope. For example, local variable will end when it exists the Procedure
2. Reset the VBA Project
   
3. Use End Statement in the Procedure, but this will also end the subsequent Procedure.

   Public Sub Procedure1()
       variableA = 1
       End
   End Sub