CODEWITHSUNDEEP
excelvbaloopsnested
Richard
Richard

Reputation: 509

VBA Nested Do While Loops are operating in series

I have a really simple problem. I'm trying to nest two "Do While" loops in Excel VBA. Test code below re-produces the issue I am having.

The code below should produce 12 message boxes. 9 for the nested loop as it is being triggered three time with the master loop that is also triggered three times and 3 further message boxes for the master loop.

What I'm finding is the two loops are operating in series as though they are not nested so I'm getting 6 message boxes in total.

I'm sure I have used nested loops in VBA in the past with no issue (although now I might need to go back and check).

Why are the nested loops behaving in a series? How does it behave on your computer? Am I missing a simple syntax error?

Sub test()
    i = 0
    m = 0
    Do While i < 3
        Do While m < 3
            MsgBox ("Nested Loop " & m)
            m = m + 1
        Loop
        MsgBox ("Master Loop " & i)
        i = i + 1
    Loop
End Sub

Upvotes: 2

Views: 795

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

The lower and upper bounds of both loops are known before the loop block is entered.

The correct loop structure to use in such a situation, would be For...Next:

Sub test()
    Dim i As Long
    Dim m As Long
    For i = 0 To 2
        For m = 0 To 2
            Debug.Print "Inner Loop " & m
        Next
        Debug.Print "Outer Loop " & i
    Next
End Sub

Use Do...Loop blocks (Do...While, Do While...Loop, Do...Until, etc.) when the number of iterations is unknown at the time the loop starts, or otherwise depends on conditions that are met during the execution of the loop body.

That way you don't need to manipulate/increment/reset any loop counter variables.

Upvotes: 2

braX
braX

Reputation: 11755

You will want to reset your m variable inside the i loop or it will only run the m loop the first time, because in your code, once m reaches 3, it will always be 3.

Sub test()
    i = 0
    Do While i < 3
        m = 0
        Do While m < 3
            MsgBox "Nested Loop " & m
            m = m + 1
        Loop
        MsgBox "Master Loop " & i
        i = i + 1
    Loop
End Sub

Also, you do not use parenthesis with MsgBox unless you are expecting it to return a value.

Upvotes: 6

Related Questions