Custom Functions

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.

Defining the Function

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:

Calling the Function

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.

Library Loading of Custom Functions

If you wish, PipeDream can load a custom function sheet from a library directory.

Custom Function Names

The same rules apply to custom function names as to names themselves:

FUNCTION

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.

Parameters

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

RESULT

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.

Custom Function Execution

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.

Custom Function Control Statements

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))

IF - ELSE - ELSEIF - ENDIF

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))

WHILE - ENDWHILE

Loops while the condition is true.

For example:

WHILE(D56<100)
  SET_VALUE(D56,D56+E63)
ENDWHILE

REPEAT - UNTIL

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

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

CONTINUE

Execution continues at the most recent loop.

BREAK

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.

Special Functions

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