How Do I...

Office COM add-ins tend to share common elements. It's safe to say that every add-in will have at least some combination of the more common elements listed and described here. Depending on what functionality your add-in provides, you might even consider these elements basic. The previous chapter also describes examples of common elements, such as setting up and handling events in an Office application when the add-in is loaded.

Every COM add-in must have a programmatic identifier (ProgID). This is a string that uniquely identifies a given COM add-in. A ProgID, which isn't considered something that an end user should see, is a text string without spaces. ProgIDs are unique identifiers for COM add-ins. A COM add-in can have more than one class, and hence, more than one ProgID.

You can set the programmatic identifier, or ProgID, of a COM add-in by changing the project name and the class name. Start Visual Basic 6.0, select ActiveX DLL or ActiveX EXE from the New Project dialog box, and complete the following steps:

1. On the Project menu, click Project1 Properties, and then enter BasicCOMAddIn or any other project name in the Project Name box. Click OK.



2. In the Project Explorer window, double-click the Add-in Designer to bring it to the front. In the Properties window, set its Name property to any valid string and make sure that its Public property is set to True.

If you're not using the Add-in Designer and you have a class module with the IDTExtensibility2 interface implemented, in the Project Explorer window double-click the Class module Class1 to bring it to the front. In the Properties window, set its Name property to any valid string and make sure that its Instancing property is set to 5 - MultiUse.

To create a COM add-in for Office 2000 without using the Add-in Designer, you need the following programs in addition to Office 2000: Visual Basic 5.0 or 6.0 and Microsoft Notepad (or any application like Microsoft WordPad that allows you to create a text file with the extension .txt). The following example summarizes the steps you need to take to create a COM add-in in Visual Basic 6.0 using a .reg file instead of the Add-in Designer. A .reg or REG file is a text file that contains Windows Registry information. The information indicates how you should register the COM add-in for a specific Office 2000 application.

1. Start Visual Basic 6.0 and in the New tab of the New Project dialog box double-click the ActiveX DLL icon to create a new project.


ActiveX DLL

2. On the Project menu, click References to display the References dialog box for the current project, then scroll down the list of Available References and select Microsoft Add-In Designer. Click OK.



This object library (stored in the file msaddndr.dll) contains the definitions of the IDTExtensibility2 interface, which is what the COM add-in model is centered around and which is described earlier in this chapter in the section "The COM Add-In Model Overview."

3. Add the following line of code to the top of the class module Class1 that's added by default to the ActiveX DLL project:

Implements IDTExtensibility2

This line of code adds a reference to the IDTExtensibility2 object to your project. An interface's methods are exposed through the Implements statement. When you enter the above syntax in the class module, IDTExtensibility2's methods become available through the class module's Procedure and Object drop-down lists, as shown in the illustration below.



4. Click IDTExtensibility2 in the Object drop-down list of the Class module.

The Procedure drop-down list of the class module lists five events for the IDTExtensibility2 object: OnConnection, OnDisconnection, OnStartupComplete, OnBeginShutdown, and OnAddInsUpdate.



5. Click each of the events in the Procedure drop-down list to add their procedures to the class module Class1. Add a comment in the OnStartupComplete, OnBeginShutdown, and OnAddInsUpdate procedures and the following two lines in the OnConnection and OnDisconnection procedures, respectively:

MsgBox "Add-in connected" MsgBox "Add-in disconnected"

The code module should appear as follows:



In Visual Basic, you need to add at least a comment to each procedure in order for your code to compile. In the OnStartupComplete, OnBegin-Shutdown, and OnAddInsUpdate procedures, you add the comment character (an apostrophe) as the one and only line in the procedure.

6. On the Project menu, click Project1 Properties, then enter BasicCOMAddIn in the Project Name text box. Click OK.



7. In the Project Explorer window, double-click the class module Class1 to make it the active module. In the Properties window, set the Name property of the class module to "Connect" and make sure its Instancing property is set to 5 - MultiUse.

This step and the previous step set the ProgID of the COM add-in. The ProgID allows you to uniquely identify your COM add-in and distinguish it from others.

8. On the File menu, click Save Project to save the project files. Name them as shown in the following table. Visual Basic will provide the indicated extensions automatically.

