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.
FUNCTION("fred")
defines a custom function called ‘fred’.
Parameters to FUNCTION after the first one specify the parameters to the custom function.
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.
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.
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 of cells |
date |
dates |
error |
error |
number |
numbers |
reference |
cell 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 of cells 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 using the function RESULT.
RESULT(10)
returns 10 to the caller.
RESULT(A10)
returns the value of cell A10 on the custom function sheet to the caller.
On a custom function sheet, recalculation, or execution of custom function statements, works differently to a normal PipeDream document. You can start a custom function in any cell you like on a custom function sheet. Type FUNCTION into the cell 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 cell. The result of the formula is then stored in the cell. 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 FOR, IF or WHILE, 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 cells.
The custom function control statements are:
These statements can be used to modify the flow of execution within a custom function. Normally, execution proceeds down the column starting at the cell after FUNCTION and ending at RESULT. Most control statements must appear in a cell on their own. Exceptions to this are BREAK, CONTINUE, RESULT and GOTO which can appear in function parameters.
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.
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.
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.
WHILE(D56<100) SET_VALUE(D56,D56+E63) ENDWHILE
Loops until the condition is true. The loop always executes once.
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, statements in the loop are 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 (one) is used.
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.
BREAK(2)
breaks out of two loops.
The following custom functions are provided in the PipeDream Examples:
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.
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