Global Variable in VBA

We will introduce how we can create global variables in VBA. We will also discuss the advantages and disadvantages of using a global variable in VBA.

Global Variable in VBA

While working with variables in VBA, we come across situations in which we may need to use the same variables in multiple functions. But a variable declared inside a function cannot be used outside of that function.

For this, we declare global variables in VBA. Global variables are the variables that are declared at the start of any macro.

These variables are defined outside the function and used in functions or modules. The variables are declared by keyword Public or Global.

We can declare global variables by following the steps shown below.

1. We use the keyword Global or Public.
2. Declare the name of the variable after writing the keyword.
3. To get the list of data types, write as.
4. Select the data type that is involved in a VBA code.

If we need to involve two variables for sub-modules of different times, then it is preferred to define the global variables explicitly. Let’s go through an example in which we will declare the global variable str and use it in 2 different subs, as shown below.

Dim str As String

Sub firstSub()

str = "This is first Sub"
MsgBox (str)

End Sub

Sub secondSub()

str = "This is second Sub"
MsgBox (str)

End Sub

Output:

As we can see from the above example, when our cursor is inside firstSub(), the message from the first sub is displayed, and when our cursor is inside secondSub(), the message from the second sub is displayed.

In this way, we can use the same variable in multiple subs. If we want to introduce global variables among different modules, then we can write our code as shown below.

Dim globalVar As String

Sub firstSub()

globalVar = 10

End Sub

Sub secondSub()

MsgBox (globalVar)

End Sub

Output:

As you can see from the above example, when we run the code from firstSub(), it assigns the value to the global variable. When we run the code from secondSub(), it displays that value.

Now let’s discuss the advantages and disadvantages of using global variables in VBA.

Advantages of Global Variable in VBA

The main advantages of global variables are:

1. These variables are globally accessible since the global variables can be used for all functions and modules of the VBA code.
2. We only need to define global variables once, and then we can use them multiple times in a VBA code.
3. The variables keep us away from the complexity involved in writing lengthy codes or confusion generated by involving variables multiple times.
4. If we declare constant variables, then the consistency of our VBA macro is ensured.
5. A key factor of global variables is easy maintenance.
6. Since variables are to be declared only once, the number of lines in the VBA codes is reduced. Such types of codes are easy to read.

Now let’s discuss the disadvantages of using the global variables in VBA.

Disadvantages of Global Variable in VBA

The disadvantages associated with global variables are:

1. Debugging the code is complicated as variables are defined explicitly.
2. A minor change in any variable at one point implies the change to all points involving the variable and degrades the functionality of the VBA code.
3. The global variable is dependent on the parent module, and we need to redesign each module every time in the case of multiple modules being used within the same VBA code.