PipeDream enables you to define your own functions. You can use custom functions to simplify specialised calculations which you often perform. Custom functions store in one place specialised formulae which you can use in many places.
To create and use a custom function, you must first define the function, then call it.
This is done by typing its definition into a document which becomes a 'custom function sheet'. Custom function sheets behave differently to ordinary PipeDream documents.
The definition starts with the function called FUNCTION and ends with the function called RESULT.
Before you define the function, you need to make the following decisions:
You call a custom function by typing its name into a formula, just as you do
with built-in functions such as SUM. Calls to custom functions are usually
external references and so need to be prefixed by [file]
, where
file
is the name of the custom function sheet. The exception to
this is when you call one custom function from within another custom function
on the same custom function sheet.
If you wish, PipeDream can load a custom function sheet from a library directory.
The same rules apply to custom function names as to names themselves:
The function FUNCTION is used to define a custom function. Any document containing FUNCTION automatically becomes a custom function sheet. FUNCTION must have at least one parameter and may have several. All parameters to FUNCTION must be strings.
The first parameter is the name of the custom function which you are defining.
For example:
FUNCTION("fred")
defines a custom function called 'fred'.
Parameters to FUNCTION after the first one specify the parameters to the custom function.
For example:
FUNCTION("my_add","number_l","number_2")
defines a custom function called 'my_add' with two parameters, called 'number_1' and 'number_2'.
You can specify the types of the parameters and it is usually best to do so.
For example:
FUNCTION("my_divide","one:number","two:number")
defines a custom function called 'my_divide' with two parameters called 'one' and 'two', both of which must be numbers. Note that the type must be preceded by a colon.
Several possible types may be given for each parameter, separated by commas.
For example:
FUNCTION("my_convert","one:date,text","two:number")
defines a custom function called 'my_convert' with two parameters called 'one' and 'two'. Parameter 'one' can be either a date or a text string; 'two' must be a number.
Possible types are the same as those which may be returned by the TYPE function:
array |
array or range |
date |
dates |
error |
error |
number |
numbers |
reference |
slot references |
text |
text |
If you don't specify a type, PipeDream assumes that the function will accept numbers, text and dates. If none of the custom function's parameters has the type set to array and you pass an array or range to it, PipeDream performs automatic array expansion.
When you want to use a parameter within a custom function, you need to give
its name as defined in FUNCTION, preceded by an @
character.
For example, inside the function my_add
defined above,you might
type:
@number_1+@number_2
When the custom function has finished its processing, you must return a result. You do so with the function RESULT.
For example,
RESULT(10)
returns 10 to the caller.
RESULT(A10)
returns the value of slot A10 on the custom function sheet to the caller.
On a custom function sheet, recalculation, or execution, works differently to a normal PipeDream document. You can start a custom function in any slot you like on a custom function sheet. Type FUNCTION into the slot where you want to start the custom function. Execution then proceeds down the column containing FUNCTION. A custom function sheet can contain many custom functions.
PipeDream proceeds row by row, evaluating the formula in each slot. The result of the formula is then stored in the slot. You can't see the results on a custom function sheet because PipeDream always shows the formulae rather than the results in custom function sheets.
Unless you use one of the custom function control statements, such as WHILE, IF or FOR, execution keeps going down the column until RESULT is found, at which point control passes back to the caller.
Formulae on custom function sheets are displayed with three dots (...) before them so that you can distinguish the formulae from text slots.
The custom function control statements are:
BREAK
CONTINUE
ELSE
ELSEIF
ENDIF
ENDWHILE
FOR
GOTO
IF
NEXT
REPEAT
RESULT
UNTIL
WHILE
These statements can be used to modify the flow of execution within a custom function. Normally, execution proceeds down the column starting at the slot after FUNCTION and ending at RESULT. Most control statements must appear in a slot on their own. Exceptions to this are BREAK, CONTINUE, RESULT and GOTO which can appear in function parameters.
Examples are:
IF(A76,,GOTO(A56))
IF(C<=D,RESULT(C))
When IF is used with one parameter, it starts an IF ENDIF sequence. The parameter is a condition and if the condition is true, execution continues on the following line. If the condition is not true, execution jumps to the first line after the IF statement which contains an ELSE or an ELSEIF or an ENDIF. You must have an IF before ELSE, ELSEIF or ENDIF. You do not need to provide ELSE or ELSEIF statements but you must provide an ENDIF.
For 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
You can use spaces to make IF statements more readable.
You can nest IF statements inside each other.
For example:
IF(A15>B15)
IF(TYPE(DEREF(C16))="error")
RESULT("Bad value")
ENDIF
C16+4+SUM(E1E10)
ENDIF
You can also use an IF statement with two or three parameters. In this case, the second and third parameters are used as the statements to be executed in the true and false cases and there is no ENDIF statement.
Examples are:
IF(B3>7,RESULT(C3))
IF(B5=2,,BREAK)
IF(@num<=0,RESULT(1),RESULT(@num))
Loops while the condition is true.
For example:
WHILE(D56<100)
SET_VALUE(D56,D56+E63)
ENDWHILE
Loops until the condition is true. The loop always executes once.
For example:
A10 REPEAT
A11 A11-C13
A12 UNTIL(A11<10)
This loop subtracts C13 from A11 until the value of A11 is less than 10.
Notice that there is a circular reference in A11. This is allowed in a custom function sheet.
FOR("counter_name",start_n,end_n{,step_n})
Loops while incrementing or decrementing a counter. PipeDream defines the name given in counter_name, if it is not already defined, and its value is set to start_n. Before each iteration of the loop, the counter value is compared to end_n. If the counter value is greater than end_n (or less than end_n if step_n is negative), the loop stops and execution carries on after the next statement. Otherwise, the loop is executed. After each loop iteration, step_n is added to the counter value and the process continues. If step_n is not given, the value 1 is used.
For example:
FOR("loop_counter",1,10)
IF(loop_counter>9)
SET_VALUE(D10,"nearly finished" )
ELSE
SET_VALUE(D10,"not there yet")
ENDIF
NEXT
Execution continues at the most recent loop.
BREAK{(n)}
Breaks out of the most recent loop. You can break out of more than one loop by giving a parameter.
For example:
BREAK(2)
breaks out of two loops.
The following custom functions are provided in the PipeDream Examples archive:
function("fact","arg_1")
result(if(@arg_1=1,1,@arg_1*fact(@arg_1-1)))
fact
returns the factorial of a positive integer.
Note that it is an example of a recursive custom function in that, for values greater than one, it calls itself. It is also an example of a function which doesn't check its parameters to see if it has been given sensible values!
function("find_space","str:text")
for("i",1,length(@str),1)
if(mid(@str,i,1)=char(32),break,0)
next
result(i)
find_space
finds the position of the first space in the input
string.
It sets up a FOR loop with a control variable called i
and uses
i
as an index into the string. When it finds a space, it breaks
out of the loop and returns i
. If there are no spaces in the
string, it returns the length of the string plus one.
function("strip_spaces","str:text")
set_name("stringout",@str)
while(code(stringout)=32)
set_name("stringout",right(stringout,length(stringout)-1))
if(length(stringout)<1,break,)
endwhile
result(stringout)
strip_spaces
strips the spaces from the start of a string and
gives back the string.
The name stringout
is used to store the string during the
function's execution. A WHILE loop tests whether the first character of the
string is a space (ASCII 32). If it is, stringout
is reset so that
it loses the initial space. An IF statement tests to see if the resulting
string still has any length and, if it hasn't, breaks out of the loop. Notice
that the IF does not need an ENDIF because it has more than one parameter.
Finally, when the loop terminates, or is broken out of, the new string is
returned.
You will find other custom function sheets in the PipeDream Examples archive.
Often, the best way to learn how to write custom functions is to start with a
function which someone else has written and develop it yourself. For example,
you may like to try extending strip_spaces
so that it not only
looks for spaces but also for dots at the start of the string. Or rewrite
find_space
so that, instead of returning the position of a space,
it converts all the spaces in a string to underscore characters. When you can
change existing custom functions successfully, you may then wish to start
writing custom functions of your own.
The following functions can be used with custom functions. They are described in detail in Miscellaneous Functions.
ALERT
INPUT
SET_NAME
SET_VALUE
TYPE
For details on | See |
---|---|
Library loading of custom functions | External References |
ALERT, INPUT, SET_NAME, SET_VALUE, TYPE | Miscellaneous Functions |
Automatic array expansion | Arrays |