[Solved] How to call calc built in functions?
-
- Posts: 22
- Joined: Tue Feb 03, 2015 6:39 pm
[Solved] How to call calc built in functions?
Hi All
Hi All
I want to perform a number of functions from a basic macro like average and standard deviation and so on.
I see here:
https://wiki.openoffice.org/wiki/Docume ... E_function
That there are a whole bunch of built in functions that do what I want but I have no idea how to call them from a basic macro. I know that if I click on the function (=) icon on a spreadsheet I can call them, but not from a basic macro.
Can someone post an example usage I could crib? Perhaps how to average the range B0:F0 on a sheet?
Regards
Brad
Hi All
I want to perform a number of functions from a basic macro like average and standard deviation and so on.
I see here:
https://wiki.openoffice.org/wiki/Docume ... E_function
That there are a whole bunch of built in functions that do what I want but I have no idea how to call them from a basic macro. I know that if I click on the function (=) icon on a spreadsheet I can call them, but not from a basic macro.
Can someone post an example usage I could crib? Perhaps how to average the range B0:F0 on a sheet?
Regards
Brad
Last edited by steadyonabix on Wed Feb 11, 2015 3:09 pm, edited 1 time in total.
Open Office 4.1.1 on Centos 6.3 DTE
-
- Posts: 22
- Joined: Tue Feb 03, 2015 6:39 pm
Re: How to call calc built in functions?
Have just found this which looks promising:
http://www.oooforum.org/forum/viewtopic.phtml?p=441547
Wil give it a try
http://www.oooforum.org/forum/viewtopic.phtml?p=441547
Wil give it a try
Open Office 4.1.1 on Centos 6.3 DTE
-
- Posts: 22
- Joined: Tue Feb 03, 2015 6:39 pm
Re: How to call calc built in functions?
I have a number of questions about this.
My scratch function: -
1 - What sheet is used when I call the function AVERAGE using sRange set to "B0:F0". (As you can see I have tried using an absolute name. Although the syntax shown here shows a cell address - https://wiki.openoffice.org/wiki/Docume ... E_function)
2 - running the above gives an Object Variable Not Set error where I call the function.
I assume the error is because I am calling the function incorrectly?
Any help much appreciated
Brad
My scratch function: -
Code: Select all
Function fnAverage() AS Long
Dim oFunctionAccess : oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
Dim sRange AS String : sRange = "$BL1DAT1_timing.$B$0:$F$0"
sRange = "B0:F0"
fnAverage = oFunctionAccess.CallFunction( "AVERAGE", sRange)
End Function
2 - running the above gives an Object Variable Not Set error where I call the function.
I assume the error is because I am calling the function incorrectly?
Any help much appreciated
Brad
Open Office 4.1.1 on Centos 6.3 DTE
Re: How to call calc built in functions?
this means the second parameter should be a CellRange, not a string that specifies a cell range.www.openoffice.org/api/docs XFunctionAccess wrote: ::com::sun::star::table::XCellRange
for a SheetCellRange object that contains the data.
So you better try:
Code: Select all
Dim oFunctionAccess As Object
Dim oSheet As Object, oRange As Object
oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
' get the first sheet of the document
oSheet = ThisComponent.getSheets().getByIndex(0)
oRange = oSheet.getCellRangeByPosition(1, 0, 5, 0) ' B1 to F1
'or oRange = oSheet.getCellRangeByName("B1:F1")
fnAverage = oFunctionAccess.CallFunction( "AVERAGE", oRange)
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
-
- Posts: 22
- Joined: Tue Feb 03, 2015 6:39 pm
Re: How to call calc built in functions?
Thanks Rudolfo
I have been misunderstanding how to drill down into the documentation. Your quote for XCellRange pointed me in the right direction.
I will try your suggestion.
Many thanks
Brad
I have been misunderstanding how to drill down into the documentation. Your quote for XCellRange pointed me in the right direction.
I will try your suggestion.
Many thanks
Brad
Open Office 4.1.1 on Centos 6.3 DTE
-
- Posts: 22
- Joined: Tue Feb 03, 2015 6:39 pm
Re: How to call calc built in functions?
Hi
I am having mixed results with this call to FunctionAccess.CallFunction:
Call to AVERAGE
This works:
But this throws illegal argument exception when called this way:
Error Message is - "cannot coerce argument type during coreflection call!."
But according to the docs it should accept the range as an argument.
Also the call to getCellRangeByName should return a XCellRange.
So I don't see what is wrong there.
Perhaps I shopuld read out the range properties into an array of doubles and use that.
Brad
I am having mixed results with this call to FunctionAccess.CallFunction:
Call to AVERAGE
This works:
Code: Select all
Function fnAverage(sSheet AS String, sRange AS String) AS Double
Dim oFunctionAccess : oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
Dim L(3) as long
L(0) = 10
L(2) = 20
L(3) = 30
fnAverage = oFunctionAccess.CallFunction( "AVERAGE", L)
End Function
Code: Select all
n = fnAverage("BL1DAT1_timing", "B1:F1")
Code: Select all
Function fnAverage(sSheet AS String, sRange AS String) AS Double
Dim oFunctionAccess : oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
Dim oDoc, oSheet, oRange AS Object
oDoc = ThisComponent
oSheet = oDoc.sheets.getByName(sSheet)
oRange = oSheet.getCellRangeByName(sRange)
fnAverage = oFunctionAccess.CallFunction( "AVERAGE", oRange)
End Function
But according to the docs it should accept the range as an argument.
Also the call to getCellRangeByName should return a XCellRange.
So I don't see what is wrong there.
Perhaps I shopuld read out the range properties into an array of doubles and use that.
Brad
Open Office 4.1.1 on Centos 6.3 DTE
Re: How to call calc built in functions?
...As rudolfo wrote:
You need get the CELLRANGE based on its NAME.
The Cellrange will be an Object type variable, but the name is a String type variable.
The "B1:F1" string NOT represent a Cellrange, but it represents the NAME of the Cellrange only. The average function can not calculate any result from a string.this means the second parameter should be a CellRange, not a string that specifies a cell range.
You need get the CELLRANGE based on its NAME.
The Cellrange will be an Object type variable, but the name is a String type variable.
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.
-
- Posts: 22
- Joined: Tue Feb 03, 2015 6:39 pm
Re: How to call calc built in functions?
Sorry Zizi, but I am confused.
Forgive me I am new to this.
I thought the call to getCellRangeByName returned the cell range object and that is what I was passing to CallFunction
Have I misunderstood?
Forgive me I am new to this.
I thought the call to getCellRangeByName returned the cell range object and that is what I was passing to CallFunction
Have I misunderstood?
Open Office 4.1.1 on Centos 6.3 DTE
Re: How to call calc built in functions?
viewtopic.php?f=45&t=24323
The argument of the Built in Average function must be an Array type variable. You need "convert" the values of the Cellrange object to an Array variable:
When the passed parameter is an Object :
- usage: just select calculable cellrange after typing the name of the user defined function (and the beginning parenthesis)
The argument of the Built in Average function must be an Array type variable. You need "convert" the values of the Cellrange object to an Array variable:
When the passed parameter is an Object :
Code: Select all
Function fnAverage(oRange AS Object) AS Double
Dim oFunctionAccess : oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
arg = Array(oRange)
fnAverage = oFunctionAccess.CallFunction( "AVERAGE", arg)
End Function
Last edited by Zizi64 on Wed Feb 11, 2015 2:09 pm, edited 2 times in total.
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: How to call calc built in functions?
or when you passed the NAME of the Sheet and the NAME of the Cellrange (as String variable):
Usage:
=fnAverage("$BL1DAT1_timing";"$B$0:$F$0")
Code: Select all
Function fnAverage(sSheet AS String, sRange AS String) AS Double
Dim oFunctionAccess : oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
Dim oDoc, oSheet, oRange AS Object
oDoc = ThisComponent
oSheet = oDoc.sheets.getByName(sSheet)
oRange = oSheet.getCellRangeByName(sRange)
arg = Array(oRange)
fnAverage = oFunctionAccess.CallFunction( "AVERAGE", arg)
End Function
=fnAverage("$BL1DAT1_timing";"$B$0:$F$0")
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.
-
- Posts: 22
- Joined: Tue Feb 03, 2015 6:39 pm
Re: How to call calc built in functions?
Many Thanks Zizi
I thought I was going to have to write the functions myself.
So I guess this was telling me to use an array for a range:
any[][]
for a mixed array, where each element must be of void, long, double or string type.
::com::sun::table::XCellRange
for a SheetCellRange object that contains the data.
Thanks for saving me a lot of work reinventing the wheel.
Brad
I thought I was going to have to write the functions myself.
So I guess this was telling me to use an array for a range:
any[][]
for a mixed array, where each element must be of void, long, double or string type.
::com::sun::table::XCellRange
for a SheetCellRange object that contains the data.
Thanks for saving me a lot of work reinventing the wheel.
Brad
Open Office 4.1.1 on Centos 6.3 DTE
-
- Posts: 22
- Joined: Tue Feb 03, 2015 6:39 pm
Re: How to call calc built in functions?
PS
How do I mark this as solved?
How do I mark this as solved?
Open Office 4.1.1 on Centos 6.3 DTE
Re: How to call calc built in functions?
Edit the title of the first post in this topic:
[Solved] How to call calc built in 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: [solved] How to call calc built in functions?
I have to admit, I did not test my code from my last post. It just seemed to be what is expected by the API. Obviously it is not.
For the records I gave it another go with the .getDataArray() method of the XCellRange interface. At least the name of the method seems to promise that an array is returned. But it is not the kind of array that is expected by the CallFunction API.
This seemed strange to me, so I started the Basic IDE debugger and inspected the arrays. This is what the "Watch" window shows for oDataArray and args = Array(oDataArray):
Both array look very similar, but the second one has one nesting level more. And actually the inner content can be quite different as Zizi's approach with wrapping the XCellRange directly into an array shows. The following is a screenshot of the watch window for the case with args = Array(oCellRange):
For the records I gave it another go with the .getDataArray() method of the XCellRange interface. At least the name of the method seems to promise that an array is returned. But it is not the kind of array that is expected by the CallFunction API.
Code: Select all
oCellRange = oSheet.getCellRangeByPosition(0,0, 0,3)
' the following fails (as we all know)
'dblValue = oFunctionAccess.CallFunction( "AVERAGE", oCellRange)
oDataArray = oCellRange.getDataArray()
' The next line will also fail
dblValue = oFunctionAccess.CallFunction( "AVERAGE", oDataArray)
args = Array(oDataArray)
' now it works !!
dblValue = oFunctionAccess.CallFunction( "AVERAGE", args)
Code: Select all
|Variable | Value | Type
|--------------------------+---------+-----------------
|- oDataArray : : Object(0 to 3)
| - oDataArray(0) : : Variant(0 to 0)
| oDataArray(0)(0) : 10 : Variant/Double
| + oDataArray(1) : : Variant(0 to 0)
| + oDataArray(2) : : Variant(0 to 0)
| + oDataArray(3) : : Variant(0 to 0)
|--------------------------+---------+-----------------
|- args : : Variant(0 to 0)
| - args(0) : : Object(0 to 3)
| - args(0)(0) : : Variant(0 to 0)
| args(0)(0)(0) : 10 : Variant/Double
| + args(0)(1) : : Variant(0 to 0)
| + args(0)(2) : : Variant(0 to 0)
| + args(0)(3) : : Variant(0 to 0)
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
-
- Posts: 22
- Joined: Tue Feb 03, 2015 6:39 pm
Re: [solved] How to call calc built in functions?
That's interesting.
Always a good feeling to understand why.
On a general note to all who helped me with my project, thanks very much. It all works perfectly now.
Always a good feeling to understand why.
On a general note to all who helped me with my project, thanks very much. It all works perfectly now.
Open Office 4.1.1 on Centos 6.3 DTE
Re: [Solved] How to call calc built in functions?
Thanks to you I have managed to figure this out for my own needs (max, min of column values to establish good y axis max and min on automated charts).
Grateful Thanks
David
Grateful Thanks
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic