Excel VBA: How to Loop Through an Array of Strings

VBA & macros · Updated November 21, 2026

In Excel VBA, use For Each to visit every string in an array, or use a counter with LBound and UBound when you need the element’s position. For a straightforward example, store the strings in a Variant array and print each one:

Sub LoopThroughStrings()
    Dim names As Variant
    Dim item As Variant

    names = Array("Ava", "Ben", "Chloe")

    For Each item In names
        Debug.Print CStr(item)
    Next item
End Sub

This writes Ava, Ben, and Chloe on separate lines in the VBA Immediate window. The examples below apply to desktop Excel for Microsoft 365 on Windows; basic VBA array loops also work in desktop Excel for Mac.

How a For Each loop works

For Each takes one element from a collection or array at a time, assigns it to a control variable, runs the statements in the loop, and then moves to the next element. In the example, item holds the current value. The loop ends after the final array element, so you do not have to calculate the array’s length or increment a counter yourself.

For an array, declare the For Each control variable as Variant. VBA requires a Variant or Object control variable when iterating an array; declaring it as String can produce a compile error. The array itself may still be a typed string array. For example:

Sub LoopThroughTypedStringArray()
    Dim names(0 To 2) As String
    Dim item As Variant

    names(0) = "Ava"
    names(1) = "Ben"
    names(2) = "Chloe"

    For Each item In names
        Debug.Print CStr(item)
    Next item
End Sub

Here the array is explicitly declared as strings, while item is Variant so it can serve as the loop control variable. CStr converts the current value to a string for output; it is optional when you already know every array element is a string.

Loop by index when position matters

Choose an indexed loop if your code needs the element number, needs to compare neighboring elements, or must update a particular position. Use LBound and UBound rather than assuming that the first index is zero:

Sub LoopThroughStringsByIndex()
    Dim names As Variant
    Dim i As Long

    names = Array("Ava", "Ben", "Chloe")

    For i = LBound(names) To UBound(names)
        Debug.Print i, CStr(names(i))
    Next i
End Sub

The Immediate window shows each index beside its string. LBound returns the lowest valid subscript and UBound returns the highest. This matters because array bounds are not always zero-based: module-level Option Base can affect arrays created with Array, and arrays can be declared with explicit lower bounds. The bounds functions keep the loop aligned with the actual array.

In VBA, use a Long for an array index. It is the usual choice for counters and supports far more values than the older Integer type. An indexed loop does not automatically make an array editable by index; it simply gives you the current subscript so you can read or assign names(i) as appropriate.

Which loop should you choose?

For a simple task applied to every string, For Each is usually easier to read and avoids off-by-one mistakes. Use an indexed loop when the index is part of the job. These examples illustrate the distinction:

  • Print every string, validate each value, or search for a matching string: use For Each.
  • Report row numbers, write a result back to the corresponding array position, or compare one item with the next: use an indexed loop.
  • Work with a typed string array using For Each: keep the control variable as Variant, even though the array is declared As String.
  • Handle a possibly empty or uninitialized dynamic array: check that it has been allocated before asking for its bounds or iterating it.

A For Each loop does not expose an index, and assigning a new value to the loop variable should not be treated as a way to replace the array element. If the goal is to change elements in place, loop by index and assign to the array element itself.

Loop through strings from worksheet cells

A common next step is to load names or labels from a worksheet and process them. This example reads column A below a header into a Variant array, then handles both a single data cell and multiple data cells. That distinction is important: Excel returns a two-dimensional array from a multi-cell range, but a single cell’s Value2 is a scalar rather than a one-element array.

Sub ReadStringsFromColumnA()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim values As Variant
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then Exit Sub

    If lastRow = 2 Then
        Debug.Print CStr(ws.Cells(2, "A").Value2)
    Else
        values = ws.Range("A2:A" & lastRow).Value2
        For i = 1 To UBound(values, 1)
            Debug.Print CStr(values(i, 1))
        Next i
    End If
End Sub

