Excel VBA Global Array Example: Scope, Setup, and Use

VBA & macros · Updated November 21, 2026

In Excel VBA, declare a shared array with a Public statement at the top of a standard module, outside any procedure. For example, Public gScores() As Long makes the dynamic array available to procedures throughout that workbook’s VBA project; use ReDim in a procedure to allocate its elements before accessing them.

What a global array means in VBA

“Global array” is a common informal name for a module-level array declared with Public. The declaration gives the variable project-wide visibility: other modules in the same workbook’s VBA project can refer to it. It does not make the array available to every open workbook, to Excel for the web, or to unrelated VBA projects.

Scope and lifetime are different. Scope describes which code can refer to a variable by name. Lifetime describes how long its current value remains in memory. A public array is visible across the project while the VBA project is running, but it is not permanent storage. Closing the workbook or resetting the VBA project clears its in-memory values.

Put the declaration in a standard module, such as one named modGlobals. A standard module is created in the Visual Basic Editor with Insert > Module. A public variable declared inside a Sub or Function is not valid VBA syntax, and a variable in a worksheet, ThisWorkbook, UserForm, or class module does not serve as a simple project-wide global in the same way.

A working dynamic-array example

The following complete example declares a public dynamic array, initializes it once, and exposes a function that checks whether the array is ready and whether the requested index is in range. In the Visual Basic Editor, place it in a standard module and set that module’s name to modGlobals if you want to use the qualified name shown later.

Option Explicit

Public gScores() As Long
Public gScoresReady As Boolean

Public Sub InitializeScores()
    ' Allocate three elements and store sample values.
    ReDim gScores(1 To 3)
    gScores(1) = 10
    gScores(2) = 20
    gScores(3) = 30
    gScoresReady = True
End Sub

Public Function GetScore(ByVal item As Long) As Long
    ' Initialize on first use, then validate the requested index.
    If Not gScoresReady Then InitializeScores

    If item < LBound(gScores) Or item > UBound(gScores) Then
        Err.Raise 9, "modGlobals.GetScore", "Score index is outside the array bounds."
    End If

    GetScore = gScores(item)
End Function

For example, a procedure in another standard module can call Debug.Print modGlobals.GetScore(2). The Immediate window displays 20, because the second element was assigned that value. Alternatively, code in the same module can call GetScore(2) without the module qualifier. The qualification makes it clearer where a public member is defined and can help avoid ambiguity if names overlap.

The readiness flag is included because a dynamic array needs to be allocated before an element can be read. It also gives the code a clear initialization check. If another procedure can change or erase the public array, it could make the flag inaccurate; for a larger project, consider keeping the array private and exposing controlled procedures or functions instead.

How to add and run the example

  1. Save a copy of the workbook first. Running a macro changes the workbook’s in-memory state, and macro actions cannot be undone with Ctrl+Z.
  2. Save the macro-enabled workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep its VBA code.
  3. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. If the Developer tab is available, you can also select Developer > Visual Basic. On Mac, use Option+F11; depending on the keyboard, you may need Fn+Option+F11. The Developer tab can also open the editor on Mac.
  4. In the editor, choose Insert > Module, paste the code into the new standard module, and confirm that the module is named modGlobals if using the qualified example call.
  5. Run InitializeScores from the editor or Excel’s macro interface, then call GetScore from another procedure. You can also run a small calling procedure and inspect the result in the Immediate window.

Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros. Use the desktop Excel application to work with this example. Macro settings and security prompts can affect whether a macro runs; only enable macros in files you trust.

Fixed arrays, dynamic arrays, and bounds

A fixed array has its bounds in the declaration, for example Public gFlags(1 To 5) As Boolean. Its size is set when the project is compiled and cannot be changed with ReDim. This is useful when the number of elements is known and will not change. A dynamic array, such as Public gScores() As Long, leaves the bounds open until code allocates it with ReDim.

Choose explicit lower and upper bounds where possible. In the example, the valid indexes are 1, 2, and 3; LBound and UBound return the actual lower and upper bounds, so the function does not assume a particular size. If a declaration or ReDim omits the lower bound, the result can depend on the module’s Option Base setting. Explicit bounds make the intended indexing easier to read and less likely to cause off-by-one errors.

