Page 1 of 1

[Solved] setArrayFormula() yet again!

PostPosted: Mon May 18, 2015 9:47 am
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   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:

Re: setArrayFormula() yet again!

PostPosted: Mon May 18, 2015 10:17 am
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   Expand viewCollapse view
inputbox "","", ThisComponent.CurrentSelection.getFormula()

Re: setArrayFormula() yet again!

PostPosted: Tue May 19, 2015 3:57 am
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/common/ref/index-files/index-19.html

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

David L.

Re: setArrayFormula() yet again!

PostPosted: Tue May 19, 2015 12:19 pm
by Villeroy