Goal = I'm trying to create reusable code to handle custom events at a workbook level.
Ideally I would like to make this so that I can add it to existing Excel macro projects/tools (be it XLSM or XLAM) and only minimal changes are required to make the code work with that project.
(Optional background info - I have several projects that use custom events. Most of these created by me but their custom events were unique to each project. I am now trying to consolidate their code to create a reusable/template class or module that they can each use.)
At this stage, I am using three objects:
- A module to contain all the custom events called from Item 1
- A module to contain the functions & global variables that are used by Item 2 but their values are unique to that project
The point of keeping Item 2 separate to Item 1 is to reduce clutter in ThisWorkbook class. Also to store all the subprocedures/functions that the custom events require.
The point of keeping Item 3 separate to Item 2 is that whenever I update Item 2, I can copy/paste it to any other project already using this code without needing to make additional edits.
So starting with Item 1 (i.e. the code inside ThisWorkbook class). Below is what I am currently using: (I will show/look at Items 2 & 3 after Item 1 is sorted out)
- COMMENT: This code is not 100% reusable as 1 variable and 2 properties need to be commented out if the project isn't using custom ribbon code. (Most of my projects already are. I see no need to change this code - it already notes the need to comment out that part of the code if that particular project is not using)
- COMMENT: The code is calling procedures that are not shown e.g. CustomEvent_TWB_BeforeSave. These are kept in Item 2. I will show these once Item 1 concepts are sorted out.
- COMMENT: Workbook_BeforeClose - The code references global constants that are not shown. These are kept in Item 3. I will show these once Item 1 concepts are sorted out.
- QUESTION: Workbook_BeforeClose - I have doubts as the Custom Save event code I have inside this function. Shouldn't the Workbook_BeforeSave trigger automatically whenever Workbook_BeforeClose is triggered and the XLSM/XLAM has property
- QUESTION: Workbook_AddinUninstall - Is there any point setting this variable back to false? Would it not be impossible to fire Workbook_Open event for that add-in after it has been uninstalled UNLESS the user reinstalls the add-in in the same Excel session? (In which case the Workbook_AddInInstall would set the variable back to True anyway)
- QUESTION: Is there anything you have noticed that you think should be changed/added?