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

In Chapter 2, "Introduction to Office Solutions," you saw that VSTO allows developers to build customized document solutions by using Word and Excel as designers inside Visual Studio. The host item classes expose the host controls present at design time as custom properties on a class that aggregates the underlying unmanaged object.

But what about host controls not present at design time? What if you want to create new named ranges, bookmarks, buttons, or other controls at runtime? It would be nice to be able to use the new events and other extensions to the programming model on dynamically generated controls. As you will see, VSTO supports adding both host items and host controls dynamically, although the former is a little bit trickier to pull off.

Chapter 14, "Using Windows Forms in VSTO," shows how to add Windows Forms controls dynamically to Word and Excel documents.

The Controls Collection

In a Windows Forms application, every form class has a property called Controls that refers to a collection of all the controls hosted by the form. In VSTO, each worksheet and document class contains a similarly named property. In Word, the document class contains an instance of Microsoft.Office.Tools.Word.ControlCollection. In Excel, each worksheet class contains an instance of Microsoft.Office.Tools.Excel.ControlCollection. These two classes are quite similar; the following sections discuss their differences.

Enumerating and Searching the Collection

You can use the Controls collection to enumerate the set of aggregated controls and perform actions upon all of them. You could disable all the button controls on a sheet or document, for example:

For Each control As Object In Me.Controls If TypeOf control Is Button Then Dim button As Button = control button.Enabled = False End If Next

The Controls collection also has some of the indexing and searching methods you would expect. Both the Excel and Word flavors have methods with these signatures:

Public Function Contains(ByVal control As Object) As Boolean Public Function Contains(ByVal name As String) As Boolean Public Function Index(ByVal control As Object) As Integer Public Function Index(ByVal name As String) As Integer

If the collection does not contain the searched-for control, IndexOf returns 1. Both collections can be enumerated via a For Each loop; should you want to enumerate the collection yourself, you can call GetEnumerator. This method returns a ControlCollectionEnumerator object from the Microsoft.Office.Tools.Excel or Microsoft.Office.Tools.Word namespace, as appropriate. They are essentially identical functionally. Both classes have only three public methods:

  • ReadOnly Property Current() As Object

  • Function MoveNext() As Boolean

  • Sub Reset()

Current returns Nothing when moved past the final element in the collection; MoveNext moves the enumerator to the next element; and Reset starts the enumerator over at the beginning of the collection.

Both collections also expose three index operators, which take a name as a String, an Integer index, and an Object, respectively. The indexers throw an ArgumentOutOfRangeException if there is no such control in the collection.

Adding New Word and Excel Host Controls Dynamically

The worksheet and document Controls collections provide methods to create host controls dynamically. In Word, you can create aggregated bookmarks dynamically:

Public Function AddBookmark( _ ByVal range as Microsoft.Office.Interop.Word.Range, _ ByVal name as String) As Microsoft.Office.Tools.Word.Bookmark

This method creates a new bookmark on the given range and aggregates it with the VSTO host control class.

Note

XMLNode and XMLNodes host controls cannot be created dynamically in Word. The XMLMappedRange host control cannot be created dynamically in Excel.

In Excel, you can create aggregated NamedRanges, ListObjects, and Chart controls dynamically. Of those, only Chart controls can be positioned at arbitrary coordinates; all the rest must be positioned with a range object:

Public Function AddChart( _ ByVal range As Microsoft.Office.Interop.Excel.Range, _ ByVal name As String) As Microsoft.Office.Tools.Excel.Chart Public Function AddChart(ByVal left As Double, _ ByVal top As Double, _ByVal width As Double, _ ByVal height As Double, ByVal name As String) _ As Microsoft.Office.Tools.Excel.Chart Public Function AddNamedRange( _ ByVal range As Microsoft.Office.Interop.Excel.Range, _ ByVal name As String) As _ Microsoft.Office.Tools.Excel.NamedRange Public Function AddListObject( _ ByVal range As Microsoft.Office.Interop.Excel.Range, _ ByVal name As String) _ As Microsoft.Office.Tools.Excel.ListObject

Removing Controls

The host controls added to a worksheet or document host item class at design time are exposed as properties on the host item class. If at runtime the user were to delete one accidentally, save the document, and then reload it, the customization code would be unable to find the aggregated control. This would likely result in an exception because eventually the customization would try to listen to an event or call a method on the missing aggregated control. If the customization detects this condition, it will throw a ControlNotFoundException.

Although it is difficult to prevent end users from accidentally or deliberately deleting controls without locking the document, the Controls collection can at least try to prevent programmatic destruction of controls added at design time. There are four equivalent ways to remove controls from the Controls collection; all will throw a CannotRemoveControlException if you attempt to remove a control that was not added dynamically.

