Advanced Topic: Dynamic Host Items

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 on an existing worksheet class that takes in the worksheet you want to be aggregated and returns an aggregated worksheet:

internal Microsoft.Office.Tools.Excel.Worksheet AggregateWorksheet(
 Microsoft.Office.Interop.Excel.Worksheet 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 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 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:

IHostItemProvider hostItemProvider = (IHostItemProvider)
 this.RuntimeCallBack.GetService(typeof(IHostItemProvider));

We next 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. However, it does access the VBProject object. 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 (Tools > Macro > Security). Otherwise, this line of code will fail:

this.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 worksheet:

return new Microsoft.Office.Tools.Excel.Worksheet(hostItemProvider,
 this.RuntimeCallback, worksheet.CodeName, this.Container,
 worksheet.Name);
}

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