Using Calc built in functions in macros

Creating a macro - Writing a Script - Using the API

Using Calc built in functions in macros

Postby gravidyne1 » Fri Apr 08, 2011 11:52 pm

I have been using OO for 3 years and I love it, but finally my inabilty to figure out how to write user-defined functions in calc is about to send me back to Microsoft :( .

I have searched everywhere for examples but I can't find any that work. I need to write a function to interpolate thermodynamic data from a chart. All the data is entered into calc, but in order to read it I need to be able to write a function that uses the 'offset' function from the list of regular calc functions. I know that offset works, because it works when I just use it in a formula, but it won't work when I try to use it in a macro function. Strangly, the COS function works fine in a macro, but SQRT doesn't and neither does OFFSET.

I found some example macro functions that used these commands, but they seemed to have no effect on the operation of the macro function that I am trying to write:

dim service as object, sheet as object, cell as object
service = createUnoService( "com.sun.star.sheet.FunctionAccess" )

Please help
OpenOffice 3.2 on Windows Vista
gravidyne1
 
Posts: 5
Joined: Fri Apr 08, 2011 11:36 pm

Re: Using Calc built in functions in macros

Postby Villeroy » Sat Apr 09, 2011 12:25 am

Code: Select all   Expand viewCollapse view
service.callFunction("OFFSET",Array(oRg.getDataArray(),1,2,3,4))
works pretty well. It returns a 3x4 array with one row and 2 cols offset to the original range.

This one includes a Basic function getOffsetRange(obj,a,b,c,d): viewtopic.php?f=20&t=38672&p=177590&hilit=getActiveCell#p177590
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26871
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using Calc built in functions in macros

Postby Zizi64 » Sat Apr 09, 2011 7:03 am

Hi,

the COS function works fine in a macro, but SQRT doesn't

Villeroy proposed a more comprehensive solution, therefore it is just a comment:
The square root function exist in the OOBasic too: it named SQR().
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8085
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using Calc built in functions in macros

Postby Villeroy » Sat Apr 09, 2011 11:14 am

Well, there is something wrong with the resulting arrays of the OFFSET function in Basic.
Scalar functions SQRT and COS work as expected.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26871
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using Calc built in functions in macros

Postby gravidyne1 » Mon Apr 11, 2011 12:29 am

Hi Zizi64,

I am trying to work through Villeroy's code, but I did try SQR() in the macro function and that works. But it doesn't work as a calc function only SQRT() works there. That makes me think that there must be two different places that these functions are being called from. I can see the list of calc functions, but I haven't found a listing of OOBasic functions; do you know where such a listing can be found?

Thanks,
OpenOffice 3.2 on Windows Vista
gravidyne1
 
Posts: 5
Joined: Fri Apr 08, 2011 11:36 pm

Re: Using Calc built in functions in macros

Postby Villeroy » Mon Apr 11, 2011 12:43 am

x=srv.callFunction("SQRT",Array(2))
print x
REM => 1.414213562

That makes me think that there must be two different places that these functions are being called from.

The primitive Basic language with its minimalistic set of functions has nothing to do with the office suite and its spreadsheet functions.
You can instanciate the same FunctionAccess and use callFunction in many different languages.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26871
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using Calc built in functions in macros

Postby gravidyne1 » Mon Apr 11, 2011 12:59 am

Hi Villeroy,

After reading your code, I think perhaps that I am trying to mis-use the Offset function. I really don't think that the function that I am trying to write should be very complicated, but I can't seem to make it work. All the examples that I can find seem to be for much more complicated things that what I am trying to do.

Let me explain exactly what I am trying to do:

I am constructing a model of a thermodynamic process on the first page of my spreadsheet. On the second page I have listed the thermodynamic properties of saturated ammonia (the 1st col is temperature, the 2nd is pressure at that temperature, etc for the other properties). I want to be able to interpolate data from this 2nd page into the model on the 1st page. For instance, the model may know the temperature of saturated ammonia at a given time, from that information it will need to find the pressure from the 2nd page.

So this would be a simple function with 3 inputs (the known temperature which is a value in a cell on the 1st page, the column number that contains the temperature data on page 2, and the column number that contains the pressure data on page 2). The value of the function will then be the resulting pressure value.

this should be a very simple algarithm:
1. get the temperature value of the first cell of the temperature column.
2. compare it to the temperature value from the 1st page. If it is less discard it and get the value of the next cell in the column. Repeat until the value is greater than the value from page 1.
3. once the correct value is found, use the data from this row to interpolate the correct pressure and set the function equal to this value.

What I can't figure out how to do is to sequentially read the values from the temperature column into the function. The rest should work fine with an IF statement.

Thanks
OpenOffice 3.2 on Windows Vista
gravidyne1
 
Posts: 5
Joined: Fri Apr 08, 2011 11:36 pm

Re: Using Calc built in functions in macros

Postby Zizi64 » Mon Apr 11, 2011 8:02 am

but I haven't found a listing of OOBasic functions; do you know where such a listing can be found

see:
http://wiki.services.openoffice.org/wik ... me_Library
or OOo Basic Help (Run-time functions - Numeric functions):
Runtime_functions2.png
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8085
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using Calc built in functions in macros

Postby Villeroy » Mon Apr 11, 2011 9:05 am

I want to be able to interpolate data from this 2nd page into the model on the 1st page.

Nobody needs macros for this trivial task. The 2 modes of [V/H]LOOKUP and MATCH should do the job.
download/file.php?id=3562
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26871
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using Calc built in functions in macros

Postby gravidyne1 » Thu Apr 14, 2011 5:23 am

Hi Villeroy,

You are right about VLOOKUP and MATCH, together they can do 2/3 of what I need to do. It seems like it would be cleaner if all of these functions calls wrapped into one new user-defined function but since that doesn't seem possible I guess that I can assemble the required interum values into several columns and then just hide them. I can do roughly the following:

1) Use MATCH to find the row that contains the lower interpolating data and put that row number into a cell.
2) Once I know that row number, I know the page, row and column numbers for all of the values that I need. I should be able to collect all of those values and put them into the next several cells (4 for simple linear interpolation), but I can't find any function that returns the value in a cell if I specify the page, row and column numbers. I should think that would be a fairly common function, but I can't find it!
3) Use the values stored in the previous several cells to calculate the actual value of the interpolation. (very simple; even I can do that if I can figure out how to get the appropriate data stored in the correct cells in step 2)

