Export (0) Print
Expand All
Around the World with Visual Basic
Asynchronous Method Execution Using Delegates
Building a Progress Bar that Doesn't Progress
Calling All Operators
Create a Graphical Editor Using RichTextBox and GDI+
Creating A Breadcrumb Control
Creating a Five-Star Rating Control
Creating and Managing Secondary Threads
Data Binding Radio Buttons to a List
Deploying Assemblies
Designing With Custom Attributes
Digital Grandma
Doing Async the Easy Way
Extracting Data from .NET Assemblies
Implementing Callbacks with a Multicast Delegate
Naming and Building Assemblies in Visual Basic .NET
Programming Events of the Framework Class Libraries
Programming I/O with Streams in Visual Basic .NET
Reflection in Visual Basic .NET
Remembering User Information in Visual Basic .NET
Advanced Basics: Revisiting Operator Overloading
Scaling Up: The Very Busy Background Compiler
Synchronizing Multiple Windows Forms
Thread Synchronization
Updating the UI from a Secondary Thread
Using Inheritance in the .NET World
Using the ReaderWriterLock Class
Visual Basic: Simplify Common Tasks by Customizing the My Namespace
What's My IP Address?
Windows Forms Controls: Z-order and Copying Collections
Expand Minimize

Tips and Tricks: Building Microsoft Office Add-ins with Visual C# .NET and Visual Basic .NET

Visual Studio .NET 2003
 

Robin Reynolds-Haertle
Visual Studio Team
Microsoft Corporation

September 2002

Summary: This article describes several techniques useful for creating Office add-ins with Visual Studio .NET. Topics include using the Office object models, C#-specific changes when using the Office object models, and debugging. Readers with some add-in or Visual Basic for Applications experience will gain the most from this article. (14 printed pages)

Contents

Introduction
Determining the Application Type
Adding Command Bars
Debugging the Add-in
Adding Buttons to Command Bars
Getting and Setting Object-Model Properties with C#
Adding Windows Forms to Your Add-in
Finding the Documentation for Office Applications
Optional Parameters in C# Add-ins
Security Concerns
Conclusion

Introduction

Building COM Add-Ins using Visual C# or Visual Basic .NET combines the rich object models of the Office applications and the .NET Framework with the language features of C# and Visual Basic. Combining COM objects with objects from the .NET Framework can present a few challenges. This article provides some guidance for combining these technologies when creating Office add-ins.

This article assumes you are comfortable with basic programming tasks, have some experience in programming Office macros in Visual Basic for Applications, and have perhaps created some add-ins before. The code in this article uses the Office XP object model. A good start if you have never created an Office add-in is the article Creating Office Managed COM Add-ins with Visual Studio .NET.

The tasks described in this article assume that you have created a shared add-in from the New Project dialog box in Visual Studio .NET. The New Project dialog box will guide you through the Extensibility Wizard and create the basic add-in and setup projects.

Determining the Application Type

If you are creating an add-in that loads into more than one Office application, you will probably want to determine which application your add-in is running in. The default add-in project has a reference to the Office library, but not to any of the specific application libraries. Before you can determine which application you are running, you need to add a reference to the applications your add-in might load into. Use the COM tab of the Add Reference dialog box to select and add references to each application.

The Connect class, which implements the IDTExtensibility2 interface, has an application parameter in the OnConnection event. This parameter represents the instance of the Office application that is running, but the parameter is of the System.Object type. To be useful, you need to cast that reference to the application type. That application type will vary depending on the Office application that the add-in loads into.

The SetApplicationFields method below sets two class fields. The method can be called from the OnConnection method of the Connect class.

  1. Add references to the Microsoft Excel Object Library and Microsoft Word Object Library to your project, using the COM tab of the Add Reference dialog box.
  2. Add two class fields to the Connect class to represent instances of the Word and Excel applications.
    ' Visual Basic
    Dim wordApp As Word.Application
    Dim excelApp As Excel.Application
    
    // C#
    Word.Application wordApp = null;
    Excel.Application excelApp = null;
    
  3. Add this method to the Connect class to set the Word and Excel application references. Only one field will be non-null when the application is loaded.
    ' Visual Basic
    Private Sub SetApplicationFields(ByVal application As Object)
        If TypeOf (application) Is Word.Application Then
            wordApp = CType(application, Word.Application)
            excelApp = Nothing
        ElseIf TypeOf (application) Is Excel.Application Then
            excelApp = CType(application, Excel.Application)
            wordApp = Nothing
        End If
    End Sub
    
    // C#
    private void SetApplicationFields(object application) 
    {
        if (application is Word.Application) 
        {
            wordApp = (Word.Application)application;
            excelApp = null;
        }
        else if (application is Excel.Application) 
        {
            excelApp = (Excel.Application)application;
            wordApp = null;
        }
    }
    
  4. Add code to the OnConnection method to set the fields when the add-in loads.
    ' Visual Basic
    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
    
        ' Setup code for the add-in.
        SetApplicationFields(application)
        ' More setup code for the add-in.
    End Sub
    
    // C#
    public void OnConnection(object application, 
    Extensibility.ext_ConnectMode 
    connectMode, object addInInst, ref System.Array custom)
    {
        // Setup code for the add-in.
        SetApplicationFields(application);
        // More setup code for the add-in.
    }
    

Adding Command Bars

New command bars are added to the application through the CommandBars collection of the application object. The following method adds a command bar to the CommandBars collection. When you call this method, you need to pass in a reference to the running instance of the Office application. In the previous section, the Word application reference was held by the class field wordApp. The use of the missing variable is explained in the section "Optional Parameters in C# Add-ins."

To put a command bar in your application

  1. Add the following method to the Connect class.
    ' Visual Basic
    Private Function AddWordToolbar(ByVal word As Word.Application, _
        ByVal toolbarName As String) As Microsoft.Office.Core.CommandBar
    
        Dim toolBar As Microsoft.Office.Core.CommandBar = Nothing
        Try
            ' Create a command bar for the add-in
            toolBar = CType(word.CommandBars.Add(toolbarName, _
                Microsoft.Office.Core.MsoBarPosition.msoBarTop, , True), _
                Microsoft.Office.Core.CommandBar)
            toolBar.Visible = True
            Return toolBar
        Catch
            ' Add exception handling here.
            Return Nothing
        End Try
    End Function
    
    // C#
    private Microsoft.Office.Core.CommandBar AddWordToolbar(
    Word.Application word, string toolbarName) 
    {
        Microsoft.Office.Core.CommandBar toolBar = null;
        try 
        {
            // Create a command bar for the add-in
            object missing = System.Reflection.Missing.Value;
            toolBar = (Microsoft.Office.Core.CommandBar)
                wordApp.CommandBars.Add(toolbarName,
                Microsoft.Office.Core.MsoBarPosition.msoBarTop, 
                missing, true );
            toolBar.Visible = true;
            return toolBar;
        }
        catch 
        {
            // Add exception handling here.
            return null;
        }
    }
    
  2. Add this code to the OnConnection method to create the toolbar when the add-in loads.
    ' Visual Basic
    Dim toolbar As Microsoft.Office.Core.CommandBar = Nothing
    If Not wordApp Is Nothing Then
        toolbar = AddWordToolbar(wordApp, "Some useful toolbar.")
    End If
    
    // C#
    Microsoft.Office.Core.CommandBar toolBar = null;
    if (wordApp != null)
    {
        toolBar = AddWordToolbar(wordApp, "Some useful toolbar.");
    }
    

If you are using Visual Basic and have Option Strict set to Off, you can use its late-binding properties and skip this step of casting the application reference in the OnConnection method to the correct application type. Both Word and Excel have a CommandBars property that you use directly from the application reference through late-binding. For an example, see the article Creating Office Managed COM Add-ins with Visual Studio .NET.

Debugging the Add-in

You can build, install, run, and debug your add-in from the development environment. To do this, you need to change some of the default settings for an add-in project.

To adjust your default settings for add-in projects

  1. In Solution Explorer, right-click the add-in project and choose Properties.
  2. On the left side of the Properties dialog box, select Configuration Properties, then select Debugging underneath it. Set the Debug Mode to Program and set Start Application to the path for the Word executable file. The default startup program is Visual Studio .NET, but the add-in will not run in Visual Studio .NET.
  3. Build the add-in project and the setup project.
  4. In Solution Explorer, right-click the setup project and choose Install. For a discussion of whether you want to install the add-in for everyone or just yourself, see PRB: Visual Studio .NET Shared Add-in Is Not Displayed in Office COM Add-ins Dialog Box (Q316723)
  5. Press F5 to start Word and load the add-in. If you do this operation with only the code shown so far in this article, the toolbar appears in Word, but there are no buttons on it yet.

Exceptions thrown by your code are trapped by the Office application and will not break into the debugger automatically. To improve the debugging behavior you can break on exceptions or use try/catch blocks extensively. Be aware that if you break on all exceptions, the debugger may break on exceptions that are not relevant to the application.

To break on exceptions

  1. From the Debug menu, select Exceptions.
  2. Select the Common Language Runtime Exceptions node.
  3. In the When the exception is thrown group, select Break into the debugger.

Office disables add-ins that have failed, and Office interprets all managed add-ins to be the same because they share the mscoree.dll. Therefore, when an add-in fails, you will need to uninstall the add-in that failed.

Adding Buttons to Command Bars

Once you have created a toolbar, you can add buttons or other controls to it. For each button you want to add to the toolbar, you need to add a reference variable to the Connect class, and a method to handle the click event of the button. Implementing the button reference as a class member insures that the button will have the same lifespan as the application and not be destroyed by garbage collection routines while the add-in is still loaded and the button is still needed.

For each new button, you can also specify the FaceID, an integer that maps to one of the icons in the Office library. Hundreds of icons that you can use on your buttons are shipped with Office. The following article includes an Excel spreadsheet that is programmed to display the icons and their corresponding face IDs, Finding FaceID Numbers for Your Microsoft Office 97 Toolbars.

Because you may be adding several buttons to the button bar, it may be worthwhile to add a single method to the Connect class that instantiates a new button, adds it to the toolbar, and associates it with the click event handler.

The following code uses delegates added to the click event of the command bar button. You can also use the OnAction property to set the event handler for the button. For an example, see the article Creating Office Managed COM Add-ins with Visual Studio .NET.

To add buttons to a command bar and create event handlers

  1. Add a reference to System.Windows.Forms to use the Clipboard object.
  2. Add the following code to create buttons. This method creates and returns a reference to a new button. It sets the caption, icon (FaceId) and event handler for the Click event.
    ' Visual Basic
    Private Function MakeANewButton(ByVal commandBar As _
        Microsoft.Office.Core.CommandBar, ByVal caption As String, _
        ByVal faceID As Integer, ByVal clickHandler As _
        Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler) _
        As Microsoft.Office.Core.CommandBarButton
        Try
            Dim newButton As Microsoft.Office.Core.CommandBarButton
            newButton = CType(commandBar.Controls.Add( _
                Microsoft.Office.Core.MsoControlType.msoControlButton), _
                Microsoft.Office.Core.CommandBarButton)
            newButton.Caption = caption
            newButton.FaceId = faceID
            AddHandler newButton.Click, clickHandler
            Return newButton
        Catch ex As System.Exception
            ' Add code here to handle the exception.
            Return Nothing
        End Try
    End Function
    
    // C#
    private Microsoft.Office.Core.CommandBarButton MakeANewButton(
        Microsoft.Office.Core.CommandBar commandBar, string caption, 
        int faceID, 
        Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler 
        clickHandler ) 
    {
        object missing = System.Reflection.Missing.Value;
        try 
        {
            Microsoft.Office.Core.CommandBarButton newButton;
            newButton = (Microsoft.Office.Core.CommandBarButton)
                commandBar.Controls.Add(
                Microsoft.Office.Core.MsoControlType.msoControlButton,
                missing, missing, missing, missing);
            newButton.Caption = caption;
            newButton.FaceId = faceID;
            newButton.Click += clickHandler;
            return newButton;
        }
        catch (System.Exception ex) 
        {
            // Add code here to handle the exception.
            return null;
        }
    }
    
  3. Add the following declarations to the Connect class for the button references.
    ' Visual Basic
    Dim insertText As Microsoft.Office.Core.CommandBarButton
    Dim styleText As Microsoft.Office.Core.CommandBarButton
    
    // C#
    Microsoft.Office.Core.CommandBarButton insertText;
    Microsoft.Office.Core.CommandBarButton styleText;
    
  4. Add these methods to the Connect class. They will serve as event handlers for the click events of the buttons. This code uses the class fields wordApp and excelApp.
    ' Visual Basic
    Public Sub insertText_Click(ByVal barButton As _
        Microsoft.Office.Core.CommandBarButton, ByRef someBool As Boolean)
        Dim text As String = ""
        Dim data As System.Windows.Forms.IDataObject = _
        System.Windows.Forms.Clipboard.GetDataObject()
        If data.GetDataPresent(System.Windows.Forms.DataFormats.Text) Then
            text = data.GetData( _
                System.Windows.Forms.DataFormats.Text).ToString()
            If (Not wordApp Is Nothing) Then
                Me.wordApp.ActiveWindow.Selection.InsertBefore(text)
            ElseIf (Not excelApp Is Nothing) Then
                Me.excelApp.ActiveCell.Value = text
            End If
        End If
    End Sub
    
    Public Sub styleText_Click(ByVal barButton As _
        Microsoft.Office.Core.CommandBarButton, ByRef someBool As Boolean)
        Dim code As Object = "Code"
        If (Not wordApp Is Nothing) Then
            Me.wordApp.ActiveWindow.Selection.Style = code
        ElseIf (Not excelApp Is Nothing) Then
            Me.excelApp.ActiveCell.Style = code
        End If
    End Sub
    
    // C#
    public void insertText_Click(Microsoft.Office.Core.CommandBarButton
        barButton, ref bool someBool)
    {
        string text = "";
        System.Windows.Forms.IDataObject data = 
            System.Windows.Forms.Clipboard.GetDataObject();
        if (data.GetDataPresent(System.Windows.Forms.DataFormats.Text))
        {
            text = data.GetData(System.Windows.Forms.DataFormats.Text).
                ToString();
            if (wordApp != null) 
            {
                this.wordApp.ActiveWindow.Selection.InsertBefore(text);
            }
            else if (excelApp != null)
            {
                this.excelApp.ActiveCell.Value2 = text;
            }
        }
    }
    
    public void styleText_Click(Microsoft.Office.Core.CommandBarButton
        barButton, ref bool someBool)
    {
        object code = "Code";
        if (wordApp != null) 
        {
            this.wordApp.ActiveWindow.Selection.set_Style(ref code);
        }
        else if (excelApp != null) 
        {
            this.excelApp.ActiveCell.Style = code;
        }
    }
    
  5. Add the following code to the OnConnection method to create the buttons when the add-in loads. To call this method, you will need to have a reference to the command bar that will host the buttons. In this example, the AddWordToolbar method shown above is used to create a toolbar for hosting the button.
    ' Visual Basic
    ' Create a toolbar.
    Dim toolbar As Microsoft.Office.Core.CommandBar = Nothing
    If Not wordApp Is Nothing Then
        toolbar = AddWordToolbar(wordApp, "Some useful toolbar.")
    End If
    
    ' Create a button to add text.
    insertText = MakeANewButton(toolbar, "Insert text", 1044, _
        AddressOf insertText_Click)
    
    ' Create a button to style selected text.
    styleText = MakeANewButton(toolbar, "Style text", 1081, _
        AddressOf styleText_Click)
    
    // C#
    // Create a toolbar.
    Microsoft.Office.Core.CommandBar toolBar = null;
    if (wordApp != null)
    {
        toolBar = AddWordToolbar(wordApp, "Some useful toolbar.");
    }
    
    // Create a button to add text.
    insertText = MakeANewButton(toolBar, "Insert text", 1044, 
        new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler
        (insertText_Click));
    
    // Create a button to style selected text.
    styleText = MakeANewButton(toolBar, "Style text", 1081, 
        new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler
        (styleText_Click));
    

Getting and Setting Object-Model Properties with C#

In the previous section, the style was set in Visual Basic using the Style property of the ActiveCell. In the C# code the style was set using the set_Style method. When you need to make this change, Intellisense will suggest the correct method to use. The method takes a System.Object parameter by reference. The System.Object type can hold any type of data.

Some object model properties can also be accessed using the System.Type.InvokeMember method. For an example, see HOWTO: Use Automation to Get and Set Office Document Properties with Visual C# .NET.

Adding Windows Forms to Your Add-in

Adding Windows Forms to the add-in is no different than in any other .NET-connected application. The appropriate project references needed for Windows applications are added along with the new form.

The interaction of the form with Office depends on whether the form is modeless. Modeless form cannot interact directly with the Office object model. Instead, you will need to design your application such that the modeless form returns information to the calling code. The calling code may then interact with the object model. Generally, modal forms will be able to interact with the Office object model.

