VBA Global Variables

What are Global Variables in Excel VBA?

VBA Global variables are those variables that are declared outside of any procedures, functions, or modules and can be accessed from anywhere in the program. Once declared, VBA Global Variables can be accessed and modified from any program part, including all the modules and procedures. It can be helpful when you need to store a value that will be used throughout your program rather than passing it as an argument between procedures or functions.

However, using global variables with caution is essential, as they can make your code more difficult to debug and maintain. In addition, if a global variable is modified in one part of the program, it can affect the behavior of other program parts that rely on its value.

Consider the following code:

It will print the value of “Example” as 200 in the Immediate tab when DeclareSum() is run, and when FindDifference() is run, if the input is given as 22, the value of Example now is 178.

What are Global Variables in Excel VBA?

The VBA Global variable is declared outside of procedures, functions, or modules. However, it can be accessed from anywhere in the program.
The syntax for the global variable is Global variable-name as Datatype.
When using global variables, it’s important to ensure that they are initialized and that their values are updated correctly, as they can be accessed and modified from multiple modules and procedures.
It’s also essential to avoid naming conflicts between different types of variables and to use descriptive variable names to improve code readability and maintainability.

How to Declare Global Variables in VBA Excel?

Steps to declare global variables in VBA are as follows –

Go to the “Developer” section in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and subprocedures. Then, click the “Insert” and “Module” buttons to create a new module or blank page.

To access global variables, use the ‘Global’ keyword followed by the variable name and variable data type in VBA. e.g., Global Example As Integer

It will declare a global variable named Example of data type Integer. You can then use this variable in any part of your code by referring to it by its name (Example).
Declare a VBA global variable, and print a concatenated string using a global variable. To access VBA global variables, we must first declare them as shown.
Create a sub-procedure to display the example mentioned above.
Initialize a value for mystr and concatenate it with another string (custom) to update its original value.
Print the variable, mystr, to check its value using the below statement.

Here, the variable Example is declared as a global variable, due to which there is access for VBA global variables across modules. The output will be printed in the Immediate tab in the VBA editor, as seen below.

• Code:

Global mystr As String
Sub PrintString()
    mystr = “Hello “
    mystr1 = InputBox(“Enter a string”)
    mystr = mystr + mystr1
    Debug.Print mystr
End Sub
Run the code by pressing the F5 or Run button. The output is shown below. Here, the string “World” is the input for the custom string. When both are concatenated, we get the result:

Module Variables

Module-level variables can be used in any sub-procedure within the same module.

When you declare a variable at the module level, it can be accessed and modified by any procedure within the same module. It means you can declare the variable in one procedure and use it in another within the same module without passing it as an argument.

However, it is essential to note that module-level variables are not accessible outside the module in which they are declared. Consider using a global variable instead if you need to use a variable across multiple modules.

To declare a module-level variable, you can use the Dim keyword followed by the variable name and its data type at the top of the module, outside of any procedures or functions. For example:

Dim myDate as Date

It will declare a module-level variable called ‘myDate’ of the datatype ‘Date.’ You can use this variable in any sub-procedure within the same module by referring to it by name (myDate).

Example

Consider an example where you want to print the current date. Excel has an inbuilt function or datatype called “Date,” which does that.

Step 1: To start, create a Sub procedure PrintTheDate() to print today’s date.

VBA Global Variables - Example 1 - Step 1

Step 2: Declare a variable myDate in the datatype Date.

Step 3: Initialize a value for the variable myDate by assigning the ‘Date’ to it. The ‘Date’ function in Excel VBA prints the current date.

Step 4: Print myDate using the command Debug.Print. It will print the value in the ‘Immediate’ tab in Excel VBA.

Step 5: Press the green triangle on the VBA toolbar to execute the function.

Step 6: The output is shown below.

VBA Global Variables - Example 1 - Step 6

Code:

Sub PrintTheDate()
Dim myDate As Date
myDate = Date
Debug.Print myDate
End Sub

Global Variables

Global variables can be used in any sub-procedure within the same module and in any module within the same Excel VBA project.

When you declare a variable as Public at the top of your code outside of any procedures or functions, it can be accessed and modified by any procedure or module within the same project. You can declare the variable in one module and access the VBA global variables between modules.

However, as mentioned earlier, using global variables with caution is essential. When you use global variables, you need to be careful about their scope and ensure that they are not being modified unexpectedly by other parts of the program. In addition, it can make your code harder to debug and maintain.

