Revision as of 11:07, 10 May 2010

Simple functions

You can write simple functions to make your spreadsheets more readable and easier to maintain. The process of developing and using these functions is very similar to other spreadsheet applications. For an introduction see Extensions development basic. Some examples in OpenOffice.org Basic follow.

Where the Basic functions must be saved

Sheet cell functions written in Basic must be saved in the library "Standard", either in the container "OOoMacros", or in "myMacros" or in the document's container.

Cell as an argument

Function Cube( c1 )
    Cube = c1 * c1 * c1
End Function

You can put =Cube(A1) in any cell.

More cells as the arguments

Function SimpleSum( c1, c2 )
    SimpleSum = c1 + c2
End Function

You can put =SimpleSum(A1;A2) in any cell.

Cells range as an argument

Function MySum( oRange As Variant )
If Not IsArray( oRange ) Then
        If IsNumeric( oRange ) Then
                MySum = oRange
        else
                MySum = 0
        End If
        Exit Function
End If
 
Dim nSum As double
 
nSum = 0
 
For i = 1 To ubound( oRange, 1 )
        For j = 1 To ubound( oRange, 2 )
 
                If IsNumeric( oRange( i, j ) ) Then
                        nSum = nSum + oRange( i, j )            
                End If
        Next j
Next i
 
MySum = nSum
End Function

You can put =MySum(A1:A3) in any cell.

It is not possible to write to custom function that uses a cell range that spans sheets. E.g. =Sheet1.A2:Sheet2.A2 instead you would have to have separate parameters for each sheet.