VBA Array Notation: Syntax, Bounds, and Examples

VBA & macros · Updated November 18, 2026

In VBA, declare an array with parentheses after its name, such as Dim scores(1 To 5) As Long, and refer to an element by putting its index in parentheses, as in scores(1) = 90. VBA also provides the Array function for creating a Variant that contains an array; it does not use square brackets for ordinary array declaration or indexing.

What VBA Array Notation Means

An array is one variable that holds multiple values of the same general purpose, addressed by numbered positions called subscripts or indexes. Instead of creating separate variables for a list of scores, for example, you can store the values in scores and select an individual value with scores(i). Parentheses are the key notation: they appear in declarations, when accessing elements, and when describing array parameters.

The declaration determines important details such as the array’s type, number of dimensions, and bounds. For example, Dim names(1 To 3) As String declares a one-dimensional array of three strings. The expression names(2) refers to its second position because the declared indexes run from 1 to 3. An index is not necessarily a count from one: arrays can start at zero or another explicitly specified lower bound.

Array notation is different from worksheet range notation and from dynamic-array formulas in Excel. A VBA array exists in memory while code runs; a worksheet range is a group of cells, and a formula such as SORT returns results to the grid. The concepts can work together, but their syntax and rules are not interchangeable.

Declaring Fixed and Dynamic Arrays

A fixed-size array has its bounds written in the declaration. Its size is set when the procedure or module is initialized, so it suits a known number of values. Here is a small example; save a copy of the workbook before running any macro, because VBA actions cannot be reversed with Ctrl+Z.

Option Explicit

Sub MakeFixedArray()
    Dim scores(1 To 5) As Long

    ' Store a value at the first declared index.
    scores(1) = 90
    Debug.Print scores(1)
End Sub

A dynamic array leaves the bounds empty in its declaration and receives them later with ReDim. This is useful when the number of records is not known until the code runs. Declare the element type even though the bounds are not yet assigned:

Option Explicit

Sub MakeDynamicArray()
    Dim names() As String
    Dim itemCount As Long

    ' Set the required upper bound at run time.
    itemCount = 3
    ReDim names(1 To itemCount)
    names(1) = "Ava"
    Debug.Print names(1)
End Sub

In that example, names has indexes 1, 2, and 3. Until a dynamic array has been dimensioned, code should not try to access one of its elements. A fixed-size array can be redimensioned only in specific circumstances; dynamic arrays are the usual choice when the bounds must change.

Indexes, Bounds, and Option Base

If an array declaration omits an explicit lower bound, VBA normally starts it at zero. A module-level Option Base 1 statement can change the default lower bound to one for applicable declarations in that module. It must appear before procedures. Because a reader may not know which default a module uses, explicitly writing both bounds—such as 1 To 10—is often clearer and avoids relying on a hidden default.

Use LBound and UBound to ask an array for its actual lower and upper bounds. This is safer than assuming the first index is zero or one, especially when an array comes from another procedure or from the Array function. For a multidimensional array, provide the dimension number as the second argument, such as UBound(data, 2).

For example, a loop that works across the bounds of a one-dimensional array can be written as follows. The loop variable is declared, and the bounds are read rather than guessed:

Option Explicit

Sub PrintArrayValues()
    Dim values(1 To 3) As Long
    Dim i As Long

    ' Visit each valid index in order.
    For i = LBound(values) To UBound(values)
        Debug.Print values(i)
    Next i
End Sub

Attempting to access an index outside the declared bounds commonly causes “Subscript out of range.” A loop that starts at zero will fail for an array declared from 1 to 3; a loop that ends at 10 will fail if the upper bound is 3. Matching loop limits to LBound and UBound prevents these mismatches.

Using the Array Function

The Array function creates an array value and returns it inside a Variant. This is convenient for a short list of values, but it is not the same as declaring a strongly typed array. Assign the result to a Variant, then access the elements with parentheses:

Option Explicit

Sub UseArrayFunction()
    Dim colors As Variant
    Dim i As Long

    ' Array returns a Variant containing an array.
    colors = Array("red", "green", "blue")
    For i = LBound(colors) To UBound(colors)
        Debug.Print colors(i)
    Next i
End Sub

By default, the indexes produced by Array start at zero. The module’s Option Base setting can affect that default. Consequently, code should use LBound(colors) instead of assuming the first element is colors(0). The function is handy when supplying a small, known list, but for a large or typed collection of values, a declared array may make the intended structure more explicit.

VBA does not provide a general square-bracket array-literal notation like some programming languages. In VBA, square brackets are commonly used as shorthand for evaluating an Excel expression or referring to a worksheet range in certain contexts; they are not the standard way to declare or index an array in a procedure.

Multidimensional Array Notation

Use a comma-separated list of bounds to declare multiple dimensions. A two-dimensional array can represent rows and columns, while a three-dimensional array can add another category such as month. Each element is accessed by providing one index for each dimension, in the same order as the declaration.

Option Explicit

