VBA Public Variables

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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.”

Table of contents

How to Declare Public Variables in VBA?

Usually, we start the VBA subprocedure, and inside them, we declare our variables. It is the common practice we all have done until this article.

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.

#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.