To loop over a VBA array in Excel, use For Each when you need to process every item, or a numbered For loop with LBound and UBound when you need an element’s position. For example, For Each item In values visits each element in the array; the examples below show how to write the complete loops and when to choose each pattern.
What an array loop does
An array stores multiple values under one variable name, and a loop runs the same instructions against those values one at a time. Arrays are useful in VBA when you want to hold a group of values in memory, work through data read from worksheet cells, or perform repeated calculations without writing a separate instruction for every item.
In Excel VBA, an array can be fixed-size or dynamic, one-dimensional or multidimensional, and can contain values of a particular data type or values of different types. Those differences matter when choosing a loop. A simple list of names is easy to traverse with For Each; a two-dimensional block of worksheet data is usually clearer with nested row and column loops.
Loop through every item with For Each
Use For Each when the task is simply to do something with every element and you do not need its index. The control variable should be declared as a Variant when iterating an array. This example prints three text values to the Immediate window in the Visual Basic Editor:
Sub PrintArrayItems()
Dim values As Variant
Dim item As Variant
' Array creates a zero-based array in this example.
values = Array("North", "South", "West")
For Each item In values
Debug.Print item
Next item
End Sub
The loop body runs once per element. Debug.Print displays each value in the Immediate window, which you can show in the Visual Basic Editor with Ctrl+G on Windows. On Mac, open the Visual Basic Editor and use View > Immediate Window; keyboard shortcuts can vary by keyboard and Excel release.
The variable item receives each value as the loop progresses. This is convenient for reading or processing elements, but it does not tell you the current index. If the code needs to update an array element, compare an index with a limit, or report a position, use an indexed loop instead.
Use LBound and UBound for indexed loops
A numbered For loop gives access to each array index. Do not assume an array starts at zero or one: its lower bound depends on how it was declared or created. LBound returns the lowest valid index and UBound returns the highest. Using both makes the loop adapt to the array’s actual bounds.
Sub PrintArrayWithIndex()
Dim values As Variant
Dim i As Long
values = Array("North", "South", "West")
For i = LBound(values) To UBound(values)
Debug.Print i, values(i)
Next i
End Sub
For this array, the printed indexes are 0, 1, and 2. The code does not hard-code those limits, so it also works if the array later changes size or has a different lower bound. Use the index syntax values(i) to read or assign the element at that position.
Declare the loop counter as Long for ordinary VBA array indexes. A Long can represent substantially more values than the older Integer type, and is a sensible default for counters and row numbers in Excel VBA.
Loop through a worksheet range array
For larger sets of worksheet values, a common pattern is to read a whole range into a Variant array, process it in memory, and write results back. Reading a multi-cell range with Value2 returns a two-dimensional array: the first dimension represents rows and the second represents columns. This example prints the values in A2:C6, one cell at a time.
Sub ReadRangeIntoArray()
Dim data As Variant
Dim rowIndex As Long
Dim columnIndex As Long
' Read the multi-cell range into a two-dimensional array.
data = ThisWorkbook.Worksheets("Sheet1").Range("A2:C6").Value2
For rowIndex = LBound(data, 1) To UBound(data, 1)
For columnIndex = LBound(data, 2) To UBound(data, 2)
Debug.Print data(rowIndex, columnIndex)
Next columnIndex
Next rowIndex
End Sub
The second argument to LBound or UBound specifies the dimension: 1 for rows and 2 for columns in this range array. The nested loops visit each row and then each column in that row. The sample assumes a worksheet named Sheet1 and a range with multiple cells; change the sheet name and address to match the workbook.
Reading a block into an array can reduce repeated communication between VBA and the worksheet, which is often useful when processing many cells. It also means that changes made to the array are not automatically written back to the cells. To update the worksheet, assign the finished two-dimensional array to a range of the same dimensions.
Change values while looping
Use an indexed loop when you want to change the values stored in an array. The following example reads five values from A2:A6, changes each one in memory, and writes the resulting array to B2:B6. In this illustration, it adds 10 to each numeric value.
Sub AddTenToRangeValues()
Dim data As Variant
Dim rowIndex As Long
' Save a copy of the workbook before running a macro.
data = ThisWorkbook.Worksheets("Sheet1").Range("A2:A6").Value2
For rowIndex = LBound(data, 1) To UBound(data, 1)
If IsNumeric(data(rowIndex, 1)) Then
data(rowIndex, 1) = data(rowIndex, 1) + 10
End If
Next rowIndex
ThisWorkbook.Worksheets("Sheet1").Range("B2:B6").Value2 = data
End Sub
For example, if A2:A6 contains 1, 2, 3, 4, and 5, then B2:B6 receives 11, 12, 13, 14, and 15. The IsNumeric check avoids trying to add 10 to text or an empty cell. Decide separately how the macro should handle error values, since they require more specific checking than ordinary text.
Macros cannot be undone with Ctrl+Z. Save a separate copy of the workbook before running code that changes data, especially when writing results to cells or replacing existing values. Test with a small sample first and confirm that the output range is the intended destination.
Handle two-dimensional and higher-dimensional arrays
A worksheet range containing several rows and columns is two-dimensional, so use nested loops when the code needs to know each row and column. A one-dimensional loop cannot correctly address a two-dimensional element by a single index; each element is addressed with two subscripts, such as data(rowIndex, columnIndex).
For Each can also iterate through the elements of an array without writing nested loops, but it does not provide row and column indexes. That makes it less suitable when processing depends on a particular column, matching a value to its row, or building a cell address. For arrays with more dimensions, use one loop per dimension when position matters, and pass the dimension number to LBound and UBound.
A noteworthy edge case is a range containing only one cell. Assigning that cell’s Value2 to a Variant gives a scalar value rather than the two-dimensional array returned for a multi-cell range. Code that might receive either a one-cell or multi-cell range should account for that difference instead of blindly using two subscripts. One simple design choice is to ensure the input range always contains more than one cell; otherwise, add explicit handling for a scalar result.
Fixed arrays, dynamic arrays, and bounds
A fixed-size array declares its bounds in advance, while a dynamic array is declared with empty parentheses and sized later with ReDim. For example, a dynamic array can be resized after the program determines how many values it needs. Whenever possible, use LBound and UBound rather than repeating a presumed first or last index in the loop.
The VBA statement Option Base can affect the default lower bound for arrays declared in a module, but it does not change every array’s bounds. For instance, the Array function normally creates a zero-based array, and an explicit lower bound in an array declaration or ReDim makes the intended range clear. The safest habit is to inspect bounds with LBound and UBound when the code should work with arrays created in different ways.
When resizing a dynamic array, ReDim normally discards existing contents. ReDim Preserve retains existing values, but VBA restricts which dimension can be changed while preserving a multidimensional array. If a growing collection needs frequent additions, repeatedly resizing it can also be inefficient; consider a suitable collection or another data structure when the size is not known in advance.
Common mistakes and practical fixes
- Hard-coding the loop limits: A loop from 0 to 9 fails if the array’s bounds or length change. Use
LBoundandUBoundfor the relevant dimension. - Using the wrong number of subscripts: A two-dimensional array element needs both a row index and a column index. Use nested loops and expressions such as
data(r, c). - Expecting a For Each loop to provide an index: The loop variable represents the current value, not its position. Use a numbered loop if position matters.
- Assuming a single-cell range is an array: A one-cell
Value2result is a scalar. Handle that case separately if the input size can vary. - Writing an array to the wrong-sized range: When assigning a two-dimensional array to cells, make the destination range match the array’s row and column dimensions.
- Forgetting that code changes may not reach the worksheet: Editing an array in memory changes the array only. Assign it back to a range to update cells.
Use Option Explicit at the top of a module to require variable declarations. It helps catch misspelled variable names before they cause confusing results. If a loop gives an error, check the array’s actual bounds and dimensions, confirm that the source range is not a single cell, and verify that each referenced element exists.
Choosing the right loop pattern
Pick the loop based on what the code needs to know. For Each is concise for tasks that treat every item alike, such as printing values or checking each value for a condition. A For loop is preferable when you need the element’s index, must change an element, or want to pair values by position. For worksheet data with rows and columns, nested indexed loops make the structure explicit.
- Use
For Eachto visit all array elements when their positions do not matter. - Use
For i = LBound(arrayName) To UBound(arrayName)for a one-dimensional array when you need its index. - Use nested loops and dimension-specific bounds for a two-dimensional array.
- Use an in-memory Variant array for bulk worksheet processing, then assign it back to a matching range if results should appear in cells.
These patterns apply to VBA in Excel for Microsoft 365 on Windows. The VBA language and array-loop syntax are also available in Excel for Mac, although the Visual Basic Editor interface and some keyboard shortcuts differ. Excel for the web does not run VBA macros; use desktop Excel to create or run this VBA code.
Frequently Asked Questions
How do I loop through an array in Excel VBA?
Use For Each item In arrayName to process every value, declaring item as a Variant. Use a numbered For loop with LBound and UBound if you also need each element’s index.
Should I use For Each or For Next for a VBA array?
Use For Each when you only need the values. Use For with an index when you need positions, want to update array elements, or are traversing a multidimensional array by row and column.
How do I loop through a two-dimensional array in VBA?
Use one loop for each dimension. For a range array, loop from LBound(data, 1) to UBound(data, 1) for rows and from LBound(data, 2) to UBound(data, 2) for columns.
Why does LBound or UBound cause an error?
Check that the array has been initialized and that the requested dimension exists. Also check whether a range supplied only one cell: its Value2 result is a scalar, not a two-dimensional array.
Can I change array values inside a For Each loop?
For reliable updates to array elements, use an indexed For loop and assign to arrayName(index) or the appropriate multidimensional element. A For Each control variable represents the current value, not a direct index for assigning back to the array.
Related
- Excel Text to Columns: Split at the Last Space
- Excel Text to Columns: Split at Line Breaks
- Excel Text to Columns Greyed Out? Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA LAMBDA: Calling Worksheet Functions from VBAExcel’s LAMBDA function is a worksheet feature, not a VBA language feature: VBA has no built-in anonymous-lambda syntax.…
- Excel VBA Derivative: Calculate a Function’s Rate of ChangeExcel VBA has no built-in function that symbolically derives any formula. If by “derive” you mean find a…
- VBA Coding Dictionary: How the Dictionary Object WorksA VBA Dictionary is an object that stores pairs of keys and values, letting an Excel macro look…
- Excel VBA For Each Array: How to Loop Through ValuesYes—Excel VBA can use For Each to visit every element in an array. Declare the loop variable as…
- Excel VBA: Get the Last Row Number ReliablyTo get the last nonempty row in a particular column, start at the bottom of that worksheet column…
- Excel VBA Date String Formatting ExplainedIn Excel VBA, use Format$ to turn a date value into a formatted text string, such as Format$(Date,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.