[Solved] setArrayFormula() yet again!

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
dlochrin
Posts: 14
Joined: Mon Jun 14, 2010 3:03 am
Location: NSW, Australia

[Solved] setArrayFormula() yet again!

Post by dlochrin »

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

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

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: setArrayFormula() yet again!

Post by Villeroy »

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

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dlochrin
Posts: 14
Joined: Mon Jun 14, 2010 3:03 am
Location: NSW, Australia

Re: setArrayFormula() yet again!

Post by dlochrin »

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/com ... ex-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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: setArrayFormula() yet again!

Post by Villeroy »

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
Post Reply