What's wrong with my Shared Add-in Constructor?

Question

Good morning, fellows developers:

I'm currently trying to fix several performance issues of an Excel Shared Add-in inherited from a previous developer, basically I'm trying to find how the add-in stuff works internally in Excel, meaning I had searched the net for information an my understanding is:

1. In the registry the LoadBehaviour should be set to 3
2. The Excel Workbook during the open event should previously load all add-ins referenced in the VBA Project
3. Once the document is open my add-in should be available to be used by the VBA code.

Now I add Log4Net to the add-in and curiously enough I had seen the following behaviour

During the Open Event in the Excel Workbook there is a global variable

Public myAddin As Object

Set myAddin = New TradingAddin.TradingAddin

Thus the Contructor of the C# Class is called

after a couple of seconds the constructor is called one more time and all the IDTExtensibility2 methods OnConnection, OnDisconnection, etc.. are called as expected.

I thought that once Excel loads the Add-in it should be available to the VBE Code and i could write something like

Set myAddin = Application.COMAddins.Item("Trading").Object

But it returns Nothing and calling the Constructor of the Class twice destroy any state saved inside the C# Object that should be available in memory during the life of the Excel Workbook.

Update:

The Platform is Visual Studio 2005 Team Edition and the Target Application is Excel 2003 and the Add-in is a Shared Add-in. I'm not using VSTO.

The actual code I was trying to invoke in VBA is

Set addIn = Application.COMAddIns.Item("K2Trading.K2Trading").Connect

Set managedObject3 = addIn.Object <--- This value that I thought was an Instance of the Add-in is equal to Nothing (NULL)

Set addIn = Application.COMAddIns("K2Trading.K2Trading").Connect

Also changing the LoadBehaviour in the registry to 3 from 2 loads the Add-in the first time correctly firing all Extensibility Event OnConnection, OnDisconecction and the Object Class Constructor, now I need to find a way for the invocation part from VBA of the Add-in, meaning how to connect the Add-in instance to a reference in VBA and from there call all the methods exposed through the Interface of the COM Object????

Also I double checked using ProcMon that the Add-in was found and loaded by Excel as per this link (very useful) http://blogs.msdn.com/dvespa/archive/2008/10/15/troubleshooting-outlook-com-addins-using-procmon.aspx .

Any ideas our perhaps pointing into the right direction?

How could I find out how many instances of the COM Object are loaded? or put into another words could be possible to have a single instance of a COM Object?

TIA, Pedro

To Mike:

I tried your solution but I'v been facing Unspecified error (Exception from HRESULT: 0x80004005 (E_FAIL)) when executing this code

