How To Create Office COM Add-Ins by Using VBA and Office Developer (306130)


The information in this article applies to:

  • Microsoft Office XP Developer
  • Microsoft Office XP Professional
This article was previously published under Q306130

IN THIS TASK

SUMMARY

This article describes how to display a UserForm that welcomes you when you start Microsoft Excel or Microsoft Word. Component Object Model (COM) add-ins provide a means to centralize common code in a compiled dynamic-link library (DLL) that can be easily implemented in any Office application (for example, you can access Microsoft Outlook contacts from Excel, Word, and Microsoft Access). The easiest way to develop a COM DLL in Microsoft Office is to work with an add-in project, which provides a designer and a reference to the IDTExtensibility2 library.

back to the top

Requirements

This article assumes that you are familiar with the following topics:

  • Creating Visual Basic for Applications (VBA) procedures in an Office XP application.
  • Working with UserForms in VBA.
  • Customizing Office XP toolbars.
back to the top

Create an Add-In Project

You can create an add-in project in any Microsoft Office 2000 or Microsoft Office XP application when you have the Microsoft Office Developer 2000 or Microsoft Office XP Developer tools installed. This example uses Microsoft Word 2002.
  1. Start Microsoft Word 2002.
  2. From the Tools menu, point to Macro, and then click Visual Basic Editor.
  3. In the Visual Basic Editor (VBE), click New Project on the File menu.
  4. In the New Project dialog box, click Add-In Project. This automatically provides an Add-In Designer.
  5. If the Properties window is not visible, click Properties Window on the View menu.
  6. In the Properties window, rename the project from AddInDesigner1 to WelcomeAddIn.
  7. From the Insert menu, click UserForm to insert a UserForm.
  8. From the toolbox, add a label and a command button to the UserForm.
  9. In the Properties window, change the attributes of the controls to match the values in the following table:
    ControlPropertiesValue
    LabelNamelblMessage
    CaptionWelcome
    Font14pt
    AutoSizeTrue
    CommandButtonNamecmdOK
    CaptionOK
    UserFormNamefrmWelcome

  10. From the Insert menu, click Module to insert a standard module.
  11. Add the following global string variable declaration to the standard module:
    Public gstrUserName As String
'This variable will be used to store the name of the user as defined
'in the Options dialog boxes (from the Tools menu) in Word and Excel.
  12. In the module, add the following code to create a public Sub procedure named DisplayForm to display the form on the screen:
    Public Sub DisplayForm()
  frmWelcome.Show vbModal
End Sub
  13. View the code window for the frmWelcome form, and add the following code to the UserForm_Initialize event to concatenate the label caption with the string variable:
    lblMessage.Caption = lblMessage.Caption & " " & gstrUserName
  14. Add the following code to the Click event for the CommandButton cmdOK to unload the form:
    Unload me
back to the top

Change the Properties of the Add-In Designer

Follow these steps to change the properties of the designer so that the add-in will start as soon as Microsoft Excel is started:
  1. In Project Explorer, double-click on the component to return to the Add-In Designer.
  2. On the General tab of the designer, change the properties of the designer to match the values in the following table:
    PropertyValue
    AddIn Display NameFunFormMessage
    AddIn DescriptionThis contains code that is specific to Excel
    ApplicationMicrosoft Excel
    Application VersionMicrosoft Excel 10.0
    Initial Load BehaviorStartup
back to the top

Implement the IDTExtensibility2 Events

  1. From the View menu, click Code to view the code module behind the designer.
  2. From the Tools menu, click References, and set a reference to the Microsoft Excel Object Library.
  3. In the Object drop-down list, click AddinInstance. In the Procedure drop-down list, click OnConnection.

    This event is useful for creating a CommandBar button or to associate code with an event such as an application event when the add-in is connected to the host application.
  4. Add the following code to the AddinInstance_OnConnection event procedure to store the user name in the global variable strUserName and to call the DisplayForm procedure:
    gstrUserName = Application.UserName
DisplayForm
  5. In the VBE, click Save, and name the project Welcome.vba.
back to the top

Add a Second Designer

In this procedure, you add a second designer to implement the same functionality in Microsoft Word.
  1. Create another Add-In Project in the VBE. AddInProject2 is created by default.
  2. In the Project Explorer, drag the new Add-In Designer interface (AddInDesigner1) into your existing add-in project. There will be two designers in the project.
  3. In Project Explorer, right-click AddInProject2, and then click Close Project to remove the project that you just created.
  4. When you receive the following message or similar
    Project AddInProject2 has been modified. Do you wish to save it?
    click No.
  5. Change the properties for the designer to specify Word 10 as the target application and to load at Startup.
  6. Add the following code to the new Word designer's AddinInstance_OnConnection event procedure, which is the same as the previously written code for the Excel designer's OnConnection event:
    gstrUserName = Application.UserName
DisplayForm
back to the top

Debug the Add-In

  1. If you prefer, add appropriate break points to the code.
  2. In the VBE, click Run Project from the Run menu to run the add-in.
  3. Start Microsoft Excel. When the project starts, you may see a dialog box for the AddIn Designer. Click the option to start an application, and browse to Excel.exe. If this dialog box does not appear, click Start, point to Programs, and then click Microsoft Excel to start Excel. Your UserForm should appear.
  4. If the COM Add-In Manager is not already available, add the COM Add-Ins command to a toolbar as follows:
    1. On the Tools menu, click Customize.
    2. In the Customize dialog box, on the Commands tab, drag the COM Add-Ins command from the Tools category onto a toolbar or onto the Tools menu CommandBarPopup object.
  5. Click the COM Add-Ins command bar to display the COM Add-In Manager (CAM). The list in the CAM displays all of the add-ins that are loaded.
  6. To test the OnConnection event, clear the Welcome Add-In check box in the CAM, and then click OK. Redisplay the CAM, select the Welcome check box, and then click OK. The UserForm should appear again.
  7. Close the host application (either Word or Excel), and click Stop Project from the Run menu in the VBE to stop running the project.
back to the top

Verify That the Add-In Works

Now you can compile the add-in and verify that it works on another computer.
  1. Save the project in the VBE. This is your editable version. The compiled state does not allow you to make modifications.
  2. From the File menu, click Make Welcome.DLL.
  3. Use the regsvr32 command to register the DLL as follows:
    1. From the Windows Start menu, click Run.
    2. In the Open text box, type the following text:

      regsvr32 <path>\Welcome.DLL

      where <path> is the file system path to the DLL.
  4. Click OK to confirm the registration of the DLL.
  5. Run Excel and/or Word. Your UserForm should appear. If you do not see your UserForm, click COM AddIns from the Tools menu, and then select the Welcome check box.
NOTE: The easiest way to distribute the DLL is to use the Package and Deployment Wizard.

back to the top

Troubleshooting

  • Make sure that you write short, efficient code for when the add-in is loaded into memory. For example, if your OnConnection event manipulates database data at startup, your users will think that the computer has stopped responding (hung).
  • Be careful when you use UserForms. If the user clicks on the host application, the user may think that he or she has lost the form because it is hidden behind the host application. Instead, make your forms modal to overcome this problem. To make a form modal, use the vbModal constant as follows: 
    frmMyUserForm.Show vbModal
    To see where this code fits in reference to this example, see the code in Step 12 of the Create an Add-In Project section.
back to the top

REFERENCES

Refer to the \Samples\Working with AddIns\VBA_COM_AddIn folder on the Office XP Developer CD for an AddIn template.

For additional information about creating CommandBar buttons, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deovrCreatingCommandBar.asp

For information about how to design an Outlook add-in, visit the MSDN Code Center at the following MSDN Web site:

http://msdn.microsoft.com/code/default.asp?url=/msdn-files/026/000/118/massmail_design_htm.asp


back to the top
Modification Type:MinorLast Reviewed:7/14/2004
Keywords:kbHOWTOmaster KB306130 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.