Accessing public shared members between add-ins

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

Accessing public shared members between add-ins
Accessing public shared members between add-ins 
Jordan Blaydes




Posts: 37
Joined: 2009-02-08
a few years ago, I had a topic on this site about this issue:
http://www.add-in-express.com/forum/read.php?a&FID=5&TID=5600

I have been using the ExcelApp.Evaluate("SomeUDF") to solve AppDomain issue with some success since 2009.

However, I am having problems getting this to work with following scenario:

I have an Automation Server and COM Add-in in one DLL. I can use the ExcelApp.Evaluate trick to allow the Automation Add-in to shared variables between two add-ins.

Now I have introduced an XLL in a separate project, separate DLL. The ExcelApp,Evaluate trick does not work, as the OnInitialize event fires before I have a chance to load the XLL into the same AppDomain.

so I wanted to try to use the following code below. However, my XLL does not recognize the AddinModule type


'Doesn't see my AddinModule

Dim MyHostApp As AddinModule = AddinExpress.MSO.ADXAddinModule.CurrentInstance

MyHostApp.COMAddins.Item({the ProgID of your add- in}).Object.MyPublicPropertyOrMethod(MyParameter)

It makes sense since there is no AddinModule class in my XLLModule. So I'm not sure what to do know. Can I use this approach?

What about this one?

