| Microsoft Office 2000/Visual Basic Programmer's Guide | |
The objects for which you've commonly written event procedures — UserForms, 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 Office 97 and Office 2000 object models also provide built-in events, but they don't have associated class modules. For example, if you look at the Object Browser in the Visual Basic Editor in Excel 2000, 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 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 isn't 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 may 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 don't 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've 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 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
Once you've 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 like the following:
Private Sub xlApp_NewWorkbook(ByVal Wb As Excel.Workbook)
End Sub
At this point, you've created an object variable that has associated events, and an event procedure. The object variable doesn't yet point to anything, however. You need to 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 need 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 need to create a new instance of the class in order 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, since 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 once 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've 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 in order to make the Application object event-ready. It's 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 may 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. But in order to run a procedure like InitXLEvents, you're going to need 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.
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 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 need to 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 like 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.