Excel 2003 and Visual Studio 2005 Tools for Office Solution: Using Dynamic Controls and Worksheets
Chris Kunicki, www.OfficeZealot.com
Published: January 2005
Updated: January 2006
Applies to: Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft Office Excel 2003
Summary: Learn how to use dynamic Microsoft .NET Framework managed controls and dynamic worksheets in an Excel 2003 solution using Microsoft Visual Studio 2005 Tools for Office. The associated code sample is provided in Visual Basic and C#. (16 printed pages)
Microsoft Office Excel provides flexibility in crunching numbers and analyzing data. Even so, you may want to provide custom functionality or an alternative way of interacting with an Excel workbook. While providing custom functionality has been easy to do for years with Microsoft Visual Basic for Applications (VBA) or Microsoft Visual Studio Tools for Office, Version 2003, it has not been easy to provide alternative ways of interacting with the Excel user interface, until recently.
Visual Studio Tools for Office, Version 2003 gave developers the ability to write Microsoft .NET code behind Microsoft Office Word and Excel documents. Visual Studio 2005 Tools for Office goes much further. With Visual Studio 2005 Tools for Office, you can now create managed controls on the document surface, create custom task panes, data bind document elements to an external data source, programmatically change documents on a server, and much more.
This article focuses on the new ability of Visual Studio 2005 Tools for Office to create managed controls in Excel dynamically. It introduces a sample solution and discusses why managed controls benefit Microsoft Office solutions.
Why Managed Controls?
Using controls in an Excel workbook is not a new idea. For years, developers used Microsoft ActiveX controls, such as the TreeView control, DatePicker control, Web Browser control, and any of the built-in Excel form controls on a worksheet. These controls provided developers with a different way to display information or with a way to give users an alternative method to navigate information.
Developers used this functionality, but often with extreme caution. Many developers found combining Excel and ActiveX controls difficult for these reasons:
ActiveX controls require a client-side installation and registration step. Because many developers do not control the desktop of their users, this can be a challenge.
ActiveX controls in Excel can behave unpredictably and require intensive testing to assure reliability. Excel and other Microsoft Office products are not ideal ActiveX containers.
Some developers consider ActiveX controls to be a possible security risk.
It was often easier to settle with a less useful solution to avoid possible problems with ActiveX. This did not mean that developers did not want to use controls, but that doing so was rarely worth the hassle.
The Microsoft .NET Framework addresses these concerns. Microsoft .NET includes many useful controls as part of the .NET Framework, removing the need to deploy controls. Also, the .NET Framework provides several ways to distribute more securely your own custom controls or those bought from third parties. Visual Studio 2005 Tools for Office resolves the stability of Excel and controls. Visual Studio 2005 Tools for Office makes working with managed controls in Excel reliable during design-time and at run time.
From personal experience, I can tell you that seeing managed controls run reliably and securely in Excel is exciting. Using the ActionsPane object to combine managed controls with the Visual Studio 2005 Tools for Office task pane programmability opens a new world for Excel solution development that goes beyond simply providing custom menus and toolbars in your solution.
To help you better understand the value and use of managed controls in an Excel solution, I developed this article around a common, Excel-based business scenario. The business scenario revolves around the Corporate Services Business unit of the fictional Woodgrove Bank.
The Corporate Services Business unit provides customized banking services and products to large corporate customers who want to outsource common employee-related retirement and investment benefits. For example, a company might hire Woodgrove Bank to provide their employees with a customized 401(k) retirement savings plan offering and training. One challenge with customized banking services is the extended sales closing process. Management requires their salespeople to closely track potential sales leads and current status. Each month, management consolidates and rolls-up all sales leads for analysis.
To aid the individual salespeople in this process, management created an Excel workbook called Sales Lead Tracking. Figure 1 shows a worksheet in the Sales Lead Tracking workbook with information about the Litware Inc. sales lead.
Figure 1. Sales Lead Tracking workbook
Sales lead tracking is a common requirement of many organizations that sell products and services with long closing cycles. The Sales Lead Tracking workbook helps organize the following information:
Individual sales opportunities
Information about the customer (name, address, phone number, e-mail address)
The primary contact
The products or services being pitched
The status of the sales opportunity
The dates of the last customer contact and the next contact date
Management reviews this information on a monthly and quarterly basis to analyze:
Individual and sales team performance and effectiveness
Future revenue and resource utilization requirements
In this workbook, each salesperson tracks each individual sales opportunity that he or she pursues. For each individual opportunity, the salesperson creates a separate worksheet and collects common information such as customer information, products and services, and the status of the opportunity. The first worksheet in the Sales Lead Tracking workbook is a Summary worksheet that consolidates all the opportunities into one master view, as shown in Figure 2.
Figure 2. Summary worksheet with consolidated leads
Now that you understand the business scenario, you can work with controls in code.
Using Managed Controls
In many ways, working with Excel and managed controls in Visual Studio 2005 Tools for Office is similar to working with controls in Microsoft Windows Forms development. In Windows Forms development, you simply drag controls from the toolbox onto the Windows Form. You then set the properties of controls in the Properties window. You can programmatically alter a Windows Form and its controls from code. As stated earlier, the experience is very similar when using Excel with Visual Studio 2005 Tools for Office.
Figure 3 shows Visual Studio 2005 with an Excel workbook open in the designer. Visual Studio 2005 Tools for Office loads Excel as a child window into Visual Studio to give you a first-class design experience. In the designer, you can drag controls from the toolbox onto the worksheet. The Properties window enables you to change the properties of the selected control. As you drag controls onto the worksheet and change their properties, Visual Studio 2005 Tools for Office generates the underlying code to recreate these controls at run time. The following illustration shows a DatePicker control being dragged from the toolbox to the Microsoft Excel design surface.
Figure 3. Visual Studio 2005 Tools for Office with Excel open in the designer
New Visual Studio 2005 Tools for Office Objects
You can access all the controls created during development programmatically, and you can dynamically create controls on the fly. The Visual Studio 2005 Tools for Office runtime has a few new objects with which you interact when working with controls.
Host item. In Visual Studio 2005 Tools for Office, the Word Document and Excel Worksheet classes are known as host items. Host items provide a view on data and are a container for controls, such as host controls and Windows Forms controls. There are two more host items in Excel, the Workbook and ChartSheet, but these do not act as containers for host controls. You should treat a host item as a helpful wrapper around built-in Excel objects, such as the workbook and worksheet.
Host control. A host item and a host control differ from each other. Host controls extend existing Word and Excel objects. Host controls added to Office documents behave like native Office objects, such as ranges; however, they have additional functionality, including events and data binding capabilities.
For example, when you want to capture the events of a native Range object in Excel, you must trap the change event of the worksheet, and then determine if the change occurred in the range in which you are interested. With the NamedRange host control, you can access the change event directly. The Sales Lead Tracking solution uses the NamedRange host control to detect when the user changes the Opportunity Name on a sales lead worksheet.
Do not confuse host controls with Windows Forms controls. Host controls are helper controls that enhance built-in Excel functionality.
Globals object. The Globals object is a helper object that, as its name implies, represents a global view of the document or workbook in your solution. The Globals object gives you easy access to the Workbook, all the Worksheets created at design time, and the Excel Application object. If you are familiar with Excel VBA programming, you might find it helpful to compare the Globals object to the ActiveWorkbook object.
The Globals object has a Worksheet object for each sheet in your workbook at design time. For example, if your workbook contains three sheets named Sheet1, Sheet2, and Sheet3, the Globals object has three child objects. You access them as:
Globals.Sheet1 Globals.Sheet2 Globals.Sheet3
The Globals object is a helper object created during design time and is only aware of worksheets created in the Visual Studio designer. For this reason, if a user adds new worksheets to a workbook at run time, you cannot depend on Globals to access these new worksheets. Later you will see how to create a dynamic Worksheet object from worksheets added at run time.
Working with Controls
The following code shows how to reference each control in the Controls collection of Sheet1 in an Excel workbook. As the code reaches each control, it displays the type name of the control in a message box:
Dim myControl As Object For Each myControl In Globals.Sheet1.Controls MessageBox.Show(myControl.GetType().FullName) Next
Notice that the variable myControl is an object. Because the worksheet can contain many control types, the object type is generic. If you know the type of the control, you can easily cast the control to its correct type. As noted earlier, Sheet1 is a Worksheet object that gives you programmatic access to the Controls collection on that worksheet.
To dynamically create a control at run time, the following code snippet dynamically creates a DateTimePicker control, and then adds it to Sheet 1:
Dim myDatePicker As Microsoft.Office.Tools.Excel.Controls.DateTimePicker myDatePicker = Globals.Sheet1.Controls.AddDateTimePicker(1, 1, _ 200, 200, "MyDatePicker")
First the code declares a DateTimePicker object. Second, it accesses the control container collection of the worksheet through Globals.Sheet1.Controls. The Controls collection represents all controls on the Worksheet, but also allows you to add new controls to the collection. The first four parameters of the AddDateTimePicker method deal with control positioning and size. The final parameter is a unique name for the new control on the worksheet.
After running this code, you see a new DateTimePicker control on the Sheet1 worksheet, aligned in the upper-right corner.
The Visual Studio 2005 Tools for Office runtime provides several helper methods in the Controls collection for adding common controls. In addition to the AddDateTimePicker method, the Controls collection includes the AddCheckBox, AddLabel, AddButton, and AddTextBox methods. There are about 28 helper functions for adding standard .NET Windows Forms controls to the workbook.
These helper functions also have a method override that accepts a range for positioning the control in the worksheet instead of using exact physical coordinates. The following code creates a DateTimePicker control and places it at range A1.
Dim myRange As Excel.Range = Globals.Sheet1.Range("A1") Dim myDatePicker As Microsoft.Office.Tools.Excel.Controls.DateTimePicker myDatePicker = Globals.Sheet1.Controls.AddDateTimePicker(myRange, _ "MyDatePicker")
By default, the new DateTimePicker moves and resizes with the underlying range. You can change this behavior through the control's Placement property. Using a range for placing controls simplifies the process of getting a control exactly where you want it on a worksheet and enables the Visual Studio 2005 Tools for Office runtime to manage keeping the control correctly positioned when the underlying range resizes or moves.
If you want to add a Windows Forms control or custom control for which the Visual Studio 2005 Tools for Office runtime does not provide a helper function, you can use the AddControl method of the Controls collection. The following code snippet shows how to add a TrackerBar control to the worksheet:
Dim myTrackerBar As New Windows.Forms.TrackBar Globals.Sheet1.Controls.AddControl(myTrackerBar, 500, 500, 200, 100, _ "MyTrackerBar") myTrackerBar.TickStyle = TickStyle.BottomRight
This code first creates a new instance of a managed control of the type Windows.Forms.TrackBar. Then, the code calls the Controls collection AddControl method. The AddControl method takes the TrackerBar control as its first parameter, along with the information needed to position the control on the workbook. Once the control is in place, you can change the TrackerBar properties programmatically.
Up to this point, the code showed what has worked against one of the worksheets available to us from the Globals object. How do we add controls to a worksheet created at run time by the user, knowing that the Globals object is only aware of worksheets created in the Visual Studio designer? The Visual Studio 2005 Tools for Office runtime does not provide a helper function to easily create a Worksheet object. To do this, we must use the runtime internals. The following code creates a Worksheet object based on a specified worksheet:
Private Function GetExtendedWorksheet( _ ByRef nativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet) _ As Microsoft.Office.Tools.Excel.Worksheet ' Get the IHostItemProvider instance. Dim hostItemProvider As _ Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider = _ RuntimeCallback.GetService( _ GetType( _ Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider)) ' Create the new worksheet and return it to calling function. Return New Microsoft.Office.Tools.Excel.Worksheet(hostItemProvider, _ RuntimeCallback, _ nativeWorksheet.CodeName, _ Container, _ nativeWorksheet.Name) End Function
Although the parameter and return type are both Worksheet objects, they are from different libraries. GetExtendedWorksheet takes a Worksheet object, which is a native Excel worksheet, and returns a Visual Studio 2005 Tools for Office Worksheet object. Remember, this Worksheet object is a wrapper around the Excel worksheet that gives you extended functionality. To add controls at run time to a new worksheet, you must get a Worksheet object. Using this function solves that problem.
Putting These Techniques to Use in Our Solution
This section shows how you can put the techniques described earlier to use in the business scenario. The salesperson using the Sales Lead Tracking workbook might generate anywhere from 10 to 50 leads each month. To add a new lead to the workbook, the salesperson uses the Add a New Sales Lead feature on the Summary worksheet. The salesperson types a name in the text box, and then clicks Add Lead, as shown in Figure 4.
Figure 4. Adding a new sales lead
Clicking Add Lead calls the CreateNewLeadWorksheet function with the information collected in the Add a New Sales Lead text box. The CreateNewLeadWorksheet function takes the name of the lead and creates a new worksheet with the name of the lead, basing it on a template that is marked as xlSheetVeryHidden. xlSheetVeryHidden tells Excel to hide this worksheet from the user. (The only way to access it is through code.) This allows us to hide the worksheet until we are ready to copy it.
First, CreateNewLeadWorksheet uses standard Excel object model methods to copy the worksheet and set various cell values. In addition, CreateNewLeadWorksheet adds the new lead to the master list of leads on the Summary sheet, in the top row of the list. CreateNewLeadWorksheet calls the AddDynamicControlsToLeadWorksheet function. The unique work happens in the AddDynamicControlsToLeadWorksheet function, as shown in this example:
Private Sub AddDynamicControlsToLeadWorksheet(ByRef sheet As _ Microsoft.Office.Interop.Excel.Worksheet) Dim dynamicWorksheetView As Microsoft.Office.Tools.Excel.Worksheet Static controlCounter As Integer = 0 Try 'To create controls dynamically we need to create a 'VSTO 2005 worksheet host control dynamicWorksheetView = GetExtendedWorksheet(sheet) 'CREATE Initial Date Range Date Picker Dim initialDateRange As Excel.Range = _ dynamicWorksheetView.Range("InitialContact") Dim initialDateDatePicker As _ Microsoft.Office.Tools.Excel.Controls.DateTimePicker = _ dynamicWorksheetView.Controls.AddDateTimePicker _ (initialDateRange, "InitialDate" & controlCounter) initialDateDatePicker.Width = 150 If initialDateRange.Value Is Nothing Then 'If no date is specified, default to today initialDateDatePicker.Value = Date.Today initialDateRange.Value = Date.Today Else 'Use existing date if it exists initialDateDatePicker.Value = initialDateRange.Value End If AddHandler initialDateDatePicker.ValueChanged, _ AddressOf initialDateTimePicker_ValueChanged 'CREATE Followup Date Range Date Picker Dim followupDateRange As Excel.Range = sheet.Range("Followup") Dim followupDateTimePicker As _ Microsoft.Office.Tools.Excel.Controls.DateTimePicker = _ dynamicWorksheetView.Controls.AddDateTimePicker _ (followupDateRange, "Followup" & controlCounter) followupDateTimePicker.Width = 150 If followupDateRange.Value Is Nothing Then 'If no date is specified, default to today followupDateTimePicker.Value = Date.Today followupDateRange.Value = Date.Today Else 'Use existing date if it exists followupDateTimePicker.Value = followupDateRange.Value End If AddHandler followupDateTimePicker.ValueChanged, _ AddressOf FollowupDateTimePicker_ValueChanged 'CREATE OpportunityName Change NamedRanged control Dim opportunityNameRange As Excel.Range = _ sheet.Range("OpportunityName") Dim opportunityNamedRangeView As _ Microsoft.Office.Tools.Excel.NamedRange = _ dynamicWorksheetView.Controls.AddNamedRange( _ opportunityNameRange, "OpportunityNamedRangeView" & _ controlCounter) opportunityNameRange.Value = sheet.Name AddHandler opportunityNamedRangeView.Change, _ AddressOf OpportunityName_Change 'CREATE TrackerBar control Dim trackerBarRange As Excel.Range = sheet.Range("StatusTaskBar") Dim tb As New Windows.Forms.TrackBar tb.TickStyle = TickStyle.BottomRight tb.Maximum = 4 dynamicWorksheetView.Controls.AddControl(tb, _ trackerBarRange, "trackbar" & controlCounter) tb.Value = trackerBarRange.Value tb.Width = 125 AddHandler tb.Scroll, AddressOf TrackBar_Scroll Catch ex As Exception MsgBox(ex.Message) Finally controlCounter += 1 End Try End Sub
AddDynamicControlsToLeadWorksheet takes the new worksheet created by CreateNewLeadWorksheet. It then calls GetExtendedWorksheet to get back the Worksheet object of the new worksheet. With a new Worksheet object, AddDynamicControlsToLeadWorksheet can dynamically add controls to the worksheet.
AddDynamicControlsToLeadWorksheet adds the following controls to the worksheet:
DateTimePicker control. Holds the initial contact date.
DateTimePicker control. Holds the follow-up contact date.
TrackerBar control. Sets the status of the lead.
NamedRange control. Monitors cell change events.
The first control added to the worksheet is a DateTimePicker control, which displays the initial contact date. The function creates the initial date DateTimePicker control, places it in a range called InitialContact, and then sets its value. On a new worksheet, the initial date is set to today's date. When the code calls AddDynamicControlsToLeadWorksheet on an existing worksheet, it pulls the initial contact date from the InitialContact range in the worksheet.
After the code creates the control and sets its properties, it hooks up the ValueChanged event of the DateTimePicker control to the initialDateTimePicker_ValueChanged procedure using AddHandler and AddressOf, as shown in this example:
AddHandler initialDateTimePicker.ValueChanged, _ AddressOf initialDateTimePicker_ValueChanged
The following code shows the initialDateTimePicker_ValueChanged function:
Private Sub initialDateTimePicker_ValueChanged( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Dim initialDateDatePicker As _ Microsoft.Office.Tools.Excel.Controls.DateTimePicker = sender Dim initialDateRange As Excel.Range = _ Globals.ThisWorkbook.ActiveSheet.Range("InitialContact") Try initialDateRange.Value = initialDateDatePicker.Value Catch ex As System.Runtime.InteropServices.COMException 'The value cannot be changed right now, reset the date picker to the original date initialDateDatePicker.Value = initialDateRange.Value Catch ex As Exception MsgBox(ex.Message) End Try End Sub
When the ValueChanged event fires on the InitialDateTimePicker control, the sender object is the DateTimePicker control that the user clicked. In this event, you can use Globals.ThisWorkbook.ActiveSheet to interact with the current worksheet. The initialDateTimePicker_ValueChanged event procedure stores the value of the DateTimePicker in the cell on which the DateTimePicker is placed.
Depending on your solution, you can choose where to store information related to the controls on the worksheet. You might choose to store values in a separate data file that you can reference later. Alternatively, you might choose to use a hidden workbook to store data that needs to persist between Excel user sessions. In my experience, the best choice for most scenarios is storing the value of the control in the cell that it covers. This gives you an easy way to store the data and it is easy to reference from anywhere else in Excel using standard Excel functions because the data is in a cell. Also, because the control sits on top of the cell, the stored value is not visible to the user.
Another benefit of storing the controls' values in underlying cells is that those values persist when the user saves the workbook. You can use those saved values to restore the state of the controls when the user reopens the workbook.
Where Did My Controls Go?
All controls created in the Visual Studio 2005 designer persist between user sessions of a workbook. In other words, when a user closes a workbook, and then reopens the workbook later, the controls are still there. However, if a control is created dynamically at run time, as in the AddDynamicControlsToLeadWorksheet function, the controls do not persist. The same problem exists in Windows Forms development. Dynamic controls do not persist.
As the developer, you must restore the controls. To do this, the Sales Lead Tracking solution uses the Sheet1_Startup event, which fires each time a user opens the workbook. The Sheet1_Startup event examines each of the worksheets and adds back the dynamic controls.
Private Sub Sheet1_Startup( ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup 'Make sure the template and configuration worksheets are hidden ShowHiddenSheets(False) Try 'Initialize All Worksheets with dynamic controls by iterating 'through each worksheet. If it is based on LeadTemplate, 'initialize all the controls. Note: The progress bar is also 'made visible during this process and then removed. Me.Select() If Globals.ThisWorkbook.Worksheets.Count > 2 Then lblUpdatingWorkbook.Visible = True pb.Visible = True pb.Step = pb.Maximum / (Globals.ThisWorkbook.Worksheets.Count - 1) For Each ws As Excel.Worksheet In Globals.ThisWorkbook.Worksheets pb.Increment(pb.Step) Globals.ThisWorkbook.Application.ScreenUpdating = False Try 'Determine that this is one of the Lead worksheets, 'but dont modify our base template If ws.CustomProperties.Count > 0 Then Dim cp As Excel.CustomProperty = ws.CustomProperties.Item(1) If cp.Value = True Then AddDynamicControlsToLeadWorksheet(ws) End If End If Catch ex As Exception End Try Me.Select() Globals.ThisWorkbook.Application.ScreenUpdating = True Next End If Catch ex As Exception MsgBox(ex.Message) Finally pb.Visible = False lblUpdatingWorkbook.Visible = False Globals.ThisWorkbook.Application.ScreenUpdating = True End Try End Sub
When the Sheet1_Startup event fires, the code walks through each worksheet and determines if it is based on the Lead template. If it is, the event calls AddDynamicControlsToLeadWorksheet, passing in the sheet that needs dynamically created controls. Because the original values of the controls are stored in cells on the worksheet, as each control is dynamically created, it is set to the value persisted into the worksheet.
As the document starts up, the code displays a progress bar to let the user know that the document is updating, as shown in Figure 5. If the workbook has many worksheets, it might take a while for each worksheet to populate with dynamic controls. The progress bar gives users feedback, letting them know to wait.
Figure 5. Progress bar
When a user first opens a workbook, he or she might notice that the controls look disabled, and that they disappear and then reappear. When a user closes a workbook and saves it, the Visual Studio 2005 Tools for Office runtime does not destroy the controls; they are saved with the document. The next time the user opens the workbook, the runtime destroys the controls, recreates them, and connects them to their event handlers. Visual Studio 2005 Tools for Office team takes this approach so that controls run reliably in Excel.
The sample code in this article is intended for instructional purposes, and should not be used in deployed solutions without modifications. In particular, you must consider code security.
For more information about code security, visit the Microsoft Security Developer Center.
In this article, you saw how to use managed controls. Managed controls enable you to present and navigate data in ways that Excel does not support originally. The .NET Framework includes many useful controls, but why stop there? Using the .NET Framework, you can create custom UserControls and use them in Excel.
Visual Studio 2005 Tools for Office
Office Developer Center
About the Author
Chris Kunicki is a longtime enthusiast of Office development and has been evangelizing Office as an important platform for building solutions for many years. As the founder of OfficeZealot.com, a leading Web site on Office solutions development, Chris builds enterprise solutions, designs tools for developers, delivers presentations, and writes extensively on the topic of Microsoft Office. Find out more about Chris and Office at www.OfficeZealot.com or send him an e-mail message at firstname.lastname@example.org.