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

Early Exit from a Loop

   <source lang="vb">

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

</source>
   
  

For...Each Variation

   <source lang="vb">

Sub WorksheetLoop2()

   Dim ws As Worksheet 
   For Each ws In ThisWorkbook.Worksheets 
       Debug.Print ws.Name 
   Next 

End Sub

</source>
   
  

Looping through the elements in an array

   <source lang="vb">

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

</source>
   
  

Loop through ranges

   <source lang="vb">

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

</source>
   
  

Using Nested Loops

   <source lang="vb">

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

</source>
   
  

Using the For Each...Next Loop

   <source lang="vb">

  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 
</source>