Building a Professional Stock Allocation System Using Visual Studio Tools for Office, Version 2003

Article
12/18/2006

November 2004

Applies to:
     Microsoft Visual Basic .NET Standard 2003
     Microsoft Visual Studio .NET Professional 2003
     Microsoft Visual Studio .NET Enterprise Developer 2003
     Microsoft Visual Studio Tools for the Microsoft Office System, Version 2003
     Microsoft Office Professional 2003
     Microsoft Office Excel 2003

Summary: Learn how to create a Microsoft Excel stock allocation solution in Microsoft Visual Basic .NET using Microsoft Visual Studio Tools for the Microsoft Office System, Version 2003. (61 printed pages)

Download StockAllocation.msi.

Introduction
Setting Up the Stock Allocation Solution
Applying Visual Studio Tools for Office to the Business of Professional Money Management
Designing the Development Architecture
Developing the Stock Allocation System Foundation
Developing the Stock Allocation System Business Functionality
Understanding Limitations of the Stock Allocation System
Conclusion
Resources

Introduction

As professional software developers, we are under ever-growing pressure to deliver increasingly sophisticated solutions. Users expect a highly interactive experience with access to their data from anywhere at any time. At the same time, development managers demand that we reduce the time and cost of delivering, enhancing, and maintaining software. We are asked to deliver more but spend less time doing it. To achieve these seemingly opposing goals, we need to find more effective ways to apply our development tools.

Although commonly associated with different problem spaces, two of the most well-known Microsoft tools are Microsoft Office Professional 2003 and Microsoft Visual Studio .NET 2003 Enterprise Edition. With the introduction of Microsoft Visual Studio Tools for the Microsoft Office System, Version 2003, developers can now apply these tools more effectively by combining them.

Note Visual Studio Tools for the Microsoft Office System, Version 2003 can also be installed on Microsoft Visual Studio .NET Professional 2003 or Microsoft Visual Basic .NET Standard 2003.

Office 2003 is among the most powerful office-automation tools in use today. It provides rich, highly interactive user tools along with powerful charting, data management, document management, and calculation capabilities. Office 2003 also supports sophisticated automation of user tasks through the integration of Microsoft Visual Basic for Applications (VBA).

Visual Studio 2003 is a complete set of professional development tools providing a variety of rapid application development (RAD) capabilities, including drag-and-drop designers and code generators. In conjunction with the Microsoft .NET Framework 1.1, Visual Studio 2003 gives developers access to thousands of support classes and enables developers to build enterprise-quality applications utilizing a variety of common development techniques, such as object-oriented, interface-based, and aspect-oriented programming.

Using Visual Studio Tools for Office, both Microsoft Office Excel 2003 and Microsoft Office Word 2003 can now load and execute .NET Framework–based assemblies. This means that we can now apply the professional development capabilities of Visual Studio 2003 and the .NET Framework to build applications that have complete access to the Excel 2003 and Word 2003 object models, taking full advantage of the rich client-side capabilities and user experience provided by Office 2003.

Setting Up the Stock Allocation Solution

System Requirements

To load and build the downloadable Stock Allocation solution you must have all of the following installed.

Microsoft Visual Studio .NET 2003 Enterprise Edition

Note You can also build the Stock Allocation solution with Microsoft Visual Studio .NET Professional 2003 or Visual Basic Standard 2003, but it was not tested with these configurations.
Microsoft Office Professional 2003

Note You must perform a complete install of Microsoft Office
Microsoft Visual Studio Tools for the Microsoft Office System, Version 2003

Caution Products must be installed in the order listed. If you do not follow the described order, you may have difficulty opening and creating Visual Studio Tools for Office projects.

Installing the Stock Allocation System

If you have not already done so, install Microsoft Visual Studio .NET, Microsoft Office Professional 2003, and Visual Studio Tools for Office, Version 2003 as described above.
Download the sample application that accompanies this article by clicking the StockAllocation.msi link at the top of this article. In the Microsoft Download Center, click Download and follow the instructions to run the installation.
Install StockAllocation2003.msi.
Open the installed solution file, StockAllocation.sln.
Build and execute the solution.

Note If you have any problems building or executing the solution, refer to ReadMe.doc, contained in the solution.

Applying Visual Studio Tools for Office to the Business of Professional Money Management

I think the best way to explore what it is like to create an application using Visual Studio Tools for Office is to build one. Rather then explore Visual Studio Tools for Office as a list of technical features; we are instead going to walk through the process of implementing a complete solution to a real business process.

Prior to becoming an independent consultant, I developed software for a brokerage firm in New York City from 1989 through 2000. Most of my work focused on developing portfolio-reporting and trade-management systems. The solution we build here is based on a portion of the real business requirements for a stock allocation system I created at that time.

The solution incorporates the Excel features of Visual Studio Tools for Office and is programmed using Visual Basic .NET. Our discussion covers the entire development process from review of business requirements through the design and architecture of the system, and finishes with a detailed review of the code and implementation.

A Little About the Business

Professional money managers are people who are paid to manage the stock accounts of several clients. The group of managed accounts is commonly known as the money manager's book. To do their job effectively, the money manager needs to analyze summary information of the whole book as well as individual account information.

A money manager commonly relies on two summary views of the book to assist with decision-making. The first is the summary of accounts, which lists descriptive information for each account, such as the account number, name, and risk tolerance—as well as financial information such as account value, profit/loss, and amount of cash available. The other summary view is the summary of stocks, which lists information on all stocks contained in the book, including the number of accounts holding each stock and the total value of all shares of that stock.

To analyze an individual account, the money manager needs a detailed view of each account. The primary purpose of the detailed view is to provide a complete list of all stocks held by the account. For each stock held in the account, the list includes the number of shares of each stock, the purchase price of the stock, the current value of the stock, and whether that stock position is currently profitable. In making stock purchase decisions, a money manager may frequently move back and forth between the account summary and individual account details.

In addition to reviewing analytical information, money managers also must deal with relatively complex stock allocation scenarios when determining how much of a particular stock to purchase for each client.

Although money managers may sometimes make stock purchase decisions on an individual account basis, purchase decisions are more commonly made using a formula. One common technique is to select accounts based on the account's risk tolerance relative to the stock's perceived risk, and then invest a fixed percentage of each account's value in that stock. For each account with the appropriate risk tolerance, we find the number of shares to purchase by determining the dollar value of the target percentage and then dividing that dollar value by the share price.

For example, the money manager determines that a two percent position in a stock judged to be aggressive (high-risk) and priced at $US25 is appropriate. Table 1 shows the number of shares to purchase for several accounts.

Table 1. Example of allocating a two percent position in an aggressive stock priced at $US25

Account Risk-Tolerance	Account Value	2% of Account Value	Shares to Purchase
Aggressive	$US300,000	$US6,000	240 (6,000 / 25)
Moderate	$US250,000	N/A	0
Aggressive	$US750,000	$US15,000	600 (15,000 / 25)
Income (low-risk)	$US600,000	N/A	0
Aggressive	$US100,000	$US2,000	80 (2,000 / 25)

If determining the number of shares to purchase were just a simple calculation, things would be easy, but there is also the matter of "blocks." When purchasing stocks, one generally gets a more favorable price if one purchases the block size of that stock. Individual stocks have different block sizes but most use a block size of 100 shares, meaning that the stock should be purchased in multiples of 100. In some cases, shares are rounded to the nearest block size but, most commonly, they are just truncated.

If the stock purchased in our previous example has a block size of 100 shares, the three accounts with the appropriate risk tolerance end up with the allocations shown in Table 2 after factoring in the block size.

Table 2. Factoring a block size of 100 shares into the allocation from the previous table

Account Risk Tolerance	Account Value	2% of Account Value	Shares to Purchase
Aggressive	$US300,000	$US6,000	200 (previously 240)
Aggressive	$US750,000	$US15,000	600 (unchanged)
Aggressive	$US100,000	$US2,000	0 (previously 80)

We also need to consider, when calculating the allocation, the amount of cash each account has available. Simply put, an account must be able to pay for the stock purchase. When factoring in the cash available, the amount of shares to purchase is based on the lesser of the target percentage and the cash available.

So, factoring cash available into our example for the two remaining accounts with shares allocated, we might end up with an allocation similar to Table 3.

Table 3. Example allocation, updated to limit allocation to available cash

Account Value	Cash Available	2% of Account Value	Shares to Purchase
$US300,000	$US12,000	$US6,000	200 (unchanged)
$US750,000	$US2,500	$US15,000	100 (2,500 / 25) (previously 600)

Although not part of the core calculation, block size and cash available have a notable effect on the allocation. In our example, what was originally a 920-share allocation has ultimately become a 300-share allocation.

Finally, there is the issue of individual discretion. These calculations are ultimately just the starting point. Most money managers review the calculated allocation numbers making small adjustments to the number of shares to purchase for some accounts based on the money manager's knowledge and experience prior to actually purchasing the stock.

Note One additional issue in the allocation process is the handling of accounts that already own the stock being allocated. For simplicity, we exclude any account that already owns the stock.

In real life, the money manager would decide how to handle such accounts. The options include excluding these accounts (as we've done), purchasing the additional shares required to increase the existing stock position to the desired percentage, and possibly even selling shares to reduce the position of any account whose position is greater then the target percentage.

Designing a Solution

To review our requirements, professional money management is a highly interactive process made up of a combination of data analysis and data interaction. The money manager must be able to review both summary and detailed information easily. When performing stock purchase allocations, the money manager starts with a list of suggested shares for a subset of accounts based on risk criteria, a calculated ratio, and business rules, and manually modifies some of these allocations at his or her own discretion.

With the nature of the business process being so interactive, it is essential that our solution supports easy and efficient user interaction. The interface must be easy to read, providing at-a-glance access to information, while at the same time allowing the user to interact with individual data values.

These requirements are a natural fit for Excel 2003. Using Excel 2003, each of the four required data views—account summary, stock summary, account detail, and stock allocation—is implemented as a separate worksheet within a single workbook.

The Account Summary Worksheet

Most money managers start the day by taking a high-level look across all accounts. Therefore, we designed our workbook such that the first worksheet displayed is the Account Summary worksheet. The Account Summary worksheet provides this high-level view by displaying relevant values from each account, as shown in Figure 1.

Figure 1. Account Summary worksheet data (click picture to see larger image)

In addition to providing a high-level view of the account data, the Account Summary worksheet also serves to help the money manager determine if any account may require special attention. There are three common scenarios that signal that an account may require special attention: a large amount of cash available, poor profits, or an account representing a large percentage of the overall book.

Being aware of these factors is an important part of successful money management. Therefore, the Account Summary worksheet provides a chart of each. The cash available and profit values display as bar charts so that the user can easily identify exceptionally large or small values. Account values display as a pie chart so that the user can also compare each account's relative value easily. Figure 2 shows the account profit/loss chart. Figure 3 shows the account value chart.

Figure 2. Account Profit/Loss bar chart (click picture to see larger image)

Figure 3. Account Value pie chart (click picture to see larger image)

The money manager, throughout the course of the day, frequently needs to move from the Account Summary worksheet to a particular Account Detail worksheet. For this reason, it makes sense for the Account Summary worksheet to serve also as the menu to the Account Detail worksheet. We can achieve this by providing a toolbar button that, when clicked, populates and displays the Account Detail corresponding to the current selection on the Account Summary worksheet. This allows the money manager to select a cell in the Account Summary worksheet and view the details by simply clicking the appropriate toolbar button.

There is not a lot of data maintenance performed by money managers but the need to reclassify an account's risk tolerance may occasionally occur, so the Account Summary provides a drop-down selection for each account's risk tolerance, as shown in Figure 4. The money manager can update the account's risk tolerance by choosing the appropriate selection from the drop-down list.

Note The choice to use three risk tolerances, Aggressive (high risk), Moderate, and Income (low risk), has been made to avoid unnecessary complications. In real life, there would be far more categories. The real categories could be based on any number of criteria, including risk tolerance, investment strategy, or even family relationship.

Figure 4. Updating account risk tolerance from the Account Summary worksheet

The Account Detail Worksheet

As its name implies, the Account Detail worksheet provides a detailed view of an individual account. The worksheet includes a detailed listing of all stocks held by the account, including purchase cost, current value, and profit/loss. For convenience, the Account Detail worksheet also includes high-level information on the account, such as total account value, cash available, and overall profit/loss. This data appears as shown in Figure 5.

Figure 5. Account Detail data (click picture to see larger image)

Although all of the information on the Account Detail worksheet is important, the money manager is looking for two primary things. First, the money manager needs to determine how evenly the value of the account is distributed across the different stocks. Second, the money manager must be aware of any stock with an exceptional profit or loss. The Account Detail worksheet charts both pieces of information. It displays the stock values as a pie chart, comparing each stock's value relative to the overall account. The profit/loss of each stock displays as a bar chart, so that it is easy to identify large profit or loss values.

As in the Account Summary worksheet, the user can also modify account risk tolerance from the Account Detail by selecting the desired value from the drop-down list, as shown in Figure 6.

Figure 6. Modifying account risk tolerance from Account Detail worksheet

The Stock Summary Worksheet

The Stock Summary worksheet is the simplest of the four worksheets but serves the important purpose of helping the money manager identify the distribution of stocks across the whole book. The worksheet lists information on every stock held by the money manager, including the total number of shares, total value, and the number of accounts that own the stock. Figure 7 shows the Stock Summary data.

Figure 7. Stock Summary data

Stock distribution is determined primarily by two values: the total value of the stock relative to other stocks, and the total number of accounts holding the stock. These two values are charted, with the stock values as a pie chart and the total number of accounts as a bar chart. Although interesting, the number of shares held of a particular stock is not very important, because stock prices vary widely.

The Stock Allocation Worksheet

To wrap up the worksheet design, we have the Stock Allocation worksheet, which is the most interactive of the worksheets because it handles both criteria-gathering and displaying stock allocations. When performing an allocation, the first step is to enter the allocation criteria, so the Stock Allocation worksheet is first displayed (as shown in Figure 8) ready to receive the stock allocation criteria. The Stock Allocation worksheet can be reached from any of the other worksheets by clicking the "New Stock Allocation" toolbar button.

Note The selection criteria are pre-populated with reasonable values as a convenience for the user of the sample. In real life, a system like this would normally minimize the use of default values to avoid errors introduced by the user overlooking one of the criteria.

Figure 8. Stock Allocation worksheet waiting for allocation criteria (click picture to see larger image)

The first step in creating the allocation criteria is identifying the stock to purchase, by using the drop-down menu shown in Figure 9. Selecting the stock automatically displays the stock price.

Figure 9. Selecting the stock to use in an allocation

After the money manger chooses the stock, he or she enters the remaining criteria of target percentage, stock block size, whether to limit the purchase to cash available, and the desired risk tolerance. To view the resulting allocation, the money manager clicks the "Display Accounts" toolbar button that initiates the corresponding calculations for the account selection and applies the appropriate business rules—producing a list of account allocations as shown in Figure 10.

Figure 10. Stock Allocation worksheet displaying account allocations (click picture to see larger image)

Notice the resulting list has two allocation columns: "Suggested Allocation" and "Allocation to Apply." Initially both columns contain the same values, which are the calculated result of applying the criteria and business rules. Providing two allocation columns enables the money manager to utilize his or her discretion to change the number of shares actually allocated to each account by modifying the value in the "Allocation to Apply" column, while still being able to view each account's originally determined value in the "Suggested Allocation" column. Once the money manager reviews the allocations and makes the desired changes, he or she applies the allocation to the accounts by clicking the "Apply Allocation" button.

Choosing allocation criteria is often an iterative process, so the Stock Allocation worksheet supports modifying the criteria. The money manager is free to modify the criteria as often as desired. After each criteria modification, the money manager must click the "Display Accounts" toolbar button to view the updated list. Be aware that clicking the "Display Accounts" button overwrites any manually entered allocations. To clear an allocation, click the "New Stock Allocation" toolbar button.

Designing the Development Architecture

It is safe to assume that the data used by our stock allocation system would originate in a database; however, expecting consistent database access is unreliable. In fact, expecting even basic network availability is completely unreliable.

Architecting As a Smart Client

Today, most executive computer users (money managers are no exception) utilize a laptop as their only computer, doing much of their work while traveling. For this reason, our stock allocation system was created as a smart client, providing the user with a consistent, quality experience without regard for database or network availability.

As a smart client, our stock allocation system caches all data locally, providing the user with complete functionality without directly reading or updating the database. A Web service updates the stock prices.

By using a Web service, the local data cache can be updated with the most recent prices from any Internet connection with no firewall concerns and without establishing a virtual private network (VPN) connection to the corporate network. When no Internet connection is available, the last available prices are used. Using a Web service enables our application to take advantage of best-of-breed services without regard for their platform or implementation. In the case of the stock allocation system, we use a Java-based Web service provided by XMethods to retrieve stock quotes (delayed by twenty minutes).

Note To view the details of the Web service, visit XMethods Delayed Stock Quote.

The .NET Framework and Visual Studio 2003 provide everything necessary to create our stock allocation system as a smart client.

First, the .NET Framework includes an in-memory data cache known as the DataSet. The DataSet provides many common features normally associated with a database without requiring network or database access. The .NET Framework DataSet class is an essential part of our smart client architecture. Table 4 lists the relevant features of the DataSet.

Table 4. .NET Framework DataSet smart client features

Feature	Description
Disconnected Access	The DataSet holds all data within the local application memory space, facilitating a rich user experience even when no network or database access is available.
Local storage	The DataSet can persist both data and schema as a local XML file and can be restored from that same file. This enables the DataSet to be saved to the local disk safely at the end of application execution and then be restored when the application is restarted.
Relational Data Model	The DataSet maintains the relational table model of the original database, avoiding the overhead of mapping the data to a different representation and avoiding the need to have developers learn a different data representation.
Familiar Query Engine	The DataSet provides a query engine similar to that of a relational database. This enables the user to filter and sort the data easily, and takes advantage of existing developer skills.
Database Synchronization	The DataSet provides full fidelity round-tripping of data between the database and local cache. When database connectivity is available, the DataSet can populate itself directly from the database and write locally made changes back to the database server.

The combination of Visual Studio 2003 and the .NET Framework further enhances the smart client experience by providing easy access to the pricing Web service. The .NET Framework provides the necessary support classes to provide access to and support for calling Web services. Visual Studio 2003 further simplifies Web service access by generating a client-side proxy that abstracts the communication and message formatting details of accessing the Web service.

Implementing the Solution in Excel 2003

Primarily, the strengths of Excel 2003 as a user interface drove the choice to build the system using Visual Studio Tools for Office and Excel 2003. Here are some of the key benefits of using Excel 2003 as a user interface.

Excel provides a familiar user experience, which both improves user acceptance and reduces the user's overall learning curve.
The chart and graph capabilities of Excel provide an easy-to-read, attractive, and very professional appearance.
Presenting the data on Excel worksheets automatically makes the data interactive, meeting the requirement that the user be able to easily view and modify data.

Developing the Stock Allocation System Foundation

The first step in building the stock allocation system is to create a Visual Studio 2003 project for Visual Studio Tools for Office. To create a Visual Studio Tools for Office Excel workbook project, do the following.

From Visual Studio 2003, on the File menu, click New\Project.

This displays the "New Project" dialog.
Expand the "Microsoft Office Systems Projects" node in the left pane.
Under "Microsoft Office Systems Projects," select "Visual Basic Projects."

The "New Project" dialog should now appear similar to Figure 11.
In the right pane, select "Excel Workbook."
Enter "StockAllocation" for the project name.
Enter the location in which you would like to create the project.
Click OK.

Figure 11. Visual Studio 2003 New Project dialog creating an Excel Workbook project (click picture to see larger image)

Visual Studio 2003 then displays the "Microsoft Office Project Wizard" dialog, which associates an Excel 2003 workbook with your project. You have the option of using an existing Excel 2003 workbook or creating a new one, as shown in Figure 12. In creating the stock allocation system, I simply accepted the defaults, allowing Visual Studio 2003 to create a workbook using the project name as the workbook name and placing the workbook in the same directory as the rest of the project.

Figure 12. Visual Studio 2003 Microsoft Office Project Wizard dialog (click picture to see larger image)

The generated project contains only two source files: AssemblyInfo.vb and ThisWorkbook.vb. We are going to focus on ThisWorkbook.vb.

Note The AssemblyInfo.vb source file is common to all Visual Studio 2003 Visual Basic .NET projects. It contains the list of assembly-level attributes, also known as global attributes, to apply to the generated assembly. For more information, see Global Attributes.

ThisWorkbook.VB

The generated ThisWorkbook.vb source file does not contain much code, but it has everything we need to start building our stock allocation system. It Imports commonly used namespaces, provides startup information to the Excel 2003 runtime, and contains the application startup class itself. Let us look at the contents of the file.

The following Imports are at the top of the file.

Imports System.Windows.Forms
Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
Imports MSForms = Microsoft.Vbe.Interop.Forms

As you probably know, Imports allows us to use classes in the imported namespaces without needing to qualify each class name fully. The System.Windows.Forms namespace is familiar but the other three are new to Visual Studio Tools for Office. They import the commonly used namespaces when developing an Excel 2003 solution. Table 5 lists the purpose of each.

Table 5. Visual Studio Tools for Office namespaces

Namespace	Description
Microsoft.Office.Core	Core Microsoft Office Professional 2003 object model
Microsoft.Office.Interop.Excel	Excel 2003 object model
Microsoft.Vbe.Interop.Forms	Microsoft Forms 2.0 Library, a control library commonly used when developing Office 2003 solutions

Using an equal sign (=) as part of the Imports statement may be unfamiliar. The equal sign notation defines an alias for the full namespace and is used to reduce typing. For example, having these Imports allow us to declare a Worksheet variable using the following syntax.

Dim ws as Excel.Worksheet

Otherwise, we would have to fully qualify it, as in the following syntax.

Dim ws as Microsoft.Office.Interop.Excel.Worksheet

The equal sign Imports notation is useful in cases where there may be class name collisions between namespaces.

The next thing we find in TheWorkbook.vb is the System.ComponentModel.Description attribute.

<Assembly: System.ComponentModel.DescriptionAttribute("OfficeStartupClass, 
Version=1.0, Class=StockAllocation.OfficeCodeBehind")>

The runtime uses this attribute to identify which class Excel 2003 should load at startup. In our case that is the OfficeCodeBehind class in the StockAllocation namespace, as defined by the "Class=StockAllocation.OfficeCodeBehind" portion of the attribute. Avoid changing the contents of this attribute because any errors are likely to prevent your solution from executing. There is one exception. If you change the name of the OfficeCodeBehind class or the project namespace, you need to modify the value following the "Class=" to be consistent with the new class name or namespace.

Finally, we get to the OfficeCodeBehind class. Table 6 shows descriptions of the class members.

Public Class OfficeCodeBehind 

  Friend WithEvents ThisWorkbook As Excel.Workbook
  Friend WithEvents ThisApplication As Excel.Application

  Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
  End Sub

  Private Sub ThisWorkbook_BeforeClose(ByRef Cancel As Boolean) Handles ThisWorkbook.BeforeClose
    Cancel = False
  End Sub

End Class

Table 6. Relevant members of the OfficeCodeBehind class

Member	Description
ThisWorkbook	Contains a reference to the current workbook This field is automatically set at application startup
ThisApplication	Contains a reference to the current Excel 2003 application instance This field is automatically set at application startup
ThisWorkbook_Open	Called when the workbook is first opened Possibly the most important method in the entire application because this is your first opportunity to interact with the workbook
ThisWorkbook_BeforeClose	Called just before the workbook is closed This is your opportunity to perform any cleanup or other final tasks Setting the Cancel parameter to true terminates the close process

Most work within the OfficeCodeBehind class is done either in the ThisWorkbook_Open method or by adding additional members. We see more of the OfficeCodeBehind class throughout our application.

Adding Global Scope

The OfficeCodeBehind fields ThisWorkbook and ThisApplication are extremely important and we use them extensively throughout the entire application. Making sure both of these references are easily accessible helps keep our programming simple. The solution that I find works well is to introduce a Globals class containing shared (static) properties exposing these objects.

Like the OfficeCodeBehind class, the Globals class also requires that we import the Office and Excel namespaces.

Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel

The Globals class has two Private shared fields and two Friend shared properties to hold the Excel 2003 Application and Workbook references, as shown here.

Public Class Globals
  Private Shared excelApplicationInstance As Excel.Application
  Private Shared excelWorkbookInstance As Excel.Workbook

  Friend Shared ReadOnly Property ExcelApplication() As Excel.Application
    Get
      Return excelApplicationInstance
    End Get
  End Property

  Friend Shared ReadOnly Property ExcelWorkbook() As Excel.Workbook
    Get
      Return excelWorkbookInstance
    End Get
  End Property
End Class

With the properties being read-only, they also need a function to initialize the references.

Friend Shared Sub InitApplicationAndWorkbook(ByVal excelApplication As 
Excel.Application, ByVal excelWorkbook As Excel.Workbook)
  excelApplicationInstance = excelApplication
  excelWorkbookInstance = excelWorkbook
End Sub

By declaring Globals members as shared, they are usable without needing to create and store a reference to the Globals class, meaning that Globals.ExcelApplication and Globals.ExcelWorkbook are globally accessible throughout the entire application.

We can now initialize the members of Globals by calling Globals.InitApplicationAndWorkbook at the beginning of the OfficeCodeBehind.ThisWorkbook.Open method and passing ThisWorkbook and ThisApplication as parameters. Remember, Visual Studio Tools for Office automatically initializes these two references at startup.

Public Class OfficeCodeBehind 

  Friend WithEvents ThisWorkbook As Excel.Workbook        ' initialized by Visual Studio Tools for Office

  Friend WithEvents ThisApplication As Excel.Application  ' initialized by Visual Studio Tools for Office

  Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
    ' Store Application and Workbook reference in Globals
    Globals.InitApplicationAndWorkbook(ThisApplication, ThisWorkbook)
  End Sub

End Class

We must use global members with caution because it can be difficult to keep track of all the places that modify them. In the case of our Globals class, this is not as much of a concern because the members are initialized at the very beginning of the program and then only read after that.

This initialize-once usage pattern is the reason I made ExcelWorkbook and ExcelApplication ReadOnly properties with a separate initialization method, rather than making the properties read/write. Although the implementation does not enforce the concept of setting the values only once, it does make this intention more obvious. To enforce this usage, we could simply add a check at the beginning of InitApplicationAndWorkbook to verify that ExcelWorkbook and ExcelApplication are set to Nothing, and throw an exception if that is not the case.

Creating the Worksheet Foundation

As we discussed, four separate worksheets make up the stock allocation system: Account Summary, Account Detail, Stock Summary, and Stock Allocation. The logic for populating, managing, and displaying each worksheet is specific to the respective worksheet classes. There is, however, some housekeeping functionality common to all four worksheet classes that we can centralize by creating a base class for the worksheet classes. Using a base class eliminates the need to write redundant code within the worksheet classes and insures that common behaviors are implemented consistently across the worksheets.

The worksheet base class is called WorksheetBase. Because we mean for it to serve as a base class to the other worksheet classes and for it not to stand alone, it is marked as MustInhert.

Public MustInherit Class WorksheetBase

WorksheetBase contains a field, myWorksheet, to hold a reference to each class's physical worksheet. myWorksheet is marked as WithEvents to allow event handling. The worksheet reference is made publicly available through the read-only property ExcelWorksheet.

Protected WithEvents myWorksheet As Excel.Worksheet
Public ReadOnly Property ExcelWorksheet() As Excel.Worksheet
  Get
    Return myWorksheet
  End Get
End Property

At this point, our WorksheetBase class is not of much value because the only thing it provides is an Excel.Worksheet reference. One convenient feature would be to let the base class resolve the Excel.Worksheet reference, because it is something every worksheet class needs to do.

Given the name of a worksheet, we find the Excel.Worksheet reference using the Excel.Application object's Worksheets collection. The WorksheetBase class does this in its constructor, also known as the New method. The constructor accepts the worksheet name as an argument, locates the Excel.Worksheet reference, and stores it in the myWorksheet member variable. The constructor also includes error checking, verifying that a worksheet with the passed name is actually located.

Public Sub New(ByVal worksheetName As String)
  myWorksheet = Globals.ExcelApplication.Worksheets.Item(worksheetName)
  If myWorksheet Is Nothing Then
    Throw New ApplicationException("Worksheet name not found: " & worksheetName)
  End If
End Sub

As you know, a constructor is a method that is automatically called when a class instance is created. In the case of WorksheetBase, the class is never directly constructed but the derived worksheet classes are. Each of the derived classes calls the WorksheetBase constructor from their respective constructor, passing in their worksheet's name. At the completion of class construction, each worksheet class has access to the appropriate Excel.Worksheet reference through the inherited property, Excel.Worksheet, which exposes the myWorksheet field. We see the WorksheetBase constructor in action when we look at the other worksheet classes.

Working with Ranges

Ranges are also an important part of interacting with the worksheets. The WorksheetBase class contains several methods for creating Excel.Range instances. First, we have the CreateRange method. Although very simple, this method proves very useful as ranges are frequently used to set or read a cell value. It is not uncommon to determine a cell by separate column and row values rather than by a distinct cell ID such as "B3." This method encapsulates the details of combining the separate column and row names into a single cell name and creates that range on the specified worksheet.

Public Function CreateRange(ByVal ws As Excel.Worksheet, ByVal column As String, _
      ByVal row As Integer) As Excel.Range
  Dim rangeString As String = column & row.ToString()
  Return ws.Range(rangeString)
End Function

Since most of the range work we need to do is for the worksheet object managed by the worksheet class, we also add an overloaded version of this method that creates the range on the worksheet class's contained worksheet object.

Public Function CreateRange(ByVal column As String, ByVal row As Integer) As Excel.Range
  Return CreateRange(ExcelWorksheet, column, row)
End Function

There are several occasions where the column value for a range is more easily expressed as an integer rather than as a letter. This is especially true when extracting the column value from an existing range's Column property. For this case, WorksheetBase includes several CreateRange overloads that accept the column values as an integer rather than as a string.

Excel 2003 supports expressing worksheet locations with integer column values using R1C1 format. Using R1C1 format, the cell located at the second column and the third row, commonly expressed as "B3," is expressed a "R3C2": row 3, column 2. The one challenge of R1C1 format is that although valid for use in formulas, it cannot be used to directly access ranges. Ranges must be accessed using the traditional format, also known as A1 format. Fortunately, the ConvertFormula method of the Excel.Application object can convert R1C1 format to A1 format.

The following is the CreateRange method to create a range over a single cell using an integer column value.

Public Function CreateRange(ByVal column As Integer, ByVal row As Integer) _
    As Excel.Range
  Dim rangeR1C1 As String = String.Format("R{0}C{1}", row, column)
  Dim rangeA1 As String = Globals.ExcelApplication.ConvertFormula( _
    rangeR1C1, Excel.XlReferenceStyle.xlR1C1, Excel.XlReferenceStyle.xlA1)
  Return ExcelWorksheet.Range(rangeA1)
End Function

To create the range instance, the column and row values are first formatted into a string in R1C1 format. The call to ConvertFormula returns a string expressing the same range in A1 format, which is then passed to the Range property to retrieve the desired range instance.

In addition to the CreateRange methods, WorksheetBase also contains the CreateNamedRange method. As you might expect, a named range allows you to associate an arbitrary string name with an area on a worksheet. In general, it is preferable to use named ranges instead of referencing specific cell locations because named ranges make your application more tolerant of changes the user may make to the worksheet. Once a named range is created, that range always refers to the same specific cells even if changes to the worksheet, such as inserting or deleting surrounding rows and columns, alter the location of those cells on the worksheet.

The CreateNamedRange method accepts a string containing the name to associate with the range and a string identifying the range of cells to name. The named range is then created by passing these two values to the Add method on the Names collection of the ExcelWorksheet. The Add method requires that the string containing the cells in the range begin with an equal sign (=), so CreateNamedRange prepends an equal sign if one is not already present. As a convenience, CreateNamedRange returns a reference to the named range. After calling CreateNamedRange, the program can access the range by using the returned range reference or by passing a string containing the range name to the ExcelWorksheet.Range property.

Public Function CreateNamedRange(ByVal rangeName As String, ByVal rangeFormula As String) As Excel.Range
  ' If range formula does not begin with the required "=" then add it.
  Dim tempRangeFormula As String
  tempRangeFormula = IIf(rangeFormula.StartsWith("="), rangeFormula, "=" & rangeFormula)

  ' Create the named range and return a reference
  Dim tempName As Excel.Name = ExcelWorksheet.Names.Add(rangeName, tempRangeFormula)
  Return ExcelWorksheet.Range(rangeName)
End Function

The derived worksheet classes use the CreateNamedRange method extensively. The worksheet classes do not rely on fixed cell locations. Instead, they utilize named ranges to identify each point of interaction on the worksheet. There are even separate named ranges for each column of data displayed on the worksheets.

Before we leave the range-related methods, we need to look at one special method, SetColumnSpanRange. Each derived class is required to call this method in its constructor after creating the named ranges identifying the data display columns. The data display column ranges are passed to SetColumnSpanRange as an array. SetColumnSpanRange then combines all of these ranges into a single range using the Union method of the Excel.Application object, and stores the resulting range in the columnSpanRange member variable.

' Range spans all display columns on the worksheet
Protected columnSpanRange As Excel.Range

' Create a single range spanning all column ranges
Protected Sub SetColumnSpanRange(ByVal rangeList As Excel.Range())

  Select Case rangeList.Length
    Case 0
      Throw New ApplicationException("Error creating spanRange - no columns specified")
    Case 1
      columnSpanRange = rangeList(0)
    Case Else
      columnSpanRange = Globals.ExcelApplication.Union(rangeList(0), rangeList(1))
      For i As Integer = 2 To rangeList.Length - 1
        columnSpanRange = Globals.ExcelApplication.Union(columnSpanRange, rangeList(i))
      Next
  End Select

End Sub

The named ranges for each data column represent a single cell, indicating where the data for that column should start. When the data is displayed, each data value for the column is written as an offset from that cell. By combining the individual data column ranges, columnSpanRange becomes a single range spanning the entire first row of data for that worksheet. This allows WorksheetBase to be aware of the location of each worksheet's displayed data.

We talk more about SetColumnSpanRange later, in the section "Populating the Account Summary Worksheet." We look at how columnSpanRange is used to determine the data location in just a moment in the "WorksheetBase Properties" section.

Formulas and Globalization

One challenge in creating a global Visual Studio Tools for Office solution is assigning range formulas. Unfortunately, setting a range's Formula property using a formula expressed in English only works correctly if the worksheet is loaded in an English locale. Loading the worksheet in a non-English locale results in an error. Formulas are locale-specific in terms of both formatting and function names. For example, the formula to sum the numbers 1.5 and 3.0 in an English locale is "=SUM(1.5, 3.0)" but in a French locale the formula is expressed as "=SOMME(1,5;3,0)".

The problem is not that Excel 2003 cannot understand English-formatted formulas when using a non-English locale, but rather that formulas are parsed according to the rules of the current locale. If we explicitly tell Excel 2003 to use the rules for English when we set the formula, the formula works correctly in all locales. This is exactly what the SetFormula method does.

Public Shared Sub SetFormula(ByVal range As Excel.Range, ByVal formula As String)
  ' en-US culture info used to set locale-independent formulas 
  Dim enUSCulture As New System.Globalization.CultureInfo(1033)

  ' Type descripter for Excel.Range—allows us to set the Formula property using reflection
  Dim rangeType As Type = GetType(Excel.Range)

  ' Set the range's Formula property indicating that 
  ' the formula being assigned is formatted as en-US culture
  ' Runtime automatically translates the formula to the actual culture
  rangeType.InvokeMember("Formula", _    ' Invoke the Formula member
      System.Reflection.BindingFlags.Public Or _ ' The member should be a Public, 
      System.Reflection.BindingFlags.Instance Or _'  Instance (not Shared),
      System.Reflection.BindingFlags.SetProperty, _'  Property 
      Nothing, range, _                  ' Set the Formula property on the passed range parameter
      New Object() {formula}, _          ' Set it to the value in the passed formula string
      enUSCulture)                       ' The formula is formatted as the English (United States) culture
End Sub

The SetFormula method uses .NET Framework–based reflection to set the Formula property explicitly using the English (United States) culture. To do this, it creates an English (United States) instance of the CultureInfo class and stores a reference to the Type class, which describes the Excel.Range class. The call to rangeType.InvokeMember then uses reflection to explicitly set the Formula property of the passed range to the value of the passed formula. The key is that the call to InvokeMember includes the enUSCulture field, indicating that the value in the formula parameter should be parsed according the rules of English (United States) no matter what the current locale is. We see SetFormula in use when we populate the Account Summary worksheet.

WorksheetBase Properties

WorksheetBase includes eight properties. As is commonly the case, four of these properties are simply accessors for a class field. For example, the RowCount is an integer serving as the accessor for the myRowCount field.

Protected myRowCount As Integer
Public Property RowCount() As Integer
  Get
    Return myRowCount
  End Get
  Set(ByVal value As Integer)
    myRowCount = value
  End Set
End Property

Table 7 describes the four properties that are simply accessors for member fields.

Table 7. WorksheetBase properties implemented as simple field accessors

Property	Accessed Field	Type	Description
RowCount	myRowCount	Integer	The number of data rows currently contained on the worksheet The value is specific to each worksheet
IsDisplaying	myIsDisplaying	Boolean	True indicates that the worksheet is in the process of updating the display Used to avoid recursively handling worksheet-changed events when populating the display
ActiveRange (ReadOnly)	myActiveRange	Excel.Range	The currently selected range See the Worksheet_SelectionChange method later in this section for more information The ActiveRange value is specific to each worksheet
ExcelWorksheet (ReadOnly)	myWorksheet	Excel.Worksheet	Reference to the physical Excel 2003 worksheet managed by the derived class

WorksheetBase also includes four read-only integer properties that return the first and last row and column of the range containing the worksheet data. These properties are FirstColumn, LastColumn, FirstRow, and LastRow. Each of these properties uses the columnSpanRange field created by the SetColumnSpanRange method. Remember that columnSpanRange is a range spanning the entire first row of data for the worksheet. This means the first column and row of the data can be found by simply accessing the Column and Row properties of columnSpanRange.

Public ReadOnly Property FirstColumn() As Integer
  Get
    Return columnSpanRange.Column
  End Get
End Property

Public ReadOnly Property FirstRow() As Integer
  Get
    Return columnSpanRange.Row
  End Get
End Property

Determining the last column and row takes a little more effort, but not much. The Count property on columnSpanRange returns the number of columns in the range, so we can add that to FirstColumn to get the LastColumn property.

Public ReadOnly Property LastColumn() As Integer
  Get
    Return FirstColumn + columnSpanRange.Count - 1
  End Get
End Property adsfafd

In the case of LastRow, we cannot get the number of rows from columnSpanRange because it is always a one-row range. We are still okay though, because the WorksheetBase class is already keeping track of the number of rows using the RowCount property. Therefore, adding the RowCount to the FirstRow gives us the LastRow.

Public ReadOnly Property LastRow() As Integer
  Get
    Return FirstRow + RowCount - 1
  End Get
End Property

Worksheet Activation and Selection

The next WorksheetBase foundation member we look at is the ActivateSheet method. ActivateSheet handles the details of making a specific worksheet the active worksheet, including insuring that the worksheet is visible and that grid lines are not displayed. Hiding the grid lines is for aesthetics only.

Public Sub ActivateSheet()
  ExcelWorksheet.Visible = Excel.XlSheetVisibility.xlSheetVisible
  ExcelWorksheet.Activate()
  Globals.ExcelApplication.ActiveWindow.DisplayGridlines = False
End Sub

Finally, we have the Worksheet_SelectionChange method that, of course, handles the worksheet's SelectionChange event. If you recall from the design discussion earlier, there are a number of situations where the stock allocation system must be selection-aware. For example, when the user clicks on the "Account Detail" toolbar button, the application must know the row of the current selection in order to determine with which account to populate the Account Detail worksheet. The Worksheet_SelectionChange method also calls the DoWorksheetSelectionChangeWork overridable (virtual) method to give derived classes an opportunity to do additional work. The WorksheetBase implementation of DoWorksheetSelectionChangeWork does not do anything.

Protected Sub Worksheet_SelectionChange(ByVal target As Excel.Range) Handles myWorksheet.SelectionChange
  myActiveRange = target

DoWorksheetSelectionChangeWork(target)

End Sub

The Worksheet_SelectionChange method and ActiveRange property work together. Worksheet_SelectionChange stores the selected range in the private field, myActiveRange, on each change; ActiveRange makes this range publicly visible.

Creating the Worksheets

The WorksheetBase class provides a foundation for common worksheet operations, so we are now ready to start creating the worksheets.

When it comes to creating the worksheets, we have two jobs to perform because the worksheets actually have two somewhat distinct identities. For each worksheet in our application, there is the physical worksheet the user sees and interacts with and there is our Visual Basic .NET class containing the code to manage the physical worksheet. You can create and format the physical worksheet either by using a .NET Framework–based class to manipulate the Excel object model or by opening Excel 2003 and doing things interactively.

In general, I find that using Excel 2003 to do the worksheet creation and formatting tends to go much faster than writing .NET Framework–based code to do the same thing. Any feature of the worksheets that is static in nature (such as headings, cell formats, etc.) can be performed interactively with Excel 2003.

Creating the Physical Worksheets

In our stock allocation system, we have four distinct worksheets: Account Summary, Account Detail, Stock Summary, and Stock Allocation. Although not all worksheets may be initially visible, at run time we never have more then these four, so this is a perfect opportunity to use Excel 2003 as part of our application development. Using Excel 2003, we set up our project workbook, StockAllocation.xls, to have four appropriately named worksheets.

Note Visual Studio 2003 with Visual Studio Tools for Office does not support editing the Excel 2003 workbook from within the Visual Studio 2003 designer. This feature has been added to Microsoft Visual Studio Tools for Office 2005 Beta 1 with Microsoft Visual Studio 2005 Enterprise Edition Beta 1.

Setting up the worksheets in the StockAllocation.xls workbook is easy.

Open the StockAllocation.xls workbook in Excel 2003.

The newly created workbook looks similar to Figure 13.
Rename "Sheet1" to "Allocation Summary" by right-clicking Sheet1, choosing "Rename," and then typing "Allocation Summary".
Repeat the previous step to rename "Sheet2" to be "Account Detail" and "Sheet 3" to be "Stock Summary".
Now insert a fourth worksheet by clicking Worksheet on the Insert menu.
Rename the new worksheet to "Stock Allocation".

The workbook now looks similar to Figure 14.
Save and close the workbook.

Figure 13. StockAllocation.xls containing default worksheets (click picture to see larger image)

Figure 14. StockAllocation.xls updated to contain the four required worksheets (click picture to see larger image)

Creating the Worksheet Classes

Now that the StockAllocation.xls workbook has the physical worksheets, we are ready to create the Visual Basic .NET classes that manage them. Creating the basic worksheet classes is simple because most of the standard housekeeping is handled by the WorksheetBase class from which each of the worksheet classes inherit.

The first worksheet class is AccountSummary, which of course inherits from WorksheetBase.

Public Class AccountSummary
  Inherits WorksheetBase

To access the reference to the Excel worksheet object, we need the worksheet name, "Account Summary." As part of good programming style, we want to minimize the literals used within the code, so we store the name of the worksheet as a constant, SHEET_NAME, within the class.

  Public Const SHEET_NAME As String = "Account Summary"

Finally, there is the class constructor. Remember, we want our worksheet class to store a reference to the Excel worksheet object. Our base class constructor knows how to access and store the Excel worksheet object if we give it the worksheet name; we just need to call the base class constructor and pass the SHEET_NAME constant.

Public Sub New()
  MyBase.New(SHEET_NAME)
End Sub

Now, when the AccountSummary class is created, the WorksheetBase class locates the appropriate worksheet in the Excel object model and stores it in the myWorksheet member, making the worksheet available to the AccountSummary class through the inherited property, ExcelWorksheet.

The complete AccountSummary class looks like the following.

Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel

Public Class AccountSummary
  Inherits WorksheetBase

  Public Const SHEET_NAME As String = "Account Summary"

  Public Sub New()
    MyBase.New(SHEET_NAME)
  End Sub

End Class

We now have a simple AccountSummary class. The project also contains classes for the other worksheets, named AccountDetail, StockSummary, and StockAllocation, with SHEET_NAME values of "Account Detail," "Stock Summary," and "Stock Allocation" respectively.

Getting It All Connected

The last step is to instantiate our worksheet classes at application startup. This is done by the OfficeCodeBehind.ThisWorkbook_Open method, just after the call to Globals.InitApplicationAndWorkbook.

Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open

  Globals.InitApplicationAndWorkbook(ThisApplication, ThisWorkbook)

  Dim accountSummaryInstance As New AccountSummary
  Dim accountDetailInstance As New AccountDetail
  Dim stockSummaryInstance As New StockSummary
  Dim stockAllocationInstance As New StockAllocation

End Sub

Making the Worksheets Available

Like the Excel.Application and Excel.Workbook references, we need access to our worksheet class references throughout the application. Like the other references, we add the worksheet references to our Globals class.

For each worksheet class we need a Private, shared field to store the reference and a Friend, read-only shared property to expose the reference. Here are the members for the Account Summary worksheet.

Public Class Globals

  Private Shared accountSummaryInstance As AccountSummary

  Friend Shared ReadOnly Property AccountSummary() As AccountSummary
    Get
      Return accountSummaryInstance
    End Get
  End Property

End Class

We also need an initialization method to set the worksheet references.

Friend Shared Sub InitWorksheets(ByVal accountSummarySheet As AccountSummary, _
      ByVal accountDetailSheet As AccountDetail, _
      ByVal stockSummarySheet As StockSummary, _
      ByVal stockAllocationSheet As StockAllocation)

  accountSummaryInstance = accountSummarySheet
  accountDetailInstance = accountDetailSheet
  stockSummaryInstance = stockSummarySheet
  stockAllocationInstance = stockAllocationSheet

End Sub

We call the InitWorksheets method in OfficeCodeBehind.ThisWorkbook.Open right after we create the four worksheet classes. While we are adding code, we should go ahead and add exception handling.

Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
  Try

    Globals.InitApplicationAndWorkbook(ThisApplication, ThisWorkbook)

    ' Create Worksheets
    Dim accountSummaryInstance As New AccountSummary
    Dim accountDetailInstance As New AccountDetail
    Dim stockSummaryInstance As New StockSummary
    Dim stockAllocationInstance As New StockAllocation

    ' Make globally available
    Globals.InitWorksheets(accountSummaryInstance, accountDetailInstance, _
         stockSummaryInstance, stockAllocationInstance)

  Catch ex As Exception
    MsgBox("Error: " & ex.Message)
  End Try

End Sub

As an interactive application, the stock allocation system needs to provide some form of intelligent navigation. Having the user directly select a worksheet is fine as long as the worksheet already contains the desired data. With intelligent navigation, the stock allocation system can populate and display a worksheet based on the user's selection in another worksheet. For example, the stock allocation system displays a specific account's data in the Account Detail worksheet based on the user's current selection in the Account Summary worksheet, or selects the appropriate stock in the Stock Allocation worksheet based on the user's current selection in the Stock Summary worksheet.

In the stock allocation system, intelligent navigation is implemented using toolbar buttons at the top of the workbook. Toolbar functionality is exposed through the Office.CommandBar control. Like the worksheets, the command bar is encapsulated in a Visual Basic .NET class to simplify working with it.

The class is named ApplicationCommandBar. It contains a constant, COMMANDBAR_NAME, (to name the command bar) and a private field, myCommandBar (to hold the reference to the real command bar).

Public COMMANDBAR_NAME As String = "Stock Allocation Management"
    Private myCommandBar As Office.CommandBar

Now we need a constructor so that the real command bar is automatically created when our ApplicationCommandBar class is created. Command bars are exposed through the CommandBars collection on the Globals.ExcelApplication object, so we use the Globals.ExcelApplication.CommandBars.Add method, specifying the command bar's name and that it should be docked at the top of the workbook. Being extra careful that we have no surprises, we explicitly set the command bar's Visible property to True.

myCommandBar = Globals.ExcelApplication.CommandBars.Add(COMMANDBAR_NAME, _
      Office.MsoBarPosition.msoBarTop)
myCommandBar.Visible = True

Next, we need to put some buttons on the toolbar. We start with three buttons: one to update the stock price, one to display the stock allocation, and one to display the Account Detail. To do this, we need to declare three Office.CommandBarButton objects. These should be declared WithEvents so that they have the ability to handle their click events.

Private WithEvents updateStockPricesButtonInstance As Office.CommandBarButton
Private WithEvents stockAllocationButtonInstance As Office.CommandBarButton
Private WithEvents accountDetailButtonInstance As Office.CommandBarButton

We also need public, read-only properties for each button so that they can be accessed outside of the ApplicationCommandBar class. The properties are named UpdateStockPricesButton, StockAllocationButton, and AccountDetailButton. Here is the UpdateStockPricesButton property.

Public ReadOnly Property UpdateStockPricesButton() As Office.CommandBarButton
  Get
    Return updateStockPricesButtonInstance
  End Get
End Property

It takes a few lines of code to create a button and set all of the necessary properties. Since we are creating several buttons, we encapsulate the button creation in a method, AddButton.

The AddButton method accepts one String parameter, caption, and three Boolean parameters: beginGroup, visible, and enabled. beginGroup indicates whether we would like a separator between the button we are creating and the previous button on the toolbar. As the names imply, visible and enabled indicate whether the button should be initially visible and enabled.

Public Function AddButton(ByVal caption As String, ByVal beginGroup As Boolean, _
        ByVal visible As Boolean, ByVal enabled As Boolean) As Office.CommandBarButton

Within the method, we create the button using the Add method of the myCommandBar.Controls collection, specifying the Office.MsoControlType.msoControlButton constant.

Dim button As Office.CommandBarButton = _
        myCommandBar.Controls.Add(Office.MsoControlType.msoControlButton)

Finally, we set the button properties corresponding to the three function parameters and then return the button instance, as shown here in the complete AddButton method.

Public Function AddButton(ByVal caption As String, ByVal beginGroup As Boolean, _
         ByVal visible As Boolean, ByVal enabled As Boolean) As Office.CommandBarButton
  Dim button As Office.CommandBarButton = _
         myCommandBar.Controls.Add(Office.MsoControlType.msoControlButton)
  button.Style = Office.MsoButtonStyle.msoButtonCaption
  button.Caption = caption
  button.BeginGroup = beginGroup
  button.Visible = visible
  button.Enabled = enabled

  Return button
End Function

The three buttons are created within the ApplicationCommandBar constructor just after creating the command bar itself. The complete constructor now looks like this.

Public Sub New()
  myCommandBar = Globals.ExcelApplication.CommandBars.Add(COMMANDBAR_NAME, _
        Office.MsoBarPosition.msoBarTop)

  updateStockPricesButtonInstance = AddButton("Update Stock Prices", False, True, True)
  stockAllocationButtonInstance = AddButton("New Stock Allocation", True, True, True)
  accountDetailButtonInstance = AddButton("Account Detail", True, False, False)

  myCommandBar.Visible = True

End Sub

In addition to creating the command bar, we also need to be sure that our class supports cleaning up the command bar so that we can remove it at the end of our application. In .NET Framework, classes requiring cleanup implement an interface named IDisposable. The IDisposable interface has just one method, Dispose.

Note For more information on .NET Framework–based application cleanup guidelines and the IDisposable interface, see Implementing a Dispose Method.

To support cleanup, we have our ApplicationCommandBar class implement the IDisposable interface, adding the Dispose method to the class. Inside of the Dispose method, we delete the command bar object by calling myCommandBar.Delete.

Public Class ApplicationCommandBar
  Implements IDisposable

  Public Sub Dispose() Implements System.IDisposable.Dispose
    if Not myCommandBar Is Nothing Then
      myCommandBar.Delete()
    End If
  End Sub

End Class

Making the Command Bar Globally Available

Like the worksheets, the command bar is used throughout the application, so we add it to our Globals class as well. In the Globals class, we create a private shared field, commandBarInstance, and a public shared property, CommandBar. We also include an InitCommandBar method.

Public Class Globals
Private Shared commandBarInstance As ApplicationCommandBar
Friend Shared ReadOnly Property CommandBar() As ApplicationCommandBar
        Get
          Return commandBarInstance
        End Get
End Property

Friend Shared Sub InitCommandBar(ByVal commandBar As ApplicationCommandBar)
        commandBarInstance = commandBar
End Sub
End Class

Connecting the Command Bar

We can now create the command bar and store it in the Globals class as part of OfficeCodeBehind.ThisWorkbook.Open, just before creating the worksheets.

Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
  Try
    Globals.InitApplicationAndWorkbook(ThisApplication, ThisWorkbook)

    ' Create and store command bar
    Dim commandBarInstance As New ApplicationCommandBar
    Globals.InitCommandBar(commandBarInstance)

    ' Create and store worksheets
    ' ...

  Catch ex As Exception
    MsgBox("Error: " & ex.Message)
  End Try

End Sub

We need to be sure to clean up the command bar when the application is being closed, so we call the command bar's Dispose method in the ThisWorkbook_BeforeClose method.

Private Sub ThisWorkbook_BeforeClose(ByRef Cancel As Boolean) Handles ThisWorkbook.BeforeClose
  Globals.CommandBar.Dispose()
  Cancel = False
End Sub

Making the Command Bar Context-Aware

The "Account Detail" toolbar button is used to display the details of a specific account based on the user's selection on the Account Summary worksheet. Because the behavior is specific to the Account Summary worksheet, the button should only be visible when the Account Summary worksheet is active.

To make the "Account Detail" toolbar button context-aware, we need to display it when the Account Summary worksheet is activated and hide it when the worksheet is deactivated. We do this by adding methods to the AccountSummary class to handle the Activate and Deactivate events on the contained worksheet reference, myWorksheet. These methods set the state of the "Account Detail" button appropriately.

Public Sub myWorksheet_activate() Handles myWorksheet.Activate
  Globals.CommandBar.AccountDetailButton.Visible = True
End Sub

Public Sub myWorksheet_Deactivate() Handles myWorksheet.Deactivate
  Globals.CommandBar.AccountDetailButton.Visible = False
End Sub

A Note on Defensive Programming

We completed the basic functionality of the command bar and, as long as our application always shuts down cleanly, we are fine. However, if the program were to terminate abnormally, there is a possibility that the command bar may not be destroyed. As a result, when the program is next executed, the call to Globals.ExcelApplication.CommandBars.Add to create the command bar throws an ArgumentException telling us that the parameter is incorrect, as shown in Figure 15. ArgumentExceptions can be thrown for many different reasons, but, in this case, it is an indication that the toolbar we are trying to create, "Stock Allocation Management," already exists.

Figure 15. Command bar ArgumentException message

The best way I have found to deal with this scenario is to attempt to locate the command bar prior to creating it. If it exists, delete it. There is no built-in method to search for a command bar, so we write our own method to loop through the list of command bars, looking for the command bar with the appropriate name. To do this, we add the FindCommandBarInstanceByName method to the ApplicationCommandBar class.

Private Function FindCommandBarInstanceByName(ByVal nameToFind As String) As Office.CommandBar
  Dim returnCmdBar As Office.CommandBar = Nothing
  For Each cmdBar As Office.CommandBar In Globals.ExcelApplication.CommandBars
    If cmdBar.Name = nameToFind Then
      returnCmdBar = cmdBar
      Exit For
    End If
  Next
  Return returnCmdBar
End Function

If a command bar with the specified name already exists, FindCommandBarInstanceByName returns a reference to it; otherwise, the method returns Nothing. We can now add code to our ApplicationCommandBar constructor to search for an existing instance of our command bar and, if it exists, delete it. Here is the new ApplicationCommandBar constructor.

Public Sub New()
  ' Delete command bar if it already exists
  myCommandBar = FindCommandBarInstanceByName(COMMANDBAR_NAME)
  If Not myCommandBar Is Nothing Then
    myCommandBar.Delete()
  End If

  ' Create the physical command bar
  myCommandBar = Globals.ExcelApplication.CommandBars.Add(COMMANDBAR_NAME, Office.MsoBarPosition.msoBarTop)

  ' Add Buttons
  ' ...
  myCommandBar.Visible = True

End Sub

Now our command bar handling is stable and the command bar is always created correctly even if the prior execution did not clean up the command bar correctly.

Adding Data Management

As mentioned earlier, we are using a .NET Framework DataSet for data storage and management. The DataSet is read from and written to the local disk so that the application provides the user with a consistent experience whether in the office or out on the road.

Rather than use the DataSet class directly, the stock allocation system is using a typed DataSet, StockAllocationDataSet, which exposes specific properties for the contained tables and columns, rather than relying on the DataSet class's Tables property and GetXX methods. We talk more about the typed DataSet shortly in the "Data Model" section.

To encapsulate management of the DataSet, we have the DataSetUtility class.

Imports System.IO

Public Class DataSetUtility
  Private Const FILE_NAME As String = "StockAllocationData.xml"

  Private Sub New()
  End Sub

End Class

DataSetUtility exposes a method to create the DataSet, populating it from the XML file. The implementation is simple because the DataSet provides a ReadXml method.

Friend Shared Function CreateAndPopulateDataSet() As StockAllocationDataSet
  Dim dataSetInstance As New StockAllocationDataSet
  Dim dataSetFileName As String = GetFileName()

  dataSetInstance.ReadXml(dataSetFileName)

  Return dataSetInstance
End Function

The GetFileName method is also part of the DataSetUtility class and handles the details of determining the fully qualified path of the XML data file.

Like the worksheet and command bar classes, we need to have access to the DataSet throughout the application—so we add the required field, property, and initialization method to our Globals class.

Class Globals
  Private Shared dataSetInstance As StockAllocationDataSet

  Friend Shared ReadOnly Property DataSet() As StockAllocationDataSet
    Get
      Return dataSetInstance
    End Get
  End Property

  Friend Shared Sub InitDataSet(ByVal dataSet As StockAllocationDataSet)
    dataSetInstance = dataSet
  End Sub

End Class

We also need to create and store the DataSet from the ThisWorkbook_Open method, just as we did with the worksheets and command bar. We can do this just after the command bar is created and stored.

Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
  Try
    Globals.InitApplicationAndWorkbook(ThisApplication, ThisWorkbook)

    Dim commandBarInstance As New ApplicationCommandBar
    Globals.InitCommandBar(commandBarInstance)

    ' Create and store the DataSet
    Dim dataSetInstance As StockAllocationDataSet = DataSetUtility.CreateAndPopulateDataSet()
    Globals.InitDataSet(dataSetInstance)

    ' Create Worksheets
    ' ...

    Catch ex As Exception
      MsgBox("Error: " & ex.Message)
    End Try

End Sub

Now we need the code to save and clean up the DataSet. First, we add a SaveDataSet method to DataSetUtilities, which uses the WriteXml function to write the new DataState to the XML file.

Friend Shared Sub SaveDataSet()
  Dim dataSetFileName As String = GetFileName()
  If Not Globals.DataSet Is Nothing Then
    Globals.DataSet.WriteXml(dataSetFileName, XmlWriteMode.WriteSchema)
  End If
End Sub

We, of course, also need to initiate the call to save the DataSet. To be consistent with the Excel 2003 user experience, we should save the DataSet just as Excel 2003 would normally save the workbook: when the user explicitly chooses the Save menu option or if the user attempts to exit Excel 2003 without explicitly saving. In our case though, we want to save the DataSet, not the workbook. We prevent the actual workbook from being saved by setting the Cancel property to True.

Private Sub ThisWorkbook_BeforeSave(ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) _
        Handles ThisWorkbook.BeforeSave
 If Not Globals.DataSet Is Nothing Then
    DataSetUtility.SaveDataSet()
 End If
 Cancel = True
End Sub

Now any time the user initiates a workbook save, whether explicitly by the user or implicitly by Excel 2003, our DataSet has its state saved to the StockAllocationData.xml file. The worksheet contents are never saved, which is also what we want, because we programmatically populate the worksheets using the DataSet contents each time the workbook is opened.

System Foundation Wrap Up

We have invested a lot of effort in building wrappers around the worksheets, command bar, and DataSet, but it's time well spent. Every application involves addressing both technical and business issues. Encapsulating the technical details as we have allows us now to focus on the business issues. Minimizing the interference of technical details allows us to focus on the core business issues and therefore enables us to better solve the business problem, and generally leads to a higher-quality and easier-to-maintain solution.

The issues we have addressed in this foundation are common to many Visual Studio Tools for Office Excel projects and can be easily applied to most applications of this type. I find that it is worthwhile to back up a version of the project at its current point and use it as the starting point for other Visual Studio Tools for Office Excel project solutions.

Developing the Stock Allocation System Business Functionality

Now that we have our application foundation in place, we are ready to begin building the business functionality into the system. Having invested in the foundation as we have, we can now focus on the required business features and minimize the time we spend on technical housekeeping.

The Data Model

The data provided to our application resides in three relational tables.

ClientMaster: The master list of client accounts and related information. See Table 8 for the table schema.
StockMaster: The master list of all stocks in the system along with their related information. This table is meant to represent all available stocks, not just those stocks actually owned by clients. In real life, this table would contain thousands of rows. See Table 9 for the table schema.
ClientHoldings: The list of stocks owned by each client, including quantity owned and original purchase information. See Table 10 for the table schema.

Table 8. Table schema for the ClientMaster table

Column	Type	Description
AccountNumber	String (9)	Client's account ID
ClientName	String (50)	Client's name (last, first)
RiskLevel	String (10)	Client's acceptable risk tolerance—Aggressive (high risk), Moderate, or Income (low risk)
CashAvailable	Decimal	Cash held in the account that is not currently invested

Table 9. Table schema for the StockMaster table

Column	Type	Description
StockSymbol	String (12)	Official short-hand identifier for the stock
StockName	String (50)	Company name
CurrentPrice	Decimal	The most recent trading price available for the stock

Table 10. Table schema for the ClientHoldings table

Column	Type	Description
AccountNumber	String (9)	Foreign key to client_master
StockSymbol	String (12)	Foreign key to stock_master
PurchaseDate	DateTime	Date position was purchased
PurchasePrice	Decimal	Price paid for the position when purchased
SharesQuantity	Integer	Number of stock shares held

These tables are exposed to the application through the typed DataSet class, StockAllocationDataSet, in Globals.DataSet. As a typed DataSet, StockAllocationDataSet is derived from the DataSet class and therefore has all of the same capabilities, but with the added bonus of being aware of our specific data model. As a typed DataSet, StockAllocationDataSet contains strongly typed class definitions for each of the three tables and exposes explicit properties for each. The tables in turn have explicit classes defined for the contained rows, with each column exposed as an appropriately typed property on the table.

StockAllocationDataSet was generated by passing the schema information from the StockAllocationData.xml file to the XSD.exe utility. The XSD.exe utility provides a number of class-generation and file-generation features, one of which is to generate a Typed DataSet class from an XML schema definition.

Note For more information about creating typed DataSets, see Creating XML Schemas and Datasets from Existing Data.

The DataSet is populated by the call to DataSetUtilities.CreateAndPopulateDataSet made from the ThisWorkbook_Open method, which reads the contents of the StockAllocationData.xml file. The DataSet maintains the relational structure of the tables so, even if you have never worked with the DataSet class, you are able to leverage your existing relational database knowledge.

Each of the worksheets retrieves its data from the DataSet, and any changes to the data are made within the DataSet. At the end of application execution, the current DataSet state is written back to the StockAllocationData.xml file using the DataSetUtilities.SaveDataSet method call made from the ThisWorkbook_BeforeSave method.

Formatting the Worksheets

Now that we understand our data model, we can lay out the worksheets. Remember, the worksheets act as our application's user interface. We want to be sure that the user interface is both aesthetically pleasing and intuitive. The rich color, font, and layout features of Excel 2003 enable us to create a professional-looking application with relatively little work. Similar to creating the physical worksheets, formatting them is something most easily handled using Excel 2003 interactively.

A Technical Aside

Before we actually begin formatting the worksheets, we need to deal with a technical aside. When we added the event handler for the workbook's BeforeSave event, we modified the behavior of Excel 2003 to save our DataSet but not the workbook contents as it normally would. Not saving the workbook contents includes not saving any format changes to the worksheets; as a result, any modifications we make to the worksheets interactively are lost. Although we do not want the workbook contents saved when we ultimately deploy the application, this is not a desirable behavior when we are formatting the worksheets.

After we go through the work of formatting the four worksheets, we want to be sure that formatting information is saved as part of the workbook. We can easily facilitate this by commenting out the code that is preventing the document from saving, which is the line setting the Cancel parameter to True in the ThisWorkbook_BeforeSave method.

Private Sub ThisWorkbook_BeforeSave(ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) _
        Handles ThisWorkbook.BeforeSave
  If Not Globals.DataSet Is Nothing Then
    DataSetUtility.SaveDataSet()
  End If
  ' Commenting out the next line now allows workbook contents to be saved
  ' Cancel = True
End Sub

We can add this line back in after we finish formatting the document.

With the ability to save the workbook contents restored, we can open the StockAllocation.xls file in Excel 2003 and start setting up the formats.

Note If you would rather not modify the code, there are a number of other options. For more information, see How to: Bypass the Assembly When Opening an Office Solution.

Formatting the Account Summary Worksheet

First, we format the Account Summary worksheet (see Figure 16).

Figure 16. Account Summary worksheet formatting (click picture to see larger image)

In addition to the fonts, colors, and borders, we apply the following format settings.

Table 11. Formatting to apply to the Account Summary worksheet

Cell	Format	Modifiers/Notes
E3, F3, G3	Number	Thousands separator, 0 decimal places
Whole Worksheet	No Gridlines	Use Excel 2003 Tools\Options menu—see Figure 17

Figure 17. Excel 2003 Options dialog, disabling worksheet grid lines (click picture to see larger image)

Formatting the Account Detail Worksheet

Figure 18 shows the formatted Account Detail worksheet.

Figure 18. Account Detail worksheet formatting (click picture to see larger image)

In addition to the fonts, colors, and borders, we apply the following format settings.

Table 12. Formatting to apply to the Account Detail worksheet

Cell	Format/Validation	Modifiers/Notes
D3	List Validation	Valid values are listed in cells M1:M3
E10	Date	3/14/2001—Culturally sensitive, based on operating system data order
F10	Number	Thousands separator, 2 decimal places
J4, J5, J6, D10, H10, I10, J10	Number	Thousands separator, 0 decimal places
Whole Worksheet	No Gridlines	Use Excel 2003 Tools\Options menu—see Figure 17

Formatting the Stock Summary Worksheet

Figure 19 shows the formatted Stock Summary worksheet.

Figure 19. Stock Summary worksheet formatting (click picture to see larger image)

In addition to the fonts, colors, and borders, we apply the following format settings.

Table 13. Formatting to apply to the Stock Summary worksheet

Cell	Format	Modifiers/Notes
D3, E3, F3	Number	Thousands separator, 0 decimal places
Whole Worksheet	No Gridlines	Use Excel 2003 Tools\Options menu—see Figure 17

Formatting the Stock Allocation Worksheet

Figure 20 shows the formatted Stock Allocation worksheet.

Figure 20. Stock Allocation worksheet formatting (click picture to see larger image)

In addition to the fonts, colors, and borders, we apply the following format settings.

Table 14. Formatting to apply to the Stock Allocation worksheet

Cell	Format/Validation	Modifiers/Notes
C4, C7, G13	Number	Thousands separator, 2 decimal places
H6, H7, D13, E13, F13, H13	Number	Thousands separator, 0 decimal places
C6	Percentage	0 decimal places
C8	List Validation	Valid values are listed in cells M5:M6
C9	List Validation	Valid values are listed in cells M1:M3
Whole Worksheet	No Gridlines	Use Excel 2003 Tools\Options menu—see Figure 17

Restoring the Previously Commented-Out Line of Code

Once we have saved the worksheet format changes, we can restore the line we commented out in the ThisWorkbook_BeforeSave method.

Private Sub ThisWorkbook_BeforeSave(ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) _
        Handles ThisWorkbook.BeforeSave
  If Not Globals.DataSet Is Nothing Then
    DataSetUtility.SaveDataSet()
  End If
  Cancel = True
End Sub

Saving the workbook again saves only the DataSet, not the workbook contents.

Populating the Account Summary Worksheet

We have data and we have formatted the worksheets, it is time to start populating the worksheets. Since Account Summary is the first worksheet the user views, that is where we start.

The Account Summary worksheet includes the account number, name, risk tolerance, current value, cash available, and profit/loss for each client account. Looking at the data model, we can see that with the exception of current value and profit/loss, all of the data can be found in the ClientMaster table. For the calculated columns, we need to use the ClientHoldings and StockMaster tables to calculate the values.

We start by defining constants for the worksheet positions.

Public Class AccountSummary
  Inherits WorksheetBase

  Public Const SHEET_NAME As String = "Account Summary"

  ' First row of data
  Public Const STARTING_ROW As Integer = 14

  ' Column positions
  Public Const ACCOUNT_NAME_COLUMN As String = "B"
  Public Const ACCOUNT_NUMBER_COLUMN As String = "C"
  Public Const RISK_COLUMN As String = "D"
  Public Const CURRENT_VALUE_COLUMN As String = "E"
  Public Const PROFIT_LOSS_COLUMN As String = "F"
  Public Const CASH_AVAILABLE_COLUMN As String = "G"
  Public Const TOTAL_VALUE_OF_BOOK_COLUMN As String = "C"
  Public Const NUMBER_OF_ACCOUNTS_COLUMN As String = "C"

Before populating the worksheet, we need to create named ranges for each data column. The named ranges serve as the reference point for adding data to the worksheet.

Once we create the named ranges, we can refer to them by either passing the string name of the range to the ExcelWorksheet.Range property or by storing the range reference returned by the WorksheetBase.CreateNamedRange method. Since each of the worksheet classes has a relatively small number of named ranges, and they are used frequently, we go ahead and store references to each as member fields in the AccountSummary class.

Private accountNameRange As Excel.Range
Private accountNumberRange As Excel.Range
Private riskRange As Excel.Range
Private currentValueRange As Excel.Range
Private profitLossRange As Excel.Range
Private cashAvailableRange As Excel.Range

We then add a method, CreateInitialNamedRanges, to create the named ranges for each column based on the corresponding constants.

Private Sub CreateInitialNamedRanges()
  accountNameRange = CreateNamedRange("AccountName", _
      ACCOUNT_NAME_COLUMN, STARTING_ROW)
  accountNumberRange = CreateNamedRange("AccountNumber", _
      ACCOUNT_NUMBER_COLUMN, STARTING_ROW)
  riskRange = CreateNamedRange("Risk", _
      RISK_COLUMN, STARTING_ROW)
  currentValueRange = CreateNamedRange("CurrentValue", _
      CURRENT_VALUE_COLUMN, STARTING_ROW)
  profitLossRange = CreateNamedRange("ProfitLoss", _
      PROFIT_LOSS_COLUMN, STARTING_ROW)
  cashAvailableRange = CreateNamedRange("CashAvailable", _
      CASH_AVAILABLE_COLUMN, STARTING_ROW)
End Sub

The named ranges are used throughout the AccountSummary class, so we create them in the class constructor. Once the ranges are created, they are passed to the WorksheetBase.SetColumnSpanRange method. Remember from our discussion back in the "Working with Ranges" subsection of the "Creating the Worksheet Foundation" section that passing the data display named ranges to the SetColumnSpanRange method allows WorksheetBase to determine the value of the properties FirstRow, LastRow, FirstColumn, and LastColumn.

Public Sub New()
  MyBase.New(SHEET_NAME)
  CreateInitialNamedRanges()

  SetColumnSpanRange(New Excel.Range() {accountNameRange, _
      accountNumberRange, riskRange, currentValueRange, _
      profitLossRange, cashAvailableRange})
End Sub

To handle worksheet population, we add a Display method to the AccountSummary class. The Display method starts by making the Account Summary worksheet the active worksheet and then it calls a helper method I have added to the WorhsheetBase class, ClearContents, which takes care of the details of clearing any previous data from the worksheet. We also need some local variables.

Public Sub Display()

  ActivateSheet()   ' Make us the active worksheet

  ' Clean up any previous data
  ClearContents()

  Dim rangeString As String             ' Temporary variable for building range names
  Dim formulaString As String           ' Temporary variable for constructing range formulas
  Dim insertRange As Excel.Range        ' Range in which to insert data
  Dim totalCurrentStockValue As Double  ' Sum of stock position current value
  Dim totalPurchaseStockValue As Double ' Sum of stock position cost at purchase

Before writing the data to the worksheet, we need to expand the size of the area we formatted. You recall that we only formatted one row in the worksheet, so we need to increase the area to the number of rows contained in our ClientMaster table.

We already know most of the insert range because the call to SetColumnSpanRange in the constructor is all that is needed for the properties FirstRow, FirstColumn, and LastColumn to be valid. What we still need to know is the LastRow property. As you may recall, the implementation of the LastRow property adds the RowCount property to the FirstRow property. Therefore, assigning the row count from the ClientMaster table to the RowCount property gives us everything we need.

To do the insert, we create a range based on the row and column properties, and then we call the Insert method of the range. Specifying the parameter Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove indicates that the new rows should have the same formatting and data validation as the row above the new range, thereby applying the cell formats we set interactively to the newly created rows.

RowCount = Globals.DataSet.ClientMaster.Rows.Count
If RowCount > 1 Then
 insertRange = CreateRange(FirstColumn, FirstRow + 1, LastColumn, LastRow)
 insertRange.Insert(Excel.XlDirection.xlDown, _
      Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove)
End If

Now that we know the row count, and therefore the location of the last row of data, we can create named ranges for the totals written at the bottom of the data area. We only need to create these ranges once, so we use a flag, firstDisplay, to avoid repeating the process.

If firstDisplay Then
  firstDisplay = False
  CreateTotalsNamedRanges()
End If

The CreateTotalsNamedRanges looks very much like the CreateInitialNamedRanges method except that the ranges rely on the LastRow property rather than the STARTING_ROW constant.

Private Sub CreateTotalsNamedRanges()
  totalValueOfBookRange = CreateNamedRange("TotalValueOfBook", _
      TOTAL_VALUE_OF_BOOK_COLUMN, LastRow + 1)
  numberOfAccountsRange = CreateNamedRange("NumberOfAccounts", _
      NUMBER_OF_ACCOUNTS_COLUMN, LastRow + 2)
End Sub

We are now ready to write the actual data. The Account Summary worksheet is a listing of the rows contained in the ClientMaster table, so we could use the Rows collection, but we would have no control over the row order. We want to display the rows in order by ClientName, so we use the Select method, which allows us to specify the selection order. In addition to the selection order, the Select method requires that we pass a select condition; however, we want all of the records, so we just pass an empty string.

We can now loop through the rows writing the ClientMaster column values to the worksheet. By incrementing a row counter on each loop iteration, we write the data values as an offset from their corresponding named range.

Dim rowIndex = 0
For Each clientMaster As StockAllocationDataSet.ClientMasterRow In _
    Globals.DataSet.ClientMaster.Select("", "ClientName")

  ' Using the rowIndex as an offset, write data to the sheet
  accountNameRange.Offset(rowIndex, 0).Value = clientMaster.ClientName
  accountNumberRange.Offset(rowIndex, 0).Value = clientMaster.AccountNumber
  riskRange.Offset(rowIndex, 0).Value = clientMaster.RiskLevel
  ' ...

  rowIndex += 1
Next

As we loop through the accounts, we need to calculate the current value and profit/loss for each account. These values require data from the ClientHoldings and StockMaster tables. The ClientHoldings table contains the stocks owned by the account along with the purchase price; the StockMaster table has the current price of each stock. Using the Select method of the tables within the DataSet, we can easily extract the desired values from each.

The Select method accepts a selection criteria formatted similar to the Where clause portion of a SQL Select and returns an array containing the rows matching the criteria. To locate the list of holdings for the current ClientMaster record, we can issue a select against the ClientHoldings table for all rows for the current account.

  Dim selectString As String = "AccountNumber = '" & _
      clientMaster.AccountNumber & "'"
  For Each clientHoldings As StockAllocationDataSet.ClientHoldingsRow In _
      Globals.DataSet.ClientHoldings.Select(selectString)

To locate the price of the stock in the ClientHoldings table, we need to issue a select against the StockMaster table, looking for the current stock symbol. Looking up data for a stock symbol is a simple query, similar to the ClientHoldings query, but it occurs a number of times throughout the program, so I have encapsulated the code in the DataSetUtility.GetStockMasterRowForSymbol method.

Now that we know how to find all of the data, we can calculate the current value and profit/loss. To find the current value of the account, we loop through all of the account holdings, summing the product of the number of shares held and the current price; we then add in the cash available. In other words, the current value of the account is the total value of the stocks plus cash. To find profit/loss, we loop through the holdings, summing the product of the number of shares and the purchase cost of each stock; we then subtract that from the sum of the current value of each stock.

Bringing the data population steps together, here is the code that loops through the accounts, performs the necessary calculations, and writes to the worksheet.

Dim rowIndex = 0
For Each clientMaster As StockAllocationDataSet.ClientMasterRow In _
    Globals.DataSet.ClientMaster.Select("", "ClientName")
  totalCurrentStockValue = 0.0
  totalPurchaseStockValue = 0.0

  ' Get list of holdings for this client, calculating the current value and profit/loss
  Dim selectString As String = "AccountNumber = '" & clientMaster.AccountNumber & "'"
  For Each clientHoldings As StockAllocationDataSet.ClientHoldingsRow In _
      Globals.DataSet.ClientHoldings.Select(selectString)

    Dim stockMaster As StockAllocationDataSet.StockMasterRow = _
        DataSetUtility.GetStockMasterRowForSymbol(clientHoldings.StockSymbol)

    totalCurrentStockValue += clientHoldings.SharesQuantity * stockMaster.CurrentPrice
    totalPurchaseStockValue += clientHoldings.SharesQuantity * clientHoldings.PurchasePrice
  Next

  ' Using the rowIndex as an offset, write data to the worksheet
  accountNameRange.Offset(rowIndex, 0).Value = clientMaster.ClientName
  accountNumberRange.Offset(rowIndex, 0).Value = clientMaster.AccountNumber
  riskRange.Offset(rowIndex, 0).Value = clientMaster.RiskLevel
  currentValueRange.Offset(rowIndex, 0).Value = totalCurrentStockValue + clientMaster.CashAvailable
  profitLossRange.Offset(rowIndex, 0).Value = totalCurrentStockValue - totalPurchaseStockValue
  cashAvailableRange.Offset(rowIndex, 0).Value = clientMaster.CashAvailable

  rowIndex += 1

Next

With the accounts displayed, we now need to show the total value of all accounts, and the number of accounts. To show the total value of the accounts, we use the Excel 2003 Sum function to total the Current Value column. The currentValueRange.Column property returns the column as an integer, so we set the formula using the R1C1 format. We then use the WorksheetBase.SetFormula method to assign the formula to the cell referenced by totalValueOfBookRange.

formulaString = String.Format("=SUM(R{0}C{1}:R{2}C{1})", _
    FirstRow, currentValueRange.Column, LastRow)
SetFormula(totalValueOfBookRange, formulaString)

Note The format used to set the formula does not affect the appearance of the formula when viewed by the user. The formula is displayed to the user in the currently selected display style, which is normally the A1 format in the United States.

For the number of accounts, we could just write the RowCount, but to make it more interesting, we use the Excel 2003 Count function. The Count function would be particularly useful if the application allowed the user to add more rows interactively. That behavior does not make sense in the case of our application, but it is useful in many cases.

formulaString = String.Format("=COUNT(R{0}C{1}:R{2}C{1})", _
    FirstRow, currentValueRange.Column, LastRow)
SetFormula(numberOfAccountsRange, formulaString)

To close the Display function, we call a helper function I added to WorksheetBase, FormatDataBorders, which draws the appropriate borders around the data cells. We then position the cell selection at the top of the worksheet.

  FormatDataBorders(True)
  ExcelWorksheet.Range("A1").Select()
End Sub

We now have all the code to display the Account Summary screen data. We just need to add the code to ThisWorkbook_Open to call it. We can do this just after the call to Globals.InitWorksheets. We also add a call to the AccountSummary.ActivateWorksheet method to be sure that the Account Summary worksheet is the first displayed.

Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
  Try
    ' Other code elided for clarity
    Globals.InitWorksheets(accountSummaryInstance, accountDetailInstance, _
           stockSummaryInstance, stockAllocationInstance)

    ' Populate Account Summary
    accountSummaryInstance.Display()

    ' Make Account Summary the active worksheet
    accountSummaryInstance.ActivateSheet()
  Catch ex As Exception
    MsgBox("Error: " & ex.Message)
  End Try
End Sub

We now have a populated Account Summary worksheet.

Populating the Stock Summary Worksheet

The Stock Summary worksheet provides a summary of all stocks in the money manager's book. Managing the Stock Summary layout is the same as the Account Summary; however, assembling the data is a little different because we need to calculate aggregate values for the number of shares and the number of accounts for each stock.

An easy way to calculate and write the aggregates is to use a traditional control-break model. Using a control-break model, we select all of the ClientHoldings records ordered by stock symbol. We loop through them, summing the shares and number of records; each time we encounter a new symbol, we write the totals.

We use the Select method to retrieve the ClientHoldings records, and specify that the records be ordered by stock symbol.

For Each clientHolding As StockAllocationDataSet.ClientHoldingsRow In _
        Globals.DataSet. ClientHoldings.Select("", "StockSymbol") 

  ' Process records ...

Next

Within the loop, we can accumulate the number of shares and increment the number of accounts for each stock. Each time the stock changes, we write the totals for the previous stock using the WriteDisplayLine helper function I added to the StockSummary class, and we zero the counters.

' If new symbol, write the previous totals to the worksheet
' prevSymbol is initialized to an empty string
If prevSymbol <> clientHolding.StockSymbol Then
  ' Don't write the first time, no totals yet
  If (prevSymbol <> String.Empty) Then

    WriteDisplayLine(RowCount, prevSymbol, accountCount, shareTotal)

    RowCount += 1

    ' Handle row inserting ...

    shareTotal = 0
    accountCount = 0
  End If
  prevSymbol = clientHolding.StockSymbol
End If
' total stock information
shareTotal += clientHolding.SharesQuantity
accountCount += 1

How we handle inserting new rows is another key issue that is different from the Account Summary worksheet. In the Account Summary worksheet, it was easy to know the required number of display rows because it was equivalent to the number of records in the ClientMaster table. This enabled us to insert all of the new rows as a single range before starting the loop, which is very efficient.

In the case of the Stock Summary worksheet, there is no easy way to know the number of display rows in advance. We can't know how many unique stock holdings are in the ClientHoldings table without looping through the whole table, and we can't use the number of rows in the StockMaster table because it contains the available universe of all stocks, and therefore likely has thousands more stocks than are actually held in the book.

Because of this, it makes sense to insert each new row into the worksheet as needed. We insert each new row just after the previous row is written. We do the insert after writing a row, rather than before, because the worksheet starts with one row: the row we created interactively. Adding the new row after each row is written in the loop also addresses the fact that we cannot write the totals for the final stock until the loop completes.

Here is the whole Stock Summary data population loop, including the row inserts and final write.

prevSymbol = String.Empty
RowCount = 0
' Get all client_holding rows in order by symbol
' Loop through totalling the positions for each stock
For Each clientHolding As StockAllocationDataSet.ClientHoldingsRow In _
    Globals.DataSet.ClientHoldings.Select("", "StockSymbol")
  ' If new symbol, write the previous totals to the worksheet
  ' prevSymbol is initialized to an empth string
  If prevSymbol <> clientHolding.StockSymbol Then
    ' Don't write the first time, no totals yet
    If (prevSymbol <> String.Empty) Then

      WriteDisplayLine(RowCount, prevSymbol, accountCount, shareTotal)

      RowCount += 1

      ' Insert the next row using the format info from the current row
      insertRange = CreateRange(FirstColumn, FirstRow + RowCount, _
          LastColumn, FirstRow + RowCount)
      insertRange.Insert(Excel.XlDirection.xlDown, _
          Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove)

      shareTotal = 0
      accountCount = 0
    End If
    prevSymbol = clientHolding.StockSymbol
  End If
  ' total stock information
  shareTotal += clientHolding.SharesQuantity
  accountCount += 1
Next
' Write the last row
WriteDisplayLine(RowCount, prevSymbol, accountCount, shareTotal)
 RowCount += 1

Just like the Account Summary worksheet, we add the call to display the Stock Summary worksheet in the ThisWorkbook_Open method. We can add it just after the call to AccountSummary.Display.

Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
  Try
    ' Other code omitted for clarity
    Globals.InitWorksheets(accountSummaryInstance, accountDetailInstance, _
           stockSummaryInstance, stockAllocationInstance)

    ' Populate the Summary worksheets
    accountSummaryInstance.Display()
    stockSummaryInstance.Display()

    ' Show Account Summary first
    accountSummaryInstance.ActivateSheet()
  Catch ex As Exception
    MsgBox("Error: " & ex.Message)
  End Try

End Sub

Selecting and Displaying the Account Detail Worksheet

Unlike the summary worksheets, the Account Detail worksheet displays on demand. To display an account, the user selects any cell in the Account Summary worksheet containing account data and clicks the "Account Detail" toolbar button. The Click event procedure extracts the selected account number from the Account Summary worksheet and initiates display of the Account Detail worksheet. Managing this process requires the involvement of the AccountSummary, ApplicationCommandBar, and AccountDetail classes.

The Role of the AccountSummary Class

The first task for the AccountSummary class is to provide feedback to the user as to when it is valid to display an Account Detail worksheet. The AccountSummary class already provides some feedback by hiding the "Account Detail" button when the Account Summary worksheet is not active. We do this by handling the worksheet's Activate and Deactivate events.

We now need to provide feedback as to whether the current Account Summary selection is on an account row. We do this by setting the enabled state of the buttons based on the worksheet's current selection. The way to know the current selection is to handle the worksheet's SelectionChange event.

Remember, our base class, WorksheetBase, already handles the SelectionChange event. As part of the event handling, WorksheetBase calls an overridable method, DoWorksheetSelectionChangeWork, to give derived classes a chance to provide additional processing. As a reminder, here is the relevant code from the WorksheetBase class.

Class WorksheetBase
  Protected Sub Worksheet_SelectionChange(ByVal target As Excel.Range) Handles myWorksheet.SelectionChange
    myActiveRange = target
    DoWorksheetSelectionChangeWork(target)
  End Sub

  ' Give derived class an opportunity to do additional work
  Protected Overridable Sub DoWorksheetSelectionChangeWork(ByVal target As Excel.Range)
    ' Default is to do nothing extra
  End Sub

End Class

By overriding the DoWorksheetSelectionChangeWork method, AccountSummary adds the necessary processing to set the enabled state of the "Account Detail" button each time the user selects a different cell. A valid selection is between the FirstRow and LastRow. To avoid confusion, we also enforce that the selection cannot span multiple rows. Assigning the result of this condition to the button's Enabled property provides the desired result.

Protected Overrides Sub DoWorksheetSelectionChangeWork(ByVal target As Excel.Range)
    Globals.CommandBar.AccountDetailButton.Enabled = _
         (target.Rows.Count = 1 And target.Row >= FirstRow And target.Row <= LastRow)
End Sub

The other responsibility of the AccountSummary class is to provide the value of the selected account number. We use this to determine which account to display on the Account Detail worksheet. To provide this functionality, we add a SelectedAccountNumber property to the AccountSummaray class. The property simply returns the value in the account number column of the same row as the active selection.

Public ReadOnly Property SelectedAccountNumber() As String
  Get
    Dim returnAccountNumber As String = String.Empty

    ' If valid selection take value of Account Number column for the active row
    If ActiveRange.Rows.Count = 1 And ActiveRange.Row >= FirstRow And ActiveRange.Row <= LastRow Then
      Dim rng As Excel.Range = CreateRange(accountNumberRange.Column, ActiveRange.Row)
      returnAccountNumber = rng.Value
    End If

    Return returnAccountNumber
  End Get
End Property

Using a property to return the account number, rather than having the ApplicationCommandBar or AccountDetail classes read it directly from the worksheet, is a matter of maintainability. By using the property, the structure and behavior of the AccountSummary class are self-contained, shielding other classes from the details. In general, I find that limiting all reading and updating of the physical worksheet to the class managing the worksheet increases the stability and maintainability of the application.

The Role of the ApplicationCommandBar Class

Because the ApplicationCommandBar class manages the "Account Detail" button, this is where we add the click event handler to display the Account Detail worksheet. In the event handler, we just retrieve the account number from the AccountSummary class and issue the call to the AccountDetail.Display method. We, of course, include some basic error checking and exception handling.

Class ApplicationCommandBar
  Private Sub accountDetailButtonInstance_Click(ByVal button As Office.CommandBarButton, _
        ByRef cancel As Boolean) Handles accountDetailButtonInstance.Click
    Try
      Dim accountNumber As String = Globals.AccountSummary.SelectedAccountNumber
      If accountNumber.Length > 0 Then
        Globals.AccountDetail.Display(accountNumber)
      Else
        MsgBox("You must select a row that contains a valid account
        number before the account detail worksheet can be viewed.")
      End If
    Catch ex As Exception
      MsgBox("Error: " & ex.Message)
    End Try
  End Sub
End Class

The Role of the AccountDetail Class

The role of the AccountDetail class is to populate the Account Detail worksheet with the data of the selected account. Like the other worksheet classes, it provides a Display method, which populates the corresponding physical worksheet. The Display method implementation is consistent with the other worksheets with no substantial differences.

Applying Stock Allocations

The Stock Allocation worksheet is the most interactive of the worksheets, accepting user input to determine the allocation criteria and allowing the user to override the calculated allocations by entering new values into the "Allocation to Apply" cells. Although slightly more involved than the other worksheets, the population and display code is still the same. What is different is that the Stock Allocation worksheet updates the data contained in the DataSet.

When the user clicks the "Apply Allocation" toolbar button, the DataSet is updated. The button click event is handled by the ApplicationCommandBar class, which ultimately calls the StockAllocation.ApplyAllocations method.

ApplyAllocations is responsible for the details of updating the DataSet. Primarily, this involves reading data from the Stock Allocation worksheet and adding new records to the ClientHoldings table. The first step is to declare local variables for the update values. We initialize the ones that are the same for all accounts, which are the price and purchase date. We do not need to read the symbol from the worksheet because it is stored in a class member variable, myStockSymbol.

Public Sub ApplyAllocations()
  Dim stockPrice As Double = stockPriceRange.Value
  Dim purchaseDate As DateTime = DateTime.Now

  Dim accountNumber As String
  Dim shareQuantity As Integer
  Dim accountRow As StockAllocationDataSet.ClientHoldingsRow

Just like when updating the worksheets, we utilize named ranges to read the data.

  For i As Integer = 0 To RowCount - 1
    accountNumber = accountNumberRange.Offset(i, 0).Value
    shareQuantity = allocationToApplyRange.Offset(i, 0).Value

    ' Update DataSet... 

  Next

On each loop iteration, we create a ClientHoldings row and update its values. We then store the row back in the table and subtract the cost of the allocation from the account's cash available.

' Create new row in client_holdings
accountRow = Globals.DataSet.ClientHoldings.NewClientHoldingsRow()

' Update row values
accountRow.AccountNumber = accountNumber
accountRow.StockSymbol = myStockSymbol
accountRow.PurchaseDate = purchaseDate
accountRow.PurchasePrice = stockPrice
accountRow.SharesQuantity = shareQuantity
' Store row back in table
Globals.DataSet. ClientHoldings.AddClientHoldingsRow (accountRow)
' Adjust Cash Available in ClientMaster table
AdjustCashAvailableForAllocation(accountNumber, stockPrice, shareQuantity)

We finish the function by displaying a success message to the user and updating the worksheets. To update the worksheets, we first call the OfficeCodeBehind.Refresh method, which tells each of the worksheets to re-read and display the DataSet data. We then call the StockAllocation.Display method with an empty stock symbol value, which clears the Stock Allocation display.

' Tell user it worked
Dim totalSharesApplied As Integer = totalAllocationToApplyRange.Value
MsgBox(String.Format("The allocation of {0} of {1} has been 
successfully applied.", totalSharesApplied, myStockSymbol))

' Update all worksheets to reflect change
OfficeCodeBehind.Refresh()

' Clear Stock Allocation worksheet
Display(String.Empty)

Updating the Stock Prices

The final aspect of the implementation is the stock price update. The user can update the stock prices at any time by clicking the "Update Stock Prices" toolbar button. The button click is handled by the ApplicationCommandBar.updateStockPrices_OnClick method, which is responsible for initiating the process of retrieving the latest prices, then refreshing all of the worksheets so that they display the updated prices.

Private Sub updateStockPricesButton_OnClick(ByVal button As Office.CommandBarButton, _
    ByRef cancel As Boolean) Handles updateStockPricesButtonInstance.Click
  DataSetUtility.UpdateStockPrices()  ' Update prices in the StockMaster table
  OfficeCodeBehind.Refresh()          ' Have all worksheets re-read the DataSet so latest prices are shown
End Sub

As mentioned in the architecture discussion, the stock allocation system retrieves the latest prices using a Java-based Delayed Stock Quote Web service provided by XMethods.net. We access the Web service using the netxmethodsservicesstockquoteStockQuoteService class, which we generated by selecting the Add Web Reference option on the Project menu of Visual Studio 2003 and typing the URL of the Web service's WSDL (http://services.xmethods.net/soap/urn:xmethods-delayed-quotes.wsdl) into the "URL" field of the "Add Web Reference" dialog.

Note For more information about accessing Web services with the Visual Studio 2003 Add Web Reference feature, see Adding and Removing Web References.

The netxmethodsservicesstockquoteStockQuoteService class handles all of the details of communicating with the Web service and makes updating the prices as simple as looping through the rows in the StockMaster table, updating the price of each.

Dim service As New netxmethodsservicesstockquoteStockQuoteService
For Each stockMaster As StockAllocationDataSet.StockMasterRow In _
    Globals.DataSet.StockMaster.Rows
  Dim currentPrice As Single = service.getQuote(stockMaster.StockSymbol)
  stockMaster.CurrentPrice = currentPrice
Next

The one complication is that the process of retrieving prices can be somewhat time consuming and this simple loop does not give the user visual feedback as to the progress. So, rather than perform this loop directly in the DataSetUtility.UpdateStockPrices method, DataSetUtility.UpdateStockPrices uses the UpdatePricesForm to retrieve the prices.

Friend Shared Sub UpdateStockPrices()
  Dim updateForm As New UpdatePricesForm
  updateForm.ShowDialog()
End Sub

UpdatePricesForm is a Windows Form class that, when displayed, performs the same loop we saw earlier to retrieve the updated prices. The key difference is that it also displays a progress bar and the name of each stock as is retrieves the updated prices (see Figure 21).

Figure 21. UpdatePricesForm displaying progress bar and stock name

Understanding Limitations of the Stock Allocation System

Before we close, here are a few issues and limitations of the stock allocation system.

Business Behavior Limitations

As I mentioned in the beginning of this paper, I based the stock allocation system on a portion of the business requirements for a real trade management system I previously wrote. If you are familiar with the brokerage industry or money management systems, you have likely noticed that the application avoids some of the more involved stock purchase issues. Obvious exclusions are support for buying on margin, multiple tax lots, partially filled orders, and average pricing. These and similar issues have intentionally been excluded to avoid introducing unnecessary complication into this example.

In the case of buying on margin, we treat all accounts as margin accounts, and we can therefore buy more shares than the number for which they have cash available. When applying an allocation to an account without sufficient cash, the whole allocation is applied and the account's cash available is set to zero.

The issue of multiple tax lots for a single stock does not come up because the allocation system excludes accounts already owning the stock being allocated from the allocation. This prevents an account from being allocated additional shares of a stock it already owns, and therefore prevents the account from having multiple tax lots for a single stock.

As for partial fills and average price, the system assumes that each account receives the allocated number of shares at the requested price. I realize that this is a bold assumption, but it is made to avoid unnecessarily complicating the sample.

Technical Limitations

When developing the stock allocation sample system, I tried to make the sample easy to install, work with, and explore. For this reason, I excluded certain technical features that would appear in a real-life system.

One key issue has to do with users entering data into the worksheets. By the nature of this application, users are not expected to enter data into the system manually. In a real-life system any new data, such as a new client, would be retrieved from the enterprise database and would not be typed into the workbook by the user. For this reason, most of the stock allocation workbook would be protected to prevent users from manually entering or changing data. The accompanying solution leaves the workbook unprotected so that you can easily explore and modify it.

Finally, there is the issue of synchronizing the DataSet with a database. In real life, the stock allocation system would include functionality to support synchronizing the DataSet with the corporate database when connectivity is available. I did not include this functionality, in order to stay focused on the issues related to Visual Studio Tools for Office. Requiring database setup would also substantially complicate the set up and installation of the sample project. For information about synchronizing a DataSet with database contents, see Using a DataSet with Existing Data.

Potential Threats to Code Security

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

To illustrate the simplicity of this sample solution, a list of potential threats has been identified using the threat modeling process and tools described in the Threat Modeling section of the Microsoft Security Developer Center.

The following table gives some examples of the identified threats that you should take into consideration before expanding or deploying this solution.

Table 15. Identified threats against the sample solution

Threat Effect	Entry Point	Known Mitigation
The XML data files are placed in an unprotected location where they are altered, or confidential information is leaked	XML data files	Windows security should be used to limit file access to only those users who require access File should be deleted if the component is no longer in use
Stock symbols transmitted to the pricing Web service are intercepted	Network	Use a secure network protocol
A Web server intercepts pricing requests, posing as the Web service, and returns bad pricing data	Network	Use a secure network protocol

For information about code security, please visit the Microsoft Security Developer Center.

Conclusion

With Visual Studio Tools for Office, we built a complete stock allocation system that takes advantage of the best that both Visual Studio 2003 and Excel 2003 have to offer. Visual Studio 2003 with the .NET Framework 1.1 gives us the enterprise-level application development tools we need. It provides a platform that enables us to easily implement a smart client solution, giving the user access to their data from anywhere at any time, while helping us to leverage our existing development skills and knowledge of relational data. The integrated Web service support has enabled us to take advantage of a Java-based Web service to maintain up-to-date pricing from any available Internet connection with minimal effort.

Excel 2003 provides our application with a rich and highly interactive user interface right out of the box. The familiar Excel 2003 interface provides the user with the comfort of a familiar look-and-feel and reduces user-training time because users can take advantage of their existing Excel 2003 skills. The rich chart and graph capabilities of Excel 2003 also give users at-a-glance access to important information.

Visual Studio Tools for Office gives us the ability to work more efficiently by enabling us to merge the rich user experience provided by Office 2003 with the enterprise-level development tools provided by Visual Studio 2003 and the .NET Framework 1.1.

Resources

Visual Studio .NET and Office Development

Microsoft Office Developer Center: Visual Studio Tools for the Microsoft Office System

Bring the Power of Visual Studio .NET to Business Solutions Built with Microsoft Office: Get a good introduction to Visual Studio Tools for Office development with code samples in both Visual Basic .NET and C#.

Creating XML Schemas and Datasets from Existing Data: See how to use the XSD.exe utility to generate typed DataSets and XML schemas.

How to: Bypass the Assembly When Opening an Office Solution: Learn about several options for opening a Word 2003 or Excel 2003 document without loading the associated assembly.

Adding and Removing Web References: See how to use the Web Reference feature of Visual Studio 2003 to access Web services.

Secure and Deploy Business Solutions with Microsoft Visual Studio Tools for Office: Read an overview of Visual Studio Tools for Office run-time security.

About the Author

Jim Wilson is the president of JW Hedgehog, Inc., a New Hampshire–based consulting firm specializing in developing solutions for the Windows and Windows Mobile platforms. Jim has worked extensively with the .NET Framework and the Microsoft .NET Compact Framework since the original beta release of each. As a speaker, Jim can be seen at Tech Ed, VSLive, Microsoft Mobility DevCon, Embedded DevCon, and WinDev. He is co-founder of the New Hampshire .NET User Group and is author of the MSDN online column titled You Can Take It With You.

Building a Professional Stock Allocation System Using Visual Studio Tools for Office, Version 2003

Contents

Introduction

Setting Up the Stock Allocation Solution

System Requirements

Installing the Stock Allocation System

Applying Visual Studio Tools for Office to the Business of Professional Money Management

A Little About the Business

Designing a Solution

The Account Summary Worksheet

The Account Detail Worksheet

The Stock Summary Worksheet

The Stock Allocation Worksheet

Designing the Development Architecture

Architecting As a Smart Client

Implementing the Solution in Excel 2003

Developing the Stock Allocation System Foundation

ThisWorkbook.VB

Adding Global Scope

Creating the Worksheet Foundation

Working with Ranges

Formulas and Globalization

WorksheetBase Properties

Worksheet Activation and Selection

Creating the Worksheets

Creating the Physical Worksheets

Creating the Worksheet Classes

Getting It All Connected

Making the Worksheets Available

Adding Navigation

Making the Command Bar Globally Available

Connecting the Command Bar

Making the Command Bar Context-Aware

A Note on Defensive Programming

Adding Data Management

System Foundation Wrap Up

Developing the Stock Allocation System Business Functionality

The Data Model

Formatting the Worksheets

A Technical Aside

Formatting the Account Summary Worksheet

Formatting the Account Detail Worksheet

Formatting the Stock Summary Worksheet

Formatting the Stock Allocation Worksheet

Restoring the Previously Commented-Out Line of Code

Populating the Account Summary Worksheet

Populating the Stock Summary Worksheet

Selecting and Displaying the Account Detail Worksheet

The Role of the AccountSummary Class

The Role of the ApplicationCommandBar Class

The Role of the AccountDetail Class

Applying Stock Allocations

Updating the Stock Prices

Understanding Limitations of the Stock Allocation System

Business Behavior Limitations

Technical Limitations

Potential Threats to Code Security

Conclusion

Resources

About the Author

Additional resources