Automation Error trying to access Basic .NET library from VBA

Feb 23, 2014 at 4:23 PM

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()
                var com_addin = new AddInComRoot();
                    BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.SetProperty, null, com_addin, new object[] { DnaLibrary.CurrentLibrary });

            catch (Exception e)
                MessageBox.Show("Error loading COM AddIn: " + e.ToString());

        public void AutoOpen() { }

    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();

                BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty, 
                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) { }

    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()
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"
                Dim a As String
                a = obj.SayHello()  'Automation Error thrown here
                MsgBox a
            End If
        End If
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.
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.

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