Building an Add-in for Excel, Application events
In the current state of our add-in, the ribbon is loaded and populated with the worksheets in the active workbook. But that is all; if we switch to a different workbook, we end up with the wrong set of sheets. There is nothing in the code that handles updating the ribbon.
So what do we need? We want the ribbon to be updated as soon as:
- I switch to another sheet
- I hide or unhide a sheet
- I switch to another workbook
Normally if you are writing code in a normal workbook, you would be adding code to the ThisWorkbook module. You would use events like these:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
But these events only respond within that workbook. We need these events for any workbook. Events like that are called Application events.
A class module
So hold your braces, we're going to insert a Class module. Yes, we are! In order to capture application events we need to do that.
Chip Pearson has written an excellent article on Application events. I don't need to go there any more to figure out how to do this, but I encourage you to bookmark his page and head over one day to read it.
I've inserted a class module and named it clsApp:
At the top of the class module I declared an object variable using the special keyword "WithEvents":
This tells the compiler that the variable "App" will hold a pointer to an object that exposes events to us. In this case, the Excel Application. Nothing obvious appears to happen, but if you did this right, an item has been added to the left-hand dropdown of the codepane (Class and (General) are always there):
If you select "App", the VBA editor will automatically insert the default application event in your module:
That is very often the wrong one, we'll remove that later. But before doing so, click the right-hand dropdown:
That's right, it shows you all available events for the application object! Select the ones you need in turn until you get this:
Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Private Sub Class_Terminate()
Set App = Nothing
Note that I added calls to InvalidateRibbon, a subroutine in modRibbonX that looks like this:
This simple "Invalidate" method tells Excel to completely redo the callbacks to populate our ribbon controls. As simple as that. I also added the Class Terminate event to ensure the App variable is neatly set to nothing when the class is terminated. Housekeeping is also important for VBA developers!
Getting the class to work
A class module is nothing more than a code blueprint. Unlike a normal module, it does not work out of the box. You have to load a copy of the class into memory. It is that copy (developers call it an "instance") that will do the work. This I did from a normal module which I inserted and called modInit.
At the top of that module, there is a module-level object variable called mcApp that will keep a pointer to the instance of the clsApp class. This is needed to ensure the class is not terminated as soon as the routine that created it reaches its End Sub statement:
'Variable to hold instance of class clsApp
Dim mcApp As clsApp
Public Sub Init()
'Reset mcApp in case it is already loaded
Set mcApp = Nothing
'Create a new instance of clsApp
Set mcApp = New clsApp
'Pass the Excel object to it so it knows what application
'it needs to respond to
Set mcApp.App = Application
I think the comments in this code should be clear enough. If not, perhaps now is the time to go to that Chip Pearson article I told you about...
Finally, we want this code to be called when the workbook opens so the add-in starts responding to those events immediately. So in ThisWorkbook I added:
Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!Init"
I use the OnTime method, because that is an old trick that ensures Excel is completely done loading everything before Init is called. Sometimes Excel fires a workbook_open event even before it is done with its housekeeping. Well, this trick ensures Excel finishes first.
Next: Creating the add-in file