Microsoft Office Tutorials and References
In Depth Information
Types of Loops
Loops are great, but you’re not obligated to use one just because you need to repeat
an action two or three times. You’ll come across situations that you know will
always require the same commands to be repeated the same way, for the same
number of times. If you feel like coding each action separately, and you can live with the
longer code, go ahead and hard-code the separate commands if that’s what works
for you. Beyond three potential iterations, however, you really should go the loop
route. It’ll save you a lot of work, and the code will be easier to maintain.
The number of a loop’s iterations will depend on the nature of the task at hand. All loops fall into
one of two categories. A fixed-iteration loop executes a specified number of times that you
hardcode directly as a numeric expression. An indefinite loop executes a flexible number of times that is
usually defined by a logical expression.
For example, a fixed iteration loop dealing with a year’s worth of data might need to cycle through
12 iterations, one for each month. An indefinite loop might need to cycle through every worksheet
in your workbook, taking into consideration that because worksheets can be added or deleted at any
time, the exact count of worksheets can never be known in advance.
TypEs of loops
VBA provides several different looping structures, and at least one of them will be suited for any
looping requirement you’ll encounter. Table 9-1 shows an overview of the types of loops in VBA.
TABlE 9-1: Types of Loops in VBA
loop sTrucTurE
cATEgory
ExplAnATion
For…Next
Fixed
Repeats an action for a specified number of times.
For…Each…Next
Fixed
Repeats an action upon an object in a Collection. For example,
you can perform a task for each worksheet in the workbook.
Do…While
Indefinite
Executes an action if the condition is True, and repeats the
action until the condition is False.
Do…Until
Indefinite
Executes an action if the condition is False, and repeats the
action until the condition is True.
Do…Loop…While
Indefinite
Executes an action once, and repeats the action while the
condition is True, until it is False.
Do…Loop…Until
Indefinite
Executes an action once, and repeats the action while the
condition is False, until it is True.
While…Wend
Indefinite
Same as the Do…While loop structure, still supported by VBA
but obsolete.
Search JabSto ::




Custom Search