Code That Loops

You've seen in this chapter and in previous chapters that it makes sense to divide up your VBA chores and place them in separate procedures or functions. That way, you need to write the code only once and then call it any time you need it. This is known in the trade as modular programming, and it saves time and effort by helping you avoid reinventing too many wheels.

There are also wheels to avoid reinventing within your procedures and functions. For example, consider the following code fragment:

MsgBox "The time is now " & Time Application.Wait Now + TimeValue("00 MsgBox "The time is now " & Time Application.Wait Now + TimeValue("00 MsgBox "The time is now " & Time Application.Wait Now + TimeValue("00

¡ÎÎ This code fragment uses the Excel Application object's wait method to produce a delay.The arguai ment Now + Timevaiue("00:00:05") pauses the procedure for about five seconds before continuing.

This code does nothing more than display the time, delay for five seconds, and repeat this two more times. Besides being decidedly useless, this code just reeks of inefficiency. It's clear that a far better approach would be to take just the first two statements and somehow get VBA to repeat them as many times as necessary.

The good news is that not only is it possible to do this, but VBA also gives you a number of different methods to perform this so-called looping. I spend the rest of this chapter investigating each of these methods.

0 0

Post a comment