Excel VBA Array Loops: For, For Each, and Examples

VBA & macros · Updated November 21, 2026

To loop through an array in Excel VBA, use For Each when you need each value, or a For loop with LBound and UBound when you need an element’s index. For example, For Each item In values visits every element in the array stored in values.

Choose the loop that fits the task

VBA provides two common ways to traverse an array. For Each is concise and works well when the operation depends on the value itself, such as printing values or checking each item. A traditional For loop gives you the current index, which is useful when you need to update a particular element, compare neighboring elements, or keep track of position.

Here is a small example using both styles. It creates an array of strings, prints each value, and then prints each value with its index:

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

    ' Create an array and print each value.
    values = Array("North", "South", "West")
    For Each item In values
        Debug.Print item
    Next item

    ' Use indexes when the position matters.
    For i = LBound(values) To UBound(values)
        Debug.Print i, values(i)
    Next i
End Sub

Open the VBA editor with Alt+F11 in Excel for Windows. On a Mac, Option+F11 commonly opens it; some keyboards require Fn as well, or you can open the Visual Basic Editor from Excel’s Tools menu. Save a copy of the workbook before running a macro: VBA changes cannot be reversed with Ctrl+Z (or Command+Z on Mac).

How For Each works

For Each visits each element in an array in sequence. In a typical array loop, the control variable—the variable immediately after For Each—should be declared as Variant. This is different from a numeric index variable, which is usually declared as Long. The loop ends automatically after the final element, so you do not have to calculate its upper bound.

Use this pattern when the array is a collection of values and you do not need to know where a value sits:

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

    values = Array("Red", "Green", "Blue")
    For Each item In values
        Debug.Print item
    Next item
End Sub

Debug.Print sends output to the Immediate window in the VBA editor, which you can display with Ctrl+G on Windows. A loop can instead assign values to cells, add them, or call another procedure. Be mindful that a For Each variable represents the current value, not a reliable index. If you need to change an element by its position, use an indexed loop.

Loop by index with LBound and UBound

An indexed loop uses a counter to refer to each array position. Do not assume that the first position is always zero or one. VBA arrays can have different lower bounds, depending on how they were created and on declarations such as Option Base. The functions LBound and UBound return the actual lower and upper bounds for a dimension.

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

    values = Array(12, 18, 25)
    For i = LBound(values) To UBound(values)
        Debug.Print "Index " & i & " has value " & values(i)
    Next i
End Sub

For this example, the output includes each value and the index VBA assigned to it. Using LBound rather than hard-coding 0 makes the loop adaptable if the array’s lower bound differs. For a multidimensional array, provide the dimension number, such as LBound(data, 1) and UBound(data, 2).

  • Use LBound(array) and UBound(array) for the first dimension.
  • Use LBound(array, dimension) and UBound(array, dimension) for a specified dimension.
  • Use a Long counter for ordinary indexed loops; it is the standard practical choice for array positions in VBA.

Loop through a worksheet range stored in an array

A common reason to loop through an array is to process worksheet data. Assigning a multi-cell range’s Value property to a Variant variable loads the cell values into a two-dimensional array. The first dimension represents rows and the second represents columns. You can then process the values in memory rather than reading each cell separately inside the loop.

Sub ReadRangeIntoArray()
    Dim data As Variant
    Dim r As Long
    Dim c As Long

    ' Load A2:C10 from Sheet1 into a two-dimensional array.
    data = ThisWorkbook.Worksheets("Sheet1").Range("A2:C10").Value

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

The nested loops visit every row-and-column position in the loaded range. The two counters have different jobs: r selects the row dimension and c selects the column dimension. If you want to write modified data back, change the elements in data and assign the whole array to a same-sized destination range. For large blocks, this approach can reduce repeated worksheet reads and writes, which are often slower than working with values held in memory.

There is an important edge case: a range containing just one cell returns a single value rather than a two-dimensional array. Code that expects two dimensions can fail when the selected range sometimes shrinks to one cell. If the input size can vary that much, handle the single-cell case separately or ensure the range contains multiple cells.

Multidimensional arrays and row-column order

VBA supports arrays with more than one dimension. In a two-dimensional array, each element is addressed with two indexes, for example data(rowIndex, columnIndex). Use nested loops: the outer loop commonly traverses rows, while the inner loop traverses columns. The exact meaning of each dimension depends on how the array was created, so keep the data layout consistent throughout the procedure.

