Excel Application Events And VBA

If you program VBA using events and event procedures (see this page for information about working with events), you may find Application level events useful. I would very strongly recommend that you read the Events And Event Procedures page prior to working with Application events.

Application events and their related event procedures are very much like events of the Workbook object or the Worksheet object. The only real difference is that there is no built in container for the application events, as there is for Workbook events (ThisWorkbook) and for Worksheet events (the Sheet modules). You must use an existing object module such as ThisWorkbook or create a dedicated class module to handle events. Neither approach, using an existing module or creating a dedicated module, has any real advantage over the other. I tend to use a separate class module for Application events so the code is modular and separated by function into separate modules. You may use which ever method your prefer.

In addition to its own events, such as NewWorkbook, Applicaion events replicate all events of its subordinate objects, the Workbook and the Worksheet objects. The events in are executed in the Sheet module first (if the event is declared), then in the Workbook module (if the event is declared) and finally in the Application module.

OBJECT MODULE -- An Object Module is one of the following objects: a Class module, the ThisWorkbook module, a Sheet module, or the code module of a user form. You can receive events messages only with code in an object module, since you must use the WithEvents to receive events and WithEvents is allowed only in object modules. Also, only object modules may raise custom events declared with the Public Event code and raised with the RaiseEvent statement.

You can use any object module to receive Application events. Of the various built-in object modules, the logical place to receive application events is the ThisWorkbook object module. In the ThisWorkbook module, enter the following code:

Private WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

This code creates a variable named App of type Application. It is declared with the WithEvents keyword to allow the object to receive events from the Application. Then, this variable is set to the Applicaiton object in the Workbook_Open event procedure. No further action is required. You can now use the events of the Application object. First, select App in the left side drop down list at the top of the code window. Then, expand the right side drop down list at the top of the code window. This drop down will list all of the available events for the Application. For example, there is a NewWorkbook event that is triggered when a new workbook is created. You can use code like the following to use this event.

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
MsgBox "New Workbook: " & Wb.Name
End Sub

As an alternative to declaring the Application events in an existing object module such as the ThisWorkbook module, you can create your own class module to handle Application events. This requires a few extra lines of code, but it keeps Applicaion events in their own class, which is beneficial for a well organized project, especially in large projects. In my commercial work, I almost always put Application events in their own class.

The first step to to insert a new class module into your project and name this class CExcelEvents. In that class module, insert the following code:

Private WithEvents App As Application

Private Sub Class_Initialize()
Set App = Application
End Sub

Next, create the event procedures. In the left side drop down list at the top of the code window, choose App and then in the right side drop down list at the top of the code window choose the event you want to use. For example, you can use the WorkbookOpen event to receive an a message when an existing workbook is opened:

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "New Workbook: " & Wb.Name
End Sub

Now, close the CExcelEvents class module and open the ThisWorkbook module. In that module, insert the following code:

Private XLApp As CExcelEvents

Private Sub Workbook_Open()
Set XLApp = New CExcelEvents
End Sub

This code uses a separate module, CExcelEvents, to work with the events. In that class, App is declared as type Application with the WithEvents keyword. That links the CExcelEvents into Excel's event system. In the ThisWorkbook module, we create a variable, XLApp to hold an instance of CExcelEvents and use the Workbook_Open event to create the new instance of XLApp. By using the Workbook_Open event, the XLApp is set when the workbook is opened.

The Class_Initialize procedure of CExcelEvents is executed when a new instance of the class is created, and this function sets the App variable to the Application.
By using the Workbook_Open event and the Class_Initialize procedure, the entire process of creating the event handlers and linking them into Excel event system is fully automatic. When the workbook is opened, all the variables get set to the proper objects.