To add an input form to the application

  1. Right-click the add-in project, point to Add, then choose Add Windows Form.
  2. Enter StyleSelection as the name of the form.
  3. Add a ListBox control to the new form and use the Items collection editor to add three entries for Text, Code, and Link. Set the Modifiers property of the ListBox control to Public.
  4. Add two buttons. Set the following properties as shown in the following table.
    Button Property Value
    Button1 Name OK
      DialogResult OK
      Text OK
    Button2 Name Cancel
      DialogResult Cancel
      Text Cancel
  5. Add this method to the Connect class. This method creates an instance of the form, displays it, checks the DialogResult and changes the color of the current selection accordingly.
    ' Visual Basic
    Public Sub changeStyle_Click(ByVal barButton As _
        Microsoft.Office.Core.CommandBarButton, ByRef someBool As Boolean)
        Dim selectionForm As New StyleSelection()
        Dim newStyle As Object = ""
        If selectionForm.ShowDialog() = _
            System.Windows.Forms.DialogResult.OK Then
            newStyle = selectionForm.ListBox1.Text
        End If
    
        If Not wordApp Is Nothing Then
            Me.wordApp.ActiveWindow.Selection.Style = newStyle
        ElseIf Not excelApp Is Nothing Then
            Me.excelApp.ActiveCell.Style = newStyle
        End If
    End Sub
    
    // C#
    public void changeStyle_Click(Microsoft.Office.Core.CommandBarButton
        barButton, ref bool someBool)
    {
        StyleSelection selectionForm = new StyleSelection();
        object newStyle = "";
        if (selectionForm.ShowDialog() == 
            System.Windows.Forms.DialogResult.OK) 
        {
            newStyle = selectionForm.listBox1.Text;
        }
    
        if (wordApp != null) 
        {
            this.wordApp.ActiveWindow.Selection.set_Style(ref newStyle);
        }
        else if (excelApp != null) 
        {
            this.excelApp.ActiveCell.Style = newStyle;
        }
    }
    
  6. Create a class field to hold a reference to a button. Clicking this button will display the StyleSelection form.
    ' Visual Basic
    Dim changeStyle As Microsoft.Office.Core.CommandBarButton
    
    // C#
    Microsoft.Office.Core.CommandBarButton changeStyle;
    
  7. Add the following code to the Connect method to add the changeStyle button to the toolbar.
    ' Visual Basic
    changeStyle = Me.MakeANewButton(toolbar, "Select style", 1082, _
        AddressOf changeStyle_Click)
    
    // C#
    changeStyle = this.MakeANewButton(toolBar, "Select style", 1082, 
        new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler
        (changeStyle_Click));  
    

Finding the Documentation for Office Applications

The documentation for the Office object models is part of Office applications, not part of Visual Studio. As an example, to access the documentation for Word, start Word, point to the to Macro command on the Tools menu and then click Visual Basic Editor. In the editor, use the Help menu to get object-model Help.

Optional Parameters in C# Add-ins

If you look in the documentation for the Word object model, you will find that many methods contain optional parameters in the parameter lists. If you are developing your application in Visual Basic .NET, you can leave these optional parameters out of your call. If you are using Visual C#, you need to send a value that represents a missing value. You cannot simply pass null, because the method will require that the value be passed by reference. In the .NET Framework, this value is System.Reflection.Missing.Value. The declaration is shown below.

// C#
object missing = System.Reflection.Missing.Value;

Security Concerns

You can have your add-in run in High security mode with the "Trust installed Add-Ins" option turned off by incorporating a COM add-in shim. Otherwise, you would have to run your Office applications at a lower security level. The shim, and instructions for using it, is available from Using the COM Add-in Shim Solution to Deploy Managed COM Add-ins in Office XP. For a discussion of the interaction of the Office security model with managed code, see Deployment of Managed COM Add-Ins in Office XP.

Conclusion

Building Office add-ins with Visual Studio .NET allows you to use both the rich object models of Office and the new programming constructs of the .NET Framework. The tips in this article will smooth out some of the inevitable bumps.

See Also

Creating Office Managed COM Add-ins with Visual Studio .NET | Deployment of Managed COM Add-Ins in Office XP | Using the COM Add-in Shim Solution to Deploy Managed COM Add-ins in Office XP | Finding FaceID Numbers for Your Microsoft Office 97 Toolbars | HOW TO: Build an Office COM Add-in with Visual Basic .NET (Q302896) | HOW TO: Build an Office COM Add-in with Visual C# .NET (Q302901) | PRB: Visual Studio .NET Shared Add-in Is Not Displayed in Office COM Add-ins Dialog Box (Q316723)

Show:
© 2014 Microsoft