Shared object instance VBA / C#

May 15, 2015 at 12:46 PM
I have the class ExcelInterface in which I instantiate an instance of class Test. The only purpose of ExcelInterface is to call the test instance where I continuously calculate and store values to memory. These UDFs are used by the normal end-users.

However, some developers wants to create an instance of Test from VBA. This works fine with export of .tlb etc - but I want the VBA calls and UDF calls to access the same instance (since it stores values). Is there any best practice for this? Singleton, static class or something else?

The VBA developers uses late binding and it's no option to run the UDF from VBA,.
[ComVisible(false)]
    public class ExcelAddin : IExcelAddIn 
    static Test test = new Test();

     [ExcelFunction(
         Description = "Call Test to store result in memory",
     )]
     public static String MyTestFunction) {
         return test.doSomethingHeavy();
     }
Thanks.
Coordinator
May 15, 2015 at 12:52 PM
Are you using the Excel-DNA ComServer support?

If not, you should. That will ensure everything is in the same AppDomain, and static variables are shared.
See the walkthrough here: http://mikejuniperhill.blogspot.com/2014/03/interfacing-c-and-vba-with-exceldna-no.html

If you're already doing that, there should be no problem sharing static variables from the VBA side and the worksheet UDFs.

-Govert
May 15, 2015 at 1:12 PM
Hi Govert - thanks for the quick reply and for the library!

The .dll are marked as ComServer:
<ExternalLibrary Path="Library.dll" Pack="true" ComServer="true"/>
and the Test class are marked as ComVisible with a reference to the "real" class RealTestClass which has no reference to Com/Excel-DNA since it's mainly used in other projects. Correct me if I'm wrong, but this enables me to separate the COM exposed methods and no need to involve this in the RealTestClass.
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class Test {
        private static RealTestClass realTest = new RealTestClass();

        public string doSomethingHeavy() {
            return realTest.heavyWork();
        }
And from VBA the developers uses:
  Dim test As New Library.Test
  test.doSomethingHeavy
Still can't see how they would share the same instance - probably missing something vital here.

Best regards,
aflyw
Coordinator
May 15, 2015 at 1:21 PM
One vital bit is that your COM library project in Visual Studio must not be marked as 'Register for COM Interop'. If it is, the VBA call will directly instantiate the library from the .dll, which will happen in the default AppDomain.

You want it to work through the Excel-DNA add-in - for this you call regsvr32 MyAddIn.xll and it will register the .xll as the InprocServer32, and everything will get loaded into the add-in's AppDomain, allow statics to be shared.

Your check for all of this is to add a diagnostic method to the COM server interface which just returns "AppDomain.CurrentDomain.FriendlyName". You can then see from the VBA side whether is is indeed loaded into the right AppDomain (the result will have Excel-DNA and the add-in path in, if all is good).

-Govert
Aug 6, 2015 at 1:54 PM
Hi Govert,

Sorry for the late reply. I followed your instructions and got it to work. Many thanks!


aflyw