Microsoft Office Tutorials and References
In Depth Information
Lesson 9: Repeating Actions with Loops
repeating actions with Loops
Suppose you need to perform the same action, or the same sequence of several actions, many
times in your macro. For example, you may need to unhide all worksheets that are hidden,
or add 12 worksheets to your workbook and name them for each month of the year.
The fact is, you’ll encounter many circumstances for which a repetition of similar commands
is a necessary part of the job. In most cases it will be impractical, and sometimes downright
impossible, to write an individual command for each performance of the action. The need for
handling a repetitive set of commands efficiently is exactly what loops are made for.
WHAT is A loop?
A loop is a method of performing a task more than once. You may need to copy each
worksheet in your workbook and save it as the only worksheet in its own separate workbook. Or,
you may have a list of thousands of records and want to insert an empty row where the value
of a cell in column A is different than the value of the cell below it. Maybe your worksheet
has dozens of cells that contain comments, and you want to add the same preceding text to
every comment’s existing text without having to edit every comment one at a time.
Instead of doing these kinds of tasks manually, or recording an impractical (and sometimes
impossible) macro to handle the repetition, you can use loops to get the job done with less
code while keeping more flexible control over the number of necessary repetitions. In VBA,
a loop is a structure that executes one or more commands, and then cycles through the
process again within the structure, for as many times as you specify. Each cycle of executing the
loop structure’s command(s) is called an iteration .