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 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.
- Start Microsoft Word 2002.
- From the Tools menu, point to Macro, and then click Visual Basic Editor.
- In the Visual Basic Editor (VBE), click New Project on the File menu.
- In the New Project dialog box, click Add-In Project. This automatically provides an Add-In Designer.
- If the Properties window is not visible, click Properties Window on the View menu.
- In the Properties window, rename the project from
AddInDesigner1 to WelcomeAddIn.
- From the Insert menu, click UserForm to insert a UserForm.
- From the toolbox, add a label and a command button to the
UserForm.
- In the Properties window, change the attributes of the
controls to match the values in the following table:
|
Label | Name | lblMessage | | Caption | Welcome | | Font | 14pt | | AutoSize | True | CommandButton | Name | cmdOK | | Caption | OK | UserForm | Name | frmWelcome |
- From the Insert menu, click Module to insert a standard module.
- 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.
- 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
- 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
- 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:
- In Project Explorer, double-click on the component to
return to the Add-In Designer.
- On the General tab of the designer, change the properties of the designer to
match the values in the following table:
|
AddIn Display Name | FunFormMessage | AddIn Description | This contains code that is
specific to Excel | Application | Microsoft Excel | Application Version | Microsoft Excel 10.0 | Initial Load Behavior | Startup |
back to the top
Implement the IDTExtensibility2 Events- From the View menu, click Code to view the code module behind the designer.
- From the Tools menu, click References, and set a reference to the Microsoft Excel Object
Library.
- 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. - 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
- 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.
- Create another Add-In Project in the VBE. AddInProject2 is
created by default.
- 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.
- In Project Explorer, right-click AddInProject2, and then click Close Project to remove the project that you just created.
- When you receive the following message or similar
Project AddInProject2 has been modified. Do you wish to
save it? click No. - Change the properties for the designer to specify Word 10 as the target application and to load at Startup.
- 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- If you prefer, add appropriate break points to the
code.
- In the VBE, click Run Project from the Run menu to run the add-in.
- 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.
- If the COM Add-In Manager is not already available, add the
COM Add-Ins command to a toolbar as follows:
- On the Tools menu, click Customize.
- 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.
- 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.
- 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.
- 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 WorksNow you can compile the add-in and verify that it works on
another computer.
- Save the project in the VBE. This is your editable version.
The compiled state does not allow you to make modifications.
- From the File menu, click Make Welcome.DLL.
- Use the regsvr32 command to register the DLL as follows:
- From the Windows Start menu, click Run.
- In the Open text box, type the following text:
regsvr32 <path>\Welcome.DLL where <path> is the file system path to the DLL.
- Click OK to confirm the registration of the DLL.
- 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
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: For information about how to design an Outlook add-in, visit the MSDN Code Center at the following MSDN Web site:
back to the top
Modification Type: | Minor | Last Reviewed: | 7/14/2004 |
---|
Keywords: | kbHOWTOmaster KB306130 kbAudDeveloper |
---|
|