Get array values using XLL

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

Get array values using XLL
 
Ashim Mishra




Posts: 43
 Joined: 2016-03-01
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 ?
Posted 18 Sep, 2020 07:36:04 Top
Andrei Smolin


Add-in Express team


Posts: 17361
 Joined: 2006-05-11
Hello Ashim,

Check Returning values when your Excel UDF is called from an array formula.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 18 Sep, 2020 07:48:05 Top
Ashim Mishra




Posts: 43
 Joined: 2016-03-01
Hi Andrei,


In this sample, user needs to select multiple cells to get the array values. But our use case is different.
We want to populate a 2-D array to different rows and columns.

for e.g i want to populate all the items that a company manufacture. So i'll call =GetItems(companyName)
this should populate the value in multiple rows. Can we do that with XLL ?

I want something similar to https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531?ui=en-US&rs=en-US&ad=US
Posted 19 Sep, 2020 05:17:13 Top
Andrei Smolin


Add-in Express team


Posts: 17361
 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.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 21 Sep, 2020 04:20:18 Top
Ashim Mishra




Posts: 43
 Joined: 2016-03-01
Hello Andrei,

We already have been doing that. We maintain a global collection to pass on the information.

But the link https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531?ui=en-US&rs=en-US&ad=US talks about populating the cells using the spilled array behavior.

Let me know if this is feasible with Addinexpress's xll implementation.
Posted 21 Sep, 2020 06:20:46 Top
Andrei Smolin


Add-in Express team


Posts: 17361
 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).

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 21 Sep, 2020 07:24:02 Top