Yes—Excel VBA can use For Each to visit every element in an array. Declare the loop variable as Variant, then write For Each item In values; use an indexed For loop instead when you need an element’s position or want to change array values.
What For Each Does With an Array
A VBA array stores multiple values under one variable name. A For Each loop processes those values one at a time without requiring you to calculate the array’s lower and upper bounds. This makes it convenient for tasks such as displaying values, checking for a condition, or combining text from a list.
The loop variable receives the current element on each pass. When VBA reaches the end of the array, it continues with the statement after Next. The loop is about the values, not their positions: For Each does not give you an index such as 1, 2, or 3. If the position matters, use a numeric loop counter.
This syntax is for VBA code in desktop Excel, including Microsoft 365 for Windows. It is different from Excel worksheet formulas: you write it in a VBA procedure, not in a cell. Excel for the web does not run VBA macros; open the workbook in the desktop application to use them.
Basic Syntax and the Loop Variable
The pattern is short. In this example, the array is named values and each item is temporarily available as item:
For Each item In values
' Work with the current item.
Next item
For an array, declare the control variable as Variant. A variable declared as String, Long, or another specific value type commonly causes the compile error “For Each control variable must be Variant or Object.” The array itself can be held in a Variant, as it is when using VBA’s Array function, or in a suitably declared array variable.
Here is a complete procedure that prints each value in the Immediate window:
Sub PrintArrayValues()
Dim values As Variant
Dim item As Variant
' Array returns an array held here in a Variant.
values = Array("Red", "Green", "Blue")
For Each item In values
Debug.Print item
Next item
End Sub
Run the procedure, then view the output in the Visual Basic Editor’s Immediate window. On Windows, open the editor with Alt+F11; on a Mac, use Option+F11 (some keyboards also require Fn). In the editor, show the Immediate window with Ctrl+G on Windows. The loop in this example displays Red, Green, and Blue on separate lines.
Looping Through Values Read From Cells
A common reason to use an array is to process a worksheet range in memory. Assigning a multi-cell range’s Value property to a Variant creates a two-dimensional array. You can then use For Each to visit the values without referring to each worksheet cell separately.
Sub PrintRangeValues()
Dim values As Variant
Dim item As Variant
' Read four worksheet cells into a Variant array.
values = ThisWorkbook.Worksheets("Sheet1").Range("A2:A5").Value
For Each item In values
Debug.Print item
Next item
End Sub
For this example, put the values to process in cells A2:A5 on Sheet1. The procedure prints those values in the Immediate window. Reading a range into an array can also reduce repeated communication with the worksheet when a macro processes many cells. However, the array contains values from the range; changing a loop variable does not write the change back to the cells. To update the worksheet, modify an array and assign it back to the range, or write to the cells explicitly.
There is an important edge case: a multi-cell range returns a two-dimensional array, but a one-cell range’s Value is a single value rather than a two-dimensional array. Code that needs to handle both cases should check the input or use a consistent range size. Also consider what blank cells, error values, and formulas’ calculated results mean for the particular task.
When to Use an Indexed For Loop
Use For Each when you only need to process each element and do not need its index. Use an indexed loop when you need to refer to an element by position, update values in the array, or work with row and column coordinates. Array bounds are not always zero-based, so use LBound and UBound rather than assuming the first index.
Sub CapitalizeArrayValues()
Dim values As Variant
Dim i As Long
' Create a zero-based array of text values.
values = Array("north", "south", "west")
For i = LBound(values) To UBound(values)
values(i) = UCase$(CStr(values(i)))
Next i
Debug.Print values(0), values(1), values(2)
End Sub
This procedure changes the array elements to uppercase and prints NORTH, SOUTH, and WEST. It uses an index because the goal is to assign a new value to each specific element. By contrast, assigning a new value to the item variable in a simple For Each loop should not be treated as a reliable way to edit the original array. Use indexes for value-type array updates.
- Choose
For Eachto read or inspect every value without needing positions. - Choose an indexed loop to change array elements or use their indexes.
- Choose nested indexed loops for code that needs explicit row and column positions in a two-dimensional array.
Bounds and Multidimensional Arrays
VBA arrays can have different lower bounds. An array created with Array is normally zero-based, while a range read into an array is normally indexed starting at 1 in each dimension. A declaration using Option Base can also affect the default lower bound of arrays declared in that module, but it does not make it safe to guess bounds for every array source. LBound(array) and UBound(array) report the limits for a dimension.
For a two-dimensional array, pass a dimension number to those functions. For example, dimension 1 is the first dimension and dimension 2 is the second. A nested loop is useful when the code must identify each row and column explicitly:
Sub PrintTwoDimensionalArray()
Dim values As Variant
Dim rowIndex As Long
Dim columnIndex As Long
' Read a three-row by two-column range.
values = ThisWorkbook.Worksheets("Sheet1").Range("A2:B4").Value
For rowIndex = LBound(values, 1) To UBound(values, 1)
For columnIndex = LBound(values, 2) To UBound(values, 2)
Debug.Print rowIndex, columnIndex, values(rowIndex, columnIndex)
Next columnIndex
Next rowIndex
End Sub
Here, A2:B4 supplies six values. The loops visit all six positions, and the output includes each array index alongside its value. Do not confuse array indexes with worksheet row numbers: the first array index for this range is typically 1, although its first worksheet row is 2. To calculate the worksheet row from this particular range, add the array row index to 1.
Common Errors and How to Fix Them
Most problems with array loops come from the control variable, the kind of value being looped over, or assumptions about bounds and dimensions. Check these points when the code does not compile or stops unexpectedly:
- Control variable has the wrong type: For array elements, declare it as
Variant. Do not declare it asStringjust because the array currently contains text. - The source is not an array: A variable holding a single value cannot be used as though it were an array. Confirm that the array assignment succeeded and that the source is actually an array.
- A one-cell range was assumed to be an array: A single cell usually returns a scalar value. Account for that case before attempting to enumerate the result.
- The code assumes a zero lower bound: Use
LBoundandUBoundfor indexed loops. Range-derived arrays commonly begin at 1, unlike arrays returned byArray. - The code needs to edit the original array: Use an indexed loop for reliable assignment to array elements, then assign the completed array back to a worksheet range if needed.
- The array contains unexpected values: A worksheet range can include empty cells or error values. Check or handle those values before converting them to text or numbers.
If an array comes from a function or another procedure, inspect how that source represents its data. In particular, a Variant may hold either an array or a scalar. Code that receives different kinds of input should validate the input before starting the loop instead of assuming every call returns the same structure.
Choosing the Right Loop for the Task
The main advantage of For Each is readability: it expresses “do this for every item” without exposing bounds or index arithmetic. That can make simple checks and output loops easier to follow. It is not automatically the best choice for every array task, though. An index is essential for changing a value in place, comparing neighboring elements, or mapping a two-dimensional array back to worksheet coordinates.
For a collection of Excel objects, such as worksheets, For Each is also a natural fit, but that is an object-collection loop rather than a loop over ordinary array values. Object variables are normally declared as an object type, such as Worksheet. Keep the distinction clear: array elements that are ordinary values call for a Variant control variable, while object collections can use an appropriate object variable.
For many worksheet-processing tasks, a practical pattern is to read a range into an array, use indexed loops when coordinates or edits matter, and write the finished array back in one operation. For a short list that only needs checking or reporting, For Each may be simpler. Choose based on whether the code needs positions and whether it must change the data—not on the assumption that one loop form is always faster.
Running VBA Safely in Excel
Put VBA procedures in the Visual Basic Editor. In Excel for Microsoft 365 on Windows, you can open it with Alt+F11; on Mac, Option+F11 may require the Fn key depending on the keyboard. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon on Windows. On Mac, use Excel > Preferences (or Settings, depending on the release) > Ribbon & Toolbar and turn on Developer.
Save a copy of the workbook before running a macro, especially if the code changes cell contents. VBA actions cannot be undone with Ctrl+Z on Windows or Command+Z on Mac. A procedure that only uses Debug.Print outputs to the editor and does not change cells, but review any macro before running it and make sure it refers to the intended workbook and sheet. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to preserve the code.
Frequently Asked Questions
Can I use For Each with an array in Excel VBA?
Yes. Use For Each item In values, with item declared as Variant for ordinary array elements. The loop visits the array’s values without requiring you to specify index bounds.
Does For Each give me the array index?
No. The control variable contains the current element, not its position. Use a numeric For loop with LBound and UBound if your code needs indexes.
Can For Each change the values in an array?
Do not rely on assigning to the loop variable to update an ordinary value-type array element. Use an indexed loop to assign directly to each element. If the array came from a worksheet range, write the updated array back when you want to change the cells.
Why does VBA say the For Each control variable must be Variant or Object?
The loop variable is likely declared as a specific value type, such as String or Long. For ordinary array elements, change its declaration to Variant. Object arrays or collections use an appropriate object variable.
Can I use For Each on a range array?
Yes. Assign a multi-cell range’s Value to a Variant, then loop through that array. Remember that a multi-cell range produces a two-dimensional array and a one-cell range generally returns a scalar, so handle the single-cell case separately if the same code must support it.
Related
- Excel Split Contents of a Cell Into Two Cells
- Excel Split Multiline Cell Into Rows
- Excel Split Empty Cells Into Multiple Rows?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: How to Add a Threaded Comment to a CellIn Excel for Microsoft 365 on Windows, add a threaded comment to a cell with the VBA method…
- Excel VBA InputBox: Ask for User InputIn Excel VBA, use InputBox to display a simple prompt and collect a response, or use Application.InputBox when…
- Concat in Excel VBA: Join Text with the & OperatorTo concatenate text in Excel VBA, use the ampersand operator, &, to join values, such as fullName =…
- VBA Sort Column Ascending in ExcelTo sort a column in ascending order with VBA, use Excel’s Range.Sort method and set Order1:=xlAscending. For example,…
- MS Access VBA Dictionary: Setup, Examples, and TipsYes. Microsoft Access VBA can use the Scripting.Dictionary object to store key-value pairs, look up a value by…
- VBA Pivot Table Update: Refresh Tables and ReportsTo update a PivotTable with VBA, call its RefreshTable method; to refresh every PivotTable and external data range…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.