Excel VBA Looping Tutorial

Posted on the December 1st, 2008 under Excel Tutorial by Poer @ Excel VBA/Macro

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

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.

Related Entries

External Resources

Microsoft Excel 2003/2007 Video Tutorials
Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.
101 Secrets of Microsoft Excel
Discover 101 of Excels little-known secrets that have been hiding right under your nose.

Tagged with: do until vba, vba do loop, vba do while, vba tutorial, vba while, vba while loop

Excel VBA Macro