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

Excel is the application most frequently programmed against in the Office family. Excel has a very rich object model, with 196 objects that combined have more than 4,500 properties and methods. It supports several models for integrating your code, including add-ins and code-behind documents. Most of these models were originally designed to allow the integration of COM components written in Visual Basic 6, Visual Basic for Applications (VBA), C, or C++. Through COM interop, however, managed objects written in C# or Visual Basic can masquerade as COM objects and participate in most of these models. This chapter briefly considers several of the ways that you can integrate your code with Excel and refers you to other chapters that discuss these approaches in more depth. This chapter also explores building user-defined functions for Excel and introduces the Excel object model.

Automation Executable

As mentioned in Chapter 2, "Introduction to Office Solutions," the simplest way to integrate with Excel is to start Excel from a console application or Windows Forms application and automate it from that external program. Chapter 2 provides a sample of an automation executable that automates Word.

COM Add-Ins

Excel can load a COM add-in that is a DLL that contains a class that implements IDTExtensibility2. The class that implements IDTExtensibility2 must be registered in the registry so that it can be discovered and talked to like other COM add-ins that extend Excel.

A COM add-in is typically written to add application-level functionalityfunctionality that is available to any workbook opened by Excel. You might write a COM add-in that adds a menu item to convert a currency in the selected Excel worksheet cell to another currency based on current exchange rates, for example.

Excel has a COM Add-Ins dialog box that enables users to turn COM add-ins on and off. Note that the dialog box that you access by choosing Add-Ins from the Tools menu is not the COM Add-Ins dialog box. That dialog box is used to turn on and off automation add-ins and XLA add-ins, which are discussed later in this chapter. To access the COM Add-Ins dialog box, you must perform the following steps:

After completing these steps, click the COM Add-Ins toolbar button you added to a toolbar. Figure 3.1 shows the COM Add-Ins dialog box.

You can add COM add-ins by using the Add button and remove them by using the Remove button. Typically, you will not have your users use this dialog box to manage COM add-ins. Instead, you will install and remove a COM add-in by manipulating registry settings with the installer you create for your COM add-in.

Excel discovers the installed COM add-ins by reading from the registry. You can view the registry on your computer by going to the Windows Start menu and choosing Run. In the Run dialog box, type regedit for the program to run and then click the OK button. Excel looks for COM add-ins in the registry keys under HKEY_CURRENT_USER\Software\Microsoft\ Office\Excel\Addins. Excel also looks for COM add-ins in the registry keys under HKEY_LOCAL_MACHINE\Software\Microsoft\Office\ Excel\Addins. COM add-ins registered under HKEY_LOCAL_MACHINE are not shown in the COM Add-Ins dialog box and cannot be turned on or off by users. It is recommended that you do not register your COM add-in under HKEY_LOCAL_MACHINE because it hides the COM add-in from the user.

COM add-ins are discussed in detail in Chapter 23, "Developing COM Add-Ins for Word and Excel."

Automation Add-Ins

Automation add-ins are classes registered in the registry as COM objects that expose public functions that can be used in Excel formulas. Automation add-ins that have been installed are shown in the Add-Ins dialog box, which you can display by choosing Add-Ins from the Tools menu. This chapter examines automation add-ins in more detail during the discussion of how to create user-defined Excel functions for use in Excel formulas.

Visual Studio Tools for Office Code Behind

VSTO enables you to put C# or Visual Basic code behind Excel templates and workbooks. VSTO was designed from the ground up for C# and Visual Basicso this model is the most ".NET" of all the models used to customize Excel. This model is used when you want to customize the behavior of a particular workbook or a particular set of workbooks created from a common template. You might create a template for an expense-reporting workbook that is used whenever anyone in your company creates an expense report, for example. This template can add commands and functionality that are always available when the workbook created with it is opened.

VSTO's support for code behind a workbook is discussed in detail in Part III of this book.

Smart Documents and XML Expansion Packs

Smart Documents are another way to associate your code with an Excel template or workbook. Smart Documents rely on attaching an XML schema to a workbook or template and associating your code with that schema. The combination of the schema and associated code is called an XML Expansion Pack. An XML Expansion Pack can be associated with an Excel workbook by choosing Data > XML > XML Expansion Packs. Figure 3.2 shows the XML Expansion Packs dialog box.

When an XML Expansion Pack is attached to a workbook, Excel loads the associated code and runs it while that workbook is opened. Smart Document solutions can create a custom user interface in the Document Actions task pane. You can view the task pane in Excel by choosing Task Pane from the View menu. Figure 3.3 shows a custom Document Actions task pane in Excel.

It is possible to write Smart Document solutions "from scratch" in C# or Visual Basic. This book does not cover this approach. Instead, this book focuses on the VSTO approach, which was designed to make Smart Document development much easier and to allow you to create a custom Document Actions task pane by using Windows Forms. Chapter 15, "Working with the Actions Pane," discusses this capability in more detail.

Smart Tags

Smart Tags enable displaying a pop-up menu that contains actions relevant for a recognized piece of text in a workbook. You can control the text that Excel recognizes and the actions that are made available for that text by creating a Smart Tag DLL or by using VSTO code behind a document.

A Smart Tag DLL contains two types of components that are used by Excel: a recognizer and associated actions. A recognizer determines what text in the workbook is recognized as a Smart Tag. An action corresponds to a menu command displayed in the pop-up menu.

A recognizer could tell Excel to recognize stock-ticker symbols (such as the MSFT stock symbol) and display a set of actions that can be taken for that symbol: buy, sell, get the latest price, get history, and so on. A "get history" action, for example, could launch a Web browser to show a stock-history Web page for the stock symbol that was recognized.

When a recognizer recognizes some text, Excel displays a little triangle in the bottom-right corner of the associated cell. If the user hovers over the cell, a pop-up menu icon appears next to the cell; the user can click this icon to drop down a menu of actions for the recognized piece of text. Figure 3.4 shows an example menu. When an action is selected, Excel calls back into the associated action to execute your code.

Smart Tags are managed from the Smart Tags tab of the AutoCorrect dialog box, as shown in Figure 3.5. You can display the Smart Tags tab by choosing AutoCorrect Options from the Tools menu. Here, the user can turn on and off individual recognizers, as well as control other options relating to how Smart Tags display in the workbook.

VSTO provides a simple model for creating a Smart Tag that works at the workbook or template level. Chapter 16, "Working with Smart Tags in VSTO," describes the VSTO model for working with Smart Tags in more detail.

It is possible to write Smart Tag recognizer and action classes in a DLL that work at the application level, but it is much more complex than the VSTO model. Chapter 16 also describes that approach.

XLA Add-Ins

Also available in the Add-Ins dialog box (shown by selecting Add-Ins from the Tools menu) are XLA add-ins. An XLA add-in starts life as a workbook that has VBA code behind it. The developer can then save the workbook as an XLA or Excel add-in file by choosing Save As from the File menu and selecting XLA as the file format. An XLA file acts as an application-level add-in in the form of an invisible workbook that stays open for the lifetime of Excel. Although it is possible to save a workbook customized with VSTO as an XLA file, many of the features of VSTO do not work when the workbook is converted to an XLA file. Some of the features that do not work include VSTO's support for the Document Actions task pane and for Smart Tags. For this reason, Microsoft does not support or recommend saving a workbook customized with VSTO as an XLA file. Therefore, this book does not cover the topic further.

Server-Generated Documents

VSTO enables you to write code on the server that populates an Excel workbook with data without starting Excel on the server. You might create an ASP.NET page that reads some data out of a database and then puts it in an Excel workbook and returns that workbook to the client of the Web page. VSTO provides a class called ServerDocument that makes it easy to do this. You can also use the XML file formats of Office to generate Excel documents in XML formats on the server, but this procedure is much more complex. In addition, the Excel XML file format is lossy, meaning that you cannot represent everything in an Excel spreadsheet in the Excel XML format. For this reason, we prefer the ServerDocument approach when generating documents on the server over the Excel XML file format.

Chapter 18, "Server Data Scenarios," describes generating documents on the server with ServerDocument.

Research Services

Excel has a Research task pane that enables you to enter a search term and search various sources for that term. Figure 3.6 shows the Research task pane.

Excel enables developers to write a special Web service called a research service that implements a set of Web methods defined by Excel. A research service can be registered with Excel and used in Office's Research task pane. You might write a research service that searches for a search term in a company database, for example.

Chapter 6, "Programming Word," discusses creating a research service in more detail.