The Code Net

Creating a COM Addin

written by Mark Rowlinson - Last updated Nov 2004

Introduction

COM addins were introduced in Office 2000. The alternative to a COM Addin is a standard addin such as an xla for Excel or Template for word. In essence COM addins are compiled dll's (dynamic link libraries) that use the COM interface to interact with Office. The fact that they are compiled means that they:

are more secure
run faster
are able to use controls and structures unavailable in VBA

The drawback though is that they a bit harder to:

create
edit
install

Before deciding whether to create a COM addin you should therefore weigh up which of the above you and your users value most highly.

Creating a COM Addin in VB6

Open VB6 and create a new project. In the list of available projects select the type Addin.

When the new project opens you will have a Form called frmAddin and a Designer object called Connect. Remove the form from the project as it is a sample form and we don't need it.

The next step is to set the addin up using the designer object. Double click on it and you will be presented with a Designer Form with several fields.

Addin Display Name - This is the name of your addin.
Addin Description - Give your addin a description so users know what it does
Application - The application you are creating the Addin for. For the purposes of this article we will create an Addin for Excel so select Excel.
Application Version - The version of the application you are creating it for.
Initial Load Behaviour - Whether the addin loads automatically when the application is started up or not. For this addin set this field to Startup. The addin will then load when Excel starts.

Setting References and Other Project properties

The next thing to do is to set the required references. By default VB adds a reference to the VB6 extensibility library i.e. it assumes we are creating an addin for VB6. We wish to remove this and replace it with the reference to Excel.

To do this goto Project/References, uncheck the VB Extensibility Library and then find the reference to the Microsoft Excel Object Model. Check the box next to it and click OK.

Next goto to Project/Properties (bottom of the Project menu) and set the name and description of the project. (The difference here between the name set in the designer is that as we are creating a standard COM/ActiveX dll it could be added to a reference to other projects as well as being used as an addin. The Description here is the one that will appear in the references list.) This would then be a good time to save the project.

More on the Designer

Right click on the designer object in the project explorer and select 'View Code' or click the 'View Code' button at the top of the project explorer.

You will notice there is already alot of code in here that was generated for the sample Addin. Most of this code can be removed from the module. The only 2 procedures you need to leave behind are:

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

And

Private Sub AddinInstance_OnDisconnection _ 
    (ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)

These 2 procedures relate to the starting up and closing down of the addin. The starting up procedure passes a reference to the application that loaded the addin. In our case this will be an Excel application object. We can store this and use it for manipulating Excel from within the addin. Add a standard code module to the project and add the following line of code:

Public xlApp As Excel.Application

Now go back to the OnConnection procedure. Remove the existing code and add:

Set xlApp=Application

This procedure is also the place to initialise any objects and create any menus/toolbars etc. Therefore add a procedure CreateToolbarButtons to your code module and call it from you OnConnection procedure. The completed procedure should now look something like the following:

 
Private Sub AddinInstance_OnConnection(ByVal Application As Object, _ 
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant) 
     
    'store the application 
    Set xlApp=Application 
     
    'set up our menus 
    CreateToolbarButtons 
     
End Sub

The OnDisconnection procedure is called when the addin is unloaded. It should therefore be used to remove any toolbars and destroy any objects etc. The procedure should therefore look something like:

 
Private Sub AddinInstance_OnDisconnection _ 
    (ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant) 
     
    'destroy xl Object reference 
    Set xlApp=Nothing 
     
    'removemenus 
    RemoveToolbarButtons 
     
End Sub

You should also now add a procedure to your code module called RemoveToolbarButtons.

This is the basic structure of a COM addin completed - not too hard really!

Handling Toolbar Events

In Excel VBA when you create a commandbar button you can set the OnAction property to the name of the procedure you wish to run when the button is clicked. With COM addins, although you can still do this, when the button is clicked the OnAction procedure will not be found if it resides within the dll. OnAction will only look in standard VBA code modules.

This means that to handle the events you have to use a class module and declare a reference to the object 'WithEvents'. You can then access the click event of button (other controls have different events available). Therefore add a class module to your project and name it cbEvents. Now add the following line:

Public WithEvents cbBtn As CommandBarButton

Now if you can select cbBtn in the top left dropdown and select the click event in the top right dropdown. The following code should result:

 
Private Sub cbBtn_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean) 
     
End Sub

Notice you are passed a reference to the control that caused the procedure to be called. We shall use this class module to handle the click event of all the buttons we create so we need to check which control was passed to determine the procedure to call. The easiest way to do this is to check the OnAction property. We can set this when we create the buttons to the name of the procedure we wish to call and use a select case statement to call that procedure e.g.

 
Select Case Ctrl.OnAction 
Case "Sub1" 
    Sub1 
Case "Sub2" 
    Sub2 
End Select

Finally, we need to set the CancelDefault property to true within this event to cancel Excel looking for the sub specified in the OnAction property which as discussed earlier it won't find!

The completed class module should therefore look something like:

 
Option Explicit 
 
Public WithEvents cbBtn As CommandBarButton 
 
Private Sub cbBtn_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean) 
     
    'supress errors 
    On Error Resume Next 
     
    'check onaction property 
    Select Case Ctrl.OnAction 
    Case "Sub1" 
        Sub1 
    Case "Sub2" 
        Sub2 
    End Select 
     
    'cancel Excel looking For the Sub specified In the OnAction property 
    CancelDefault = TRUE 
     
