This chapter describes how to use and write custom functions within your Fireworkz spreadsheets. It explains what a custom function is, and when it might be useful to create one, and goes on to show you how to create a custom function and to describe the tools provided by Fireworkz for this purpose.
Before you start to use and write custom functions, you will need to understand all the components of Fireworkz spreadsheets, including functions, ranges, arrays and external references. If you are unsure about any of these, read the appropriate section(s) of the User Guide.
Custom function control statements
Recalculation and custom function sheets
Custom functions are spreadsheet functions which are not built into Fireworkz, but are written by users in Fireworkz custom function language.
There are two parts to a custom function. The first, in your worksheet, is the call to the custom function, which is just like a call to an ordinary function, and specifies how you want to use the custom function.
The second, the custom function definition, specifies what the custom function actually does. This is stored in a special worksheet called a custom function sheet. Custom function sheets are different to ordinary worksheets.
Custom function sheets will usually be supporting documents to single worksheets, and will be loaded automatically when the dependent document is loaded. Remember that it is easiest to keep supporting and dependent documents in the same directory.
Custom function sheets can also be stored in a library of sheets. If Fireworkz cannot find the supporting custom function sheet in the same directory as the worksheet it is loading, it will look here for the custom function sheet which you called.
The default library location is:
Choices:Fireworkz.Library
%APPDATA%\Colton Software\Fireworkz\Library
Calling a custom function is just like calling an ordinary, built-in function. The custom function sheet containing the function must be open; you can then enter the custom function in the formula line by choosing it from the custom function section of the function selector.
The custom function dialogue box which you see will vary according to the custom functions you have loaded. If there are no custom functions loaded the list in the dialogue box will be blank.
Calling a custom function in this way creates an external reference, and therefore a dependency between your worksheet and the custom function sheet containing the custom function used.
Writing custom functions is like writing procedures and functions in BASIC or a similar language. The following description of Fireworkz custom functions assumes that you have programmed in BASIC or a similar language. You will find it difficult to write working custom functions without this type of experience.
Before you start to create your custom function, you should decide some important things about it:
Deciding all these matters in advance will make the process of creating the custom function much simpler. If you have thought it through in advance you are much more likely to write a function which works and produces the kind of results you anticipated.
A custom function sheet is not an ordinary worksheet. Formulae that you enter into custom function sheets are displayed on the sheet. Recalculation is also carried out differently; see the section below.
While reading through this section, it will be useful to have the file Tangent loaded.
This file can be found in the Custom sub-directory within the Examples directory.
This contains a call to the custom function tangent
,
stored in the custom function worksheet c_tangen
.
Unsurprisingly, this function calculates the tangent of an angle (expressed in radians).
You declare a custom function using the FUNCTION
spreadsheet function.
This must be done on a worksheet created from a special custom function template.
A custom function template, Custom, is provided in the Custom sub-directory in the Examples directory.
If you are going to create a number of custom function sheets
you might want to copy the Custom template as one of your standard templates.
You should use FUNCTION
in the following way:
FUNCTION( name:Text {, parameter_1:Text {, parameter_2:Text …} } )
You must always specify the name of a custom function.
parameter_n are optional data which may be required by your custom function.
Example:
The tangent
example custom function has one parameter,
called angle
, which is specified to be of type number
,
so its first line reads:
FUNCTION("tangent","angle:number")
All custom functions must be given a unique name, if they are to be used in your work with Fireworkz.
The name is expressed as the first parameter to FUNCTION
.
Certain rules apply to the names you can give your custom functions:
The name should appear in the following way:
FUNCTION("my_function")
In addition to the name, your custom function can have several parameters. You can add as many parameters as your function will require. For example:
FUNCTION("my_function","number_1","number_2")
Parameters must be expressed as strings, but they need not refer to strings. You can use the parameter names to reflect the type of data your function expects to be given for these parameters.
The types of data which can be used in custom functions are
the same as those which appear throughout Fireworkz,
as yielded by the TYPE
function:
array |
array or range |
date |
dates and/or times |
error |
errors |
number |
numbers |
reference |
cell references |
text |
text |
You may specify the type of data a parameter must have by appending one of the above names, preceded by a colon, to the parameter name.
For example:
FUNCTION("adding", "first:number", "second:number")
specifies a function called adding
, which has two parameters, first
and second
, which must be numbers.
If you don't specify a type, Fireworkz permits the function to accept numbers, text and dates as parameters.
If you pass an array to a custom function which has no parameters set as arrays, Fireworkz will perform automatic array processing.
Example:
In the tangent
example custom function,
the parameter angle
is specified as requiring a number,
so it is named in the function statement as:
"angle:number"
To call the parameters you have specified in the FUNCTION
definition from within the function,
you should precede their names with an @
character.
So, for example, you would have
FUNCTION("adding","one:number","two:number")
@one+@two
Whatever your custom function does, it must produce a result.
You do this using the RESULT
function, in the following form:
Syntax:
RESULT( value)
You may enter either a specific value, a formula, or a cell reference in the custom function sheet.
Example:
The tangent
example custom function has the following RESULT
:
RESULT(sin(@angle)/cos(@angle))
The value here is returned to the cell in the worksheet from which the custom function was called.
You may need to edit your custom function. When you do this, your changes will not immediately cause the custom function calls in the main worksheet to be recalculated. When you want calls to the custom function to be recalculated, re-enter the function name definition. This will cause all references to the function to be recalculated.
Normally a custom function is executed in a precise order, working line by line down the column from the cell containing the FUNCTION function which marked its beginning. As each cell is evaluated, the result is stored in the cell and this can be referred to by references in other cells.
Fireworkz includes some features for use in custom functions which are more like a programming language, and control the order in which commands are executed or formulae evaluated. These are the custom function control statements.
Most of these control statements must appear in their own cell (with their parameters if appropriate) but
BREAK
, CONTINUE
, RESULT
and GOTO
can appear in the parameters to other control statements, for example:
IF(C<=D,RESULT(C),)
Syntax #1:
BREAK
Breaks out of the most recent loop in a custom function.
Syntax #2:
BREAK( number )
You can break out of a number of containing loops at once.
Execution continues at the most recent loop in a custom function:
WHILE
loop, it continues at the next ENDWHILE
;REPEAT
loop, it continues at UNTIL
;FOR
loop, it continues at NEXT
.Statements between the CONTINUE and the pertinent loop continuation statement are not evaluated.
Repetetive execution of statements in a custom function, controlled by a counter.
FOR( counter_name:Text, start:Number, end:Number {, step:Number} )
The named counter is initialised to start.
Statements in the loop are executed if the counter is less than, or equal to, end.
The NEXT
statement signals the end of an iteration.
At the end of each iteration 1 (one) is added to the counter,
unless the optional step parameter is specified,
in which case that value is added.
When the counter passes the end value, execution continues after the NEXT
statement.
If step is negative, the loop will be iterated while the counter is greater than, or equal to, end.
Example:
The following custom function code scans an array of numbers and builds a set of square roots of the number in column D, checking for negative roots along the way:
FOR("i",1,rows(@list)) IF(index(@list,1,i)<0) SET_VALUE(INDEX(D1:D100,1,i),0) ELSE SET_VALUE(INDEX(D1:D100,1,i),SQR(INDEX(@list,1,i))) ENDIF NEXT
Move to a specified statement in a custom function.
Syntax:
GOTO( cell_reference )
Moves to the statement specified by cell_reference.
Conditional execution of statements in a custom function.
Syntax #1:
IF( parameter )
When IF
has only a single parameter,
it signals the start of an IF … ENDIF
sequence.
The parameter is a condition:
TRUE
, execution follows on the following line;FALSE
, execution continues after the next
ELSE
, ELSEIF
or ENDIF
statement that follows.
Example:
IF(@parameter_1>10) SET_VALUE(C10,1) ELSEIF(@parameter_1>8) SET_VALUE(C12,1) ELSEIF(@parameter_1>6) SET_VALUE(C14,1) ELSE SET_VALUE(C16,1) ENDIF
ENDIF
marks the end of the sequence,
ELSEIF
marks the beginning of a second condition, and
ELSE
a statement to be executed if the preceding conditions evaluated to FALSE
.
You can nest IF
statements inside each other,
and it is advisable to use spaces to make IF
statements more readable:
IF(A15>B15) IF(TYPE(DEREF(C16))="error") RESULT("Bad value") ENDIF C16+4+SUM(E1:E10) ENDIF
Syntax #2:
IF( parameter_1, parameter_2 {, parameter_3} )
If the condition expressed in parameter_1 is TRUE
,
then parameter_2 is executed.
If it is FALSE
, execution continues or if a third parameter is supplied, that is executed.
You should not use an ENDIF
statement to mark the end of the sequence.
Examples:
IF(B3>7,RESULT(C3))
IF(B5=2,,BREAK)
IF(@num<=0,RESULT(1),RESULT(@num))
Repetetive execution of statements in a custom function, controlled by condition at end of loop.
Syntax:
REPEAT
loop code
UNTIL( condition )
Loops executing statements between the REPEAT
and UNTIL
statements until the condition is TRUE
.
Note that the loop will always execute at least once.
Example:
REPEAT SET_VALUE(remainder,remainder-divisor) UNTIL(remainder<divisor)
Returning a value from a custom function.
Syntax:
RESULT( value )
As described above, a RESULT
statement marks the end of the custom function.
The value is returned as the result of the function.
Repetetive execution of statements in a custom function, controlled by condition at start of loop.
Syntax:
WHILE( condition )
loop code
ENDWHILE
Loops executing statements between the WHILE
and ENDWHILE
statements while the condition is TRUE
.
Example:
WHILE(D56 < 100) SET_VALUE(D56, D56+E63) ENDWHILE
You can use custom functions to process arrays of data or ranges of cells. The example Total, in the sub-directory Custom in the Examples directory, illustrates this use of custom functions. It returns the sum of the numbers contained in the first column of the range of cells. It could be used as the starting point for your own custom functions which process ranges of cells.
Cell A4 SET_NAME("sumtotal",D4)
creates a name sumtotal and defines it to refer to the cell D4
.
Cell A5 SET_VALUE("sumtotal",0)
sets the value of sumtotal (i.e. D4
) to 0
.
Note the use of quotes in A4
and their absence in A5
.
In cell A8, FOR("i",1,rows(@list))
, the part rows(@list)
returns the number of rows in the array.
Cell A9 ...index(@list,1,i)
returns the i‑th element of the array to be added to the total.
Ordinary worksheets use natural recalculation. This means that Fireworkz builds a tree of dependencies and uses this dependency tree to determine the order of recalculation. Custom function sheets are recalculated differently, with recalculation proceeding down the worksheet from one row to the next.
The only links between the worksheet and the custom function sheet are the function name definition cells. Each of these cells is known about by the dependency tree, but none of the rest of the custom function is put on the dependency tree.
For this reason you should make not references into a custom function sheet from an external worksheet and you should not make references from a custom function sheet to an external worksheet. All of the data needed by a custom function should be passed into the function as function parameters. All of the data which the function exports should be passed out using RESULT, perhaps passing out an array.