Ashim Mishra
Guest
|
Hi Team,
I would like to know if there are any approach, to get two dimensional array from XLL formula.
for e.g:
GetValue(p1, p2, p3)
will return values which can be populated in columns and rows ? |
|
Andrei Smolin
Add-in Express team
Posts: 19138
Joined: 2006-05-11
|
|
Ashim Mishra
Guest
|
|
Andrei Smolin
Add-in Express team
Posts: 19138
Joined: 2006-05-11
|
Hello Ashim,
I would need to study the mechanism described on that page. At the moment I suggest that you use the machinery I already described in your other topic; see below.
An XLL can't modify an arbitrary cell. To bypass this, your XLL function can use SendMessage() to send a unique message and data to the hidden window that Add-in Express creates and manages. When Windows delivers the message to the window, the add-in module (COM add-in) generates the OnSendMessage() event. Since the event occurs outside of the context of an XLL call, you can freely use the Excel object model to modify any cell(s) you need.
The blog at https://www.add-in-express.com/creating-addins-blog/2011/10/11/invoke-excel-addin-xll/ demonstrates how to use this approach. It misses passing data however. To do this, you create a globally available collection (or dictionary) that stores data, when you add a data portion to the collection you store the index and then pass the index to the SendMessage method. That method accepts 1) message Id (an interger > 1024) , and 2 IntPtr parameters called wParam and lParam: you cast the index (an integer) to IntPtr when you call SendMessage and cast the data the other direction when OnSendMessage occurs.
That it, when OnSendMessage occurs, you retrieve the index, use the index to obtain the data portion and process it.
Andrei Smolin
Add-in Express Team Leader |
|
Ashim Mishra
Guest
|
|
Andrei Smolin
Add-in Express team
Posts: 19138
Joined: 2006-05-11
|
Hello Ashim,
That link describes a change in Excel. The XLL API isn't changed. In section "Returning values when your Excel UDF Is called from an array formula" at https://www.add-in-express.com/docs/net-excel-udf-tips.php#array-formula, you can find functions which returns arrays and which work in the newest Excel versions without pressing Ctrl+Shift+Enter (=CSE).
Andrei Smolin
Add-in Express Team Leader |
|