In VBA, a loop is a structure that repeats a number of statements, looping back to the beginning of the structure once it has finished executing them. Each cycle of execution of a loop is called an iteration.
There are two basic categories of loops:
Fixed-iteration loops repeat a set number of times.
Indefinite loops repeat a flexible number of times.
The running of either type of loop is controlled by the loop invariant, also called the loop determinant. This can be either a numeric expression or a logical expression. Fixed-iteration loops typically use numeric expressions, whereas indefinite loops typically use logical expressions. For example, a fixed-iteration loop might need to run through five iterations of a loop, while an indefinite loop might need to continue taking an action until the end of a document is reached.
Table 12.1 explains the types of loops that VBA provides.
Table 12.1. VBA's Loop Types
Loop
Type
Explanation
For... Next
Fixed
Repeats an action or a sequence of actions a given number of times.
For Each... Next
Fixed
Repeats an action or a sequence of actions once for each object in a VBA collection.
Do While... Loop
Indefinite
Performs an action or a sequence of actions if a condition is True and continues to perform it until the condition becomes False.
While... Wend
Indefinite
Performs an action or a sequence of actions if a condition is True and continues to perform it until the condition becomes False. Like Do... Loop While but almost obsolete.
Do Until... Loop
Indefinite
Performs an action or sequence of actions while a condition is False and continues to perform it until the condition becomes True.
Do...Loop While
Indefinite
Performs an action or a sequence of actions once and then repeats it while a condition is True until it becomes False.
Do... Loop Until
Indefinite
Performs an action or a sequence of actions once and repeats it while a condition is False until it becomes True.