Some functions are defined inside a function and are used within the functions and some variables are defined outside of the functions and are used by all the functions and such variables are used as Global Variables, for example, the variables declared under the sub-function are known as Global Variables.
Global Variable in Excel VBA
Declaring a variable seems pretty simple, but to have good hands-on them, we need to understand the scope of those variables. Often we declare the variables for each macro inside the subprocedure every time. 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. In this article, we will show you how to declare Global Variables in Excel VBA.
What are Global Variables in Excel VBA?
VBA Global Variables are variables which are declared before the start of any macro in the module. When the variables are declared by using either “Public” or “Global,” it becomes “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. I have declared the variable “k” as integer inside the subprocedure Global_Example1.
Suppose we use this variable inside this Sub procedure at any point in time. However, I 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 subprocedure Global_Example1, cannot be used in the Sub procedure Global_Example2.
Similarly, variable “j” declared in the subprocedure Global_Example2 cannot be used in the Sub procedure Global_Example1 even though both the subprocedures are in the same module.
4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
How to Declare Global Variable in VBA?
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 subprocedure variables in any of the modules. To make them available for all the Sub procedure in the same module, we need to declare the variables at the top of the module.
In the above image, I have declared the variable at the start of the module only. I have expressed the variable “MyNumber” as Integer in Module 1.
Once the variable is declared 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.
The problem is 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
Now we have seen two kinds of variable declaration and the scope of them 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, you can see I have used the word “Public” to declare the variable instead of our veteran word “Dim.”
In the above screenshot, I have declared the variable in Module 1. I have two more modules, named Module 2 & Module 3.
Since I have declared the variable using the word “Public” at the top of the module, now I 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 & Public are the two keywords to declare the variable and make them available across modules in VBA.
Things to Remember
- 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 value of the variable is by resetting the macro code by pressing the stop button.
This has been a guide to VBA Global Variables. Here we learn how to declare global variables in Excel VBA along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –