Creating a Capital Expenditure Model with Visual Studio 2005 Tools for Office and Excel 2003 Using Visual Basic and C#

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Charles Maxson, Microsoft Corporation

Updated: January 2006

Applies to: Microsoft Visual Studio 2005, Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft Office Excel 2003

Summary: Learn how to use Microsoft Visual Studio 2005 Tools for the Microsoft Office System to turn a Microsoft Office Excel 2003 worksheet into a complex capital budgeting application. (21 printed pages)

Download OfficeVSTOCapitalExpenditureModel.msi.

Contents

• Overview

• Scenario

• Setup Requirements and Instructions

• Capital Expenditure Model Solution Overview

• Running the Capital Expenditure Model Solution

• The User's Perspective

• The Developer's Perspective

• Deploying the Solution

• Code Security Considerations

• Conclusion

• Additional Resources

• About the Author

Overview

Microsoft Office Excel 2003 is a tool that is used for many different tasks. Its uses range from quick, simple spreadsheets to broad, complex models that are mission-critical to businesses. But, it can be a challenge to use Excel when a spreadsheet becomes more than just a spreadsheet.

A spreadsheet turns into an application when it becomes part of a complex business process that extends far beyond the original creator's reach, and when others have to learn the intricacies of the process to get their jobs done.

So how does a spreadsheet become an "application"? Well, some may say that copying an Excel workbook to a file share so that others can access it makes it an application. But I think a spreadsheet becomes an application when it is designed specifically to enable users to get the most work done with the least amount of effort. That may mean simply optimizing the spreadsheet so the formulas are laid out in a certain way, or it may mean coding large amounts of solution logic to meet a specific business requirement. There is no set rule, but the results should be obvious — users that are more productive. Then, you know you have an application.

This article demonstrates how to take a sophisticated Excel spreadsheet model and turn it into an intuitive, easy-to-use, integrated application using Microsoft Visual Studio 2005 Tools for the Microsoft Office System (Visual Studio 2005 Tools for Office).

Scenario

This article is based on a solution that begins with an Excel spreadsheet used in corporate finance scenarios. Called the Capital Expenditure Analysis Model, its business purpose is to enable users to forecast the profitability of capital asset items before they are purchased. In finance, this is referred to as "capital budgeting." See Figure 1.

Figure 1. Capital Expenditure Analysis Model (Click picture to view larger image)

This application shows you how to use Visual Studio 2005 Tools for Office to make a complex business process simpler. It does not teach you the concepts of capital budgeting. But, even if you do not know anything about capital budgeting and your finance skills are limited to balancing your checkbook, read on.

Business Process

Every organization is concerned with costs. Therefore, before any organization acquires a new capital asset — whether it is a piece of machinery, a vehicle, or a computer — there is going to be some scrutiny to decide whether the acquisition is a good business move. That is, will the value added to the business by the asset exceed the cost of purchasing it?

Capital budgeting is a common approach to determining this. There are differing capital budgeting methods for analyzing investments, but most involve taking information about the capital asset, such as its initial cost, the estimated lifetime of the asset, and the salvage value remaining at the end of that lifetime, and combining it with information about market factors, such as the cost of borrowing money and the anticipated growth rate of business. This information is then applied against a series of calculations that take into consideration tax rates, depreciation, and operating cash flows to yield financial metrics that reveal whether an investment is worthwhile.

This process requires a little knowledge of finance and a flexible tool that can perform such evaluations. As you might guess, Excel is a good choice for this. In an organization, it often happens that someone with an understanding of capital budgeting builds an Excel spreadsheet and shares it with others who have to analyze investment opportunities. While this may work, it also introduces a couple of problems. Other users must understand the spreadsheet model and the business logic behind it to use it successfully. The learning curve can be significant. Also, because in most organizations multiple people are involved in the acquisition process of capital assets — and many even have automated approval processes already in place — storing data in numerous Excel spreadsheets is not favorable. This means that although Excel may be an excellent starting point, it often should not be the end point in the capital budgeting process.

Building the Spreadsheet

One benefit of developing solutions with the Microsoft Office System is the ability to draw on help from users. Unlike most development efforts, where you can only gather requirements from users, when you build Microsoft Office solutions, you can frequently use existing content provided by users. It is like getting a working prototype or specification document, but from a subject matter expert in an area that you may not understand or have time to learn. As I mentioned earlier, you do not have to understand capital budgeting to create a great application. But you must have a spreadsheet to start with. So, as I did in this case, I encourage you to look to your users, who are often the experts.

Examine the spreadsheet used in this solution. You find that it is typical of a spreadsheet produced by someone who is experienced with Excel. Beyond its colorful appearance, it uses defined range names, conditional formulas, and financial functions extensively. The good news is that you do not have to change anything; the spreadsheet on its own is a very capable solution. However, combined with Visual Studio 2005 Tools for Office, it can be an even better solution.

Setup Requirements and Instructions

To use the Capital Expenditure Analysis Model solution, you must have the following software installed:

• Microsoft Visual Studio 2005 Tools for the Microsoft Office System

• Microsoft Office Excel 2003 with SP 1 (complete installation) or Microsoft Office Professional Edition 2003 with SP 1 (complete installation)

Installation Steps

1. Install Visual Studio 2005 Tools for Office.

2. Perform a complete installation of Microsoft Office Professional Edition 2003 or Excel 2003.

   Only a complete installation includes the necessary primary interop assemblies.

3. Download and install the package associated with this article, which contains the source code, Excel workbook, and XML data files.

4. Install the files to a folder on your computer.

5. Open the Visual Basic version or the Visual C# version of the CapitalExpenditureSample solution in Visual Studio 2005.

6. PressF5 to compile and run the sample.

Capital Expenditure Model Solution Overview

As mentioned previously, there were a couple of key concerns with leaving the Capital Expenditure Analysis Model as a spreadsheet-only solution. So, after deciding that the spreadsheet has to be extended as an application, the next questions are how, and with what toolset?

As you are probably aware, there are many choices for creating solutions in Excel. You can choose to use the traditional Visual Basic for Applications (VBA), COM add-ins based on Visual Basic, smart documents created with the Smart Documents Software Development Kit (SDK), or Microsoft Visual Studio Tools for the Microsoft Office System, Version 2003, in addition to Visual Studio 2005 Tools for Office. Although each has merits, before I selected one, I considered my objectives for the application:

• To simplify the process of performing capital budgeting assessments for all users.

• To mask the complexity of the spreadsheet model and the business logic from novice users.

• To allow the solution to integrate easily with enterprise data and other business processes external to Excel.

Why Use Visual Studio 2005 Tools for Office?

While it is true that I could achieve parts of my solution objectives using any of the technologies that I mentioned, the case for using Visual Studio 2005 Tools for Office is very strong. My decision rested partially on obvious solution-specific abilities in Visual Studio 2005 Tools for Office, such as a highly programmable Document Actions task pane, which I will discuss later in this article. I had other, more personal, thoughts though: I am simply more productive using a Visual Studio toolset to build Microsoft Office solutions than I am using any other technology.

The following items summarize why I selected Visual Studio 2005 Tools for Office:

• Visual Studio and the Microsoft .NET Framework

  Visual Studio 2005 Tools for Office is hosted within Visual Studio and runs on the common language runtime (CLR) of the .NET Framework. This enables you to take advantage of the many known benefits of the .NET Framework, in addition to the latest developer productivity features in Visual Studio. Another less known advantage to building the solution using Microsoft .NET technology is that more developers are likely to be able to support a .NET-based application because of the broad reach of .NET.

• Advanced feature set

  Visual Studio 2005 Tools for Office offers many features, such as host controls, data binding, and an easily-programmable addition to the Document Actions task pane that is called the actions pane. With these features, you can build new types of solutions based on Microsoft Office that are more powerful and also easier to build.

• Security model

  One benefit of using Visual Studio 2005 Tools for Office is that its security model is based on the .NET Framework. When working with financial information, as the Capital Expenditure Analysis Model solution does, it is reassuring to know that rogue code cannot be maliciously run against it.

• Distribution model

  One concern when you develop a spreadsheet solution is the possibility of ending up with different versions of the spreadsheet scattered across different computers. Introducing code that accompanies the spreadsheet workbook makes this issue even more complicated. Unlike VBA, Visual Studio 2005 Tools for Office uses a document-specific .dll file, which is separate from the Excel workbook file. It is therefore easier to distribute and maintain solutions, and you do not have to worry about version conflicts.

Running the Capital Expenditure Model Solution

After you compile the solution, you can perform the following steps to become familiar with this application.

First Steps and Instructions

1. Open the CapitalExpenditure.xls workbook in Excel.

2. In the Document Actions task pane, select the Show this page at startup check box.

3. Click Close.

Using Glossary Mode

1. In the Document Actions task pane, click Help, and then click Glossary.

2. In the worksheet, select input cells, such as Opportunity Cost, Salvage Value, or Tax Credit.

   Each of the input cells with a white background contains a glossary definition.

3. In the Document Actions task pane, click Close.

Entering Data

1. In the Document Actions task pane, enter project summary information.

   The values you enter are not relevant.

2. Click Next (the green right-arrow).

3. Enter initial investment information by selecting an item from the Investment Item drop-down list.

4. Click Next (the green right-arrow).

5. Enter cash flow information.

   Again, the values are not relevant.

6. Click Next (the green right-arrow).

7. Enter working capital information.

8. Click Next (the green right-arrow).

9. Select the CAPM option button, and then enter discount rate information.

10. Click Next (the green right-arrow).

11. Enter growth rate information.

Submitting the Project

• In the Document Actions task pane, click Actions, and then click Submit for Approval.

The User's Perspective

When a user opens the Capital Expenditure Analysis Model spreadsheet in Excel, the Visual Studio 2005 Tools for Office solution assembly also loads, if you have configured the proper security settings on the local computer. Along with the spreadsheet, the user sees the custom Document Actions task pane. See Figure 2.

Figure 2. Capital Expenditure Analysis Model with Document Actions task pane

The application uses the Document Actions task pane extensively, as an alternative form of user interface (UI). In fact, all user interaction with the application, outside of the spreadsheet itself, is done from within this task pane. This "alternative user interface" design is intentional, so that users do not have to open dialog boxes to enter information or give up valuable screen space for modeless dialogs — the Document Actions task pane is a native part of the Microsoft Office UI.

The solution even implements its own custom menus from within the Document Actions task pane, as shown in Figure 3. So, instead of sorting through custom application commands mixed with Excel menu bars and toolbars, users must look to this task pane only to find the commands to operate the application. If users want to use commands specific to Excel, they can find them in familiar locations. This separation of custom UI from built-in UI allows users to concentrate on their business tasks instead of learning to operate the application.

Figure 3. Application menu bar in the Document Actions task pane

Getting Help in the Document Actions Task Pane

By default, the first time a user starts the Capital Expenditure Analysis Model, the Document Actions task pane displays instructions on how to use the application, as shown in Figure 4. The only thing fancy about this feature is that it helps to satisfy one of the design goals, which is to make the application easier for new users. After users become familiar with the application, they can bypass this option on subsequent startups.

Figure 4. Document Actions task pane displaying instructions

Another feature that helps the novice user become familiar with the application is "Glossary Mode." Because the Capital Expenditure Analysis Model uses terminology that may be new or unclear to users, this learning feature enables them to switch to a mode where they can move the cursor through the cells in the worksheet and see definitions of the associated terms automatically displayed in the Document Actions task pane. Glossary Mode (see Figure 5) is a good alternative to replacing a conventional Help file, for several reasons. It is more efficient because users do not have to perform a search or look through an index to get help on a term; they simply move the cursor into the cell for which they want help. Interactivity helps users to learn the model because they can work directly within it while learning the financial concepts. Also, Glossary Mode is not a compiled, static file like a Help file is. The content displayed in the Document Actions task pane is dynamic data that comes from an external source. In this sample, that source is an XML file. This enables greater flexibility for maintaining and expanding the content provided by the solution.

Entering Information in the Spreadsheet

Complex spreadsheet models can be difficult to follow and to use. In the case of the Capital Expenditure Analysis Model, although the spreadsheet is laid out logically, the user still must understand quite a bit to enter the values that are required to perform a capital budgeting analysis. To make the process as streamlined and as straightforward as possible, once again use the Document Actions task pane in Visual Studio 2005 Tools for Office.

The task bar menu uses a forward arrow and backward arrow metaphor, which is common in dialog wizards, to enable the user to click through the process of entering the required parameters for an investment project. The controls in the Document Actions task pane are synchronized with the corresponding cells in the spreadsheet. This allows users to use this task pane to enter data, or, if they are more experienced with the Model, to use any combination of editing cells directly and to use the task pane. This synchronization process also enables default values to be copied directly from the spreadsheet model into the Document Actions task pane when a user enters data. This is an advantage because you do not have to hard-code default values into the solution, and you can easily change them with the spreadsheet. The application also pulls back-end data for investment items into the Document Actions task pane. Thus, users can use enterprise data in the model without having to import, copy, or retype it.

Figure 6. Investment item inputs in the Document Actions task pane

Persisting the Project Scorecard Inside the Task Pane

An Excel spreadsheet can grow to an unmanageable size if you use formulas or if your data spans numerous columns and multiple worksheets. Often users have to repeatedly refer to content in a specific cell, forcing them to either freeze the spreadsheet's window panes or to continuously navigate back to the particular location to view the information. Freezing panes is a common option in Excel, but it is not always a viable one because of a small screen size or the location of the content. Therefore, the burden falls back on the user to monitor changes by scrolling.

The Capital Expenditure Analysis Model uses several influential formulas in the spreadsheet to determine the valuation of the capital asset being analyzed. They are the Net Present Value (NPV), the Internal Rate of Return (IRR), and the Return on Investment Capital (ROIC) of the project. These formulas are located at the bottom of the worksheet. As the user enters and edits values, these numbers change accordingly. To enable the user to monitor these important values while working with the model, these Excel formulas are persisted and immediately updated inside the Document Actions task pane when each change occurs. See Figure 7. This is a great benefit to the application user and a surprisingly easy feature for the developer to implement using Visual Studio 2005 Tools for Office.

Figure 7. Project scorecard

Submitting a Purchase for Approval

Most companies require every capital purchase to go through an approval process before money is spent. But, as you can imagine, it would be an inefficient process if someone had to sift through multiple spreadsheets to decide whether to purchase capital assets. After users finish evaluating a project with the Capital Expenditure Analysis Model application, instead of sending the workbook as an attachment to an e-mail message or uploading the workbook to a network share or Web server, they click Actions in the Document Actions task pane, and then click Submit for Approval.

In the demonstration, this saves the key data points from the spreadsheet as an XML document on the user's hard drive and displays the data in the browser. Being able to easily separate the essential project data from the supporting content in an XML document, while leaving the Excel workbook file behind, is very valuable.

Imagine a real-world solution in which this XML document could be uploaded by an XML Web service to a back-end application that starts an approval process. Or, the document could use Microsoft BizTalk Server to launch an enterprise workflow that automatically alerts those with approval authority that a project has been proposed. Whatever the result, using the ability of Excel to apply XML Schema (XSD) to a workbook is a powerful way to enable such scenarios.

The Developer's Perspective

If you have read all the previous sections and installed the sample application, you are ready to learn about how the Capital Expenditure Analysis Model application was built using Visual Studio 2005 Tools for Office.

Getting Started

Visual Studio 2005 Tools for Office allows you to create an Excel project in Visual Studio using a new or existing Excel spreadsheet or template. As mentioned previously, I was fortunate to have the Capital Expenditure Analysis Model already built as a stand-alone spreadsheet file, so all I did to get started was create a Visual Studio 2005 Tools for Office project that used the file CapitalExpenditure.xls. To do so, I opened the Project Wizard, as shown in Figure 8.

Figure 8. Visual Studio 2005 Tools for Office Project Wizard

Once inside the Visual Studio designer, another aspect of Visual Studio 2005 Tools for Office is immediately apparent: the integration of Excel, which is now hosted directly within the Visual Studio designer, as shown in Figure 9. This means that without opening the Excel application independently you can make changes to the spreadsheet using the native functionality of Excel from within the Visual Studio 2005 development environment. For example, you can edit formulas, insert rows, and create charts as if you were working on a Microsoft Windows Forms project — except it is really Excel.

Figure 9. Visual Studio 2005 Tools for Office hosting Excel

When I made a couple of minor design-time changes to the spreadsheet, Visual Studio integration made these changes efficient. Without toggling applications, I added an XML map to the workbook from inside the Visual Studio designer. Loaded from the file CapitalExpenditure.xsd, the elements of the XML map were mapped to the corresponding cells in the worksheet. This was most of the effort required to extract the project data as an XML document. Also, to display the Project Scorecard in the Document Actions task pane, I added a second worksheet called "Data" to the workbook. (I'll explain later how it is used.)

Building the Document Actions Task Pane

The application revolves around using the Microsoft Office Document Actions task pane to host the entire user interface. The task pane programming model in Visual Studio 2005 Tools for Office is powerful and, when compared to other task pane technologies that are available for Excel, probably the simplest to implement. In Visual Studio 2005 Tools for Office, this programmable task pane is called the actions pane.

The Visual Studio 2005 Tools for Office actions pane is a Windows Forms control that serves as a container for other managed Windows Forms controls. You create controls, using the familiar Windows Forms designer at design time or programmatically at run time. Then, at run time, controls are rendered in the actions pane by adding them to the controls collection of the ActionsPane object. Controls hosted in the actions pane operate just like they would if they ran in a Windows Forms application. You can set control properties, capture events, position controls, and bind controls directly to data sources.

You can add any number of controls individually to the actions pane, but implementing Windows Forms user controls greatly simplifies development. Using user controls enables you to add and lay out controls at design time as if you were creating a Windows Form. Then, at run time, you have to add only the user control to the actions pane instead of adding individual controls. Visual Studio 2005 Tools for Office introduces an Actions Pane control that you can add from the Add New Item dialog box in Visual Studio .NET.

In the Capital Expenditure Analysis Model application, the Document Actions task pane appears to operate as one sophisticated control. But, rather than a single user control, it is 11 controls. Although not all 11 show at the same time, the solution takes advantage of the fact that you can display multiple controls in the ActionsPane object simultaneously. This technique allows the application to simulate a dynamic actions pane that responds to user commands, while it appears that the logo, title, menu bar, and Project Scorecard are stationary.

Here is how it works. When the project is loaded, Visual Studio 2005 Tools for Office runs startup events for the workbook and each worksheet. Using the Startup event for the ProjectWorksheet class, a class-level variable named actionsPane is set to refer to the application's ActionsPane object, type Microsoft.Office.Tools.ActionsPane:

actionsPane = Globals.ThisWorkbook.ActionsPane

Then, three individual user controls are added to actionsPane. The first control serves as the "header" for the Document Actions task pane. It contains the logo, the title, the menu bar, and all of the code for the menu bar commands:

' Add the header control.
taskPaneHeader = New TaskPaneHeader
actionsPane.Controls.Add(taskPaneHeader)

When you add multiple controls to the ActionsPane object, by default, the position where they appear is determined by the order in which you add them to the collection. As you add each control, it is placed directly following the previously added control, allowing controls to appear as if they are attached. In the case of the Capital Expenditure Analysis Model, the header was added first, followed by the body, and then the footer, so that the layout appears as intended. Note that you can override the default layout of controls by managing the stacking order of the controls with the Microsoft.Office.Tools.ActionsPane.StackOrder property.

Another important consideration of actions pane design is that the user ultimately controls the position of the Document Actions task pane. The default location is for it to dock vertically along the right side of the document, but users can reposition the task pane so that it docks horizontally along the top or bottom of the document, docks vertically along the left side, or "floats" undocked above the Microsoft Office application. It is up to the developer to manage the layout of the controls based upon the current orientation of the Document Actions task pane. You can do this using the Microsoft.Office.Tools.StackStyle enumeration combined with the Microsoft.Office.Tools.ActionsPane.StackOrder property. For purposes of simplifying the sample solution, however, the orientation of the task pane is not addressed.

The second control serves as the "body" of the Document Actions task pane. It is a blank user control: it has no controls and no code, and size is the only property assigned to it. This is because its main purpose is to function as a placeholder within the center of the task pane for other controls that it will host for the application.

' Add the body control.
taskPaneBody = New TaskPaneBody
actionsPane.Controls.Add(taskPaneBody)

The "footer" control is added last. The footer is responsible for containing the Project Scorecard. The footer also fills any spare space in the Document Actions task pane, because its .Dock property is set to Fill.

' Prepare and add the (footer) InvestmentMeasures control.
taskPaneFooter = New TaskPaneInvestmentMeasures
taskPaneFooter.Dock = DockStyle.Fill
actionsPane.Controls.Add(taskPaneFooter)

Adding Controls to the Document Actions Task Pane

Because the header and footer controls are permanently fixed, all the activity in the task pane happens within the body control. The concept is quite simple. While still within the Startup event for the ProjectWorksheet class, the controls used for entering data, displaying the application instructions, and hosting the glossary terms are added to the body task user control. The following code adds the Instructions control to the body control:

' Prepare the Instructions control.
taskPaneInstructions = New TaskPaneInstructions
taskPaneInstructions.Visible = showInstructions
taskPaneBody.Controls.Add(taskPaneInstructions)

Then, when a specific control has to appear as the result of an event fired by a user action, the BringToFront method is applied to that control. This displays the required control and hides the others from view. Within the sample, this code is located in a routine, called ShowPane, in the ProjectWorksheet class.

Although I could have developed this code with a single control, that would be awkward to manage, especially at design time with the need for so many form controls. Changes and maintenance would also be difficult.

Also, all of these controls load once and persist in class-level variables. Loading occurs immediately when the application starts up. All of these controls perform operations when they load, such as data binding, synchronizing to the spreadsheet, or loading glossary terms. This means the application might take slightly longer to load initially, but it is much more responsive while it is used. Because a lot is happening during startup, loading is less likely to be noticed then.

Finishing Startup

While most of the initial startup event focuses on preparing the actions pane, a few other aspects of the application also occur during loading.

Adding a Control to Toggle the Document Actions Task Pane Display

Another task performed on startup is adding a button to the worksheet to hide and display the Document Actions task pane. As anyone who spends a lot of time in Excel can tell you, more screen space is always better. This feature makes screen space manageable. Notably, the feature uses a PictureBox Windows Forms control, (System.Windows.Forms.PictureBox, that directly resides on the Excel worksheet. Visual Studio 2005 Tools for Office allows you to add managed controls to Excel worksheets. And, even though you have been able to add COM controls to Excel for several versions now, the ability to add managed controls provides more options for building applications. You can add managed controls at design time or run time.

The following code adds the PictureBox control at run time, during the Startup event. It also creates the object's Click event and sets a tooltip for it. Although the PictureBox control resides on the Excel spreadsheet, the code is almost identical to code you would write for use on a Windows Form:

' Add the spreadsheet control to toggle the Document Actions task pane.
Dim imageTaskPaneButton As Image = My.Resources.taskPane
Dim pictureTaskPaneButton As PictureBox = _
    Me.Controls.AddPictureBox(540.75, 0, 18, 18, "picTaskPane")
pictureTaskPaneButton.Image = imageTaskPaneButton
pictureTaskPaneButton.SizeMode = PictureBoxSizeMode.CenterImage
pictureTaskPaneButton.BackColor = Color.FromArgb(201, 219, 250)
AddHandler pictureTaskPaneButton.Click, _
    AddressOf pictureTaskPaneButton_Click

' Add the tooltip text to the button.
ToolTip.SetToolTip(pictureTaskPaneButton, "Toggle Actions Pane On/Off")

Inserting Values in Excel Ranges

The final step processed in the Startup event of the application calls for values to be set in spreadsheet cells. The idea is to apply some data points that can be inferred automatically, including the user's login name from Windows and today's date, and insert them in the Project Summary section of the Capital Expenditure Analysis Model. This saves the user a few steps, and you can enable it relatively easily with the following code:

' Insert the current user name in the SubmittedBy range.
SubmittedBy.Value2 = Environment.UserName
' Insert today's date in the SubmissionDate range.
SubmissionDate.Value2 = Now()

When you look closely, you see that there is no reference to the Excel Range object, and there is no Worksheets(n).Cells(n,n) convention applied. Also, SubmittedBy and SubmissionDate are not assigned variables.

This is possible because of a new type of control, introduced in Visual Studio 2005 Tools for Office, called a host control. Host controls are first-class controls in Visual Studio 2005 Tools for Office projects that extend existing Excel objects. (Microsoft Office Word projects also have associated host controls.) In Excel, there are host controls for named ranges, charts, list objects, and XML mapped ranges. They offer events and data binding capabilities similar to what you expect with other controls based on Windows Forms, but they are still native to Excel and retain all of the functionality specific to Excel.

Host controls simplify solution development. You can add them directly in Visual Studio at design time using the same native Excel commands you use to work in the Excel application. Alternatively, you can add host controls using the Visual Studio Toolbox by dragging them directly onto a worksheet, as shown in Figure 10.

Figure 10. Adding a NamedRange host control in Excel

Because the Capital Expenditure Analysis Model spreadsheet was built previously outside of Visual Studio, Visual Studio 2005 Tools for Office automatically adds all the existing named ranges found in the workbook as host controls when the project is created. This is a tremendous benefit, enabling developers to use existing work without repeating efforts.

Building the Application Menu

Even experienced Microsoft Office developers say that working with CommandBar objects can be challenging. And, although Visual Studio 2005 Tools for Office creates a project reference to the Microsoft Office 11.0 Object Library, which gives you access to Microsoft.Office.Core.CommandBar and all its associated members, the Capital Expenditure Analysis Model application intentionally does not automate Microsoft Office menu bars and toolbars. As mentioned earlier, there are benefits for users when you keep the custom application UI separate from the Excel application UI. But, the biggest benefit may be for developers.

By targeting an actions pane to build the application menu, you do not have to work with the core Microsoft Office objects to construct command bars. The application's menu, which resides in the TaskPaneHeader class, uses a standard System.Windows.Forms.MenuStrip control as the basis for the entire menu. This enables you to set all of the properties for the MenuStrip using the Visual Studio designer (Figure 11), which is often more familiar and easier for most developers. Then, to tie events to application commands, you add code to the Click event for each System.Windows.Forms.ToolStripMenuItems that is added to the MenuStrip.

Figure 11. TaskPaneHeader user control in design mode

Managing Events in the Capital Expenditure Analysis Model

Hooking user events to application actions is one of the main aspects of the Capital Expenditure Analysis Model application. Managing events that occur in any of the task pane controls is analogous to working with Windows Forms events. Those are straightforward. However, when a user clicks through a spreadsheet, it can be complex, and sometimes impossible, to manage events with development technologies in Microsoft Office. In contrast, Visual Studio 2005 Tools for Office host controls make it simpler to manage these spreadsheet events. For example, when you add a named ranged host control to a spreadsheet in design mode and then double-click the cell in the designer, your event handler is stubbed out in code.

The following code automatically generated when I double-clicked the ProjectName named range host control from the Project worksheet within the Visual Studio designer:

Private Sub ProjectName_Change(ByVal Target As _
    Microsoft.Office.Interop.Excel.Range) Handles ProjectName.Change
        ' Add your event code here.
End Sub

Synchronizing the Document Actions Task Pane and the Spreadsheet

Keeping the Document Actions task pane and the spreadsheet acting together as one application requires trapping events in both places. If the user updates a control in the task pane, the changes have to replicate in the spreadsheet. If the user updates the spreadsheet, the task pane has to display those updates.

Although host controls support binding to data sources, they cannot bind directly to controls in the task pane. (Conversely, controls in the task pane cannot bind directly to host controls.) So, trapping events is an acceptable substitute. If a user changes the value of a cell in the worksheet, its Change event is captured and the new value is used to update the control.

The following code, taken from the ProjectWorksheet class in the application, manages the Change event for all the input cells in the Project Details section of the model. When a change occurs to any of these cells, the Change event calls another routine, UpdatePane, which is used by all Change events in the application. Within it, it passes the taskPaneProjectSummary task pane control that must be updated and a reference to the range (Microsoft.Office.Interop.Excel.Range) that changed. Then, within the UpdatePane routine, the Name property of the passed-in range is used to determine which control should be updated and the value is applied:

Private Sub ProjectDetails_Change( _
    ByVal Target As Microsoft.Office.Interop.Excel.Range) _
        Handles _
            ProjectDetailsCostCodeCell.Change, _
            ProjectDetailsDepartmentCell.Change, _
            ProjectDetailsProjectNameCell.Change, _
            ProjectDetailsSubmittedByCell.Change

    UpdatePane(taskPaneProjectSummary, Target)

End Sub

' Updates a task pane control when a spreadsheet range's value 
' changes.
Private Sub UpdatePane( _
    ByVal taskPaneControl As Control, ByVal rng As Excel.Range)

    Dim rangeName As String = rng.Name.Name
    taskPaneControl.Controls(rangeName).Text = rng.Value2

End Sub

The previous code handles the Change events for XML-mapped range host controls and not named-range host controls. You can use either interchangeably and, in the case of this application scenario, both named ranges and ranges mapped to XML elements are available. In the UpdatePane routine, however, a defined range name is implied. The application logic updates the control in the task pane that bears the user-friendly name associated with the defined name of the range that just changed.

Synchronizing in the other direction — from the task pane to the spreadsheet — is similar. In cases where text boxes (System.Windows.Forms.TextBox) are used, the TextChanged event is captured and a routine, UpdateRangeControl, is called while being passed the control that changed:

' Synchronizes task pane with worksheet if any control changes value.
Private Sub Project_TextChanged( _
    ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles _
            CostCode.TextChanged, _
            Department.TextChanged, _
            ProjectName.TextChanged, _
            SubmittedBy.TextChanged

    Globals.ProjectWorksheet.UpdateRangeControl(sender)

End Sub

UpdateRangeControl uses the name of the passed-in control to determine what NamedRange host control has to be updated, and then it sets the host control's Value property with the newly-applied text from the text box. Note the use of the cancelUpdatePane variable, which prevents a looping situation that would occur if the task pane and host control change events fired without being throttled.

' Updates named range control if a task pane control changes value.
Public Sub UpdateRangeControl(ByVal changedControl As Control)
    ' Avoid looping effect while synchronizing worksheet and task pane.
    cancelUpdatePane = True

    Dim controlName As String = changedControl.Name
    Dim controlValue As String = changedControl.Text

    Dim rng As Excel.Range = Me.Controls(controlName)
    rng.Value = controlValue

End Sub

Creating the Project Scorecard

Always having the updated Project Scorecard in view in the Document Actions task pane is a welcome feature for users because they can immediately gauge the profitability of the project they are working on. And, while Visual Studio 2005 Tools for Office enables the task pane, the rest of this feature draws on Excel tricks.

As I mentioned in The Developer's Perspective, I added a second worksheet called Data to the workbook. Within the Data worksheet, I added a named range called Scorecard. The Scorecard range refers to duplicates of the important formulas found on the main Project worksheet, which make up the key project metrics (NPV, IRR, and ROIC). They are duplicated on a hidden worksheet, so that they can be richly formatted and presented in the Document Actions task pane footer (Figure 12).

Figure 12. Data worksheet containing the Scorecard range

The Project Scorecard is displayed in the solution's task pane by capturing the Calculate event for the ProjectWorksheet class. Each time the worksheet is updated and a calculation occurs, an Excel.Range reference is set that refers to the Scorecard named range host control. Then, using the CopyPicture method, a bitmap image of the Scorecard range is sent to the Clipboard. The image is retrieved from the Clipboard as a System.Drawing.Image object. This is accomplished by using the GetImage method on the System.Windows.Forms.Clipboard object, which is then used to set the Image property of a PictureBox control that resides on the taskPaneFooter user control:

' Redraw the Project Scorecard image on worksheet calculate.
Private Sub ProjectWorksheet_Calculate() Handles Me.Calculate

    Dim rng As Excel.Range = Globals.DataWorksheet.Scorecard

    ' Copy the range to the Clipboard.
    rng.CopyPicture(Excel.XlPictureAppearance.xlScreen, _
        Excel.XlCopyPictureFormat.xlBitmap)

    ' Get the image from the Clipboard and resize the control.
    taskPaneFooter.picExcel.Image = Clipboard.GetImage
    taskPaneFooter.picExcel.Height = _
        taskPaneFooter.picExcel.Image.Height + 1
    taskPaneFooter.Height = taskPaneFooter.picExcel.Image.Height

    Clipboard.Clear()

End Sub

This feature is tied to the Excel Calculate event, which places content on the Clipboard. Even though it performs with no noticeable lag, sending images to the user's Clipboard may not be optimal in some scenarios. You could easily create a lower-level, text-based version of the Project Scorecard as a lightweight substitute.

Submitting the Project as XML

The ability to separate spreadsheet data as XML from an Excel workbook is a great advantage in building solutions with Excel. This enables open integration with any other systems that can process XML and allows you to avoid the problem of being locked into the Excel workbook binary file format outside of Excel. Even better, Excel makes this so easy that it takes only one line to export mapped XML data from a workbook, as shown in the following code:

    ' Use the built-in Excel export feature to save data as XML.
    Globals.ThisWorkbook.XmlMaps(1).Export(sFileName)

As mentioned in the sample application, the XML document is only launched in the default XML editor, as shown here:

    ' Show the exported XML document in the browser/default viewer.
    System.Diagnostics.Process.Start(sFileName)

The key idea is that after you have the XML as a file (or as a string of XML, if you use the ExportXml method), you can use Visual Studio to load it into a dataset, database, or Web service and process it with greater simplicity than if it was still trapped inside the workbook.

Deploying the Solution

There are three main deployment options for Visual Studio 2005 Tools for Office solutions, depending on your requirements for code maintenance and document distribution.

Model 1: The Word (or Excel) document and the associated assembly are deployed to a user's local computer.

Model 2: The Word (or Excel) document is deployed to a user's local computer. The associated assembly is deployed to a network share (UNC) or Web server (HTTP).

Model 3: The Word (or Excel) document and the associated assembly are deployed to a network share (UNC) or Web server (HTTP).

For more information, see Deploying Office Solutions.

Code Security Considerations

The sample code included with this article is intended for instructional purposes; it should not be used in deployed solutions without modifications. In particular, code security must be taken into greater consideration.

Potential threats with the sample application have been identified using the threat modeling process and tools described in the Threat Modeling section of the Microsoft Security Developer Center.

The following are some examples of identified threats that should be taken into consideration before expanding or deploying this solution.

• XML data files are altered. The sample application uses external XML files as sources for the investment items that are displayed and the glossary terms list. If these files are compromised, invalid data could be used within the application or the application could stop functioning properly. You can mitigate this threat by marking these files as read-only or by moving the data into a more secure, protected database that limits user access.

• Visual Studio 2005 Tools for Office Assemblies are replaced. If the Visual Studio 2005 Tools for Office assemblies are replaced with other assemblies, the application behavior could be altered to compromise the system. You can mitigate this threat by signing the code, using strong name conventions, or providing hash evidence. This sample does not currently implement these precautions, but they are strongly recommended before deploying an application to a production environment.

• Copying to the Clipboard is intercepted. In creating the Project Scorecard feature, the sample solution uses the Clipboard to return an image of an Excel range containing project data. If the contents of the Clipboard are accidentally or maliciously altered or emptied, the application could display incorrect information. You can remove this threat by replacing the reliance on the Clipboard with a feature that directly displays the content of the Excel range as secured data.

For more information about code security, see the Microsoft Security Developer Center.

Conclusion

Using Visual Studio 2005 Tools for Office, you can easily turn an Excel spreadsheet into a richer, more user-friendly, integrated application that blends the best of Microsoft Office with the power of Visual Studio and the .NET Framework. With many advantages compared to earlier Microsoft Office development technologies, Visual Studio 2005 Tools for Office enables you to create new types of applications in Microsoft Office that were previously unavailable.

Additional Resources

See the following resources for more information.

Visual Studio 2005 Tools for Office

• Microsoft Office Developer Center: Visual Studio Tools for Office Developer Portal

• Microsoft Visual Studio Developer Center

• Visual Studio Tools for Office documentation

• Microsoft Visual Studio 2005 Tools for the Microsoft Office System blog

• Visual Studio Tools for Office Forum

• Visual Studio Tools for the Microsoft Office System: Office Development Samples

Code Security

• Writing Secure Code, Second Edition

• Microsoft Security Developer Center

• Security at Home: Protect Your PC

Office Developer Center

• Microsoft Office Developer Center

• Microsoft Office Developer Center: Excel

About the Author

Charles Maxson specializes in designing and developing smart client solutions featuring the Microsoft Office System. As one of the resident zealots at OfficeZealot.com, he evangelizes the virtues of Office as a solutions platform.