Walkthrough: Creating a COM Add-in with VBA

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Using this walkthrough, you can create a COM add-in using Microsoft® Visual Basic® for Applications (VBA). COM add-ins can extend your application's functionality without adding complexity for your user. Underneath the surface, a COM add-in is a dynamic-link library (DLL) that is registered to make it possible for Microsoft® Office XP applications to load and use it. Although an add-in can be written as an executable file (.exe), DLLs generally provide better performance than .exe files.

This walkthrough creates and runs a custom toolbar COM add-in using VBA in Microsoft® Excel. The tasks in this topic assume you are familiar with Office applications and VBA projects, as well as with debugging and running code.

Introduction

You can create COM add-ins in any language that supports COM, such as VBA, Visual Basic, and Microsoft® Visual C++®. Add-ins created with Office Developer are packaged automatically as DLLs and registered for loading in the host application. You can create a single add-in that can work in several applications. For more information, see Creating COM Add-ins for Multiple Applications.

This walkthrough follows a series of steps to create a COM add-in with VBA. The basic process for creating this COM add-in includes:

Configuring the Add-in Designer.
Writing code in the Add-in Designer.
Integrating a command bar control into the add-in.
Debugging and testing the COM add-in.
Making a DLL for the COM add-in.
Troubleshooting COM add-in development problems.

Configuring the Add-in Designer

Projects created with Add-in Designers provide the work area for developing your add-in. You can use Add-in Designers to create COM add-ins for use in VBA or any Office application. Each Add-in Designer in the project represents a separate add-in that can run in only one Office application. The resulting DLL can contain multiple add-ins that share forms, modules, and class modules but are targeted for different applications. This walkthrough uses Excel as the host Office application.

If you want your add-in to be available for multiple applications, you must add a separate Add-in Designer for each host application. You can share code through modules; however, in each project, you must reference the object model specific to each host application.

To configure the Add-in Designer

Open Microsoft Excel.
From the Tools menu, select Macros, and then select Visual Basic Editor.
From the File menu, select New Project, and then select Add-In Project.
Type the name Greeting Toolbar in the Addin Display Name text box, and type the description Toolbar add-in that launches a Hello World message in Excel in the Addin Description text box.
Select Microsoft Excel from the Application list.
Select Microsoft Excel 10.0 from the Application Version list.
Select Startup from the Initial Load Behavior list. For more information on the different types of initial load behavior, see Specifying Load Behavior.
From the Tools menu, select References, and make sure the following type-libraries are referenced for the project. (This list of references is required for this walkthrough. For your add-ins, be sure to select the type libraries required by each Office application that might use the add-in.)
- Visual Basic for Applications
- OLE Automation
- Microsoft Add-in Designer
- Microsoft Office 10.0 Object Library
- Microsoft Excel 10.0 Object Library
From the File menu, select Save AddInProject1.
In the Save Project As dialog box, enter the name Greetings, select a folder to save it in, and then click Save.

Writing Code in the Add-in Designer

After you have created the project and assigned values to the Add-in Designer, you can add the code that connects the add-in to the host application. This walkthrough shows you the typical procedures and events you must have for the add-in and host application to work together. The IDTExtensibility2 interface supplies the COM objects and events required for the two to connect. Then, your add-in can interface with the host application using the object model exposed by the host application. You can view the object model for your particular application in the Object Browser.

Code that is in the Add-in Designer handles the integration of the add-in with the host application. For example, code that runs when the add-in is loaded or unloaded resides in the Add-in Designer's module. If the add-in contains forms, the Add-in Designer also might contain code to display the forms.

To declare variables and set up procedure stubs

In the Project Explorer window, select AddInDesigner1, and then open the View menu, and click Code.
In the General Declarations section, reference the extensibility interface.
```
Implements IDTExtensibility2
```
Add the module-level variables that provide communication between the add-in and the host. The variable assigned As Application persists as long as the COM add-in is loaded, so all procedures can determine in what application the add-in is running currently. Because the WithEvents keyword is assigned to the cbbButton variable, the menu item's Click event procedure will be triggered when the user clicks the new menu item.
```
'Global object references
Public appHostApp As Application
Private WithEvents cbbButton As Office.CommandBarButton
```
In the Code Window, select IDTExtensibility2 from the Object list, and OnConnection from the Event list. This creates the OnConnection event procedure stub.

Note You must include the event procedure stub for each event provided by the IDTExtensibility2 interface. If you omit any of the event procedures, your project will not compile.
Add an event procedure stub for each of the following events:
- OnDisconnection
- OnStartupComplete
- OnBeginShutdown
- OnAddinsUpdate

Now, you can add the functionality for your add-in.

Integrating a Command Bar Control into the Add-in

If your COM add-in has a user interface, you can add code that displays a command bar that makes it possible for users to run your add-in. This walkthrough shows how to include code that creates a new command bar control (toolbar button or menu item) in the host application. When your add-in is loaded, the control is loaded as well, and the user can click the button or menu item to open and work with the add-in.

To create the command bar control

Find the OnConnection event procedure. Between the Private Sub and End Sub lines, add the code to create a new command bar control and assign it to the event-ready CommandBarButton object variable. The entire procedure will appear as the following:
```
Private Sub IDTExtensibility2_OnConnection(ByVal _
   Application As Object, ByVal ConnectMode As _
   AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst _
   As Object, custom() As Variant)

   'Store startup reference
   Set appHostApp = Application

   ' Add the commandbar
   Set cbbButton = CreateBar()
End Sub
```
If you are familiar with creating add-ins using Visual Basic, you might notice that you do not set a value for the command bar button OnAction property when creating an add-in in VBA. This is because the event is hooked up for you automatically.

