Excel VBA Declare Global Variable (2 Easy Ways) 

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of declaring a global variable using VBA in Excel


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.

Dim as a global variable

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

Accessing global variables from different sub


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

Accessing global variable from a different module in Excel VBA


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

Entering a local variable in vba

  • 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

Entering a local variable in vba

  • 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

Having access to global variables but not to local variables


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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo