Using Excel VBA to Declare a Global Variable - 2 Methods

This is an overview.

Download Practice Workbook

Download the practice workbook.

Declare Global Variable.xlsm

Method 1 – “Dim” for Global Variables in the Same Module

Launch VBA. Click here to see how to launch VBA and insert a Module in Excel.
Declare the global variable inside the module using “Dim“. Place the variable declaration outside any particular subroutine or function.

Option Explicit
Dim x As Integer

The global variable can have a value assigned to it at any point in your code in the same module. Consider this:

Sub GlobalVar()
x = 440
MsgBox x
End Sub

The global variable “x” is assigned the value “440” inside the “GlobalVar” subroutine.

By running the “GlobalVar” subroutine, you can run the code. The global variable “x” will be given the value 440, and the “GlobalVar2” subroutine will display a message box containing that value.

Option Explicit
Dim x As Integer
Sub GlobalVar()
x = 440
Call GlobalVar2
End Sub
Sub GlobalVar2()
MsgBox x
End Sub

Using “Dim“, you declare the global variable “x” and show how it can be used in different subroutines in the same module.

Method 2 – “Public” or ” Global” for Global Variables in Different Modules of a Workbook

Enter the following 3 VBA codes in three different modules. Here, Module 2.

Global y As String

In Module 3, the following VBA code will be displayed..

Global z As Integer

In Module 4 if you enter the following code, you will get the message “The number is 10 ”

Option Explicit
Sub AccessGlobalVariables()
    y = "The number is"
    z = 10
    MsgBox y & " " & z
End Sub

Local Vs Global Variable in Excel VBA

Enter the following VBA code in any one of your modules. Here, module 5.

Module5:

Sub LocalVariables()
    Dim x As Integer ' Declare a local variable
    x = 10 ' Assign a value to the local variable
    MsgBox "Local variable x: " & x ' Display the value of the local variable
End Sub

Enter the following code in module 6. Here, x is declared as a global integer and assigned a value of 20.

Module6:

Option Explicit
Global x As Integer
Sub GlobalVariableExample()
    x = 20 ' Assign a value to the global variable
End Sub

After running the code, in module 7, a message box is showing the value of x , declared in the previous module as a global variable.

Module7:

Option Explicit
Sub AnotherProcedure()
    Call GlobalVariableExample
    MsgBox "Global variable x =" & x ' Access the global variable declared in a different module
End Sub