Looping Mechanisms

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

Another subtlety of the way For/Next loops operate is illustrated by the code:

For count = 2 To 14 Step 4
MsgBox count
Next count
MsgBox "After the loop we have count = " & count

The resulting display will be

2
6
10
14
After the loop we have count = 18

Here is another example, but counting backwards:
For j = 20 To -5 Step -6
MsgBox j
Next j
MsgBox "After the loop we have j = " & j

The resulting display will be

20
14
8
2
-4
After the loop we have j = -10

For Each ... In ... Next Loops

A For Each ... In ... 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.

Here is an example where this type of loop can be used to validate data entered by a user.

Option Explicit
Sub InputDigit()
' User is asked to input a single upper case character.
' If the user inputs anything other than a single upper case character,
' (s)he will be given a chance to re-enter another character.
' Process doe not stop until a valid input from the user is obtained.
' K. Ming Leung, October 2, 2007.

Dim char As String * 1

char = InputBox("Enter a single upper case letter: ")
MsgBox "You entered the character: " & char
' perform data validation
Do While char < "A" Or char > "Z"
char = InputBox("You did not enter a single upper case letter!" & Chr(13) & _
"Please re-enter an upper case letter: ")
Loop

MsgBox "You entered the character: " & char
End Sub

Chr(13) is a character that represents a line-break. It forces the subsequent line to appear on a separate line.

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

Nested Loops

Loops may be enclosed completely within other loops. This arrangement is known as nested loops.

For example:

For i = 2 To 4 Step 2
For j = 3 To 9 Step 5
MsgBox i & " " & j
Next j
Next i
MsgBox "After the loop: i = " & i & "; j = " & j

The values displayed would be:
i j 2 3 2 8 4 3 4 8 After the loop i = 6, j = 13

A Full Length Nested-Loops Example

We want to write a program that prints the stars on the flag of the
United States of America on a message box:

* * * * * *
 * * * * *
* * * * * *
 * * * * *
* * * * * *
 * * * * *
* * * * * *
 * * * * *
* * * * * *

The stars are arranged in 9 rows and 11 columns (another reason for the 911 date?).
Write a more versatile program so that the user can input any 2
positive odd numbers as the number of rows and columns,
You must take appropriate precautions to assure the correctness of the input data:
the number of rows and columns must be positive and odd.

You are only allowed to print a character at a time.

Here is a copy of the VBA sub procedure:

Option Explicit
Sub AmericanFlag()
Dim maxRows As Integer, maxCols As Integer, row As Integer, col As Integer
Dim char As String * 1
Dim flag As String
char = "*"

Do
maxRows = InputBox("Enter a positive odd integer for the number of rows: ")
maxCols = InputBox("Enter a positive odd integer for the number of columns: ")
Loop While maxRows Mod 2 = 0 Or maxCols Mod 2 = 0 Or _
maxRows <= 0 Or maxCols <= 0

MsgBox "The number of rows and columns are: " & maxRows & " " & maxCols

For row = 1 To maxRows
For col = 1 To maxCols
flag = flag & char
If char = "*" Then
char = " "
Else
char = "*"
End If
Next col
flag = flag & Chr(13)
Next row
MsgBox flag
End Sub

Here we show an example using the bisection method to find a root of a function f(x).

Suppose we know (perhaps by graphing the function) that the function f(x) changes sign somewhere within the interval [a, b], and therefore the function must have at least a root inside [a, b].

The bisection method repeatedly halves the length of the bracketing interval until a solution has been isolated as accurately as desired. If we want the solution to have an error of no larger than tol, then the terminating condition is (b - a ) < tol.

A psuedo-code of this algorithm is:

while ((b - a) > tol) do
 m =  a + (b - a ) /2
 if sign(f(a)) = sign(f(m))
       a = m
 else
       b = m
       
 end if
 end while

We will use this method to find the root of the function f(x) = x^3 - 2x - 5 that lies between 2 and 3.

The main Sub procedure is called bisection. In this main procedure we need to be able to evaluate f(x) for arbitrary values of x. Thus we write a Function procedure that returns the value of f evaluated at its argument x.

