Creating A COM Add In

This page describes how to write a COM Add In With Visual Basic 6.

Introduction

Beginning in verison 2000, Office programs such as Excel and PowerPoint began support for a new type of add in component called a COM Add In. A COM Add IN (CAI) is completely different from and independent from standard XLA type add-ins. A CAI has several advantages over a conventional XLA or PPA (PowerPoint) add in.

• Performance -- A CAI is an ActiveX DLL that is compiled into native machine code. A CAI runs considerably faster than the interpreted code of VBA.
• Code Security -- When you distribute a CAI to other users, you distribute only the DLL file, not the source code. Unlike a VBA-based add in such as an XLA, no source code is distributed to the users, so your proprietary code and valuable intellectual property remain secured on your machine. The user never gets any source code.
• Multiple Application Support -- One of the more interesting aspects of a CAI is that it can be written to support multiple applications. This allows you to create, manage, and distribute a single add in file that will support any or all of the Office applications. Of course, you must write the code for each supported application, but it is all compiled into a single DLL file.
• More Components -- Since you are writing code in Visual Basic 6 (or any language that suppports COM) you have access to many more forms controls, including third party controls that serve a specified purpose. You also get to work with VB6 forms, which include features like menu bars, command bars, and status bars not avaiable to VBA UserForms.
• Callable Functions In The Add In -- With Excel 2002, Automation Add Ins were added which allow you to call functions in the DLL directly from worksheet cells.

If you are going to deploy your COM Add-In to users running Excel 2007, see COM Add-Ins In Excel 2007 for information about using CAIs in Excel 2007.

Example COM Add In

To understand how to write and deploy a COM Add In, we will build a simple CAI that has the following features:

• Support For Excel and PowerPoint, versions 2003 and 2007.
• Trap Application Events for both Excel and PowerPoint.
• Several function library classes with functions that can be called directly from worksheet cells (not applicable to the PowerPoint implementation).
• A structure that allows functions in the library classes to be called from VBA code.

The CAI won't actually do much, but will illustrate nearly everything you need to learn about COM Add Ins. It will create an item on the Tools menu in both Excel and PowerPoint that displays a "Hello World" message box. It will also include two classes, Multiplication and Division, that will contain functions that can be called directly from worksheet cells (Excel only, of course). The object model will also be structured so that you can reference the CAI's classes and call their functions from within your VBA code.

Getting Started

First, create a new folder such as C:\MyCOMAddIn in which all the source code will be stored. Next, open Visual Basic 6 and choose ActiveX DLL in the New Project dialog. Change the Project Name to XLPPT. Change the name of the existing class module to ConnectExcel. Add a new class module to the project and name it ConnectPowerPoint.

The next step is to add the type library references required to work with Excel and PowerPoint. From the Project menu choose References, click the Browse button and paste in each of the following file names, clicking Open after each file name. Note that your exact paths may differ from those shown below.

• C:\Program Files\Common Files\microsoft shared\OFFICE11\MSO.DLL
• C:\Program Files\Office2003\OFFICE11\EXCEL.EXE
• C:\Program Files\Office2003\OFFICE11\MSPPT.OLB
• C:\Program Files\Common Files\Designer\MSADDNDR.DLL

Your project should now have the following references. Other references are not required for a basic COM Add-In, but may be necessary by your code. The versions shown below are for Office 2003. Your version number will be different if you are using a different version of Office or Excel.

• Visual Basic For Applications
• Visual Basic runtime objects and procedures
• Visual Basic objects and procedures
• Microsoft Add In Designer
• OLE Automatioln
• Microsoft Excel 11.0 Object Library
• Microsoft Office 11.0 Object Library
• Microsoft PowerPoint 11 .0 Object Library

Events And User Interface Controls

Next, we'll create two classes that will handle events and user interface controls from Excel and PowerPoint. Insert a class module and name it CExcelEvents. Insert another class module and name it CPowerPointEvents. Next, insert a new module, name it modGlobals. In the it modGlobals module, insert the following code:

Public XL As Excel.Application
Public PPT As PowerPoint.Application
Public ExcelEvents As CExcelEvents
Public PowerPointEvents As CPowerPointEvents
Public ThisCAI As Office.COMAddIn
Public Const C_EXCEL_TOOLS_MENU_ID As Long = 30007
Public Const C_POWERPOINT_TOOLS_MENU_ID As Long = 30007

Now we turn our attention to the CExcelEvents class. Copy the following code to the CExcelEvents class module.

'''''''''''''''''''''''''''''''''''''''''
' collection to hold controls
''''''''''''''''''''''''''''''''''''''''' 
Private pControlsColl As Collection

'''''''''''''''''''''''''''''''''''''''''
' application for events
''''''''''''''''''''''''''''''''''''''''' 
Private WithEvents XLApp As Excel.Application


'''''''''''''''''''''''''''''''''''''''''
' user interface controls
''''''''''''''''''''''''''''''''''''''''' 
Private WithEvents pMenuItem1 As Office.CommandBarButton

Private Sub Class_Initialize()
    Set pControlsColl = New Collection
    Set XLApp = modGlobals.XL
    SetupControls
