Excel VBA Arrays: How to Define and Use Them

VBA & macros · Updated November 21, 2026

An Excel VBA array is a variable that holds multiple values under one name, with each value accessed by an index. Declare a fixed array with a statement such as Dim scores(1 To 5) As Long; use a dynamic array, declared as Dim scores() As Long, when its size will be set or changed while the macro runs.

What an array means in VBA

Ordinary VBA variables usually hold one value at a time. An array groups related values of the same declared type, or of mixed types if it is a Variant array, so code can process a collection with a loop. Instead of creating separate variables for five scores, for example, a macro can store them in one array and refer to each value by its position.

An array is not the same thing as an Excel worksheet range, even though the two work well together. A range is a set of cells in a workbook; an array is data held in memory while VBA runs. A macro can read a range into an array, work with the values, and write results back to cells. That approach can be much faster than repeatedly reading and writing individual cells in a large loop.

Array indexes are called subscripts. An array declared with bounds from 1 to 5 has five elements, addressed as scores(1), scores(2), and so on. The bounds are part of the array’s definition; they do not necessarily match worksheet row numbers or column numbers.

Declaring a fixed-size array

A fixed-size array has bounds specified in its declaration. For example, this procedure stores five whole-number values and displays the third value:

Option Explicit

Sub FixedArrayExample()
    Dim scores(1 To 5) As Long
    Dim i As Long

    ' Fill each element with a multiple of 10.
    For i = LBound(scores) To UBound(scores)
        scores(i) = i * 10
    Next i

    MsgBox scores(3)
End Sub

The message box displays 30 because the third element is assigned the value 3 × 10. Long is a common type for whole numbers in VBA. Other useful element types include String for text, Double for decimal numbers, Date for dates and times, and Variant when elements may need to hold different kinds of values.

If you declare Dim scores(5) As Long without writing a lower bound, the usual default lower bound is zero: the indexes are 0 through 5, making six elements. A module-level Option Base 1 changes the default for declarations without explicit lower bounds in that module. To avoid ambiguity, especially in shared code, specify both bounds explicitly when the intended range matters.

Dynamic arrays and ReDim

A dynamic array leaves the bounds out of its initial declaration. VBA allocates its storage later, when the procedure knows how many elements are needed. The following example starts with three elements and expands the array to five:

Option Explicit

Sub DynamicArrayExample()
    Dim items() As String
    Dim i As Long

    ' Allocate three elements, indexed from 1 to 3.
    ReDim items(1 To 3)
    items(1) = "Red"
    items(2) = "Green"
    items(3) = "Blue"

    ' Preserve existing values while increasing the upper bound.
    ReDim Preserve items(1 To 5)
    items(4) = "Gold"
    items(5) = "Silver"

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

ReDim allocates or reallocates a dynamic array. Without Preserve, resizing discards the values already stored in the array. With Preserve, VBA keeps the existing elements, but there is an important limit: for a multidimensional array, only the upper bound of the last dimension can be changed while preserving the data. A dynamic array can also be released with Erase; for a dynamic array, that deallocates its storage.

Use a fixed-size array when the required number of elements is known and will not change. Use a dynamic array when the number of records comes from a range, a file, or some other value determined at run time. Dynamic does not mean that VBA automatically grows the array: your code must use ReDim to allocate or resize it.

Finding array bounds and looping safely

LBound returns an array’s lower bound, and UBound returns its upper bound. These functions let a loop work with the actual indexes rather than assuming that an array begins at zero or one. This matters when arrays come from another procedure, use a different base, or have been resized.

For a one-dimensional array, use LBound(arrayName) and UBound(arrayName). For a multidimensional array, include the dimension number: LBound(data, 1) checks the first dimension and UBound(data, 2) checks the second. Asking for a dimension that does not exist causes an error, so use the correct dimension count.

There is no requirement that an index represent a row number. If an array’s lower bound is 1, its first element is at index 1 regardless of whether the related worksheet data begins in row 2, row 10, or another location. Keep that distinction in mind when translating between array positions and worksheet cells.

Multidimensional arrays

A multidimensional array uses more than one index to represent a table-like structure. This declaration creates a two-dimensional array with three rows and four columns:

Dim grid(1 To 3, 1 To 4) As Double

The first index identifies a position in the first dimension, and the second identifies a position in the second. For instance, grid(2, 3) refers to the element at position 2 in the first dimension and position 3 in the second. In Excel work, two-dimensional arrays are especially useful because a rectangular cell range can be read and written as a block.

When code loops through a two-dimensional array, it is often clearest to use a nested loop: the outer loop visits one dimension and the inner loop visits the other. Check both dimensions with LBound and UBound, rather than assuming a particular size. That makes the code easier to adapt if the source range changes.

