How to Use for Each Loop in Excel VBA?

A For Each loop is used to execute a statement or a set of statements for each element in an array or collection.

Syntax:

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

The For…Each…Next statement syntax has the following three parts:

Part	Description
element	Required (Must be mentioned). Variable is used to iterate through the elements of the collection or array. For collections, the element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, the element can only be a Variant variable.
group	Required(Must be mentioned). Name of an object collection or array (except an array of user-defined types).
statement	Optional (May or may not be mentioned). One or more statements are executed on each item in the group.

There are 4 basic steps to writing a For Each Next Loop in VBA:

• Declare a variable.
• Write the For Each Line with the variable and collection references.
• Add line(s) of code to repeat for each item in the collection.
• Write the Next line to terminate the loop.

The For…Each block is entered if there is at least one element in the group. Upon entering the loop, all the statements in the loop are executed for each element. When there are no more elements in the group, the loop is exited and execution continues with the statement following the Next statement. The next statement line terminates the loop.

Any number of Exit For statements may be placed anywhere in the loop as an alternative way to exit. Exit For is often used after evaluating some condition, for example, If…Then, and transfers control to the statement immediately following Next.

You can also nest For…Each…Next loops by placing one For…Each…Next loop within another. However, each loop element must be unique in its way.

NOTE

• Execution continues as if element is included, if you omit element in a Next statement.
• An error occurs, If a Next statement is encountered before its corresponding For statement,

You can’t use the For…Each…Next statement with an array of user-defined types because a Variant can’t contain a user-defined type.

Example 1

  Private Sub Demo_Loop()

        students is an array

       students = Array(“Akshit”, “Nikita”, “Ritesh”)  //Initialising Array-> students

      Dim studentnames As Variant  // Variable is assigned

‘iterating using For each loop.

For Each Item In students

     studentnames =studentnames & Item & Chr(10)

    Next

       MsgBox studentnames

End Sub

It would look somewhat like below:

When the above code is executed, it prints all the student names with one item in each line.