Have you ever looked at the Excel PIA “Microsoft.Office.Interop.Excel” using the object browser in Visual Studio and just been blown away and confused by what you see there?

 

For example, consider what .NET Interop does to the simple Excel Application object you know and love from VBA days.  It turns it into a multi-headed (8 heads to be exact, 36 if you count each delegate individually) monster.  All of the following are public types that you see in the browser related to the Excel Application object:

 

Interfaces

1. _Application

2. AppEvents

3. AppEvents_Event

4. Application

5. IAppEvents

 

Delegates

6. AppEvents_*EventHandler (29 of them)

 

Classes

7. AppEvents_SinkHelper (AppEvents)

8. ApplicationClass (_Application, Application, AppEvents_Event)

 

This pattern repeats for Chart, OLEObject, QueryTable, Worksheet, and Workbook.

 

Let’s try to untangle this mess by working our way backwards from the original IDL file for the Excel application object.  The COM coclass for the Application object looks like this—it has two interfaces, a primary interface called _Application and an event interface called AppEvents.

 

    [

      uuid(00024500-0000-0000-C000-000000000046),

      helpcontext(0x00020001)

    ]

    coclass Application {

        [default] interface _Application;

        [default, source] dispinterface AppEvents;

    };

 

TLBIMP (which is used to process the COM type library for Excel and make the PIA) directly imports the _Application(1) and AppEvents(2) interfaces, so now we’ve explained where two of the 8 types come from.  But the AppEvents interface isn’t very useful—it seems to me like an artifact of the TLBIMP conversion in some ways.  It has to be processed further to create another interface described later called AppEvents_Event to be of any use.

 

When TLBIMP processes the coclass, it creates a .NET class called ApplicationClass(8) which is named by taking the coclass name and appending “Class”.  It also creates a .NET interface with the same name as the coclass called Application(4) for our example.  If you look at Application in the browser, it has no properties and methods of its own, but it derives from the other two interfaces associated with the coclass: _Application and AppEvents_Event. 

 

OK—but we haven’t explained where the AppEvents_Event interface comes from.  When TLBIMP processes the AppEvents event interface on the coclass, it creates several helper types.  First it creates AppEvents_Event(3) which looks like AppEvents but with events and delegate types replacing the methods in AppEvents.  It also creates delegates named AppEvents_*EventHandler(6) where * is the method name for each method on the original AppEvents interface.  Finally it creates an AppEvents_SinkHelper(7) which was supposed to be private in the original TLBIMP implementation, but this ended up breaking the Office PIAs.  I can’t remember the whole explanation for this issue right now (if I remember I'll blog about it)—suffice it to say that we had to make it public to get events to work.  But you can treat AppEvents_SinkHelper as being “theoretically private”—that is, you should ignore it.

 

So that leaves only the IAppEvents(5) interface unexplained.  TLBIMP imports this interface directly because it is a public type in the Excel typelibrary.  But you can treat this as theoretically private also.  This is effectively a duplicate of AppEvents, except AppEvents is declared as a dispinterface in the type library and IAppEvents is declared as a dual interface type.  I’m not sure quite why this is there (future blog entry if I figure it out), but you can ignore it for the purposes of this conversation.

 

So which of these do you really use?  Basically, you should only use in your code the Application interface (4) (which derives from _Application and AppEvents_Events) and the delegates (6).  The rest you should pretend don’t exist. 

 

To recap:

 

Interfaces

_Application                              Direct import from type library (Ignore.  Typically you don’t use this directly--Application interface derives from this)

AppEvents                                Direct import from type library (Ignore—artifact that is not used in real coding)

AppEvents_Event                       Created while processing the AppEvents event interface (Ignore.  Typically you don’t use this directly—Application interface derives from this)

Application                                Created while processing the Application coclass (Use this interface)

IAppEvents                               Dual interface version of AppEvents in the type library (Ignore—artifact that is not use in real coding)

 

Delegates

AppEvents_*EventHandler (29 of them)    Created while processing the AppEvents event interface (Use these.  You use these when declaring delegates to handle events).

 

Classes

AppEvents_SinkHelper               Created while processing the AppEvents event interface (Ignore. Theoretically private)

ApplicationClass                       Created while processing the Application coclass (Ignore.  This is used behind the scenes to make it look like you can “new” an Application interface)

 

The Application interface that is created by TLBIMP for the coclass is an interesting duck.  You can write code like this in C# that makes it look like you are creating an instance of the Application interface which we all know is impossible:

 

Application myApp = new Application();

 

Really, this is syntactical sugar that is using the ApplicationClass behind the scenes (the Application interface is attributed to associate it with the ApplicationClass) to cocreate an Excel application and return the appropriate interface.

 

Finally, I mentioned earlier that this pattern repeats for Chart, OLEObject, QueryTable, Worksheet, and Workbook.  The mapping to Chart is straightforward—replace “Application” with “Chart” and “AppEvents” with “ChartEvents” and you’ll get the general idea.  Worksheet is a bit strange.  Its coclass looks like this:

 

    [

      uuid(00020820-0000-0000-C000-000000000046),

      helpcontext(0x00020010)

    ]

    coclass Worksheet {

        [default] interface _Worksheet;

        [default, source] dispinterface DocEvents;

    };

 

So for Worksheet replace “Application” with “Worksheet” but replace “AppEvents” with “DocEvents”—yielding “DocEvents_*EventHandler” as the delegates for WorkSheet events.

 

QueryTable is even weirder.  Its coclass looks like this:

 

    [

      uuid(59191DA1-EA47-11CE-A51F-00AA0061507F),

      helpcontext(0x000200be)

    ]

    coclass QueryTable {

        [default] dispinterface _QueryTable;

        [default, source] dispinterface RefreshEvents;

    };

 

 

So for QueryTable, replace “Application” with “QueryTable” and replace “AppEvents” with “RefreshEvents”—yielding “RefreshEvents_*EventHandler” as the delegates for QueryTable events.