File	Filename	Extension
Class module	Connect	.cls
Project	BasicCOMAddIn	.vbp

The class module that implements the IDTExtensibility2 interface is the center of every COM add-in. It's the place where the add-in gets called when it's loaded and unloaded in an Office application. In the previous steps, you see a message when you connect and disconnect the add-in. The main class module is where most of the common elements of an add-in are connected, such as adding and removing command bar customizations and responding to events that occur in any of the Office applications.

To make a COM add-in work in Office, you need to make sure that two things happen so that the COM add-in is recognized by an Office application as an available add-in. Both involve registration in the Windows Registry. The first is to register the add-in on the end-user's machine so that the system knows where it's located on the machine. The second is to register the add-in under the Office key so an application knows it's available and can be loaded. Once Office knows the add-in's available, it needs to know when it should load it. This is described in the earlier section of this chapter entitled "COM Add-In Registration."

1. Click the File menu, and then click Make BasicCOMAddIn.dll. In the Make Project dialog box, click OK.

Doing this will register the COM add-in in the Windows system registry. The act of compiling the .dll registers it in the system registry. However, even though the add-in is registered on the machine, it's yet to be registered where an Office 2000 application will know to load it. This is handled in the following steps.

2. Start Notepad and type the following in the Notepad window:

REGEDIT4 [HKEY_CURRENT_USER\Software\Microsoft\Office\ Word\AddIns\BasicCOMAddIn.Connect] "LoadBehavior"=dword:00000003 "FriendlyName"="Microsoft Office Basic COM Add-In" [HKEY_CURRENT_USER\Software\Microsoft\Office\ Excel\AddIns\BasicCOMAddIn.Connect] "LoadBehavior"=dword:00000003 "FriendlyName"="Microsoft Office Basic COM Add-In"

3. Save the text file as COMAddin.reg. You must type in the .reg extension in the filename text box so that Notepad knows to save the file with the extension .reg rather than .txt.
4. In the Windows Explorer, navigate to the file you saved in the step above and double-click the file COMAddin.reg.

The following message should appear, indicating that the information in the COMAddin.reg file was successfully entered in the registry. If not, you may have typed something incorrectly in step 2. You can also copy the file COMAddin.reg from the Chapter 14 folder on the CD that comes with this book.



5. Switch to the Visual Basic window containing the BasicCOMAddIn project and press F5 to run the project.
6. Start Word or Excel and then switch to the Visual Basic window. The following message box will appear in front of the Visual Basic window:



7. Click OK in the message box and in Word or Excel click Exit on the File menu. Switch to the Visual Basic window.

You'll see the message box in front of the Visual Basic window indicating that the add-in is disconnected. In step 2 above, the value on the line that lists the text "LoadBehavior" is 3. This means that the add-in is set to be loaded when the Word or the Excel application is started. (Earlier in the chapter, under the section "COM Add-In Load Behavior Settings," the different load behaviors for a COM add-in are described, as are examples to help you decide what the load behavior for your COM add-in should be.)

In the case of the basic COM add-in described here, the add-in is registered and available on your machine. Once your add-in is fully developed, debugged, and ready to go, the next step is to package it and make its functionality available to other users. The previous chapter describes how to deploy your COM add-in so that you can register and install it on any user's machine. The Package and Deployment Wizard also has an option to package a .reg file and have it executed on the user's machine during setup of the COM add-in.

As Office continues to evolve, consistency among the applications becomes more prominent and lasting. More and more Office functionality, like menus, toolbars, and the Office Assistant, is implemented across the applications. The functionality is written once centrally in a .dll and is loaded into each Office application. COM add-ins allow you to develop functionality that can be loaded in any combination of Office applications, including Microsoft FrontPage 2000. However, there are a few differences among the Office applications, and sometimes you'll have to write code that is application-specific within the shared COM add-in.

To determine what application a shared COM add-in is loaded in, you first retrieve its name. The first argument passed to the OnConnection event procedure in the COM add-in is an instance of the Application object of the Office application where the add-in is loaded. You can retrieve the application's name by accessing the Name property of the Application object. Once you know the application's name, you can use a Select Case statement to distinguish between applications and write application-specific code, as you'll see in the following Select Case statement. Throughout your COM add-in, you'll use a variation of the following Select Case statement when you write application-specific code.

1. In the COM add-in project created in the previous example (in the sections "Develop a COM Add-In Without the Add-In Designer" and "Test the BasicCOMAddIn Add-In"), click Add Module on the Project menu. In the Add Module dialog box, select Module in the New tab and click Open. Add the following declaration:

Public HostApp As Object

You define the generic object variable HostApp as a public-level variable so you can use it throughout any code module. However, you can declare this variable at any level, depending on how you write your code.

2. Switch to the Connect class module and add the following line as the first line in the procedure IDTExtensibility2_OnConnection:

Set HostApp = Application

Because the Set statement is the first line in the OnConnection procedure, the very first thing that happens when the COM add-in is loaded is the assigning of the variable HostApp to the Application object of the Office application loading the COM add-in. The first argument passed into the OnConnection procedure is named Application. The argument is declared as the generic type Object because it could be the application object of any of the Office applications.

3. Below the set statement added in the previous step, add the following Select Case block.

Select Case HostApp.Name Case "Microsoft Word" Case "Microsoft Excel" Case "Microsoft PowerPoint" Case "Microsoft Access" Case "Outlook" End Select

As noted previously, throughout your COM add-in you use variations of the Select Case statement, depending on the differences between applications and how many applications your COM add-in targets; you may not need all the Case expressions listed in this step.

4. Add application-specific code within each Case expression. The following contains code that writes out the number of items in a specific application collection.

Select Case HostApp.Name Case "Microsoft Word" Debug.Print HostApp.Documents.Count Case "Microsoft Excel" Debug.Print HostApp.Workbooks.Count Case "Microsoft PowerPoint" Debug.Print HostApp.Presentations.Count Case "Microsoft Access" Debug.Print HostApp.CurrentObjectName Case "Outlook" Debug.Print HostApp.ActiveExplorer _ .CurrentFolder.Items.Count End Select

5. Repeat steps 1 through 4 in the example in the section "Test the BasicCOMAddIn Add-In" in order to register your COM add-in.

In step 2 of that section, make sure to add the registry information in the Notepad file for each of the applications listed in the Select Case block in the previous step. To do this, copy the follow-ing registry information in the Notepad file for each Office application. You'll also need to change Word in the following registry information to the name of the Office application. Also remember to concatenate in the Notepad file the first two lines below.

[HKEY_CURRENT_USER\Software\Microsoft\Office\ Word\AddIns\BasicCOMAddIn.Connect] "LoadBehavior"=dword:00000003 "FriendlyName"="Microsoft Office Basic COM Add-In"

6. Switch to the Visual Basic window containing the BasicCOMAddIn project and press F5 to run the project.
7. Start an Office application that the add-in is registered for and switch to the Visual Basic window.

The message box indicating that the add-in is connected will appear in front of the Visual Basic window. After you click OK on the message box, the Immediate window in Visual Basic displays the count of the collection specified in the Select Case block for that application.

Unlike what happens when you write code in the Visual Basic Editor in each of the Office applications, when you type in the dot after the object variable HostApp in the previous steps, you won't see the Auto List Members drop-down list. This is because you declared the object variable HostApp as the generic type Object. Therefore, when you write code, the code editor you're writing in doesn't know which application you're referring to when you hit the dot. You can easily make your code-writing experience take advantage of the Auto List Members drop-down list functionality by declaring more variables that are defined as the application your COM add-in targets.

1. On the Project menu in Visual Basic, click References to display the References dialog box for the current COM add-in project.
2. Scroll down the list of Available References and select each of the object library names listed in the table below.

Microsoft Office 2000 Application	Object Library Name
Office	Microsoft Office 9.0 Object Library
Word	Microsoft Word 9.0 Object Library
Excel	Microsoft Excel 9.0 Object Library
PowerPoint	Microsoft PowerPoint 9.0 Object Library
Access	Microsoft Access 9.0 Object Library
Outlook	Microsoft Outlook 9.0 Object Library

