Excel VBA Build Array: Methods, Examples, and Limits

To build an array in Excel VBA, assign the Array function to a Variant, or declare a typed array and fill its elements. Use ReDim when the array’s size is not known in advance, and use a range’s Value property when you want to load worksheet cells into a two-dimensional array.

What a VBA array is

An array stores multiple values under one variable name, with each value identified by an index. Instead of declaring separate variables for several product names, for example, you can store them in one array and process them in a loop. Arrays can hold one dimension, such as a list, or several dimensions, such as a table of rows and columns.

VBA arrays are held in memory while the macro runs. They are not the same thing as Excel’s worksheet array formulas or dynamic-array functions such as FILTER. Building a VBA array does not, by itself, place values in cells; your code must explicitly write the array to a worksheet if that is the goal.

Before running any macro that changes a workbook, save a copy. Macro actions generally cannot be reversed with Ctrl+Z on Windows; on Mac, they cannot be reversed with Command+Z either.

Choose a way to build the array

The right approach depends on whether the values are already known, whether they have one data type, and whether the number of elements can change. These are the common choices:

Array(...) is concise for a short list of known values. It returns a Variant containing an array.
A fixed-size declaration, such as Dim scores(1 To 5) As Long, is suitable when the number of elements is known and a specific data type is useful.
A dynamic declaration, such as Dim scores() As Long, followed by ReDim, is useful when the required size is determined while the macro runs.
Assigning a multi-cell range’s Value to a Variant is usually a convenient way to read a rectangular block of worksheet data.

For a short, mixed list, Array is often the simplest. For a large list with a predictable type and size, a typed array can make the intended data structure clearer. For data coming from cells, reading a range into a Variant array can avoid repeatedly accessing the worksheet inside a loop.

Build a short list with Array

The Array function accepts comma-separated values and returns an array held in a Variant. Put this example in a standard VBA module. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11; on Mac, use Tools > Macro > Visual Basic Editor. The Mac keyboard shortcut may be Option+F11, or Fn+Option+F11 depending on the keyboard.

Sub BuildListWithArray()
    Dim items As Variant
    Dim i As Long

    ' Create a Variant containing three strings.
    items = Array("North", "South", "West")

    ' Print each item and its index in the Immediate window.
    For i = LBound(items) To UBound(items)
        Debug.Print i, items(i)
    Next i
End Sub

By default, a VBA array created with Array is usually indexed from zero, so this example has three elements at indexes 0, 1, and 2. The Option Base statement at the top of a module can change the default lower bound for arrays in that module. Because bounds can vary, use LBound and UBound rather than assuming an array starts at zero or one. Those functions return the lower and upper index for a dimension.

The elements in a Variant array can contain different kinds of values, but mixed types can make later processing harder to reason about. If all values should be numbers, dates, or strings, a typed array is often a more explicit choice.

Declare and fill a typed array

A fixed-size array has its bounds in the declaration. The following example creates a one-dimensional array of three strings, with indexes explicitly set from 1 to 3. Explicit bounds make the intended indexing clear and do not depend on the module’s Option Base setting.

Sub BuildFixedArray()
    Dim months(1 To 3) As String
    Dim i As Long

    ' Assign a value to each declared element.
    months(1) = "January"
    months(2) = "February"
    months(3) = "March"

    For i = LBound(months) To UBound(months)
        Debug.Print months(i)
    Next i
End Sub

Choose the element type to match the values: for example, Long for whole-number counts, Double for decimal numeric values, and String for text. VBA also supports types such as Date and Boolean. A typed array can help signal what the data represents and prevent some unintended assignments. If values can be different types or include Excel error values, a Variant array may be more appropriate.

Use a dynamic array when the size changes

A dynamic array is declared with empty parentheses, then sized with ReDim. This is useful when a count is calculated at run time, such as the number of records that meet a condition. The bounds in ReDim determine how many elements are available.

Sub BuildDynamicArray()
    Dim values() As Long
    Dim i As Long

    ' Allocate five elements, indexed from 1 through 5.
    ReDim values(1 To 5)

    For i = LBound(values) To UBound(values)
        values(i) = i * 10
    Next i

    ' Print the values: 10, 20, 30, 40, and 50.
    For i = LBound(values) To UBound(values)
        Debug.Print values(i)
    Next i
End Sub

To change the size while keeping existing values, use ReDim Preserve. For example, ReDim Preserve values(1 To 8) extends the one-dimensional array while retaining its current elements. Without Preserve, resizing an already populated dynamic array clears its contents. With Preserve, VBA allows you to change only the upper bound of the last dimension; it does not let you resize any other dimension while retaining the values.

Repeatedly expanding an array one element at a time with ReDim Preserve can be inefficient for large collections because VBA may need to move the existing elements during each resize. If you know the final count, allocate once. If you do not, consider reserving capacity in larger increments or using a Collection while gathering items, then copying the results into an array once the count is known.

Build an array from worksheet cells

When you assign a rectangular, multi-cell range’s Value to a Variant, VBA returns a two-dimensional array. Its first dimension represents worksheet rows and its second dimension represents columns. A range such as A1:C3 therefore produces three rows by three columns, and you can address a cell’s value using both indexes.

