Creating Event Procedures for Built-in Events by Using the WithEvents Keyword

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

The objects for which you have commonly written event procedures — UserForms, Microsoft® Access forms and reports, and the ThisDocument, ThisWorkbook, and SheetN objects — have one thing in common: They all have an associated class module. Some other objects in the Microsoft® Office 97, Office 2000 and Office XP object models also provide built-in events, but they do not have associated class modules. For example, if you look at the Object Browser in the Visual Basic Editor in Microsoft® Excel, you'll see that the Excel Application object has several events listed as its members — NewWorkbook, WorkbookOpen, and SheetChange, to name a few. Examples of other Excel objects that provide events include the Chart, OLEObject, and QueryTable objects. The Microsoft® Word Application object and the Microsoft® PowerPoint® Application object also provide events.

You can create event procedures for these events in a generic class module. Though the class module is not associated with the object by default, it can contain event procedures for an object that has events.

If you consider event procedures in a form module, you might realize that those event procedures exist only for a particular instance of the form. For example, UserForm1 and UserForm2 are separate instances of an object of type UserForm. Both have an Initialize event, which occurs only when that form is loaded. The Initialize event procedure for UserForm1 runs only when UserForm1 is loaded, not when UserForm2 is loaded, and vice versa.

The same holds true for events on objects that do not have associated modules — an event occurs for a particular instance of an object. More specifically, it occurs for an instance of an object that you have indicated should respond to events, not for any other instance.

To indicate that an instance of an object should respond to events, you declare a module-level object variable of that type by using the WithEvents keyword in a class module. This keyword notifies Microsoft® Visual Basic® for Applications (VBA) that you want to respond to events for the instance that is assigned to that object variable. You can use the WithEvents keyword only with objects that support events, and only in a class module.

For example, the following line of code in a class module declares a private object variable of type Excel.Application to respond to events:

Private WithEvents xlApp As Excel.Application

When you have declared an object variable to respond to events, that object variable appears in the Object box in the class module's Code window, and its events appear in the Procedures box in the Code window. To create an event procedure stub, click the object name and event name in these boxes. The Visual Basic Editor inserts an event procedure stub for you that looks similar to the following:

Private Sub xlApp_NewWorkbook(ByVal Wb As Excel.Workbook)

End Sub

At this point, you have created an object variable that has associated events, and an event procedure. The object variable does not yet point to anything, however. You must assign a reference to it, which you can do in the Initialize event procedure for the class module. The Initialize event occurs as soon as a new instance of the class is created, so if you assign a reference to the object variable here, you can always be sure it will exist when you must have it.

In this case, you want the object variable to point to the current instance of the application:

Private Sub Class_Initialize()
   Set xlApp = Application
End Sub

Finally, you must create a new instance of the class to trigger the Initialize event and load the event-ready Application object variable into memory. Insert a new standard module, and declare a private module-level object variable that will point to the instance of the class. For example, if the class is named XLEvents, you can declare the following object variable:

Private p_evtEvents As XLEvents

You must declare this object variable at the module level, so that it will remain in memory until the project is closed or reset, because this object variable points to the class that contains the event-ready object and its event procedures. Otherwise, the object will no longer respond to events when the variable has gone out of scope.

Next, add the procedure that creates the new instance of the class in a standard module:

Public Sub InitXLEvents()
   Set p_evtEvents = New XLEvents
End Sub

After you run this procedure, any code you have added to the Application object's event procedures will run when the corresponding Application object event occurs.

Note that you have to run the InitXLEvents procedure each time you open Excel to make the Application object event-ready. It is not possible to define events for the Application object once and for all.

Note If you think about the events that are available to you for objects such as the Excel Application object, you might realize that there's no way to use some of them consistently from within their own application. For example, the Excel Application object has an OpenWorkbook event. However, to run a procedure such as InitXLEvents, you are going to have to open a workbook. So there's no way to trap the event for the first workbook that's opened, although after you run InitXLEvents, the event will occur when subsequent workbooks are opened.

Note It makes more sense to use the OpenWorkbook event in the context of a COM component that supports Automation (formerly OLE Automation). For example, you can write an application in Microsoft® Word that includes the class and standard modules described above. You have to make two changes — you must set a reference to the Excel object library, and you must use the New keyword to create a new instance of the Excel Application object, rather than returning the current instance. When you create a new instance of the Application object from Automation by calling the InitXLEvents procedure or one similar to it, Excel loads without opening a workbook. The Application object is now event-ready, and the OpenWorkbook event will occur as soon as you open a workbook through Automation.

Share via

Creating Event Procedures for Built-in Events by Using the WithEvents Keyword

See Also

Additional resources