Using Calc built in functions in macros

[gravidyne1]

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

[Villeroy]

Code: Select all

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): http://user.services.openoffice.org/en/ ... ll#p177590

[Zizi64]

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().

[Villeroy]

Well, there is something wrong with the resulting arrays of the OFFSET function in Basic.
Scalar functions SQRT and COS work as expected.

[gravidyne1]

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,

[Villeroy]

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.

[gravidyne1]

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

[Zizi64]

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):

[Villeroy]

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.
http://user.services.openoffice.org/en/ ... hp?id=3562

[gravidyne1]

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.

[gravidyne1]

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.

[RoryOF]

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.

[Zizi64]

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).