Excel VBA Array Indexing: Bounds, Loops, and Examples

VBA & macros · Updated November 21, 2026

An Excel VBA array index is the number used to identify an element in an array. In VBA, an array declared without an explicit lower bound normally starts at index 0, but you can set its bounds yourself; use LBound and UBound to check the actual indexes before looping through it.

What an array index means

An array stores multiple values under one variable name. Each value occupies an element, and its index—also called a subscript—tells VBA which element you mean. For example, scores(2) refers to the element whose index is 2, not necessarily the third element. That distinction matters because an array can start at 0, 1, or another explicitly chosen number.

Suppose an array has bounds 0 through 4. It contains five elements: indexes 0, 1, 2, 3, and 4. An array bounded 1 through 5 also contains five elements, but its valid indexes differ. VBA raises run-time error 9, “Subscript out of range,” if code requests an index outside the array’s bounds.

Array indexes are not Excel worksheet row or column numbers by definition. They are the bounds assigned to that particular VBA array. A worksheet range read into an array happens to use one-based row and column indexes, while an array you declare may use zero-based indexes.

Default bounds and Option Base

In a module with no Option Base statement, a declaration that omits the lower bound starts at 0. Thus Dim values(5) As Long has six elements, from 0 through 5—not five elements. This is a common source of off-by-one mistakes.

You can place Option Base 1 at the top of a module, before any procedures. For declarations in that module that omit the lower bound, the default becomes 1. The declaration Dim values(5) As Long then has indexes 1 through 5. Option Base applies at the module level, not as a workbook-wide setting, so modules can use different defaults.

For readable code, explicit bounds usually make intent clearer than relying on a module setting. Dim values(1 To 5) As Long states its range directly and is unaffected by Option Base. Explicit bounds are especially useful when indexes correspond to days numbered 1 through 31, months 1 through 12, or another meaningful range.

Declaring arrays with explicit bounds

Use Dim with a lower and upper bound to create a fixed-size array. The following example has four elements, indexed 1 to 4. The comment describes the mapping, and the variable is declared before use. Before running any macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z.

Option Explicit

Sub ShowArrayIndexes()
    Dim colors(1 To 4) As String
    Dim itemIndex As Long

    ' Assign a value to the element at index 1.
    colors(1) = "Blue"

    ' Display every valid index and its value.
    For itemIndex = LBound(colors) To UBound(colors)
        Debug.Print itemIndex, colors(itemIndex)
    Next itemIndex
End Sub

In this example, colors(1) is the first element because the lower bound is 1. VBA does not translate that index into a worksheet cell or adjust it to match a collection automatically. The index belongs to the array.

Finding bounds with LBound and UBound

LBound(array) returns the lowest valid index, while UBound(array) returns the highest. For a multidimensional array, supply a dimension number: LBound(array, 1) and UBound(array, 1) return the bounds for the first dimension. These functions are safer than assuming an array starts at 0 or 1.

Use the bounds in loops when you do not control how an array was created, or when it may be resized. The loop then follows the array’s actual range rather than a hard-coded guess. For an array with valid indexes 3 through 8, for example, this pattern visits all six elements:

Option Explicit

Sub PrintArrayValues()
    Dim readings(3 To 8) As Double
    Dim itemIndex As Long

    ' Loop over the declared bounds, including both endpoints.
    For itemIndex = LBound(readings) To UBound(readings)
        Debug.Print readings(itemIndex)
    Next itemIndex
End Sub

VBA’s ordinary For loop includes both its starting and ending values. This is why using To UBound(readings) visits the last element; you do not subtract 1 as you might in some other programming languages.

Looping without off-by-one errors

The safest general pattern is For i = LBound(array) To UBound(array). It works whether a one-dimensional array starts at 0, 1, or a custom lower bound. It also continues to work if the upper bound changes after resizing. Choose a Long for the loop counter in typical Excel VBA code; worksheet row counts can exceed the range of the older Integer type.

  • Use LBound and UBound when the bounds are not guaranteed by the surrounding code.
  • Use an explicit lower bound when the index has meaning, and document that meaning.
  • Do not loop from 0 just because many programming examples do; first confirm the array actually includes index 0.
  • Check that the loop uses the right dimension when working with a multidimensional array.

If you deliberately want to skip an element or process indexes in reverse, adapt the loop knowingly. For reverse order, the pattern is to start at UBound(array) and count down to LBound(array) with Step -1. Bounds still matter: a reverse loop must stop at the lower bound, not at an assumed zero.

Indexes in multidimensional arrays

A two-dimensional array has an index for each dimension. Its elements are addressed as table(rowIndex, columnIndex). The first dimension is commonly treated as rows and the second as columns, particularly for data transferred to or from an Excel range. Each dimension has its own lower and upper bounds, so query both dimensions separately.

