More about the VSTO 2.0 Programming Model--introducing Views

I wrote in some detail about view controls earlier. My hope in this entry is to give you a slightly higher level view of the programming model and to introduce the concept of views.

To begin, we will examine the programming model created in VSTO 2.0 for Excel. This will help to explain the system provided by VSTO 2.0 for creating programming models.

Shown below is an Excel worksheet opened in a VSTO 2.0 project that has a number of elements on it. If you’ve never seen VSTO 2.0 before you’ll notice some interesting things in the screenshot—namely that we open the excel document inside of Visual Studio—this further makes it feel like you are editing something akin to a form.

Each element in the screenshot above is tagged. This table describes the elements that are tagged.

VSTO 2.0 promotes all these objects as named objects in a generated programming model that reflects the content of the document. This is a departure from VBA. The programming model for Excel in VBA would promote the worksheets and workbooks as named objects into the programming model, but wouldn’t expose things like named ranges and list objects directly into the model. Instead, the developer would have to walk the object model to get at a particular named range or a particular shape. Note that VBA would promote ActiveX controls in the document into the programming model as named objects. VSTO doesn’t support ActiveX controls directly (it doesn’t automatically promote an ActiveX control into your programming model), but it does support putting managed controls (WinForms controls) into the document. Any managed controls (above we have a WinForms button and a WinForms checkbox) are promoted into the programming model.

VSTO 2.0 promotes the notion of naming these host provided objects and putting them in the programming model used by the developer. Elements 1 through 7—the host elements in the document—are called “View controls” in our model—“controls” because of their similarity to controls on a form and “view controls” because instead of being on an actual form, they are on a host provided surface or “view”.

Views in VSTO 2.0 are classes (which are individual project items in the IDE) that contain view controls. For Excel, we create a view for each sheet in the workbook and a view for the workbook itself (elements 9 through 12). The resulting programming model has a project item for each view (Sheet1.vb, Sheet2.vb, Sheet3.vb, and ThisWorkbook.vb). Views implement Excel interfaces—worksheets derive from Microsoft.Office.Interop.Excel.Worksheet, workbooks derive from Microsoft.Office.Interop.Excel.Workbook. This means when you are writing code in Sheet1 and type “Me.” or “This.” you will see all the properties and methods of the worksheet (if coding in Sheet1-3) or the workbook (if coding in ThisWorkbook.vb).

Since the view controls (elements 1 through 7) are on Sheet1, they are members of the View class created for sheet1. If we go into the code window for sheet1.vb, we see the following in the left drop down box of objects we can write code against:

Note that element 9 has resulted in a class—a “View” in our terminology—that is called Sheet1. Elements 1-7 have resulted in “View Controls” that are members of Sheet1. These are also numbered in this diagram to show the correspondence to the original Excel worksheet diagram.

A developer can then write code against these view control objects in the generated programming model by putting code into the Sheet1 class. I've provided some sample code that you might write in “Sheet1.vb“. I’ve added a bunch of comments to try to explain this code.

' This is the "user" side of Sheet1--VSTO generates additional code into Sheet1

' that is hidden and gives you the declaration of the view controls and the fact

' that Sheet1 implements Microsoft.Office.Interop.Excel.Worksheet

Public Class Sheet1

    ' This is an event handler for a sheet1 event--this is an event on this view since sheet1

    ' implements the Worksheet interface provided by Excel.

    Private Sub Sheet1_BeforeDoubleClick(ByVal Target As Microsoft.Office.Interop.Excel.Range, ByRef Cancel As Boolean) Handles Me.BeforeDoubleClick, MyBase.BeforeDoubleClick

        MsgBox("Sheet1 was just double clicked")

    End Sub

    ' This is an initialize event for the class--called when the workbook opens

    Private Sub Sheet1_Initialize(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Initialize, MyBase.Initialize

        ' Talk to a view control (a Winforms checkbox) that is a member of Sheet1

        CheckBox1.Enabled = True

        ' Talk to a view control (a named range) that is a member of Sheet1

        myNamedRange.Value2 = "Hello Excel"

        ' Talk to an Excel Worksheet method on this view class that comes from Microsoft.Office.Interop.Excel.Worksheet

        Me.CheckSpelling()

    End Sub

    ' This is a shutdown event for the class--called when the workbook closes

    Private Sub Sheet1_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown, MyBase.Shutdown

    End Sub

    ' This is an event handler for a view control event--the change event for myNamedRange

    ' which is a member of the Sheet1 class.

    Private Sub myNamedRange_Change(ByVal Target As Microsoft.Office.Interop.Excel.Range) Handles myNamedRange.Change

        MsgBox("myNamedRange just changed")

    End Sub

End Class