Chapter 12. Using Loops to Repeat Actions

As in life, so in VBA: at times, you may want to repeat an action to achieve a certain effect. Sometimes, you'll want to repeat an action a predetermined number of times: break six eggs to make an omelet, or create six new documents based on a certain template.

More often, you'll want to repeat an action until a certain condition is met: buy two lottery tickets a week until you win more than $2,000, or repeat an action for every instance of a value that appears in an Excel spreadsheet. In these cases, you won't know when you'll triumph against the wretched odds of the lottery, and you won't know how many instances of the value will appear in the spreadsheet — you'll just carry on until the condition is met.

In VBA, you use loops to repeat actions. By using loops, you can transform a simple recorded macro into one that repeats itself as appropriate for the material it's working on. VBA provides a number of expressions for creating loops in your code. In this chapter, you'll learn about the different types of loops and typical uses for each.

In this chapter you will learn to

• Understand when to use loops

• Use For... loops for fixed repetitions

• Use Do... loops for variable numbers of repetitions

• Nest one loop within another loop

• Avoid infinite loops