To declare a global variable, use the ‘Public’ keyword followed by the variable name and its data type at the top of your code, outside of any procedures or functions.

Example

Consider a table consisting of sales data you want to update consistently. In this case, we can utilize VBA Global variables and print them on the cell to change the values dynamically.

Step 1: Declare a global variable totalSales of datatype Integer.

Step 2: Initialize a sub procedure CalculateSales() which adds the values of the first 3 rows.

Step 3: Initialize a sub-procedure CalculateSales(), which adds the values of the first three rows.

Global Variables in Excel VBA - Example 2 - Step 3

Step 4: Create another sub-procedure to print the totalSales value.

It will print the updated totalSales value as the number of values increases. For example, if the code is run, it will present the value of totalSales as 12000.

Step 5: To update totalSales, initialize another sub-procedure called UpdateSales() which adds the values of the last three rows of the table (A4 to A6), which we have added later.

Step 6: Assign or update the value of totalSales by adding the values of cells A4:A6 using the VBA Range() in Excel.

Step 7: Now click the Insert button and open another module to call the global variable. In the module, create another sub-procedure CallVariable()

Global Variables in Excel VBA - Example 2 - Step 7a

Step 8: Print the totalSales value in cell B2. To execute the function, press the green triangle on the VBA toolbar.

Hovering your cursor over the respective sub-procedures is important to print the desired totalSales value.

Step 9: The output is shown below.

Code:

Global totalSales As Integer
Sub CalculateSales()
totalSales = Range(“A1”).Value + Range(“A2”).Value + Range(“A3”).Value
End Sub

Sub PrintSales()
Debug.Print “Total sales for the month: $” & totalSales
End Sub

Sub UpdateSales()
totalSales = Range(“A4”).Value + Range(“A5”).Value + Range(“A6”).Value
End Sub

In the next module:

Sub Callvariable()
Range(“B2”).Value = totalSales
End Sub

Important Things to Note

Scope: Global variables have a broader scope than local variables so that they can be accessed and modified from any module or procedure in the project. It’s essential to be aware of where your global variables are being used and to ensure they are not being modified unexpectedly by other program parts.

Naming conventions: To avoid naming conflicts with other variables in your code, using a naming convention for your global variables is a good practice. For example, you can prefix them with “g_” or “global_” to clarify that they are global variables.

Initialization: Global variables are initialized to their default values when loading the VBA module. For numeric data types, this is usually 0, and for string data types, it is an empty string (” “). If you need to initialize your global variables with specific values, you can assign them values in a sub-procedure or function that runs when the module is loaded.

Global variables are stored in a separate “Global” module, automatically created when the first Global variable is declared in the project.

Frequently Asked Questions (FAQs)

1. Why are VBA Global Variables not working?

• Scope: Make sure that the global variable is declared in the correct module, and has a Public scope.
• Initialization: If you rely on the global variable to have a specific value, ensure it is properly initialized.
• Name conflicts: If other variables have the same name as your global variable, it can cause naming conflicts and make it difficult to access the variable.

2. Are variables in VBA global or public?

• Global variables are declared with the “Global” keyword and have a global scope, which means they can be accessed and modified from any module or procedure in the project.
• Public variables are declared with the “Public” keyword and have a module-level scope, which means they can be accessed and modified from any procedure within the same module.
• It’s important to note that public variables are not necessarily global, as they are limited to the module where they are declared.

3. What’s the difference between macro and VBA global variables?

• A macro is a series of recorded commands that automate a specific task or set of tasks in an application, such as Microsoft Excel. A macro can be triggered by a user action or by a specific event, such as opening or closing a workbook.
• A global variable, on the other hand, is a variable that is declared at the module level and has a scope that extends beyond the module or procedure where it is declared.
• The main difference between a macro and a global variable is that a macro is a set of predefined commands that automate a specific task. In contrast, a global variable is a variable that stores a value or object that can be accessed and modified from multiple modules or procedures.

4. What is the difference between public, private, and global variables in VBA?

• Public Variables: Public variables are declared using the “Public” keyword and can be accessed and modified from any module or procedure within the entire project.
• Private Variables: Private variables are declared using the “Private” keyword and can only be accessed and modified from within the module where they are declared.
• Global Variables: Global variables are declared using the “Global” keyword and can be accessed and modified from any module or procedure within the entire project, just like Public variables.

Download Template

This article must be helpful to understand the VBA Global Variables, with its formula and examples. You can download the template here to use it instantly.

VBA Global Variables Download

What are Global Variables in Excel VBA?

Table of contents

Key Takeaways