With class modules, you can create your own objects. You can also extend the function of existing objects. In this post, I will describe how to use a class module to hook events of the Application object.
With objects such as the Workbook object or the Worksheet object, Excel has provided a class module for you (e.g. ThisWorkbook, Sheet1) complete with events for you to use. It doesn’t, however, provide a class module for the Application object. For that, you need to create your own class module.
Why would you want to? The Worksheet object has a Change event that fires whenever a cell on that worksheet is changed. The Workbook object has a SheetChange event that fires whenever a cell on any sheet in that Workbook is changed. Application events go one step further. For instance, the Application object has a SheetChange event that fires whenever a cell on any sheet in any workbook is changed – application wide. There are other useful events for the Application object that can’t be found anywhere else. Let’s get started setting one up.
First, you’ll need a blank workbook with a standard module and a class module. In the standard module, type one line like this
Public gxlApp As Class1
A public variable in a standard module will never go out of scope as long as the workbook is open. We need our “App” variable to be around all the time or the events won’t fire. Class1 is the name of our class module. In the class module, create another variable like this
Public WithEvents xlApp As Application
The WithEvents keyword tells VBA that we want access to this objects events, the object in question being the Application object. With just this much done, you can see which events are available by using the dropdowns at the top of the code pane.
Now let’s create an event procedure. In the class module, use the dropdowns to insert the Workbook_NewSheet event. This will insert the Sub and End Sub statements between which you can type your code. Create a macro that defines a certain header whenever a worksheet is inserted into any workbook.
Private Sub xlApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
If TypeName(Sh) = "Worksheet" Then
Sh.PageSetup.CenterHeader = "ABC, Inc."
Any time a sheet is added to any workbook, this event will fire. If the sheet is a worksheet, the header will be added. There’s one more step before it will work. We need to tell VBA that our public variable gxlApp represents the Application object. This is commonly done in the Workbook_Open event. In the ThisWorkbook module, type a procedure like this
Private Sub Workbook_Open()
Set gxlApp = New Class1
Set gxlApp.xlApp = Application
Press F5 while in this procedure to simulate the workbook opening. Now add a sheet to the workbook and watch the magic. For even more fun, add a new workbook and add a sheet to that.
This example is only illustrative. There are better ways to add a header to new worksheets. The basic steps to using application level events are
- Create a public variable in a standard module
- Create a class module with a public variable using the WithEvents keyword and dimmed as the Application object.
- Assign the class module variable to the object in a procedure (usually Workbook_Open)