Calc Guide

The macro in Listing 5 demonstrates some important techniques:
1) The argument x is optional. When an argument is not optional and the function
is called without it, OOo prints a warning message every time the macro is
called. If Calc calls the function many times, then the error is displayed many
times.
2) IsMissing checks that an argument was passed before the argument is used.
3) IsArray checks to see if the argument is a single value, or an array. For
example, =PositiveSum(7) or =PositiveSum(A4). In the first case, the number
7 is passed as an argument, and in the second case, the value of cell A4 is
passed to the function.
4) If a range is passed to the function, it is passed as a two-dimensional array of
values; for example, =PositiveSum(A2:B5). LBound and UBound are used to
determine the array bounds that are used. Although the lower bound is one, it
is considered safer to use LBound in case it changes in the future.
Tip
The macro in Listing 5 is careful and checks to see if the argument is an
array or a single argument. The macro does not verify that each value is
numeric. You may be as careful as you like. The more things you check, the
more robust the macro is, and the slower it runs.
Passing one argument is as easy as passing two: add another argument to the
function definition (see Listing 6). When calling a function with two arguments,
separate the arguments with a semicolon; for example, =TestMax(3; -4).
Listing 6. TestMax accepts two arguments and returns the larger of the two.
Function TestMax(x, y)
If x >= y Then
TestMax = x
Else
TestMax = y
End If
End Function
Arguments are passed as values
Arguments passed to a macro from Calc are always values. It is not possible to know
what cells, if any, are used. For example, =PositiveSum(A3) passes the value of cell
A3, and PositiveSum has no way of knowing that cell A3 was used. If you must know
which cells are referenced rather than the values in the cells, pass the range as a
string, parse the string, and obtain the values in the referenced cells.
Writing macros that act like built-in functions
Although Calc finds and calls macros as normal functions, they do not really behave
as built-in functions. For example, macros do not appear in the function lists. It is
possible to write functions that behave as regular functions by writing an Add-In.
However, this is an advanced topic that is not covered here; see
http://wiki.services.openoffice.org/wiki/SimpleCalcAddIn
Chapter 12 Calc Macros 309