Chapter 9 Loops

9.1 For… Next

The For… Next structure is a relatively simple one, and so there is not to much to add to the material in the lectures. However, there are some points of note concerning the step size.

First, the step size may not exactly divide the range of numbers which you wish to consider. For example, consider the function

Function oddstep(n As Integer, m As Integer) As Integer
Dim i As Integer
oddstep = 0
For i = 1 To n Step m
    oddstep = oddstep + i
Next i
End Function

This function will loop until \(i\) is greater than \(n\) and then stop. If we calculate the value of this function when \(n=10\) and \(m=2\) then we are calculating \[1+3+5+7+9=25.\]

The step size can even be a negative number. For example, consider the function

Function bavkward(n As Integer) As Integer
Dim i As Integer
backward = 1
For i = n To 1 Step -1
    backward = backward * i
Next i
End Function

This is a rather strange implementation of the usual factorial function, but counting down from \(n\) to \(1\).

Finally, we should remark on the use of the Exit For statement. This is an optional line in a For… Next loop, which causes the function to leave the loop immediately and proceed to the next command. For example, suppose that we have a function prime(n) which returns the value True if \(n\) is prime and False otherwise. (It is not hard to define such a function, but we will not do it here.) The function

Function largeprime(m As Integer) As Integer
Dim i As Integer
For i = m To 1 Step -1
    If prime(i) = True Then
        largeprime = i
        Exit For
    End If
Next i
End Function

would then calculate the largest prime less than or equal to some number \(m\). If \(m\) was very large then we would not want to waste a long time calculating whether all of the numbers smaller than \(m\) are prime once we have already found the largest one.

9.2 Do While… and Do Until… Loop

These two kinds of loop are a little more subtle than the For… Next loops introduced earlier, and so we will devote a little more space to discussing some of their features. First let us reconsider in more detail the first example from lectures:

Function SumToN(n As Integer) As Integer
Dim i As Integer
i = 1
SumToN = 0
Do Until i = n + 1
    SumToN = SumToN + i
    i = i + 1
Loop
End Function

It is important to keep track of the order in which the various operations are carried out. We start with i=1 and SumToN=0. Note that if we do not define a starting value for a variable, it will take the value \(0\). It is very good practice always to define the initial values of all variables, to make sure that you do not make a mistake. This is a common source of error.

In the main loop we first check whether i=n+1 then update the value of SumToN, and finally increase i by \(1\). Then the process repeats, and stops when i=n+1. Thus the first step in the loop adds \(1\) to SumToN and the last step adds n to SumToN.

If the order of the two statements in the Do Until loop were reversed, then we would increase the value of i before adding i to SumToN. In that case the first step would add \(2\) to SumToN and the last step would add n+1 to SumToN. Getting confused about the order of operations and their effect on each other is a common source of error.

Next consider the following example of a double sum from lectures.

Function DoubleSum(n As Integer) As Integer
Dim t As Integer, i As Integer
DoubleSum = 0 
t = 1
Do While t <= n
    i = 1
    Do While i <= t
        DoubleSum = DoubleSum + i
        i = i + 1
    Loop
    t = t + 1
Loop
End Function

Here we have two loop variables \(t\) and \(i\). Looking at the original function from the lectures, we see that we need to vary \(t\) between \(1\) and \(n\), and then for each value of \(t\) calculate the sum from \(1\) to \(t\). This sum is calculated in the inner of the two loops, adding the result to the running total of the entire function so far.

The variables need to be initialised more carefully. We start by setting t=1 and DoubleSum=0. However, we do not set i=1 until we are inside the first loop. This is because each time we carry out a complete run of the inner loop we need to start afresh from i=1, and so we have to make sure that each time we restart we reinitialise the variable. Forgetting to initialise variables in circumstances such as this is an even more costly error than before.

9.3 Recursive functions

In lectures we used a loop to construct a recursive function. However a better and more natural way to define a recursive function is to write a function that calls itself in the middle. This may seem like it would be a nonsensical thing to do, as you might imagine that such a function would get trapped in an endless regress. However, when written carefully, such functions can be very powerful.

As a simple example, let us write our own version of the factorial function.

Function diyFact(n As Integer) As Integer
If n = 1 Then 
    diyFact = 1
Else
    diyFact = n * diyFact(n-1)
End If
End Function

To calculate diyFact(n) we need to know the value of the function at \(n-1\). This process does not go on forever as we know the value when \(n=1\), and so the function will eventually terminate.

Recursive functions are a very powerful tool to have at our disposal, and VBA is happy to define such functions. However you can easily create a function that does not ever terminate, so just as for loops you need to be careful to avoid writing code that never ends.

As another, slightly more complicated, example we will consider the Fibonacci sequence. The Fibonacci numbers are defined by the formula \[F(n)=\left\{\begin{array}{ll} 1 & \text{if}\ n=1\ \text{or}\ n=2\\ F(n-1)+F(n-2) &\text{otherwise.}\end{array}\right.\] Thus the first few terms in the sequence are \[1,1,2,3,5,8,13,21,34.\] The \(n\)th Fibonacci number can be calculated using the following function.

Function Fibonacci(n As Integer) As Integer
If n = 1 Or n = 2 Then
    Fibonaaci = 1
Else
    Fibonacci = Fibonacci(n-1) + Fibonacci(n-2)
End If
End Function

9.4 Looping through cells in a spreadsheet

In Excel we often want to carry out some kind of repetitive task involving a list or table of cells. Loops are ideal for automating this kind of process. In order to illustrate this we will need the very useful VBA function Cells(i,j) which refers to the value of the cell in row \(i\) and column \(j\) of the active spreadsheet. Note the order of these two arguments, this is not the same convention we use for naming cells by letters and numbers. To illustrate how we might use this function, consider the following example.

Suppose that we are given data in the range A1:A30 in our spreadsheet. We wish to count the number of cells containing numbers larger than some value \(x\). Here is one way to write such a function:

Function CellCount( x As Single) As Integer
Dim i As Integer
CellCount = 0
For i = 1 To 30
    If Cells(i, 1) > x Then
        CellCount = CellCount + 1
    End If
Next i
End Function

As a second example, suppose that we wished to add up the entries in all of the cells in the range given by the first \(m\) rows and \(n\) columns. This could be carried out using the simple function

Function RangeSum(m As Integer, n As Integer) As Single
' m is the number of rows
' n is the number of columns
Dim i As Integer, j As Integer
RangeSum = 0
For i = 1 To m
    For j = 1 To n
        RangeSum = RangeSum + Cells(i,j)
    Next j
Next i
End Function

We have (for once) put a couple of comments in the code to remind us which variable corresponds to the rows and which to the columns.

Having used VBA to read the contents of a cell, you might expect that you would be able to write to reverse the process and write values to cells using a user defined function. However, this is not possible! User defined functions are not able to change properties of arbitrary cells in a spreadsheet, such as their value or their format. They are (as the name suggests) just functions, which calculate something and return a value in the cell where they are called from.

Clearly we would like to be able to write programs that do have the ability to change the nature of the spreadsheet. This can be done, but with what is called a Sub procedure. We will consider how to write and use Sub procedures in the next Chapter.