VBA Loop Array: Examples, Bounds, and Best Practices

A VBA array loop visits array elements one at a time, usually with a For loop using LBound and UBound, or with For Each. For example, use For i = LBound(values) To UBound(values) when you need an element’s index; use For Each when you only need each value.

Arrays let VBA hold a group of related values in memory, so a loop can inspect, calculate, or change those values without repeatedly reading individual cells. The right pattern depends on whether the array is one-dimensional or two-dimensional, how it was created, and whether the loop needs to write results back to a worksheet.

What an Array Loop Does

An array is a variable that contains multiple elements, each identified by an index. A one-dimensional array is like a list; a two-dimensional array is like a table with rows and columns. A loop repeats a set of instructions for each relevant index or value.

For instance, a macro might place a list of numbers in an array, multiply each number by a factor, and print or store the results. A worksheet range can also be loaded into an array, processed in memory, and then written back. The loop itself does not make an array faster automatically: the main speed advantage comes from moving a block of worksheet data into memory and avoiding repeated cell-by-cell communication with Excel.

VBA arrays can be fixed-size or dynamic. A fixed-size array has its bounds declared in advance. A dynamic array is declared without bounds and sized later with ReDim. Both can be looped over; the key is to use the array’s actual bounds rather than assume that indexing starts at zero or one.

Choose Between For and For Each

Use a counted For loop when the element’s position matters, when you need to move through an array in a particular order, or when you need to update elements by index. The loop variable is commonly declared as Long, which is suitable for array indexes and worksheet row numbers.

Use For Each when the task is simply to handle every value and the index is irrelevant. For an array, declare the control variable as Variant. A For Each loop is convenient for reading values, but it does not directly give you the current index. If you need both the position and the value, use a counted loop instead.

Counted For: best when you need indexes, bounds, or precise control over the sequence.
For Each: best when you only need to process each array element.
Nested For loops: needed for a two-dimensional array when you must visit every row-and-column combination.

Do not assume that a For Each loop lets you safely resize or replace the array while iterating. Finish the iteration before changing the array’s dimensions or structure.

Use LBound and UBound for Safe Bounds

LBound(array) returns the lowest valid index, and UBound(array) returns the highest. Use both in a counted loop so that the code works with arrays whose bounds differ. A loop that starts at zero can fail for an array declared with a lower bound of one; a loop that starts at one can skip the first item of a zero-based array.

VBA arrays do not all share the same starting index. A declared array can specify its bounds, and a module can use an Option Base statement. Arrays returned by the VBA Array function can also be affected by the module’s base setting. Rather than relying on a remembered default, ask the array for its bounds.

For a multidimensional array, pass the dimension number: LBound(data, 1) and UBound(data, 1) identify the first dimension, while the second argument of 2 identifies the second dimension. In a typical worksheet array, dimension 1 represents rows and dimension 2 represents columns.

Loop Through a Simple Array

This example creates a Variant array of four numbers and prints each value in the Immediate window. In the VBA editor, open the Immediate window with Ctrl+G on Windows. The array bounds are detected at runtime, so the loop does not depend on a particular starting index.

Sub LoopThroughArray()
    Dim values As Variant
    Dim i As Long

    ' Create a small array for the example.
    values = Array(10, 20, 30, 40)

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

The Immediate window displays 10, 20, 30, and 40 on separate lines. To use For Each instead, declare a Variant control variable and place the processing statement inside the loop. This is useful when you do not need to know whether a value is the first, second, or later element.

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

    ' Store values in a Variant array.
    values = Array(10, 20, 30, 40)

    ' Process each value without using its index.
    For Each item In values
        Debug.Print item
    Next item
End Sub

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a copy gives you a way to recover if a later version of the code changes data unexpectedly.

Loop Through Values Loaded from Excel

For a range containing more than one cell, assigning its Value2 property to a Variant variable returns a two-dimensional array. Even a single-column range has a row dimension and a column dimension, so read an element as data(rowIndex, 1), not just data(rowIndex). The following macro reads A2:A5 on the active sheet and prints each cell value.

Sub LoopThroughRangeArray()
    Dim ws As Worksheet
    Dim data As Variant
    Dim rowIndex As Long

    ' Use the currently active worksheet.
    Set ws = ActiveSheet

    ' Load the range into memory as a two-dimensional array.
    data = ws.Range(A2:A5).Value2

    ' Visit each row in the first column.
    For rowIndex = LBound(data, 1) To UBound(data, 1)
        Debug.Print data(rowIndex, 1)
    Next rowIndex
End Sub

In actual VBA code, a worksheet range address must be supplied as a string. The correct assignment line is data = ws.Range("A2:A5").Value2; the quotation marks tell VBA that A2:A5 is an address, not a variable. The macro prints four values if the four cells are populated, and prints blank entries for empty cells.

