Using Calc built in functions in macros
-
- Posts: 5
- Joined: Fri Apr 08, 2011 11:36 pm
Using Calc built in functions in macros
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
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
Re: Using Calc built in functions in macros
Code: Select all
service.callFunction("OFFSET",Array(oRg.getDataArray(),1,2,3,4))
This one includes a Basic function getOffsetRange(obj,a,b,c,d): http://user.services.openoffice.org/en/ ... ll#p177590
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Using Calc built in functions in macros
Hi,
The square root function exist in the OOBasic too: it named SQR().
Villeroy proposed a more comprehensive solution, therefore it is just a comment:the COS function works fine in a macro, but SQRT doesn't
The square root function exist in the OOBasic too: it named SQR().
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: Using Calc built in functions in macros
Well, there is something wrong with the resulting arrays of the OFFSET function in Basic.
Scalar functions SQRT and COS work as expected.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 5
- Joined: Fri Apr 08, 2011 11:36 pm
Re: Using Calc built in functions in macros
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,
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
Re: Using Calc built in functions in macros
x=srv.callFunction("SQRT",Array(2))
print x
REM => 1.414213562
You can instanciate the same FunctionAccess and use callFunction in many different languages.
print x
REM => 1.414213562
The primitive Basic language with its minimalistic set of functions has nothing to do with the office suite and its spreadsheet functions.That makes me think that there must be two different places that these functions are being called from.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 5
- Joined: Fri Apr 08, 2011 11:36 pm
Re: Using Calc built in functions in macros
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
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
Re: Using Calc built in functions in macros
see:but I haven't found a listing of OOBasic functions; do you know where such a listing can be found
http://wiki.services.openoffice.org/wik ... me_Library
or OOo Basic Help (Run-time functions - Numeric functions):
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: Using Calc built in functions in macros
Nobody needs macros for this trivial task. The 2 modes of [V/H]LOOKUP and MATCH should do the job.I want to be able to interpolate data from this 2nd page into the model on the 1st page.
http://user.services.openoffice.org/en/ ... hp?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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 5
- Joined: Fri Apr 08, 2011 11:36 pm
Re: Using Calc built in functions in macros
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.
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
-
- Posts: 5
- Joined: Fri Apr 08, 2011 11:36 pm
Re: Using Calc built in functions in macros
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.
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
Re: Using Calc built in functions in macros
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.
Try
OOoCalc Help, Contents Tab, Macros and Programming, Command Reference, and then make your choice of offered options.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Using Calc built in functions in macros
+1Perhaps 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.
...or: Are you using a localized version? Then you need search the translated expression (in your language).
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.