ENGG1811 Lab 6: Functions and Selection

The on-line assessment is a thinking exercise (what's wrong with this algorithm-type question). It's recommended that you complete Part A before attempting it.

You should get through Part A and most of B in the first hour and do Part C in the second hour. You must show your tutor Part B by the mid-point of the second hour or you may not be able to be assessed at all. Don't panic if you don't get Part C finished, A and B are the most important. Remember to save from within the editor!

There's a fair amount of description in the lab notes: if your considered reaction is TL;DR and you understand generally what's going on, here is the quick version:
  • Part A: debug the subprogram ThinkOfANumber in module PartA. Use the sheet PartA_NumThink.
  • Part B: write a function B2A that accepts a bearing and returns the equivalent mathematical angle; test using the data on sheet PartB_B2A.
  • Part C: write a function EFujita that classifies a tornado given its maximum wind speed, using the enhanced Fujita scale descriptors as defined in any reputable source. Test using sheet PartC_Tornado.


After completing this lab, students should be able to


You should be ready to demonstrate the results Parts B and C to your tutor. They will decide which programs to question you about. Part B and C are each worth 1 mark, but you will lose a half mark if the Style Assessor gives you less than 3.5 out of 4.


It is essential that you have devised the algorithm for Part B before you attend the lab, have pseudocode (mixed English statements with Basic structures) on paper, and preferably have worked out the whole function. If you wait until getting to the lab before even thinking about the problem, the two hours will evaporate before you know it.

Download the spreadsheet lab06.ods. It has one sheet for each part, and either incomplete or incorrect procedures.

Part A: Tracing and Debugging

Open the lab workbook, and enable macros if necessary. Select sheet PartA_NumThink, enter 1 in any cell and leave it as the active cell (bold border). Edit the ThinkOfANumber subprogram by navigating to Tools - Macros - Organize Macros - OpenOffice Basic; click the + next to lab06.ods, select Standard and PartA. Try to keep both the sheet and the editor both visible.

The subprogram aptly named ThinkOfANumber simulates a simple puzzle that goes like this:

And the "magician" states the answer, which is of course the same regardless of the initial number. (if you don't know what it is, have a bit of a think about it).

We want you to fix up a few things that are wrong with the implementation.

  1. The constant definition for ANSWER is obviously wrong. Fix it.
  2. Run the subprogram by pressing F5, or the little green arrow on the toolbar.

    Oops, the letters 'n' and 'b' are adjacent on the keyboard so it's a simple typo, that why we always add Option Explicit to every module.
  3. Place a breakpoint at the first statement (the first If) by double-clicking in the margin next to it. Run the subprogram again. The program pauses at the statement with the big brown dot.
  4. Step through the program using the F8 (single-step) key. Assuming the active cell still contains 1, you can see that the main If statement is skipped because num9 has a single digit.
  5. Go back to the sheet and enter a number between 2 and 9 in the active cell. Repeat the trace, stopping at each step in the main If to confirm that the two digits are extracted correctly (hover the mouse over variable names).
  6. Go back to the sheet and change the active cell so it contains text, not a number. Rerun the program. It should give the first message and execute the Exit Sub statement, which returns control to the user.
  7. Repeat with an empty cell. IsNumeric does seem to work (whereas VBA's IsNumeric misclassifies empty strings).
  8. Now repeat with 42 in the active cell. Not a very sensible response, is it? We've made an assumption that anything numeric is valid, that's not true. Add an If statement after num is assigned, so that if it's out of range the program produces a message and Exits, just like the numeric test. That will be a full Boolean expression, think about the operator: is it And or Or?
  9. Confirm that the program now only performs the calculations for numbers in the correct range. We could keep going, making sure only integers are provided, but that's enough for now.

Part B: Bearing to Angle Conversion

Bearings are angular measurements used in surveying and navigation. They are expressed in degrees clockwise from North (conventionally shown upwards). On the other hand, positive mathematical angles are counted anti-clockwise from the X axis, and negative ones clockwise. For example, the red line on the diagrams below is at an angle of –45 degrees, or bearing 135 degrees.

Bearing and Angle definition

To use trigonometric functions with angles expressed as bearings, we would need to convert them to mathematical angles in the range –180 to 180 (and then probably to radians of course).

  1. Print and fill in the table below, using the diagrams as a guide.

    Bearing Angle

  2. The following linear formula expresses the conversion for any bearing in the range 0 to 270 degrees. What are appropriate values for the parameters m and b?

        angle = m * bearing + b, where   m = _______ b = ________

  3. If we apply the same formula to the range 270 to 360 as well, what adjustment would we need to apply to bring the value into the required range?


  4. Now express the whole conversion as in pseudocode (a mixture of English phrases, program variables and Basic control structures such as If and, later, While). Use an initial variable bearing and a resulting variable angle (whose value can be changed as needed).
    ' given: bearing in degrees, real number, 0.0 to 360.0
    ' calculate: dblAngle in mathematical degrees, -180.0 to 180.0
    ' preferred angle for the negative X axis is 180 rather than -180

  5. Module PartB in the lab workbook contains a function B2A in which you should place your implementation. Use the named constants provided (CIRCLE, QTR_CIRCLE, MIN_ANGLE, MAX_ANGLE) to avoid non-obvious numbers appearing in the program.

    B2A already has a parameter called bearing and it returns another Double. Recall that a function returns what ever is last assigned to the function name during execution of the function. Currently the function just returns zero at the end. Assign your final angle to it instead, either there or inside your If statement.

  6. Correct any errors reported by the Compiler (the icon to the left of the run arrow).

  7. The worksheet PartB_B2A has a variety of numbers in column A and a call on your B2A function in cell B2 (inside an If to skip the call if there's no bearing). When you think you have the function working, trigger a recalculation by editing the formula in B2 (just re-type the "A" in B2A), then fill the cells down to row 20.

    If any result is outside the valid range, or exactly –180 (since 180 is preferred) conditional formatting will highlight the cell.

  8. What happens if the function is applied to a cell containing other than a number? (You don't have to deal with this case.)

Part C: Enhanced Fujita Scale

The amount of damage inflicted by a tornado is related to peak wind speed. The Enhanced* Fujita Scale uses an integer between 0 and 5 (plus the prefix "EF") to describe 6 levels of damage to typical structures. Each number on the EF scale corresponds to an estimated range of wind speeds:

Scale Minimum wind speed
Damage descriptor
EF0 105 Light
EF1 138 Moderate
EF2 179 Considerable
EF3 219 Severe
EF4 267 Devastating
EF5 322 Explosive

* Modified in 2007 because Ted Fujita's original 1971 scale overestimated the wind speed needed to produce particular damage patterns.

Insert a new module, which you can do by right-clicking in the vacant area next to the PartB tab in the editor. It will be given a default name, right-click on the tab to rename it PartC or Fujita so the Style Assessor can find it.

Implement a function called EFujita that accepts wind speed in km/hr and returns a string representing the scale and its descriptor. For example, EFujita(162.5) would return the string "EF2 - Moderate damage". For winds below the minimum speed, return the value "No damage".

Define constants for the minimum speeds, for example

Const EF0_MIN = 105
Const EF1_MIN = 138

Hint: the UNSWgrade lecture example has the same structure. Select the highest category first (that is, work from max speed to min), as this allows the most sensible and readable use of the constants.

The PartC_Tornado sheet in the lab workbook contains wind speeds in column A. Use a formula to call on your function to fill in the Effect column. Don't fill down until you are confident it's producing the right answer for the first value.