The four ways to remove a dynamic control are to call Delete() on the control itself or to call Remove(ByVal control As Object), Remove(ByVal name as String), or RemoveAt(ByVal index as Integer) on the Controls collection itself. All four of these methods remove the control from the collection, remove the control from the document or worksheet, and destroy the extender object.

Most collections have a Clear() method that removes every member from the collection. Because completely clearing a Controls collection would almost always result in an exception when a design-time control was removed, this method always throws a NotSupportedException and is hidden from IntelliSense.

Saving and Loading Controls

What happens when you add one or more dynamic controls to a document, save it, and reload it later?

Dynamically created Windows Forms controls such as buttons and check boxes do not survive being saved and then loaded. They just disappear; your customization code can create them again afresh the next time the document is loaded.

Because "host" controls such as ranges and bookmarks are themselves part of the document, they will be persisted along with the rest of the document. The controls do not save any information about any aggregating objects you may have created around them, however. When the document is reloaded, the controls will still be there, but there will be no aggregates wrapping them. You will have to add the controls back to the Controls collection to create new aggregates for the controls. The Controls collection provides Add methods that can reconnect an aggregate to an existing control in the document without creating a new control in the document.

Advanced Topic: Dynamic Host Items

As you have just seen, adding new aggregated host controls onto a host item is relatively straightforward: just call the appropriate method on the controls collection for the containing host item and the control is created, aggregated, and placed on the host item automatically.

But what if you should want to use some of the features of an aggregated host item class on a dynamically created worksheet? To do that, you need only three lines of code. Understanding those three lines will require us to delve somewhat deeper into how the VSTO runtime, the hosting application, and the aggregating class all work together.

Start by creating a helper method in the ThisWorkbook class that takes in the worksheet you want to be aggregated and returns an aggregated worksheet:

Friend Function AggregateWorksheet( _ ByVal worksheet As Microsoft.Office.Interop.Excel.Worksheet) _ As Microsoft.Office.Tools.Excel.Worksheet

Recall that the aggregating object obtains the aggregated object "on demand." That is, it obtains the underlying object only when the first method is called that must be passed along to the underlying object. That means that the aggregating object must not require the aggregated object when the aggregating object is constructed, but it does need to be able to obtain that object at any time. Somehow, the aggregating object must talk to the host and obtain the unique object that it is aggregating.

It does so by passing a string called the cookie, which identifies the aggregated object to a special service object provided by the host. In the event that an error occurs when attempting to fetch the worksheet, the runtime will need to raise an error. It is possible that the cookie that uniquely identifies the aggregated object might contain control characters or be otherwise unsuitable for display. Therefore, the aggregate constructor also takes a human-readable name, used in the event that the host is unable to find the object to be aggregated. In the case of Excel worksheets, we will use a cookie that is already created for each worksheet by VBA, called the CodeName. To initialize that cookie, we must make a call into the VBA engine to force the cookie to be created.

How do we obtain a reference to the service that maps cookies onto unmanaged host objects? The already-aggregated host item has a member variable called RuntimeCallback that contains a reference to the VSTO runtime library's service provider. Service provider is actually a bit of a misnomer; a service provider is an object that knows how to obtain objects that provide services, not necessarily one that provides those services itself. We identify services by the interface they implement.

Finally, to make data binding work properly, the aggregating class needs to know what object contains this worksheet. Chapter 17, "VSTO Data Programming," covers data binding in more detail.

Let's put all this together. We need to obtain five things to create an aggregating worksheet:

  • A host-provided service that can obtain the aggregated object

  • The cookie that the host application uses to identify the worksheet

  • A human-readable name for the worksheet

  • The container of the worksheet

  • The VSTO runtime service provider

We obtain the service that maps the name and container to the aggregated object by passing the appropriate interface type to the VSTO runtime service provider:

Dim hostItemProvider As IHostItemProvider hostItemProvider = Me.RuntimeCallback.GetService( _ GetType(IHostItemProvider))

Next, we have to make a call into VBA to initialize the CodeName for the new worksheet. This line of code does nothing except force VBA to initialize. It does not add a VBA project to the workbook or anything else of that nature. It does access the VBProject object, however. For a solution that dynamically creates host items in Excel, you must make sure that users of your solution have Trust access to Visual Basic Project checked in the VBA Security dialog box (Tools > Macro > Security). Otherwise, this line of code will fail:

Me.VBProject.VBComponents.Item(1)

We will use the name of the new Worksheet object for the human-readable name and the CodeName as the host cookie. The container of the new worksheet is the same as the container of the current workbook. The final function looks like this.

