2.4.3 Loops in VBA: Do Until...Loop

The Do Until...Loop in VBA is used to execute a block of code repeatedly until a specified condition becomes True. Unlike the Do While...Loop, which runs while the condition is True, the Do Until...Loop continues until the condition evaluates to True.

This type of loop is useful when you want to perform an action until a certain condition is met.

1. Syntax of Do Until...Loop

The basic syntax of the Do Until...Loop is:

Do Until condition
    ' Code to execute as long as the condition is False
Loop

• condition: A logical expression that is evaluated before each iteration. The loop continues as long as the condition evaluates to False.
• Loop: Marks the end of the loop body.

2. Basic Example of a Do Until...Loop

Here's a simple example that uses a Do Until...Loop to display numbers from 1 to 5 in a message box:

Dim i As Integer
i = 1

Do Until i > 5
    MsgBox "Iteration " & i
    i = i + 1  ' Increment the counter to avoid an infinite loop
Loop

• The loop continues executing as long as i is not greater than 5.
• In each iteration, the message box displays the current value of i, and then i is incremented by 1.
• When i becomes greater than 5, the condition i > 5 becomes True, causing the loop to exit.

3. Condition Evaluation in Do Until...Loop

In a Do Until...Loop, the condition is checked before each iteration. If the condition is True at the start, the loop will not run at all. If the condition is False, the loop will continue to execute.

Example:

Dim i As Integer
i = 10

Do Until i < 5
    MsgBox "Iteration " & i
    i = i + 1  ' Increment the counter
Loop

• Since the condition i < 5 is False initially, the loop will not execute, and no message box will appear.

4. Using Exit Do to Terminate the Loop Early

The Exit Do statement can be used to exit the loop before the condition is met, based on a different condition or event. This allows you to terminate the loop early, even if the condition for continuation is still False.

Example with Exit Do:

Dim i As Integer
i = 1

Do Until i > 5
    If i = 3 Then
        MsgBox "Breaking out of the loop"
        Exit Do  ' Exits the loop when i equals 3
    End If
    MsgBox "Iteration " & i
    i = i + 1
Loop

• The loop starts at i = 1 and continues as long as i <= 5.
• When i reaches 3, the loop exits early due to the Exit Do statement, and the message "Breaking out of the loop" is shown.

5. Do Until...Loop vs. Do While...Loop

While both Do While...Loop and Do Until...Loop are used for repetitive tasks, they differ in how they evaluate the condition:

• The Do While...Loop continues as long as the condition is True.
• The Do Until...Loop continues as long as the condition is False and stops once the condition becomes True.

6. Using Do Until...Loop with Arrays

The Do Until...Loop can also be useful for iterating over arrays or ranges when the end condition is dynamic and unknown at the start.

Example with Arrays:

Dim fruits(3) As String
Dim i As Integer
i = 0

fruits(0) = "Apple"
fruits(1) = "Banana"
fruits(2) = "Cherry"

Do Until i = 3
    MsgBox "Fruit " & (i + 1) & ": " & fruits(i)
    i = i + 1
Loop

In this example:

• The loop iterates over the fruits array and displays each fruit until i equals 3 (the length of the array).
• The loop runs until the condition i = 3 is met, at which point it stops.

7. Do Until...Loop with Multiple Conditions

You can also combine multiple conditions in the Do Until...Loop using logical operators like And or Or.

Example:

Dim i As Integer
i = 1

Do Until i > 5 Or i = 3
    MsgBox "Iteration " & i
    i = i + 1
Loop

• The loop continues until either i > 5 or i = 3.
• This will stop when i reaches 3, even though it would normally run to 5.

8. Conclusion

The Do Until...Loop is a powerful tool for situations where you want to execute a block of code repeatedly until a specific condition becomes True. This loop provides flexibility for iterating when the exact number of iterations is unknown or when you need the loop to stop as soon as a certain condition is met. By using Exit Do or combining logical operators, you can create more complex loop conditions and control the flow of your program efficiently.