End Sub

You should now create your toolbar buttons in the usual way in your CreateToolbarButtons procedure remembering to use xlApp instead of Application to reference the commandbars collection. The only difference is that when you have created the button you should assign it to an instance of the class we just created. The easiest way to do this is to declare an instance of the class and of a collection at the top of the module i.e.

Dim ButtonEvent As cbEvents 
Dim ButtonEvents As Collection

Now at the start of your CreateToolbarButtons procedure create a new collection:

Set ButtonEvents=New Collection

Finally after each button you create create a new instance of cbEvents class and assign to ButtonEvent, assign the created button to the cBtn of the ButtonEvent object then add the ButtonEvent object to the collection. In code this becomes (assuming your newly created button is in an object btNew):

Set ButtonEvent=New cbEvents 
Set ButtonEvent.cBtn=btNew 
ButtonEvents.Add ButtonEvent

An example procedure is as follows:

 
Public Sub CreateToolbarButtons() 
     
    'to make sure the buttons we are about To add aren't added twice 
    'try To remove them first 
    RemoveToolbarButtons 
     
    'declare some variables 
    Dim cbBar As Office.CommandBar 
    Dim btNew As Office.CommandBarButton 
     
    'create a New collection 
    Set ButtonEvents=New Collection 
     
    'find the worksheet menu bar In excel (this Is the one 
    'with the file, edit, view etc. commands) 
    Set cbBar = xlApp.CommandBars("Worksheet Menu Bar") 
    'add a New button To the Tools menu 
    Set btNew = cbBar.Controls("Tools").Controls.Add(msoControlButton, , , , TRUE) 
    With btNew 
        .OnAction = "Sub1" 
        'set a unique tag To make our custom controls easy 
        'to find later To delete 
        .Tag = "COMAddinTest" 
        'set the tooltip text 
        .ToolTipText = "Calls Sub1" 
        'set the caption that appears In the menu 
        .Caption = "Sub1" 
    End With 
     
    'get a New instance of our cbevents class 
    Set ButtonEvent = New cbEvents 
    'now assign the button we created To it 
    Set ButtonEvent.cbBtn = btNew 
    ButtonEvents.Add ButtonEvent 
     
    'now add another button 
    Set btNew = cbBar.Controls("Tools").Controls.Add(msoControlButton, , , , TRUE) 
    With btNew 
        .OnAction = "Sub2" 
        'set a unique tag To make our custom controls easy 
        'to find later To delete 
        .Tag = "COMAddinTest" 
        'set the tooltip text 
        .ToolTipText = "Calls Sub2" 
        'set the caption that appears In the menu 
        .Caption = "Sub2" 
    End With 
     
    'get a New instance of our cbevents class 
    Set ButtonEvent = New cbEvents 
    'now assign the button we created To it 
    Set ButtonEvent.cbBtn = btNew 
    ButtonEvents.Add ButtonEvent 
     
     
End Sub

The RemoveToolbarButtons sub would then look something like:

 
Public Sub RemoveToolbarButtons() 
     
    Dim cbBar As CommandBar 
    Dim cbCtr As CommandBarControl 
     
    'supress errors - this Is important here As they may Not have been created 
    'yet Or may have been alreday deleted 
    On Error Resume Next 
    'need To remove button from the command bar 
    'first find the commandbar 
    Set cbBar = xlApp.CommandBars("Worksheet Menu Bar") 
    'not find the control using the tag we Set when creating 
    Set cbCtr = cbBar.FindControl(, , "COMAddinTest") 
    While Not cbCtr Is Nothing 
        'now delete it 
        cbCtr.Delete 
        Set cbCtr = cbBar.FindControl(, , "COMAddinTest") 
         
    Wend 
    'remove event handlers from memory 
    Set ButtonEvents = Nothing 
    Set ButtonEvent=Nothing 
End Sub

Conclusion

The above should hopefully have given you the knowledge required to create COM addins of your own. If there is anything you feel needs clarifying or you would like to see further discussion on please email suggestions@thecodenet.com.

Installing an Excel COM Addin

Registering the Dll

Dll's require registering with Windows in order to run. To do this you need to run regsvr32.exe passing it the filename of the dll. An easy way to do this is to download the script on the downloads page. Once you have run the script you'll be able to Register and Unregister dll's easily using the right click context menu.

NB: When you create the DLL in VB6 it will auto register it on the PC it was created on.

NB2: Also note that to remove or delete a dll you should unregister it first. You can again do this easily if you have ran my script but if not you need to run regsvr32.exe passing the /u switch.

Installing the Addin in Excel

As we set the initial load behaviour to 'Startup' in the designer the Addin should load automatically on reloading Excel. If startup isn't selected then you may need to load it manually. To do this you need to select the 'Com Addins' menu option in Excel.

This is often not available by default and needs to be added by going to Tools/Customise and dragging it on to the menu bar. On selecting the 'Com Addins' button the available Com addins will be listed. The Addin should be in the list with a checkbox next to it. This will be checked if it is loaded and unchecked if unloaded. You can also remove addins and browse for other addins not in the list.

Name:
Password:
Remember me?
Register

Login