Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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

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 "sum(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 = "=SUM(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 = "=SUM(B2;C1)"

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


end sub

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

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 "sum(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 = "=SUM(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 = "=SUM(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.

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

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 "sum(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 = "=SUM(A3;A5)"
"=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 = "=SUM(B2;C1)"
"=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

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

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 "sum(a3:a5)" "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() 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: Edit: Using LibreOffice 5.3.6.1, with Spanish Language.

Edit2: 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: Edit3: Changed String in both Subs to the one I was actually usingusing and improved question looks a bit.