Andrei Smolin

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

2 Comments

  • Hello,

    >> I’m a 48 years old grandfather

    That proves that You are an optimist once for all! I find myself (at the age of 48) in a constant learning process and what we say today does not necessarily be the truth the next day.

    What makes Your tools nice is not only how well the purposes they serves but also how they evolve over time. In a perfect world both tools and the supporting documentation would be perfect and all the customers would also be perfect. As we know, we don’t live in any perfect world but as long as we learn and evolve things are OK.

    Kind regards,
    Dennis

  • Thank you, Dennis! Your comments are always well-wishing.

Post a comment

Have any questions? Ask us right now!

string(10) "SUMMERSALE"