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.

Greater Office

Extending the Capabilities of O2K and the VBE

By Peter Vogel

Have you ever wanted to save all your open Word documents with a single mouse click? How about a function that closes all open document windows? COM Add-ins let you add this kind of functionality to Microsoft Office, the Visual Basic Editor (VBE), and the Microsoft Development Environment (MDE). It was possible to extend these environments, via Add-ins, before Office 2000 (O2K). However, each Office application handled Add-ins differently; Word even had its own peculiar DLL format for Add-ins. With COM Add-ins, a single mechanism is used for integrating new components with all O2K applications, the VBE, Visual Basic, and the MDE.

Thomas Rizzo's two-part series, in the March and April 1999 issues of Microsoft Office & VBA Developer, introduced you to COM Add-ins and showed you how to use them with Outlook. In this article, I'm going to go under the hood to show you some of the details of how COM Add-ins connect to their hosts. I'll show you how to use the tools that come with Office to create a COM Add-in and what code you need to connect your Add-in to the hosting environments of Office, Visual Basic, and the MDE. After that, adding the specific functionality that you want is up to you.

A word of warning: Saying that COM Add-ins allow you to extend all the hosts in the same way is something of an exaggeration. Any Add-in you write will have to interact with the object model of the application that is hosting your Add-in. So, when creating any Add-in, you'll need to be familiar with the object model for the application the Add-in is supposed to extend. And, once you start using an application's object model, your Add-in is tied to that model.

It's also not strictly true that a COM Add-in can be loaded by any of the potential environments. It's more accurate to say that you can configure your Add-in to work with as many of the hosting environments as you want. I'll discuss what's required to connect an Add-in to any of the host environments.

Linking Add-ins and Hosts

COM Add-in hosts determine which Add-ins are available for them by reading a set of entries in the Windows registry. So, in addition to the normal entries for a component, a COM Add-in must also have entries in the Add-ins section of the registry for the host or hosts that will use it. For example, an Add-in that works with Word and Access would have to be listed in the Access and Word Add-in sections in the Windows registry. See the sidebar "Adding Add-ins" for more on linking Add-ins so they work with Office.

The Add-in Designer

If the mention of registry entries makes you cringe, relax. Microsoft Office 2000 Developer and Visual Basic 6 include an ActiveX designer for creating Add-ins (see FIGURE 1). The Add-in designer provides a form for you to enter the information that's required in the registry entries for a single host. The designer then takes care of making those registry entries for you. You will need to add to your project an instance of the designer for each host that you want your COM Add-in to work with.

FIGURE 1: The General tab of the VBA designer for Add-ins.

To begin creating an Add-in in any of the Office products, open the VBE, select New Project, and, from the dialog box that appears, select Add-in Project. A project is created for you containing an Add-in designer. It doesn't matter what Office application you create your Add-in from, because the Add-in will be compiled as a stand-alone application.

Adding the additional designers to support multiple hosts is awkward. You first have to save the Add-in designer that's automatically added to your designer using File | Export File. You can then import the designer (using File | Import File) to add a second designer to your project. You'll need to add one designer for each host application you want your Add-in to work with.

The Add-in designer has two tabs. The General tab lets you set a number of values for your Add-in:

• Addin Display Name, Addin Description: These two options let you set values that are displayed in the Object Browser and the References dialog box.
• Application, Application Version: These two entries allow you to determine in which Add-ins section in the registry the Add-in will appear. Selecting Word, for instance, will cause the designer to list your Add-in in the Add-ins section of Microsoft Word.
• Initial Load Behavior: This setting controls when the host will activate your Add-in.

The Advanced tab provides you with two kinds of information (see FIGURE 2). First, you can associate a resource file with your Add-in (resource files are typically used to support internationalization). The resource file must be in the same directory as your compiled Add-in file.

FIGURE 2: Creating a new registry entry using the Add-in designer's Advanced tab.

The Advanced tab also lets you put some additional values in the Windows registry as part of installing your Add-in. In the text box labeled Registry Key for Additional Addin Data, you can specify the full name of a registry key that you want created or have entries added to. In the Addin Specific Data box, you can enter the name and data for the values that you want stored under that key.

For instance, I typically store my company name in the registry with any Add-ins I create. Entering the key HKEY_CURRENT_USER\Software\Microsoft\Office\Word\PHVData on the Advanced tab creates an entry called PHVData under the same key as the Add-ins entry. Entering a value with the name "CompanyName" and the value "PH&V Information Systems" stores that information under the new key.

Loading Your Add-in

The designer exposes a set of events that your Add-in's host will use to coordinate its activities with your Add-in:

• OnConnection: This event fires when your Add-in is being added to the host environment.
• OnDisconnection: This event fires when your Add-in is removed from the host environment.
• OnStartupComplete: This event fires when the host is finished loading (including loading any initial documents or projects).
• OnBeginShutdown: This event fires as the host starts to unload.
• OnAddinstUpdate: This event fires when any Add-in is added or removed from the host environment.

