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
Probably you did not do it correctly. Here is how to set a global variable.
Result:
This was adapted from my answer at https://stackoverflow.com/a/70405189/5100564