Sub ReadRangeIntoArray()
    Dim ws As Worksheet
    Dim values As Variant
    Dim rowIndex As Long
    Dim colIndex As Long

    ' Read a three-row by three-column block into memory.
    Set ws = ThisWorkbook.Worksheets("Data")
    values = ws.Range("A1:C3").Value

    ' Print each value, using the array's actual bounds.
    For rowIndex = LBound(values, 1) To UBound(values, 1)
        For colIndex = LBound(values, 2) To UBound(values, 2)
            Debug.Print rowIndex, colIndex, values(rowIndex, colIndex)
        Next colIndex
    Next rowIndex
End Sub

The range should contain more than one cell for this pattern: a single-cell range’s Value is a scalar value rather than a two-dimensional array. Also make sure the worksheet named Data exists in the workbook containing the code, or change the sheet name. Qualifying the range through a worksheet variable avoids relying on whichever sheet happens to be active.

You can write a two-dimensional array back to a range of matching dimensions by assigning it to that range’s Value. For example, after changing values in an array loaded from A1:C3, assign it to ws.Range("A1:C3").Value. For a destination whose size is not fixed, use the array’s row and column bounds to size the destination, and check that the bounds correspond to the intended output.

Work with dimensions and indexes

In a multidimensional array, LBound and UBound accept a dimension number. For a two-dimensional array, dimension 1 is the row dimension and dimension 2 is the column dimension when the array comes from a worksheet range. The nested loops in the range example use those bounds instead of hard-coding row and column counts.

To declare a two-dimensional typed array directly, specify both sets of bounds, for example Dim grid(1 To 4, 1 To 6) As Double. You can then assign an element with two indexes, such as grid(2, 3) = 7.5. The declared bounds mean the first dimension has four positions and the second has six. Use nested loops to visit each element, with a separate loop variable for each dimension.

One frequent source of bugs is mixing the worksheet’s row and column numbers with an array’s actual bounds. A range read into an array commonly uses one-based bounds, even if other arrays in the module use a different lower bound. Use the relevant bounds for the array you are processing rather than carrying over assumptions from another array.

Common errors and practical safeguards

A dynamic array declared as Dim values() As Long has not yet been allocated. Trying to use its elements before calling ReDim causes a run-time error. Similarly, attempting to use LBound or UBound on an uninitialized dynamic array can fail. Allocate it before reading or looping through it, and keep track of whether an array has been initialized when that depends on program flow.

Subscript out of range: The index is outside the array’s lower and upper bounds. Check the bounds with LBound and UBound.
Unexpected starting index: Do not assume every array starts at zero. Explicitly state bounds where clarity matters, and use the bound functions in loops.
Lost values after resizing: Use ReDim Preserve when you need to retain elements, while observing its last-dimension restriction.
Wrong range shape: The dimensions of a two-dimensional array and the destination range must match when writing values back.
Unexpected scalar: Reading one cell returns a single value, not the two-dimensional array pattern used for a multi-cell range.

For a dynamic array, Erase resets a fixed array’s elements or releases the allocated storage of a dynamic array. If you plan to reuse a dynamic array after erasing it, allocate it again before accessing elements. Avoid suppressing all errors simply to get past a failed bounds check; determine whether the array is initialized and whether the requested index is valid.

When to use a Collection instead

An array is a good fit when you need indexed access, a compact block of values, or a direct exchange with a worksheet range. A Collection can be more convenient when you need to add items one at a time without deciding the final count at the start. It can also store items under keys, although its behavior and access patterns differ from array indexing.

Collections do not replace arrays in every situation. They are not assigned to worksheet ranges in the same straightforward way as a two-dimensional Variant array, and converting a collection to an array takes extra code. For a large block of worksheet data, reading it into an array and processing it in memory is often simpler than making a worksheet call for every cell. For a small, changing set of items assembled incrementally, a collection may be clearer than repeatedly resizing an array.

Frequently Asked Questions

Does VBA Array start at zero or one?
Usually, the Array function starts at zero, but the module’s Option Base setting can affect the default lower bound. Check the actual lower bound with LBound instead of relying on an assumption.

How do I find the size of a VBA array?
Use UBound(arrayName) - LBound(arrayName) + 1 for a one-dimensional array. For a particular dimension of a multidimensional array, pass its dimension number to both bound functions.

Can VBA arrays contain text and numbers together?
Yes. A Variant array can contain values of different types, including text and numbers. A typed array, such as one declared as Long or String, is intended for values of its declared type.

How do I put a VBA array into Excel cells?
Assign a two-dimensional array to a worksheet range with matching dimensions using the range’s Value property. A one-dimensional array usually needs to be arranged into the row-or-column shape expected by the destination before writing it as a block.

Can I resize a multidimensional array and keep its values?
With ReDim Preserve, VBA can change only the upper bound of the last dimension while preserving existing values. If you need to resize another dimension, copy the values into a newly allocated array.

Why does my macro’s array disappear from the worksheet?
An array exists in VBA memory; it does not automatically create or update cells. Assign its values to a worksheet range, or write individual elements to cells, to display the data in the workbook.

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.