The OnConnection event should contain any code required to integrate your Add-in with its host. When the OnConnection event fires depends on how the host loads the Add-in. The host uses the Load Behavior setting in the designer to decide how to load your Add-in. The various settings for Load Behavior and their descriptions are shown in FIGURE 3.

Load Option	Description
None	The Add-in will appear in the Add-in Manager, but will not be loaded.
Startup	The Add-in will be loaded with the hosting environment.
Load at next startup only	The Add-in will be loaded with the hosting environment the next time the host is used and, after that, only when required.
Load on Demand	The Add-in will be loaded only when required.
Command Line	Load the Add-in when the host is started from the command line.

FIGURE 3: Load Behavior settings.

Only the Office components offer the Load at next startup setting (the Command Line option is only available for Visual Basic and the MDE). The Load at next startup setting causes the host to load the Add-in and run its OnConnection routine so the Add-in can integrate itself with the host, e.g. add required menu items. After the OnConnection routine finishes running, though, the Add-in's Load Behavior setting is changed to Load on Demand. The next time the user opens the host, only the Add-in's type library will load. The Add-in itself won't be loaded until it's requested.

After being loaded and having its OnConnection routine run, the next major event in the Add-in's life occurs when the host calls the OnDisconnection routine. This call signals that the Add-in is being removed from memory. The Add-in can be unloaded for two reasons:

• The user has disconnected the Add-in (either by using the Add-in Manager or programmatically); or
• the host is shutting down.

You will normally put code in the OnDisconnection routine to undo any changes made to the host environment in the OnConnection routine.

The OnConnection and OnDisconnection routines have several parameters passed to them that you can use to determine what action to take in those routines. In the next two sections, I'll show you how you can use the information in those parameters.

The OnConnection Routine

The first parameter passed to the OnConnection method is a reference to the top-level object in the host environment - typically, the Application object. If other routines in your Add-in need access to the host's objects, you'll need to set a variable to point to this object.

The OnConnection routine is called in two situations. First, the routine is called when the user adds your Add-in to the host environment using the host's Add-in Manager. Second, the routine is called when the Add-in is loaded as part of the host's startup process. The second parameter passed to the OnConnection routine, ConnectMode, allows you to determine when in the host's load process your Add-in is being loaded. The values you'll receive in this variable are:

• ext_cm_Startup: The Add-in was started after the host was loaded, but before any documents or projects were loaded.
• ext_cm_AfterStartup: The Add-in was started after the host was loaded, and after any documents were displayed.
• ext_cm_External: The Add-in was loaded by another program.
• ext_cm_CommandLine: The Add-in was invoked by Visual Basic or the MDE when they were started from a command line.

Your OnConnection code should check the ConnectMode parameter and, if it's set to ext_cm_AfterStartup, assume that the user has just selected your Add-in from the host's Add-in Manager dialog box. In that situation, you should perform any activities that integrate your Add-in with the host, e.g. adding menu items to call routines in your Add-in. I also display a reassuring message that includes the name of the Add-in to say that it has loaded correctly, in case the user has selected my Add-in by mistake.

If the ConnectMode is ext_cm_Startup, you should assume the Add-in was added to the host in a previous session. In this situation, you shouldn't need to perform any activities to integrate the Add-in, because you would have done that when the Add-in was first added. You shouldn't perform any activities in the OnConnection event that assume the host environment is fully loaded. Instead, you should defer those activities to when the OnStartupComplete routine is called.

The third parameter passed to the OnConnection routine is a reference to your Add-in. Some of the object methods typically called by an Add-in require a reference to the Add-in itself. If you're going to use those methods, you should set a Public object variable to point to that object as well.

The final parameter passed to the OnConnection method is an array of Variants. This array is used by some hosts to pass information to the Add-in. The first entry in this array indicates how the hosting environment itself was loaded. The three values this parameter can contain are listed in FIGURE 4.

Value	Description
1	Host started as a stand-alone application.
2	Host is being embedded in another document.
3	Host started via Automation.

FIGURE 4: Values for the first position in the custom parameter, i.e. custom(0).

The second and third values of the custom array's first position require some explanation. The value 2 would be passed to an Excel Add-in if an Excel spreadsheet was being embedded in a Word document. The value 3 would be passed if a program was using Excel as a component by starting it with New or CreateObject. In both situations, you may not want your Add-in to be active (especially if it's a user-interface enhancement). You may also want to disable any menu items that call your Add-in.

FIGURE 5 shows a typical OnConnection method. The routine first checks the custom array to see how the host application was started. Not all hosts pass this array to the routine, however, so you must test for its presence before using it. The mechanism shown here - checking the first position, then checking for an error - is the only reliable method I've found. If the custom(0) data indicates that the Add-in is being started as part of being embedded or through Automation, I set a Boolean variable as a flag to other methods that they shouldn't execute, and then exit the routine.

