AppDomain Excel VBA

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

AppDomain Excel VBA
Accessing shared variable when creating instance of module in Excel vba 
ewessely




Posts: 55
 Joined: 2019-01-31
Hi,
in my AddIn I'm using a ribbon with some buttons to take control over an object doing communication with other services.
This object must be a singleton.
It provides functionality used also in the RDT server and also exposes UDFs in an XLLModule - both works fine :-)

My problem is now to expose functions (usning that singelton object) and events the user can use in Excel VBA code in his worksheet.
I tried this in an ADXExcelAddinModule as well as in an ADXXLLModule.
The problem is that whenever I create an instance of one of these modules in Excel vba code it runs in the DefaultDomain instead of the AppDomain although they are initialized in the AppDomain when the addin loads. So I've no access to my singelton object.
I've read I think really all of your formum entries about the Appdomain and accessing public members from another add-in, but I can't get it to work.
Also your sample of creating COM addin, XLL UDF and RTD server in one assembly does not help :-(

I'm trying to convert my addin from excel.dna in which the new instance created in excel vba code was loaded into the AppDomain which does not produce any problems then.

Do you have any suggestions / samples how to access my singleton object created when the addin is loaded from module instanciated in Excel VBA code? This would be the last step to get rid off the excel.dna.

VS2017 C# ADX 9.4.4644


Many thanks in advance
ew
Posted 15 Oct, 2019 04:18:31 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Hello ew,

ewessely writes:
whenever I create an instance of one of these modules in Excel vba code it runs in the DefaultDomain


Here's how I tested using an XLL module function. In a sample XLL add-in project I've created this function: 

public static string MyFunc4(object arg)
{
    return AppDomain.CurrentDomain.FriendlyName;
}


I've registered the XLL add-in, started Excel, created this VBA macro in a new workbook: 

Sub dfdfgdf()
Dim s As String
s = Application.Evaluate("=MyFunc4()")
Stop
End Sub


When I run the macro and it stops, the string variable contains the path to the bin Debug folder of my XLL add-in project, not "Default AppDomain" or such.

ewessely writes:
It provides functionality used also in the RDT server and also exposes UDFs in an XLLModule


Are these modules in the same project?


Andrei Smolin
Add-in Express Team Leader
Posted 16 Oct, 2019 03:04:44 Top
ewessely




Posts: 55
 Joined: 2019-01-31
Hello Andrei,
many thanks for your soon reply!
Maybe my explanation of my problem is a little bit confusing...
What I finally need working in an Excel makro or module would be (simplified): 


Public WithEvents x As MyModuleInAddIn 'Events are absolutly required

Sub Init()
x = New MyModuleInAddIn  'must be in AppDomain and not in DefaultDomain
Debug.Print x.myFunction
End Sub


Maybe this clearifies my problem...
In excel.dna this was working. But I absolutly want to get rid of that and use the cool features of ADX :-)

Many thanks for your help!
Erich
ew
Posted 16 Oct, 2019 09:30:22 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Hello Erich,

Consider using this construct in a VBA macro: 

Application.COMAddins.Item({put the value of the ProgId attribute of your add-in module such as "MyAddin1.AddinModule"}).Object.myFunction()


This approach works if myFunction is located on the add-in module (of the ADXAddinModule type) and is declared public. If however you declare myFunction on the XLL module (in the XLLContainer class; create a new XLL module and find this class in the module), you can use it via Application.Evaluate("=MyFunction()"); see also https://www.add-in-express.com/creating-addins-blog/2011/10/03/invoke-excel-udf-programmatically/.

Is this what you are looking for?


Andrei Smolin
Add-in Express Team Leader
Posted 17 Oct, 2019 09:35:41 Top
ewessely




Posts: 55
 Joined: 2019-01-31
Hello Andrei,

thanks for the reply. But unfortunately this does not solve my problem.
In the excel vba I've to be able to subscribe to the events of the class from the addin.
I've absolutly to declare with the WithEvents 

Public WithEvents x As MyModuleInAddIn


The class in the addin has to use my "global" singleton in the addin and has to fire events.
Do you have any other idea to create the instance in Excel vba in the AppDomain?

br
Erich
ew
Posted 17 Oct, 2019 09:57:30 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Erich,

To get events from such a class, see this sample: http://temp.add-in-express.com/support/com-addin-with-events.zip. As to using a singleton, I'll check this tomorrow.


Andrei Smolin
Add-in Express Team Leader
Posted 17 Oct, 2019 10:06:57 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Hello Erich,

I've made the following changes in that project.

1. Added to the IAddInUtilities interface: 

Function GetSomeValue() As String


2. Implemented in the AddInUtilities class: 

    Public Function GetSomeValue() As String Implements IAddInUtilities.GetSomeValue
        Return AppDomain.CurrentDomain.FriendlyName
    End Function


3. Replaced the last lines in AddinModule.vb: 

    Private theAddInUtilities As AddInUtilities = Nothing

    Protected Overrides Function GetRemotingConfiguration(ByRef channel As System.Runtime.Remoting.Channels.IChannel, ByRef url As String) As Object
        Return GetSingleton()
    End Function

    Public ReadOnly Property GetSingleton() As AddInUtilities
        Get
            If theAddInUtilities Is Nothing Then
                theAddInUtilities = New AddInUtilities()
            End If
            Return theAddInUtilities
        End Get
    End Property


4. Modified ADXExcelTaskPane1.vb to use GetSingleton: 

ComAddinWithEvents.AddinModule.CurrentInstance.GetSingleton.FireEvent(Me, New System.EventArgs)


5. Added an XLLModule1.vb; it contains this function: 

        Public Shared Function GetDomainName() As String
            Return ComAddinWithEvents.AddinModule.CurrentInstance.GetSingleton.GetSomeValue()
        End Function


I start Excel, print '=GetDomainName()' in a cell and get 'E:\_Projects\_useful\Andrei Smolin\com-addin-with-events\com-addin-with-events\bin\Debug\'.

In the sample workbook supplied with that project, I've modified the UserForm1: I put a command button on it; the button invokes this code: 

Private Sub CommandButton2_Click()
    Dim result As String
    result = CStr(Application.Evaluate("=GetDomainName()"))
    MsgBox result
End Sub


The message box shows the same path.

What do you think?


Andrei Smolin
Add-in Express Team Leader
Posted 18 Oct, 2019 07:02:51 Top
ewessely




Posts: 55
 Joined: 2019-01-31
Hello Andrei,
you saved my weekend! Everything works as expected.
The - for me - missing piece of the puzzel was the "GetRemotingConfiguration" which allows me to expose my class.
But it's also not mentioned in the documentation ;-)

Many thanks for your outstanding and very responsive support!!!

Regards
Erich
ew
Posted 20 Oct, 2019 04:42:49 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
You are welcome!


Andrei Smolin
Add-in Express Team Leader
Posted 21 Oct, 2019 02:26:35 Top