For a dynamic array, you can use ReDim to allocate or resize it. A later ReDim normally clears the existing contents. ReDim Preserve retains existing values when resizing, but VBA restricts which dimension can be changed when preserving a multidimensional array. Design the array’s dimensions with that limitation in mind rather than assuming every dimension can be resized freely.

Sharing worksheet data with a public array

A common reason to use an array is to read a worksheet range into memory, process many values, and then write results back. A public Variant can hold the two-dimensional array returned when a multi-cell range’s Value2 property is assigned to it. For example, a module-level declaration can be Public gTable As Variant; a procedure can then assign gTable = ThisWorkbook.Worksheets("Data").Range("A1:C10").Value2.

That assignment produces a two-dimensional, one-based array for the three columns and ten rows in this example. Code can read the first cell of that in-memory array as gTable(1, 1), corresponding to cell A1. When you write a Variant array of matching dimensions to a range’s Value2 property, Excel can place the values into the worksheet in one operation. This is often more efficient than repeatedly reading or writing individual cells in a large loop.

There is an important edge case: assigning a single-cell range to a Variant returns a scalar value, not a two-dimensional array. Code that expects two indexes can therefore fail if the selected range sometimes contains only one cell. If range size varies, handle the single-cell case deliberately or ensure that the input range contains multiple cells. Also note that a public Variant can hold values of different types, so validate data before performing calculations on it.

When to use a global array—and when not to

A public array is convenient when several procedures need the same in-memory data during a coordinated task. It avoids passing a large array through many calls and can make a shared processing workflow straightforward. It is most appropriate when the data has a clear owner, initialization point, and end of use.

  • Use a local array when only one procedure needs the data. Local scope reduces accidental changes from unrelated code.
  • Pass an array as an argument when one procedure creates data and another consumes it. This makes the dependency explicit and often makes the code easier to reuse.
  • Use a private module-level array when several procedures in one module need shared state but other modules should not change it directly.
  • Use a public array when multiple modules genuinely need direct access, and document who initializes it and whether procedures are allowed to modify it.
  • Store lasting data in the workbook when it must survive closing Excel. A VBA variable is not a substitute for worksheet cells, a table, or another persistent data store.

Public state can make procedures dependent on call order: one routine may assume another has already loaded the values. The initialization check in the example reduces that risk, but it does not replace a deliberate design. Passing data explicitly or providing public getter and setter procedures can make larger projects easier to maintain and debug.

Common errors and troubleshooting

An “Subscript out of range” error commonly means the code requested an index outside the array’s bounds, or tried to use a dynamic array before it had been allocated. Check the values returned by LBound and UBound, confirm that the initialization procedure ran, and make sure the array was not erased or resized unexpectedly. The example raises a clear error for an invalid score index rather than relying on an unexplained failure later.

If another module says a variable is not defined, check that its declaration is truly at the top level of a standard module and uses Public, not Private or a procedure-level declaration. Also check spelling and module names. With Option Explicit, VBA flags undeclared names at compile time, which helps catch typos before they become runtime bugs.

If values unexpectedly disappear, consider whether the VBA project was reset. Pressing the Reset button in the Visual Basic Editor, using an End statement, or encountering some unhandled errors can reset module-level state. Closing the workbook also removes it. Re-run the initializer after a reset; do not expect the array to retain data between separate Excel sessions.

Frequently Asked Questions

Can I declare a global array inside a Sub?
No. Put the Public declaration at module level in a standard module, outside all Subs and Functions. The Sub can initialize or use the array, but it cannot contain a project-wide variable declaration.

Is a Public VBA array shared by every open workbook?
No. It is available within the VBA project in which it is declared. Another workbook has its own project and variables, unless you deliberately build a separate shared solution such as an add-in or reference another project.

Does a global array keep its values after I close Excel?
No. It is an in-memory variable, not saved worksheet data. Write values to a sheet or another persistent storage location if they must be available the next time the workbook opens.

How do I check whether a dynamic array has been initialized?
A separate Boolean flag, as in the example, is a straightforward approach. Set it after successful allocation and reset it whenever you erase or replace the array. Keep control of changes consistent so the flag cannot say “ready” when the array is not allocated.

Can Excel for the web run a VBA global array example?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to use the Visual Basic Editor and execute VBA.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.