You'll select only the object libraries associated with each of the Office 2000 applications your COM add-in is targeting. The table lists the object library associated with each Office 2000 application. Note that in most cases, you'll also need to reference the shared Office 2000 object library. This object library appears as Microsoft Office 9.0 Object Library in the Available References list.

3. In the standard code module inserted in the previous example, add the following declarations.

Public WrdApp As Word.Application Public XclApp As Excel.Application Public PptApp As PowerPoint.Application Public AccApp As Access.Application Public OlkApp As Outlook.Application

You declare each variable as a specific application object. When you made the reference to each application's object library in the previous step, each application became listed in the Auto List Members drop-down list after you typed the word "As" in each declaration.

4. Starting at the first line in the OnConnection procedure in the Connect class module, add the following Select Case block.

Select Case Application.Name Case "Microsoft Word" Set WrdApp = Application Case "Microsoft Excel" Set XclApp = Application Case "Microsoft PowerPoint" Set PptApp = Application Case "Microsoft Access" Set AccApp = Application Case "Outlook" Set OlkApp = Application End Select

Because the Select Case statement is the first line in the OnConnection procedure, the very first thing that happens when the COM add-in is loaded is the assessment of the name of the application loading the COM add-in. The appropriate object variable is set, depending on the application.

5. Within each Case expression added in the previous example (not the previous step), use the variable for each application object. For example, add the following line to the Case expression for Word.

MsgBox WrdApp.Documents.Count

You'll see the Auto List Members drop-down list when you type in the dot after the variable WrdApp.

When the COM add-in is loaded into an application, only one application object variable declared will be set. All others will continue to be set to Nothing by default. This approach means that if you target your COM add-in for multiple Office applications, Visual Basic will define and make space in memory for object variables that are never used. However, this overhead will be small and insignificant. Now you can take advantage of the benefits of the Auto List Members drop-down list functionality.

To create a demand-loaded COM add-in, you need to set the OnAction property on the CommandBarButton or CommandBarComboBox object. When you click a command bar button, for example, Office parses the string returned by the OnAction property to determine what COM add-in needs to be loaded and to handle the click event. If the syntax of the OnAction string contains a ProgID of a registered COM add-in, the click event is sent to the Click event procedure in the COM add-in. The syntax for the OnAction property string is as follows:

!<Prog.ID>

In your code that creates a command bar button, the string you assign to the OnAction property would appear similar to the following code:

Set cmdBtn = Application.CommandBars("Help") _ .Controls.Add With cmdBtn .Caption = "&Custom Control" .OnAction = "!<Project1.Connect>" .Tag = "MyControlTag" End With

1. Start Visual Basic 6.0 and double-click the ActiveX DLL icon in the New Project dialog box.
2. On the Project menu in Visual Basic, click References to display the References dialog box for the current COM add-in project, scroll down the list of Available References and select Microsoft Office 9.0 Object Library. Click OK.
3. On the Project menu, click Project1 Properties and in the Properties dialog box, change the Project Name from Project1 to DemandLoad.
4. Click Add Addin Class on the Project menu and on the General tab of the Add-in Designer, select Microsoft Excel from the Application drop-down list and "Load at next startup only" in the Initial Load Behavior drop-down list.
5. In the Properties window, change the Name property from AddinDesigner1 to Connect. Also change the Public property to True. Click OK after you see the message box.
6. Press F7 to display the Addin class module's code window.
7. Add the following lines of code so that the Addin class module appears as follows (and as listed in the DmndLoad.vbp project in the Chapter 14 practice files):

