Using Do Loops

Using Do Loops

For…Next loops are useful when you can specify the number of times that you need to repeat an action. But sometimes you don't know in advance how many times you want to repeat something. For those times, there's the Do looping structure.

Creating a Simple Do Loop

This first Do loop prints a chart showing the amount to be billed for one hour, two hours, and so on through eight hours at a specific hourly rate:

 Sub PrintBilling(curHourlyRate As Currency)   ' Print out a billing chart   Dim intHours As Integer   intHours = 1   Do     Debug.Print intHours * curHourlyRate     intHours = intHours + 1   Loop Until intHours = 9 End Sub 

The lines between Do and Loop constitute the Do loop. When VBA executes the Do statement, it starts executing the statements in the body of the loop, and continues executing them until the condition in the Loop statement is True. In this particular example, VBA continues adding one to the intHours variable and executing the statements in the loop until intHours is equal to 9, at which time it proceeds to the next statement.

TIP

If you make a mistake in programming a Do loop for example, if you forget to change anything that affects the ending condition you can set up an infinite loop that executes forever, locking up your computer. If this happens, press Ctrl+Break to enter Debug mode and suspend the loop.

NOTE

In many cases, a Do loop can be replaced by an equivalent For…Next loop. In the PrintBilling example, you could have used For intHours = 1 to 8 as the loop structure. Choose whichever statement you think is more clear to read when building your own loops.

Varieties of the Do Loop

The Do Loop comes in four slightly different varieties. You can perform the check for termination at the start or the end of the loop, and you can loop while something is true or until it becomes true. You've already seen the first version, which checks at the end of the loop and proceeds until a condition is true:

 Do   statements Loop Until condition 

If you prefer, you can execute the statements in the loop while a condition is true. In this case, the condition is still checked at the end of each run through the loop, and execution continues past the loop when the condition returns False:

 Do   statements Loop While condition 

You can also perform the check at the start of the loop instead of at the end. You can do this with an Until condition:

 Do Until condition   statements Loop 

Finally, you can check at the start of the loop with a While condition:

 Do While condition   statements Loop 

Choosing between these four ways of constructing a Do loop can be a bit tricky. If you find that your code isn't executing the loop the number of times that you were expecting, one thing to consider is whether your Do loop is not the right type. The debugging tools that you learned about in Chapter 4 can be a big help here; by single-stepping through the loop, you can see exactly what's going on.

CAUTION

If you put the check at the end of the loop, the statements in the loop are always executed at least once, regardless of the initial value of the condition.

Aborting a Do Loop

When you want to jump out of the middle of a Do loop, you can use the Exit Do statement, which works very much like Exit For. Here's an example:

 Sub PrintLimitedBilling(curHourlyRate As Currency)   ' Print out a billing chart, limited to $200   Dim intHours As Integer   intHours = 1   Do     Debug.Print intHours * curHourlyRate     If intHours * curHourlyRate >= 200 Then       Exit Do     End If     intHours = intHours + 1   Loop Until intHours = 9 End Sub 

This procedure works much the same as the PrintBilling procedure that you saw earlier in the chapter. However, each time VBA executes the statements within the loop, it determines whether the calculated value is greater than or equal to 200. If it is, VBA executes the Exit Do statement to terminate the loop.