Public bolInactive As Boolean
      Public objHost  As Object
      Public objSelf As Object
       Private Sub AddinInstance_OnConnection( _
        ByVal Application As Object, _
        ByVal ConnectMode As _
          AddInDesignerObjects.ext_ConnectMode, _
        ByVal AddInInst As Object, custom()As Variant)
         On Error Resume Next
        If custom(0) > 1 Then
          If Err.Number = 0  
      Then 
            bolInactive =  True
            Exit Sub
          Else
            Err.Clear
            On Error GoTo 0
          End If
        End If
         Set objHost = Application
        Set objSelf = AddInInst
        If ConnectMode = ext_cm_AfterStartup  
      Then
       
          ' ...perform integration activities... 
          MsgBox "MyAdd-in successfully loaded." 
        End If
      End Sub

FIGURE 5: A typical OnConnection method.

If the host is starting normally, the code sets module-level variables to point to the Application and AddInInst objects so they can be used by other routines in the Add-in. Finally, the code checks the ConnectMode property. If the Add-in being loaded for the first time, the code performs any integration tasks (e.g. adding menu items) and displays a message indicating that the routine completed successfully.

The OnDisconnection Routine

The OnDisconnection routine is called when your Add-in is being unloaded. Your Add-in may be unloaded because the host is being shut down, or because the user has decided to remove your Add-in with the Add-in Manager. The first parameter passed to the OnDisconnection method is RemoveMode, which tells you why your Add-in is being removed from the host environment. There are two values that can be passed as an argument:

• ext_dm_HostShutdown: The host is being shut down.
• ext_dm_UserClosed: The Add-in is being removed from the host.

In a typical OnDisconnection routine, I check the RemoveMode parameter to see if contains ext_dm_UserClosed, indicating that the Add-in is being removed because the user is unchecking the Add-in's entry in the Add-in manager. If so, I display a message saying that the add-in has been successfully removed, and undo any integration changes made in the OnConnection routine, e.g. removing menu items. If the RemoveMode is ext_dm_HostShutdown, I leave the OnConnection routine's changes in place.

FIGURE 6 shows a typical OnDisconnection routine that checks the RemoveMode to determine if it should "back out" anything from the host environment.

Private Sub AddinInstance_OnDisconnection( _
        ByVal RemoveMode As _
          AddInDesignerObjects.ext_DisconnectMode,_
      As Variant)
         If RemoveMode = ex_dm_UserClosed Then
          ' ...disconnect activities... 
          Msgbox "My Add-in has been unloaded." 
        End If
      End Sub

FIGURE 6: A typical OnDisconnection routine.

The only other parameter passed to the OnDisconnection routine is the custom array. The other three routines (OnStartupBegin, OnAddinstUpdate, OnBeginShutdown) are passed only the custom array.

Conclusion

This article provides a detailed look at using VBA's Add-in designer to create COM Add-ins. Armed with this information - and some knowledge of a COM Add-in's host's objects - you can extend the power of the tools that you work with. Now you can get Word, Excel, or the Visual Basic IDE to do what you want.

Peter Vogel (MBA, MCSD) is a principal in PH&V Information Services, specializing in system design and development for VBA-based systems. PH&V is involved in creating intranet and component-based applications. Peter is also the editor of the Smart Access newsletter (the authority for in-depth technical information for Microsoft Access developers). Peter's The Visual Basic Object and Component Handbook is published by Prentice Hall. He teaches Access, Visual Basic, and database design for Learning Tree International and wrote Learning Tree's Web application development course. His articles have appeared in every major magazine devoted to VB-based development and in the Microsoft Developer Network libraries. Peter also sits on the editorial advisory board for the IT Consultant newsletter.

Adding Add-ins

When users want to add an Add-in to an application, they use the Add-in Manager to get a list of all Add-ins listed in the host's Add-ins registry area. In the VBE and Visual Basic, the Add-ins | Add-in Manager menu item on the standard menu bar displays the Add-in Manager. In Office, the equivalent Add-ins menu item isn't part of the standard menus. In part, this is because the Office applications must continue to support their old Add-in architecture. As a result, the Add-ins entry on Office's standard menus lists the old application-specific add-ins rather than the new COM Add-ins.

In Office, the user must add the COM Add-ins menu choice to their menu system before they can use your Add-in (or you can test it). To add this menu item, select Tools | Customize and click on the Commands tab to display a list of menu commands. The various commands you can add are organized by the menus they normally appear on. To find the COM Add-ins menu item, click on the Tools entry in the Categories column. Once you've found the COM Add-ins choice, click and drag it to whichever menu or menu bar you want to use it from.

- Peter Vogel