Function with range input and array output

Mar 9, 2016 at 2:03 AM

I have a ExcelFunction defined in c# that takes a range of cells as input (keys) and looks up values for the keys against a table and returns a object[,]. All works fine except when I convert the formula to a array formula in excel.

Lets say I have 5 keys in A1:A5. On cell B1 I enter the formula as =GetValues(A1:A5). Now this function is called once with all keys passed in. Now if I select B1:B5 and press F2 and then ctrl+shift+enter, the function GetValues is getting called 5 times each for all the keys. I would have thought the function would be called only once? Or is there a setting I am missing here? This is very urgent as it is slowing down the sheet for me.

Mar 9, 2016 at 5:07 AM
To throw more light to this, this function (GetValues) hooks up to a RTD server. It apparently just registers one call with the RTD, but when converted to an array formula it seems to re-invoke the formula about 5 times (taking the above example). Is this is problem with excel itself? I cant really figure out a solution to this. If I try to use the ArrayResizer instead, then it goes into an infinite calculation loop.

Any solution/workaround will be much appreciated.

Mar 9, 2016 at 8:21 AM

Yes - it is a problem with Excel - Arrays and RTD don't mix. The multiple calls to the function (one for each element in the array, and one overall) come from Excel when you have RTD in the formula.

I suggest splitting your function in two - the first comes from the RTD and returns a handle as a single value. The second function takes the handle, retrieves the data from an internal structure, and returns the array.

It's less convenient for your users, as they need an extra cell for the handle (you can't put the two formulas into a single cell etiher). But it works well.

See also!searchin/exceldna/array$20rtd/exceldna/xKPFw94qbiQ/piCzLoaxjhkJ and!searchin/exceldna/array$20rtd/exceldna/L2zC5YZiix4/I9bukLQHV8wJ