Custom functions
Contents
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. To start writing code, from the menu select Tools -> Macros -> Organize Macros and choose a scripting language: OpenOffice.org Basic, Python, or BeanShell. Find your file in the dialog box, select it, and then press the New button. This creates a new module where you can type in your code. After you create the module, an editor window will open and you can start writing your code. Some examples in OpenOffice.org Basic follow.
Cell as an argument
[oobas]
Function Cube( c1 )
Cube = c1 * c1 * c1
End Function You can put =Cube(A1) in any cell.
More cells as the arguments
[oobas]
Function SimpleSum( c1, c2 )
SimpleSum = c1 + c2
End Function You can put =SimpleSum(A1;A2) in any cell.
Cells range as an argument
[oobas]
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.