Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Build Excel Add-in > Application events
Deze pagina in het Nederlands

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)
    InvalidateRibbon
End Sub

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
    InvalidateRibbon
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    InvalidateRibbon
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()
    moRibbon.Invalidate
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


 


Comments

All comments about this page:


Comment by: D. Dunn (5/8/2015 7:03:29 PM)

How would I update a control on the ribbon? For example I'd like to add a label to the ribbon to display the name of the active worksheet. It's redundant, I know, :-) but should be able to translate to other controls.

 


Comment by: Jan Karel Pieterse (5/9/2015 11:43:53 AM)

Hi Jango,

You would use the getLabel callback of the label control.

So in RibbonX:

<labelControl
id="Labelcontrol1"
getLabel="Labelcontrol1_getLabel"/>


And in VBA:

Public Sub Labelcontrol1_getLabel(control as IRibbonControl, ByRef returnedVal)
'
' Code for getLabel callback. Ribbon control labelControl
'
returnedVal = ActiveSheet.Name
End Sub

 


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 but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

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