Using Excel VBA to Declare a Global Variable – 2 Methods

This is an overview.

---

Download Practice Workbook

Download the practice workbook.

---

 

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

 

 

Get FREE Advanced Excel Exercises with Solutions!