For a manually allocated array, dimensions are specified in the declaration or with ReDim. The following example uses a one-based two-dimensional array and fills it with a simple grid of values:

Sub FillGrid()
    Dim grid(1 To 2, 1 To 3) As Long
    Dim r As Long
    Dim c As Long

    For r = LBound(grid, 1) To UBound(grid, 1)
        For c = LBound(grid, 2) To UBound(grid, 2)
            grid(r, c) = r * 10 + c
            Debug.Print grid(r, c)
        Next c
    Next r
End Sub

The first row contains 11, 12, and 13; the second contains 21, 22, and 23. This makes the row and column positions visible in the values themselves. When traversing two-dimensional data, avoid using the first dimension’s bounds for the second dimension: each dimension has its own lower and upper bounds.

Dynamic arrays and changing their size

A dynamic array is declared without fixed bounds and receives its size later through ReDim. This is useful when the number of values is not known until the procedure runs. After allocating the array, loop through its current bounds just as you would with a fixed-size array.

Sub BuildDynamicArray()
    Dim values() As String
    Dim i As Long

    ReDim values(1 To 3)
    values(1) = "A"
    values(2) = "B"
    values(3) = "C"

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

To resize a dynamic array while retaining its existing values, VBA provides ReDim Preserve. There is a significant restriction: with a multidimensional array, Preserve lets you change only the upper bound of the last dimension. It cannot be used to freely resize every dimension. Also, calling LBound or UBound on a dynamic array that has not yet been allocated can produce an error. Allocate it before looping, or structure the procedure so that an empty or uninitialized array is handled first.

For Each versus For: which should you use?

Both loop types are useful; the choice depends on what the code needs to know. Choose For Each for straightforward value-by-value work. Choose an indexed For loop when the position matters, when you need to update the array by index, or when the algorithm compares one element with another.

  • Read or inspect every value: For Each keeps the code compact.
  • Use the current position: an indexed For loop gives you an explicit counter.
  • Compare neighboring values: use indexes so you can refer to positions such as i and i + 1, while ensuring the latter stays within the bounds.
  • Process two dimensions: use nested indexed loops to control rows and columns clearly.

Do not change the size of an array while a loop is traversing it unless the code is deliberately designed for that behavior. In particular, resizing an array during an indexed loop can make the original loop limits no longer match the current array bounds. Decide whether the bounds should be recalculated or whether the array should be resized before the loop begins.

Common errors and ways to avoid them

Many array-loop problems come from incorrect assumptions about bounds or data shape, rather than from the loop syntax itself. Check these details when code works for one input but fails for another:

  • Hard-coded starting index: An array may not start at zero or one. Use LBound and UBound instead of assuming its bounds.
  • Wrong number of dimensions: A range loaded from multiple cells is generally two-dimensional. Supply both row and column indexes, and use dimension-specific bounds.
  • Unallocated dynamic array: Allocate it before calling LBound, UBound, or attempting to read an element.
  • Single-cell range: Its value is a scalar, not a two-dimensional array. Do not send it through code that unconditionally expects two dimensions.
  • Wrong control-variable type: Use a Variant control variable for a For Each loop over an array; use a numeric type such as Long for an index.
  • Errors in worksheet cells: A range array can contain Excel error values such as #N/A. Check for error values with IsError before converting or performing arithmetic on an element.

When a loop appears to skip values, inspect the Immediate window output and confirm the bounds and dimensions. A small diagnostic print of the current index and value can reveal whether the issue is an unexpected lower bound, an empty input, or a mismatch between the worksheet range and the code’s assumptions.

Frequently Asked Questions

How do I loop through every item in a VBA array?
Declare a Variant item variable, then use For Each item In arrayName and close the loop with Next item. This visits each value without requiring an index.

How do I get the index while looping through an array?
Use a numeric counter in a For loop from LBound(arrayName) to UBound(arrayName). Access the current value with arrayName(counter).

Does a VBA array always start at zero?
No. The lower bound depends on how the array was declared or created and can be affected by Option Base in applicable cases. Check it with LBound rather than relying on a fixed starting index.

How do I loop through a range stored in an array?
Assign a multi-cell range’s Value to a Variant, then use nested loops with bounds for dimensions 1 and 2. Treat a one-cell range separately because it returns a scalar value.

Can Excel for the web run VBA macros?
No. Excel for the web can open workbooks containing VBA, but it cannot create, edit, or run VBA macros. Use the desktop Excel application for these tasks. Excel for Mac supports VBA, though some Windows-specific VBA features and integrations may differ.

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.