Libre Office Calc - How to write calc-functions inside a cell using Basic

[XanderN]

I’m trying to add calc-functions inside cells so I can then update other sheet parts and have those cells update automatically without macro intervention, yet is getting a bit tedious to achieve that.

What I tried is to get the cell I want to put the function string and then assigning the value using CellVariable.Value and CellVariable.Function, but then LibreOffice Converts the text and the function doesn’t work, displaying in the final cell “suma(a3:a5)” instead of the result of that function:

sub FirstAttemptCode

Dim Document As Object
Dim Sheet As Object
Dim Cell As Object

Document = ThisComponent
Sheet =  Document.Sheets(0)

Cell = Sheet.getCellByPosition(0,0)
Cell.Formula = "=SUMA(A3;A5)"

end sub

Doing it with macro recorder makes use of CurrentController.Frame and uno “frame.dispatchHelper”, which works, but the process is more like automatized user input than code executing, which make the process tediously slow.

Also tried to use myDoc.lockControllers(), myDoc.addActionLock() and myDoc.removeActionLock(), myDoc.unlockControllers() at the end as suggested Here but nothing went faster and the sheet got “bugged”, so I would humbly ask for a better way of overcoming this, as the more I search more lost I feel.

Here’s the macro recorder-like code in case it helps:

sub MacroRecorderCode
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue

args1(0).Name = "StringName"
args1(0).Value = "=SUMA(B2;C1)"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args1())


end sub

Edit: Using LibreOffice 5.3.6.1, with Spanish Language.

Edit2: Seems that trying to use “localized” version of calc functions triggers the error, writting “=SUM(B2;C1)” translates to “=SUMA(B2;C1)” in the spreadsheet cell.

Edit3: Changed String in both Subs to the one I was actually using and improved question looks a bit.

[mikekaganski]

Your first function works for me with 6.1.1.2; what is your version?

[XanderN]

My bad I didn’t put my version, it is 5.3.6.1.

It is also a localized one, in Spanish, so it is wrote “=SUMA(A3;A5)”, could be that?

[mikekaganski]

I have just tested with 5.3.7.2, and it works, too. I have tested on a Russian UI, where localized function name is “СУММ”. But of course, I have not changed the BASIC code - it still reads Cell.Formula = "=SUM(A3;A5)", because Formula takes non-localized names to be portable.

[XanderN]

Yeah indeed there should be a warning in .Formula method about not using localized versions, that was what was giving me a headache, as in my code I had Cell.Formula = "=SUMA(A3;A5)" instead of Cell.Formula = "=SUM(A3;A5)" , thanks a lot for the help!