Friend Function AggregateWorksheet(ByVal worksheet As _ Excel.Worksheet) As Microsoft.Office.Tools.Excel.Worksheet Dim hostItemProvider As IHostItemProvider hostItemProvider = Me.RuntimeCallback.GetService( _ GetType(IHostItemProvider)) Me.VBProject.VBComponents.Item(1) AggregateWorksheet = New Excel.Worksheet(hostItemProvider, _ Me.RuntimeCallback, worksheet.CodeName, Me, worksheet.Name) End Function

To use this function, put the following code in the Startup handler for Sheet1. This code creates a new worksheet, calls the AggregateWorksheet function in the ThisWorkbook, and then adds a dynamic button to the newly created worksheet using the aggregated worksheet returned by the function:

Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup Dim worksheet As Excel.Worksheet = _ Globals.ThisWorkbook.Sheets.Add() Globals.ThisWorkbook.AggregateWorksheet( _ worksheet).Controls.AddButton(10, 10, 100, 100, "foo") End Sub

Just as dynamic host controls are not re-created when a document containing them is saved and then reloaded, dynamic host items are not re-created.

Inspecting the Generated Code

Let's take a deeper look behind the scenes at what is going on when you customize a worksheet or document. Create a new Excel project; create a named range called MyRange; and take a look at the code for Sheet1.vb in Listing 13.1.

Listing 13.1. The Developer's Customized Worksheet Class