Sub UseTwoDimensionalArray()
    Dim sales(1 To 2, 1 To 3) As Double

    ' First index selects a row; second selects a column.
    sales(1, 2) = 125.5
    Debug.Print sales(1, 2)
End Sub

Here, the first dimension has indexes 1 through 2 and the second has indexes 1 through 3. The element sales(1, 2) means row 1, column 2 according to the program’s chosen interpretation. VBA does not automatically label dimensions as rows or columns; the code’s design establishes what each position means. For loops over a rectangular array, use LBound and UBound separately for each dimension.

Resizing Arrays with ReDim

ReDim sets or changes the bounds of a dynamic array. Without Preserve, redimensioning discards the array’s existing contents. With Preserve, VBA retains existing values that still fit, but there is a significant limitation: you can change only the upper bound of the last dimension. You cannot use Preserve to change the number of dimensions or the lower bound.

For example, a one-dimensional dynamic array can grow while retaining its existing values:

Option Explicit

Sub GrowArray()
    Dim values() As Long

    ' Allocate two elements, then extend the upper bound.
    ReDim values(1 To 2)
    values(1) = 10
    ReDim Preserve values(1 To 3)
    Debug.Print values(1)
End Sub

The printed value is still 10 because Preserve retains the element at index 1. If the second ReDim omitted Preserve, existing contents would be lost. When repeatedly adding items to a large array, extending it for every item can be inefficient; code can allocate extra capacity in batches and track how many positions are in use.

Looping Through and Passing Arrays

A standard For loop is useful when the index matters, such as when one array position corresponds to a row number. A For Each loop is convenient when the code only needs each value. Its control variable should be declared as a Variant when iterating through an array of values:

Option Explicit

Sub PrintWithForEach()
    Dim values As Variant
    Dim item As Variant

    ' Iterate through each element without using an index.
    values = Array("north", "south")
    For Each item In values
        Debug.Print item
    Next item
End Sub

Procedures can accept arrays as arguments. For a typed array, a procedure parameter can be declared with empty parentheses, for example ByRef values() As Long; the calling code passes the array variable, not an individual element. A Variant parameter can instead accept a Variant that contains an array. Whichever approach is used, document expected bounds and dimensions, and avoid assuming the caller used a particular lower bound unless that is part of the procedure’s contract.

Arrays and Excel Worksheet Ranges

Arrays are often used to process worksheet data efficiently. In Excel VBA, assigning a multi-cell range’s Value to a Variant typically places the values in a two-dimensional, one-based array: the first dimension represents rows and the second represents columns. Assigning an appropriately sized two-dimensional array back to a range writes its values to the cells.

There is an important exception: reading the value of a single-cell range returns a scalar value rather than a two-dimensional array. Code that must handle both one cell and multiple cells should account for this difference rather than blindly applying array bounds functions to every range result. Also remember that worksheet row and column numbers are not automatically the same as array subscripts in every array you create; inspect the bounds and keep the mapping explicit.

When data is processed in memory, reading a block into an array, changing its values, and writing it back can reduce repeated worksheet interaction. However, validate the target range dimensions before assigning the result, and be careful not to overwrite formulas or unrelated data. A workbook copy is prudent before running code that writes to cells.

Common Notation Mistakes

  • Using square brackets for an element: use values(index), not values[index], for ordinary VBA array access.
  • Assuming every array starts at zero: explicit bounds, Option Base, and the source of the array can affect the lower bound. Ask with LBound.
  • Forgetting to dimension a dynamic array: declare it with empty parentheses, then use ReDim before accessing an element.
  • Resizing without preserving data: ReDim normally clears the previous contents. Use Preserve when appropriate, while observing its last-dimension restriction.
  • Confusing a Variant with an element type: a Variant can hold an array, but a typed declaration such as Dim counts() As Long specifies the type of each element.
  • Mixing worksheet formulas with VBA syntax: formula spill behavior belongs to Excel’s calculation grid; VBA arrays use declarations, subscripts, and bounds in code.

Frequently Asked Questions

Does VBA use square brackets for arrays?
No. Standard VBA array declarations and element references use parentheses, for example Dim a(1 To 4) As Long and a(2). Square brackets have other uses in Excel VBA, but are not the normal array-index notation.

How do I find the size of a VBA array?
Use LBound(array) and UBound(array) to get its first and last indexes. For a multidimensional array, pass a dimension number, such as UBound(array, 2). The count of elements in one dimension is the upper bound minus the lower bound plus one.

What is the difference between Dim and ReDim?
Dim declares the array variable and its element type. For a dynamic array, ReDim allocates or changes its bounds at run time. A fixed array usually has its bounds in its declaration instead.

Can a VBA array start at 1?
Yes. Write an explicit lower bound such as Dim a(1 To 5) As Long. Option Base 1 can set a module’s default lower bound for applicable arrays, but explicit bounds make the intended indexes easier to see.

Why does Array return a Variant?
The VBA Array function returns a Variant containing an array, which allows it to create a list without a separate typed-array declaration. Assign it to a Variant and use LBound and UBound to work safely with its indexes.

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.