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:
- Code: Select all Expand viewCollapse view
fc=createUnoService
("com.sun.star.sheet.FunctionAccess")
InRange.getCellByPosition(I-1,J-
1).Value=fc.callFunction("Median",Array
(TransSheet.getCellRangeByName(RngAdd)))
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:
- Code: Select all Expand viewCollapse view
Slp=fc.callFunction("Slope",Array(TransSheet.getCellRangeByName(RngAdd),TransSheet.getCellRangeByName(RngAdd1)))
Notice that two range names were included in the Array separated by commas.