How to pass arguments to an event listener in LibreOffice Basic?

491 Views Asked by At

I am writing a macro to automate tasks on a spreadsheet in LibreOffice Calc.
One of those tasks is simply to add the numbers contained in a given cell range and write the total in the appropriate cell when one of these cells is edited. (The cells actually contain text: the names of different services. The program then fetches the number of hours associated with each service's name to add them all up.)

Editing such a cell triggers the Modify_modified(oEv) event listener.
The listener then calls the subroutine UpdateTotalHoursOfAgent(calendarSize, allServices, agentTopleftCell) which performs the task described above.

The problem is that arguments calendarSize and allServices, which are defined in other places in the code, are out of scope in the event listener.

I do not know how to pass those arguments to the listener.
I tried using global variables instead even though it is frowned upon, but I suspect that they reach the end of their lifetime when the main program's execution is complete, and are not available anymore when a cell is edited afterwards.

How can I pass arguments calendarSize and allServices to the UpdateTotalHoursOfAgent subroutine when Modify_modified(oEv) is triggered?

Here's part of the code used to create the event listener (found on a forum):


Private oListener, cellRange as Object

Sub AddListener
    Dim sheet, cell as Object

    sheet = ThisComponent.Sheets.getByIndex(0)  'get leftmost sheet
    cellRange = sheet.getCellrangeByName("E4:J5")

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

Sub Modify_modified(oEv)

' *Compute agentTopleftCell*

REM How to obtain calendarSize and allServices from here?
    UpdateTotalHoursOfAgent(calendarSize, allServices, agentTopleftCell)
End Sub

Sub Main
' *...code...*
    Dim allServices As allServicesStruct
    Dim calendarSize As calendarStruct

    AddListener

' *...code...*
End Sub
1

There are 1 best solutions below

0
On

I tried using global variables...

Probably you did not do it correctly. Here is how to set a global variable.

Private oListener, cellRange as Object
Global AllServices

Type allServicesStruct
    svc As String
End Type

Sub AddListener
    Dim sheet, cell as Object
    sheet = ThisComponent.Sheets.getByIndex(0)  'get first sheet
    cellRange = sheet.getCellrangeByName("E4:J5")
    oListener = createUnoListener("Modify_","com.sun.star.util.XModifyListener")
    cellRange.addModifyListener(oListener)
End Sub

Sub Modify_modified(oEv)
    MsgBox AllServices.svc
End Sub

Sub Main
    Dim allServicesLocal As allServicesStruct
    allServicesLocal.svc = "example"
    AllServices = allServicesLocal
    
    AddListener
End Sub

Result:

example message box

This was adapted from my answer at https://stackoverflow.com/a/70405189/5100564