This documentation is archived and is not being maintained.

Walkthrough: Creating Your First Application-Level Add-in for Excel

Updated: July 2008

Applies to

The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office.

Project type

  • Application-level projects

Microsoft Office version

  • Excel 2007

  • Excel 2003

For more information, see Features Available by Application and Project Type.

This introductory walkthrough shows you how to create an application-level add-in for Microsoft Office Excel. The features that you create in this kind of solution are available to the application itself, regardless of which workbooks are open.

This walkthrough illustrates the following tasks:

  • Creating an Excel add-in project for Excel 2003 or Excel 2007.

  • Writing code that uses the object model of Excel to add text to a workbook when it is saved.

  • Building and running the project to test it.

  • Cleaning up the completed project so that the add-in no longer runs automatically on your development computer.

NoteNote:

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

You need the following components to complete this walkthrough:

  • Visual Studio Tools for Office (an optional component of Visual Studio 2008 Professional and Visual Studio Team System).

  • Excel 2003 or Excel 2007.

By default, Visual Studio Tools for Office is installed with the listed versions of Visual Studio. To check whether it is installed on your computer, see Installing Visual Studio Tools for Office.

To create a new Excel add-in project in Visual Studio

  1. Start Visual Studio.

  2. On the File menu, point to New, and then click Project.

  3. In the Project Types pane, expand Visual C# or Visual Basic, and then expand Office.

  4. Select the 2007 folder if you are developing an Excel 2007 add-in, or select the 2003 folder if you are developing an Excel 2003 add-in.

    NoteNote:

    If you are developing an add-in for a particular version of Excel, that version must be installed on your development computer.

  5. In the Templates pane, select Excel 2003 Add-in or Excel 2007 Add-in.

  6. In the Name box, type FirstExcelAddIn.

  7. Click OK.

    Visual Studio creates the FirstExcelAddIn project and opens the ThisAddIn code file in the editor.

Next, add code to the ThisAddIn code file. The new code uses the object model of Excel to insert boilerplate text in the first row of the active worksheet. The active worksheet is the worksheet that is open when the user saves the workbook. By default, the ThisAddIn code file contains the following generated code:

  • A partial definition of the ThisAddIn class. This class provides an entry point for your code and provides access to the object model of Excel. For more information, see AddIn Host Item. The remainder of the ThisAddIn class is defined in a hidden code file that you should not modify.

  • The ThisAddIn_Startup and ThisAddIn_Shutdown event handlers. These event handlers are called when Excel loads and unloads your add-in. Use these event handlers to initialize your add-in when it is loaded, and to clean up resources used by your add-in when it is unloaded. For more information, see Visual Studio Tools for Office Project Events.

To add a line of text to the saved workbook

  1. In the ThisAddIn code file, add the following code to the ThisAddIn class. The new code defines an event handler for the WorkbookBeforeSave event, which is raised when a workbook is saved.

    When the user saves a workbook, the event handler adds new text at the start of the active worksheet.

    Private Sub Application_WorkbookBeforeSave(ByVal Wb As Microsoft.Office.Interop.Excel.Workbook, ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) Handles Application.WorkbookBeforeSave
        Dim activeWorksheet As Excel.Worksheet = CType(Application.ActiveSheet, Excel.Worksheet)
        Dim firstRow As Excel.Range = activeWorksheet.Range("A1")
        firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown)
        Dim newFirstRow As Excel.Range = activeWorksheet.Range("A1")
        newFirstRow.Value2 = "This text was added by using code" 
    End Sub
    
  2. If you are using C#, add the following required code to the ThisAddIn_Startup event handler. This code is used to connect the Application_WorkbookBeforeSave event handler with the WorkbookBeforeSave event.

    this.Application.WorkbookBeforeSave += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookBeforeSaveEventHandler(Application_WorkbookBeforeSave);
    

To modify the workbook when it is saved, the previous code examples use the following objects:

  • The Application field of the ThisAddIn class. The Application field returns a Application object, which represents the current instance of Excel.

  • The Wb parameter of the event handler for the WorkbookBeforeSave event. The Wb parameter is a Workbook object, which represents the saved workbook. For more information, see Excel Object Model Overview.

To test the project

  1. Press F5 to build and run your project.

    When you build the project, the code is compiled into an assembly that is included in the build output folder for the project. Visual Studio also creates a set of registry entries that enable Excel to discover and load the add-in, and it configures the security settings on the development computer to enable the add-in to run. For more information, see Office Solution Build Process Overview.

  2. In Excel, save the workbook.

  3. Verify that the following text is added to the workbook.

    This text was added by using code.

  4. Close Excel.

When you finish developing a project, remove the add-in assembly, registry entries, and security settings from your development computer. Otherwise, the add-in will continue to run every time that you open Excel on your development computer.

To clean up the completed project on your development computer

  • In Visual Studio, on the Build menu, click Clean Solution.

Now that you have created a basic application-level add-in for Excel, you can learn more about how to develop add-ins from these topics:

Date

History

Reason

July 2008

Added topic.

Information enhancement.

Show: