VBA Array Cheat Sheet: Syntax, Bounds, and Examples

A VBA array stores multiple values under one variable name, and the essential pattern is to declare it, fill it, then loop from LBound to UBound. For example, use Dim scores(1 To 5) As Long for five fixed slots, or Dim scores() As Long followed by ReDim scores(1 To 5) when the size must be chosen later.

VBA Array Syntax at a Glance

Arrays are useful when a procedure needs to work with a group of related values, such as monthly totals, worksheet data, or names returned by a function. Instead of creating a separate variable for every item, an array gives each item an index. The index is also called a subscript.

Dim a(0 To 4) As Long declares a fixed array with five Long elements.
Dim a() As String declares a dynamic array whose bounds will be set later.
ReDim a(1 To 10) allocates or changes the size of a dynamic array.
LBound(a) and UBound(a) return its lowest and highest valid indexes.
Erase a resets a fixed array or deallocates a dynamic one.

The number of elements is not always the same as the upper bound. An array from index 0 through index 4 has five elements, while one from index 1 through index 5 also has five. To calculate a count for a one-dimensional array, use UBound(a) - LBound(a) + 1. Explicit bounds make code easier to read and prevent assumptions about where indexing starts.

Before running any macro that changes a workbook, save a separate copy: VBA macros cannot be undone with Ctrl+Z. The examples below are procedures or code fragments for a standard VBA module and use declared variables.

Bounds, Indexing, and Option Base

VBA arrays can start at zero or one, depending on how they are declared and the module’s Option Base setting. The default lower bound is zero unless a module uses Option Base 1; a declaration with explicit bounds, such as 1 To 12, states the intended range directly. Option Base applies to arrays whose lower bound is not explicitly specified in that module, so it should not be treated as a project-wide setting.

Use LBound and UBound rather than writing a loop that assumes the array begins at zero. This matters when code receives an array from another procedure or creates one using a different method. For a multidimensional array, provide a dimension number, such as LBound(matrix, 2), to inspect the second dimension.

Sub PrintItems()
    Dim items(1 To 3) As String
    Dim i As Long

    ' Fill the three explicitly indexed elements.
    items(1) = "North"
    items(2) = "Central"
    items(3) = "South"

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

This loop prints each value to the Immediate window, which can be opened in the Visual Basic Editor with Ctrl+G on Windows. The essential benefit is not the output method; it is that the loop remains correct if the declared bounds change. In Excel for Mac, the Visual Basic Editor is available, but keyboard shortcuts can differ by keyboard and Excel version; use the editor’s View menu if the Windows shortcut is unavailable.

Fixed and Dynamic Arrays

A fixed-size array has bounds in its declaration. Use it when the number of elements is known and will not need to change during the procedure. For example, a twelve-element array can hold one value for each month. Fixed arrays are straightforward, but their size cannot be adjusted with ReDim.

A dynamic array is declared with empty parentheses and allocated later. This is useful when the number of records depends on worksheet data or user input. ReDim creates its bounds, and it can be used again to resize the array. Without Preserve, resizing discards the values already stored.

Sub GrowNumberList()
    Dim numbers() As Long

    ' Allocate two elements, then grow to three without losing them.
    ReDim numbers(0 To 1)
    numbers(0) = 8
    numbers(1) = 13
    ReDim Preserve numbers(0 To 2)
    numbers(2) = 21
End Sub

In a multidimensional array, ReDim Preserve can change only the upper bound of the last dimension. It cannot add or remove a dimension, and it cannot resize an earlier dimension while preserving the existing contents. If a data structure needs to grow in a different direction, consider copying values into a newly dimensioned array or using a different structure, such as a collection.

Use Erase according to the array type. For a dynamic array it releases the allocated storage, so the array must be dimensioned again before indexing it. For a fixed array it resets the elements to their default values: numeric elements become zero, strings become empty, and Variant elements become Empty. Do not call LBound or UBound on a dynamic array that has not been allocated; that can raise a runtime error.

Looping Through Arrays

A For loop is the most direct way to process an array when you need its index as well as its value. The index is helpful when updating an element, matching it with another array, or reporting the position of a value. Declare the counter as Long, a common choice for VBA loop indexes.

A For Each loop is convenient when you only need to read each item and do not need its index. It is often easy to scan, but changing the loop variable does not replace the current array element. Use an indexed loop when the code must write back into an element or compare positions.

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

    ' Array creates a Variant containing an array.
    values = Array("Red", "Green", "Blue")

    For Each item In values
        Debug.Print item
    Next item
End Sub

The Array function is a quick way to make a Variant array from a list of values. Its bounds can depend on the module’s base setting, so check them with LBound and UBound if an index matters. A Variant can hold values of different types, which is convenient for mixed data but less explicit than a typed array such as Dim amounts() As Currency. Prefer a specific data type when all elements have the same meaning and type.

Multidimensional Arrays

A two-dimensional array uses two indexes, often thought of as rows and columns. It is useful for grid-shaped data, including a rectangular block of worksheet cells. Each dimension has its own lower and upper bounds, so use the dimension argument when checking the row or column range.

