| For Each...Next Statement |
Named Arguments
No
Syntax
For Each element In group [statements] [Exit For] [statements] Next [element]
element
Use: Required
Data Type: Variant or Object
A variant or object variable to which the current element from the group is assigned.
group
Use: Required
A collection, object collection, or an array.
statements
Use: Optional
A line or lines of program code to execute within the loop.
Description
Loops through the items of a collection or the elements of an array.
Rules at a Glance
The For...Each code block is executed only if group contains at least one element. If group is a dynamic array that has not yet been dimensioned or an empty collection, a syntax error (runtime error 92, "For loop not initialized," and 424, "Object required," respectively) results.
All statements are executed for each element in group in turn until either there are no more elements in group, or the loop is exited prematurely using the Exit For statement. Program execution then continues with the line of code following Next.
For Each...Next can't be used with an array of user-defined types, because you can't assign a user-defined type to a variant.
For Each...Next can't be used with an array of fixed-length strings.
For Each...Next loops can be nested, but each element must be unique. For example:
For Each myObj In AnObject For Each subObject In myObj SName = subObject.NameProperty Next Next
uses a nested For Each...Next loop, but two different variables, myObj and subObject, represent element.
Any number of Exit For statements can be placed with the For Each...Next loop to allow for conditional exit of the loop prematurely. On exiting the loop, execution of the program continues with the line immediately following the Next statement. For example, the following loop terminates once the program finds a name in the myObj collection that has fewer than 10 characters:
For Each subObject In myObj SName = subObject.NameProperty If Len(Sname) < 10 then Exit For End if Next
Programming Tips and Gotchas
Each time the loop executes when iterating the objects in a collection, an implicit Set statement is executed. The following code reflects the "long-hand" method that is useful for explaining what is actually happening during each iteration of the For Each...Next loop:
For i = 1 to MyObject.Count Set myObjVar = MyObject.Item(i) Debug.Print myObjVar.NameProperty Next i
Because the elements of an array are assigned to element by value, element is a local copy of the array element, and not a reference to the array element itself. This means that you can't make changes to the array element using For Each...Next and expect them to be reflected in the array once the For Each...Next loop terminates, as the following example demonstrates:
strNameArray(0) = "Paul" strNameArray(1) = "Bill" For Each varName In strNameArray varName = "Changed" Debug.Print strNameArray(0) Next
If you run the code through the VB or VBA development environment, note that on the first loop, although varName has been changed from "Paul" to "Changed," the underlying array element, strNameArray(0), still reports a value of "Paul." This proves that a referential link between the underlying array and object variable isn't present, and that, instead, the value of the array element is passed to element by value.
The Microsoft VBA documentation for the For Each...Next loop contains an example which is confusing, misleading, and incorrect. The example states, "The following code loops through each element in an array and sets the value of each to the value of the index variable I":
Dim TestArray(10) As Integer, i As Variant For Each i In TestArray TestArray(i) = i Next i
In fact, if you look at this code carefully, you'll see that on each loop, i has a value of 0. Therefore all this code does is assign a value of to element of the array 11 times! This example makes the mistake of combining the For Each...Next statement, which iterates each member of an array or collection in an arbitrary order, with the traditional For...Next statement, which iterates each member of an array in index order.