ENGG1811 Lab 7: Iteration
Objectives
After completing this lab, students should be able to
 Use While or For statements to implement algorithms involving simple
iteration
 Read and write values to/from cells on the active worksheet, including
named cells
 Learn to break down a programming job into a number of subtasks
Assessment
One mark for each part, and one for the online assessment, which deals
with loop statements. Your solutions must be neat and tidy, including
consistent capitalisation and indenting, to get full marks.
Your tutor may ask you questions about the programs to be sure you fully
understand how you developed the solutions.
Preparation
It is essential that you have completed the Prelab
exercises for Part A, and preferably have devised the algorithm itself
before you attend the lab.
Download the workbook lab07.ods.
Part A: Iteration and Cell Processing
Last week's lectures introduced the concept of iteration, and included
examples that processed
columns of data. In this lab we will calculate factorials
(n! = 1 * 2 * 3 * ... * (n–1) * n)
using OO Basic and display them on a spreadsheet.
The table of factorials should occupy Columns 1 and 2 of the active
sheet, starting at row 2 (row 1 has column headings). Column 1 is for n,
column 2 is for n!. Your OO Basic program should fill in Columns A
and B. Note that Column C contains a formula and you do not need to do
anything with it. The first 10 rows of the completed worksheet should look
like the following:
To generate the table, you will need to able to do the following subtasks:
 A subprogram that contains a loop that generates factorials in order,
starting with 0! = 1, 1! = 1*0! = 1, 2! = 2*1! = 2, 3! = 3*2! = 6 and so
on. You'll notice that each step uses the next value of n and
the previous factorial value to produce the next.
 A means of writing values onto particular cells on the active
worksheet. As we've seen in lectures, given variables holding the row
and column numbers, you can assign to a cell using:
ActiveSheet.Cells(row, col) = myData ' default property is Value
 Some way of stopping the generation process. For this version there