Dim HostApp As Object Dim WithEvents cmdBtn As Office.CommandBarButton Const m_sProgId As String = "!<DemandLoad.Connect>" Private Sub AddinInstance_OnConnection( _ ByVal Application As Object, _ ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _ ByVal AddInInst As Object, custom() As Variant) Set HostApp = Application Select Case ConnectMode Case ext_cm_Startup AddCmdBarCustomizations Case ext_cm_AfterStartup If Not HostApp.CommandBars.FindControls( _ Tag:="DemandLoadButton1") Is Nothing Then Set cmdBtn = HostApp.CommandBars _ .FindControls(Tag:="DemandLoadButton1") _ .Item(1) Else AddCmdBarCustomizations End If End Select End Sub Private Sub AddinInstance_OnDisconnection( _ ByVal RemoveMode As AddInDesignerObjects _ .ext_DisconnectMode, custom() As Variant) If RemoveMode = ext_dm_UserClosed Then HostApp.CommandBars.FindControls( _ Tag:="DemandLoadButton1").Item(1).Delete End If End Sub Private Sub cmdBtn_Click( _ ByVal Ctrl As Office.CommandBarButton, _ CancelDefault As Boolean) MsgBox "Demand-loaded add-in is now loaded." End Sub Sub AddCmdBarCustomizations() Set cmdBtn = HostApp.CommandBars("Tools") _ .Controls.Add With cmdBtn .Caption = "&Custom Control" .OnAction = m_sProgId .Tag = "DemandLoadButton1" End With End Sub

8. Add a breakpoint to the first line in the OnConnection procedure by placing the cursor in the line Private Sub AddinInstance_OnConnection and pressing F9.
9. Press F5 to run the project. Click OK in the project Properties dialog box when it is displayed.

1. On the Windows Start menu, click Programs and then Microsoft Excel. Excel starts and the COM add-in created in the previous example loads. The OnConnection procedure is called and execution breaks on the first line.
2. Press F8 to step through the code. The code continues through the Select Case statement and the code in which ConnectMode equals ext_cm_Startup is executed. The procedure AddCmdBarCustomizations is called to add a new custom command bar control on the Tools menu. In the AddCmdBarCustomizations procedure, the custom control is connected to the Click event procedure.
3. Press F5 to continue execution through the OnConnection procedure.
4. Switch to Excel and click Custom Control at the bottom of the Tools menu. The Click event of the command bar button executes and a message box is displayed.

5. Switch back to the Visual Basic window and click OK on the message box.
6. Switch to Excel and click Exit on the File menu.
7. Start Excel again. Note that the breakpoint in the OnConnection pro-cedure in the running Visual Basic project isn't hit. That's because the OnConnection procedure isn't executed. The COM add-in is demand-loaded; therefore, it's not loaded when Excel starts up.
8. Click Custom Control at the bottom of the Tools menu. The COM add-in created in this example loads. The OnConnection procedure is called and execution breaks on the first line. By pressing F8, the code continues through the Select Case statement and the code in the case where ConnectMode equals ext_cm_AfterStartup is executed. The existing instance of the custom command bar control is connected to the Click event procedure. Once the OnConnection procedure fully executes, the Click event procedure is then executed.

The OnConnection procedure is the main place where you should add a call to code that inserts command bar customizations like a menu item, toolbar button, or drop-down control. A couple of important things should happen when this code executes. The first is to determine when the add-in is to be loaded. In other words, is the add-in loaded at the startup of the Office application? Or is it to be demand-loaded by a click of a toolbar button or menu item? In each case your code should search to make sure that the customizations exist.

In the OnDisconnection procedure, then, you should add a call to code that removes command bar customizations. However, your code should first determine when the add-in is unloaded. Your code in the OnDisconnection procedure will appear similar to the following (similar also to the code in the previous example):

If RemoveMode = ext_dm_UserClosed Then ' User unloaded add-in through COM ' Add-Ins dialog box. Thus, remove commands. ElseIf RemoveMode = ext_dm_HostShutdown Then ' If your add-in is demand loaded, ' do not remove commands. End If

The first argument passed to the OnDisconnection procedure is the RemoveMode value. The two common values passed in are ext_dm_HostShutdown or ext_dm_UserClosed. In the previous example, in the section "Create a Demand-Loaded Add-In," you added code that evaluates the RemoveMode value to the OnDisconnection procedure.

Once you register a COM add-in so that it loads when an Office 2000 application starts, your add-in can determine how the Office application starts. Word, Excel, and PowerPoint can be started in one of the following three ways. Outlook and Access can be started in either of the following first two ways.

• Through the Windows Start menu or the Windows Explorer A user typically starts an application by clicking Start, Programs, and then the Office application item, like Word, for example. The user can also start an application by double-clicking a file associated with the application in the Windows Explorer.
• Through Automation For example, a solution or program creates an instance of Word using the function CreateObject("Word.Application") in Visual Basic.
• Through Embedded Objects For example, a user inserts a Word document into an Excel spreadsheet. In this case, Word is started through an embedded Word object.

Developers can use the first value passed into the custom() array in the OnConnection procedure to determine if an application is started by means of Automation. You can use this information, for example, to not display a message box or custom dialog box until the application is made visible. The following example shows how to evaluate the first value of the custom() array passed into the OnConnection procedure and determine how an application is started.

1. Start Visual Basic 6.0 and in the New Project dialog box, double-click the ActiveX DLL icon.
2. Right-click the class module Class1 added by default in the Project Explorer window. Click Remove Class1 on the shortcut menu. Click No in the dialog box that asks if you want to save changes.
3. In the Project menu, click Add Addin Class. If this menu item doesn't appear, click Components on the Project menu. In the Components dialog box, click the Designers tab and select Addin Class in the list box. Click OK. You should see the menu item Add Addin Class on the Project menu.
4. In the General tab of the Add-in Designer, select Microsoft Excel from the Application drop-down list and Startup in the Initial Load Behavior drop-down list.

You may have to resize the Add-in Designer window in order to see the Initial Load Behavior drop-down list at the bottom of the General tab.

5. Press F7 to display the class module for the Add-in Designer. In the Object drop-down list at the top left of the class module window, select AddinInstance.
6. In the OnConnection procedure, add the following Select Case statement:

Select Case custom(1) Case 1 Debug.Print "UI" Case 2 Debug.Print "Embedding" Case 3 Debug.Print "Automation" End Select

7. In the Properties window, change the Public property to True. Click OK when you see the message box. Change the name of the Add-in Designer from AddInDesigner1 to AppStart.
8. Add a breakpoint to the first line in the OnConnection procedure by placing the cursor in the code Select Case custom(1)and pressing F9.
9. Press F5 to run the project. Click OK in the project Properties dialog box when it is displayed.

1. On the Windows Start menu, click Programs and then Microsoft Excel. Excel is started and the COM add-in created in this example is loaded. The OnConnection procedure is called and execution breaks on the first line of the Select Case statement.
2. Press F8 to step through the code. The value of custom(1) is evaluated in the first line of the Select Case statement and code execution continues through to the first Case expression. The string "UI" is printed to the Immediate window in Visual Basic, indicating that Excel has started through the user interface.
3. Press F5 to continue execution through the OnConnection procedure.
4. Click Exit on the File menu in Excel.
5. Using the Windows Explorer, navigate to an Excel workbook file. Double-click the file in the Windows Explorer to start Excel and open the workbook. Excel is started and the COM add-in is loaded. The OnConnection procedure is called and execution breaks on the first line of the Select Case statement.
6. Press F8 to step through the code. The value of custom(1) is evaluated in the first line of the Select Case statement and code execution continues through to the first Case expression. The string "UI" is printed to the Immediate window in Visual Basic, indicating that Excel has again started through the user interface.
7. Press F5 to continue execution through the OnConnection procedure.
8. Click Exit on the File menu in Excel.

The following steps will work only if the security setting in Word (or Excel or PowerPoint) is set to Low. Before completing the following steps, temporarily set the security setting to Low by clicking Macro on the Tools menu and then Security on the submenu. In the Security Level tab, select Low and click OK.

In Word, Excel, and PowerPoint, when the application is started through an embedded object, any COM add-in that isn't digitally signed with a certificate or the certificate is not added to the trusted sources list won't be loaded at startup. To see the list of trusted sources, click the Trusted Sources tab in the Security dialog box. For more information about security and digitally signing your COM add-in before distribution, see the section "Digitally Sign a COM Add-In" later in this chapter.

1. Start PowerPoint and after the default presentation is created, temporarily change the security settings to Low.
2. Click Object on the Insert menu.
3. In the Insert Object dialog box, select Microsoft Excel Worksheet from the Object Type list box in the Create New tab. Click OK.

Excel is started and the COM add-in is loaded. The OnConnection procedure is called and execution breaks on the first line of the Select Case statement.