Using arrays with worksheet ranges

For a range containing multiple cells, assigning its Value property to a Variant variable creates a two-dimensional Variant array. Its first dimension corresponds to worksheet rows and its second to columns; the indexes for this range-based array are one-based. With data in A2:C6 on Sheet1, the first array element corresponds to A2, and data(1, 3) corresponds to C2.

Option Explicit

Sub ReadRangeIntoArray()
    Dim data As Variant
    Dim i As Long

    ' Read the five-row, three-column block into memory.
    data = ThisWorkbook.Worksheets("Sheet1").Range("A2:C6").Value

    ' Print the first and third columns for each row.
    For i = LBound(data, 1) To UBound(data, 1)
        Debug.Print data(i, 1), data(i, 3)
    Next i
End Sub

This example reads the range once, then works with the stored values. It prints values from columns A and C to the Immediate window in the VBA editor; it does not change the worksheet. To write a same-sized two-dimensional array back to a matching rectangular range, assign the array to that range’s Value property. The dimensions must fit the destination range.

A common edge case is a range containing only one cell. In that case, reading its Value returns a single value, not a two-dimensional array. Code that expects two indexes can therefore fail if the selected range sometimes shrinks to one cell. Handle the single-cell case separately, or ensure that the range always contains multiple cells.

Choosing an array type and avoiding common errors

Choose the element type according to the data and the operations the macro performs. A typed array, such as Long() or String(), makes the intended contents clear and can prevent unsuitable values from being assigned. A Variant array is convenient for mixed data and for receiving a multi-cell range, but each element can hold different kinds of values. In workbook automation, a Variant variable is often the practical choice for a range read, while a typed array may suit a well-defined calculation.

  • Subscript out of range: the code refers to an index outside the array’s bounds. Check the declared bounds or use LBound and UBound in the loop.
  • Array not dimensioned: a dynamic array was used before storage was allocated with ReDim, or it was erased. Allocate it before accessing an element.
  • Values disappeared after resizing: ReDim without Preserve discards existing values. Use ReDim Preserve when its restrictions fit the task.
  • Type mismatch: a value cannot be stored in the declared element type, or code treats a single-cell range result as an array.
  • Wrong dimension used: a two-dimensional array needs two indexes for an element. Use the right dimension number when checking its bounds.

Arrays and Excel’s dynamic-array worksheet formulas are separate features. A VBA array is data used by a macro; worksheet formulas such as FILTER or UNIQUE spill results into cells and do not require a VBA declaration. If the task is simply to calculate or display a list with a worksheet formula, a macro may not be necessary.

Running and saving VBA code safely

VBA runs in the desktop Excel application, not Excel for the web. On Windows, open the Visual Basic Editor with Alt+F11. On a Mac, Option+F11 is commonly used; depending on keyboard settings, you may also need to hold Fn. You can place a procedure in a standard module in the editor and run it from Excel’s macro controls or from the editor.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want to retain its macros. A standard .xlsx workbook does not store VBA project code. Macro security settings may also prevent code from running until the workbook or its macros are permitted under your organization’s security rules; do not enable macros in an untrusted file.

Before running a macro that changes workbook data, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, and a macro that writes values to cells can overwrite existing content. A copy gives you a way to restore the original if the code produces an unexpected result.

Frequently Asked Questions

What is the simplest way to define an array in Excel VBA?
For five whole numbers, write Dim values(1 To 5) As Long. This declares a fixed array with indexes 1 through 5. Use explicit bounds to make the intended indexes clear.

Does a VBA array start at 0 or 1?
It depends on how its bounds are declared and, for declarations without an explicit lower bound, on the module’s Option Base setting. An array declared as Dim values(1 To 5) As Long always uses indexes 1 through 5. Check with LBound and UBound when the bounds are not obvious.

How do I resize an array in VBA?
Declare a dynamic array, such as Dim values() As Long, then allocate it with ReDim values(1 To 10). Use ReDim Preserve to retain existing values when resizing, bearing in mind that for a multidimensional array only the last dimension’s upper bound can be changed this way.

How do I put an Excel range into an array?
Assign a multi-cell range’s Value to a Variant variable, for example data = Worksheets("Sheet1").Range("A2:C6").Value. The result is a two-dimensional array with row and column indexes. A one-cell range returns a single value instead.

Can I use VBA arrays in Excel for the web?
You can open a macro-enabled workbook there, but Excel for the web cannot run, create, or edit VBA macros. Use desktop Excel for Windows or Mac to work with VBA code.

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.