Alternately I could use VLOOKUP twice to give me the two lower bound numbers for a linear interpolation, but even though it is then obvious that the two upper bound numbers are right below them, I can't see how to use VLOOKUP to get them.
OpenOffice 3.2 on Windows Vista
gravidyne1
 
Posts: 5
Joined: Fri Apr 08, 2011 11:36 pm

Re: Using Calc built in functions in macros

Postby gravidyne1 » Thu Apr 14, 2011 5:32 am

Hi Zizi64,

When I type Run-time functions into OOhelp, I don't get anything like what you showed. Very strange!

I found a few functions by searching for them explicitely.
OpenOffice 3.2 on Windows Vista
gravidyne1
 
Posts: 5
Joined: Fri Apr 08, 2011 11:36 pm

Re: Using Calc built in functions in macros

Postby RoryOF » Thu Apr 14, 2011 5:56 am

Perhaps because you are using OOoCalc help and Zizi64 is using OOoBasic help.
Try
OOoCalc Help, Contents Tab, Macros and Programming, Command Reference, and then make your choice of offered options.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29093
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Using Calc built in functions in macros

Postby Zizi64 » Thu Apr 14, 2011 7:38 am

Perhaps because you are using OOoCalc help and Zizi64 is using OOoBasic help.
Try
OOoCalc Help, Contents Tab, Macros and Programming, Command Reference, and then make your choice of offered options.


+1

...or: Are you using a localized version? Then you need search the translated expression (in your language).
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8085
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests