Loops – For Each

In this blog post we will be explaining the For…Each loop and providing some examples of its usage.

Demonstrating The For Each Loop with An Array

We can use the For Each loop over a standard array.

Sub forEachArray()
    
    Dim element As Variant
    Dim animals(0 To 5) As String
    'We have created an array that can hold 6 elements
    
    animals(0) = "Dog"
    animals(1) = "Cat"
    animals(2) = "Bird"
    animals(3) = "Buffalo"
    animals(4) = "Snake"
    animals(5) = "Duck-billed Platypus"
    'We fill each element of the array
    
    
    For Each element In animals
    'animals consists of 6 "elements"
    
        Debug.Print element
        'printing to the immediate window
        
    Next

End Sub

The output to the immediate window will be:

Using Loops with Collections

Alternatively, the For…Each loop can be used with a collection.

Public Sub forEachCollection1()

    Dim element As Variant
    Dim animals  As Collection
    Set animals = New Collection
    'Collections are literally collections of objects
    'and are a useful feature of MS Access
    'They have 4 methods - add, count, item, remove
        
    animals.Add "Dog"
    animals.Add "Cat"
    animals.Add "Bird"
    animals.Add "Buffalo"
    animals.Add "Snake"
    animals.Add "Duck-billed Platypus"
    'We utilise the add method to add the various
    'animals to the collection
    
    For Each element In animals
        Debug.Print element
    Next
    'We print out all the elements in the
    'animals collection
    
    animals.Remove 3
    'We remove an element from the animals collection
    'we are removing the 3rd item in the collection (bird)
    
    Debug.Print ""
    'prints a blank line
    
    For Each element In animals
        Debug.Print element
        'printing to the immediate window
        
    Next
    'Here we are printing out all the elements in the
    'animals collection minus the bird
End Sub

The output to the immediate window will be:

Access contains some collections of its own! Knowing how to utilise these collections, can make life much simpler when coding.

Sub forEachCollection2()
    Dim i As Integer
    For i = 0 To CurrentProject.AllForms.Count - 1
    'CurrentProject.AllForms is a collection and
    'therefore has the add, count, item and remove
    'methods available
    
        Debug.Print CurrentProject.AllForms(i).Name
        'Here we print the names of the forms to the
        'immediate window
    Next

End Sub

In our database, we have 3 forms (Form1, Form2, Form3) and so the output to the immediate window will be:

Let’s take a look at how to exit a for each loop.

Exit For

To leave the For Each loop before its natural end,we can use the Exit For statement.

Sub forEachExit()
    Dim element As Variant
    Dim animals(0 To 5) As String
    'We have created an array that can hold 6 elements
    
    animals(0) = "Dog"
    animals(1) = "Cat"
    animals(2) = "Bird"
    animals(3) = "Buffalo"
    animals(4) = "Snake"
    animals(5) = "Duck-billed Platypus"
    'Here we fill each element of the array
    
    For Each element In animals
    'iterates over the animals collection
    
        Debug.Print element
        'print each element to the immediate window
    
        If element = "Buffalo" Then Exit For
        'if, at any point, the element becomes equal
        
    Next

End Sub

The output to the immediate window will be (we exited the loop before all items could be printed):

The For…Each is worth learning if you want to use arrays correctly. Its ability to search over all the elements of an array sets it apart from a For…Next Loop.

Related Posts