VBA Nested Do While Loops are operating in series

user_10079909 0
Viewed 304

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
vba excel nested loops
2 Answers

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.

user_84229530

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.

user_11885130
Related