When a UDF returns an Array - Use XLL or Automation Add-in?

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

When a UDF returns an Array - Use XLL or Automation Add-in?
 
ideaFocus




Posts: 63
 Joined: 2013-07-19
We are developing an Excel Com Add-in with a variety of features that require access to the Excel Object Model.

The Add-in will include a number of UDFs that will require access to the Excel Object Model.

The first UDF we are developing will return a text value to the Cell it is called from and generate an array below and to the right of that Cell as Numbers and Text.

In the ADX documentation "Which UDF to Choose" it mentions "Automation add-ins are suitable if your UDF deals a lot with the Excel object model" which appears to indicate that it is best for our purpose.

However, the article also mentions "Automation add-ins cannot modify arbitrary cells" so that we would have to use a work around to populate the Array Cells if we decide to use Com Excel Add-in Module.

XLL Add-ins can create Arbitrary Cells and can access "low-level Excel features through the ADXXLLModule.CallWorksheetFunction method". No mention is made as to whether the XLL Add-in relates to the Excel Object Model.

Which approach (XLL Add-in or Automation Add-in) is better for our UDF requirement?
Posted 21 Jul, 2013 00:29:11 Top
Andrei Smolin


Add-in Express team


Posts: 18816
 Joined: 2006-05-11
Hello,

I would recommend that you use this approach:
- when your UDF is called it prepares data to fill the array and passes them to the COM add-in by calling a public method defined in the add-in module
- the method stores the data, calls the ADXAddinModule.SendMessage method, passes a unique window messages and quits; for Excel, this indicates that it can finish the UDF call and release the corresponding context
- when the ADXAddinModule.OnSendMessage event occurs, Excel is already switched to the COM add-in context and this allows you to fill the array without any risk

As you can see this approach can be used with both UDF types.

Wait a Little
Some things aren't possible to do right at the moment; say, you can't close the inspector of an Outlook item in the Send event of that item. A widespread approach is to use a timer. Add-in Express provides a way to do this by using the <SendMessage> method and <OnSendMessage> event: when you call <SendMessage>, it posts the Windows message that you specified in the methods' parameters and the execution continues. When Windows delivers this message to an internal Add-in Express window, the <OnSendMessage> event is raised. Make sure that you filter incoming messages; there will be quite a lot of them. The <OnSendMessage> event always occurs on the main thread.
VB.NET:
Private Const WM_USER As Integer = &H400
Private Const MYMESSAGE As Integer = WM_USER + 1000

Private Sub AdxKeyboardShortcut1_Action(sender As Object) _
Handles AdxKeyboardShortcut1.Action
Me.SendMessage(MYMESSAGE)
End Sub

Private Sub AddinModule_OnSendMessage _
(sender As Object, e As AddinExpress.MSO.ADXSendMessageEventArgs) _
Handles MyBase.OnSendMessage

If e.Message = MYMESSAGE Then
' ...
End If
End Sub

C#:
private const int WM_USER = 0x0400;
private const int MYMESSAGE = WM_USER + 1000;
private void adxKeyboardShortcut1_Action(object sender) {
this.SendMessage(MYMESSAGE);
}

private void AddinModule_OnSendMessage(object sender,
AddinExpress.MSO.ADXSendMessageEventArgs e) {
if (e.Message == MYMESSAGE) {
//...
}
}



Andrei Smolin
Add-in Express Team Leader
Posted 22 Jul, 2013 04:19:18 Top
ideaFocus




Posts: 63
 Joined: 2013-07-19
Thanks for the advice.

In the approach, you said, call a public method for COM add-in, for calling a method, we have to have access to the COM add-in class object into automation class, how do you suggest that? Do we need to share object between those two classes? Or it will be static?
Posted 22 Jul, 2013 06:20:36 Top
Andrei Smolin


Add-in Express team


Posts: 18816
 Joined: 2006-05-11
Please check http://www.add-in-express.com/creating-addins-blog/2010/03/24/addin-xll-rtd-one-assembly/.


Andrei Smolin
Add-in Express Team Leader
Posted 22 Jul, 2013 07:46:56 Top
ideaFocus




Posts: 63
 Joined: 2013-07-19

Please check HowTo: Create a COM add-in, XLL UDF and RTD server in one assembly.


I have downloaded the example and checked it. It's only showing to do it with RDT, XLL UDFs and COM Add in. We are using automation udf. I try to follow same approach but I am not able to get the Com Add in object the way its getting in Common Data. 


public object MyFunc2(object range)
{
	comaddin_xll_rtd_cs.AddinModule addinModule = AddinExpress.MSO.ADXAddinModule.CurrentInstance as comaddin_xll_rtd_cs.AddinModule;

	if (addinModule != null)
	{
		addinModule.SendMessage(0);
	}
	else
	{
		Log.WriteLine("addinModule is null");
	}

	return null;
}


It always prints "addinMdoule is null". Can you please tell me what I am missing?
Posted 22 Jul, 2013 22:46:08 Top
Andrei Smolin


Add-in Express team


Posts: 18816
 Joined: 2006-05-11
Oh, I see. In this case, you can access your COM add-in via the following code path:

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

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


Andrei Smolin
Add-in Express Team Leader
Posted 23 Jul, 2013 02:03:55 Top
ideaFocus




Posts: 63
 Joined: 2013-07-19
Thanks! It works.
Posted 23 Jul, 2013 03:00:29 Top