Do...Loop

Use a Do...Loop statement to run a block of statements an indefinite number of times. There are several variations of the Do...Loop statement, but each one evaluates a condition to determine whether or not to continue running. As with an If...Then statement, the condition must be a value or an expression that evaluates to True or False. The different variations are described in this section. For more information about the Do...Loop statement, see "Do...Loop" in Help.

Do While...Loop

Use the Do While...Loop statement when you want to test a condition before you run the loop and then continue to run the loop while the condition is True.

Do While condition
statements
Loop

The statements must eventually cause the condition to become False, or the loop will run forever (this is called an infinite loop). To stop an infinite loop, press Esc or CTRL+BREAK.

The following Function procedure counts the occurrences of a target string within another string by looping as long as the target string is found. Because the test is at the beginning of the loop, the loop runs only if the string contains the target string.

Function CountStrings(longstring, target)
    position = 1
    Do While InStr(position, longstring, target) 'Returns True/False
        position = InStr(position, longstring, target) + 1
        Count = Count + 1
    Loop
    CountStrings = Count
End Function

Do Until...Loop

Use the Do Until statement if you want to test the condition at the beginning of the loop and then run the loop until the test condition becomes True. If the condition is initially True, the statements inside the loop never run.

Do Until condition
statements
Loop

With the test at the beginning of the loop in the following example, the loop doesn't run if Response is equal to vbNo.

Response = MsgBox("Do you want to process more data?", vbYesNo)
Do Until Response = vbNo
    ProcessUserData    'Call procedure to process data
    Response = MsgBox("Do you want to process more data?", vbYesNo)
Loop

Do...Loop While

When you want to make sure that the loop will run at least once, you can put the test at the end of the loop. The statements run as long as the condition is True.

Do
statements
Loop While condition

This variation guarantees that your procedure will run the loop at least once.

Sub MakeBlue()
    Set rSearch = Worksheets("sheet1").Range("a1:a10")
    Set c = rSearch.Find("test")
    If Not c Is Nothing Then
        first = c.Address
        Do
            c.Font.ColorIndex = 5
            Set c = rSearch.FindNext(c)
        Loop While (Not c Is Nothing) And (c.Address <> first)
    Else
        MsgBox "not found"
    End If
End Sub

Do...Loop Until

With the Loop Until test at the end of the loop, the loop runs at least once and stops running when the condition becomes True.

Do
statements
Loop Until condition

Do
    ProcessUserData    'Call procedure to process data
    response = MsgBox("Do you want to process more data?", vbYesNo)
Loop Until response = vbNo