Automation Error trying to access Basic .NET library from VBA

Feb 23, 2014 at 4:23 PM
Hello,

I've been working through the examples/samples/docs and have put together a basic project to test DNA out. Unfortunately when I try to call a method from the comaddin.object I get "Automation Error" from excel. I'm using VS2010 & Excel 2010 & .NET 4.0. I can put a breakpoint in the AddInRoot and confirm that it gets loaded.
public class AddInRoot : IExcelAddIn
    {
        public void AutoClose()
        {
            try
            {
                var com_addin = new AddInComRoot();
                com_addin.GetType().InvokeMember("DnaLibrary", 
                    BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.SetProperty, null, com_addin, new object[] { DnaLibrary.CurrentLibrary });

                ExcelComAddInHelper.LoadComAddIn(com_addin);
            }
            catch (Exception e)
            {
                MessageBox.Show("Error loading COM AddIn: " + e.ToString());
            }
        }

        public void AutoOpen() { }
    }

    [ComVisible(true)]
    public class AddInComRoot : ExcelDna.Integration.CustomUI.ExcelComAddIn 
    { 
               // : IDTExtensibility2, ie COM "AddIn".ExcelDNA finds this by magic.
        MyAddinObject _helper;
        public AddInComRoot() { }

        public override void OnConnection(object Application,
            ext_ConnectMode ConnectMode, object AddInInst, ref Array custom) 
        {
            _helper = new MyAddinObject();

            AddInInst.GetType().InvokeMember("Object",
                BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty, 
                null, 
                AddInInst,  
                new object[] { _helper });
        }
        public override void OnDisconnection(ext_DisconnectMode RemoveMode, ref Array custom) { }
        public override void OnAddInsUpdate(ref Array custom) { }
        public override void OnStartupComplete(ref Array custom) { }
        public override void OnBeginShutdown(ref Array custom) { }
    }

    [ComVisible(true)]
    public class MyAddinObject
    { 
        // This becaomes the VBA addin.Object
        public string SayHello() 
        {
            return "Hello from the future!";
        }

        public void DoNothing()
        {
            MessageBox.Show("Yo yo");
        }

        public string ActiveCell3() 
        {
            var app = (Excel.Application)ExcelDnaUtil.Application;
            Excel.Range r = app.ActiveCell;
            return "ActiveCell3: " + r.Value;
        }
    }
and then in the workbook, module ThisWorkbook:

Private Sub Workbook_Open()
    Application.RegisterXLL ("C:\stuzor\dropbox\Work\Resources\MyCodePrototypes\ExcelDnaLibrary\ExampleDnaLibrary\bin\Debug\ExampleDnaLibrary-AddIn.xll")
End Sub
and a simple module to test the functionality

Sub Button1_Click()
    TestDnaComAddIn
End Sub

Sub TestDnaComAddIn()
    Dim cai As COMAddIn
    Dim obj As Object
    For Each cai In Application.COMAddIns
        ' Could check cai.Connect to see if it is loaded.
        If InStr(cai.Description, "ExampleDnaLibrary Add-In (COM Add-in Helper)") Then
            Set obj = cai.Object
            If obj Is Nothing Then
              MsgBox "ObjNothing"
            Else
                Dim a As String
                a = obj.SayHello()  'Automation Error thrown here
                MsgBox a
            End If
        End If
    Next
End Sub
Any suggestions?

I want to upgrade an old class library that I've been registering with Regasm.exe - too often people have problems with this.. I'm hoping DNA can solve these problems for me.
Coordinator
Feb 24, 2014 at 3:21 PM
I think you put the code for the AutoOpen() in your AutoClose() method.

If that's not it, let me know and I'll have a closer look.

-Govert
Feb 24, 2014 at 9:37 PM
wow dude. that's it. trust me to make that kind of stupid mistake.. Thanks so much for your eagle eyes