Option Explicit

Sub PrintTableArray()
    Dim table(1 To 2, 1 To 3) As Variant
    Dim rowIndex As Long
    Dim columnIndex As Long

    ' Visit every row and column in the two-dimensional array.
    For rowIndex = LBound(table, 1) To UBound(table, 1)
        For columnIndex = LBound(table, 2) To UBound(table, 2)
            Debug.Print rowIndex, columnIndex, table(rowIndex, columnIndex)
        Next columnIndex
    Next rowIndex
End Sub

The first argument after the array name is the row index in this example; the second is the column index. Reversing them can produce incorrect results or an out-of-range error when the dimensions have different sizes. A three-dimensional array follows the same idea, with a third subscript and a third dimension number for its bounds.

Dynamic arrays and resizing

A dynamic array is declared without bounds, then sized later with ReDim. This is useful when the required number of elements depends on data discovered while the macro runs. For example, Dim items() As String declares the array but does not allocate elements; a later ReDim items(0 To 9) gives it ten valid indexes.

ReDim without Preserve creates or resets the array, so previous contents are lost when it is resized. ReDim Preserve keeps existing values while changing the size, but in a multidimensional array VBA permits changing only the upper bound of the last dimension. Plan the layout before choosing how to resize a two-dimensional array.

Calling LBound or UBound on a dynamic array that has not yet been allocated can raise an error. Allocate it before querying its bounds, or track whether allocation has happened. Do not assume a zero-length or unallocated array can be handled like an ordinary array in a bounds-based loop.

Array indexes from Excel ranges

When VBA reads a multi-cell range into a Variant, Excel returns a two-dimensional array. Its first dimension represents worksheet rows and its second represents columns, and the bounds normally begin at 1. For example, a three-row by two-column range has indexes 1 to 3 in the first dimension and 1 to 2 in the second. A one-cell range is an important exception: its value is a scalar, not a two-dimensional array.

This example reads a fixed multi-cell range and prints each row’s two values. It uses a Variant because the range assignment returns an array of values rather than a single typed Long or String array. Save a copy before running macros that alter workbook data; macros cannot be reversed with Ctrl+Z.

Option Explicit

Sub ReadRangeIntoArray()
    Dim data As Variant
    Dim rowIndex As Long
    Dim sheet As Worksheet

    ' Read a three-row, two-column range into a Variant array.
    Set sheet = ThisWorkbook.Worksheets("Sheet1")
    data = sheet.Range("A1:B3").Value2

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

In this example, the loops visit rows 1 through 3, and the two column indexes are 1 and 2. If the range size can vary, query both dimensions with LBound(data, 1), UBound(data, 1), LBound(data, 2), and UBound(data, 2). If a macro may receive either a single cell or a larger range, handle the single-cell scalar separately before indexing it as a two-dimensional array.

Common index errors and how to diagnose them

“Subscript out of range” usually means the requested index is outside the array’s bounds, or the code is using the wrong dimension. If the failing expression is values(i), inspect i, LBound(values), and UBound(values). For a table, check the row and column subscripts against their respective dimensions.

Another frequent issue is confusing an element count with an upper bound. An array declared as Dim values(5) under the default base has six elements, not five. To make five elements starting at zero, declare bounds 0 through 4; to make five starting at one, declare bounds 1 through 5.

  • Loop ends too early: Check for a hard-coded upper limit that is smaller than UBound.
  • Loop starts at the wrong place: Replace an assumed 0 or 1 with LBound, unless a specific start is intentional.
  • Wrong range-array index: Remember that a range array has separate row and column dimensions.
  • Unallocated dynamic array: Allocate it before requesting bounds or reading an element.
  • Single-cell range behaves differently: A one-cell range returns a scalar value, so it cannot be indexed like a two-dimensional array without additional handling.

Frequently Asked Questions

Does a VBA array start at 0 or 1?
Usually 0 when the declaration omits its lower bound and the module has no Option Base 1. It can start at 1 or another value if you specify the bounds explicitly. Use LBound to confirm.

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

Does Dim array(10) create ten elements?
Not under the default zero-based setting: it creates indexes 0 through 10, which is eleven elements. To create ten elements starting at zero, use bounds 0 through 9.

Why does a range array start at 1?
Excel returns multi-cell range values as a two-dimensional Variant array whose row and column indexes normally start at 1. That convention is independent of the lower bound you might use in a separately declared array.

Can I use UBound on an array before ReDim?
No. An unallocated dynamic array does not yet have usable bounds, so asking for its bounds can raise an error. Allocate it first or track its allocation state in your 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.