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 sub procedure every time. But by declaring one variable we can use it in all the macros in the same module as well in 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 sub procedure by using the word “Dim”.
Look at the above image I have declared the variable “k” as integer inside the sub procedure Global_Example1.
If we use this variable inside this Sub procedure at any point of time. However, I cannot use this variable in another sub procedure either in the same class module or in another module.
As shown in the above image the variable “k” which is declared in the subprocedure Global_Example1 cannot be used in the Sub procedure Global_Example2.
4.6 (247 ratings)
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.
How to Declare Global Variable in VBA?
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. In order 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 declared the variable “MyNumber” as Integer in Module 1.
Once the variable 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, variable “MyNumber” which is 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 interesting thing is we can declare the variable in any one of the modules and use for all the Sub procedures in all the modules of the same VBA Project.
In order 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 word “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 by 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 macro runs with a global variable’s value of 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 –