Nested Loops

In this blog post we will be discussing nested loops and how they can be used.

A loop inside a loop is termed a nested loop. We’ll make a grid of numbers to illustrate.

Sub nestedLoop1()
    Dim y As Integer
    Dim x As Integer
    Dim xString As String
    
    For y = 0 To 9
    'We start by looping through 0 - 9. This will provide
    'us with 10 loops
        For x = 0 To 9
        'Adding a second loop will mean that we end up
        'looping a hundred times (10 x 10)
            xString = xString & x & " "
            'On each loop we are concatenating the x
            'variable with a space so we have a line that
            'goes 0 1 2 3 4 5 etc.
        Next x
        
        Debug.Print xString
        'Here we print out the full xString
        
        xString = ""
        'We reset the xString to nothing
    Next y
End Sub

The output of the above code will be:

0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9

Nested Loops and Multidimensional Arrays

Nested loops work very well with multidimensional arrays.

Sub nestedLoop2()
    Dim y As Integer
    Dim x As Integer
    Dim xString As String
    Dim MyArray(10, 10) As String
    'Here we have a multidimensional array of 10 x 10
    'This array will be able to hold 100 items
    
    For y = 0 To 9
    '10 loops here...
        For x = 0 To 9
        '...and 10 more here give us 100 loops!
            MyArray(y, x) = y * x
            'We fill the array element with the
            'multiple of x and y
        Next x
    Next y
    
    For y = 0 To 9
        For x = 0 To 9
        'And now we loop again and print out the
        'results of the code above
            xString = xString & MyArray(y, x) & " "
        Next x
    
        Debug.Print xString
        xString = ""
    Next y

End Sub

0 0 0 0 0 0 0 0 0 0 0 1 2 3 4 5 6 7 8 9 0 2 4 6 8 10 12 14 16 18 0 3 6 9 12 15 18 21 24 27 0 4 8 12 16 20 24 28 32 36 0 5 10 15 20 25 30 35 40 45 0 6 12 18 24 30 36 42 48 54 0 7 14 21 28 35 42 49 56 63 0 8 16 24 32 40 48 56 64 72 0 9 18 27 36 45 54 63 72 81

A Useful Implementation of Nested Loops

A more practical example is to iterate over a Collection within a Recordset.

Sub nestedLoop3()

    Dim rs As DAO.Recordset, field As DAO.field
    Dim rowText As String
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblStudents")
    
    While (Not rs.EOF)
    'Loop no1
        For Each field In rs.Fields
        'Loop no2
        'we will be looping through all of the field names in tblStudents
        
            rowText = rowText & field.Name & "=" & rs.Fields(field.Name) & ", "
            'we use the field name to get the value of that field and create
            'a concatenated string to print out.
            'e.g. StudentID=15, LastName=Kupova, etc.
        Next
        
        Debug.Print rowText
        rowText = ""
        rs.MoveNext
    Wend

End Sub

nestedLoop3 StudentID=1, LastName=Bedecs, FirstName=Anna ‘ … more commented out StudentID=2, LastName=Gratacos Solsona, FirstName=Antonio ‘… StudentID=3, LastName=Axen, FirstName=Thomas, ‘…

Here the Fields collection is being iterated over and rowText populated with the field’s name and value.

Nested loops can be complicated but are worth the time and effort spent learning them.

Related Posts

DoEvents Loops
Looping Through a Recordset Loops
Loops – Do Until / Do While / Do Loops
Loops – For Each Loops
Loops – For Next Loops
Loops – While Wend Loops
What are Recordsets Loops