How to Define a Global Array in Excel VBA - 2 Methods

This is an overview.

Overview image of how to define a global array in VBA (Feature Image)

What Is a 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. The array can be accessed and modified by any subroutine or function within the same module.

The Scope of Variables in Excel VBA

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

1. Procedure-Level: Variables declared within a procedure (a function or a subroutine). 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.

2. Module-Level (Private): Variables declared at the module level with the “Private” keyword. These variables can be accessed from any procedure within the same module, but not from procedures in other modules.

3. Global Level (Public): Variables declared at the module level with the “Public” keyword. These variables can be accessed from any procedure within the same module or any other module in the same project.

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

Launch the VBA editor and insert a Module.
Declare the variable using the “Public” keyword. For example, to declare a global integer variable “myGlobalVar“, enter:

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

Run the code by pressing F5 or by clicking the play button to see the result.

Using “Public” to Declare a Global Array

A message box with the message “SOFTEKO” will be displayed:

Outcome after applying code

Read More: How to Declare Array in Excel VBA

Method 2 – Using the Keyword “Global” to Declare a Global Array in VBA

This is the code:

Global myGlobalVar As String
Sub myGlobal_example_1()
End Sub

Using “Global” to Declare a Global Array

Consider the example below:

Assign text to the defined variable A in two subcategories. Here, “ExcelDemy1” and “ExcelDemy2” for variable A in both subcategories as shown below. Choose MsgBox to show the values stored in variable A.

Launch VBA and insert a Module.
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 pressing F5 key or clicking the play button.

VBA global variables

The output is “ExcelDemy1” because the cursor was kept in the first subcategory.

Utilizing the first subcategory of the VBA code

The output of the first portion of the VBA code

Place the cursor anywhere in the second subcategory and run the code again.

Utilizing the first subcategory of the VBA code

The message box displays “ExcelDemy2“.

The output of the second portion of the VBA code

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

Frequently Asked Questions

1. Where do I put global variables in VBA?

You can use a global variable in modules, functions, subroutines, and classes. Declare it in the Declarations Section, under the Options Explicit statement, and use the keyword “Global”.

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

Declare an array by name.
Leave the parenthesis unfilled.
Use the ReDim statement.
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. You must define variable types and sizes and allocate them. In VBA, variables are usually declared as Integer, String, or Boolean, and their memory allocation is determined statically at compilation or runtime.

VBA provides 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.
Keep a separate module to declare global variables in VBA.
You can only reset the value of the variable by resetting the macro code: click stop.

Download Practice Workbook

VBA Global Array.xlsm