The For Each Loop

The For Each loop is a variation on the For loop that was designed to iterate through a collection of objects (as well as through elements in an array) and is generally much more efficient than using the traditional For loop. The general syntax is:

For each ObjectVar In CollectionName
   ' block of code goes here . . .

Next ObjectVar

where ObjectVar is a variable of the same object type as the objects within the collection. The code block will execute once for each object in the collection.

The FindFirstNonEmpty procedure shown in Example 8-1 illustrates the For Each loop.

Thus, when iterating through a collection of objects, we have two choices:

For Each object in Collection
   ' code block here
Next object

or:

For i = 1 to Collection.Count
   ' code block here
Next i

It is important to keep in mind that the For Each loop can be much faster than the For loop when dealing with collections of Excel objects. Thus, except for small collections, it is the preferred method.