The code assumes row 1 contains a header and the data is in column A of Sheet1. Value2 returns the cell values without the Currency and Date conversions associated with Value. Since a multi-cell range is represented as a two-dimensional array, the row index is the first subscript and the single selected column is the second: values(i, 1). If the data is in a different column or starts on a different row, adjust the range and the corresponding last-row logic.

Search, skip blanks, and compare text

Inside the loop, you can apply normal string operations. For example, a search can stop as soon as it finds a match. This version uses an indexed array so that its bounds are explicit:

Sub FindStatus()
    Dim statuses As Variant
    Dim item As Variant

    statuses = Array("Open", "In progress", "Closed")

    For Each item In statuses
        If StrComp(CStr(item), "Closed", vbTextCompare) = 0 Then
            Debug.Print "Found the closed status"
            Exit For
        End If
    Next item
End Sub

StrComp with vbTextCompare compares text without distinguishing uppercase and lowercase, so “closed” would also match. Use vbBinaryCompare when capitalization should matter. Exit For leaves the current loop immediately; omit it if the code should find or process every match.

For values that may contain blank cells, whitespace, or errors, check them before converting or comparing. A blank string and a cell containing an Excel error are different values. In a worksheet-reading loop, use IsError before calling CStr on a value that might be an error; otherwise conversion can fail. Use Trim$ when leading and trailing spaces should not affect the comparison, but do not trim if those spaces are meaningful data.

Common array-loop problems

Several frequent errors come from treating all arrays as if they were created the same way. Check these points when a loop fails or skips data:

  • “For Each control variable must be Variant or Object.” Change the loop variable to Variant for an array. A String array declaration does not make a String control variable valid.
  • Subscript out of range. The loop may be using an index outside the array’s bounds. Use LBound and UBound, and confirm that the array has the dimension you expect.
  • Invalid procedure call or argument from LBound. A dynamic array that has not been allocated, or has been cleared with Erase, does not have usable bounds. Check whether your code initialized it before querying those bounds.
  • Type mismatch when reading a cell range. A one-cell range returns a scalar, while a larger range returns an array. Handle the one-cell case separately, or use a cell-by-cell approach if that makes the code clearer.
  • Unexpected blanks or duplicates. A loop faithfully visits every element; it does not automatically filter empty strings, remove spaces, or eliminate repeated values. Add those rules explicitly if the task requires them.

For an uninitialized dynamic array, VBA does not provide a universal, simple IsArray test that also proves the array has been allocated: a Variant can report that it contains an array while the array still has no usable bounds. Structure the code so allocation is known, or use a dedicated helper routine when allocation status must be checked in several places. Avoid calling LBound or UBound blindly in error-prone paths.

Performance and safe macro use

For a handful of strings, either loop style is appropriate; choose for clarity. When processing many worksheet cells, reading a range into a Variant array and looping over that in memory is generally more efficient than repeatedly asking Excel for each cell’s value. If you need to write results back, build an output array and assign it to a suitably sized range in one operation rather than making thousands of separate worksheet calls.

To run the examples in desktop Excel for Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and place the code there. Open the Immediate window with Ctrl+G to see Debug.Print output. On Mac, Option+F11 commonly opens the editor; some keyboards require Fn as well. Excel for the web can open a macro-enabled workbook but cannot create, edit, or run VBA macros. Before running a macro, save a copy of the workbook: VBA actions cannot be undone with Ctrl+Z (or Mac’s Command+Z).

Frequently Asked Questions

Can I use For Each with a VBA string array?

Yes. The array can be declared As String, but the For Each control variable must be a Variant for an array. A Variant array created with Array("A", "B") is also a common option.

Why does For Each require a Variant variable?

VBA requires the control variable used to iterate an array to be a Variant or Object. Declaring it as String can cause a compile error, even when every element in the array is a string.

How do I get the index in a For Each loop?

For Each does not provide the index. Use a counter loop from LBound(array) to UBound(array) when you need the element’s position.

How do I loop through an array backwards?

Use an indexed loop that starts at UBound(array) and counts down to LBound(array) with Step -1. This is useful when the processing order must be reversed.

Can Excel for the web run VBA loops?

No. Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac to execute VBA; the basic array-loop syntax is the same in both desktop versions.

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.