### [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.

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.