VBA Array Index: Bounds, Options, and Examples

In VBA, an array index is the number inside an array’s parentheses that identifies an element, such as values(0) or values(1). The valid indexes are set by the array’s lower and upper bounds; use LBound and UBound to check them rather than assuming every array starts at zero or one.

What an Array Index Means in VBA

An array stores multiple values under one variable name. Each stored value is an element, and its index (also called a subscript) identifies its position within that array. For example, if prices has an element at index 2, prices(2) refers to that particular element. Indexes are part of the array’s bounds, not a guarantee that the first element is numbered 0 or 1.

This distinction matters when a loop accesses an array. A loop that starts at 0 will fail if the array begins at 1, while a loop that starts at 1 can skip the first element of a zero-based array. VBA reports an invalid index with an error such as “Subscript out of range” (error 9). The reliable approach is to ask the array for its bounds and loop over that range.

Array indexes are also different from row numbers in a worksheet or item numbers in a collection. An array has its own lower and upper bounds. A worksheet range converted to an array commonly has indexes beginning at 1, but that does not mean every VBA array does.

VBA’s Default Lower Bound

In a VBA module, the default lower bound is 0 unless the module uses Option Base 1. Thus, a declaration such as Dim values(5) As Long normally creates six elements indexed from 0 through 5, not five elements. The number in parentheses is the upper bound when the lower bound is omitted.

Option Base 1 changes the default for array declarations in that module where the lower bound is omitted. It must appear in the declarations section at the top of the module, before procedures. It is a module-level setting, so it does not automatically set the default for other modules. Code that relies on an implicit default can therefore be confusing when arrays are passed between modules or copied into another project.

For clarity, specify both bounds when the intended indexes matter:

Option Explicit

Sub ShowExplicitBounds()
    ' This array has three elements, indexed 1 through 3.
    Dim values(1 To 3) As Long
    Dim i As Long

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

Explicit bounds are unaffected by Option Base. This makes declarations such as Dim values(0 To 5) As Long or Dim values(1 To 5) As Long easier to read and less dependent on module settings.

How to Check an Array’s Index Range

LBound(array) returns the lower bound, and UBound(array) returns the upper bound. For a one-dimensional array, these calls are usually enough to create a loop that works regardless of whether the array begins at 0, 1, or another number.

Option Explicit

Sub PrintEveryElement()
    ' The declared bounds are explicit, but the loop reads them from the array.
    Dim names(2 To 4) As String
    Dim i As Long

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

In that example, the loop runs for indexes 2, 3, and 4. It remains correct if the declaration’s bounds later change. For a multidimensional array, supply the dimension number: LBound(grid, 1) and UBound(grid, 1) inspect the first dimension; use 2 for the second dimension. If the requested dimension does not exist, VBA raises an error.

For a dynamic array that has been declared but not allocated with ReDim, calling LBound or UBound raises an error. Allocate it first, or use error handling or a separate state check when your procedure must support an unallocated array. VBA does not provide a built-in IsAllocated function.

Fixed and Dynamic Array Bounds

A fixed-size array gets its bounds in its declaration. A dynamic array is declared with empty parentheses and receives bounds later through ReDim. Both kinds have lower and upper bounds, and both can be zero-based, one-based, or explicitly set to other values.

Option Explicit

Sub ResizeValues()
    ' Save a copy of the workbook before running any macro.
    ' Changes made by VBA cannot be undone with Ctrl+Z.
    Dim values() As Long
    Dim i As Long

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

    ReDim Preserve values(0 To 4)
    Debug.Print LBound(values), UBound(values), values(2)
End Sub

The initial allocation has three elements: indexes 0, 1, and 2. After the preserved resize, it has five elements, from 0 through 4, and the existing value at index 2 remains 20. Newly added numeric elements start at zero. Preserve retains existing elements when the array is resized within VBA’s restrictions.

With ReDim Preserve, VBA permits changing the upper bound of the last dimension only. You cannot use it to change the number of dimensions or the lower bound. Without Preserve, resizing an already allocated array discards its existing contents. Use Erase deliberately: it deallocates a dynamic array, while elements of a fixed-size array are reset to their default values.

Indexes in Multidimensional Arrays

A two-dimensional array uses two indexes, one for each dimension. In a declaration such as Dim grid(1 To 2, 1 To 3) As Double, valid pairs are rows 1 to 2 and columns 1 to 3. The expression grid(2, 3) identifies the element at the second dimension’s third position; it is not the same as a single index of 2 or 3.

Use the appropriate bound for each loop. The first dimension is often treated as rows and the second as columns, especially for arrays read from worksheet ranges, but the array’s declaration determines the bounds—not its labels or the variable names chosen in the code.

Option Explicit

