[Dropped] Pass arguments to LibreOffice Basic event listener

[idris.cheikh]

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):

Code: Select all

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

[ms777]

Hi, this works like a charm for me

Good luck,

ms777

Code: Select all

Global counter

Sub Main
    counter = 0

    sheet = ThisComponent.Sheets.getByIndex(0)  'get leftmost sheet
    cellRange = sheet.getCellrangeByName("A1:B2")
   
    oListener = createUnoListener("Modify_","com.sun.star.util.XModifyListener")  'create a listener
    cellRange.addModifyListener(oListener)  'register the listener
End Sub

Sub Modify_modified(oEv)
	counter = counter + 1
	ThisComponent.Sheets.getByIndex(0).getCellByPosition(3, 0).Value = counter
End Sub

[JeJe]

You can store values various places

If it doesn't matter that a user can change it - custom document properties (or you can put "Do not modify" at the start)

a simple external text file

User defined attributes (I don't know how stable this is in calc)
https://forum.openoffice.org//en/forum/ ... 0&p=368642

in a hidden form control perhaps anchored to page/width and height 0/using the control's additional information (.tag)

hidden in an out of the way cell with white text so it can't be seen

Or a combination of one of those with a global variable. Say loading from a file to the global variable - testing to see if empty first - in which case Basic has been reset and it needs to be loaded.

Probably other places I haven't thought of

[MrProgrammer]

Hi, and welcome to the forum.

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.)

No need for evil macros and cell listeners and wasting hours of your time researching access to variables in Basic when Calc (or any spreadsheet from the last 25 years) provides standard features to total the cost of various named services. The UseAuxillaryColumns sheet is the simplest way to do that. The UseSUMPRODUCT sheet avoids an auxillary column if you want to add complexity. Of course, either method automatically updates the total if you change any of the services, since that's how spreadsheets work.

202204111633.ods

(11.42 KiB) Downloaded 144 times

[Tutorial] VLOOKUP questions and answers

If you need any additional assistance attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Explain in detail which cells contain the services to be added and how one knows the values of those services. I will ignore any post without a spreadsheet attachment.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[robleyd]

Cross posted at AskLibreOffice

If you cross post, please let us know that you have done so, otherwise it leads to several discussions and a waste of time because several identical answers may be posted by different users.

[idris.cheikh]

Hello,

Thank you to everybody for your answers, it is much appreciated.

