This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

VBtech

COM Add-ins: Part II

Coding, Debugging, Deployment, etc.

By Ken Getz

In Part I, I related a short history of the COM add-in, and demonstrated why the Office 2000 version is far superior to its Office 97 predecessor. I also introduced the COM add-in designer, and described why Visual Basic 6 makes a particularly good environment for developing COM add-ins.

In this installment, we'll delve into the coding requirements, including the necessary methods and event handlers. We'll also discuss debugging techniques and deployment issues, so by the end of the article you'll have all the information necessary to create your own COM add-ins.

Coding the Methods

Because all COM add-ins for Office 2000 must implement the IDTExtensibility2 interface, the COM add-in designer takes care of the details for you. In its class module, you supply code for any or all of the interface's five methods described in FIGURE 1. Depending on the needs of your add-in, you'll normally provide code for at least the OnConnection and OnDisconnection methods.

FIGURE 1: Methods required by the IDTExtensibility2 interface.

FIGURE 2: Parameters sent to the OnConnection method.

Normally, when you implement an interface in VB, you must supply code (at least a comment) for each of the members of the base interface. In this case, because the designer implements the interface for you, and you're simply handling events passed through from the designer into your code, all you need to do is supply code for the methods that are necessary to your add-in. In both example add-ins, you only need to supply code for the OnConnection and OnDisconnection methods. (Although these aren't actually event procedures, they look and act as if they are. Therefore, these implementation-specific methods will be referred to as event procedures here.)

Adding Code to Event Procedures

In the OnConnection event procedure, you'll usually want to accomplish specific goals. You'll need to:

• Store the Application object passed into the method into some sort of global variable, so you can refer to it later.
• Store the AppInstance object passed into the method. You'll need its ProgID property later on, if you're attaching your add-in to a CommandBar button.
• Set up some sort of event hook. Whether you want your add-in to be loaded because the user selects a menu item, or in reaction to some event (perhaps a piece of mail arriving in Outlook), you'll need to hook up the event here, so your code can run later in reaction to the event.

The sample project, InsertName.vbp, includes code that takes care of all these things. First, it sets up a WithEvents Office.CommandBarButton variable (the WithEvents keyword is required so you can react to the Click event of this object from within your add-in):

Private WithEvents mcbb As Office.CommandBarButton

Then, in the OnConnection event procedure, InsertName.vbp hooks up all the necessary variables, and calls the code that creates the CommandBar button, as shown in FIGURE 3.

Private Sub AddInInstance_OnConnection( _
   ByVal Application As Object,
ByVal ConnectMode As _
  AddInDesignerObjects.ext_ConnectMode, _
   ByVal AddInInst As Object, custom()As Variant)
      ' Save reference to host. 
   Set pobjHost = Application
   Set pobjCommandBarHost = Application
   Set pobjAddIn = AddInInst
   ' Set up module-level CommandBar item. 
   Set mcbb = AddToCommandBar()
End Sub

FIGURE 3: Creating the CommandBar button.

Why does the code store the passed-in Application object into two separate project-global variables (both defined by the project template in the basSharedCode module)? To be as generic as possible, the template includes code that allows you to work with the host's Application object (if you want to manipulate objects within the application, you'll need this reference), and code that allows you to manipulate CommandBars within the host. For most applications, the top-level Application object provides both these capabilities. In Outlook, however, the Application object doesn't supply any means of getting to the CommandBars collection, and there's no reason why any specific VBA-host application should either. (In Outlook, you can use the Application.ActiveExplorer as one way to work with CommandBar objects. Other applications may provide a different object altogether.) In an Outlook add-in, for example, you might modify the code in FIGURE 3 to store the two objects, as shown in FIGURE 4.

