Is it possible to return a matrix of values from UDF?

[Some One]

Is it possible to return a matrix of values from UDF?

[Govert van Drimmelen]

Example of an array function:

public static class ArrayTest
{
public static object[,] TestArray()
{
object[,] result = new object[2, 2];

result[0,0] = "Hello";
result[0,1] = "World";
result[1,0] = 2.3;
result[1,1] = 3.4;

return result;
}
}
This gets entered into as Excel as an "Array Formula" by selecting a
2x2 range of cells, entering the formula "=TestArray()", then pressing
Ctrl+Shift+Enter, so that the formula displays as "{=TestArray()}".

[Some One]

Thanks. Is it possible to make this function auto-updating? (RTD)

[Govert van Drimmelen]

It's a pleasure.

Yes, but there are some complications.

Excel behaves a bit strange when an RTD function wrapper returns an
array. It will call the wrapper function once for the array overall,
and once for every individual cell in the array. There is no good
workaround, apart from structuring your function so that these
multiple calls are expected and fast.

More here:

http://groups.google.com/group/exceldna/browse_thread/thread/4ea571957ac09d8b

http://groups.google.com/group/exceldna/browse_thread/thread/3b3155508c8ce81

-Govert

[Tjaart van Wijck]

I've had some success with storing the matrix in memory and only returning a reference. With a second function I dereference this into a matrix.

So you might have an RTD that retrieves a matrix and stores it in an in-memory dictionary against a unique string ID. But instead of returning the matrix, the RTD just returns the ID. Use a second worksheet function to pull the matrix from memory passing the unique string ID as a parameter.

On 15 July 2011 14:15, Ronan Mouquet <ronan....@gmail.com> wrote:

Don't do that !  :)

There is a bug in RTD server (from Microsoft) : when the RTD server notify Excel that it must reevaluate the function, it will call the function one for each cell.
So in your case, one notification will end with 4 calls !

Ron

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.