2.4.2 Loops in VBA: Do While...Loop

The Do While...Loop is another type of loop in VBA, used to execute a block of code repeatedly as long as a specified condition is True. This type of loop is useful when you don't know how many iterations are required, but you know the loop should continue as long as the condition holds.

1. Syntax of Do While...Loop

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

Do While condition
    ' Code to execute as long as the condition is True
Loop

• condition: A logical expression that is evaluated before each iteration. If the condition evaluates to True, the code inside the loop is executed. If it evaluates to False, the loop exits.
• Loop: Marks the end of the loop body.

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

Here's an example where a Do While...Loop is used to display numbers from 1 to 5 in a message box:

Dim i As Integer
i = 1

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

• The loop continues as long as i <= 5.
• In each iteration, the message box displays the current value of i.
• After the message box is shown, i is incremented by 1.
• When i becomes greater than 5, the loop exits.

3. Using Do While...Loop with Conditional Termination

The loop condition is evaluated before the loop begins each iteration. If the condition is initially False, the loop will not execute at all. This is different from some other types of loops (like Do Until...Loop), where the code runs at least once.

Example:

Dim i As Integer
i = 10

Do While i < 5
    MsgBox "Iteration " & i
    i = i + 1
Loop

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

4. Using Exit Do to Terminate the Loop Early

You can use the Exit Do statement to break out of a Do While...Loop early, regardless of whether the condition has been met. This is useful when you need to stop the loop based on some other condition that is not part of the original condition.

Example with Exit Do:

Dim i As Integer
i = 1

Do While i <= 10
    If i = 6 Then
        MsgBox "Breaking out of the loop"
        Exit Do  ' Exits the loop when i equals 6
    End If
    MsgBox "Iteration " & i
    i = i + 1
Loop

• The loop starts at i = 1 and continues until i = 6, at which point the loop breaks early due to the Exit Do statement.

5. Do While...Loop vs. For...Next Loop

While both the For...Next loop and Do While...Loop are used for repetition, there are some key differences:

• The For...Next loop is typically used when the number of iterations is known in advance.
• The Do While...Loop is used when the number of iterations depends on a condition and may not be known beforehand. The loop continues as long as the condition remains True.

6. Do Until...Loop

The Do While...Loop checks if the condition is True before executing, but there's also the Do Until...Loop, which runs until the condition becomes True (or the condition is False at the start).

The syntax for Do Until...Loop is:

Do Until condition
    ' Code to execute until the condition becomes True
Loop

The primary difference is that Do While runs as long as the condition is True, while Do Until runs until the condition becomes True.

7. Conclusion

The Do While...Loop is ideal for scenarios where you want to repeat a block of code as long as a condition holds true. Its flexibility makes it a powerful tool for situations where the number of iterations is not known in advance. By combining it with other control structures, such as Exit Do, you can manage more complex iteration logic in your VBA programs.