UDF's from a COM Add-In

Add-in Express™ Support Service
That's what is more important than anything else

UDF's from a COM Add-In
How to create a COM Add-In that has Excel UDF's and share code 
John Putman




Posts: 19
 Joined: 2008-06-25
The primary purpose for purchasing Add-In Express is to be able to have a COM Add-In (with Ribbon and Toolbar functionality) and also be able to have UDF's call into the same add-in instance (same appdomain) and the Ribbon/Toolbars and UDF's share the same underlying code.

I thought that I was onto it at first... I created an Add-In Express COM Add-In and set up some ribbon controls. I registered the ADX Project and ran it in Excel... ribbon worked fine.

Next I wanted to add the UDF's... so I added a XLL Add-In Module. I was able to add functions and it seemed like it was working. Wehn I started Excel, however, I got the MDA message:
red

...and Excel did hang after that. In additional attempts to open Excel, COM Add-In does not load anymore. And when I try to add the XLL, I get the same message above.

Any pointers on how to make the COM Add-In and XLL get along? Or is there another way to get UDF's and the COM Add-In to share code?

thanks for the help...

-putman
Posted 25 Jun, 2008 13:33:55 Top
John Putman




Posts: 19
 Joined: 2008-06-25
OK... I switched gears and created another COM Add-In. This time after I created some ribbon buttons, I tried adding a 'COM Excel Add-In Module'. I added a few functions to the class and started it up in Excel.

The Ribbon buttons work fine... and the UDF's work too. However, they cannot see shared code.

For instance, I have a class with a static UserName property. I can have a Ribbon button set that UserName. Then if I have a UDF try to access the UserName property, then it finds an uninitialized UserName.

Is it not possible to have the UDF see the same shared code?

thanks all...
Posted 25 Jun, 2008 14:58:34 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello John,

Is it not possible to have the UDF see the same shared code?


It is possible. But at run time, UDF and COM add-in are loaded into the Excel process through different ways. That is, your assembly is loaded twice: as a COM add-in and as an Excel Automation add-in. Normally, they live in different domains but if your COM add-in calls Evaluate(<a function in the UDF>) at add-in startup, this creates Excel Automation add-in in the same domain.

I've reproduced the "Loader Lock" issue. I clicked Continue twice and it worked. I know for sure that it is possible to switch this warning off. Unfortunately, I cannot tell you where this option resides in Studio.


Andrei Smolin
Add-in Express Team Leader

Posted 26 Jun, 2008 11:53:54 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
John,

I missed a signinficant part of your post. Sorry for that.

If the UDF and COM add-in are created in the same AppDomain, you can use the AddinModule.CurrentInstance static method. Just cast the result of the method.

If they are in different domains (and this is a general approach), then you need to write the following code (it's VB):

ExcelApp.COMAddins.Item("MyAddin.ProgId").Object.MyProperty

This is late binding.

Will this do?


Andrei Smolin
Add-in Express Team Leader
Posted 26 Jun, 2008 12:24:17 Top
John Putman




Posts: 19
 Joined: 2008-06-25
Thanks for your replies Andrei... but I still can't quite get it to work. I must be doing something... not quite right.

I emailed you my simple example... could you look at that and see if you can get the UDF 'GetUserName' to read the same value as the Ribbon button?

That would get me on the right track... thank you so much!
Posted 26 Jun, 2008 17:27:21 Top
Eugene Astafiev


Guest


Hello John,

You should replace the code in the GetUserName method with: 


object progId = "AddInExpressTest3.AddinModule";
object addin = ((Excel.Application)this.HostApplication).COMAddIns.Item(ref progId).Object;
object value = ((Excel.Application)this.HostApplication).COMAddIns.Item(ref progId).Object.GetType().InvokeMember("UserName", System.Reflection.BindingFlags.GetProperty | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic, null, addin, null);
if (value != null)
{
    return value.ToString();
}
else
{
    return string.Empty;
}
Posted 27 Jun, 2008 06:28:40 Top
John Putman




Posts: 19
 Joined: 2008-06-25
Nevermind Andrei... I got it working using the Evaluate function to put them in the same AppDomain... that seems to be exactly what I was looking for!
Posted 27 Jun, 2008 13:24:05 Top
John Putman




Posts: 19
 Joined: 2008-06-25
Oh... I didn't see your response... thank you for doing that for me!

Are there any known drawbacks to using the Evaluate method and having them load into the same AppDomain? I know if one rashes, it will crash them both, but I'm not worried about that. Is there anything else? Otherwise I'm just going to stick with that solution for now.

thanks again for all of your help!
Posted 27 Jun, 2008 13:28:04 Top
Eugene Astafiev


Guest


Hello John,

We don't know about other pitfalls related to the usage of the same AppDomain in the XLL and COM add-in.
Posted 28 Jun, 2008 06:34:08 Top