## ENGG1811 Lab 7: Iteration

### Objectives

After completing this lab, students should be able to

• Use the debugger to single-step a subprogram that uses iteration
• 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

### Assessment

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. Half a mark is deducted if the online style assessor reports a mark less than 3.5/4.

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 Pre-lab exercises for Part A, and preferably have devised the algorithm itself before you attend the lab.

### 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

• 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).Value = myData  ' fully qualified
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).

#### Pre-Lab Exercises

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

#### 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.