Writing Your Own Spreadsheet Functions

With OOo, it's trivial to write and use your own functions that are recognized by the Calc document. It's as simple as writing a macro and then addressing it directly. I wrote an example of this in Listing 39 that returns information about the passed argument as a String. Table 51 shows the return values for different arguments.

Table 51: Return values for different arguments to WahooFunc (assume cell E9 contains 2).

Function	Return
"=WahooFunc()"	I am in WahooFunc. No argument was passed.
"=WahooFunc(E9)"	I am in WahooFunc. Scalar argument (2) is type Double.
"=WahooFunc(2)"	I am in WahooFunc. Scalar argument (2) is type Double.
"=WahooFunc(A11:C15)"	I am in WahooFunc. Argument is an array (1 To 5, 1 To 3).

Listing 39: WahooFunc is in the Calc module in this chapter's source code files as SC14.sxc.

 Function WahooFunc(Optional x) As Variant   Dim s$   s = "I am in WahooFunc. "   If IsMissing(x) Then     s = s & "No argument was passed"   ElseIf NOT IsArray(x) Then     s = s & "Scalar argument (" & CStr(x) & ") is type " & TypeName(x)   Else     s = s & "Argument is an array (" & LBound(x, 1) & " To " & UBound(x, 1) &_             ", " & LBound(x, 2) & " To " & UBound(x, 2) & ")"   End If   WahooFunc = s End Function 

 

The argument that is passed to the function that you write, may be missing. If the argument is declared as optional, use the IsMissing() method to test if a value has been passed. For example, "=WahooFunc()".

You can directly pass a single scalar value either as a constant, or by referencing a single cell. For example, "=WahooFunc(32)" and "=WahooFunc(E7)" both pass a scalar value to the function. The first example passes the numeric value 32 and the second example passes the contents of cell E7.

Tip  

The actual type of argument that is passed to your own functions depends on how it is called (see Table 51). When a range is used as the argument, the data is passed as a two-dimensional array that does not use the traditional lower bound of zero.

If the argument refers to a range, a two-dimensional array is passed to the function. For example, "=WahooFunc(E7:F32)" passes the contents of the cells in the range E7 through F32 as a two-dimensional array. Be absolutely certain to use the functions LBound() and UBound(), because the lower bounds start at 1 rather than at the expected value of 0 (see Listing 40 ).

Listing 40: Add all elements together.

 Function SumAll(myArray as Variant)   Dim iRow%, iCol%   Dim d As Double   For iRow = LBound(myArray, 1) To UBound(myArray, 1)     For iCol = LBound(myArray, 2) To UBound(myArray, 2)       d = d + myArray(iRow, iCol)     Next   Next   SumAll = d End Function