How ForEach works in VBA

Repeats a group of statements for each element in an array or collection.

Syntax

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

Next [element]

Argument	Description
element	Required. 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..
datatype	Required if element isn't already declared. Data type of element.
group	Required. A variable with a type that's a collection type or Object. Refers to the collection over which the statements are to be repeated.
statements	Optional. One or more statements between For Each and Next that run on each item in group.
Continue For	Optional. Transfers control to the start of the For Each loop.
Exit For	Optional. Transfers control out of the For Each loop.
Next	Required. Terminates the definition of the For Each loop.

Remarks

The For…Each block is entered if there is at least one element in group. Once the loop has been entered, all the statements in the loop are executed for the first element in group. If there are more elements in group, the statements in the loop continue to execute for each element. When there are no more elements in group, the loop is exited and execution continues with the statement following the Next statement.

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 nest For...Each...Next loops by placing one For…Each…Next loop within another. However, each loop element must be unique.

Note :

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

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

This example uses the For Each...Next statement to search the Text property of all elements in a collection for the existence of the string "Hello". In the example,

MyObject is a text-related object and is an element of the collection

MyCollection. Both are generic names used for illustration purposes only.

Sub ForEach1() Dim Found, MyObject, MyCollection Found = False    ' Initialize variable. For Each MyObject In MyCollection    ' Iterate through each element.     If MyObject.Text = "Hello" Then    ' If Text equals "Hello".         Found = True    ' Set Found to True.         Exit For    ' Exit loop.     End If Next End Sub click here