Public Class Sheet1 Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup End Sub Private Sub Sheet1_Shutdown(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Shutdown End Sub End Class

Upon closer inspection, a few questions might come to mind. Where is the MyRange property declared and initialized? Didn't we say earlier that the customized worksheet class extends a base class? Where is the base class declaration?

It's Visual Basic's support for partial classes that is the key. C# and Visual Basic support a new syntax that allows a class declaration to be split among several files. The portion that you see before you is the home of all your developer-customized code; the automatically generated code is hidden in another portion of the class not displayed by default.

Click the Show All Files button in Solution Explorer, and you will see that a number of normally hidden files make up the class, as shown in Figure 13.3.

Figure 13.3. Using the Show All Files button to examine hidden code.

First, notice that behind every worksheet is an XML file for the worksheet. If you look at the first few lines of the XML, you will see that it contains a description of the contents of the worksheet and how to represent it as a class. This "blueprint" contains information about what namespace the class should live in, what the name of the class should be, what controls are exposed on the class, how Excel identifies those controls, and so on.

Behind this language-independent representation of the class is another Visual Basic file that contains the other half of the partial class, generated from the XML blueprint. It begins something like this:

[View full width]

<Microsoft.VisualStudio.Tools.Applications.Runtime. _ StartupObjectAttribute(1), _ System.Runtime.InteropServices.ComVisibleAttribute(False), _ System.Security.Permissions.PermissionSetAttribute( _ System.Security.Permissions.SecurityAction.Demand, _ Name:="FullTrust")> _ Partial Public NotInheritable Class Sheet1 Inherits Microsoft.Office.Tools.Excel.Worksheet Implements Microsoft.VisualStudio.Tools.Applications.Runtime .IStartup Friend WithEvents MyRange As _ Microsoft.Office.Tools.Excel.NamedRange

As you can see, here is where the base classes are specified and the member variables declared. The class also specifies that it is one of the startup classes in your customization assembly and that code that calls members of this class must be fully trusted.

Plenty more code is in the hidden portion of the partial class, most of which is devoted to initializing controls, starting data binding, and handling data caching; Chapter 17, "VSTO Data Programming," discusses data binding in more detail. The constructor, in particular, should look familiar:

[View full width]

Public Sub New(ByVal RuntimeCallback As _ Microsoft.VisualStudio.Tools.Applications.Runtime .IRuntimeServiceProvider) MyBase.New(CType(RuntimeCallback.GetService( GetType( _ Microsoft.VisualStudio.Tools.Applications.Runtime .IHostItemProvider)), _ Microsoft.VisualStudio.Tools.Applications.Runtime .IHostItemProvider), _ RuntimeCallback, "Sheet1", Nothing, "Sheet1") Me.RuntimeCallback = RuntimeCallback End Sub

This is functionally the same code discussed in the previous section on creating custom host items by calling the aggregate base class constructor.

If you ever want to debug through this code, ensure that Just My Code Debugging is turned off (via the Tools > Options > Debugging > General dialog box). Then you can put breakpoints on any portion of the hidden code, just like any other code.

Do not attempt to edit the hidden code. Every time you make a change in the designer that would result in a new control's being added, or even change a control property, the hidden half of the partial class is regenerated completely. Any changes you have made to the hidden half will be lost; that is why it is hidden by default!

The Startup and Shutdown Sequences

You have probably noticed by now that we have been putting custom initialization code in an event handler:

Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup Me.MyRange.Value = "Hello" End Sub

But exactly what happens, in what order, as the startup classes are created and initialized? Excel customizations typically have many startup classes, one for each sheet and one for the workbook itself. Which ones load first?

You already saw a clue that answers the latter question. In the hidden half of the partial class, each class declaration has an attribute:

<Microsoft.VisualStudio.Tools.Applications.Runtime. _ StartupObjectAttribute(1), _

The Workbook class has 0 for the argument; Sheet1 has 1; Sheet2 has 2; and so on. The workbook aggregate always has ordinal 0, and each worksheet is given its ordinal based on the order in which Excel enumerates its sheets. The startup sequence happens in four phases, and each phase is executed on each startup class in order of the given ordinal before the next phase begins.

In the first phase, each class is constructed using the constructor mentioned above. This simply constructs the classes and stores the information that will be needed later to fetch the unmanaged aggregated objects from Excel or Word.

In the second phase, the Initialize method of each startup class is calledagain, in multiclass customizations, starting with the workbook and then each worksheet by ordinal. If you look at the hidden half of the partial class, you will see the Initialize method:

[View full width]

<Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _ Global.System.ComponentModel.EditorBrowsableAttribute( _ System.ComponentModel.EditorBrowsableState.Never)> _ Public Sub Initialize() Implements _ Microsoft.VisualStudio.Tools.Applications.Runtime.IStartup .Initialize Me.HostItemHost = CType(Me.RuntimeCallback.GetService(GetType( _ Microsoft.VisualStudio.Tools.Applications.Runtime .IHostItemProvider)), _ Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider) Me.DataHost = CType(Me.RuntimeCallback.GetService(GetType( _ Microsoft.VisualStudio.Tools.Applications.Runtime .ICachedDataProvider)), _ Microsoft.VisualStudio.Tools.Applications.Runtime .ICachedDataProvider) Globals.Sheet1 = Me System.Windows.Forms.Application.EnableVisualStyles() Me.InitializeCachedData() Me.InitializeControls() Me.InitializeComponents() Me.InitializeData() Me.BeginInitialization() End Sub

The attributes prevent the Initialize method from showing up in IntelliSense drop-down lists and mark the method as being "not my code" for the Debug Just My Code feature. Then the initializer fetches services from the host that are needed to initialize the view and data elements, sets up the global class (discussed in more detail later in this chapter), loads cached data, and initializes all the controls.

In the third phase, data binding code is activated. Data bindings must be activated after all the classes are initialized because a control on Sheet2 might be bound to a dataset on Sheet1.

Finally, in the fourth phase, after everything is constructed, initialized, and data-bound, each startup class raises its Startup event, and the code in the developer's half of the partial class runs.

This multiphase startup sequence ensures that you can write handlers for the Startup event that can assume not just that the class itself is ready to go, but also that every startup class in the customization is ready to go.

Ideally, it would be a good idea to write Startup event handlers for each class that do not depend on the order in which they are executed. If you must, however, you can always look at the startup attributes to see in what order the events will be executed.

The shutdown sequence is similar but simpler. As the host applicationWord or Excelshuts down, each host item class raises the Shutdown event. Shutdown events are raised in the same order as each phase in the startup sequence.

The Globals Class in Excel

Suppose that you're writing code in the Sheet1 class that needs to set a property on a control hosted by Sheet2. You are probably going to need to obtain the instance of the aggregated Sheet2 class somehow. Instead of aggregating properties representing all the other sheets and the workbook aggregates onto each startup class, VSTO exposes all the sheets and the workbook as static members of the Globals class:

Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup Globals.Sheet2.MyRange.Value = "Hello" End Sub

Because at least the first three phases of the startup sequence have finished at this point, you know that the Globals class and Sheet2 have been initialized, although Sheet2's Startup event has probably not fired yet.

Notice that by default, controls aggregated onto the worksheet classes are given the Friend visibility modifier. You can change the visibility modifier generated for a control by selecting the control in the designer and then selecting the Modifiers property in the Properties window. If you change the visibility of the control to Private, however, you will be unable to access the control's field from the Globals class.

The Globals class is also constructed using partial classes, although by default, there is no visible portion. Rather, each generated code file defines a portion of the Globals class. You can see this code at the bottom of the hidden file for each class. Should you for some reason want to add your own custom members to the Globals class, you can always create your own portion of the partial class.

Related

Категории