public void OnConnection(object application, Extensibility.ext_ConnectMode connectMode, object addInInst, ref System.Array custom) { object missing = System.Reflection.Missing.Value;

        try
        {

            if (debug)
            {
                log.Debug("Connection Mode :" + connectMode);
            }

            this.excelApp = (Excel.Application)application;
            this.addInInstance = addInInst;

            Office.COMAddIn addIn = this.excelApp.COMAddIns.Item(ref addInInst);


            //addIn.Object = this;

            // We connect our Instance of the Add-in to the Arrya of COMAddins of Excel
            VBA.Interaction.CallByName(addIn, "Object", VBA.CallType.Let, this);

            ^
            |
            The Exception occurs here.

You will noticed this looks a little different from you posted a while ago

public void OnConnection( object application, Extensibility.ext_ConnectMode connectMode, object addInInst, ref System.Array custom) { // Direct call fails b/c ".Object" is a late-bound call: // // addInInst.Object = this;

        // Reflection fails I believe b/c .Object is a 'let' assigned 
        // property for reference type, which is very unusual even for
        // COM (although legal) and is a foreign concept to .NET. Use 
        // of the right BindingFlags here *might* work, but I'm not sure:
        //
        //    PropertyInfo propInfo;
        //    propInfo = addInInst.GetType().GetProperty("Object");
        //    propInfo.SetValue(addInInst, this, null);


        // This works!:
        VBA.Interaction.CallByName(addInInst, "Object", VBA.CallType.Let, this);
}

Because addInInst is not pass as a Office.COMAddin but a instance of my class, so trying to assign to the Object Property is incorrect since it doesn't exists in that class

Also I'm curious about how Excel loads the Add-ins, what I mean by that is based on my observation when I just loaded Excel the OnConnection method didn't execute right away, but until I hit the =AvgCost() function ??

Any ideas?

Answer 1

Hi Pedro,

How could I find out how many instances of the COM Object are loaded? or put into another words could be possible to have a single instance of a COM Object?

Do you mean COM add-in here? You cannot have the same COM add-in loaded into Excel more than once.

Now I need to find a way for the invocation part from VBA of the Add-in, meaning how to connect the Add-in instance to a reference in VBA and fro there call all the methods exposed throught the Interface of the COM Object????

VBA is all late-bound, it is not pre-compiled into a DLL like a VB6 DLL or a .NET assembly. Therefore, all of your VBA calls will have to be late bound as well. Easiest is to call Excel.Appliction.Run("NameOfYourVbaMacro") to call any macro stored within a standard module within your workbook. (You can access your workbook by name using Excel.Application.Workbooks["NameOfYourAddin.xla"]. You don't need to treat it as an add-in specifically, other than when you force it to load.) You can also use reflection code to access the workbook and worksheet members, if you have code behind either the ThisWorkbook class module or any of the Worksheet class modules.

I thought that once Excel loads the Add-in it should be available to the VBE Code and i could write something like

Set myAddin = Application.COMAddins.Item("Trading").Object

So, if I understand correctly, you want not only to have your C# managed COM add-in call VBA code (as discussed above), but now you also want to have VBA code to be able to call our C# managed COM add-in? I think this is a ton of complexity that probably needs some re-thinking... but it can be done.

Exposing your managed COM add-in to a COM caller via the ComAddin.Object property is tricky when done from C#, but doable. See the following discussion:

Calling Managed Add-In method from Automation client.

I hope this helps get you going...

Mike

Edit: Response to Pedro's Reply

Pedro,

You will noticed this looks alittle different from you posted a while ago...

Yes, your code is different and is why it does not work!

At a minimum, your last line looks incorrect:

VBA.Interaction.CallByName(addIn, "Object", VBA.CallType.Let, this);

Instead, your code should be passing in your class to the 'addInInst':

VBA.Interaction.CallByName(addInInst, "Object", VBA.CallType.Let, this);

And I'm not sure what you are trying to do by this line:

Office.COMAddIn addIn = this.excelApp.COMAddIns.Item(ref addInInst);

That line looks like it should throw an exception. I am very surprised that it does not. But code similar to that -- where you pass in the progId for the COM add-in you wish to access -- is typically used by an external caller that wishes to access your COM Add-in via the .Object property. This is not code that should be used from within the add-in itself.

Because addInInst is not pass as a Office.COMAddin but a instance of my class, so trying to assign to the Object Property is incorrect since it doesn't exists in that class

I don't understand what you are trying to do here. I don't know if you can pass in any other object other than the instance of the class that implements IDTExtensibility2. At a minimum, whatever class you do pass back must be be a COM visible class by using the correct class and interface attributes. But I think that it is far easier to stick to the standard practice of passing in the class that implements IDTExtensibility2 from within the OnConnection method. That is, pass in your 'this' object reference.

If you want to try fancy things that go beyond the standard approach, that's ok, but I would get a simple example working first. Try to replicate the code that I show in the example Calling Managed Add-In method from Automation client. Once you have that working, you can try to move onto more sophisticated operations. But once you have the simple version working, I think you'll find that this is all that you need.

I hope this helps Pedro,

Mike