I am creating an Excel Add-in in Angular using TypeScript. The goal of the add-in is to bind values from the add-in to the spreadsheet and save the reference so the user can know which worksheet/cell he bounded the value to.
However, if the user inserts or removes a column (or a row), the cell reference recorded in the add-in needs to update to the new value.
Here's an example: Let's say a user binds a value to cell A1. The reference (A1) will be displayed for that value in the add-in. Now, the user adds a new column in position A. The value previously shown in cell A1 will now be in cell B1. However, the add-in will still show cell A1 for the reference.
I have thought of using the event Office.EventType.DocumentSelectionChanged to detect changes in the document. The problem is that the handler doesn't specify which part of the worksheet has changed, and I don't see myself checking every single cell to see if something has changed, as it would greatly impact my add-in performance.
Any suggestions?
The best approach IMHO is to use Excel's Defined Names (aka Named Ranges or Named Items) instead of explicit references. That way you are making Excel do the work for you rather than trying to duplicate what Excel is already doing in your own code using Events.
This is the recommended and most frequently used technique in other languages and APIs