Experienced VBA programmers are familiar with Worksheet and Workbook level events -- those events that occur when certain actions are performed on a workbook or worksheet.   For information about events in general, click here.

Excel also supports Application level events, such as adding a new workbook.   However, these are not as simple to implement as worksheet and workbook level events, because there is no user-programmable object, similar to a worksheet or workbook, that represents the Application object.

First, you must create a class module to contain the events, and then enter the event code procedures in the class module.  You may create the class module in the workbook that you want to handle application events, or you may create it in a workbook that will always be open (though possibly hidden), such as your Personal.XLS workbook.

In the VBA Editor, insert a class module from the Insert menu.   Press F4 to view the properties for the class module, and assign the name EventClass to the module. 

In the General Declarations section of the module, enter the following line of code:

Public WithEvents App As Application

Now, in the ThisWorkbook module of your workbook, enter the following declaration in Global declaration section of your code.

Dim AppClass As EventClass

This declares a new instance of the EventClass named AppClass.   This provides a link between your workbook code and the application EventClass.  Next, you've got to set AppClass to the current Excel Application.  Enter the following code in the Workbook_Open event procedure for your workbook:

Set AppClass = New EventClass
Set AppClass.App = Application

If desired, you can move the code to initialize the App object to the Initialize event of the class itself. This event is automatically executed when a new object based on the class module is created. There is also a corresponding Terminate event.

If you use this technique, you can remove the following line of code from your Workbook_Open event procedure.

Set AppClass.App = Application

Now, your workbook is ready to process application level events. 

To code application level events, open the EventClass module, select App from the section combo-box in the code window and select the procedure name from event combo-box.  The parameters passed to these procedures are similar to those passed to the workbook and worksheet level event procedures.

If more than one open workbooks are enabled for Application-level events, both workbooks will receive the events. However, you should not count on one workbook receiving the events before another.

Download a workbook illustrating Application Events.