The Spreadsheet Manual
Part Two, Reference
The OPL Function
The OPL Function
It is recommended that you use this function only when you have a sound working knowledge of OPL (Organiser Programming Language) and the way in which values are passed back from OPL procedures. Refer to your Organiser manual for further details.
NB. Formulae containing the OPL function cannot be Exported. Each cell referring to the function will display =ERR on the other computer.
The OPL function adds three facilities to the Spreadsheet:
The OPL function is entered into a cell as a formula. Then when the grid is recalculated, in order to work out this formula the gild calls a named procedure, which then returns a value to the cell in which the formula was entered.
The OPL function has this syntax:
<cell> is the reference of a cell containing an OPL procedure name.
The procedure name must be a valid one, excluding the trailing colon. The procedure itself:
<range> is a cell range whose contents are used as the input values of the procedure named in <cell>. The cells:
Even if <range> contains only one cell, <range> must still be a fully specified range, in the format B2:B2.
When the current worksheet is recalculated, the procedure named in <cell> takes as its input value the value contained in the first cell in <range>. The procedure runs as normal, then returns a value to <cell>. Then, if there is another cell in <range>, the value In this cell Is used as the input value, and the new result returned to <cell>. This continues until the end of <range>.
Two new functions are added to OPL while the Spreadsheet is installed in the Organiser: CELL: and CELL$:.
These functions pass to the procedure named in <cell> the value contained in the first cell in <range>, then in the second and so on until the end of the range. They ignore blank cells.
The values in the cells in <range> are accessed by the named procedure from left to right and from top to bottom, i.e. in the order:
Once the contents of one cell have been 'grabbed’ from the Spreadsheet, an internal pointer is set to the next non-blank cell in the range.
Unless trapped with OPL's ONERR command, these errors are reported in the usual way, the procedure ends and the OPL function in the Spreadsheet returns the value ERR.
When the OPL function is used in a Spreadsheet, the procedure name(s) present in the grid call their procedures every time the worksheet Is recalculated. You should therefore set the recalculation mode to Manual with the Recalc command before setting up the worksheet.
Example: Share Portfolio
The share portfolio worksheet set up in the tutorial can be improved by using the OPL function to:
This means that you can find out the result you designed the Spreadsheet to calculate, but without having to actually see and move around the grid itself.
A first procedure, called ASK:, prompts for a price for each of the shares held and inserts that price into the appropriate cell in the worksheet. Then a second procedure, called TELL:, 'grabs' the grand total from the cell in which it has been worked out and prints it to the screen.
Here is the original worksheet:
| |QTY |TODAY|SUB-T |TOTAL |F |PORT| | | | | |BT | 1000| 2.37| 2370.00| 3190.00| |TSB | 500| 0.9| 450.00| | |GAS | 200| 0.75| 150.00| | |BA | 200| 1.1| 220.00| |
Either load this worksheet if you had originally saved it, or enter it afresh if you hadn't (see tutorial example for details). Then:
PRINT "Todays value of"
PRINT "Todays total"
The ASK: procedure will be called from each cell in turn where the price of a share was entered in the original worksheet. The number you then enter (INPUT a) is entered into the cell which called the procedure. This needs to be a different cell for each share price, so:
When you press EXE the formula is evaluated. Enter today's price of the BT shares, as asked:
Todays value of BT:
The OPL function has as its parameters:
To make the grid automatically tell you today's total:
This function performs the procedure named in cell F3 (TELL:) on the contents of the single-cell range E3:E3 i.e. the cell displaying the folio total. When you press EXE to enter the formula, the screen tells you the total value of your shares, like this:
Todays total = 3190
To improve this worksheet, make sure that the folio total returned by TELL: is a rounded figure. This is necessary because, though cell E3 displays the result to only two decimal places, it stores it to more than two - and it is the stored number which the TELL: procedure takes and displays on the screen. It would be Inappropriate to show a result of more than 2 decimal places.
This takes the total calculated in cell E3 and rounds it to two decimal places.
so that TELL: takes the rounded value in cell E4, not the full value in E3.