A version of such a program is shown here:

Option Explicit
Sub bisection()
Dim a As Double, b As Double, midPoint As Double
Dim fa As Double, fm As Double
Dim iterationCount As Integer
Const tol As Double = 0.0001

a = 2
b = 3
fa = fcn(a)
iterationCount = 0
Do While Abs(b - a) > tol
midPoint = a + (b - a) / 2
fm = fcn(midPoint)
If Sgn(fa) = Sgn(fm) Then
a = midPoint
fa = fm
Else
b = midPoint
End If
iterationCount = iterationCount + 1
Loop
MsgBox "After " & iterationCount & " iterations," & Chr(13) _
& "a root is found at " & midPoint & Chr(13) _
& "The function value there is: " & fm
End Sub

Function fcn(x)
fcn = x * (x * x - 2) - 5
End Function

Recursion

A recursive process is a process that calls itself. Recursion is a powerful problem-solving tool. It is available in many modern languages such as VBA.

For example the factorial function, n!, can be expressed recursively as

n! = n * (n - 1)!

for any non-negative n, with 0! defined as 1.
Thus 5! = 5 * 4 * 3 * 2 * 1 can be recast as a recursive sequence:

5! = 5 * 4!
4! = 4 * 3!
3! = 3 * 2!
2! = 2 * 1!
1! = 1 * 0! = 1

Let us first develop a function that computes the factorial function using a loop rather than using recursion. The basis for the loop implementation comes from the observation:

n! = 1 * 2 * 3 * ... * (n - 1) * n

We can write the function as follows:

Function Factorial(n)
Dim i As Integer
Factorial = 1
For i = 1 To n
Factorial = Factorial * i
Next i
End Function

To see how this function works, let us assume that n = 3. Before the loop is entered, Factorial is initialized to 1. For the first iteration of the loop, i = 1, and Factorial is computed as

Factorial = 1 * 1 = 1

On the second iteration, i = 2,

Factorial = 1 * 2 = 2

On the final iteration, i = 3,

Factorial = 2 * 3 = 6

The loop then terminates, and the function returns the correct result for #! = 6.

Note that for n = 0, the loop is never executed, and the correct result for 0! = 1 is returned.

Next we develop an alternate factorial function that exploits recursive function, that is the function actually calls itself. In order for a recursive function to eventually terminate, there must be at least one termination condition. For the factorial function that computes n!, it involves calling itself to compute (n - 1)!, which in turn call itself to compute (n - 2)!, etc. The termination condition of course comes from the fact that 1! = 1, that is for an argument of 1, the function should return the value 1.

The following is a version of such a recursive factorial function:

Function FactRecurs(n)
If n > 0 Then
FactRecurs = n * FactRecurs(n - 1)
Else
FactRecurs = 1
End If
End Function

To see how this function works, again we assume that n = 3. When the function is first invoked, the If statement will test true, since 3 > 0, and FactRecurs will be computed as

FactRecurs = 3 * FactRecurs(2)

To compute the right-hand side FactRecurs is called, except that the argument n (= 2)is one less than before. With n = 2, the If statement will again test true, and FactRecurs will be computed as

FactRecurs = 2 * FactRecurs(1)

Thus FactRecurs calls itself again, but now with an argument of 1. Again the If statement will test true, and FactRecurs will be computed as

FactRecurs = 1 * FactRecurs(0)

Now the function calls itself with an argument of 0. . This time, since n = 0, the If statement will test False, and the Else clause will be implemented as
FactRecurs = 1

without invoking itself. Thus the function terminates and returns a value of 1 to the previous invocation (FactRecurs(1)). Thus,

FactRecurs = 2 * FactRecurs(1) = 2 * 1 = 2

This process is repeated as the value is returned to the initial invocation,

FactRecurs = 3 * FactRecurs(2) = 3 * 2 = 6

and the function terminates completely and returns a value of 6.

We use both of these versions of the factorial function in TestFactorial.xls.