VSTO (Visual Studio Tools for Office)

VSTO (Visual Studio Tools for Office)

Creating Your First VSTO Add-in for Excel


  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# or VB code style (Visual Studio Tools for Office)

To create a new Excel VSTO Add-in project in Visual Studio follow the steps:

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

Visual Studio creates the First Excel AddIn project and opens the ThisAddIn.cs code file in the editor:

create project 2
  • This class provides an entry point for your code and provides access to the object model of Excel. For more information, see Programming 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.

Let’s add a line of text to the saved workbook. In the ThisAddIn.cs code file, add the following code to the This Add In class. The new code defines an event handler for the Work book Before Save event, which is raised when a workbook is saved

C# code example:

void Application_WorkbookBeforeSave(Microsoft. Office. Interop. Excel.Workbook Wb, bool Save As UI, ref bool Cancel)
	//get reference of active sheet
	Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet);
	//get A1 range
	Excel.Range firstRow = activeWorksheet.get_Range("A1");
	//insert one row below
	//Take firstRow range
	Excel.Range newFirstRow = activeWorksheet.get_Range("A1");
	//added text to range
	newFirstRow.Value2 = "This text was added by using code";

VB code example:

Private Sub Application_WorkbookBeforeSave(ByVal Wb As Microsoft.Office.Interop.Excel.Workbook, ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) Handles Application.WorkbookBeforeSave
	'taking reference of active sheet
	Dim activeWorksheet As Excel.Worksheet = CType(Application.ActiveSheet, Excel.Worksheet)
	'Take range of A1 cell
	Dim firstRow As Excel.Range = activeWorksheet.Range("A1")
	'Inserting one row down
	'take the range back
	Dim newFirstRow As Excel.Range = activeWorksheet.Range("A1")
	'put the value
	newFirstRow.Value2 = "This text was added by using code"

End Sub

Event declaration: 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 Microsoft.Office.Interop.Excel.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 Microsoft.Office.Interop.Excel.Workbook object, which represents the saved workbook. For more information, see Excel Object Model Overview.

Run Project:

  1. Press F5 to build and run your project.
  2. 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 Building Office Solutions.
  3. In Excel, save the workbook.
  4. Verify that the following text is added to the workbook.
  5. This text was added by using code.
  6. Close Excel.

Next >> Accessing the Object Model of the Host Application