HowTo: Create a COM add-in, XLL UDF and RTD server in one assembly

<OffTopic>

You know, a pessimist differs from an optimist by his attitude to a glass: the former thinks the glass is half empty, while the latter considers it’s half full. When I was contemplating on the circumstances that made me write this post, I worked out another definition: a pessimist could say “what a fool I am at such a ripe age”  (I’m a 48 years old grandfather), but an optimist would rather declare “I am more clever now than I was a couple of years ago”. I am an optimist :)

It all began about a year ago, when I wrote the documentation for Add-in Express for Office and .net and stated that Office would load the assembly containing a COM add-in, an XLL UDF and RTD server (all in one) thrice: first as a COM add-in, then as an XLL, and one more time as an RTD. Now I see the only possible explanation for those words of mine – seasonal exacerbation of my foolishness.  

Yesterday I received a question that made me assume that I had been mistaken (see XLL, RTD, and COM Appdomains on our forums). I wrote a small project and understood that I definitely had been wrong. Now, when you are reading this post, the corresponding page in the Add-in Express 2010 documentation is already corrected (see Developing multiple Office extensions in the same project), however, you can still find that incorrect statement in the PDF manual for Add-in Express 2009 for Office and .net.

</OffTopic>

About this sample

Office loads an assembly containing a COM add-in, an XLL UDF and RTD server only once. The sample that I wrote for verifying this demonstrates the following things:

• How to check what AppDomain a COM add-in, XLL, and RTD server are loaded to.
• How to detect the sequence of loading the COM add-in, XLL, and RTD server (note that the load order heavily depends on extensions registered in Excel, on the way you start Excel, and on the Excel version installed on your machine).
• How to create and use data common for the COM add-in, XLL, and RTD server.
• How to find out whether the COM add-in, XLL, and RTD server were initialized.
• How to wrap the RTD call in an XLL.
• How to access the COM add-in, XLL, and RTD server classes from another extension of the same assembly.

By the way, there are other, universal for all scenarios, ways of accessing public methods defined in a COM add-in and Excel UDF.

Accessing a COM add-in and XLL UDF from another Office extension, standalone application and VBA

You can access a public method defined in a COM add-in from another Office extension, standalone application, and VBA using the following pathway:

ExcelApp.COMAddins.Item(strMyComAddinProgId).Object.MyPublicPropertyOrMethod

1. strMyComAddinProgId – see the ProgId attribute of your add-in module.

2. MyPublicPropertyOrMethod is called via late binding (see System.Type.InvokeMember in MSDN or search for examples on our .NET forum)

As to UDFs, you can call a public method defined in your UDF using ExcelApp.Evaluate(strMyMethodAndParams)

Note that in case of XLL, you can make  such a method unavailable for the end user, besides that this method may return no value.  

None of the above can be done for the Excel Automation add-in, which is not covered in this sample project, in particular because Add-in Express always loads it into the default AppDomain. You can try to bypass this limitation and load the UDF into the AppDomain of your COM add-in by calling a method defined in the UDF, again via ExcelApp.Evaluate. But this should be done BEFORE Excel calls some of the methods of your Excel Automation add-in. Taking into account that the order of loading Excel extensions is unknown, it may turn out to be quite a dilemma. I hope to write about this as well some day.  

<OffTopic>

One more time, in sackcloth and ashes, I apologize for bugs in our documentation. And one more time it makes me happy that a detected and fixed bug has made our product a bit better. One more time I wish you good luck! :) 

</OffTopic>

NOTA BENE. Before you open the project, you need to copy {Add-in Express}\Redistributables\adxloader.dll to {this project}\Loader\ and rename the file to adxloader.comaddin_xll_rtd_cs.dll, replacing the existing file of the same name.

Available downloads:

C# sample for Visual Studio 2005

You may also be interested in:

Creating a COM add-in for Microsoft Office
Building Excel real-time data servers
Developing Excel XLL add-ins