A single-cell range is a special case: its Value or Value2 result is a single value rather than the usual two-dimensional array. Code designed to index a range result as data(rowIndex, columnIndex) should therefore work with a range of at least two cells, or explicitly handle the one-cell case separately.

Loop Through a Two-Dimensional Array

For a rectangular block, use nested loops: the outer loop moves through rows, and the inner loop moves through columns. This example reads A2:B5 and prints every item, including the row and column indexes. The bounds are checked independently for each dimension, so the loop adapts if the range’s dimensions change.

Sub LoopThroughTableArray()
    Dim ws As Worksheet
    Dim data As Variant
    Dim rowIndex As Long
    Dim columnIndex As Long

    ' Use the currently active worksheet.
    Set ws = ActiveSheet

    ' Read a two-column, four-row range into memory.
    data = ws.Range(A2:B5).Value2

    ' Visit every row and column.
    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

As with the single-column example, use quotation marks around the range address in executable code: ws.Range("A2:B5").Value2. If the range has four rows and two columns, the nested loops make eight visits. For code that processes a range whose dimensions are known in advance, it is still safer to use LBound and UBound than to hard-code a final row or column index.

Process Data in Memory and Write It Back

A common Excel pattern is to read a range into an array, make changes in the array, and assign the entire array back to a range of matching dimensions. This reduces worksheet read and write operations, which can matter when processing thousands of cells. Before using this pattern, confirm that every value supports the operation you intend to perform and that the destination range is the same size as the array.

For example, if B2:B5 contains numeric values, a loop can add 10 to each in-memory value and write the result back to B2:B5. The bounds of a multi-cell range array usually start at one, but the code still uses LBound and UBound to make the assumption explicit. In a real macro, add checks for blanks, text, or cell errors if those may occur; a calculation on an error value can stop the procedure.

When using ReDim Preserve with a multidimensional array, VBA permits changing only the last dimension while preserving existing values. This constraint can affect designs that build a table one row at a time. If the data shape is not known, consider collecting values in a suitable structure or sizing the array once when the required row and column counts are available.

Common Array Loop Problems

Subscript out of range: an index is outside the array’s bounds, or the code uses the wrong dimension. Check both LBound and UBound for the dimension being accessed.
Wrong number of subscripts: the code treats a range array as one-dimensional even though a multi-cell range result has two dimensions. Use both row and column indexes.
Type mismatch: an element may contain text, an Excel error, or another value that cannot be used in the intended calculation. Test the value before converting or calculating with it.
One-cell range behaves differently: a one-cell range returns a scalar rather than a two-dimensional array. Handle this case explicitly if the input range can shrink to one cell.
Unexpected index base: code assumes every array starts at zero or one. Use LBound and UBound instead.
Slow cell-by-cell processing: repeatedly reading and writing worksheet cells inside a large loop can be slower than transferring a block to an array, processing it in memory, and writing it back once.

When the macro reports an error, step through it with F8 in the Windows VBA editor and inspect the current index and the array bounds. A useful debugging check is to print LBound and UBound for each dimension before the loop. That helps distinguish an indexing error from a problem in the data itself.

Where to Run VBA and How to Protect the Workbook

In desktop Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11. On a Mac, Option+F11 is the usual equivalent; some keyboards require Fn as well. Excel for the web does not run VBA macros, so use the desktop application for these procedures. Ribbon and keyboard details can vary by Mac keyboard and Excel configuration.

To keep VBA code in a workbook, save it in a macro-enabled Excel format such as .xlsm. A standard .xlsx workbook does not retain VBA macros when saved. Excel may block macros based on security settings or the file’s origin; only run code from a source you trust. Keep a backup before a macro that edits cells, because changes made by VBA cannot be reversed with Ctrl+Z.

Frequently Asked Questions

How do I loop through an array in VBA?
Use a counted For loop from LBound(array) to UBound(array). For a two-dimensional array, specify the dimension in each bound call and provide both row and column indexes.

Should I use For or For Each for a VBA array?
Use For when you need the element’s index or control over the order. Use For Each when you only need to process each value; declare its array control variable as Variant.

How do I loop through an Excel range stored in an array?
Assign a multi-cell range’s Value2 to a Variant variable, then loop through its row and column dimensions. A range such as A2:A5 is still a two-dimensional array, so access its values with a row index and column index of 1.

Do VBA arrays start at zero or one?
It depends on how the array was created and the module’s settings. Use LBound to find the actual lower bound rather than assuming a starting index.

Why does my VBA range array give a subscript error?
Common causes include using one index for a two-dimensional range array, starting at an invalid index, or looping beyond the last element. Check the bounds for the correct dimension and account for a one-cell range, which returns a scalar.

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.