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.
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.
Name of an object collection or array (except an array of user-defined types).
Statement(s) to execute on each item in collection.
Provides a way to exit a For loop early.
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
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
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
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