Application.COMAddIns.Item("DnaTest").Object

Dec 1, 2012 at 5:11 AM

Hello,

In the Vsto ExcelAddIn,I can used Application.COMAddIns.Item("xxx").Object in VBA Code.

And In ThisAddin.cs I used 

 protected override object RequestComAddInAutomationService()
        {
            //.....

            return expClsPtr;//return a cs# object
        }

Now,I create a dna excelAddin,and how can I set Application.COMAddIns.Item("xxx").Object a cs# object?

Coordinator
Dec 1, 2012 at 6:46 AM

You can use the COM Server feature of Excel-DNA to expose classes to VBA.

1. Add a public class that is COM Visible, and has explicit ProgId and ClsId attributes.

2. In your AutoOpen, register it with a call to ExcelDna.ComInterop.ComServer.DllREgisterServer().

3. Mark you <ExternalLibrary ...> tag in the .dna file as ComServer="true".

4. Create the object in VBA using CreateObject("...") with the ProgId you assigned in step 1.

-Govert

Coordinator
Dec 1, 2012 at 1:08 PM

Another approach is create a COM add-in as part of your Excel-DNA add-in, and when OnConnection is called you hook up the COMAddIn's Object property.

Your Excel-DNA add-in would be like this:

<DnaLibrary RuntimeVersion="v4.0" Language="C#" >
<Reference Name="System.Windows.Forms" />
<![CDATA[

using System;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
using ExcelDna.Integration.Extensibility;

namespace TestComAddIn
{
    public class ExcelAddIn : IExcelAddIn 
    { 
        private MyComAddIn com_addin; 

        public void AutoOpen() 
        { 
            try 
            { 
                com_addin = new MyComAddIn(); 
                ExcelComAddInHelper.LoadComAddIn(com_addin); 
            } 
            catch (Exception ex) 
            { 
                MessageBox.Show("Error loading COM AddIn: " + ex); 
            } 
        } 
        public void AutoClose() 
        {
            // Not sure whether to unload here....
        } 
    } 

    [ComVisible(true)]
    [Guid("6EBB3413-E604-4FAF-897D-E2B6262CBEBA")]
    [ProgId("Test.ComAddIn")]
    public class MyComAddIn : ExcelComAddIn
    {
        public override void OnConnection(object Application, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
        {
            MessageBox.Show("In OnConnection.");
            try
            {
                dynamic addIn = AddInInst;
                addIn.Object = new SharedClass();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex);
            }
        }
    }

    [ComVisible(true)]
    public class SharedClass
    {
        public string SayHello() 
        {
            return "Hello from .NET!";
        }  
    }

}
]]>
</DnaLibrary>

and the corresponding VBA code might be:

Sub test()
    Dim x As Object
    Set x = Application.COMAddIns("Test.ComAddIn").Object
    Debug.Print x.SayHello()
End Sub
-Govert

Sep 16, 2013 at 5:59 PM
Hi Anyone,

Could anyone tell me what would I need to do (i.e., how to configure the dna file above) in order to have the class SharedClass reside in a file of its own within the project under some namespace, say, MyProjectNameSpace?

With the given example above I seem to be constrained to write the entire add-in within the the dna-extension xml file -- I would prefer to write the code in a separate file as writing code within VS IDE could be easier (e.g., I could enjoy the context sensitivity feature).

Thank you

Avi
Coordinator
Sep 16, 2013 at 6:58 PM
Hi Avi,

The code can also be part of your project. Say you are compiling it to a library as 'MyAddIn.dll'. Then your .dna file will not have the code, but just a reference to your library, like this:
<DnaLibrary RuntimeVersion="v4.0" >
   <ExternalLibrary Path="MyAddIn.dll" />
