Excel VBA Lesson 15: Mastering Do...Loop and While...Wend in Excel VBA

❮ Previous Lesson Next Lesson ❯

In the previous lesson, you learned about the For...Next loop. This lesson dives deeper into two more powerful looping structures in VBA: the Do...Loop and While...Wend. These loops are essential when you need to repeat actions but don't know exactly how many times in advance.

Pro Tip: Do loops are particularly useful when you need to:

  • Process data until a condition is met
  • Read through files line by line until the end
  • Validate user input
  • Search through collections or arrays

15.1 Do Loop Structures

VBA provides four flexible ways to implement Do loops:

Structure Description When to Use
Do...Loop While Executes at least once, checks condition at end When you need the loop to run at least one time
Do Until...Loop Checks condition at start, may not execute When you need to check before first iteration
Do While...Loop Checks condition at start, may not execute Positive condition checking
Do...Loop Until Executes at least once, checks condition at end When you need to run once then check

Example 15.1: Ascending Numbers with Do...Loop While

This example demonstrates a post-test loop that always executes at least once:

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

Key Points:

  • The loop continues while the condition remains true
  • Counter starts at 0 (default for Integer)
  • Loop runs until counter reaches 10
Excel VBA Do Loop While output showing numbers 1 through 10
Figure 15.1: Output of ascending numbers example

Example 15.2: Descending Numbers with Do Until...Loop

This example shows a pre-test loop with negative condition checking:

Private Sub CommandButton1_Click() Dim counter As Integer Do Until counter = 10 counter = counter + 1 Cells(counter, 1) = 11 - counter Loop End Sub
Excel VBA Do Until Loop output showing numbers 10 through 1
Figure 15.2: Output of descending numbers example

Example 15.3: Calculating Values with Do While...Loop

This practical example demonstrates calculations across multiple columns:

Private Sub CommandButton1_Click() Dim counter, sum As Integer Range("A1:C11").Select With Selection .HorizontalAlignment = xlCenter End With Cells(1, 1) = "X" Cells(1, 2) = "Y" Cells(1, 3) = "X+Y" 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

Best Practice: Always format your output for better readability, as shown in the example with cell alignment.

Excel VBA calculation output showing X, Y, and X+Y columns
Figure 15.3: Calculation output example

New Example 15.4: Reading Data Until Empty Cell

This practical example shows how to read data until an empty cell is encountered:

Private Sub ReadDataUntilEmpty() Dim rowNum As Integer rowNum = 1 Do While Cells(rowNum, 1).Value <> "" ' Process your data here Debug.Print "Processing row " & rowNum & ": " & Cells(rowNum, 1).Value rowNum = rowNum + 1 Loop MsgBox "Processed " & (rowNum - 1) & " rows of data." End Sub

15.2 While...Wend Loop

The While...Wend loop is a simpler alternative to Do loops, with this structure:

While condition [statements] Wend

Note: While...Wend is less flexible than Do loops and is maintained for backward compatibility. Microsoft recommends using Do loops for new code.

Example 15.5: Summing Numbers with While...Wend

This example demonstrates number summation using While...Wend:

Private Sub CommandButton1_Click() ListBox1.Clear Dim sum, n As Integer While n <> 20 n = n + 1 sum = sum + n ListBox1.AddItem (n & vbTab & sum) Cells(n + 1, 2) = n Cells(n + 1, 3) = sum Wend End Sub
Excel VBA While Wend output showing number summation
Figure 15.4: While...Wend summation output

15.3 Loop Control Statements

VBA provides statements to control loop execution:

Statement Description Example
Exit Do Immediately exits a Do loop If x > 100 Then Exit Do
Exit While Exits a While loop (VBA doesn't support this directly) Use Exit Do in While...Wend

New Example 15.6: Exit Do with Condition

This example shows how to exit a loop prematurely:

Private Sub ExitDoExample() Dim i As Integer i = 1 Do While True ' Infinite loop without Exit Do Cells(i, 1) = i * 2 If i >= 10 Then Exit Do i = i + 1 Loop MsgBox "Loop exited at i = " & i End Sub

15.4 Common Pitfalls and Best Practices

Avoid These Common Mistakes:

  • Infinite loops: Always ensure your loop has a valid exit condition
  • Off-by-one errors: Carefully check your loop boundaries
  • Uninitialized variables: Initialize counters before using them
  • Slow performance: Minimize operations inside loops when possible

Recommended Best Practices:

  • Use meaningful variable names (counter vs. just i)
  • Add comments explaining complex loop logic
  • Consider using For loops when iteration count is known
  • Test edge cases (empty input, minimum/maximum values)

Summary

✅ In This Lesson, You Learned:

  • Do loops offer four flexible structures for different looping needs
  • While...Wend provides simpler syntax but less functionality
  • Always ensure your loops have proper exit conditions to prevent infinite execution
  • Choose the right loop type based on whether you need pre-test or post-test evaluation
  • Use Exit Do to break out of loops when necessary
  • Proper loop structure and variable initialization are crucial for reliable code
  • Formatting output improves readability of your results

Practical Applications

These looping techniques are essential for:

  • Processing large datasets in Excel
  • Automating repetitive tasks
  • Validating user input
  • Searching through data
  • Implementing complex calculations

🔗 Related Resources


❮ Previous Lesson Next Lesson ❯


Copyright ® 2008- Dr. Liew Voon Kiong . All rights reserved   [Privacy Policy]

Contact: Facebook Page