End Sub

Private Sub Class_Terminate()
    ''''''''''''''''''''''''''''''''''''''
    ' Get rid of any existing controls.
    ''''''''''''''''''''''''''''''''''''' 
    Dim Ctrl As Office.CommandBarControl
    Do Until pControlsColl.Count = 0
        pControlsColl(1).Remove
        pControlsColl.Remove 1
    Loop
    Set XLApp = Nothing
End Sub

Private Sub SetupControls()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Setup one control. Repeat this code for all other
    ' user interface elements.
    '''''''''''''''''''''''''''''''''''''''''''''''''''' 
    Set pMenuItem1 = XLApp.CommandBars.FindControl(Id:=C_EXCEL_TOOLS_MENU_ID).Controls.Add( _
        Type:=msoControlButton, temporary:=True)
    With pMenuItem1
        .BeginGroup = True
        .Caption = "Click Me Excel"
    End With
    pControlsColl.Add pMenuItem1
End Sub

Private Sub pMenuItem1_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    MsgBox "Menu Item Click From Excel COM Add In"
End Sub

Private Sub XLApp_NewWorkbook(ByVal Wb As Excel.Workbook)
    MsgBox "New Workbook From AddIn: " & Wb.Name
End Sub

We'll explain the features of this code later. First, we need to set up the CPowerPointEvents class. This will be almost identical to CExcelEvents, except it will refer to PowerPoint not Excel. Paste the code below in to the CPowerPointEvents class module.

Option Explicit


'''''''''''''''''''''''''''''''''''''''''
' collection to hold controls

''''''''''''''''''''''''''''''''''''''''' 
Private pControlsColl As Collection

'''''''''''''''''''''''''''''''''''''''''
' application for events
''''''''''''''''''''''''''''''''''''''''' 
Private WithEvents PPTApp As PowerPoint.Application


'''''''''''''''''''''''''''''''''''''''''
' user interface controls
'''''''''''''''''''''''''''''''''''''''''
Private WithEvents pMenuItem1 As Office.CommandBarButton

Private Sub Class_Initialize()
    Set pControlsColl = New Collection
    Set PPTApp = modGlobals.PPT
    SetupControls
End Sub

Private Sub Class_Terminate()
    ''''''''''''''''''''''''''''''''''''''
    ' Get rid of any existing controls.
    ''''''''''''''''''''''''''''''''''''' 
    Dim Ctrl As Office.CommandBarControl
    Do Until pControlsColl.Count = 0
        pControlsColl(1).Remove
        pControlsColl.Remove 1
    Loop
    Set XLApp = Nothing
End Sub

Private Sub SetupControls()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Setup one control. Repeat this code for all other
    ' user interface elements.
    '''''''''''''''''''''''''''''''''''''''''''''''''''' 
    Set pMenuItem1 = PPTApp.CommandBars.FindControl(Id:=C_POWERPOINT_TOOLS_MENU_ID).Controls.Add( _
        Type:=msoControlButton, temporary:=True)
    With pMenuItem1
        .BeginGroup = True
        .Caption = "Click Me PowerPoint"
    End With
    pControlsColl.Add pMenuItem1
End Sub

Private Sub pMenuItem1_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    MsgBox "Menu Item Click From PowerPoint COM Add In"
End Sub

Private Sub PPTApp_NewPresentation(ByVal Pres As PowerPoint.Presentation)
    MsgBox "New Presentation from COM Add In: " & Pres.Name
End Sub

The Connection Classes

Now we need to set up the Connect classes. Open the ConnectExcel module and paste in the following code:

Option Explicit
Implements AddInDesignerObjects.IDTExtensibility2


Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)
' not used but required by Implements.
End Sub

Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)
' not used but required by Implements.
End Sub

Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, _
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
    ByVal AddInInst As Object, custom() As Variant)
	''''''''''''''''''''''''''''''
	' Called automaticaly when Excel loads the CAI.
	'''''''''''''''''''''''''''''' 
    Set XL = Application
    Set ThisCAI = AddInInst
    Set ExcelEvents = New CExcelEvents
End Sub

Private Sub IDTExtensibility2_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
	''''''''''''''''''''''''''''''
	' Called automaticaly when Excel unloads the CAI.
	'''''''''''''''''''''''''''''' 
    Set XL = Nothing
    Set ThisCAI = Nothing
    Set ExcelEvents = Nothing
End Sub

Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)
' not used but required by Implements. 
End Sub

This code Implements the IDTExtensibility2 interface, which provides the linkage betweeen the host application (e.g., Excel) and this add in. When you implement an interface, you must include all the properties and methods of that interface, even if you don't use them. We put a comment in the unused methods to prevent the compiler from eliminating empty procedures. The OnConnection event is the most important event. It is called when the add in is loaded by the host application. In this event, we set the XL variable (declared in modGlobals module) to the Application parameter, which is a reference to the host application. We set ThisCAI (also declared in modGlobals) to the AddInInst parameter. It refers to this instance of the Add In.

Finally, we set ExcelEvents to a new instance of CExcelEvents. The Initialize event, which runs automatically when the an object is created from the class, carries out two actions:

