Trapping Events in an Add-In

Events are some of a developer's more powerful and useful tools. They allow you to create solutions that intercept double-clicking a cell in an Excel spreadsheet, the selection of a sentence in a Word document, a right-click on a shape on a PowerPoint slide, or a mail item opened in Outlook's Inbox. Events enable more solutions than any other aspect of the programming model that Microsoft Office 2000 provides. Events can be handled, or trapped, in a COM add-in just as easily as they can be from any VBA project in an Excel workbook, for example.

The following steps describe placing code to handle Excel events in a class module. The code is encapsulated in a separate class module; that way the code in the class module containing the OnConnection and OnDisconnection procedures is kept separate from the code that handles events in an application. If your COM add-in works for more than one Office application, this code separation makes the project simpler to follow and easier to read and expand.

Setting Up the SheetSelectionChange Event in Excel

  1. On the Project menu, click Add Class Module. In the Properties window, set the Name property to ExcelEvents.

  2. If a reference to the Excel object library isn't listed in your COM add-in project, click References on the Project menu and select the Microsoft Excel 9.0 Object Library entry in the Available References list. You'll know if there's no reference to Excel if "Excel" doesn't appear in the AutoList Members drop-down list after you type As in the declaration statement in the next step.

  3. Add the following line of code to the top of the class module ExcelEvents:

    4. Public WithEvents XlApp As Excel.Application

    The WithEvents keyword specifies that variable XlApp is an object variable used to respond to events that Excel triggers. After the new object has been declared with events, it appears in the Object drop-down list in the ExcelEvents class module, and you can write event procedures for the new object. (When you select the new object in the Object drop-down list, the valid events for that object are listed in the Procedure drop-down list.)

  4. Click XlApp in the Object drop-down list of the ExcelEvents class module.

  5. Click the SheetSelectionChange event in the Procedure drop-down list to add the procedure to the ExcelEvents class module. Add the following code to the SheetSelectionChange procedure:

    6. Private Sub XlApp_SheetSelectionChange( _
    ByVal Sh As Object, ByVal Target As Excel.Range)

    MsgBox "SheetSelectionChange"
End Sub

    In the Procedure drop-down list, all of the Application-level events that Excel triggers are listed when XlApp is selected in the Object drop-down list. In this example, the SheetSelectionChange event procedure is set up. The SheetSelectionChange event is triggered every time you change the active cell on the active worksheet. You can add any of the Application-level events by selecting any of the events listed in the Procedure drop-down list.

  6. On the File menu, click Save Project to save the ExcelEvents project file. Name the file ExcelEvents.cls.

  7. In the Project Explorer window, select the ConnectExcel item and click the View Code button at the top left of the Project Explorer window. Add the following line of code to the top of the ConnectExcel code module.

    8. Dim XlEvents As New ExcelEvents

    As described in the Visual Basic for Applications help file, the New keyword enables the implicit creation of an object. In this case a new instance of the ExcelEvents class module is created on first reference to it, so you don't have to use the Set statement to assign the object reference. The first reference to this class is made in the next step.

  8. In the procedure AddinInstance_OnConnection, add the following line at the end of the procedure:

    9. Set XlEvents.XlApp = Application

    Before the event procedures will run, the declared object XlApp in the ExcelEvents class module must be connected with the Application object of Excel. This is done with the code Set XlEvents.XlApp = Application in the OnConnection procedure. After the OnConnection procedure is executed, the XlApp object in the ConnectExcel code module points to the Excel Application object, and the event procedures in the ExcelEvents class module will run when the Excel events occur.

  9. Save the project, and on the Run menu, click Run or click Make Basic-COMAddIn.dll in the File menu. In the Make Project dialog box, click OK.

    10. You can either put the project in run mode or compile it into a .dll file before loading the add-in into Excel to see how the SheetSelection-Change event is handled.

  10. Start Excel, and in the COM Add-Ins dialog box, select Microsoft Office Basic COM Add-In for Excel. Click OK. Select any cell in the active worksheet.

    11. Every time you select a different cell, you'll see a message box with the text SheetSelectionChange.

  11. Click the COM Add-Ins command again. Clear the Microsoft Office Basic COM Add-In for Excel option and click OK.

Quick Guide: Setting Up an Event Procedure In a COM Add-in

You can easily set up your COM add-in to handle events from any Office application. To add code to handle events in an application, use the code structure defined in the previous example and follow this abbreviated list of steps (Word will be used here; to handle the events from another application, change the specific application where appropriate in the steps below):

