[Tip] Using spreadsheet functions in Basic
Posted: Fri Sep 12, 2014 3:15 am
On using spreadsheet functions in OpenOffice Basic:
There has always been a doctrine in programming that you don't invent the wheel, so if there are already routines around that do what you want a subroutine to do, it's natural to use the pre-existing routine. It's ridiculous to strain your brain trying to figure out a Shell sort when you need to find a median when there is a perfectly functioning median spreadsheet function in Calc. But the programming to access the spreadsheet functions from a Basic program is a little tricky so, here is the basics. OpenOffice Basic uses the UNO service FunctionAccess to pull up a spreadsheet function. Here is some code I used for a data imputation function:
fc has been previously dimensioned as an object. The first expression calls the UNO service; the second expression uses the spreadsheet function. The callFunction function has two parts, the name of the spreadsheet function being called and the arguments being transferred to the spreadsheet function. The name is easy - just enclose it in quotes and spell it right.
Arguments are transferred to the spreadsheet function by an array using an array function. The Array function can take numbers, text, variables, or, as above, range or cell addresses. Above, RngAdd is a string naming a range (by the form B1:D15). Array elements are separated by commas.
I had a problem when I wanted to use a spreadsheet function that required two range addresses for arguments but I played around with it until I figured out that only one Array function was required. The results was:
Notice that two range names were included in the Array separated by commas.
There has always been a doctrine in programming that you don't invent the wheel, so if there are already routines around that do what you want a subroutine to do, it's natural to use the pre-existing routine. It's ridiculous to strain your brain trying to figure out a Shell sort when you need to find a median when there is a perfectly functioning median spreadsheet function in Calc. But the programming to access the spreadsheet functions from a Basic program is a little tricky so, here is the basics. OpenOffice Basic uses the UNO service FunctionAccess to pull up a spreadsheet function. Here is some code I used for a data imputation function:
Code: Select all
fc=createUnoService("com.sun.star.sheet.FunctionAccess")
InRange.getCellByPosition(I-1,J-1).Value = fc.callFunction("Median",Array(TransSheet.getCellRangeByName(RngAdd)))Arguments are transferred to the spreadsheet function by an array using an array function. The Array function can take numbers, text, variables, or, as above, range or cell addresses. Above, RngAdd is a string naming a range (by the form B1:D15). Array elements are separated by commas.
I had a problem when I wanted to use a spreadsheet function that required two range addresses for arguments but I played around with it until I figured out that only one Array function was required. The results was:
Code: Select all
Slp=fc.callFunction("Slope",Array(TransSheet.getCellRangeByName(RngAdd),TransSheet.getCellRangeByName(RngAdd1)))