Visual Studio Tools for Office: Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath

In Windows Forms programming, a form is a window that contains controls, such as buttons and combo boxes. To implement a form, you can drag and drop controls from the Visual Studio toolbox onto the form's designer. Then the form designer generates a customized subclass of the Form class. Because each form is implemented by its own class, you can customize the form code further by adding properties and methods of your own to the class. And because all the controls are added as properties on the form class, you can use IntelliSense to program those custom methods more rapidly.

The system of host items and host controls in VSTO is directly analogous to Windows Forms. By host, we mean the applicationWord or Excelthat hosts the customization. Host items are like forms: programmable objects that contain user interface elements called host controls. The Workbook, Worksheet, and Chartsheet objects are host items in Excel; the Document object is the sole host item in Word. In Outlook, the Outlook Application object is exposed as a host item.

As we saw back in Chapter 2, "Introduction to Office Solutions," the Visual Studio Excel and Word designers create custom classes that extend the Worksheet and Document base classes. As you place host controls such as lists, named ranges, charts and buttons onto the worksheet, they are exposed as fields on the customized subclass.

Separation of Data and View

Some people use spreadsheet software solely for its original purpose: to lay out financial data on a grid of cells that automatically recalculates sums, averages, and other formulas as they update the data. You might have a simple Excel spreadsheet that calculates the total expenses for a wedding given all the costs involved, for example. Similarly, some people use word-processing software solely for its original purpose: to typeset letters, memos, essays, books and other written material automatically.

In a business setting, however, spreadsheets and documents have evolved to have both high internal complexity and external dependencies. Unlike a spreadsheet containing a wedding budget, a spreadsheet containing an expense report or a document containing an invoice is likely to be just one small part of a much larger business process. This fact has implications for the design of a programming model. Consider this Visual Basic for Applications (VBA) code, which might be found in a spreadsheet that is part of a larger business process:

SendUpdateEmail _ ThisWorkbook.Sheets(1).Cells(12,15).Value2

Clearly, the unreadable snippet is sending an e-mail to someone, but because the Excel object model emphasizes how the spreadsheet represents the data, not what the data represents, it is hard to say what exactly this is doing. The code is not only hard to read, but also brittle; redesigning the spreadsheet layout could break the code. We could improve this code by using a named range rather than a hard-coded direct reference to a particular cell:

SendUpdateEmail _ ThisWorkbook.Names("ApproverEmail").RefersToRange.Value2

Better, but it would be even nicer if the particular range showed up in IntelliSense. VSTO builds a convenient custom object model for each work sheet, workbook, or document so that you can more easily access the named items contained therein:

SendUpdateEmail(ExpenseReportSheet.ApproverEmail.Value2)

A more readable, maintainable, and discoverable object model is a welcome addition. Even in the preceding snippet, however, the VSTO programming model still does not address the more fundamental problem: We are manipulating the data via an object model that treats it as part of a spreadsheet. The spreadsheet is still the lens through which we see the data; instead of writing a program that manipulates ice cream sales records, we wrote a program that manipulates a list and a chart.

The crux of the matter is that Word and Excel are editors; they are for designing documents that display data. Therefore, their object models thoroughly conflate the data itself with the "view": the information about how to display them. To mitigate this conflation, the VSTO programming model was designed to enable developers to separate view code logically from data code. Host items and host controls represent the view elements; host items and host controls can be data bound to classes that represent the business data.

Model-View-Controller

If you're familiar with design patterns, you will have already recognized this as based on the Model-View-Controller (MVC) design pattern, shown in Figure 13.1. In the MVC pattern, the data model code represents the business data and the processes that manipulate that data. The view code reads the data, listens to Change events from the data, and figures out how to display the data. The controller code mediates between the view and the data code, updating the data based upon the gestures the user makes in the view (mouse clicks, key presses, and so on).

Benefits of Separation

Logically separating the data code from the view code leads to a number of benefits when building more complex business documents on top of Word and Excel:

• Business data and rules can be encapsulated in ADO.NET datasets and reused in different applications.

• Changes to view code are less likely to break data code unexpectedly (and vice versa).

• Data code can cache local copies of database state for offline processing.

• Server-side code can manipulate cached data inside the document without starting Word/Excel.

Now that you know some of the design philosophy behind VSTO, let's take a look at how the host items and host controls actually extend the Word and Excel object models. (The data side is covered in Chapter 17, "VSTO Data Programming," and server-side data manipulation is covered in Chapter 18, "Server Data Scenarios.")