Sub ReadGrid()
    Dim grid(1 To 2, 1 To 3) As Variant
    Dim rowIndex As Long
    Dim columnIndex As Long

    ' Fill a two-row, three-column array.
    grid(1, 1) = "A"
    grid(1, 2) = "B"
    grid(1, 3) = "C"
    grid(2, 1) = "D"
    grid(2, 2) = "E"
    grid(2, 3) = "F"

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

VBA supports arrays with more than two dimensions, but extra dimensions can make the meaning of each index harder to follow. If a procedure has many dimensions, document what each one represents or consider separating the data into clearer structures. For ordinary worksheet tables, a two-dimensional array is usually the natural fit.

Moving Data Between Arrays and Excel Ranges

For bulk worksheet work, reading a rectangular range into a Variant array, processing the values in memory, and writing the result back can avoid repeatedly communicating with individual cells. When a multi-cell range’s Value property is assigned to a Variant, Excel supplies a two-dimensional array. Its row and column indexes are normally one-based. A one-cell range is a special case: its value is a scalar rather than a two-dimensional array.

Sub ReadAndWriteBlock()
    Dim ws As Worksheet
    Dim data As Variant

    ' Read the three-by-two block into a Variant array.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    data = ws.Range("A1:B3").Value

    ' Change the first value in memory and write the block back.
    data(1, 1) = "Updated"
    ws.Range("A1:B3").Value = data
End Sub

This example assumes the worksheet is named Sheet1 and that A1:B3 is the intended six-cell block. Because the array is written back to the same size range, its dimensions match the destination. If the destination range is too small or too large, the result may not be what the code intends. Qualifying the worksheet through ThisWorkbook also avoids accidentally reading from whichever sheet happens to be active.

To process the returned block, loop over dimension one for rows and dimension two for columns. A common mistake is treating a one-cell range as an array and immediately using two indexes; handle that case separately when the range size can vary down to one cell. Another is assuming worksheet rows start at array index zero. For a range-derived two-dimensional array, use its actual bounds or the known one-based layout.

Useful Array Operations

VBA includes a few basic functions that work naturally with arrays. Split breaks a delimited string into a zero-based array of strings, while Join combines a string array into one string. These are useful for simple text handling, but they do not replace a structured table when values can contain the delimiter or require more complex parsing.

Sub SplitAndJoinText()
    Dim parts() As String
    Dim combined As String

    ' Split at each comma, then join with a readable separator.
    parts = Split("oak,pine,maple", ",")
    combined = Join(parts, " | ")
    Debug.Print combined
End Sub

The result printed is oak | pine | maple. For array size, UBound - LBound + 1 gives the number of elements in a one-dimensional allocated array. VBA does not provide a single built-in function that safely returns the number of elements for every possible unallocated or differently shaped array; code should know whether it has allocated the array before asking for its bounds.

Choosing an Array Type and Avoiding Common Errors

Choose a typed array when all values share a known type: for example, Long for whole-number counts, Double for floating-point calculations, and String for text. A Variant array is flexible and is commonly used for data read from Excel, but it uses Variant elements and can conceal inconsistent data types. A Variant variable can also contain an entire array, as in the Array and range examples above.

Use explicit bounds when the intended index range is important.
Use LBound and UBound instead of assuming a starting index.
Use ReDim Preserve only when retaining existing values is necessary, and remember its last-dimension limitation.
Check that a dynamic array has been allocated before using its bounds or indexing it.
Match the dimensions of a range destination to the array being written.
Use descriptive names such as rowIndex and columnIndex for multidimensional loops.

Out-of-range subscripts commonly occur when a loop starts at the wrong bound, goes one step beyond UBound, or confuses a count with an upper index. A type mismatch can occur when code puts a value into an array element that cannot accept it, or when it treats a scalar range result as an array. When debugging, inspect the array’s bounds and the value’s type before changing the loop or adding error suppression. Broad use of On Error Resume Next can hide the actual problem.

Frequently Asked Questions

How do I find the size of a VBA array?
For an allocated one-dimensional array, calculate UBound(array) - LBound(array) + 1. For multidimensional arrays, do this separately for each dimension, supplying its dimension number to LBound and UBound.

Are VBA arrays zero-based or one-based?
They can be either. The default lower bound is zero unless Option Base 1 changes the default for implicit bounds in that module, and explicit bounds specify the starting index directly. Arrays returned from worksheet ranges are normally one-based in both dimensions.

What is the difference between Dim and ReDim?
Dim declares a variable, including a dynamic array with empty parentheses. ReDim allocates or resizes that dynamic array after its declaration. Use Preserve with care when existing elements must remain unchanged.

Can ReDim Preserve resize a two-dimensional array?
It can change the upper bound of the last dimension while preserving values. It cannot preserve values while changing the first dimension or changing the number of dimensions. If the first dimension must grow, copy the data into another array or choose a data structure suited to the change.

Why does LBound fail on my dynamic array?
A dynamic array that has not yet been allocated with ReDim has no usable bounds, so calling LBound or UBound can raise an error. Track whether allocation has occurred, or structure the procedure so bounds are queried only after the array is dimensioned.

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.