Visual Studio Tools for Office: Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath

2017-07-07 02:10:07

Objects in an Office application's object model are .NET classes that have properties, methods, and events that can be accessed by solution code. An object in the object model is required to have at least one property, method, or event. Most of the objects in an Office application's object model have several properties, a few methods, and no events. The most important objects in the object model, such as Application and Document, are typically much more complex and have a much larger number of properties and methods as well as events. Word's Application object, for example, has about 100 properties, 60 methods, and 20 events. Table 1.2 lists some of the properties, methods, and events of the Word Application object to give a sense of the types of functionality an object model object provides.

Table 1.2. Selected Properties, Methods, and Events from Word's Application Object
Name	What It Does
Properties
ActiveDocument	Returns a Document object for the active documentthe document that is currently being edited by the user.
ActivePrinter	Gets and sets the default printer.
Caption	Gets and sets the caption text for the application windowtypically, this is set to "Microsoft Word."
Documents	Returns a Documents collection that represents the collection of open Word documents.
Methods
Activate	Brings Word to the front of other windows and makes it the active window.
NewWindow	Creates a new Word window that shows the same document as the active window and returns a Window object model object representing that new window.
Quit	Closes Word.
Events
DocumentBeforeClose	An event that is raised before a document is closed. The Document object for the document being closed is passed as a parameter to the event along with a `Boolean Cancel` parameter. If the code handling the event sets the `Cancel` parameter to `true`, the document will not be closed.
DocumentOpen	An event that is raised when a document is opened. The Document object for the document being opened is passed as a parameter to the event.
WindowActivate	An event that is raised when a Word window is activated by the user, typically by clicking an inactive window, thereby making it active. The Document object for the document being activated is passed as a parameter to the event along with a Window object for the window that was activated (because two windows could be showing the same document).

In Office object models, properties predominate, followed by methods and trailed distantly by events. Figure 1.3 shows the distribution of properties, methods, and events in the Word, Excel, and Outlook object models. A couple of general statements can be made about the Office object models as shown by Figure 1.3. The Excel object model is the biggest of the Office object models in terms of total number of properties, methods, and events, followed closely by Word. Word has a very small number of events. We can also say that there are many more properties in Office object models than methods.

Figure 1.3. Distribution of properties, methods, and events in the Word, Excel, and Outlook object models.

Properties

Properties are simple methods that allow you to read or write particular named values associated with an object. Word's Application object, for example, has a property called CapsLock, which returns a Boolean value. If the Caps Lock is on, it will return true; if the Caps Lock is off, it will return False. Listing 1.5 shows some code that examines this property. The code assumes that the root Application object of the Word object model has already been assigned to a variable called app.

Listing 1.5. A Property That Returns a Value Type: The Boolean CapsLock Property on Word's Application Object

If app.CapsLock Then MsgBox("CapsLock is on") Else MsgBox("CapsLock is off") End If

Another thing to note about the CapsLock property is that it is a read-only property. That is to say, you cannot write code that sets the CapsLock property to False; you can only get the value of the CapsLock property. Within the Office object model, many properties are read-only. If you try to set a read-only property to some value, an error will occur when you compile your code.

The CapsLock property returns a Boolean value. It is also possible for a property to return an enumeration. Listing 1.6 shows some code that uses the WindowState property to determine whether Word's window is maximized, minimized, or normal. This code uses Visual Basic's Select Case statement to evaluate the WindowState property and compare its value with the three possible enumerated value constants. Notice that when you specify enumerated values in Visual Basic, you must specify both the enumerated type name and the enumerated value. If you used just wdWindowStateNormal rather than Word.WdWindowState.wdWindowStateNormal, the code would not compile.

Listing 1.6. A Property That Returns an Enumeration: The WindowState Property on Word's Application Object

Select Case app.WindowState Case Word.WdWindowState.wdWindowStateMaximize MsgBox("Maximized") Case Word.WdWindowState.wdWindowStateMinimize MsgBox("Minimized") Case Word.WdWindowState.wdWindowStateNormal MsgBox("Normal") End Select

Properties can also return other object model objects. Word's Application object, for example, has a property called ActiveDocument that returns the active documentthe one the user is currently editing. The ActiveDocument property returns another object in the Word object model called Document. Document in turn also has properties, methods, and events. Listing 1.7 shows code that examines the ActiveDocument property and then displays the Name property of the Document object.

