Lesson 13: Mastering Do Loops in Excel VBA 365

13.1 Understanding Do Loops in VBA

Do Loops in Excel VBA 365 provide powerful ways to repeat blocks of code while or until certain conditions are met. Unlike For...Next loops that run a set number of times, Do Loops offer more flexibility in controlling program flow.

The Four Do Loop Variations

VBA provides four ways to implement Do Loops, each serving different programming needs:

Do...Loop While - Executes the loop at least once before checking the condition
Do Until...Loop - Checks condition before entering the loop
Do While...Loop - Checks condition before entering the loop
Do...Loop Until - Executes the loop at least once before checking the condition

Key Differences

While vs Until: While continues while a condition is true, Until continues until a condition becomes true.

Condition Placement: When the condition is at the start, the loop may not run at all. When at the end, it runs at least once.

Example 13.1: Basic Do...Loop While

This example demonstrates how to arrange numbers in ascending order using a Do...Loop While structure:

Private Sub CommandButton1_Click()
    Dim counter As Integer
    Do
        counter = counter + 1
        Cells(counter, 1) = counter
    Loop While counter < 10
End Sub

Explanation: This code will:

Initialize a counter variable
Enter the loop and increment the counter
Place the counter value in column A
Repeat while counter is less than 10

Example 13.2: Practical Do While...Loop

This example shows a more practical application with multiple columns and calculations:

Private Sub CommandButton1_Click()
    Dim counter As Integer, sum As Integer
    
    'Formatting the output range
    Range("A1:C11").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    
    'Setting column headers
    Cells(1, 1) = "X"
    Cells(1, 2) = "Y"
    Cells(1, 3) = "X+Y"
    
    'Main loop with calculations
    Do While counter < 10
        counter = counter + 1
        Cells(counter + 1, 1) = counter
        Cells(counter + 1, 2) = counter * 2
        sum = Cells(counter + 1, 1) + Cells(counter + 1, 2)
        Cells(counter + 1, 3) = sum
    Loop
End Sub

Excel VBA Do Loop output example — Figure 13.1: Output of the Do While Loop Example

Additional Examples

Example 13.3: Do Until...Loop

This example demonstrates exiting a loop when a condition becomes true:

Private Sub CommandButton2_Click()
    Dim userInput As String
    Do Until userInput = "quit"
        userInput = InputBox("Enter text (type 'quit' to exit)", "Loop Example")
        If userInput <> "quit" Then
            MsgBox "You entered: " & userInput
        End If
    Loop
End Sub

Example 13.4: Do...Loop Until with Error Handling

This example includes basic error handling within a loop:

Private Sub CommandButton3_Click()
    Dim validEntry As Boolean
    Dim userValue As Double
    
    Do
        On Error Resume Next
        userValue = InputBox("Enter a number between 1 and 100", "Number Validation")
        
        If Err.Number <> 0 Then
            MsgBox "Please enter a valid number", vbExclamation
            Err.Clear
        ElseIf userValue >= 1 And userValue <= 100 Then
            validEntry = True
        Else
            MsgBox "Number must be between 1 and 100", vbExclamation
        End If
    Loop Until validEntry
    
    MsgBox "Thank you! Your number is: " & userValue, vbInformation
End Sub

Best Practices for Do Loops

Always ensure your loop has a clear exit condition to prevent infinite loops
Use meaningful variable names for loop counters and conditions
Consider adding a safety counter to prevent unexpected infinite loops
Use comments to explain complex loop conditions
For performance-critical code, minimize operations inside the loop

Common Mistakes to Avoid

Forgetting to increment the counter variable (in While/Until loops)
Creating conditions that can never be met (infinite loops)
Using Do...Loop Until when you meant Do Until...Loop (or vice versa)
Modifying loop counter variables in complex ways that make the code hard to follow

Summary of Key Points

Do While...Loop checks condition before each iteration
Do...Loop While checks condition after each iteration (runs at least once)
Do Until...Loop runs until condition becomes true (checks first)
Do...Loop Until runs until condition becomes true (checks last)
Use Exit Do statement to exit a loop prematurely when needed
Always test loops with edge cases to ensure proper termination
Do Loops are ideal when you don't know in advance how many iterations are needed

🔗 Related Resources

❮ Previous Lesson Next Lesson ❯

Contact: Facebook Page