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

Question

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

Welcome to SO! *"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"* - why not formula like as `=SUM(E4:J5)` in target cell? — JohnSUN, Apr 11 '22 at 10:14
@JohnSUN Thank you! I should have mentioned that the cells actually contain text: the user types in the names of different services, and the program automatically fetches the number of hours associated with each service's name to add them all up. — Idris Cheikh, Apr 11 '22 at 10:47
Yes, `SUM()` won't work in this case. However, you can easily convert `UpdateTotalHoursOfAgent(calendarSize, allServices, agentTopleftCell)` into a user defined function and write something like `=UPDATETOTALHOURSOFAGENT(Sizes.$B$4:$C$24, Dictionary.$B$8:$C$31, E4:J5)` into the target cell. If you do not turn off Auto-recalculation, then the function will be calculated for any change in the cells in the specified ranges - is this exactly the result that you are trying to achieve using the event handler? — JohnSUN, Apr 11 '22 at 13:33

score 0 · Answer 1 · answered Apr 11 '22 at 13:17

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:

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

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

1 Answers1