Excel 2019

For Each … Next Loop

There is another type of For loop in VBA: the For Each…Next loop. It is similar to the For…Next loop. However, it works with collections (a group of objects of the same type). The iterations are based on the number of objects in a collection, such as the worksheets collection in a workbook or the cells collection in a range. Unlike the For…Next loop you might have no idea how many objects are in the collection, so you don’t know how many times to go through the loop but VBA will know during the execution of loop.

Syntax

The syntax for the For Each…Next statement is:

For Each element In collection
[ statements ]
[ Exit For ]
[ statements ]
Next [ element ]

Note: the square brackets indicate the optional parts of loop.

element
Variable used to iterate through the elements of the collection or array. For collections, element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, element can only be a Variant variable.

collection
Name of an object collection or array (except an array of user-defined types).

statements
Statement(s) to execute on each item in collection.

Exit For
Provides a way to exit a For loop early.

Next
VBA starts by evaluating the number of objects in the specified collection. It then executes the statements in the loop for the first of those objects. When it reaches the Next keyword, it loops back to the For Each line, re-evaluates the number of objects, and performs further iterations as appropriate. When there are no more elements in collection, the loop is exited and execution continues with the statement following the Next statement.

Examples

For example you want to perform some action on all objects in a collection or you want to evaluate all objects in a collection and take action under certain conditions. These occasions are perfect for the For Each…Next loop because you don’t have to know how many elements are in a collection to use the For Each…Next loop.

The following example uses the For Each…Next loop with the Worksheets collection in the active workbook. When you execute the code, the Immediate window displays each worksheet’s Name property:

Sub TheFor_Each_Next_Loop()
 Dim ws As Worksheet
 For Each ws In ActiveWorkbook.Worksheets
  Debug.Print ws.Name
 Next ws
End Sub

For Each Loop Example

Press Alt+F11 to open Visual Basic Editor (VBE), write the code in ThisWorkbook (as shown in the image) and run code by pressing the F5 button (or from the VBE menu) and the worksheet’s names will display on Immediate window.

Early Exit For Each…Next Loop

Sometimes you need to exit a For loop prematurely. This is possible with the Exit For statement. It immediately takes you out of the loop. In following we'll exit from the loop if the worksheet name is brainbell:

Sub TheFor_Each_Next_Loop()
 Dim ws As Worksheet
 For Each ws In ActiveWorkbook.Worksheets
   If ws.Name = "brainbell" Then
    Debug.Print "Exit"
    Exit For
  End If
 Debug.Print ws.Name
 Next ws

For this example, we renamed Sheet2 to brainbell.

For Each Loop Exit Example

Advertisement:
Advertisement: