Building a COM Add-In with Visual Basic .NET

 < Day Day Up > 

Just because Visual Basic .NET doesn’t have native support for COM components doesn’t mean that you can’t build COM add-ins with it. Like Visual Basic 6, Visual Basic .NET includes templates that help you to create your own add-ins.

The goal of this add-in is to put a new button on the Excel Standard toolbar (the one with the New, Open, Save, Print, and so on, buttons) and have it respond to a click with a message box.

Running the Shared Add-In Wizard

The easiest way to create an add-in is to use the Microsoft Visual Studio .NET Shared Add- in Wizard. This will create a new solution for you that includes all the pieces needed to run your add-in, including the logic to automatically add the registry keys.

The solution consists of two projects. The first project contains the code necessary to interact with the application, which you can use as a basis for building the add-in itself. The second project is an installation project, which will install the add-in including any necessary changes to the registry.

Follow these steps to create a new add-in solution:

  1. Create a New Project in Visual Studio .NET. When prompted to choose the type of project, expand the Other Projects icon and select Extensibility Projects as the Project Type and then select the Shared Add-In template. Enter values for the project’s Name and Location. Press the OK button to start the wizard.

    click to expand

  2. The wizard will prompt you to choose which language you want to use. You can choose from Microsoft Visual C#, Visual Basic, or Visual C++/ATL. Select Visual Basic, and press the Next button.

  3. On Page 2 of the wizard, you’ll be prompted to choose which applications will host the add-in.

    click to expand

  4. Step 3 of the wizard prompts you to enter the name of your add-in, along with the add-in’s description. This information will be displayed to users when they select the add-in from the COM Add-Ins dialog box.

  5. Step 4 prompts you to select whether or not you would like your add-in to load whenever the host application loads and whether or not the add-in should be made available to all users or just the user who installed it. If you’re not sure what values to use, just select both boxes.

  6. The last step allows you to review all of your selections. If you want to revise any of your choices, press the Back button until you reach the appropriate step. Otherwise, press the Finish button to create the new project.

    click to expand


    Developing Add-Ins That Span Multiple Office Applications
    It’s possible to develop an add-in that can be used with multiple Office applications. This can be very useful if you want to write a general-purpose add-in such as an image browser or a file locator. By using the application object passed to the add-in OnConnection method, you can determine which application called the add-in and make the appropriate modifications to the menus or information contained in the application.

Modifying the Template

Even though you selected Excel when you ran the wizard, you still need to explicitly add a reference to the Excel TypeLib in your Visual Basic .NET program. You can do this by choosing Project, Add Reference from the main menu. In the Add Reference dialog box, choose the COM tab and then select Microsoft Excel 11.0 Object Library. Finally, press the Select button and then OK to add the reference to your project.

click to expand

Because the code skeleton created with the Shared Add-In Wizard merely constructs a minimal IDTExtensibility2 interface, you’ll need to add your own code to make the add-in do something useful.

In this case, we need to declare a module-level variable that contains information about the button to be added on the toolbar. The following statement defines the MyButton object. Notice that the WithEvents keyword is included. When the button is pressed in Excel, the Click event associated with the MyButton object will be fired.

    Dim WithEvents MyButton As CommandBarButton 

The OnConnection routine is called by the host application to initialize the add-in. As you saw earlier in this chapter, the only real work required by this routine is to save an object reference to the application object. All the following code is automatically generated by the wizard. The applicationObject variable is also defined by the wizard as a module-level variable and is used to access the resources owned by the application hosting the add-in.

Public Sub OnConnection(ByVal application As Object, _
ByVal connectMode As Extensibility.ext_ConnectMode, _
ByVal addInInst As Object, ByRef custom As System.Array) _
Implements Extensibility.IDTExtensibility2.OnConnection

applicationObject = application
addInInstance = addInInst

End Sub


The Implements keyword indicates that this routine implements a routine found in a particular interface. In this case, you can see that the IDTExtensibility2.OnConnection routine is being referenced.

The OnStartupComplete routine is called after the add-in has been initialized, but before the user can start using the application. This is the best place to modify menus or make any other changes to Excel.

You can add a button to a toolbar with this code. First you create a new instance of the MyButton object by using the Add method associated with the Controls collection on a particular toolbar. Next set the Caption property to hold the name to be displayed on the button and set the Style property so that the caption is displayed on the button rather than on an icon.

Public Sub OnStartupComplete(ByRef custom As System.Array) _
Implements Extensibility.IDTExtensibility2.OnStartupComplete

MyButton = applicationObject.CommandBars("Standard").Controls.Add(1)
MyButton.Caption = "My Button"
MyButton.Style = MsoButtonStyle.msoButtonCaption
MyButton.Tag = "My Button"
MyButton.OnAction = "!<MyCOMAddin.Connect>"

MsgBox("Startup complete")

End Sub

You should then set the Tag property to indicate the name of the button, along with defining the OnAction property so that it points to this component.

Refer to Chapter 18, “Manipulating Menus and Toolbars,” for more information on how to create and manipulate menus and toolbars.

When the application is ready to end, you need to properly remove the button using code like the OnBeginShutdown routine. The button’s Delete method is used to actually remove the button from the toolbar. Then the resources associated with the button object are destroyed by setting the MyButton object to Nothing.

Public Sub OnBeginShutdown(ByRef custom As System.Array) _
Implements Extensibility.IDTExtensibility2.OnBeginShutdown

MyButton = Nothing

End Sub

Finally, the code associated with the button’s click event is called whenever My Button is pressed on the toolbar. As you can see in this example, the MyButton_Click routine merely calls the MsgBox routine to display a simple message on the screen. However, in a more complex add-in, routines like this will handle the interactions associated with the add-in for the user.

Private Sub MyButton_Click(ByVal Ctrl As _
Microsoft.Office.Core.CommandBarButton, _
ByRef CancelDefault As Boolean) Handles MyButton.Click

MsgBox("My button was clicked")

End Sub

Installing the Add-In

To install the add-in, you must first build it. Then you must build the installation program and finally run the installation program.

  1. Choose Build, Build Solution from the Visual Studio .NET main menu. This will compile your add-in.

  2. The next step is to build the installation program. This is done by right-clicking the Setup project in the Solution Explorer and choosing build from the context menu.


    The installation package created by the add-in wizard does not include the .NET Framework, which must be installed prior to installing your add-in. If you already have Visual Studio .NET installed on your computer, you also have installed the .NET Framework.

  3. Once the build is complete, right-click the Setup project again and choose Install from the popup menu. This will run the MyCOMAddinSetup Setup Wizard, which will guide the user through the steps required to install the add-in on the user’s computer.

    click to expand

You can verify that the add-in is properly installed by starting Excel. When the add-in runs, you’ll see a message box informing you that the add-in’s startup is complete. Then you should notice the My Button button on the Standard toolbar along with other buttons such as the New, Open, and Save buttons.

click to expand

Pressing the My Button button results in a simple message box that indicates that the My Button button was clicked.

In this chapter, you learned about add-ins and how you can incorporate them into an Excel application. Then you learned how to use the Add-Ins dialog box to incorporate an add-in into Excel. Finally you learned three different techniques to build an add-in: creating a workbook with a series of Excel macros and saving it as an .xla file; creating an automation add- in using Visual Basic 6; and creating a COM add-in using Visual Basic .NET. Each of these techniques has its strengths and weaknesses depending on how you plan to incorporate it into your application.

 < Day Day Up > 

Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: