|
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
|
|
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
|
|
Posted 23 Jul, 2013 03:00:29
|
|
Top
|
|