@MrProgrammer
Thank you very much for your solution. I decided to give up my hundreds of lines of code for an adapted version of it (Sheet2 contains the services' names along with their beginning/end times of day and associated durations. Sheet1 reuses a modified version of your formula that deals with blank cells too).
However, I am starting to wonder if I shouldn't have stuck with the code:

• * I need to account for a 10% increase in the number of hours depending on whether a given service took place on a Sunday or at night.
  * I should also count holiday hours for certain agents and not others, and in varying quantities, depending on their contract (e.g. 168h/month, or 160h, or 150h; info in the cell next to the agent's name).
  * Finally, I need to check whether the distribution of services is in compliance with the law according to a set of rules I still need to study.

I do not know much about spreadsheets. Taking the above into account, would you still recommend against writing macros or not? If so, how would you go about implementing the above in Calc (just in broad strokes, so I don't go in the wrong direction)?

Thank you very much for your time and for your help, it is much appreciated.

Best regards,
Idris

[MrProgrammer]

Thank you for the attachment. It really helps to see your layout.

Sheet1 reuses a modified version of your formula that deals with blank cells too).

VLOOKUP can match the empty cells and supply 0 as their value, so you don't need to use IF and ISBLANK in your formula. See Q26/A26 in the VLOOKUP tutorial. I was surprised it didn't already cover that situation.

I am starting to wonder if I shouldn't have stuck with the code

For someone with a background which focuses on imperative programming, I can appreciate that it is natural to want to use macros for your calculations. The problem with that in an OpenOffice environment is that one then needs to use the Application Programming Interface, which is very difficult to understand. It raises hundreds of questions about "How do I access cell attribute X?", "How to I change cell attribute Y?", "How do I get control when Z happens?", "How can my macro create a formula which references the cell above?" etc. It can be difficult for a beginner to determine how to access the service one wants or which interface can perform a desired operation. Things like "How can I tell if a cell is merged?" or even "How do I determine the active cell?" can be surprisingly complicated. It will take at least a week of your time to begin to understand the API. While you may be able to find examples on the forum for some tasks, that can be challenging, and "copy and paste" programming without really understanding the API is unlikely to be suitable except for very simple tasks.

I do not know much about spreadsheets.

The Ten Concepts tutorial is a good place to start. But in this case I will tell you that your data layout is going to make using formulas difficult. You are using a "cross table" which presents information in a 2-dimensional layout, which is nice for display but not well-suited for performing calculations. It is much better to organize in "normal form" with data in columns, where each cell in a column contains the same type of information, as I did in the UseAuxillaryColumns sheet which uses very simple formulas for its calculation. For example, column A might contain an agent name, column B a date, C a service, D and E the start and end times, F the duration, etc. We have numerous posts on the forum where people propose a cross table but can't determine how to calculate something and a volunteer shows how to reorganize into normal form and then the calculation is simple. In your situation, once the calculations are complete you can create another sheet which presents the information in a cross table for display. These display sheets are often very simple to create using the Pivot Table feature where no additional formulas are needed.

* I need to account for a 10% increase in the number of hours depending on whether a given service took place on a Sunday or at night.
* I should also count holiday hours for certain agents and not others, and in varying quantities, depending on their contract (e.g. 168h/month, or 160h, or 150h; info in the cell next to the agent's name).
* Finally, I need to check whether the distribution of services is in compliance with the law according to a set of rules I still need to study.

These seem as if they should be possible, though someone who says "I do not know much about spreadsheets" may need help. They key is to use auxiliary columns to hold sub-calculations (like the duration above) so that the formulas do not become too involved. Counting holiday hours will probably involve a table which provides those dates.

Taking the above into account, would you still recommend against writing macros or not?

If it were me, I would use macros as an absolute last resort. Other volunteers may have different opinions. My preference is to use the standard features of Calc, formulas, pivot tables, filters, sorting, etc. A hybrid approach with Calc (which is functional programming) and imperative programming is to use the latter, say Perl or Python, to create a CSV file after all of the calculations have been completed, then use [Example] Loading CSV into preformatted spreadsheets to display the CSV data as a spreadsheet, perhaps creating a pivot table as the final step.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[idris.cheikh]

Hello @MrProgrammer,

Thank you very much for your answer.

I've started working on the first item in the list. I could calculate the pay increase for a Sunday shift easily (although I realized while writing this that I probably failed to deal with edge cases).
That said, I got stuck trying to calculate the pay increase for a night shift. As you predicted, the formula quickly became too involved, and I cannot find a method that is more straightforward.

Any tips would be much appreciated, if you don't mind having a look at it. I've created a new topic for it.

Thank you very much for your help.

Best regards,
Idris

[idris.cheikh]

Hello again,

While the replies were helpful and answered my specific needs, I feel like it would be good to still provide an answer to my original question so I can mark this thread as solved and so it can be useful to anyone visiting this page in the future who might need precisely to pass arguments to an event listener.

If anybody knows how to do this, kindly outline the method to follow (or is using global variables the way to go in this case)?

Best regards,
Idris

[JeJe]

You can't pass any more arguments to an event listener than the built in one.

I gave you a list of several workarounds that won't get reset like a global variable.
Here's the code for making a document variable for calendarsize and how to access it.

Code: Select all

propname = "calendarSize"

	with ThisComponent.DocumentProperties.UserDefinedProperties
		if .getPropertySetInfo().hasPropertyByName(propname) = true then .removeproperty(propname)
		.addProperty(propname,128,1.1) '128 = removeable
		'putting the number 1.1 will ensure the variable type is a double
		'if we wanted a long we'd pick a number in the long range big enough so it couldn't be made something smaller
		.setPropertyValue(propname, 50)	'change it to 50 and it will still be a double
		msgbox .getpropertyvalue(propname)
	end with

[JeJe]

Here's a quickly written example of loading and saving from a text file in the user's userconfig path location (edit: actually simple binary file/dat file used)

If calendarSize (which I've made a long this time when set) is empty the function will load the value from that file and reset the global variable to it.

Code: Select all

Global calendarSize

sub test

'remove file if exists
	oPathSettings = CreateUnoService( "com.sun.star.util.PathSettings" )
	fileurl = oPathSettings.UserConfig & "/CalendarSize.dat"
	if dir(fileurl) <>"" then kill fileurl


	calendarsize=empty
	msgbox "isempty " & isempty(calendarsize) 'variable empty
	msgbox "calendarsize " & getcalendarsize 'return 0 as we've never set it yet
	setCalendarSize 50 'set it to 50
	msgbox "calendarsize " & getcalendarsize 'returns 50
	calendarsize = empty 'make calendarsize empty again
	msgbox "isempty " & isempty(calendarsize) 'shows this
	msgbox "calendarsize " & getcalendarsize 'value is now returned from the file

end sub

Sub setCalendarSize(newcalendarSize as long)
	calendarSize = newcalendarSize
	oPathSettings = CreateUnoService( "com.sun.star.util.PathSettings" )
	fileurl = oPathSettings.UserConfig & "/CalendarSize.dat"
	f = freefile
	open fileurl for binary as #f
	put f,,newcalendarsize
	close #f

End Sub

function GetCalendarSize() as long
	dim newcalendarsize as long
	if isempty(calendarSize) then
		oPathSettings = CreateUnoService( "com.sun.star.util.PathSettings" )
		fileurl = oPathSettings.UserConfig & "/CalendarSize.dat"
		if dir(fileurl) <>"" then
			f = freefile
			open fileurl for binary as #f
			get f,,newcalendarsize
			close #f
			getcalendarsize = newcalendarsize
			calendarsize= newcalendarsize
		end if
	else
		GetCalendarSize = CalendarSize
	end if
end function