is a cell on the sheet named MaxFact. Pick up this value using
the ActiveSheet.Range(name)
notation, where name is a string
constant or variable holding the name or address of the required cell.
Stop the iteration when the factorial exceeds this value, but before
displaying it (the algorithm structure should allow this).
Unless you are an expert programmer, it can be overwhelming to try to do
all of the above in one go. The recommendation is that you break the work
down into smaller tasks. You should try to complete the prelab exercises
which help you to realise a program that can calculate the factorial (Task
#1 above) without thinking about writing to the cells (Task #2 above) and
stopping at calculation at the right place (Task #3 above). After completing
the prelab exercises, you will be ready to try to tackle Tasks #2 and #3.
We hope that you will learn from this process on how you can break a piece
of programming work into smaller subtasks, which is an important skill
that you need for software development.
PreLab Exercises
Fill in the following (or make a copy on paper):
 What is the value of 8! (use a calculator if you like)?
__________________
 What is the value of 9!? _________________________
 Did you recalculate 9! from the start, or did you use the
previous answer (tick one)? [_] I used 8! [_] I recalculated
from the start—ah I see now that that's more work.
 If you calculated 9! from the start, we want you to think
about how you can calculate 9! by using 8!. Write your method
here: _________________________
 There are a number of different tasks that you need to do
for Part A. You need to calculate the factorial and to display
the results. A good software development habit is to divide
the work into small parts. We will work on the computation of
factorial first. The module PartA_PreLab contains a
subprogram that is partially complete. The program contains a
while loop, a variable n
and a variable nFact.
The aim is to use the variable nFact
to store the value of n!.
If you run the program, you will find that it pops a MsgBox
which shows "n = 0", "n = 1", ..., up to "n = 4". The MsgBox
also shows the value of nFact
but because you haven't implemented the calculations of
factorial, it always says "nFact = 0". If you have implemented
the factorial calculations correctly, you expect to see Msgbox
displaying these messages:
 n = 0, nFact = 1
 n = 1, nFact = 1
 n = 2, nFact = 2
 n = 3, nFact = 6
 n = 4, nFact = 24
Recall that you figured out earlier
that you can calculate factorial of a number from the factorial of
a smaller number. You should try to come out with a BASIC
statement which updates the nFact
value at each iteration. A way to think about that is to try to
use the expected messages shown about, take the nFact
value from one line and see how you can obtain it from the nFact
value in the line above it. If you have difficulty in coming out
with the answer, take a look at the examples in the lecture notes
on summing up a sequence of numbers; the calculations of factorial
is analogous to that. You can write down you BASIC statement here:
____________________________________________________________
Note that you need to think about
initial condition for nFact.
Implement it in the program PartAPreLab and make sure it works.
This is all the PreLab exercises. The following two points will
guide you to complete the whole Part A.
 If you have successfully completed the last task,
you can transfer your program to PartA_Factorial. Your aim is
to display the value of n
and its factorial in Columns A and B. You can ignore the limit
for now and see whether you can display the first five
factorials.
 There is only one more task for you to do and this is to
generate all the factorials up to MaxFact.
 We would like to add a remark here on writing loops. Note
that a whileloop has two aspects: (1) What the loop needs
to iterate; (2) When to stop the iteration. Sometimes it is
hard to think about both at the same time. What we are
suggesting here is to work on these two aspects
independently. We can ignore the question on "When to stop
the iteration" by writing a whileloop with a fixed number
of iterations. This is what we have done in the PreLab
part. Once you know the whileloop is working correctly for
a fixed number of iterations, you can think about inserting
the right condition so that the loop stops at the right
time.

In the Lab
 Assuming you have the editor open, select the module
PartA_Factorial. Complete the subprogram. Use the supplied
constants for the limit, and fixed row and column numbers.
 Check for gross errors with the Compile icon.
 As this subprogram is the first in the module and it has no
parameters, you can run it just by pressing F5. Make sure the PartA_Fact
sheet is visible, or it will scribble over the wrong one. If you
don't see at least 20 rows filled then something's wrong. Column
C shows the ratio of adjacent values, so should be the same as n.
If it's producing wrong values review your code by trying to
imagine what's happening. Use the debugger (see sidebar) to
see where incorrect values are assigned to variables. Your
tutor may be able to suggest where the problem lies, but they
will only give you hints about how to correct it. You have to
learn this skill yourself, in time.
 When you are successful in implementing this (very small)
program, your tutor will ask you something like: "What would
happen if these two statements were reversed [pointing to two of
the update assignment statements]?" or "Why can't you use a For
loop instead of a While loop?"

Tracing Loops
 Set a breakpoint (click in margin) at the first statement of
the subprogram or function that contains the loop.
 Run the program as normal. When it stops at the breakpoint,
use the F8 key (on the iMac keyboard you also have to
hold down the Fn key) to step through one statement at a
time.
 Examine variables by hovering the mouse over them. You can
only see the values of variables in the currently executing
procedure.

Extension (Optional)
Extend the subprogram so that it clears any entries in the first two columns
below the table just generated, to avoid confusion if the limit is reduced.
Part B: MiniMax
The sheet PartB_Coords
contains grid coordinates representing the outline of the UNSW Kensington
campus, obtained with a handheld GPS navigator. Grid coordinates are
distances in metres east ("eastings") and north ("northings") of a fixed
point. Eastings are in Column A and Northings in Column B. Develop a
subprogram that calculates the extent of the data. The extent of a
set of coordinates is the maximum NorthSouth and EastWest distances. The
subprogram should write to the cell named EastWest the difference
in kilometres between the largest and smallest easting, and similarly
stores the maximum NorthSouth distance in the cell named NorthSouth.
How to reference named cells with OO Basic is described in Part A above.
You should devise a solution using pseudocode first. When you split the
problem into subtasks you should find that the same thing has to be done
to the two columns. Try to express that common subtask as a single pass
over a column of numbers, keeping the current minimum and maximum in
separate variables. This subtask must be in a function of its own with the
column number passed to it. The function returns the difference between
the calculated maximum and minimum.