VBA/Excel/Access/Word/Language Basics/For Each

Early Exit from a Loop

 
Sub GetControls2() 
    Dim myControl As Control 
    Dim myForm As Form 
    DoCmd.OpenForm "Customers" 
    Set myForm = Screen.ActiveForm 
    For Each myControl In myForm 
        Debug.Print myControl.Name 
        If myControl.Name = "Address" Then Exit For 
    Next 
End Sub

For...Each Variation

 
Sub WorksheetLoop2() 
    Dim ws As Worksheet 
    For Each ws In ThisWorkbook.Worksheets 
        Debug.Print ws.Name 
    Next 
End Sub

Looping through the elements in an array

 
Sub ArrayLoop() 
    Dim avColors As Variant 
    Dim vItem As Variant 
    avColors = Array("Red", "Green", "Blue") 
    For Each vItem In avColors 
        Debug.Print vItem 
    Next 
End Sub

Loop through ranges

 
Sub TestAreas()
  Dim rng As Range
  For Each rng In Range("A1:B5,C6:D10,E11:F15").Areas
    MsgBox rng.Address
  Next rng
End Sub

Using Nested Loops

 
Sub GetFormsAndControls() 
    Dim accObj As AccessObject 
    Dim myControl As Control 
    Set obj = CurrentProject.AllForms 
    For Each accObj In obj 
        Debug.Print accObj.Name & "---Form" 
        If Not accObj.IsLoaded Then 
            DoCmd.OpenForm accObj.Name 
           For Each myControl In Forms(accObj.Name).Controls 
                Debug.Print Chr(9) & myControl.Name 
            Next 
            DoCmd.Close 
        End If 
    Next 
End Sub

Using the For Each...Next Loop

 
   Sub GetControls() 
       Dim myControl As Control 
       Dim myForm As Form 
       DoCmd.OpenForm "Customers" 
       Set myForm = Screen.ActiveForm 
       For Each myControl In myForm 
           Debug.Print myControl.Name 
       Next 
   End Sub