LOOPING MECHANISMS
A loop refers to a group of statements that is executed repeatedly depending on a
specific condition of a logical expression.
For ... To ... Step ... Next Loops
The syntax of the For ... To ... Step ... Next loop is:
For counter = start To end Step increment
statements
Next counter
Both Step increment, and counter following the Next are optional.
If increment is omitted, it is set equal to 1.
Increment can be negative.
For example the following code sums the series:
1/2 + 1/4 + 1/6 + ... + 1/120
and stores the result in the integer variable sum:
Dim n As Integer, sum = 0
For n = 2 To 120 Step 2
sum = sum + 1.0/n
Next n
For Each ... In ... Next Loops
A For Each ... Tn ... Next loop is similar to the
For ... To ... Step ... Next loop,
except that it executes the statements within the loop for each object within a group of objects.
The syntax is:
For Each object In group
statement
Next object
The loop returns an object variable in each pass through the loop.
You can access or use any of the properties and methods that apply to
that object.
For example, in a loop such as
For Each cel In Selection
Selection is a group of selected cells, and the variable cel is n object
within that group.
It has all the properties of a cell (a Range object) such as Value, Formula, NumberFormat, etc.
Do Loops
There are two different forms of the do loop.
The first form of the do loop has syntax:
Do While LogicalExpression
statement
Loop
The LogicalExpression is first evaluated.
If it evaluates to true, then the statement (or group of statements)
is executed, otherwise the loop is exited.
After execution of the statement in the body of the loop, the LogicalExpression is
evaluated again, and if it evaluates to True then the statement is executed again.
This process continues until the LogicalExpression becomes False, at which point the loop is exited.
After exiting the loop, the program continues with the statement
immediately after the loop.
With this form of the Do loop, the statement within the loop may not execute even once.
The second form of the do loop has syntax:
Do
statement
Loop While LogicalExpression
The LogicalExpression is evaluated only after the statement within the body of the loop
is executed once.
If the logical expression evaluates to True,
then the statement is executed a second time. This process is repeated
until the logical expression evaluates to False.
In that case the loop is exited and control continues with the statement immediately
after the loop.
With this form of the Do loop, the statement within the loop executes at least one time.
Exiting From a Loop or From a Procedure
Sometimes one wants to exit immediately from a loop or from a procedure.
Use Exit For to exit from any For loop.
Use Exit Do to exit from any Do loop.
Use the Exit Sub or Exit Function to exit from a procedure.
Typically these Exit statements are located within an If statement.
For example,
If cellContents.Value <= 0 Then Exit For
.