VBA Global Variables

Excel VBA Global Variables

We usually declare any variable by using the word DIM and this is the best direct method of doing this. But the variables declared using DIM can only be used within the same module. If we try to use this variable defined by using DIM, it will be applicable for the module under which it is being declared. What if we want to use a variable in any module or project? Which cannot be possible if we keep on using DIM to define that variable?

In that case, we have a secondary option by defining the variable by word Global or Public in VBA. VBA Global or Public variables are those variables which are declared at the beginning of subcategory with the help of Option Explicit. Whatever things which we don’t or can’t do under subcategories, they are done before that under Option Explicit.

How to Declare Global Variables in VBA?

Below are the different examples to declare a global variable in Excel using VBA code.

VBA Global Variables – Example #1

In this example, we will see how different subcategories can be used as a single module without using Global or Public word for defining any variable. For this, we would need a module.

• Go to Insert menu and click on Module as shown below.

• Now type the subcategory in any name as shown below.

Code:

Sub Global_Variable()

End Sub

• Now use DIM to define any kind of variable. Here we have chosen alphabet A as String.

Code:

Sub Global_Variable()

  Dim A As String

End Sub

• Now create another subcategory in the same module as shown below.

Code:

Sub Global_Variable()

  Dim A As String

End Sub

Sub Global_Variable6()

End Sub

• Now in the second subcategory define another variable B using DIM as String.

Code:

Sub Global_Variable()

  Dim A As String

End Sub

Sub Global_Variable6()

  Dim B As String

End Sub

As shown above, both the variables A and B defined in both subcategories cannot be used in each other’s region. Those will be applicable in their own subcategory only.

• Now to make this work, write Option Explicit above the first subcategory as shown below.

Code:

Option Explicit

Sub Global_Variable()

End Sub

Sub Global_Variable6()

End Sub

• Now we can define our variable which will be used in both of the subcategories of which we have below. So now consider writing any variable say A as String using DIM.

Code:

Option Explicit

  Dim A As String

Sub Global_Variable()

End Sub

Sub Global_Variable6()

End Sub

As we have defined the variable all the sub-categories will be in that module and this would be applicable to all the code of that module only. And if we try to call the variables defined in this module to some other module, then it would give us the error.

VBA Global Variables – Example #2

In this example, we will see how to use Global or Public word for defining any variable in excel VBA. For this, we will use the code which we have seen in example-1.

We will use below portion of code for Global or Public variable declaration.

Code:

Option Explicit

Sub Global_Variable()

End Sub

Sub Global_Variable6()

End Sub

As we did in example-1 where we have declared the common variable which would be used in both the subcategories, below the Option Explicit. In the example, we will declare the Global Variable below the Option Explicit as well.

As we already have our code ready so we can directly go on declaring variables using Global. Now in the below Option Explicit write Global the same we used to with DIM and select a variable of choice. Here we are choosing alphabet A as shown below.

Code:

Option Explicit

Global A

Sub Global_Variable()

  Dim A As String

End Sub

Sub Global_Variable6()

  Dim B As String

End Sub

Now we choose any type of variable to be it. As we have already used String in the above example, so we would use the same here as well.

Code:

Option Explicit

Global A As String

Sub Global_Variable()

  Dim A As String

End Sub

Sub Global_Variable6()

  Dim B As String

End Sub

This completes our Global variable declaration process. Now we can use this in any module, any project as String only. Before we use it, delete the previously declared variable, then the code would look like as shown below.

Code:

Option Explicit

Global A As String

Sub Global_Variable()

End Sub

Sub Global_Variable6()

End Sub

Now let’s assign some text to defined variable A in both subcategories. We are choosing “Test1” and “Test2” for variable A in both subcategories as shown below. And also we have chosen MsgBox to show the values stored in variable A.

Code:

Option Explicit

Global A As String

Sub Global_Variable()

A = "Test1"
MsgBox A

End Sub

Sub Global_Variable6()

A = "Test2"
MsgBox A

End Sub

Now run the code by pressing the F5 key or by clicking on the play button to see the result.

We will get the message as “Test1” as shown above. It is because we had kept our cursor in the first subcategory.

Now put the cursor anywhere in the second subcategory and run the code again. We will now get the message box with message “Test2” as shown below.

This is how we can create and define a variable once with the help of Global and that can be used in any module, any class and in any project. We can use Public as well in place of Global as shown below. And this will give the same result as we got in using Global.

Code:

Option Explicit

Public A As String

Sub Global_Variable()

A = "Test1"
MsgBox A

End Sub

Sub Global_Variable6()

A = "Test2"
MsgBox A

End Sub

Pros of VBA Global Variable

• It saves time in declaring the multiple variables when we know, we may need to define the same type of variable again in different modules or subcategories.
• By using Global or Public in defining a variable process, our code becomes smaller saving time and spaces.
• It reduces the complexity when we are writing the huge code and may get confused among the use of different variables in different modules or subcategories.

Cons of VBA Global Variable

• If we do any changes to the Global variable then that changes will get implemented in all where it is used causing the problem in the functionality of written code.

Things to Remember

• Global and Public can be used interchangeably with the same functionality.
• If we don’t want to use Global variable then we can follow the process as shown in example-1 for defining the variable. But that would be limited to the same module.
• Global variables can be used in any module, any subcategory, any class or in any project.
• A Global variable gives the output of the code where we have kept the cursor. It will not run the complete code at one go giving all the output one by one.

Recommended Articles

This is a guide to VBA Global Variables. Here we discuss how to declare Global Variables in Excel using VBA code along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA UserForm
2. Solve Equation in Excel
3. VBA Activate Sheet
4. How to Edit Drop Down List in Excel?