In LibreOffice Calc, how do I change through LibreOffice Basic the value of a cell with an event listener set to it without crashing the program?

Question

I am trying to create two tables which mirror changes made to any of them to one another automatically.

To that end, I added event listeners which are triggered when the cells of these tables are edited by the user.

Unfortunately, editing one of the tables causes LibreOffice to crash, even though the changes are indeed reflected correctly, as seen upon reopening the file.
I thought the crash might be due to a never-ending circular reference, but it still crashes after it has been made non-circular (by commenting out the relevant parts of the code so that changes are reflected only one way rather than both ways).
I noticed the code worked fine when writing to a cell that didn't have an event listener set to it.

How can I write to one of the cells with event listeners set to them without causing LibreOffice to crash?

You may want to download the following file. Please run Main and then try editing the cell C3 of the Planning sheet. The arbitrary string "C" should be written in the cell C4 of the Services sheet.

Here is a simplified version of the code :

REM  *****  BASIC  *****

const SERVICESSHEET_NUMBER = 2
const SERVICESSHEET_SERVICES_COLUMN = 2

Type cellStruct
    columnNumber As Integer
    rowNumber As Integer
End Type



Sub UpdateServicesSheet(editedCell As cellStruct, newValue As String)
    
    Dim oSheets
    Dim servicesSheet
        
    oSheets = ThisComponent.getSheets()
    servicesSheet = oSheets.getByIndex(SERVICESSHEET_NUMBER)

    servicesSheet.getCellByPosition(SERVICESSHEET_SERVICES_COLUMN, 3).setString(newValue)
    
End Sub


Private oListener, cellRange as Object

Sub AddListener
    Dim sheet, cell as Object

    sheet = ThisComponent.Sheets.getByIndex(0)  'get leftmost sheet
    servicesSheet = ThisComponent.Sheets.getByIndex(2)

    cellRange = sheet.getCellrangeByName("C3")
    oListener = createUnoListener("Modify_","com.sun.star.util.XModifyListener")  'create a listener
    cellRange.addModifyListener(oListener)  'register the listener


    cellRange = servicesSheet.getCellrangeByName("C4")
    oListener = createUnoListener("Modify_","com.sun.star.util.XModifyListener")  'create a listener
    cellRange.addModifyListener(oListener)  'register the listener
End Sub

global CircularReferenceAllowed As boolean

Sub Modify_modified(oEv)

    Dim editedCell As cellStruct
    Dim newValue As String
    
    editedCell.columnNumber = 2
    editedCell.rowNumber = 2
    

    If CircularReferenceAllowed Then
        CircularReferenceAllowed = false
        UpdateServicesSheet(editedCell, "C")
    End If

End Sub

Sub Modify_disposing(oEv)
End Sub

Sub RmvListener
    cellRange.removeModifyListener(oListener)
End Sub


Sub Main

    CircularReferenceAllowed = true
    AddListener

End Sub

Crossposted to :

• OpenOffice forums
• LibreOffice discourse platform

Answer 1

It seems like the event trigger is within another event's function is causing the crash. In any case, the solution is to remove the listener, then add it back after modifying the other cell. You do need to global the Listener and the Cell objects to make this work.

This code is simplified to work on C3 and C15 on the first sheet. It would also output some information on C14, which isn't really necessary for your purpose, but I use it to see what's happening. You need to adopt the according to what you need.

global goListener as Object
global goListener2 as Object
global goCellR as Object
global goCellR2 as Object
global goSheet as Object

global giRun as integer
global giUpd as Integer

Sub Modify_modified(oEv)
    Dim sCurStr$
    Dim sNewStr As String
    'xRay oEv
    
    giRun = giRun + 1
    sCurStr = oEv.source.string

    oCell = goSheet.getCellByPosition(2, 14)
    If (oCell.getString() <> sCurStr) Then
        ' only update if it's different.
        giUpd = giUpd + 1
        goCellR2.removeModifyListener(goListener2)
        oCell.setString(sCurStr)
        goCellR2.addModifyListener(goListener2)
    End If
    sNewStr =sCurStr & " M1 Run=" & giRun & " Upd=" & giUpd
    goSheet.getCellByPosition(2, 13).setString(sNewStr)    
End Sub

Sub Modify2_modified(oEv)
    Dim sCurStr$
    Dim sNewStr As String
    Dim oCell as Object
    'xRay oEv
    
    giRun = giRun + 1
    sCurStr = oEv.source.string
    oCell = goSheet.getCellByPosition(2, 2)
    If (oCell.getString() <> sCurStr) Then
        ' only update if it's different.
        giUpd = giUpd + 1
        goCellR.removeModifyListener(goListener)
        oCell.setString(sCurStr)
        goCellR.addModifyListener(goListener)
    End If
    sNewStr =sCurStr & " M2 Run=" & giRun & " Upd=" & giUpd
    goSheet.getCellByPosition(2, 13).setString(sNewStr)
End Sub

Sub Modify_disposing(oEv)
    MsgBox "In Modify_disposing"
End Sub

Sub Modify2_disposing(oEv)
    MsgBox "In Modify2_disposing"
End Sub

Sub RmvListener
    MsgBox "In RmvListener"
    goCellR.removeModifyListener(goListener)
    goCellR2.removeModifyListener(goListener2)
End Sub


Sub AddListener

    goSheet = ThisComponent.Sheets.getByIndex(0)  'get leftmost goSheet
    'servicesSheet = ThisComponent.Sheets.getByIndex(2)

    goCellR = goSheet.getCellrangeByName("C3")
    goListener = createUnoListener("Modify_","com.sun.star.util.XModifyListener")  'create a listener
    goCellR.addModifyListener(goListener)  'register the listener

    goCellR2 = goSheet.getCellrangeByName("C15")
    goListener2 = createUnoListener("Modify2_","com.sun.star.util.XModifyListener")  'create a listener
    goCellR2.addModifyListener(goListener2)  'register the listener
End Sub

Sub Main
    giRun = 0
    giUpd = 0
    AddListener

End Sub