Find the OnDisconnection event procedure. Between the Private Sub and End Sub lines, add code to remove the command bar control when the add-in is unloaded. The entire procedure will appear as the following:

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

   RemoveToolbar
   ' remove references to shutdown
   Set appHostApp = Nothing
   Set cbbButton = Nothing
End Sub

Wherever you generally store functions in your code, add the code for the function you called in the OnConnection procedure. This function creates the command bar, sets the properties for the command button, and provides for handling of error messages.

Public Function CreateBar() As Office.CommandBarButton
   ' Specify the command bar
   Dim cbcMyBar As Office.CommandBar
   Dim btnMyButton As Office.CommandBarButton

   On Error GoTo CreateBar_Err

   Set cbcMyBar = appHostApp.CommandBars.Add(Name:="GreetingBar")

   ' Specify the commandbar button
   Set btnMyButton = cbcMyBar.Controls.Add(Type:=msoControlButton, _
      Parameter:="Greetings")
   With btnMyButton
      .Style = msoButtonCaption
      .BeginGroup = True
      .Caption = "&Greetings"
      .TooltipText = "Display Hello World Message"
      .Width = "24"
   End With

   ' Display and return the commandbar
   cbcMyBar.Visible = True
   Set CreateBar = btnMyButton
   Exit Function

   CreateBar_Err:
   MsgBox Err.Number & vbCrLf & Err.Description
End Function

Add the code for the function you called in the OnDisconnection procedure. This function removes the command bar when the add-in is unloaded.
```
Private Function RemoveToolbar()
   appHostApp.CommandBars("GreetingBar").Delete
End Function
```
Add a click event procedure for the CommandBarButton object. This procedure will be called when the new command bar button is clicked. The following code displays a message to show that the click event is working:
```
Private Sub cbbButton_Click(ByVal Ctrl As _ 
   Office.CommandBarButton, CancelDefault As Boolean)
   MsgBox ("Hello World!")
End Sub
```
Save your project.

Now, your COM add-in is complete. The remaining steps are to debug and test the code to make sure it works as you expect and then to make the add-in into a DLL file that can be distributed and used on other computers that have Office XP installed.

Debugging and Testing the COM Add-in

When you are developing a COM add-in in VBA, you can debug the add-in by putting the project into run mode. With the project in run mode, you can load and use the COM add-in from within an Office application to test and debug it by using any of the VBA debugging tools.

To debug and test a COM add-in using VBA

Place any desired breakpoints, Stop statements, or watches in the code.
On the Run menu, click Run Project. In the Debugging dialog box, select Wait for components to be created, and click OK. This compiles your project, alerting you to any compilation errors, and then puts the project into run mode.
Verify that [Published] appears in the editor's title bar.

**Note **The add-in must be published to make it available to the host application.
Start a new instance of Excel. Because you set the load behavior of the add-in to Startup, the add-in loads as soon as you start the application, the OnConnection event occurs, and the Greetings button appears. Now, you can use the breakpoints and Stop statements that you added to debug the code.

If you click the Greetings button, the Hello World message will appear in front of the Visual Basic Editor — not in front of Excel — as long as you are running the project in the Editor. After you make the project into a DLL file, the message will appear in front of Excel.
When you are finished debugging and testing, open the Run menu, and click Stop Project. This clears up temporary files and registry entries and puts the project in the correct state to be made into a DLL file.

Making a DLL for the COM Add-in

After you have written and debugged your code, you can make your add-in into a DLL that can be deployed to other computers that have Office XP.

To package the COM add-in as a DLL in VBA

From the File menu, select Make Greetings.DLL.
Keep the file name Greetings in the Make Project dialog box, and choose a location where you want to save your project.
Click OK.

This step will create the COM add-in, add the appropriate registry entries, and make the COM add-in available for use in your Office host. When you create the add-in DLL, VBA uses the information you have given to the Add-in Designer to register the DLL as a COM add-in. VBA writes the name, description, and initial load behavior setting of the add-in to the registry. The host application of the add-in reads these registry entries and loads the add-in accordingly.

For information on distributing your COM add-in, see Deploying Your Application and Deploying COM Add-ins.

Troubleshooting Common Add-in Development Problems

While working in the development environment and switching between applications, you might encounter some error messages or unexpected behavior. Here are some common problems and their solutions.

Compile errors appear or Statement Completion does not work in the editor

Be sure the appropriate object libraries are referenced for the project.

Nothing happens when I run the project and open a new instance of the host application

Be sure that the word "Published" appears in the title bar of the add-in you are running. If not, you must run the project.
Click the window of each open application. Your object might be running, but it might be visible only after you click the first instance of the application that you opened.
Make sure you have specified and set the module-level variables properly.
In the host application, make sure your add-in is selected in the COM Add-Ins dialog box. You can customize the host application by adding the COM Add-ins command to a toolbar. This command opens the COM Add-Ins dialog box.

To add the COM Add-ins dialog box to a toolbar

Open the Tools menu, click Customize, and select the Commands tab. Under Categories, select Tools. Scroll down the Commands list until you find COM Add-Ins. Drag COM Add-Ins onto a toolbar to create a new button, and then close the Customize dialog box.
On the toolbar, click the COM Add-ins button you just added.
In the COM Add-Ins dialog box, verify the check box next to your add-in is selected. To unload the add-in, clear the check box.

**Note **If your add-in does not appear in the list, make sure that the project is running in the Visual Basic Editor.

The object appears in the host application but does not respond

Multiple instances of your object might be running. Your code for detecting and removing an existing object with that name might not be running. Check and debug your OnDisconnection code.