' Code from basSharedCode: For all apps but Outlook, the
' Application object hosts the CommandBars collection. In
' Outlook, the Explorer (or Inspector) object hosts the
' CommandBars. So, for most applications, pobjHost and 
' pobjCommandBarHost will refer to the same object, the 
' Application object. 
Public pobjCommandBarHost As Object
Public pobjHost As Object
Public pobjAddIn As Object
  ' Code from the application-specific designer module. 
Set pobjHost = Application
Set pobjCommandBarHost = Application.ActiveExplorer
Set pobjAddIn = AddInInst

FIGURE 4: Modifying the code for Outlook.

This is exactly what the Outlook add-in in the sample project does. As mentioned earlier, you need to store the AddInInst parameter's value so you can retrieve the ProgID property of the object later, if you connect a CommandBarButton object to load your add-in. If you choose some other mechanism to load your add-in (other than the CommandBar button used here), you need to replace the call to the AddToCommandBar method with one of your own design. (The AddToCommandBar method appears in the shared module in the template basSharedCode.)

In the OnDisconnection event procedure, your code needs to determine why your add-in has been disconnected. (If the application is shutting down, there's no need to remove your CommandBar item. If the user has unselected your add-in from the list of available add-ins, however, your menu item has to go.) In the sample project, all the add-ins call the same code, as shown in FIGURE 5.

Private Sub AddInInstance_OnDisconnection(ByVal _
  RemoveMode As AddInDesignerObjects.ext_DisconnectMode, _
  custom()As Variant)
     If RemoveMode = ext_dm_UserClosed Then
     Call DeleteFromCommandBar()
   End If
End Sub

FIGURE 5: OnDisconnection event procedure.

In this case, if the add-in is being disconnected because the user chose to remove the add-in, the code calls the shared DeleteFromCommandBar procedure (see basSharedCode in the sample project). The sample project is available for download; see end of article for details.

The AddToCommandBar method (in basSharedCode in the template project) provides simple brute force code to add a CommandBarButton object to an Office command bar. The interesting part comes after adding the control: in order for the menu item to load the COM add-in for you, it must know the ProgID for the add-in itself. If the OnAction property of a CommandBarButton object includes "!<ProgID>", where ProgID is the ProgID for a specific add-in, clicking the menu item will cause the host application to load the add-in (if you've selected the Load on demand startup option) and call the Click event procedure for the menu item as well. In addition, Microsoft Word won't take any action unless you've also set the Tag property of the menu item. To make the code generic for all hosts, it sets the Tag property no matter which host is active. The DeleteFromCommandBar procedure simply removes the CommandBar button from its parent command bar. Listing One includes both of these procedures, provided as starting points for your own add-ins in the included project template.

What do you do when the user clicks the add-in's menu item within the host? That's up to your specific needs. The InsertName sample add-in loads a form, and waits for the form to be dismissed. If the form wasn't cancelled (that is, the user selected the OK button), the code for each separate host reacts to the data entered into the form, provided as a property that returns an array of strings, appropriately for the host application. The Word add-in creates paragraphs containing the formatted information. The Excel add-in inserts the data into a vertical group of cells starting at the current location. The Outlook add-in creates a new contact, containing the data entered into the form. In each case, the add-in's code uses objects provided by the host application's Application object to do its work. FIGURE 6 shows the code from the Word add-in.

Private Sub mcbb_Click( _
   ByVal cbb As Office.CommandBarButton, _
  CancelDefault As Boolean)
     Dim wrdApp As Word.Application
   Dim strOut As String
   Dim strInfo()As String
   frmInsertAddress.Show vbModal
   If Not frmInsertAddress.Cancelled Then
     ' Cast global objHost as Word.Application object. 
     Set wrdApp = pobjHost
    astrInfo = frmInsertAddress.AddressInfo
    strOut = astrInfo(dfFirstName) &" " & _
      astrInfo(dfLastName) & vbCrLf &_
      astrInfo(dfAddress) & vbCrLf & _
      astrInfo(dfCity) & " " & _
      astrInfo(dfState) & " "& _
      astrInfo(dfZip) & vbCrLf
     With wrdApp.Selection
      .Range.InsertAfter strOut
      .MoveEnd wdParagraph, 4
      .Collapse wdCollapseEnd
     End With
   End If
   Set frmInsertAddress = Nothing
End Sub

FIGURE 6: Each of the sample add-ins use code like this to work with the supplied data.

Determining the Host

Sometimes, you'll need to alter behavior of a common component in an add-in project based on the current host application. Because your add-in always receives a reference to the host application (and the template stores this information in a project-global variable of type Object), your code can use the If TypeOf construct to determine the exact object type. For example, the InsertName sample application's form, frmInsertAddress, displays an e-mail textbox if the host application is Outlook, and hides the control otherwise. You could use code in the form to make the decision, like this:

blnOutlook = False
If TypeOf pobjHost Is Outlook.Application Then
  blnOutlook = True
End If

All of the Office applications' Application objects also supply a Name property, so you could modify the code to use that property:

blnOutlook = (pobjHost.Name = "Outlook") 

You can't count on all applications that host COM add-ins to provide this property, but all the Office applications do. If you check, you'll find that all the Office 2000 applications fill their Name properties with "Microsoft Excel," "Microsoft Word," and so on, except Outlook, which returns simply "Outlook." (Apparently, all the Office applications besides Outlook have Microsoft as part of their registered trademarks. So much for product consistency.)

Debugging COM Add-ins

Unless you're extremely lucky, you'll need to spend some time single-stepping through your add-in's code to ensure that it works correctly. Fortunately, debugging ActiveX components with VB is straightforward. You can simply run the project from within the VB environment, set a breakpoint at the desired spot, and run the add-in from within the host environment. When you hit the breakpoint, set the focus back to the VB IDE, and debug away. However, there are a few things you should consider:

• The first time you run the add-in this way, VB displays a dialog box asking how you want to start the add-in. Accept the default option, Wait for components to be created.
• If your add-in displays a form, debugging can get complicated. If you set your breakpoint before the add-in attempts to display the form, you should be able to switch to VB and continue debugging. If you set a breakpoint after the form displays, or if you don't set a breakpoint at all, it's likely you'll never see the form, and VB will appear to hang. To work around this, switch back to the VB IDE, and press [Ctrl]k to drop into Break mode. You'll find yourself at a breakpoint on the line of code that displays the form. You should then be able to continue debugging normally.
• As when running a COM add-in in any host, you'll need to make sure you completely restart the host application before attempting to debug the application. Otherwise, your host app will either not see the add-in, or will use the last version you had compiled.

Deploying Your Add-ins

How do the various host applications know what add-ins are available, and how to use them? Each product looks in the registry at HKEY_CURRENT_USER\Software\Microsoft\Office\<AppName>\Addins, as shown in FIGURE 7, for information about available add-ins. Of course, VB places these registry settings into your own registry when you compile the add-in on your development machine. You'll need some way to get the add-in onto users' machines, registered, and the correct values into the registry.

FIGURE 7: After installing an add-in, the registry includes information about the various properties you set in the add-in designer.

Just as with any other ActiveX component, COM add-ins include the DLLRegisterServer entry point (and the corresponding DLLUnregisterServer entry point for removing the registry entries). This procedure includes all the information it needs so that a call to REGSVR32 (for DLLs), or running the component from the command line (for EXEs), will set everything up correctly. Therefore, you can use any tool that creates distribution media, as long as it understands how to install ActiveX components. The Package and Deployment Wizard that comes with VB should work fine.

Conclusion

Rather than learning add-in technologies for each individual product for which you need to create add-ins, use COM add-ins to create cross-product tools for any or all of the Microsoft Office 2000 applications. Creating the add-ins requires VB, or Office 2000 Developer. VB however, provides you with additional help, including a template filled in with much of the code you'll need. You simply create a standard ActiveX DLL or EXE, implement the required interface, and set up the necessary registry keys, and you've got an add-in that can work in multiple applications.

Ken Getz (mailto: KenG@mcwtech.com) is a Senior Consultant with MCW Technologies, a Microsoft Certified Solution Provider focusing on Visual Basic and the Office and BackOffice suites. He is also co-author of Visual Basic Language Developer's Handbook (with Mike Gilbert) [SYBEX, 2000], and Access 2000 Developer's Handbook (with Gilbert and Paul Litwin) [SYBEX, 1999]. He's recently completed a series of VB6 training tapes, and a series of Access 97 tapes (with co-author Paul Litwin) for Application Developers Training Company (http://www.appdev.com/video).

IDE Add-ins

You might also find it interesting to dig into writing add-ins for the VBA IDE, and I've provided a simple one along with the examples for this article. The provided WebBrowser add-in allows you to create a dockable window in the IDE. If you're interested, you'll need to investigate the VBA IDE extensibility object model, and the use of the CreateToolWindow method that allows you to create dockable windows. The issues involved in creating add-ins for the VBA IDE are similar to those you encounter when writing add-ins for the Office products, except for these points:

• You should use the AddIn project template project from within VB.
• In your add-in's designer, select Visual Basic for Applications IDE as the host application.
• Remove the reference to the Microsoft Visual Basic 6.0 Extensibility type library, and add a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 type library. (Yes, although VB and VBA share most of the same objects in their extensibility models, they use different type libraries.)

Once you've set things up correctly, you're ready to write an add-in, such as the WebBrowser add-in available for the VBA IDE. To load your add-in once you've compiled it, load the VBA IDE, then select Add-Ins | Add-In Manager. You'll see your new add-in there, and selecting the Loaded/Unloaded checkbox loads the add-in for this session. (The WebBrowser add-in loads automatically once you've done this. If you close the window, use the Add-Ins | Web Browser menu item to display it again.)

- Ken Getz

Begin Listing One - AddToCommandBar, DeleteFromCommandBar

Public Function AddToCommandBar( _
   Optional intPosition As Integer = 0) _
   As Office.CommandBarButton
     Dim cbb As Office.CommandBarButton
   Dim cbr As Office.CommandBar
     On Error Resume Next
     ' See if you can find correct menu. Note: most apps don't
  ' require the .Item, but Outlook does. 
   Set cbr = _
    pobjCommandBarHost.CommandBars.Item(conMenuName) 
   If cbr Is Nothing Then
     ' Not available so you fail. 
     Exit Function
   End If
   ' Try to retrieve reference to existing 
  ' CommandBar control. 
   Set cbb = cbr.FindControl(Tag:=conMenuTag) 
   If cbb Is Nothing Then
     ' CommandBar button isn't there. Add it to CommandBar. 
     If intPosition = 0 Then
       ' Add to the end. 
       Set cbb = cbr.Controls.Add(Type:=msoControlButton) 
     Else
       Set cbb = cbr.Controls.Add(Type:=msoControlButton, _
                                  Before:=intPosition) 
     End If
     ' Set the caption. 
    cbb.Caption = conMenuText
     ' This is only necessary for Word add-ins, but the code
    ' uses it for all applications just to be consistent. 
    cbb.Tag = conMenuTag
     ' This is only necessary for "on-demand" add-ins, 
     ' but it can't hurt. 
    cbb.OnAction = "!<" & pobjAddIn.ProgId & ">" 
   End If
   Set AddToCommandBar = cbb
End Function
  Public Sub DeleteFromCommandBar()
   ' Remove the control you added from the CommandBar. All
  ' apps but Word seem to manage this by calling the Delete
  ' method of the WithEvents CommandBarControl object. Word
  ' requires you to find the control again, and delete it. 
   ' Therefore, all the apps use this same technique. 
   On Error Resume Next
  pobjCommandBarHost.CommandBars(conMenuName)._
    Controls(conMenuText).Delete
  Err.Clear
End Sub

End Listing One