[Solved] setArrayFormula() yet again!

The Application Programming Interface and the OASIS Open Document Format

[Solved] setArrayFormula() yet again!

Postby dlochrin » Mon May 18, 2015 9:47 am

I have a Basic macro which has to set an array formula. I've read posts in various places regarding the bug in the syntax for the string argument defining a formula in setArrayFormula(), but I don't think that's the problem here. This code using the built-in function SQRT() works fine:
Code: Select all   Expand viewCollapse view
sFormula="SQRT(Tasks)"
oCellRange=oActSheet.getCellRangeByName("NewStates")
oCellRange.setArrayFormula(sFormula)


But changing the formula to use an add-in REFORMAT_TIME() which I've written does not. The add-in is well debugged and works fine when used interactively, and it's installed to allow access by all users. It's also visible in the extension manager's "All" drop-down menu as well as the Add-In menu. The problem seems to be that the add-in isn't found, and in fact no add-in function is found, not even the bundled ones. For example:
Code: Select all   Expand viewCollapse view
sFormula="COMPLEX(Tasks,7)"
oCellRange=oActSheet.getCellRangeByName("NewStates")
oCellRange.setArrayFormula(sFormula)

sets the array formula to {=complex(Tasks, 7)} (note, function name in lower case) and returns an Err:508 diagnostic ("Error: Pair missing" / "Missing bracket, for example, closing brackets, but no opening brackets").

Please, does anyone have any suggestions?

David L.
:crazy:
Last edited by dlochrin on Wed May 20, 2015 1:44 am, edited 1 time in total.
OpenOffice 2.0.4 on SuSE 10.1, now LibreOffice 4.3.5.2 on SuSE 11.4
dlochrin
 
Posts: 14
Joined: Mon Jun 14, 2010 3:03 am
Location: NSW, Australia

Re: setArrayFormula() yet again!

Postby Villeroy » Mon May 18, 2015 10:17 am

Complex is an add-in formula (not part of the ODF specification). Enter a valid COMPLEX formula into some cell and run this to get the right function name:
Code: Select all   Expand viewCollapse view
inputbox "","", ThisComponent.CurrentSelection.getFormula()
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26559
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: setArrayFormula() yet again!

Postby dlochrin » Tue May 19, 2015 3:57 am

Many thanks, Villeroy, that fixed it! :bravo:

I'd suspected that the correct syntax might include what the API documentation refers to as the "programmatic name" but was using "javaPackageName.methodName()" instead of "javaPackageName.addinName.methodName()".

However it also appears that semi-colons must be used to separate arguments instead of commas. OpenOffice specified semi-colons at one stage, but LIbreOffice now allows commas when entering formulae interactively, and of course commas are used in Java code. Another trap for the unwary... And that raises a bit of a question regarding the macro / API documentation.

Do you have time to describe, very briefly, the framework within which the macro processor is implemented? The method setArrayFormula() is defined in the UNO interface "com.sun.star.sheet.XArrayFormulaRange" and could be called directly from a Java or other program, so there's obviously a very close relationship between the macro processor and the interfaces & methods documented in https://www.openoffice.org/api/docs/common/ref/index-files/index-19.html

Clearly, the macro processor is not just a stand-alone interpreter.

David L.
OpenOffice 2.0.4 on SuSE 10.1, now LibreOffice 4.3.5.2 on SuSE 11.4
dlochrin
 
Posts: 14
Joined: Mon Jun 14, 2010 3:03 am
Location: NSW, Australia

Re: setArrayFormula() yet again!

Postby Villeroy » Tue May 19, 2015 12:19 pm

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


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 1 guest