Basic Example of Excel VSTO Add-in: Display Form with Grid

by Igor Katenov, Lead Developer of 10Tec Products

Microsoft developed a unique and powerful technology to extend Microsoft Office apps with their WinForms package and .NET Framework. This technology is named Visual Studio Tools for Office, or VSTO for short. In this article we will give you a basic example of Excel VSTO add-in demonstrating how to display a WinForms form containing a grid control:

We will use our WinForms grid control, iGrid.NET, in this basic example. One of the benefits of VSTO is that the described approach will work for any WinForms controls - the standard DataGridView control, simple controls like Button or DateTimePicker, or third-party WinForms controls. Another important benefit of VSTO is its independence of the CPU architecture: the same VSTO Add-in can be used both in the 32-bit and 64-bit version of Microsoft Office without any changes.

If you are developing for Microsoft Office with VBA and want to get acquainted with VSTO to build more powerful Office solutions, this article is for you. We chose VB.NET for our VSTO starter sample below to help VBA developers to dive into VSTO quickly. But this does not mean we are limited only by Visual Basic in VSTO solutions. The same Excel VSTO add-in example can be implemented easily in C# as well.

We will be developing our basic Excel VSTO add-in in the Community Edition of Microsoft Visual Studio 2019, which can be downloaded and installed for free. Any other available version or edition of Visual Studio can be used for VSTO development as well.

Prerequisites for VSTO development

To start developing with VSTO in Visual Studio, we need to install this component. To do that, launch the Visual Studio Installer and then click the Modify button for your instance of Visual Studio. The Modifying dialog appears. On the Workloads tab, check the ‘Office/Sharepoint development’ workload and click the Modify button at the bottom right corner:

The Visual Studio Installer will add the required components to your instance of Visual Studio, including the project template we need for our Excel VSTO example.

The simplest Excel add-in: Display a WinForms form

Let’s create our first Excel VSTO Add-in that will display an empty WinForms form when Microsoft Excel starts. We will use the project template named ‘Excel VSTO Add-in’ as a basis for our add-in. Launch Visual Studio 2019 and choose ‘Create a new project’. In the new project dialog, set the language filter to ‘Visual Basic’ and the project type filter to ‘Office’ to find the required project template faster. Select this template in the list and click Next:

In the next dialog titled ‘Configure your new project’ leave the default field values unchanged. Most likely, if you have never created Excel VSTO add-ins before, your solution will be named ExcelAddIn1. Leave this field value as is or type in your own solution name and click the Create button. After a while you will see the default code for a new Excel VSTO Add-in:

Public Class ThisAddIn

    Private Sub ThisAddIn_Startup() Handles Me.Startup

    End Sub

    Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

    End Sub

End Class

Let’s create an empty WinForms form and display it on the screen when the user launches Microsoft Excel. Define a class variable of the type System.Windows.Forms.Form that will contain a reference to the form object and display it on the screen in the Startup event handler:

Let’s create an empty WinForms form and display it on the screen when the user launches Microsoft Excel. First, define a class variable of the type System.Windows.Forms.Form that will contain a reference to the form object. Then display this form on the screen in the Startup event handler of the add-in. This event is raised when the add-in is initialized during Microsoft Excel start:

Public Class ThisAddIn

    Private fForm As New System.Windows.Forms.Form

    Private Sub ThisAddIn_Startup() Handles Me.Startup
        fForm.Show()
    End Sub

    Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

    End Sub

End Class

Hit F5 or click the Start button with the green arrow on the main Visual Studio toolbar to build and launch the solution. Visual Studio will automatically build the add-in, register it in Excel and launch this app so that you can see our Excel VSTO example in action:

Most likely, the WinForms form will be somewhere in the background and you will need to click the corresponding taskbar button to bring it to the front. There are solutions allowing you to keep the form on top of the main Excel window like this, but they are beyond the scope of basic introduction to building of VSTO add-ins described in this article.

The next step: Add controls to the form

Well, let’s move further and add a button to our form. To make the whole picture friendlier to the user, let’s also set captions for the button and the form itself. This can be done with the following code in the add-in’s Startup event handler:

Private Sub ThisAddIn_Startup() Handles Me.Startup
    fForm.Text = "VSTO Add-in Form"

    Dim myButton As New System.Windows.Forms.Button
    myButton.Text = "Button"

    fForm.Controls.Add(myButton)

    fForm.Show()
End Sub

When we compile and launch our example of Excel VSTO add-in with the modified Startup event handler, we should see this:

Ok, now we know how to add a WinForms control to a form and can do this for the iGrid grid control. To use iGrid in our Excel VSTO add-in, we must add a reference to the iGrid DLL first. This is done with the ‘Add Reference…’ command from the context menu for the References node in the add-in project:

The ‘Reference Manager’ dialog appears. Click the ‘Browse…’ button at the bottom of the dialog and select the core iGrid DLL on your hard drive. iGrid.NET X (v10.0) was the latest release of the control on the moment of this writing, so that you need to find and select the file TenTec.Windows.iGridLib.iGrid.v10.0.dll on your hard drive. Do it and click the OK button. This action must add a reference to the iGrid control to our Excel add-in project:

Pay attention to the fact that a reference to the System.Windows.Forms library is added automatically when we create a new solution based on the Excel VSTO Add-in project template. This allowed us to use the standard button control included into this library in our Excel add-in example. However, we must add a reference to any third-party control like iGrid manually before we can use it in the add-in.

Now all is ready to add iGrid to the form. Let’s rewrite the Startup event handler to add the iGrid control instead of the button:

Private Sub ThisAddIn_Startup() Handles Me.Startup
    fForm.Text = "VSTO Add-in Form"

    Dim myGrid As New TenTec.Windows.iGridLib.iGrid

    fForm.Controls.Add(myGrid)

    fForm.Show()
End Sub

If we launch our VSTO add-in example, it will display a form like on the picture below:

Not bad. Let’s create some cells in the grid and position it in the center of the form:

Private Sub ThisAddIn_Startup() Handles Me.Startup
    fForm.Text = "VSTO Add-in Form"

    Dim myGrid As New TenTec.Windows.iGridLib.iGrid
    myGrid.Cols.Count = 3
    myGrid.Rows.Count = 5
    myGrid.Cells(0, 0).Value = 123

    myGrid.SetBounds(12, 12,
        fForm.ClientSize.Width - 24,
        fForm.ClientSize.Height - 24)

    fForm.Controls.Add(myGrid)

    fForm.Show()
End Sub

Now the form with the WinForms grid looks like this:

Congratulations! You have just mastered the basic technology of creating Excel VSTO add-ins that can display a form with a WinForms grid control inside.

Final words about Excel VSTO Add-ins

To use a VSTO add-in from Excel, the add-in must be properly installed and registered. Visual Studio does this work for us automatically behind the scenes when we launch a VSTO add-in solution from the IDE. To deploy the built add-in to the computers of your end users, you need to install and register yourself. Fortunately, the VSTO infrastructure is incorporated into all modern versions of Microsoft Office and allows you to deploy your VSTO add-ins very easily. You can do this using the ClickOnce technology, MSI installation packages or even build your own installers.

Note that the add-in remains registered after we close the Visual Studio IDE with a VSTO add-in solution. This means that our Excel VSTO add-in will be activated every time when you open Excel, and you will see the WinForms form with iGrid then too. To avoid this, unregister the Excel VSTO add-in by invoking the ‘Clean Solution’ command from the Build menu in Visual Studio when you close the IDE.

Displaying a WinForms form with visual controls is not the only way to build user interfaces with the VSTO technology. VSTO can be used to insert controls into the action pane incorporated into the main Excel window, or you can create a custom button on the Office ribbon to display a WinForms dialog when needed. One more way is to insert a visual control like command button or even iGrid directly into an Excel worksheet.

The rich object model provided by VSTO opens access to the whole Excel window or opened Excel workbooks, what is generally used to modify worksheet cells from add-ins. We will try to give you more examples of Excel VSTO add-ins related to these techniques and touch the question of deployment in further articles.

Next VSTO sample: add a button to the Office Ribbon »