4. Press F8 to step through the code. The value of custom(1) is evaluated in the first line of the Select Case statement and code execution continues through to the second Case expression. The string "Embedding" is printed to the Immediate window in Visual Basic, indicating that Excel has started through an embedded object.
5. Press F5 to continue execution through the OnConnection procedure.
6. Switch to Excel and click Exit on the File menu. Reset the security setting back to Medium or High in PowerPoint by clicking Macro on the Tools menu and then Security on the submenu. In the Security Level tab, select Medium or High and click OK.

1. Click ALT+F11 to display the Visual Basic Editor in PowerPoint.
2. Click Module on the Insert menu, and then add the following code:

Dim appXL As Object Sub StartExcelThroughAutomation() Set appXL = CreateObject("Excel.Application") appXL.Visible = True End Sub

3. Place the cursor in the StartExcelThroughAutomation procedure and press F5 to run the procedure.

Excel is started and the COM add-in is loaded. The OnConnection procedure is called and execution breaks on the first line of the Select Case statement.

4. Press F8 to step through the code. The value of custom(1) is evaluated in the first line of the Select Case statement and code execution continues through to the third Case expression. The string "Automation" is printed to the Immediate window in Visual Basic, indicating that Excel has started through Automation.
5. Press F5 to continue execution through the OnConnection procedure.
6. Switch to Excel and click Exit on the File menu.

In Office 97, Word, Excel, PowerPoint, and Access all provide the Run method on the Application object. However, PowerPoint's method is slightly different than the others. In all cases, the Run method doesn't allow you to directly access a class object. The Run method allows you to execute a procedure in another add-in file.

With the COM add-in model, you can use the Object property on the COMAddIn object (defined in the Microsoft Office 9.0 Object Library) to access a public class exposed by a COM add-in. If two COM add-ins are loaded in one Office application, code in the first COM add-in would expose a class to another add-in by setting the Object property in its OnConnection procedure. The code would appear similar to the following:

Private Sub IDTExtensibility2_OnConnection( _ ByVal Application As Object, _ ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _ ByVal AddInInst As Object, custom() As Variant) ' ' some code AddInInst.Object = MyPublicClass ' End Sub

Code in the second COM add-in that accesses the public class exposed in the first COM add-in would then use the Object property on the COMAddIn object. The code would appear similar to the following:

Sub AnyProcedure() Dim addinLibrary As Office.COMAddIn Dim objFromFirstAddin As Object Set addinLibrary = HostApp.COMAddIns("Library.ProgID") Set objFromFirstAddin = addinLibrary.Object ' use objFromFirstAddin object to access methods ' and properties exposed by the public class End Sub

The following example shows how to use this syntax so that one COM add-in can access methods and properties exposed in another.

1. Start Visual Basic 6.0 and double-click the ActiveX DLL icon in the New Project dialog box.
2. On the Project menu, click Project1 Properties and in the Properties dialog box change the Project Name from Project1 to Communicate1.
3. Select the class module Class1 added by default in the Project Explorer and in the Properties window, change the Name property to TopLevelObject.
4. Change the Instancing property of the class module to 2 - PublicNotCreateable.
5. Double-click the TopLevelObject class module to display its code window and add the following code:

Public Property Get Name() As String Name = "MyCOMAddin" End Property

6. Click Add Addin Class on the Project menu. On the General tab of the Add-in Designer, select Microsoft Excel from the Application drop-down list and Startup from the Initial Load Behavior drop-down list.
7. In the Properties window, change the Public property to True. Click OK when you see the message box. Also change the Name property from AddinDesigner1 to Addin1.
8. Press F7 to display the class module for the Add-in Designer. On the Object drop-down list at the top left of the class module window, select AddinInstance.
9. In the OnConnection procedure, add the following line of code so that the procedure appears as follows:

Private Sub AddinInstance_OnConnection( _ ByVal Application As Object, _ ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _ ByVal AddInInst As Object, custom() As Variant) AddInInst.Object = New TopLevelObject End Sub

10. Add a breakpoint to the first line in the OnConnection procedure by placing the cursor in the code AddinInstance_OnConnection and pressing F9.
11. Press F5 to run the project. Click OK in the project Properties dialog box when it is displayed.