Sub VisitGrid()
    ' Visit every element of a two-dimensional array.
    Dim grid(1 To 2, 1 To 3) As Double
    Dim rowIndex As Long
    Dim columnIndex As Long

    For rowIndex = LBound(grid, 1) To UBound(grid, 1)
        For columnIndex = LBound(grid, 2) To UBound(grid, 2)
            Debug.Print rowIndex, columnIndex, grid(rowIndex, columnIndex)
        Next columnIndex
    Next rowIndex
End Sub

For arrays with more dimensions, use the dimension argument to inspect each one. Keeping the dimension number visible in the code helps prevent errors where a valid-looking index is applied to the wrong dimension.

Arrays Created from Excel Ranges

When a multi-cell Excel range is assigned to a Variant using its Value or Value2 property, the result is a two-dimensional array. Its row and column bounds normally begin at 1. A one-cell range is an important exception: the property returns a single value rather than a two-dimensional array.

Option Explicit

Sub ReadRangeValues()
    ' Save a copy before running macros; VBA changes cannot be undone with Ctrl+Z.
    Dim data As Variant
    Dim rowIndex As Long
    Dim columnIndex As Long

    data = ThisWorkbook.Worksheets(1).Range("A1:C3").Value2

    For rowIndex = LBound(data, 1) To UBound(data, 1)
        For columnIndex = LBound(data, 2) To UBound(data, 2)
            Debug.Print rowIndex, columnIndex, data(rowIndex, columnIndex)
        Next columnIndex
    Next rowIndex
End Sub

The assigned range in this example has three rows and three columns, so the loop visits nine values. If the range is changed to a single cell, the same two-dimensional loop will not work because data will hold a scalar. Code that accepts a range of unknown size should account for that case, for example by ensuring the range contains multiple cells or by handling a single cell separately.

These arrays are useful for processing worksheet data in memory, but do not assume their bounds for every source. An array returned by another procedure, a COM component, or a custom routine may have different bounds. Inspect it with LBound and UBound.

Common Index Errors and How to Avoid Them

The most common indexing bug is a loop whose starting or ending number is hard-coded. A loop from 1 to 10 fails if the array has different bounds; a loop from 0 to UBound fails when the lower bound is 1. A second frequent mistake is treating the declaration’s final number as an element count. For example, Dim items(1 To 5) contains five elements, while Dim items(5) with the default base of 0 contains six.

Use LBound and UBound for loop limits instead of assuming a base.
Declare explicit bounds when a particular indexing convention is required.
For multidimensional arrays, pass the dimension number to each bound function.
Check that a dynamic array has been allocated before asking for its bounds.
Remember that a single-cell range assignment is a scalar, not a two-dimensional array.

When error 9 appears, inspect the array’s actual bounds at the failing line, then compare them with the index being used. In the VBA editor, execution can be paused at a breakpoint and the values inspected in the Locals window or with the Immediate window. This is more informative than changing the loop’s start number at random.

Choosing Zero-Based or One-Based Indexes

There is no single base that is right for every VBA array. Zero-based indexes are common in programming and can align with APIs or data structures that count offsets from zero. One-based indexes can feel natural when the array represents worksheet rows, numbered steps, or items counted from one. Explicitly selecting the bounds makes the choice clear.

For worksheet range data, retaining the returned 1-based row and column indexes is often convenient because they correspond to positions within the returned block. For an array used as a general sequence, either convention works if every procedure that reads or writes it respects the actual bounds. When passing an array between procedures, document its intended bounds if callers might otherwise assume a different base.

Avoid using Option Base 1 as a substitute for clear declarations in shared or complex code. It can make an omitted lower bound change meaning when code is moved to a module with different settings. Explicit declarations and bound-aware loops reduce that hidden dependency.

Frequently Asked Questions

Does a VBA array start at 0 or 1?
Usually 0 when the lower bound is omitted, unless the module uses Option Base 1. An explicit declaration such as Dim a(1 To 5) sets its own bounds.

Does Dim a(5) create five elements?
Not with the default lower bound of 0: it creates indexes 0 through 5, which is six elements. Use Dim a(1 To 5) for exactly five elements indexed 1 through 5.

How do I find an array’s last index?
Use UBound(array) for the upper bound of a one-dimensional array. For a multidimensional array, specify the dimension, such as UBound(grid, 2).

Why does LBound return an error?
A common cause is asking for bounds on a dynamic array before it has been allocated with ReDim. Another is requesting a dimension that the array does not have.

Are Excel range arrays zero-based?
A multi-cell range assigned through Value or Value2 normally produces a two-dimensional array with both dimensions starting at 1. A one-cell range returns a scalar instead.

Can ReDim Preserve change an array’s lower bound?
No. With Preserve, VBA allows the upper bound of the last dimension to change, but not the lower bound or the number of dimensions.

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.