Home Newsletter

Deze pagina in het NederlandsHome > Article index > Build Excel Add-in >

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.

Necessary events

So what do we need? We want the ribbon to be updated as soon as:

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_Activate()
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
End Sub

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:

Class module

At the top of the class module I declared an object variable using the special keyword "WithEvents":

Public WithEvents App As Application

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):

The top-left dropdown of the classes code window

If you select "App", the VBA editor will automatically insert the default application event in your module:

Private Sub App_NewWorkbook(ByVal Wb As Workbook)

End Sub

That is very often the wrong one, we'll remove that later. But before doing so, click the right-hand dropdown:

The lefthand dropdown, showing the available events

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_SheetActivate(ByVal Sh As Object)
End Sub

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
End Sub

Private Sub Class_Terminate()
    Set App = Nothing
End Sub

Note that I added calls to InvalidateRibbon, a subroutine in modRibbonX that looks like this:

Sub InvalidateRibbon()
End Sub

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:

Option Explicit

'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
End Sub

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:

Private Sub Workbook_Open()
    Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!Init"
End Sub

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



Showing last 8 comments of 14 in total (Show All Comments):


Comment by: Grzegorz (2-2-2018 14:02:44) deeplink to this comment

Hi, I want to use your way to invalidateRibbon, however:
When I add your add-in to Excel addins, then whenever I want to open any XLS files, my excel dont respond, I have to close it by manager task.
It works perfectly only if I run Excel first, then excel file...

Comment by: Jan Karel Pieterse (8-2-2018 07:19:45) deeplink to this comment

Hi Grzegorz,

One thing that is very, very important when you use this method is that you must make sure you do not try to use a pointer which is wrong. If you save the file with an old pointer value and then re-open it, Excel will probably crash because you try to assign the ribbon object using the wrong value for the pointer. So add a before-save event which clears the pointer from the cell and add a check for the content of the cell too.

Comment by: @chernipeski (17-5-2020 03:52:00) deeplink to this comment

So I am to ask my user base to go into developer mode and put this code into every new workbook they create?

Private Sub Workbook_Open()
    Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!Init"
End Sub

Surely you jest?

Comment by: Jan Karel Pieterse (18-5-2020 11:43:00) deeplink to this comment

Hi chernipeski,

I'm not sure I understand your remark?

Comment by: @chernipeski (20-5-2020 00:54:00) deeplink to this comment

(1) I code my add-in
(2) I publish my add-in on the company network, Internet, or send it in a message
(3) My user selects my Add-In
(4) My user opens a new Workbook
(5) how does your initialization code get into their brand-new Workbook?

Private Sub Workbook_Open()
    Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!Init"
End Sub

Comment by: Jan Karel Pieterse (20-5-2020 10:42:00) deeplink to this comment

Hi chernipeski,

The article does not explain how to update VBA code using VBA code. To do so, have a look at this article by Chip Pearson: http://www.cpearson.com/Excel/vbe.aspx

Comment by: @chernipeski (29-5-2020 11:08:00) deeplink to this comment

That's a cool article. Ok here's the situation. I have a VBA add-in. It is an event handler that captures a double-click on any cell in the user's workbook. Works great when I'm running my own source workbook. When I publish the xlam file and my customer uses my add-in, and double-click's on a cell nothing at all happens. This is because the add-in never initializes the add-in module that establishes the event handler. All the events are captured by the module my client's workbook establishes when he creates that. My add-in modules are completely ignored.

What does my user have to do to make the double-click event handler in the Add-In he bought from me begin to work? The answer should be NOTHING. He bought the handler, it should handle things on it's own. But for sake of discussion, what do you all suggest my customer should do to make my event handler work in his workbooks?

BTW here it is: www.facebook.com/datepickr

Comment by: Jan Karel Pieterse (29-5-2020 12:05:00) deeplink to this comment

Hi Gary,

Perhaps this: https://jkp-ads.com/Articles/Excel-Add-ins-fail-to-load.asp

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website according to our Privacy Policy.