Excel VBA Declare Global Variable (2 Easy Ways) 

We’ll look at the step-by-step procedure for declaring global variables in Excel VBA in this blog post. We’ll go over the various approaches, like using the “Public” keyword or declaring variables at the module level, and give usage examples. Learning the craft of declaring global variables will help you optimize your VBA code and produce Excel solutions that are more dynamic and durable. In the end, you’ll be able to understand clearly the topic Excel VBA Declare Global Variable.

Download Practice Workbook

You can download the practice workbook from the following download button.

What Are VBA Global Variables in Excel?

Global variables in Excel VBA are variables that are declared at the module level and can be accessed from any area of the workbook, including other modules, worksheets, and user forms. Their scope encompasses the entire workbook.

Excel VBA to Declare Global Variable: 2 Simple Ways

You will be taken step-by-step through the process of declaring global variables and usage examples will be given. Gaining a thorough understanding of global variables in Excel VBA will help you write more flexible, effective code that produces more durable and dynamic Excel solutions.

1. “Dim” Keyword for Global Variable in Same Module

Based on your requirements and how you prefer to access your variable, you will choose the global variable declaration processes. In the following steps, you will see the usage of Dim to declare a global variable.

• First, you have to launch the VBA code. Click here to see how to launch VBA and insert a Module in Microsoft Excel.
• Declare the global variable inside the module by using the “Dim” keyword. Place the variable declaration outside any particular subroutine or function, at the module level.

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” in this example is given 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 the “Dim” keyword, you declare the global variable “x” and show how it can be used in different subroutines of the same module.

2. Global Variable in Different Modules of a Workbook

If you want to avail your variable throughout the modules, you need to declare the variables outside the subroutine and start the declaration using Public or Global.

• You need to enter the following 3 VBA codes in three different modules. As in the following example I have entered the following VBA code in Module 2.

Global y As String 

• And in Module 3, I have the following VBA code.

Global z As Integer 

• Now in Module 4 if I 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

Using “Dim“, local variables are declared inside a particular procedure or function.

The only place they can be accessed and changed is inside the procedure or function where they are declared, giving them a constrained scope. In order to temporarily store data while a particular procedure or function is being executed, local variables are frequently used. Now in the following stage, we will find a difference between Local and Global variables.

• The following 3 VBA code is inserted in 3 different modules. After running the last VBA code the message box will show the value of x=20 as that variable was declared as a global one.
• Enter the following VBA code in any one of your modules. For demonstration, I have used 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 in x.

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 that is 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

Frequently Asked Questions (FAQs)

1. What are the benefits of using global variables in Excel VBA?

When you want to share data between sections of your workbook, global variables are helpful. They offer a practical means of storing and gaining access to data that is necessary for several processes or modules.

2. When using global variables in Excel VBA, are there any considerations or best practices to keep in mind?

Global variables are useful, but they should only be used sparingly. Observe the following best practices:

• Declare global variables outside of any particular function or subroutine at the module level.
• To make your code easier to read, give your global variables meaningful names.
• Keep your global variables’ scope in mind and only access and modify them when necessary.
• To enforce explicit variable declaration and detect any typos, use the “Option Explicit” statement.

3. How are global variables different from local variables?

Compared to local variables, global variables have a wider range of applications. Global variables are declared at the module level and can be accessed from anywhere in the workbook, as opposed to local variables, which are declared within a particular procedure or function and are only accessible within that scope.

Conclusion

Finally, in Excel VBA to declare global variables enables the sharing and manipulation of data among various modules, processes, worksheets, or user forms inside your workbook. Global variables allow for the dynamic updating of shared data and the information flow between components. Please post your comments on Exceldemy’s blog or forums if you have any queries.