libreoffice-basic

LO Basic: ThisComponent.CurrentController does not exist when the spreadsheet is started

After the spreadsheet is fully started, then it works, but not on startup, resulting in some cells not calculated.
I'm talking about a User Defined Function (UDF):

Function Eval(Formula As Variant) As Variant
    Dim oDoc As Object
    Dim oCurrentSheet As Object
    Dim oWorkCell As Object
    MyMacro
    oDoc = ThisComponent
    oCurrentSheet = oDoc.CurrentController.ActiveSheet
    oWorkCell = oCurrentSheet.getCellByPosition(0,0)
    oWorkCell.setFormula(Formula)
    If oWorkCell.getError = 0 Then
        Eval = oWorkCell.getDataArray()(0,0)
    Else
        Eval = oWorkCell.getString()
    EndIf
End Function

To get around this problem, I created a macro that repeats the erroring instruction until it works:

Sub MyMacro
    On Error GoTo ErrorHandler
    ThisComponent.CurrentController
    GoTo Done
    ErrorHandler:
    Wait 100
    Resume 0
    Done:
    On Error GoTo 0
End Sub

Unfortunately, the macro runs forever because LibreOffice doesn't want to load the spreadsheet until the macro finishes, and the macro needs the spreadsheet to be loaded in order to finish.
I'm forced to kill the process because of this.

enter image description here

How can I fix this?

Solution

  • Don't lock up the thread — let the function fail quietly. On View Created event, call ThisComponent.calculateAll to run the function again, successfully this time.

    https://stackoverflow.com/a/39254907/5100564