VBA For Each

Just like the For Next statement, For Each is a loop.

For Each is a type of loop that is normally used with arrays, matrices or collections (advanced topic). It works similarly to the For Next loop, however it performs an iteration for each element available in the array, array, or collection.

    Dim AnArray(2) As String

AnArray(0) = "One"
AnArray(1) = "Two"
AnArray(2) = "Three"

For Each Element In AnArray
Msgbox Element
Next Element


Element is just a chosen name for the example. Any variable name will work for the For Each statement (as long as it respects the variable naming rules).

For each iteration, the variable Element will assume the value of an element of the array: In the first iteration, Element will assume the value of AnArray(0), which is "One". In the second iteration it will assume the value of AnArray(1), which is "Two" and so on.

Be careful because the For Each statement will go through all elements of the array, matrix or collection, even those without value.

  Dim AnArray(4) As String 'array with 5 elements

AnArray(0) = "One"
AnArray(1) = "Two"
AnArray(2) = "Three"
'AnArray(3) = ""
'AnArray(4) = ""

For Each Element In AnArray
Msgbox Element
Next Element
'The last MsgBox will come empty equal to AnArray(4)


To avoid this kind of problem, we can add an If Then function to check if the element has a value.

  Dim AnArray(4) As String

AnArray(0) = "One"
AnArray(1) = "Two"
AnArray(2) = "Three"

For Each Element In AnArray
If Element <> "" Then
Msgbox Element
End If
Next Element
'The last MsgBox will come empty equal to AnArray(2)


Remember that a String with undeclared value is equal to "".