ENGG1811 Lab 7: Iteration


After completing this lab, students should be able to


One mark for each part, and one for the on-line 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.


It is essential that you have completed the Pre-lab 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 sub-tasks:

  1. 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.
  2. 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
  3. 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 pre-lab 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 pre-lab 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 sub-tasks, which is an important skill that you need for software development.

Pre-Lab Exercises

Fill in the following (or make a copy on paper):

  1. What is the value of 8! (use a calculator if you like)? __________________
  2. What is the value of 9!? _________________________
  3. 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.
  4. 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:  _________________________
  5. 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 sub-program 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 PartA-PreLab and make sure it works.

This is all the Pre-Lab 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 while-loop 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 while-loop with a fixed number of iterations. This is what we have done in the Pre-Lab part. Once you know the while-loop 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

  1. Set a breakpoint (click in margin) at the first statement of the subprogram or function that contains the loop.
  2. 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.
  3. 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 hand-held 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 North-South and East-West 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 North-South 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.