[Tip] Using spreadsheet functions in Basic

Home made tutorials, by users, for users
Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
WolfVanZandt
Posts: 11
Joined: Mon Feb 15, 2010 4:53 pm
Location: Alabama

[Tip] Using spreadsheet functions in Basic

Post by WolfVanZandt »

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:

Code: Select all

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

Slp=fc.callFunction("Slope",Array(TransSheet.getCellRangeByName(RngAdd),TransSheet.getCellRangeByName(RngAdd1)))
Notice that two range names were included in the Array separated by commas.
Last edited by MrProgrammer on Wed Aug 30, 2023 4:58 pm, edited 1 time in total.
Reason: Removed double-spacing in the post
OpenOffice Ver. 3.2 on Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tip] Using spreadsheet functions in Basic

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply