Custom Workbook Events

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:

1. ThisWorkbook
2. A module to contain all the custom events called from Item 1
3. 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)

Please Login or Register  to view this content.

1. 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)
2. 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.
3. 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.
4. 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
   

   Please Login or Register  to view this content.

   ?
5. 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)
6. QUESTION: Is there anything you have noticed that you think should be changed/added?

Hi mc84,

It looks like there is a way to create custom events in VBA. Read about them at:
https://docs.microsoft.com/en-us/off...vent-statement
Or
https://stackoverflow.com/questions/...ithevents-part

Marv

Hi Marv, Thanks for the suggestion. My questions are not so much asking "How" to run custom events but rather focusing more on what we are doing with custom events and why.

Hey,

I think the first line in my last suggestion has an example of what you would use a RaiseEvent call for. I wish there were more examples of custom events.

Here is another site that uses Events on Charts.
https://powerspreadsheets.com/excel-vba-events/
I think this site explains many more places where Events might be used.

Hi Kyle, good to hear from you again

Originally Posted by Kyle123

Before we start, are you using actual custom events or Subs/Functions that you are referring to as “events”.

I am using the event procedures in the ThisWorkbook module * to call my custom Subs.

Put another way - I am redirecting the actual event macros in TWB module by having them call my custom macros. As an example, here is one of the procedures from ThisWorkbook class/module

Please Login or Register  to view this content.

* Note I usually refer to this as the 'ThisWorkbook class' - as I mentally classify this as being a type of class. Stop me if that is confusing!

Originally Posted by Kyle123

I suspect the latter based on your description and code, if so are you wedded to this approach or are you prepared to use actual events?

Well I'm wouldn't say I am wedded to my current approach, no. I am open to the idea of changing over to a custom class and WithEvents if that is a better approach.

Originally Posted by Kyle123

It will make a significant difference to any code/approach

Agreed. Correct me if I am wrong, but would the differences be limited to how we start/trigger the custom events and where they are stored? What we actually decide to do in the custom event itself would be the same regardless of which approach used?

I found that if I cleared the tab, removed instructions and saved it. Then re-entered it exactly as before, I got my image back.