Dim MyHostApp As Excel.Application
MyHostApp=DirectCast(System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"), Excel.Application)

Back in 2009, your staff said "In VB.NET (if you have Options Strict Off), you can use either of the above but you need to replace HostApp with a correct reference to the Application object of the host application of your add-in."

I am so lost on this one, and I would appreciate any help.

Thanks,
Jordan
Posted 20 Jul, 2011 17:31:22 Top
Eugene Astafiev


Guest


Hi Jordan,

It seems the AddinModule class is defined in another assembly. You can use the base class (ADXAddinModule) instead. For example, you can cast an instance of the ExcelApp.COMAddins.Item(strMyComAddinProgId).Object to the ADXAddinModule class and then call its methods and properties using the late binding technology available in .net framework (see the http://www.add-in-express.com/docs/net-tips.php#accessing section in the online documentation).
Posted 21 Jul, 2011 08:02:25 Top
Jordan Blaydes




Posts: 37
Joined: 2009-02-08
Hi Eugene,

I am not able to get this to work in the XLL. I am having problems with scope. I need pass the information from a method in my COMAddin to a function in my Friend XLL Container Class, so when try to call this function in my XLL Module from my XLL Container, I cannot use it without declaring an instance of the XLL Module class, which I am trying to avoid, because I want to use the current instance. If I try to move the function inside the XLL Container class, then my function is not able to use ExcelApp because it is declared in the XLL Module class

Here is my function in my XLL Module Class:


    Public Function ReturnMyAdminPath() As Object
        Dim MyHostApp As AddinExpress.MSO.ADXAddinModule
        MyHostApp = ExcelApp.COMAddIns.Item("MYPROGID").Object

        Dim MyReturn As Object
        MyReturn = MyHostApp.GetType().InvokeMember("MY_COM_ADD_IN_METHOD", Reflection.BindingFlags.InvokeMethod, Nothing, MyHostApp, Nothing)

        Return MyReturn
    End Function


My target method in the the COMAddIn has no arguments, am I calling InvokeMember correctly? I don't know what to pass for the last two arguments of InvokeMember, is MyHostApp and Nothing correct?

Here is how I am trying to call it from a UDF in my Friend XLL Container Class:

Dim adminpath As Object
adminpath = ReturnMyAdminPath()


I cannot use this in my OnInitialize method of XLL because I think it fires before the COMAddIn is loaded.

Thanks for any help on this,
Jordan
Posted 27 Jul, 2011 20:24:56 Top
Eugene Astafiev


Guest


Hi Jordan,

Sorry, but I don't quite understand you. Could you please send a sample project or solution which can reproduce the issue you faced to the support e-mail address for testing on our side? Then I will be able to provide you with any workaround.
Posted 28 Jul, 2011 05:38:44 Top
Jordan Blaydes




Posts: 37
Joined: 2009-02-08
Hi Eugene,

Thank you for your reply. I will send the project to the support e-mail address. I created a new XLL Project and have pasted the relevant code into it, it is also copied below:

I'm sorry if I am not understanding the workaround yet.




Imports System.Runtime.InteropServices
Imports System.ComponentModel
Imports System.Windows.Forms
Imports AddinExpress.MSO
Imports Excel = Microsoft.Office.Interop.Excel

'Add-in Express XLL Add-in Module
<ComVisible(True)> _
Public Class XLLModule
    Inherits AddinExpress.MSO.ADXXLLModule
 
#Region " Component Designer generated code. "
    'Required by designer
    Private components As System.ComponentModel.IContainer
 
    'Required by designer - do not modify
    'the following method
    Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container()
        '
        'XLLModule
        '
        Me.AddinName = "SampleAddIn"
    End Sub
 
#End Region
 
#Region " Add-in Express automatic code "
 
    'Required by Add-in Express - do not modify
    'the methods within this region
 
    Public Overrides Function GetContainer() As System.ComponentModel.IContainer
        If components Is Nothing Then
            components = New System.ComponentModel.Container
        End If
        GetContainer = components
    End Function
 
    <ComRegisterFunctionAttribute()> _
    Public Shared Sub RegisterXLL(ByVal t As Type)
        AddinExpress.MSO.ADXXLLModule.RegisterXLLInternal(t)
    End Sub
 
    <ComUnregisterFunctionAttribute()> _
    Public Shared Sub UnregisterXLL(ByVal t As Type)
        AddinExpress.MSO.ADXXLLModule.UnregisterXLLInternal(t)
    End Sub
 
#End Region
 
    Public Sub New()
        MyBase.New()

        Application.EnableVisualStyles()
        'This call is required by the Component Designer
        InitializeComponent()

        'Please add any initialization code to the OnInitialize event handler

    End Sub

    Public Shared Shadows ReadOnly Property CurrentInstance() As XLLModule
        Get
            Return CType(AddinExpress.MSO.ADXXLLModule.CurrentInstance, XLLModule)
        End Get
    End Property

    Public ReadOnly Property ExcelApp() As Excel._Application
        Get
            Return CType(HostApplication, Excel._Application)
        End Get
    End Property

#Region " Define your UDFs in this section "

    'The container for user-defined functions (UDFs). Every UDF is a public static (Public Shared in VB.NET) method that returns a value of any base type: string, double, integer.
    Friend Class XLLContainer

        'Required by Add-in Express. Please do not modify this method.
        Friend Shared ReadOnly Property _Module() As SampleAddIn.XLLModule
            Get
                Return CType(AddinExpress.MSO.ADXXLLModule. _
                    CurrentInstance, SampleAddIn.XLLModule)
            End Get
        End Property


#Region "XLL Functions "



        'This does not compile because ReturnMyAdminPath is not a shared instance?
        'I don't want to create a new instance of the XLL Module Class, do I?
        Public Shared Function InformationFromMyCOMAddIn(ByVal arg As Object) As String
            Dim adminpath As Object
            adminpath = ReturnMyAdminPath()
        End Function

        'This is not a shared UDF function but still does not compile.
        Public Function InformationFromMyCOMAddIn_NotShared(ByVal arg As Object) As String
            Dim adminpath As Object
            adminpath = ReturnMyAdminPath()
        End Function

        'Just grasping at straws here.
        Public Shared Function InformationFromMyCOMAddIn_Call_As_Sub_Instead(ByVal arg As Object) As String
            ReturnMyAdminPath_As_Sub()
        End Function

#End Region

    End Class

    'I think this is right way to write this function to get the information from the COM Add In Method.
    Public Function ReturnMyAdminPath() As Object
        Dim MyHostApp As AddinExpress.MSO.ADXAddinModule
        MyHostApp = ExcelApp.COMAddIns.Item("MYPROGID").Object

        Dim MyReturn As Object

        'I'm not sure I have the right syntax here, but it does compile because late binding is used.
        'method MY_COM_ADD_IN_METHOD takes no arguments, so is the last two arguments correct? MyHostApp? Nothing?
        MyReturn = MyHostApp.GetType().InvokeMember("MY_COM_ADD_IN_METHOD", Reflection.BindingFlags.InvokeMethod, Nothing, MyHostApp, Nothing)

        Return MyReturn

    End Function

    'This does not compile because ExcelApp is not a shared instance.
    'I don't want to create a new instance of the XLL Module Class, do I?
    Public Shared Function ReturnMyAdminPath_Shared_In_XLL_Module() As Object
        Dim MyHostApp As AddinExpress.MSO.ADXAddinModule
        MyHostApp = ExcelApp.COMAddIns.Item("MYPROGID").Object

        Dim MyReturn As Object

        'I'm not sure I have the right syntax here, but it does compile because late binding is used.
        'method MY_COM_ADD_IN_METHOD takes no arguments, so is the last two arguments correct? MyHostApp? Nothing?
        MyReturn = MyHostApp.GetType().InvokeMember("MY_COM_ADD_IN_METHOD", Reflection.BindingFlags.InvokeMethod, Nothing, MyHostApp, Nothing)

        Return MyReturn

    End Function

    Public Sub ReturnMyAdminPath_As_Sub()
        Dim MyHostApp As AddinExpress.MSO.ADXAddinModule
        MyHostApp = ExcelApp.COMAddIns.Item("MYPROGID").Object

        Dim MyReturn As Object

        'I'm not sure I have the right syntax here, but it does compile because late binding is used.
        'Method MY_COM_ADD_IN_METHOD takes no arguments, so is the last two arguments correct? MyHostApp? Nothing?
        MyReturn = MyHostApp.GetType().InvokeMember("MY_COM_ADD_IN_METHOD", Reflection.BindingFlags.InvokeMethod, Nothing, MyHostApp, Nothing)

    End Sub

#End Region

    Private Sub XLLModule_OnInitialize(sender As Object, e As System.EventArgs) Handles Me.OnInitialize

        'This compiles, but does not work because my COMAddIn 
        'has not been initialized yet when this event is executed. 
        'If this is the only way to use this function then it will not work.

        Dim adminpath As Object
        adminpath = ReturnMyAdminPath()

    End Sub
End Class



Posted 28 Jul, 2011 16:26:17 Top
Eugene Astafiev


Guest


Hi Jordan,

I have just answered you via e-mail. Please check out your Inbox.
Posted 29 Jul, 2011 11:57:21 Top