VBA Global Variables

Declaring a variable seems pretty simple. But to have good hands-on experience, we need to understand the scope of those variables. We often declare the variables for each macro inside the subprocedure. But by declaring one variable, we can use it in all the macros in the same module and the other modules of the current VBA project. This article will show you how to declare global variables in Excel VBA.

VBA global variables are variables declared before any macro in the module starts. When the variables are declared using either "Public" or "Global," it becomes a global variable.

Sub Procedure Variables Cannot Use Anywhere.

We usually declare the variable inside the subroutine in VBA by using the word “Dim.”

Look at the above image. We have declared the variable "k" as an integer inside the Sub procedure Global_Example1.

Suppose we use this variable inside this Sub procedure at any time. However, we cannot use this variable in another Sub procedure either in the same class module in VBA or another module.

As shown in the above image, the variable "k," declared in the Sub procedure Global_Example1, cannot be used in the Sub procedure Global_Example2.

Similarly, variable "j" declared in the Sub procedure Global_Example2 cannot be used in the Sub procedure Global_Example1 even though both are in the same module.

The following are the ways to declare a global variable in Excel VBA.

#1 - Module Variables can be used in any Sub Procedure in the Same Module

As we have seen, we cannot use the Sub procedure variables in any of the modules. Therefore, to make them available for all the Sub procedures in the same module, we need to declare the variables at the top of the module.

In the above image, we have declared the variable at the start of the module only. I have expressed the variable "MyNumber" as an Integer in Module 1.

Once declared the variable at the top of the module, we can use the same variable for all the other Sub procedures in the same module. In this case, we can use the variable "MyNumber" for all the Sub procedures in Module 1.

We cannot use them in any of the other modules. In this case, the variable "MyNumber," declared in Module 1, cannot be used in Module 2.

 #2 - Global Variables can be used in any Sub Procedure and also in any Module

We have seen two kinds of variable declaration and their scope while using. The exciting thing is we can declare the variable in any one of the modules and use it for all the Sub procedures in all the modules of the same VBA Project.

To make the variable available for all the Sub procedures across all the modules, we need to declare the variable at the top of the module not by using the word “Dim” but by using the name “Public” or “Global.”

In the above image, we have used the word "Public" to declare the variable instead of our veteran word "Dim."

In the above screenshot, we have declared the variable in Module 1. However, we have two more modules, Module 2 and Module 3.

Since we have declared the variable using the word "Public" at the top of the module, now we can access these variables in any Sub procedure across any module of the same workbook.

Not only “Public,” but we can also use the word “Global” to declare the variable.

"Global" and "Public" are the keywords to declare the variable and make them available across modules in VBA.

• Once the excel macro runs with a global variable's value, the variable is the same across all the Sub procedures.
• It is better to maintain a particular module to declare global variables in VBA and have all the variables in one module.
• The only way we can reset the variable's value is by resetting the macro code by pressing the stop button.

This article has been a guide to VBA Global Variables. Here, we learn how to declare global variables in Excel VBA, practical examples, and a downloadable template. Below you can find some useful Excel VBA articles: -

• VBA Exit Sub
• VBA ByRef Argument Type Mismatch
• VBA Option Explicit
• VBA Long Data Type