Difference between revisions of "Custom functions"
(→Simple functions: added info about where to save the functions) |
|||
(8 intermediate revisions by 4 users not shown) | |||
Line 1: | Line 1: | ||
=Simple functions= | =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== | ==Cell as an argument== | ||
− | < | + | <source lang="oobas"> |
Function Cube( c1 ) | Function Cube( c1 ) | ||
Cube = c1 * c1 * c1 | Cube = c1 * c1 * c1 | ||
End Function | End Function | ||
− | </ | + | </source> |
You can put ''=Cube(A1)'' in any cell. | You can put ''=Cube(A1)'' in any cell. | ||
==More cells as the arguments== | ==More cells as the arguments== | ||
− | < | + | <source lang="oobas"> |
Function SimpleSum( c1, c2 ) | Function SimpleSum( c1, c2 ) | ||
SimpleSum = c1 + c2 | SimpleSum = c1 + c2 | ||
End Function | End Function | ||
− | </ | + | </source> |
You can put ''=SimpleSum(A1;A2)'' in any cell. | You can put ''=SimpleSum(A1;A2)'' in any cell. | ||
==Cells range as an argument== | ==Cells range as an argument== | ||
− | < | + | <source lang="oobas"> |
Function MySum( oRange As Variant ) | Function MySum( oRange As Variant ) | ||
If Not IsArray( oRange ) Then | If Not IsArray( oRange ) Then | ||
Line 44: | Line 49: | ||
MySum = nSum | MySum = nSum | ||
End Function | End Function | ||
− | </ | + | </source> |
You can put ''=MySum(A1:A3)'' in any cell. | 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. <tt>=Sheet1.A2:Sheet2.A2</tt> instead you would have to have separate parameters for each sheet. | It is not possible to write to custom function that uses a cell range that spans sheets. E.g. <tt>=Sheet1.A2:Sheet2.A2</tt> instead you would have to have separate parameters for each sheet. | ||
+ | |||
+ | [[Category:Basic:Tutorials]] | ||
+ | [[Category:Calc]] |
Revision as of 11:07, 10 May 2010
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. 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.