Life cycle of .NET automation server called from Excel over COM

Question

Like the subject says, I wrote a C# class, say Test, then compiled it as a DLL and exposed it to Excel as a COM automation server. Now, you can call the methods of that class in Excel formulas either directly or through a VBA function that itself calls them under the hood. What happens in the second case is relatively transparent (you wrote the code!) while in the first case I assume a Test instance is transparently instantiated and then used (it has to since the exposed methods are not and -- from what I have read -- cannot be static) but exactly when and how is unclear. Thus my question is: what is the precise life cycle of an automation server called in this setting and where is that documented?

A subsidiary question is whether there is a way to make both direct calls in formulas and calls via VBA use a single singleton instance of Test? I think I already know how to get all the VBA procedures to use a single Test instance but even so a second one is created whenever a Test method is called directly in a formula.

Lastly, if that Test class holds resources at the class level (i.e., static resources), when and how should you dispose of them? Again, that begs the question of exactly when Test classes are created and destroyed by .Net/COM Interop.

Precision: the service is a DLL, not an application, and Excel should be the only thing using it, although there might be more than one copy of the spreadsheet open. This is Excel 2007.

Answer 1

"Excel should be the only thing using it":

I found out the hard way that possibly the only way to accomplish this application restriction is hackish or cryptic. AFAIK: keep a hard-coded or external list of calling application white/blacklists referenced by your library, with a lookup performed on each library load. By nature, COM classes are exposed to the entire world... literally.

"whether there is a way to make both direct calls in formulas and calls via VBA use a single singleton instance of Test?"

You'll have to treat it as a singleton, either by trying very hard to continually use only one instance, or make a "singleton wrapper" function holding a static variable which holds the object reference in your VBA code.

"if that Test class holds resources at the class level (i.e., static resources), when and how should you dispose of them?":

They are (typically) automatically cleaned up at application termination time, or when the application calls the COM object's 'finalize' method (if applicable to the internal workings of the COM class's library), or when otherwise the object's reference count drops to 0 (if applicable to the internal workings of the COM class's library).

I think a question which might provide better insight is: "How does the particular COM class I'm using handle its own lifespan?" Sometimes COM objects are perpetual, even living well beyond an end of a procedure within your calling application. Depending on the COM class design, the object could be made to remain alive regardless of if there's any remaining referencing objects within the calling application. Sometimes you would be required to explicitly destroy the object reference (using VBA 'delete' methods) or else the object could persist (as perhaps intended by designers of that particular COM class) as long as the calling application does. This is painfully evident by the persistence of ActiveX objects from website to website, and one of the main reasons why when browsing the net, it might be best to "trust no one". Even worse is if a perpetual object itself references other objects and resources internally.

To truly get a handle on the life-span of the class, you'd have to study documentation provided by the COM class authors. In this case, you'll need to devise a way from within your COM class library to enforce or provide externally accessible methods to enforce life-span standards (implementing IDisposable, offering up your own public Dispose() method, etc).

A reference link to the DLL certainly will remain active during the entire life-time of the calling application, being born at the moment you even tried to create the first object. AFAIK, there's nothing you can do about that. This is evident just by monitoring DLL application ownership info using some 3rd party system info application. This makes sense as it saves resources loading and unloading the DLL every time you need it.