1. Start a second session of Visual Basic 6.0 through the Window Start menu. Double-click the ActiveX DLL icon in the New Project dialog box.
2. On the Project menu, click Project1 Properties and in the Properties dialog box, change the Project Name from Project1 to Communicate2.
3. Click Add Addin Class on the Project menu and on the General tab of the Add-in Designer, select Microsoft Excel from the Application drop-down list and Startup from the Initial Load Behavior drop-down list.
4. In the Properties window, change the Public property to True. Click OK when you see the message box. Also change the Name property from AddinDesigner1 to Addin2.
5. Press F7 to display the class module for the Add-in Designer. In the Object drop-down list at the top left of the class module window, select AddinInstance. In the OnConnection and OnStartupComplete procedures, add the following line of code so that the module appears as follows:

Dim HostApp As Object Private Sub AddinInstance_OnConnection( _ ByVal Application As Object, _ ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _ ByVal AddInInst As Object, custom() As Variant) Set HostApp = Application End Sub Private Sub AddinInstance_OnStartupComplete(custom() As Variant) Debug.Print _ HostApp.COMAddIns("Communicate1.Addin1").Object.Name Debug.Print _ TypeName(HostApp.COMAddIns("Communicate1.Addin1").Object) End Sub

6. Add a breakpoint to the first line in the OnConnection procedure by placing the cursor in the code AddinInstance_OnConnection and pressing F9.
7. Press F5 to run the project. Click OK in the project Properties dialog box when it is displayed.

1. On the Windows Start menu, click Programs and then Microsoft Excel. Excel is started and the first COM add-in created in this example is loaded. The OnConnection procedure is called and execution breaks on the code AddInInst.Object = New TopLevelObject.
2. Press F8 to step through the code. Once the OnConnection procedure of the first COM add-in, Communicate1, is executed, the Visual Basic window containing the Communicate2 add-in project is activated and the OnConnection procedure of the second COM add-in, Communicate2, is called.

The code in the OnConnection procedure of Communicate2 sets the public-level HostApp object variable to the Excel Application object. As you continue to step through the code, the OnStartupComplete procedure in the Communicate2 add-in is called.

The code in the OnStartupComplete procedure accesses the COMAddIns collection object through the Excel Application object. The ProgID of the first COM add-in, "Communicate1.Addin1," is used in the index argument of the COMAddIns property to retrieve the COMAddIn object representing the first add-in. The Object property of the COMAddIn object is then used to access the class TopLevelObject.

As you continue to step through the code, the Name property, defined in the TopLevelObject class, is accessed. The Visual Basic window containing the Communicate1 add-in project is activated and the value of the Name property is retrieved. The Visual Basic window containing the Communicate2 add-in project is activated and the value of the Name property is printed to the Immediate window.

Note also that the second line of code in the OnStartupComplete procedure prints to the Immediate window the name of the type of object returned by the Object property. In this case, the string "TopLevelObject" is returned.

3. Switch to Excel and click Exit on the File menu.

Before distributing your COM add-in, you should digitally sign the add-in file. Office 2000 introduces a new security model similar to Microsoft Internet Explorer's default (Low, Medium, High) security settings. You can view the security setting for Word, Excel, and PowerPoint by clicking Macro on the Tools menu and then Security on the submenu.

If the security setting in an Office application is Medium or High, Office requires you to give a digital signature to a COM add-in available before the add-in can be loaded. A digital signature gives Office a way to verify two things: that the contents of a file haven't been altered since it was installed on the user's machine and that the file comes from a responsible source (the author or company that developed and distributed the COM add-in).

To digitally sign your COM add-in, you need a certificate and the file SignCode.exe (along with supporting files) that are part of the Microsoft Platform Software Development Kit. You can buy a certificate from a certificate authority, which is a company that validates your identity and issues you a certificate. The certificate authority not only issues certificates but also manages and revokes them when necessary. Once issued, a unique certificate contains your digital signature and verifies your credentials.

• In the latest Microsoft Developer Network (MSDN) CD or at http://msdn.microsoft.com, search on the words "signing code."
• For information about Microsoft security technologies, the Microsoft CryptoAPI, and other Microsoft technologies, see the Microsoft Security Advisor Web site (http://www.microsoft.com/security/).