Listing 1.7. A Property That Returns Another Object Model Object: The ActiveDocument Property on Word's Application Object

Dim myDocument As Word.Document = app.ActiveDocument MsgBox(myDocument.Name)

What happens if there is no active documentif Word is running, for example, but no documents are open? In the case of the ActiveDocument property, it throws an exception. So a safer version of the preceding code would catch the exception and report that no active document was found. Listing 1.8 shows this safer version. An even better approach is to check the Count property of the Application object's Documents collection to see whether any documents are open before accessing the ActiveDocument property.

Listing 1.8. A Property That Might Throw an Exception: The ActiveDocument Property on Word's Application Object

Dim myDocument As Word.Document Try myDocument = app.ActiveDocument MsgBox(myDocument.Name) Catch ex As Exception MsgBox(String.Format("No active document: {0}", ex.Message) End Try

Object models sometimes behave differently in an error case in which the object you are asking for is not available or does not make sense in a particular context. The property can return a Nothing value. The way to determine whether an object model property will throw an exception or return a Nothing value is to consult the object model documentation for the property in question. Excel's Application object uses this pattern for its ActiveWorkbook property. If no Excel workbook is open, it returns Nothing instead of throwing an exception. Listing 1.9 shows how to write code that handles this pattern of behavior.

Listing 1.9. A Property That Might Return Nothing: The ActiveWorkbook Property on Excel's Application Object

Dim myWorkbook As Excel.Workbook = app.ActiveWorkbook If myWorkbook Is Nothing Then MsgBox("No active workbook") Else MsgBox(myWorkbook.Name) End If

Parameterized Properties

The properties examined so far are parameterless. Some properties, however, require parameters. Word's Application object, for example, has a property called FileDialog that returns a FileDialog object. The FileDialog property takes an enumeration parameter of type MsoFileDialogType, which is used to pick which FileDialog is returned. Its possible values are msoFileDialogOpen, msoFileDialogSaveAs, msoFileDialogFilePicker, and msoFileDialogFolderPicker.

The code in Listing 1.10 uses the FileDialog property and passes msoFileDialogFilePicker as a parameter to specify the type of FileDialog object to be returned. Then it calls a method on the returned FileDialog object to show the dialog box.

Listing 1.10. A Parameterized Property That Takes an Enumeration Parameter and Returns an Object Model Object: The FileDialog Property on Word's Application Object

Dim dialog As Office.FileDialog dialog = app.FileDialog(Office.MsoFileDialogType. _ msoFileDialogFilePicker) dialog.Show()

The Office object models also have properties that have optional parameters. Optional parameters are parameters that can be omitted, and the Office application will fill in a default value for the parameter. Optional parameters are typically of type Object because of how optional parameters are passed to the underlying COM API. In Visual Basic you can omit optional parameters, or you can specify some optional parameters and omit others. To omit a particular optional parameter, you just leave the parameter out. Given a property on app called SomeProp that takes three optional Integer parameters named A, B, and C, you can call it in any of these ways:

app.SomeProp 'omit all optional parameters app.SomeProp(5) 'omit second and third parameters app.SomeProp(, 8) 'omit first and third parameters app.SomeProp(, ,12) 'omit first and second parameters app.SomeProp(, 13, 7) 'omit first optional parameter app.SomeProp(11, ,25) 'omit second optional parameter app.SomeProp(44, 6) 'omit third optional parameter app.SomeProp(12, 19, 31) 'specify all optional parameters

It is also possible to use the name of the parameter to specify the optional parameters you wish to supply. Remember that the names of the three optional Integer parameters in our example are A, B, and C. You can call SomeProp in any of the ways shown below. When using the name of the parameter, the parameters can be specified in any order.

app.SomeProp(C:=10) 'specify the third parameter named C app.SomeProp(B:=4, A:=15) 'specify the parameters B and A app.SomeProp(A:=1, C:=4, B:=11) 'specify all optional parameters

Finally, you can mix the ordered-parameter and named-parameter approaches:

app.SomeProp(5, C:=10) 'specify first and C parameters

Listing 1.11 shows an example of calling a parameterized property called Range, which is found on Excel's Application object. Calling the Range property on Excel's Application object returns the Range object in the active workbook as specified by the parameters passed to the property. The Range property takes two parameters. The first parameter is required, and the second parameter is optional. If you want to specify a single cell, you just pass the first parameter. If you want to specify multiple cells, you have to specify the top-left cell in the first parameter and the bottom-right cell in the second parameter.

Listing 1.11. A Parameterized Property with an Optional Parameter: The Range Property on Excel's Application Object

' Omit the optional second parameter Dim myRange As Excel.Range = app.Range("A1") ' Specify the optional second parameter Dim myRange2 As Excel.Range = app.Range("A1", "B2")

Properties Common to Most Objects

Because all the object model objects have Object as their base class, you will always find the methods GetType, GetHashCode, Equals, and ToString on every object model object. You will also often find a property called Application that will return the Application object associated with the object. This is provided as a quick way to get back to the root of the object model. Many objects have a property called Creator, which gives you a code indicating which application the object was created in. Finally, you will often find a Parent property that returns the object that is the parent in the object model hierarchy.

Default Parameterized Properties

Earlier in this chapter, we presented this code as a simple way of navigating the object hierarchy of Excel to get a Worksheet object:

Dim myWorksheet As Excel.Worksheet myWorksheet = CType(app.Workbooks.Item(1).Worksheets.Item(1), _ Excel.Worksheet)

There is an even simpler way to write this code. It can be rewritten like this:

Dim myWorksheet As Excel.Worksheet myWorksheet = CType(app.Workbooks(1).Worksheets(1), _ Excel.Worksheet)

In this more efficient example, the code uses a feature of Visual Basic called default properties that makes the code a little simpler. Many collections and even some objects in the Office object models have a parameterized property designated as the default property. The most common use of a default property is with a collection such as Workbooks or Worksheets where the default property is the Item property of the collection, which takes a parameter specifying which item in the collection you want returned. A default property must have at least one parameter. Default properties allow the code to omit specifying the Item property and instead just pass the parameters. When the property name is omitted in this way, Visual Basic will call the default property with the specified parameters. Therefore, these two lines of code are equivalent. In the first line, the Item property is explicitly called. In the second line, the Item property is implicitly called, as Item is the default property specified by the object that Visual Basic uses when it is omitted.

app.Workbooks.Item(1).Worksheets.Item(1) app.Workbooks(1).Worksheets(1)

You can inspect an object or collection using the object browser of Visual Studio to determine whether it has a default property. The default property always has the special name of "_Default" and is an alias to the parameterized property of the object that has been marked as the default property.

Methods

A method is typically more complex than a property and represents a "verb" on the object that causes something to happen. It may or may not have a return value and is more likely to have parameters than a property.

The simplest form of a method has no return type and no parameters. Listing 1.12 shows the use of the Activate method from Word's Application object. This method activates the Word application, making its window the active window (the equivalent of clicking the Word window in the taskbar to activate it).

Listing 1.12. A Method with No Parameters and No Return Type: The Activate Method on Word's Application Object

MsgBox("Activating the Word window.") app.Activate()

Methods may also have parameters and no return type. Listing 1.13 shows an example of this kind of method. The ChangeFileOpenDirectory method takes a String that is the name of the directory you want Word to default to when the Open dialog box is shown. For a method this simple, you might wonder why a property was not used instead; you can imagine Word's having a FileOpenDirectory property, for example. In this case, the ChangeFileOpenDirectory changes the default open directory only temporarilyfor the lifetime of the current Word session. When you exit Word and then restart Word, the default will no longer be what you set with this method. Perhaps for this reason, this functionality was exposed via a method rather than a property. A second reason why object models sometimes use a simple method such as this rather than a property is because some values exposed in an object model are "write-only"that is, they can be set but cannot be read. It is common to create a read-only property but not common to create a write-only property. So when a write-only property is needed, a simple method is often used instead.

Listing 1.13. A Method with Parameters and No Return Type: The ChangeFileOpenDirectory Method on Word's Application Object

app.ChangeFileOpenDirectory("c:\temp") MsgBox("Will open out of temp for this session.")

Methods can have no parameters and a return type. Listing 1.14 shows an example of this kind of method. The DefaultWebOptions method returns the DefaultWebOptions object, which is then used to set options for Word's Web features. In this case, DefaultWebOptions really should have been implemented as a read-only property as opposed to a method.

Listing 1.14. A Method with No Parameters and a Return Type: The DefaultWebOptions Method on Word's Application Object

Dim options As Word.DefaultWebOptions = app.DefaultWebOptions() MsgBox(String.Format("Pixels per inch is {0}.", _ options.PixelsPerInch))

Methods can have parameters and a return type. Listing 1.15 shows an example of this kind of method. The CentimetersToPoints method takes a centimeter value and converts it to points, which it returns as the return value of the method. Points is a unit often used by Word when specifying spacing in the document.

Listing 1.15. A Method with Parameters and a Return Type: The CentimetersToPoints Method on Word's Application Object

Dim centimeters As Single = 15.0 Dim points As Single = app.CentimetersToPoints(centimeters) MsgBox(String.Format("{0} centimeters is {1} points.", _ centimeters, points))

Methods can also have optional parameters. Optional parameters do not need to be specified directly to call the method. You can omit any parameters you do not want to specify. Listing 1.16 shows a method called CheckSpelling in Excel that has optional parameters. Listing 1.16 illustrates the syntax you use to omit parameters you do not want to specify. The CheckSpelling method takes a required Stringthe word you want to check the spelling ofalong with two optional parameters. The first optional parameter enables you to pick a custom dictionary to check the spelling against. The second optional parameter enables you to tell the spell checker to ignore words in all uppercasesuch as an acronym. In Listing 1.16, we check a phrase without specifying any of the optional parameters; we omit the optional parameters. We also check a second phrase that has an acronym in all uppercase, so we omit the first optional parameter because we do not want to use a custom dictionary, but we specify the second optional parameter to be true so the spell checker will ignore the words in all uppercase.

Listing 1.16. A Method with Optional Parameters and a Return Type: The CheckSpelling Method on Excel's Application Object

Dim phrase1 As String = "Thes is spelled correctly." Dim phrase2 As String = "This is spelled correctly AFAIK." Dim isCorrect1 As Boolean = app.CheckSpelling(phrase1) Dim isCorrect2 As Boolean = app.CheckSpelling(phrase2, , True)

Events

You have now read about the use of properties and methods in some detail; these are both ways that your code controls the Office application. Events are the way the Office application talks back to your code and enables you to run additional code in response to some condition that occurred in the Office application.

In the Office object models, there are far fewer events than there are methods and properties. There are 36 events in Word and 84 in Excel, for example. Some of these events are duplicated on different objects. When the user opens a Word document, both the Application object and the newly created Document object raise Open events. If you wanted to handle all Open events on all documents, you would handle the Open event on the Application object. If you had code associated with a particular document, you would handle the Open event on the corresponding Document object.

In most of the Office object models, events are raised by a handful of objects. The only objects that raise events in the Word object model are Application, Document, and OLEControl. The only objects that raise events in the Excel object model are Application, Workbook, Worksheet, Chart, OLEObject, and QueryTable. Outlook is a bit of an exception: About half of the objects in the Outlook object model raise events. Most of these objects raise the same set of events, however, making the total number of unique events small in Outlook as well.

Table 1.3 shows all the events raised by Excel's Application object. This table represents almost all the events raised by Excel, because events prefaced by Sheet are duplicated on Excel's Worksheet object, and events prefaced by Workbook are duplicated on Excel's Workbook object. The only difference in these duplicated events is that the Application-level Sheet and Workbook events pass a parameter of type Sheet or Workbook to indicate which worksheet or workbook raised the event. Events raised by a Workbook object or Sheet object do not have to pass the Sheet or Workbook parameter, because it is implicitly determined from which Workbook or Sheet object you are handling events for.

Table 1.3. Events Raised by Excel's Application Object
Event Name	When It Is Raised
NewWorkbook	When a new workbook is created
SheetActivate	When any worksheet is activated
SheetBeforeDoubleClick	When any worksheet is double-clicked
SheetBeforeRightClick	When any worksheet is right-clicked
SheetCalculate	After any worksheet is recalculated
SheetChange	When cells in any worksheet are changed by the user
SheetDeactivate	When any worksheet is deactivated
SheetFollowHyperlink	When the user clicks a hyperlink in any worksheet
SheetPivotTableUpdate	After the sheet of a PivotTable report has been updated
SheetSelectionChange	When the selection changes on any worksheet
WindowActivate	When any workbook window is activated
WindowDeactivate	When any workbook window is deactivated
WindowResize	When any workbook window is resized
WorkbookActivate	When any workbook is activated
WorkbookAddinInstall	When any workbook is installed as an add-in
WorkbookAddinUninstall	When any workbook is uninstalled as an add-in
WorkbookAfterXmlExport	After data in a workbook is exported as an XML data file
WorkbookAfterXmlImport	After data in a workbook is imported from an XML data file
WorkbookBeforeClose	Before any workbook is closed
WorkbookBeforePrint	Before any workbook is printed
WorkbookBeforeSave	Before any workbook is saved
WorkbookBeforeXmlExport	Before data in any workbook is exported as an XML data file
WorkbookBeforeXmlImport	Before data in any workbook is imported from an XML data file
WorkbookDeactivate	When any workbook window is deactivated
WorkbookNewSheet	When a new worksheet is created in any workbook
WorkbookOpen	When any workbook is opened
WorkbookPivotTableCloseConnection	After a PivotTable report connection has been closed
WorkbookPivotTableOpenConnection	After a PivotTable report connection has been opened
WorkbookSync	When a workbook that is part of a document workspace is synchronized with a copy on the server

Declarative Event Handling

Visual Basic provides two ways to handle events. The first way is to handle an event declaratively. Consider the Excel Application object, which has the events described in Table 1.3. By declaring an instance of the Excel Application object using the WithEvents keyword, you tell the Visual Basic compiler that it is an object that can raise events:

Public WithEvents app As Excel.Application

When you have declared the Excel Application object as an object that can raise events, you can declare a method that handles an event raised by the Excel application object. To handle the events raised by Office object models, you must first declare a handler method in your code that matches the signature expected by the event being raised. The WindowActivate event raised by the Application object in Excel expects a handler method to match this signature:

Event WindowActivate( _ ByVal Wb As Excel.Workbook, _ ByVal Wn As Excel.Window)

To handle this event, you must declare a handler method in your code that matches the expected signature. Below, we declare a method called app_WindowActivate that handles the WindowActivate event. Note that the Event keyword shown in the signature above is omitted in our handler method because we are not defining a new event typejust implementing an existing one defined by the Office object model. The declaration includes a Handles clause that gives the name of the object instance raising the event (app) and the event that is being handled (WindowActivate).

Private Sub app_WindowActivate(ByVal Wb As Excel.Workbook, _ ByVal Wn As Excel.Window) Handles app.WindowActivate MsgBox("The window " & Wn.Caption & " was just activated.") End Sub

The one piece we are missing is some code to set the app object to an instance of the Excel Application object. Listing 1.17 shows a VSTO customization that puts it all together. VSTO provides code items where you can write code that accesses member variables that are connected to objects in the Office object model. VSTO raises the Sheet1_Startup event automatically. We have added some code to this event handler to set the app member variable we have declared, using the WithEvents keyword to an instance of the Excel Application object. In this case, the code uses a property in the base class of Sheet1 called Application to get an instance of the Excel Application object. With app set to an instance of the Excel Application object, the event handler app_WindowActivate is called whenever a window is activated within Excel.

Listing 1.17. A VSTO Customization That Handles the Excel Application Object's WindowActivate Event

Public Class Sheet1 Public WithEvents app As Excel.Application Private Sub app_WindowActivate(ByVal Wb As Excel.Workbook, _ ByVal Wn As Excel.Window) Handles app.WindowActivate MsgBox("The window " & Wn.Caption & " was just activated.") End Sub Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup app = Me.Application End Sub End Class

Visual Studio helps make this process simpler by generating the event handler method for you automatically. When you declare a member variable such as app with the WithEvents keyword, Visual Studio lists that variable in the left drop-down list above the code-editing window, as shown in Figure 1.4.

Figure 1.4. Selecting a member variable declared using `WithEvents`.

When you have selected app in the left drop-down list, drop down the list on the right to pick the event you want to handle. In Figure 1.5, all events raised by the app variable are displayed, and we select the WindowActivate event as the event we want to handle.

Figure 1.5. Selecting an event to handle for a member variable declared using `WithEvents`.

When you select the WindowActivate event, Visual Studio will automatically generate a handler for that event, or if a handler has already been created, it will move the cursor to that handler. This greatly simplifies the process of writing handlers for events.

Advanced Topic: Handling Events Dynamically

There is a second way to handle events when declarative event handling falls short. You may want to handle an event for a short time and then unregister your event handler so it does not get called again. Although it is not possible to do this declaratively, it is possible to do it dynamically using Visual Basic's AddHandler and RemoveHandler statements.

The AddHandler and RemoveHandler statements are passed the event to be handled and the event handler method that will handle the event. The AddressOf keyword is used when specifying the event handler method. The following code uses AddHandler to add dynamically the event handler MyWindowActivateHandler to handle the app object's WindowActivate event:

AddHandler app.WindowActivate, _ AddressOf Me.MyWindowActivateHandler

At some point later in the execution of the code, you can use the RemoveHandler statement to remove the event handler method from handling the event by using an almost-identical syntax:

RemoveHandler app.WindowActivate, _ AddressOf Me.MyWindowActivateHandler

As with dynamic event handlers, the event handler signature must match the expected signature of the event. The Handles keyword, however, is not used in the event handler signature when you are handling events dynamically. So the dynamic event handler for the WindowActivate event looks like the declarative event handler but omits the Handles clause:

Private Sub app_WindowActivate(ByVal Wb As Excel.Workbook, _ ByVal Wn As Excel.Window) MsgBox("The window " & Wn.Caption & " was just activated.") End Sub

Listing 1.18 shows a complete implementation of dynamic event handling in a simple VSTO customization class. The code declares an app variable as in Listing 1.17 but does not use the WithEvents keyword. Sheet1_Startup is called automatically when the VSTO customization class starts up. In the Sheet1_Startup method, we set app to an instance of the Excel Application object as before. Then the AddHandler statement dynamically adds the event handler method MyWindowActivateHandler to handle the WindowActivate event raised by the app variable. In the event handler, after displaying a message box showing the name of the window that was activated, the RemoveHandler statement dynamically removes the event handler from handling future WindowActivate events.

Listing 1.18. A VSTO Customization That Dynamically Adds and Removes an Event Handler for the Excel Application Object's WindowActivate Event

Public Class Sheet1 Public app As Excel.Application Private Sub MyWindowActivateHandler(ByVal Wb As _ Excel.Workbook, ByVal Wn As Excel.Window) MsgBox("The window " & Wn.Caption & " was just activated.") RemoveHandler app.WindowActivate, _ AddressOf Me.MyWindowActivateHandler End Sub Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup app = Me.Application AddHandler app.WindowActivate, _ AddressOf Me.MyWindowActivateHandler End Sub End Class

The "My Button Stopped Working" Issue

One issue commonly encountered when dynamically handling Office events in .NET is known as the "my button stopped working" issue. A developer will write some code to handle a Click event raised by a CommandBarButton in the Office toolbar object model. This code will sometimes work temporarily but then stop. The user will click the button, but the Click event appears to have stopped working.

The cause of this issue is connecting an event handler method to an object whose lifetime does not match the desired lifetime of the event. This typically occurs when the object to which you are connecting an event handler goes out of scope or gets set to Nothing so that it gets garbage collected. Listing 1.19 shows an example of code that makes this mistake. In this case, an event handler called btn_Click is connected to a newly created CommandBarButton called btn. btn is declared as a local variable, however, so as soon as the ConnectEvents function exits and garbage collection occurs, btn gets garbage-collected, and the event connected to btn is not called.

The complete explanation of this behavior has to do with btn being associated with something called a Runtime Callable Wrapper (RCW), which is described in Chapter 24, "Creating Outlook Add-Ins with VSTO." Without going into too much depth, btn holds on to an RCW that is necessary for the event to propagate from the unmanaged Office COM object to the managed event handler. When btn goes out of scope and is garbage-collected, the reference count on the RCW goes down, and the RCW is disposedthereby breaking the event connection.

Listing 1.19. A Class That Fails to Handle the CommandBarButton Click Event

Imports Excel = Microsoft.Office.Interop.Excel Imports Office = Microsoft.Office.Core Class SampleListener Private app As Excel.Application Public Sub New(ByVal application As Excel.Application) app = application End Sub ' This appears to connect to the Click event but ' will fail because btn is not put in a more permanent ' variable. Public Sub ConnectEvents() Dim bar As Office.CommandBar = app.CommandBars("Standard") Dim btn As Office.CommandBarButton = bar.Controls.Add(1) If btn IsNot Nothing Then btn.Caption = "My Button" btn.Tag = "SampleListener.btn" AddHandler btn.Click, AddressOf Me.btn_Click End If End Sub ' The Click event will never reach this handler. Public Sub btn_Click(ByVal ctrl As Office.CommandBarButton, _ ByRef cancelDefault As Boolean) MessageBox.Show("Button was clicked") End Sub End Class

Listing 1.20 shows a second example of a failed event listener class that is attempting to connect to Outlook's NewInspector event, which is raised by Outlook's Inspectors object. This event is raised whenever an inspector window opens (a window where you are viewing or editing an Outlook item). This code will also fail to handle any events. In this case, it is more subtle because the event handler is connected to the Inspectors object, which is temporarily created in the line of code that begins with app.Inspectors. Because the Inspectors object returned by app.Inspectors is not stored in a permanent variable, the temporarily created Inspectors object is garbage-collected, and the event connected to it will never get called.

Listing 1.20. A Class That Fails to Handle the Outlook Inspectors Object's NewInspectorEvent

[View full width]

Imports Outlook = Microsoft.Office.Interop.Outlook Class SampleListener Private app As Outlook.Application Public Sub New(ByVal application As Outlook.Application) app = application End Sub ' This will appear to connect to the NewInspector event, but ' will fail because Inspectors is not put in a more permanent ' variable. Public Sub ConnectEvents() AddHandler app.Inspectors.NewInspector, _ AddressOf Me.MyNewInspectorHandler End Sub ' The NewInspector event will never reach this handler. Public Sub MyNewInspectorHandler(ByVal inspector As Outlook .Inspector) MessageBox.Show("New inspector.") End Sub End Class

The fix for this issue is to declare a variable whose lifetime matches the lifetime of your event handler and set it to the Office object for which you are handling the event. Listing 1.21 shows a rewritten class that successfully listens to the CommandBarButton Click event. This class works because instead of using the method-scoped variable btn, it uses a class-scoped member variable called myBtn. This ensures that the event handler will be connected for the lifetime of the instance when ConnectEvents is called.

Listing 1.21. A Class That Succeeds in Handling the CommandBarButton Click Event Because It Stores the CommandBarButton Object in a Class Member Variable

Imports Excel = Microsoft.Office.Interop.Excel Imports Office = Microsoft.Office.Core Class SampleListener Private app As Excel.Application Private myBtn As Office.CommandBarButton Public Sub New(ByVal application As Excel.Application) app = application End Sub Public Sub ConnectEvents() Dim bar As Office.CommandBar = app.CommandBars("Standard") myBtn = bar.Controls.Add(1) If myBtn IsNot Nothing Then myBtn.Caption = "My Button" myBtn.Tag = "SampleListener.btn" AddHandler myBtn.Click, AddressOf Me.myBtn_Click End If End Sub Public Sub myBtn_Click(ByVal ctrl As Office.CommandBarButton, _ ByRef cancelDefault As Boolean) MessageBox.Show("Button was clicked") End Sub End Class

Listing 1.22 shows a similar fix for our failed Outlook example. Here, we declare a class-level variable called myInspectors that we assign to app.Inspectors. This ensures that our event handler will be connected for the lifetime of the instance when ConnectEvents is called because the lifetime of myInspectors now matches the lifetime of the instance.

Listing 1.22. A Class That Succeeds in Handling the Outlook Inspectors Object's NewInspector Event Because It Stores the Inspectors Object in a Class Member Variable

Imports Outlook = Microsoft.Office.Interop.Outlook Class SampleListener Private app As Outlook.Application Private myInspectors As Outlook.Inspectors Public Sub New(ByVal application As Outlook.Application) app = application End Sub Public Sub ConnectEvents() myInspectors = app.Inspectors AddHandler myInspectors.NewInspector, _ AddressOf Me.MyNewInspectorHandler End Sub Public Sub MyNewInspectorHandler( _ ByVal inspector As Outlook.Inspector) MessageBox.Show("New inspector.") End Sub End Class

Table 1.2. Selected Properties, Methods, and Events from Word's Application Object