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 asVariant, even though the array is declaredAs 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
Variantfor 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
LBoundandUBound, 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 withErase, 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
- Excel Text to Columns: How to Enter a Delimiter
- Excel Text to Columns: Can It Split Capital Letters?
- Excel Text to Columns: Split a Cell at Line Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Rows: Select, Count, and Manage Worksheet RowsIn Excel VBA, Rows refers to worksheet rows or the rows within a specified range. For example, Worksheets("Data").Rows(5)…
- Excel VBA Date Today: How to Insert the Current DateIn Excel VBA, use the built-in Date function to get today’s date. To put it in a worksheet…
- How to Use Macros on SteelSeries Keyboards and MiceTo use a macro on a SteelSeries keyboard or mouse, open SteelSeries GG, select the device in Engine,…
- Excel Macro Settings: What They Do and How to ChooseMacro settings are Excel’s security controls for deciding whether VBA macros in workbooks can run, and under what…
- VBA Send Mail from Excel: Outlook Setup and Code GuideYes. In Excel for Microsoft 365 on Windows, VBA can create and send an email through classic Outlook…
- Excel VBA Cell Color RGB: Set and Read ColorsIn Excel VBA, set a cell’s fill color with Range("A1").Interior.Color = RGB(255, 0, 0); that example makes the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.