For Each Loop

VBA – For Each Loop

The For Each...Next loop is a powerful control flow structure in VBA that simplifies iterating through collections of objects.

This is repeated for every element in a group and is carried out if there is at least one element in the group.

A For Each loop is similar to For Loop; however, the loop is executed for each element in an array or group. Hence, the step counter won’t exist in this type of loop. It is mostly used with arrays or used in context of the File system objects in order to operate recursively.

Explanation:

element: A variable that represents the current element in the collection during each iteration.
collection: The collection of objects to iterate through (e.g., an array, a range of cells, a collection of shapes).

Syntax:

For Each element In collection
‘ Code to be executed for each element
Next element

Example: Looping through an Array

				
					Sub LoopThroughArray()
    Dim myArray() As Variant
    myArray = Array("Apple", "Banana", "Orange")

    For Each fruit As Variant In myArray
        MsgBox fruit
    Next fruit
End Sub

This code will display a message box for each fruit in the array.

Example: Looping through a Range of Cells

				
					Sub LoopThroughCells()
    Dim cell As Range

    For Each cell In Range("A1:A5")
        cell.Value = "Hello"
    Next cell
End Sub

This code will set the value of cells A1 to A5 to “Hello”. Ref. image img-01

Key Points:

The For Each...Next loop is designed to work with collections of objects.
You don’t need to know the number of elements in the collection beforehand.
The For Each...Next loop can be used to iterate through various collections, such as:
- Worksheets: For Each ws In ThisWorkbook.Worksheets
- Shapes: For Each shp In ActiveSheet.Shapes
- Cells: For Each cell In Range("A1:A10")

By using the For Each...Next loop, you can efficiently process collections of objects and perform repetitive tasks with greater ease.