Public Variables in VBA
“Public Variables” in VBA, as the name suggests are variables that are declared to use publicly for all the macros we write in the same module as well as in different modules as well. So, when the variables are declared at the start of any macro are called “Public Variables” or “Global Variables”.
How to Declare Public Variables in VBA?
Usually, we start the VBA subprocedure and inside the subprocedure, we declare our variables. This is the common practice we all have done until this article.
Every time we write a new subprocedure we declare fresh variables with data types assigned to them. But today we will say goodbye to repetitive variables across subprocedures.
Let’s recollect the old style, below is the code I have written with a single variable.
In sub procedure “Public_Variable” I have declared this variable. Now I cannot use any of the other modules.
Now in the subprocedure “Public_Variable1” we cannot use the variable “Var1” which was declared in the first subprocedure “Public_Variable”. This is the limitation of declaring variables inside the subprocedures.
#1 – Module Level Variables
As we all know we write macros in modules, we can insert a number of modules. We can declare two kinds of “Public Variables” in VBA, one is to use the variables for all the subprocedures in the same module and the second one is to use the variables for all the subprocedures across all modules.
First, we will see declaring public variables in VBA at the module level.
To use the variables for all the subprocedures in the same module we need to declare the variables at the top of the module before we start any macros.
Below is the example screenshot for your understanding.
As we can see in the above image, I have declared two variables before I start any macro in the module. Now, these two variables can be used in any number of macros in this module.
Inside the subprocedure starts typing the variable name you can see IntelliSense list will show up variables names.
Now we can use these variables in all the macros we write in “Module1”.
These variables are limited to use in this module only. For example, now I will insert one more module and write a new macro.
In Module2 I cannot use those variables we have declared in “Module1”.
So, how can we make these variables public in VBA to use across all modules and across all sub procedures?
#2 – Declare Variables use them Publicly
Go back to “Module1” in this module we have declared variables before we start writing the way of the macro and also what world we have used to declare those variables.
Our traditional way of using the “DIM” word we have declared these variables.
When we use only the “DIM” word it only limited to be used across all macros but in the same module.
Instead of the word “DIM” we need to use the word either “PUBLIC” or “GLOBAL” to make them available to use across all modules of macros.
I have used the word “Global” to make the variable declaration public. You can also use the word “Public” as well.
So, by using the words “Global” and “Public” we can declare variables that can be used for all the macros across modules.
Things to Remember
- It is a good practice to declare variables publicly but needs ample experience before declaring them.
- Once the macros are started to run across the macro’s value of the variable will be the same.
- Assign the particular value to the variable inside the specific macro only to avoid any sort of confusion.
This has been a guide to VBA Public Variable. Here we discuss how to declare a Public variable in VBA and how to use it along with examples and download templates. Below are some useful excel articles related to VBA –