Integrating with VBA

Excel Dna can make it easy to call between .Net and VBA. This means existing VBA code need not be rewritten. And end users are likely to find VBA muc easier to develop in.

To do this, create a .dna project, and register the (one) Dna class that will be the entry point from VBA as follows:-

    public class AddInRoot : IExcelAddIn {
        public void AutoOpen() {
            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 AutoClose() {
        }
    }


    [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 string ActiveCell3() {
            var app = (Excel.Application)ExcelDnaUtil.Application;
            Excel.Range r = app.ActiveCell;
            return "ActiveCell3: " + r.Value;
        }
    }

}


We then need to get a handle to the Excel DNA file and call these methods. We need to search through Descriptions because we cannot set the ProgId directly. The object is nothing test is also required as one can easily end up with dead entries in the Addins list. CustomUI ribbon objects may also appear in this list, so test for the entire Description.

' In VBA: 
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.
        Debug.Print cai.Description, cai.GUID
        If InStr(cai.Description, "MyTitle (COM Add-in Helper)") Then
            Set obj = cai.Object
            If obj Is Nothing Then
              Debug.Print "ObjNothing"
            Else
              Debug.Print obj.SayHello(), obj.ActiveCell3
            End If
        End If
    Next
End Sub


To call from .Net to VBA it is probably easiest to just use Application.Run.

However, be careful about asynchronous calls, see the Performing Asynchronous Work topic.

Last edited Nov 21, 2013 at 3:25 AM by aberglas, version 6

Comments

TuRRiCAN Jan 10, 2014 at 9:53 AM 
Hi,
I have some questions here:
Because my VS could not resolve the symbol 'Excel' in the code for ActiveCell3 I added the using clause 'using Excel = NetOffice.ExcelApi;'
Now VS can resolve 'Excel' and it compiles smoothly. BUT when I run the VBA-Code the SayHello function works well, but the ActiveCell3 function causes the Error:
Runtime Error '-2147467262 (80004002)':
The COM-Object of type "System.__ComObject" can not be converted to classtype "NetOffice.ExcelApi.Application". Instances of types, which represents COM-Components, can't converted into types, which are not representing COM-Components.
A conversion into interfaces is possible, if the COM-Component supports QueryInterface-Calls for the IID of the interface.
(Sorry for my bad translation)
What to do?
Do I always have to register the comAddin before I can use a function or is there a more general way?

Thanks in advance
Greetings
TuRRiCAN