Page 107 - Excel Progamming Weekend Crash Course

h540629 ch06.qxd 9/2/03 9:33 AM Page 82

82 Saturday Morning

You can exit a Do loop early by including the Exit Do statement within the loop. When
this statement is encountered, execution immediately passes to the code following the Loop
statement regardless of the value of the condition that the loop is testing. The following
example loops until the variable X is either zero or greater than 50:
Do Until X > 50
...
If X = 0 Then Exit Do
...
Loop

Sometimes it is useful to set up a loop that would execute forever, and use Exit Do to
terminate it. This loop repeatedly prompts the user to give an answer until he answers the
question correctly:
Do While True
str = InputBox(“What’s the capital of France?”)
If str = “Paris” Then Exit Do
Loop
Of course, the same result could be obtained as follows:

Do
str = InputBox(“What’s the capital of France?”)
Loop Until str = “Paris”
This demonstrates VBA’s flexibility — there is almost always more than one way to do
something.
When programming loops, you must be careful not to create an infinite loop — one that
repeats forever. This can happen if the loop’s condition is expressed incorrectly, or if code
within the loop does not take any action to change the condition. Here’s an example:
Do Until X < 5 And X > 10
...
Loop
Perhaps you already see the problem — it is impossible for X to be less than 5 and more
than 10 at the same time, so the condition will never be met and the loop will repeat for-
ever. If a VBA program stops responding and seems to be “frozen,” an infinite loop might be
the problem.
VBA supports the obsolete While...Wend statement, a holdover from earlier versions of
Basic. Its syntax is:
While condition
...
Wend