Choose your language

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 "".


Show Advanced Topics


SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

© 2019 SuperExcelVBA | ABOUT

Protected by Copyscape