• Sets its own XLApp variable, declared WithEvents, so we can recieve events from the Excel application.
• It calls a procedure called SetupControls that create an item in the Tools menu. In your own add in you should declare all the control variables similar to the way pMenuItem1 is declared, and provide an event procedure for each control. Each control should be added to the pControlsColl collection so they will be properly removed when the add in is unloaded.

The code for the ConnectPowerPoint is nearly identical to the code for ConnectExcel:

Option Explicit
Implements AddInDesignerObjects.IDTExtensibility2


Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)
' not used but required by Implements. 
End Sub

Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)
' not used but required by Implements. 
End Sub

Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, _
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
    ByVal AddInInst As Object, custom() As Variant)
Set PPT = Application
Set ThisCAI = AddInInst
Set PowerPointEvents = New CPowerPointEvents
End Sub

Private Sub IDTExtensibility2_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
    Set XL = Nothing
    Set ThisCAI = Nothing
    Set PowerPointEvents = Nothing
End Sub

Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)
' not used but required by Implements. 
End Sub

Setting Up The Add In In The Registry

At this point, the add in is ready to use. From the File menu, choose Make to compile the DLL. If this is successful, you need to register the DLL with Windows. To do this, you must be logged on as an account with administrative privileges. Go to the Windows Start menu, choose Run and enter the following and click OK:
RegSvr32 "C:\MyCOMAddIn\XLPPT.dll"
Next, you must modify the registry to inform Excel that the add in is available. On the Windows Start menu, choose Run and enter RegEdit to run the Registry Editor program. If you want the add in to be available only to you, the current user, expand the HKEY_CURRENT_USER key. If you want to make the add in available to all users of the machine, expand the HKEY_LOCAL_MACHINE key. With the appropriate key expanded, find Software then, under that, Microsoft, then Office then Excel. If there is a key named AddIns expand it. If there is no key named AddIns, create a new key named AddIns. Under the AddIns key, create a key name XLPPT.ConnectExcel. In the XLPPT.ConnectExcel key, create a String Value named FriendlyName and give it a value to be displayed in the COM Add Ins dialog. Create another String Value named Description and enter a short description of the add in. Finally, create a DWORD value named LoadBehavior and assign it a value of 3. Repeat this process for the PowerPoint key under the Office key.

If you configure the add in in the HKEY_LOCAL_MACHINE region of the registry, it will not show up in the COM Add Ins dialog.

Creating Callable Functions

To fully complete the COM Add In, we want to create functions that can be called directly from worksheet cells or from within VBA. Create a class named Multiplication and enter the following code:

Option Explicit

Public Function Times10(D As Double) As Double
    Times10 = D * 10
End Function

Public Function Times100(D As Double) As Double
    Times100 = D * 100
End Function

Next, create a class named Division and enter the following code:

Option Explicit

Public Function DivideBy2(D As Double) As Double
    DivideBy2 = D / 2
End Function

Public Function DivideBy3(D As Double) As Double
    DivideBy3 = D / 3
End Function

You can add additional classes to contain groups of other related functions. At this point, you can call your functions directly from worksheet cells. Note that Automation Add In were added in Excel 2002, so they are not available in Excel 97 or 2000. In Excel, go to the Tools menu, choose Add Ins (not COM Add Ins) and click the Automation button to display the list of available automation components. Scroll down in the list until you find XLPPT.Division and click OK. Click the Automation button again and choose XLPPT.Multiplication from the list. Both of these items will appear in the list of standard Excel AddI Ins. You can call the functions directly from worksheet cells, such as
=Times10(1234).

Creating The Object Model For VBA

A few additional steps are required to make the functions in the class libraries Division and Multiplication available to VBA code. In the ConnectExcel class, enter the following code immediately after the Implements statement.

Private DivObj As XLPPT.Division
Private MultObj As XLPPT.Multiplication

Public Property Get Division() As XLPPT.Division
    Set Division = DivObj
End Property

Public Property Get Multiplication() As XLPPT.Multiplication
    Set Multiplication = MultObj
End Property

Private Sub Class_Initialize()
    Set DivObj = New XLPPT.Division
    Set MultObj = New XLPPT.Multiplication
End Sub

This code declares to variable of the Division and Multiplication class types. When the ConnectExcel class is created, the Initialize method is executed, and this creates new instances of the Division and Multiplication classes. These objects can be referenced by the Division and Multiplication properties. Insert the code above in to the ConnectPowerPoint class module.

To use the functions in your VBA code, first create a reference to the Add In library. On the Tools menu, choose References and check the entry for XLPPT. Once this reference is in place, you can use code like the following:

Dim D1 As Double
Dim D2 As Double
Dim Functions As New XLPPT.ConnectExcel
D1 = 123
D2 = Functions.Multiplication.Times100(D1)
Debug.Print D2

Additional Information

You can download the complete Visual Basic 6 source code here. The following pages are related to COM Add Ins and Automation Add Ins:

• Adding The COM Add In Menu Item
• Automation Add Ins
• COM Add Ins Installation And Security
• DLL Name Of A COM Add In
• COM Add-Ins In Excel 2007

This page last updated: 8-January-2008