Walkthrough: Create your first VSTO Add-in for Excel

Article
08/14/2019

Applies to: Visual Studio Visual Studio for Mac

Note

This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

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.

Applies to: The information in this topic applies to VSTO Add-in projects for Excel. For more information, see Features available by Office application and project type.

Note

Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.

This walkthrough illustrates the following tasks:

Creating an Excel VSTO Add-in project for Excel.
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 VSTO Add-in no longer runs automatically on your development computer.

Note

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 Personalize the IDE.

Prerequisites

You need the following components to complete this walkthrough:

An edition of Visual Studio that includes the Microsoft Office developer tools. For more information, see Configure a computer to develop Office solutions.
Excel 2013 or Excel 2010.

Create the project

To create a new Excel VSTO Add-in project in Visual Studio

Start Visual Studio.
On the File menu, point to New, and then click Project.
In the templates pane, expand Visual C# or Visual Basic, and then expand Office/SharePoint.
Under the expanded Office/SharePoint node, select the Office Add-ins node.
In the list of project templates, select Excel 2010 Add-in or Excel 2013 Add-in.
In the Name box, type FirstExcelAddIn.
Click OK.

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

Write code to add text to the saved workbook

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 Program VSTO Add-ins. 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 VSTO Add-in. Use these event handlers to initialize your VSTO Add-in when it is loaded, and to clean up resources used by your Add-in when it is unloaded. For more information, see Events in Office projects.

To add a line of text to the saved workbook

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

void Application_WorkbookBeforeSave(Microsoft.Office.Interop.Excel.Workbook Wb, bool SaveAsUI, ref bool Cancel)
{
    Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet);
    Excel.Range firstRow = activeWorksheet.get_Range("A1");
    firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
    Excel.Range newFirstRow = activeWorksheet.get_Range("A1");
    newFirstRow.Value2 = "This text was added by using code";
}

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.

Test the project

To test the project

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 VSTO Add-in, and it configures the security settings on the development computer to enable the VSTO Add-in to run. For more information, see Build Office solutions.
In Excel, save the workbook.
Verify that the following text is added to the workbook.

This text was added by using code.
Close Excel.

Clean up the project

When you finish developing a project, remove the VSTO Add-in assembly, registry entries, and security settings from your development computer. Otherwise, the VSTO 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.

Next steps

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

General programming tasks that you can perform in VSTO Add-ins: Program VSTO Add-ins.
Programming tasks that are specific to Excel VSTO Add-ins: Excel solutions.
Using the object model of Excel: Excel object model overview.
Customizing the user interface (UI) of Excel, for example, by adding a custom tab to the Ribbon or creating your own custom task pane: Office UI customization.
Building and debugging VSTO Add-ins for Excel: Build Office solutions.
Deploying VSTO Add-ins for Excel: Deploy an Office solution.

Share via