Share data between VSTO and Excel DNA App domains

Jan 8, 2014 at 9:24 PM
Edited Jan 8, 2014 at 9:24 PM
Hi,

Is there a way to share data between a VSTO add in and an Excel DNA add in? Or can the Excel DNA add in be loaded into the VSTO's app domain?

Regards,
Prajnan Das
Coordinator
Jan 8, 2014 at 10:07 PM
The Excel-DNA add-in will always be in a separate AppDomain. You might try to pass an object via the AddIn's Object property. Any MarshalByRef object works across AppDomains, but you need make the initial link...
Jan 9, 2014 at 6:21 PM
Edited Jan 9, 2014 at 6:29 PM
Thanks for your reply. Finally I ended up with this approach:

1) Put the sharable objects in a class lib and mark them COM visible:
namespace SharedLib
{
    using System.Runtime.InteropServices;

    [ComVisible(true)]
    [InterfaceType(ComInterfaceType.InterfaceIsDual)]
    public interface ISharedObject
    {
        int GetVSTOInstanceNumber();
    }

    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.None)]
    public class SharedObject : ISharedObject
    {
        private readonly int seed;

        private int serial;

        public SharedObject(int seed)
        {
            this.seed = seed;
        }

        public int GetVSTOInstanceNumber()
        {
            return ++serial + this.seed;
        }
    }
}
2) In VSTO create an instance of the shareable object and expose it via RequestComAddInAutomationService:
namespace SampleExcelAddIn
{
    using SharedLib;

    public partial class ThisAddIn
    {
        private const int Seed = 10;

        private ISharedObject sharedObject;

        protected override object RequestComAddInAutomationService()
        {
            if (sharedObject == null)
            {
                sharedObject = new SharedObject(Seed);
            }

            return sharedObject;            
        }

... code removed for brevity ...

    }
}
3) Now consume the shared object in the Excel DNA class lib:
namespace ExcelFunctions
{
    using ExcelDna.Integration;
    using Excel = Microsoft.Office.Interop.Excel;
    using SharedLib;

    public class ExcelFunctions
    {
        [ExcelFunction(Description = "Gets a serial number from the VSTO")]
        public static int GetVSTOInstanceNumber()
        {
            var application = (Excel.Application)ExcelDnaUtil.Application;
            object addinName = "SampleExcelAddIn";
            var addin = application.COMAddIns.Item(ref addinName);
            var utils = (ISharedObject)addin.Object;
            return utils.GetVSTOInstanceNumber();
        }
    }
}
This approach works great for me.

Regards,
Prajnan