This is an overview.
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.
Understanding 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
To make the variable available to all Sub methods throughout all modules, declare it at the top of the module using “Public” or “Global” rather than “Dim.”
To declare a global variable in VBA:
- 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.
A message box with the message “SOFTEKO” will be displayed:
Read More: How to Declare Array in Excel VBA
Method 2 – Using the Keyword “Global” to Declare a Global Array in VBA
Use the word “Global” to declare a public array:
This is the code:
Global myGlobalVar As String
Sub myGlobal_example_1()
End Sub
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.
- The output is “ExcelDemy1” because the cursor was kept in the first subcategory.
- Place the cursor anywhere in the second subcategory and run the code again.
- The message box displays “ExcelDemy2“.
Since this is a Global level variable, you can also use it in Module 2 or in any other Module.
Read More: How to Create an Array in Excel VBA
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