Set up the WindowSelectionChange event in Word

  1. On the Project menu, click Add Class Module. In the Properties window, set the Name property to WordEvents.

  2. If a reference to the Word object library doesn't exist in your COM add-in project, add one by clicking References on the Project menu and selecting the Microsoft Word 9.0 Object Library entry in the Available References list.

  3. Add the following line of code to the top of the class module WordEvents:

    4. Public WithEvents WdApp As Word.Application

    The variable WdApp appears in the Object drop-down list in the WordEvents class module.

  4. Click WdApp in the Object drop-down list of the WordEvents class module. The events for the Word application are listed in the Procedure drop-down list.

  5. Click any event in the Procedure drop-down list to add the procedure to the WordEvents class module. For example, click WindowSelectionChange in the Procedures drop-down list and add the following code in the inserted WindowSelectionChange event procedure:

    6. MsgBox "WindowSelectionChange"

  6. In the Project Explorer window, select the ConnectWord item and click the View Code button at the top left of the Project Explorer window. Add the following line of code to the top of the ConnectWord code module:

    7. Dim WdEvents As New WordEvents

  7. In the AddinInstance_OnConnection procedure, add the following line at the end of the procedure:

    8. Set WdEvents.WdApp = Application

    After the OnConnection procedure is executed, the WdApp object in the class module points to the Word Application object, and the event procedures in the WordEvents class module will run when the Word events occur.

  8. Save the project, and then run or compile the project into a .dll file.

  9. Start Word and load the add-in through the COM Add-Ins dialog box if the add-in is not currently loaded. Type in some text and select the text using the mouse pointer, or use the arrow keys to change the selection. You'll see a message box telling you that the event procedure in the COM add-in triggered and trapped the WindowSelectionChange event in Word.

Add-Ins That Handle Office Application Events Only

Add-ins don't necessarily have to have any visible user interface, such as command bar customizations. With the addition of a number of events in Office, some add-ins are loaded when the application is started and work in the background, handling events such as documents opening, closing, or being saved. Add-ins that handle the save event of any document, for example, may ascertain whether a document has certain properties (such as whether the author or title is filled out) before it's saved. If the document doesn't, the add-ins prompt the user to add properties that are listed in the Properties dialog box, which is accessed by clicking Properties in the File menu.

In a Word document, add-ins may also handle the double-click event to determine if text was selected by the double-click, and what the text is. If the text is a company's name, for example, a dialog box may be automatically displayed. This is functionally equivalent to inserting a symbol in a Word document and then double-clicking that symbol in the text: when you double-click the symbol, the Symbol dialog box is automatically displayed.

Setting the Load Behavior to Load at Application Startup

To set the basic COM add-in to load when Word starts up, complete the following steps. Once completed, the add-in monitors the selection change event in the background, evaluating the text of the selection to see if it contains the acronym "VBA." If it does, the add-in expands the acronym to "Visual Basic for Applications." This functionality behaves like Word's AutoCorrect, but it requires the user to select first before the text is "corrected."

  1. In your COM add-in project, double-click the ConnectWord project item in the Project Explorer to make the Add-in Designer window active.

  2. In the Initial Load Behavior drop-down list, select Startup.

  3. Double-click the WordEvents item in the Project Explorer to make the code module active, and then add the following code in the Window-SelectionChange event:

    4. If Sel.Type <> wdSelectionNormal Then Exit Sub 
If UCase$(Trim$(Sel.Text)) = "VBA" Then
    Sel.Text = "Visual Basic for Applications"
End If

    The one and only argument passed into the WindowSelectionChange event in Word is the Sel argument. The Sel argument is defined as the Selection object and represents the current selection in Word. If the selection type is not normal text, the Sub procedure is exited.

    If it is text, the expression evaluated in the If…Then statement determines whether the selected text is equivalent to the acronym "VBA." The procedure makes the comparison by removing spaces from the string representing the selected text and converting the string to uppercase. If the expression is true, it changes the selected text to "Visual Basic for Applications."

  4. Add a comment to the beginning of the line MsgBox "WindowSelection-Change " so the message box doesn't appear when the selection is changed.

  5. Exit Word. On the File menu in Visual Basic, click Make BasicCOMAddIn.dll. In the Make Project dialog box, click OK.

  6. Start Word. Type in some text and insert the acronym "VBA" anywhere. Select the text using the mouse pointer or use the arrow keys while holding down the SHIFT key.

    7. Word converts the acronym "VBA" to "Visual Basic for Applications." The add-in is loaded when Word starts because the add-in is now registered to load at Word startup. The user doesn't have to explicitly load the add-in through the COM Add-Ins dialog box. As you'll see in the next section, you can use the Package and Deployment wizard to deploy your add-in onto a user's machine.

    Once the add-in is set up on the user's machine, the next time the user starts Word, the add-in is available (assuming you set the initial load behavior to be either Startup or Load at Next Startup Only). The next chapter explains the different load behaviors of a COM add-in.