Do while … loop, do until … loop, do … loop while, do … loop until, and while … wend are the format of looping that we can use in Excel VBA.

The main reason why we need to perform looping is to simplified a repeating tasks. Rather then manually calling a block of code over and over again, it’s more simple and readable if we make it into loop.

There are two kind of loop

First, looping as long as the condition is true or false. This type of loop check the value of condition first, before the looping begin to start.

Second, repeat the task until the condition achieved. This type of loop will check the value of condition when the looping is finish.

Which one that we gonna pick is depend on our condition or purpose.

Syntax

Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]

Loop

Or

Do
[statements]
[Exit Do]
[statements]

Loop [{While | Until} condition]

And

While condition
[statements]

Wend

While … wend is the same with do while … loop, and do while … loop is more structured and flexible compare to while … wend statement.

Examples:

' example of do while ... loop
Sub ChkFirstWhile()
    counter = 0
    myNum = 20
    ' do while myNum > 10
    ' we perform condition check before we do loop
    ' if the first check fail to meet the criteria
    ' no loop will be perform
    Do While myNum > 10
        myNum = myNum - 1
        counter = counter + 1
    Loop
    MsgBox "The loop made " & counter & " repetitions."
End Sub

' example of do ... loop while
Sub ChkLastWhile()
    counter = 0
    myNum = 9
    ' do ... loop while myNum > 10
    ' we perform condition check after the loop
    ' we do at least 1 loop
    Do
        myNum = myNum - 1
        counter = counter + 1
    Loop While myNum > 10
    MsgBox "The loop made " & counter & " repetitions."
End Sub

' example of do until ... loop
' the principle is the same with do while ... loop
Sub ChkFirstUntil()
    counter = 0
    myNum = 20
    Do Until myNum = 10
        myNum = myNum - 1
        counter = counter + 1
    Loop
    MsgBox "The loop made " & counter & " repetitions."
End Sub

' example of do ... loop until
' the principle is the same with do ... loop while
Sub ChkLastUntil()
    counter = 0
    myNum = 1
    Do
        myNum = myNum + 1
        counter = counter + 1
    Loop Until myNum = 10
    MsgBox "The loop made " & counter & " repetitions."
End Sub

' example of while ... wend loop
Sub ChkWhile()
    Dim Counter
    Counter = 0    ' Initialize variable.
    While Counter < 20    ' Test value of Counter.
        Counter = Counter + 1    ' Increment Counter.
    Wend    ' End While loop when Counter > 19.
    Debug.Print Counter    ' Prints 20 in the Immediate window.
End Sub

FIN.