Late Binding CreateObject for COM Functions

[Steve]

Hi,

I want to understand how to apply late binding for COM functions in the below link case.

I don't know how to do after creating object.

Set func = CreateObject("ExcelExampleComAddIn.ComAddIn")

How can I use " ExampleClassLibraryCOMFunctions " ?

(I can use by early binding, but  I want to know late binding case.)

https://medium.com/@efrem.sternbach/excel-dna-or-why-are-you-still-using-vba-a76f565884ff

// COMIntegration

using ExcelDna.ComInterop;
using ExcelDna.Integration;
using ExcelDna.Integration.Extensibility;
using ExcelDna.IntelliSense;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows;

namespace ExampleClassLibrary
{
    [ComVisible(true)]
    [Guid("A3F91E06-8C06-4C7E-A87C-6C966CB796DE")]
    [ProgId("ExcelExampleComAddIn.ComAddIn")]
    public class ExcelExampleComAddIn : ExcelComAddIn
    {
        public override void OnConnection(object Application,
                                          ext_ConnectMode ConnectMode,
                                          object AddInInst,
                                          ref Array custom)
        {
            try
            {
                dynamic addIn = AddInInst;
                addIn.Object = new ExampleClassLibraryCOMFunctions();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex);
            }
        }
    }

    [ComVisible(false)]
    internal class ExcelAddin : IExcelAddIn
    {

        private ExcelExampleComAddIn com_addin;

        public void AutoOpen()
        {
            try
            {
                com_addin = new ExcelExampleComAddIn();
                ExcelComAddInHelper.LoadComAddIn(com_addin);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error loading COM AddIn: " + ex);
            }

            ComServer.DllRegisterServer();
            IntelliSenseServer.Install();
        }

        public void AutoClose()
        {
            ComServer.DllUnregisterServer();
            IntelliSenseServer.Uninstall();
        }
    }
}

// COMFunctions

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;

namespace ExampleClassLibrary
{
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class DataPair
    {
        public string Label { get; set; }
        public int LabelId { get; set; }
    }

    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class ExampleClassLibraryCOMFunctions
    {
        public DataPair GetDataPair(string label, int label_id)
        {
            return new DataPair() { Label = label, LabelId = label_id };
        }

        public DateTime ThirtyDaysAgo()
        {
            return DateTime.Today - TimeSpan.FromDays(30);
        }
    }
}

[Govert van Drimmelen]

--------------------------------------------------

Excel-DNA is now registered on GitHub Sponsors.

You can add Excel-DNA support with easy billing through a corporate GitHub account.

--------------------------------------------------

 

Hi Steve,

 

If you just want to access some classes in your Excel-DNA add-in from VBA, then you can do it much more simply without making the extra COM Excel add-in.

For this case, you can work through the example here:

Samples/ComServer at master · Excel-DNA/Samples (github.com)

 

Basically you don’t need any of the COM add-in stuff.

 

If, for some other reason, you really want the extra COM add-in involved, you need to get to it from VBA like this:

 

     Set func = Application.COMAddIns(1).Object

     Set result = func.GetDataPair("asd", 42)

     Debug.Print result.Label

 

So you have to find the right COM add-in in the COMAddIns collection, and then the “.Object” property that you set in the add-in can be read and used here.

 

Let me know if you need more help on this.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/4bd6ee4a-731e-4157-982c-998815f24853n%40googlegroups.com.

[Steve]

Hi Govert,

Thanks for your prompt reply.

Set func = Application.COMAddIns("my_addin").Object

works well.

Now I add another classes (MyClass2, MyClass3, ,,,) here, and I want to run SetData method of MyClass2.

When I apply late binding by the above method and run VBA, I got error of "invalid procedure call or argument".

However, if I apply early binding, I don't have any issue.

So, I'm not sure how come this error.

Would you please advise ?

// COMIntegration

{
            try
            {
                dynamic addIn = AddInInst;
                addIn.Object = new ExampleClassLibraryCOMFunctions();

               // Add new class, MyClass2, MyClass3, ,,,,

                addIn.Object = new MyClass2();

                addIn.Object = new MyClass3();

            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex);
            }
        }

2021年9月14日火曜日 16:53:21 UTC+9 Govert van Drimmelen:

[Govert van Drimmelen]

The COMAddIn object only has a single ‘Object’ property, so that would not give a directly way to pass multiple objects to VBA.

I suggest you take the other approach of using the COM classes directly, as in the example I linked to.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/be40c25c-9fa5-4020-a33a-234c6731e6aen%40googlegroups.com.

[Steve]

Hi Govert,

Noted. Thank you !

2021年9月14日火曜日 18:30:30 UTC+9 Govert van Drimmelen: