VBA Public Variables

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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 and different modules. So, when one declares the variables 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 subprocedureVBA SubprocedureSUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read more, and inside them, we declare our variables. It is the common practice we all have done until this article.

You can download this VBA Public Variables Excel Template here – VBA Public Variables Excel Template

Every time we write a new subprocedure, we declare new variables with data types assigned to them. But today, we will say goodbye to repetitive variables across subprocedures.

Let us recollect the old style. Below is the code we have written with a single variable.

Declare VBA Public Variables 1

In the sub procedure “Public_Variable,” we have declared this variable. Now, we cannot use any of the other modules.

Declare VBA Public Variables 1-1

Now, in the subprocedure “Public_Variable1,” we cannot use the variable “Var1,” which one can declare in the first subprocedure “Public_Variable.” It is the limitation of declaring variables inside the subprocedures.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

#1 – Module Level Variables

As we all know, we write Macros in modules. We can insert several 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. 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 sub procedures 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.

Module Level Variables 1

As we can see in the above image, we have declared two variables before we start any macro in the module. So now, we can use these two variables in any Macros in this module.

Inside the sub procedure, start typing the variable name. You can see the IntelliSense list will show up variables names.

Module Level Variables 1-1

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, we will insert one more module and write a new Macro.

Module Level Variables 1-2

In Module2, we 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 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.

Declare Variables use them Publicly 1

We have traditionally used the “DIM” word to declare these variables.

When we use only the “DIM” word, it is 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.

Declare Variables use them Publicly 1-1

We have used the word “Global” to make the variable declaration public. You can also use the word “Public.”

Declare Variables use them Publicly 1-2

So, by using the words “Global” and “Public,” we can declare variables that we can use 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 start 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 confusion.

Recommended Articles

This article has been a guide to VBA Public Variable. Here, we discuss how to declare a public variable in VBA and use it, along with examples and download templates. Below are some useful Excel articles related to VBA: –