Just like the For Next statement, For Each is a loop.
For Each is a type of loop that is normally used with , 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 ).
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 anfunction 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 "".
Collection is a data structure, similar to a vector, which has as its elements objects of type Variant.
The three most common collections for looping are:
|Workbooks||Open Excel files|
|Worksheets||Spreadsheets in the active Excel file|
|Range of Cells||Cells within a given Range|
Workbooks, Worksheets and objects will be explored in topic.
Dim wb as Workbook For Each wb In Workbooks 'Code... Next wb
Dim ws as Worksheet For Each ws In Worksheets 'Code... Next ws
Dim Rng as Range Dim C as Range Set Rng = Range("A1:A2")'Sets the Range for the loop For Each C In Rng.Cells 'For Each Cell in Range Cells 'Code... Next C
Usually the loops are used together with the instruction, to check if any element meets a given condition..
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.
© 2021 SuperExcelVBA | ABOUT