</DnaLibrary>
Regards,
Govert
Sep 16, 2013 at 7:58 PM
Thank you so much for the prompt reply Govert(-:


I am not sure what I am doing something wrong but this (inclusion of the ExternalLibrary tag) does no seem to resolve the issue (-:

I musts say that when I place all of the code that is to be exposed through Excel VBA (i.e., COM-related codes) in the dna-extension file, all seems to work fine in that I am able to call on dll functions from Excel VBA module AS WEL AS use Excel worksheet function created with ExcelDNA (e.g., Multiply function mentioned below). The following are the two files which work WITHOUT any issues

File 1 (dna-extension file)

<DnaLibrary RuntimeVersion="v4.0" Language="C#">


<ExternalLibrary Path="IntegratingWithVBA2.dll" />
<Reference Name="System.Windows.Forms" />
<![CDATA[
using System;
using System.Reflection;
using SWF = System.Windows.Forms;
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
using ExcelDna.Integration.Extensibility;
using System.Runtime.InteropServices;


public class MyCom : ExcelDna.Integration.CustomUI.ExcelComAddIn
{
MyAddinObject _helper;
public MyCom()
    {
    }
    public override void OnConnection(object Application, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
    {
        SWF.MessageBox.Show("OnConnection");
         _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)
    {
        SWF.MessageBox.Show("OnDisconnection");
    }
    public override void OnAddInsUpdate(ref Array custom)
    {
        SWF.MessageBox.Show("OnAddInsUpdate");
    }
    public override void OnStartupComplete(ref Array custom)
    {
        SWF.MessageBox.Show("OnStartupComplete");
    }
    public override void OnBeginShutdown(ref Array custom)
    {
        SWF.MessageBox.Show("OnBeginShutDown");
    }
}

public class AddIn : IExcelAddIn
{
    private ExcelComAddIn com_addin;

    public AddIn()
    {
    }

    public void AutoOpen()
    {
        try
        {
            com_addin = new MyCom();
            // We want to do this: 
            // com_addin.DnaLibrary = ExcelDna.Integration.DnaLibrary.CurrentLibrary;
            // But the DnaLibrary property is marked 'internal' to ExcelDna.Integration.
            // v0.29 workaround: set by Reflection
            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)
        {
            SWF.MessageBox.Show("Error loading COM AddIn: " + e.ToString());
        }

    }

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


}    

]]>
</DnaLibrary>


File 2 (cs-extension file)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using ExcelDna.Integration;
//using ExcelDna.Integration.XlCall;

namespace IntegratingWithVBA2
{
public class Class1
{
    [ExcelFunction(Description = "My multiplication XLL formula")]
    public static double Multiply([ExcelArgument(Description = "The first number")] double x, [ExcelArgument(Description = "The second number")] double y)
    {
        Object XlApp = ExcelDnaUtil.Application;
        Microsoft.Office.Interop.Excel.Application a = (Microsoft.Office.Interop.Excel.Application)XlApp;
        int coount = a.Workbooks.Count;
        object workbookname = ((Microsoft.Office.Interop.Excel.Worksheet)a.ActiveSheet).Name;
        return x * y;

    }
    private static object ReferenceToRange(ExcelReference xlRef)
    {
        long cntRef = 0;
        string strText;
        string strAddress;
        ExcelReference yy = ((xlRef.InnerReferences).ToArray())[0];
        strAddress = (string)ExcelDna.Integration.XlCall.Excel(ExcelDna.Integration.XlCall.xlfReftext, yy, true);


        for (cntRef = 1; cntRef <= xlRef.InnerReferences.Count - 1; cntRef++)
        {
            yy = ((xlRef.InnerReferences).ToArray())[cntRef];
            strText = (string)ExcelDna.Integration.XlCall.Excel(ExcelDna.Integration.XlCall.xlfReftext, yy, true);
            strAddress = strAddress + "," + strText.Substring(strText.LastIndexOf("!") + 2, 1 - (strText.LastIndexOf("!") + 2 + strText.Length));// +Mid(strText, strText.LastIndexOf("!") + 2);// +2 because IndexOf starts at 0 
        }
        return ((Microsoft.Office.Interop.Excel.Application)(ExcelDnaUtil.Application)).Range[strAddress];
    }
    [ExcelFunction(IsMacroType = true)]
    public static string GetAddress([ExcelArgument(AllowReference = true)] object arg)
    {
        object range;
        if (arg is ExcelReference)
        {
            range = ReferenceToRange((ExcelReference)arg);
            return ((Microsoft.Office.Interop.Excel.Range)range).Address[false, false];
        }
        else
        {

            return "!!! Not a sheet reference";
        }
    }

}

}


HOWEVER, when I attempt to move MyAddinObject Class from dna-extension file in its entirety to the cs-extension file (the classs under the IntegratingWithVBA2 namespace) while making sure to include a "using IntegratingWithVBA2;" line in the dna-extsnsion file (i.e., right beneath the using System.Runtime.InteropServices; line), I get the following error message upon trying to debug in Excel the newly compiled code:
There were errors when compiling project: 
c:\Documents and Settings\Local Settings\Temp\ff0dou5w.0.cs(8,7) : error CS0246: The type or namespace name 'IntegratingWithVBA2' could not be found (are you missing a using directive or an assembly reference?)
c:\Documents and Settings\Local Settings\Temp\ff0dou5w.0.cs(12,5) : error CS0246: The type or namespace name 'MyAddinObject' could not be found (are you missing a using directive or an assembly reference?)
As you can see the reference to the library file IntegratingWithVBA2.dll is in the dna-extension file already in the first successful compilation (before I attempt to isolate the add-in code by moving it to the cs-extension file). In other words, I did not see the need to include the ExternalLibrary tag with path attribute IntegratingWithVBA2.dll upon attempting to move the add-in code from the dna-extension file to the cs-extension file. That brings me to suspect that I did something wrong ?

I would appreciate any help.




Best Regards,

Avi
Coordinator
Sep 17, 2013 at 10:13 AM
Hi Avi,

You mail me the problematic project, and I'll try to have a look.

Regards,
Govert
Sep 17, 2013 at 2:05 PM
Hi Govert,

Sorry if my e-mail was too verbose -- In hindsight, I tend to think that I over-complicated things for lack of presenting the problem in a simpler way.


The code that I pasted above is based on your example taken from the following link that you posted : https://gist.github.com/govert/1079888


In connection to that example and my code above, what I really didwas modifying the onConnection method (from the link's code) with the aim of registering the Add-in project (so that it can be used from VBA Module) . With the existing code that I pasted above, the add-in object (MyAddinObject) is defined and declared IN the dna-extension file, but my objective is to have it reside in the project on a separate C# cs-extension file

In light of that -- and this is only an attempt to re-present the issue in a simpler way --perhaps you can point me to some possible existing sample code wherein it's possible to call on functions from the Excel worksheet AS WELL AS from VBA code ?


Once again thank you for your kind help!

Avi
Coordinator
Sep 20, 2013 at 5:54 AM
Edited Sep 20, 2013 at 5:55 AM
Hi Avi,

It's hard to tell from your description why it might not be working.

If you just want to call some of the UDFs defined in your Excel-DNA add-in from VBA, the easiest is to use

myResult = Application.Run("MyFunction", param1)

If you need to get hold of some object from the Excel-DNA add-in in your VBA, things get a bit more tricky, which is what the snippet was about. If you are trying to do that, I suggest you send me the problematic project (or a small sample that exhibits the same problems) via email. The errors you report seem to relate to how the references and different parts of the project are set up, and that not easy to follow from your description.

Cheers,
Govert
Sep 20, 2013 at 3:28 PM

Hi Govert,

Thank you so much for having taken the time to look into the matter.

I will use that temporary workaround that you specified (i.e., myResult = Application.Run("MyFunction", param1)) until I get to figure out where did I go wrong, given that referencing the library through the DNA file was supposed to make it possible to allow calling from VBA on functions which were written in the cs-extension file rather than the dna extension file (I am referring to the code you e-mailed me below)

If I don’t get to be successful, I will e-mail you a mini project, as you mentioned below, which will simulate the issue such that it would be possible for you to open and run it in VS 2010, thereby allowing you to pinpoint the issue.

Thank you for the great help

Avi

<DnaLibrary RuntimeVersion="v4.0" >
   <ExternalLibrary Path="MyAddIn.dll" />
</DnaLibrary>