Clarification of request context and event listeners
Hi, I am currently building an Excel add-in and I am looking for some clarification on how the Excel.RequestContext object works, particularly with relation to bindings and event listeners.
In my add-in, I have a 'table' - a collection of ranges formatted to look like a table, rather than a legitimate Excel.Table object - which a user can enter text into. Several of the ranges utilise built-in features such as data validation and conditional formatting and one of the ranges has a binding with an onDataChanged EventListener, which alters another cell in the worksheet, depending on the value of this particular range. This is a simplified version; the short story is the functionality is too complex to use a native Excel formula. Using an EventListener works perfectly, until the user starts adding rows to the table, using a custom button on the right-click context menu. Each of these rows contains the same set of ranges, holding data validation, formatting, and EventListeners as described above. The first time a row is added, everything continues to work as expected. However, the second time the context menu button is used and a row is added, only the EventListener on the most recently added row works, the others no longer update the associated cell(s), or show any sign of having the EventListener attached when the code is run in debug. To my best understanding, this happens because each time the menu button is clicked, a new context instance is created, therefore 'overwriting' the previous instance. As the right-click menu button triggers a different kind (? I don't know how to articulate it) of context, the first time a row is added, the existing rows are unaffected, because they were created in a context derived from a ribbon button click, and therefore do not exist in the same context space as that of the right-click menu. However, the second time the button is clicked, a new context instance in the same space is created and as EventListeners only persist within the context in which they were created (I think?), they are immediately lost when a new context is instantiated, with the only ones to still be valid being the ones which were just created. This is understanding I have gleaned from trial-and-error rather than any official documentation, so clarity on whether this is a correct interpretation of what is happening would be very much appreciated!
Additionally, I am struggling to think of the best solution for this would be. At the moment, I am leaning towards having a function which reassigns EventListeners every time a new row is added to the table, but I can envisage that spiralling out of control very quickly; as the add-in grows and there are more functions to keep track of, I am not sure of a) how to structure it in a maintainable way or b) the performance loss incurred by having to frequently reassign EventListeners to multiple ranges.
Any insight into this would be gratefully received, I am very aware of how much I don't know, so if there is anyone who can help to guide in the right direction, if not offer an outright solution, I would be deeply appreciative. Many thanks!