How to Define VBA Global Array in Excel (2 Easy Ways)

In this article, we will explore the process of defining a global array in Excel VBA. We’ll discuss the concept of module-level variables, their scope, and how they can be used to create a global-like array accessible throughout the module or project. Using module-level variables, you may store and manipulate data in an array that can be accessed and modified by numerous processes in your VBA project.

Whether you’re working on Excel automation, creating custom functions, or building complex macros, understanding how to define a global array in VBA will allow you to efficiently manage and share data across different parts of your codebase. So, let’s dive in and learn how to harness the power of global arrays in VBA programming.

What Is Global Array in Excel?

In VBA (Visual Basic for Applications), a global array is an array that is declared at the module level, outside any functions. This means that the array can be accessed and modified by any subroutine or function within the same module.

Understand the Scope of Variables in Excel VBA

In VBA (Visual Basic for Applications), There are three ways of defining scopes to variables. The terms “Procedure-Level”, “Module-Level (Private)“, and “Global Level (Public)” refer to different levels of variable scope. Here’s what they mean:

1. Procedure-Level: Variables declared within a procedure (a function or a subroutine) have a procedure-level scope. These variables can only be accessed within the procedure in which they are declared. Once the procedure is exited, the variables are no longer accessible. Procedure-level variables are typically used for the temporary storage of data or intermediate calculations within a specific procedure.

2. Module-Level (Private): Variables declared at the module level with the “Private” keyword have module-level (private) scope. These variables can be accessed from any procedure within the same module, but not from procedures in other modules. Module-level variables are typically used for values that need to be shared across multiple procedures within the same module, but not with other modules.

3. Global Level (Public): Variables declared at the module level with the “Public” keyword have a global level (public) scope. These variables can be accessed from any procedure within the same module or any other module in the same project. Global-level variables are typically used for values that need to be shared across multiple procedures in different modules within the same project.

How to Declare VBA Global Array in Excel: 2 Easy Ways

1. Using “Public” Sub to Declare a Global Array in Excel

To make the variable available to all Sub methods throughout all modules, we must declare it at the top of the module using the name “Public” or “Global” rather than the word “Dim.”

To declare a global variable in VBA, you need to do the following steps:

First, see launch VBA editor and insert a Module in Microsoft Excel.
After launching the VBA and inserting a module, In the code window, outside any procedure, declare the variable using the “Public” keyword. For example, to declare a global integer variable named “myGlobalVar“, you would write:

Public myGlobalVar As Integer
Sub myGlobal_example()
A = "SOFTEKO"
MsgBox A
End Sub

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

The message box with the message “SOFTEKO” will now appear, as illustrated below.

Read More: How to Declare Array in Excel VBA

2. Using Keyword “Global” to Declare a Global Array in VBA

In the above, we used “Public” to declare a global array. But now we are going to use the word “Global” to declare a public array in this section. Let’s see whether it works or not.

Here’s a simple code on how to declare:

Global myGlobalVar As String
Sub myGlobal_example_1()
End Sub

Now, we are going to show you an example of how to use a global array in VBA code.

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

For that, Launch VBA on your Microsoft Excel and insert a Module.
Then enter the following VBA code:

Option Explicit
Global A As String
Sub Global_example()
A = "ExcelDemy1"
MsgBox A
End Sub
Sub Global_example_1()
A = "ExcelDemy2"
MsgBox A
End Sub

To see the output, run the code by using the F5 key or clicking on the play button.

We will get the output as “ExcelDemy1” as shown in the image below. It is because we had kept our cursor in the first subcategory.

Place the cursor anywhere in the second subcategory and execute the code once more.

The message box with the message “ExcelDemy2” will now appear, as illustrated below.

Since this variable is a Global level variable, you can also use it in Module 2 or any other Module.

Frequently Asked Questions

1. Where do I put global variables in VBA?

You can use a global variable everywhere in your code, including modules, functions, subroutines, and classes. We declare them in the Declarations Section, under the Options Explicit statement, and using the keyword “Global”.

2. How do I create a dynamic array in Excel VBA?

First, declare an array by name.
The items count then left the parenthesis unfilled.
Use the ReDim statement now.
Finally, provide the number of elements you want to add to the array.

Here is an example:

Dim ABC() As String
ReDim ABC(10)

3. Is VBA static or dynamic?

VBA is a static programming language. This implies that you must define variable types and sizes and allocate them ahead of time. In VBA, we normally declare variables having certain data types, such as Integer, String, or Boolean, and their memory allocation is determined statically at compilation or runtime.

VBA does, however, provide limited support for dynamic behavior via features such as dynamic arrays and the ReDim statement. Dynamic arrays allow you to resize an array at runtime, giving you more flexibility in dealing with variable data sizes. You can use ReDim to dynamically alter the size of an array as needed.

Things to Remember

When the Excel macro runs with the value of a global variable, the variable is the same throughout all Sub operations.
It is preferable to keep a separate module for declaring global variables in VBA rather than having all variables in one module.
You can reset the variable’s value only by resetting the macro code by pressing the stop button.
A Global variable gives the output of the code where we have kept the cursor. It will not run the complete code in one go giving all the output one by one.

Download Practice Workbook

VBA Global Array.xlsm

Conclusion

In essence, defining a global array in VBA can significantly enhance the flexibility and efficiency of your code. While VBA does not have built-in support for truly global variables, you can achieve similar functionality by using module-level variables. By declaring an array at the module level, you can create a global-like array that you can access and modify by various procedures within the module or project. If you have any questions or comments, please leave them in the comments box below.

Get FREE Advanced Excel Exercises with Solutions!