VB6 Excel COM Addin -> VB.NET ADX Com Addin: Class Instancing MultiUse Problems

Add-in Express™ Support Service
That's what is more important than anything else

VB6 Excel COM Addin -> VB.NET ADX Com Addin: Class Instancing MultiUse Problems
 
John West




Posts: 37
Joined: 2010-02-15
I'm converting a VB6 based Excel COM Addin to VB.NET 2008 / ADX 2009.

So far, everything has gone well but I have the following problem when it comes to calling functions in a class within my COM Addin from Excel VBA.

With the VB6 addin, I can do this in Excel VBA:

Dim oMyAddin as MyAddin.SomeClass
Set oMyAddin = New MyAddin.SomeClass

oMyAddin.SomeSub "Some Data"


The SomeClass is a class module (SomeClass.cls file) in the VB6 project and it's Instancing is set to MultiUse

When the above code runs, the SomeClass is created and SomeSub is called. SomeSub is able to see all of the global (module) variables from the instance of the addin that Excel loaded at startup.

Now when I try this with the ADX/VB.Net version of the addin, the SomeClass is created and SomeSub is called BUT SomeSub is NOT able to see any of the global (module) variables from the original instance of the addin that Excel loaded at startup. Instead it seems to have created a completely separate instance of my Addin but this time it isn't calling sub New and AddinModule_AddinInitialize in the ADX module.

The VB.Net code for the class is simply:

Class SomeClass

Public Sub SomeSub (ByVal strParam as String)

   '  global variables have not been initialized

   MsgBox (strParam)

End Sub
End Class


How do I make this class behave the same way it did with the VB6 based COM Addin?

Thanks.
Posted 19 Mar, 2010 17:07:07 Top
Andrei Smolin


Add-in Express team


Posts: 16956
Joined: 2006-05-11
Hello John,

You need to use the following static (Shared in VB.NET) method:
AddinExpress.MSO.ADXAddinModule.CurrentInstance.

Then you cast the result returned by this method to the type of the add-in module in your project (say, to MyAddin1.AddinModule) and access any members of the add-in module.

I assume you have at least two classes in your project: 1) the add-in class (it descends from ADXAddinModule) and 2) SomeClass (it uses class #1). In VB6, Instancing=MultiUse means SomeClass is creatable outside of your ActiveX DLL project.

But it doesn't make sense to create an instance of that class outside of a COM add-in because it will not be able to access a properly initialized add-in module. I suggest that you access this class in another way: access the add-in class first and then call a method returning an instance of SomeClass.

You do this in the following way:

ExcelApp.COMAddins.Item(strMyComAddinProgId).Object.GetInstanceOfSomeClass.

1. strMyComAddinProgId - see the ProgId attribute of your add-in module.
2. GetInstanceOfSomeClass is called via late binding (see System.Type.InvokeMember in MSDN or search our forums for samples)

Regards from Belarus (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 22 Mar, 2010 08:05:37 Top
John West




Posts: 37
Joined: 2010-02-15
Thanks. I have done just as you suggested and it now works as I had hoped.

Your work is most helpful and impressive Andrei!
Posted 23 Mar, 2010 21:37:35 Top