locked
Communication between Excel UDF addin and VSTO addin RRS feed

  • Question

  • Hi. Hope someone can help.

    I have created an Excel UDF addin which fetches some data from a database into an Excel cell, this all works fine.  I have also created a VSTO Excel addin which assists the user in creating the formula required for the UDF.  This also accesses the same database as the UDF, and again, this all works fine.  I now would like to streamline things a little, for example I would just like to use the one data connection.  I therefore either need to expose a property from from UDF addin to my VSTO addin, or from my VSTO to UDF. 

    I have read countless articles (for example http://blogs.msdn.com/b/andreww/archive/2007/01/15/vsto-add-ins-comaddins-and-requestcomaddinautomationservice.aspx) on exposing properties from a VSTO addin and have successfully managed to expose my VSTO property to Excel VBA via the Office.Addin.Object property.  However, because my UDF addin does not have access to the Excel application object I cannot access it.  If someone could give me some idea how I might achieve this it would really help.

    I have also tried the same method on my UDF addin, but because it is not a Office.COMAddin object (it is an Excel.Addin object), it therefore does not have an object property and the solution will not work.  Perhaps I am looking at the problem in the wrong way?

    Any suggestions would be greatly appreciated.  Thanks.


    Alan Moseley

    Wednesday, March 18, 2015 6:59 PM

Answers

  • Hello Alan,

    If your both assemblies are managed you can use standard ways for communicating between domains (or processes) - WCF (.Net Remoting).

    Thursday, March 19, 2015 2:23 PM
  • Hi Alan,

    >>Every time you open a workbook you get another instance of the addins running.<<

    Base on my understanding, the add-in would be created when we start the Office application. Open a workbook will not get another instance of add-ins.

    You can get more detail about load process about VSTO add-in from link below:
    Architecture of Application-Level Add-Ins (Loading Process section)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 24, 2015 9:36 AM

All replies

  • Hi Alan

    As a "common denominator" you could use a DLL that both components can access. If you create the DLL using .NET, then you'd make it COMVisible...


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, March 18, 2015 7:16 PM
  • Hi Cindy

    Thanks for your super speedy reply.  Interestingly my data communications are already through a COM visible DLL.  However each addin creates its own instance of my object, and I therefore end up with two connections.  Is there some way that one of my addins can create an instance and then the other one hook into the already running instance?


    Alan Moseley

    Wednesday, March 18, 2015 7:37 PM
  • Hi Alan

    << Is there some way that one of my addins can create an instance and then the other one hook into the already running instance?>>

    I don't think so, unless you'd migrate the VBA-UDF to a .NET component, then it might be possible (Word, not Excel, is my special area). But the best place to ask, maybe, would be the Excel for Developers forum - worth a try, anyway. It will depend on whether there's anyone there with VSTO experience...

    Usually, I would recommend creating a second message, but you could still get a more helpful response here and, once I've moved a message to Excel I can't move it back since I don't have moderator status in that forum. However, if you do get a satisfactory answer in that forum we could post a link on this thread which could help the next person with a similar issue :-)


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, March 18, 2015 7:43 PM
  • Cindy

    My UDF isn't VBA, it is a Com Visible .NET DLL.  Does that change things?


    Alan Moseley

    Wednesday, March 18, 2015 7:45 PM
  • Hi Alan,

    According to the description, you want to call the code in UDF dll from an application-level add-in. Did 'Com Visible .NET DLL' mean an XLL dll?

    Based on my understanding the both XLL and VSTLassemblly will create their own domain. And XLL doesn't provied a method to expose object for other solution to consume.

    In addition to Cindy's suggestion, I also would suggest that you to reopen a new thread in Common Language Runtime Internals and Architecture forum whether it is possible that expose an object from one domain to another.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 19, 2015 6:28 AM
  • Hello Alan,

    If your both assemblies are managed you can use standard ways for communicating between domains (or processes) - WCF (.Net Remoting).

    Thursday, March 19, 2015 2:23 PM
  • Thank you all for your helpful suggestions.  Eugene's post made me think a little harder about my problem and I thought it was going to be my solution until I remembered an annoying fact about Microsoft Excel.  Every time you open a workbook you get another instance of the addins running.  So even if I did get VSTO addin and UDF sharing one remoted data application, as soon as I open another workbook I am would have to check to see if I already had the data application running and make use of that, and when closing the last instance of Excel I would need to make sure that I close down my data application separately.

    I have also investigated implementing Extensibility in my UDF.  I already use this in a previous version of the software I am working on, but I have found it problematic with permissions on Windows 7 and 8 so I have ruled that out also.

    I have therefore decided to write a service which can sit in the background opening and closing connections as required.

    Once again, thank you to everyone who contributed.


    Alan Moseley

    Saturday, March 21, 2015 2:24 PM
  • Hi Alan,

    >>Every time you open a workbook you get another instance of the addins running.<<

    Base on my understanding, the add-in would be created when we start the Office application. Open a workbook will not get another instance of add-ins.

    You can get more detail about load process about VSTO add-in from link below:
    Architecture of Application-Level Add-Ins (Loading Process section)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 24, 2015 9:36 AM
  • Hi Alan, I'm facing the same problem. How did you solve it?
    Wednesday, November 21, 2018 10:24 PM
  • I'm afraid I didn't, I just put up with the extra connections!  Sorry. :(

    Alan Moseley

    Thursday, November 22, 2018 4:58 PM