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.