[Tip] Using spreadsheet functions in Basic

Home made tutorials, by users, for users
Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

[Tip] Using spreadsheet functions in Basic

Postby WolfVanZandt » 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:
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.
OpenOffice Ver. 3.2 on Windows XP
WolfVanZandt
 
Posts: 11
Joined: Mon Feb 15, 2010 4:53 pm
Location: Alabama

Re: [Tip] Using spreadsheet functions in Basic

Postby Villeroy » Fri Sep 12, 2014 1:37 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24590
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tutorials

Who is online

Users browsing this forum: No registered users and 1 guest