udf that outputs to multiple cells

[Onn]

hi all

This question is about outputing multiple values from a UDF/RTD function.

Say I have need to do the following:

I provide a wrapper function to an RTD server. The wrapper function "MyFunc" receives a single argument x. The function needs to output the results to 3 adjacent cells. So if the user types =MyFunc(2) in A1, the result would be that cells A1, B1, C1 are filled with 2,4,6 

So my question is what is the approach for doing this? Do I use dynamic arrays for this? The reason I ask is that in my requirements, the output is not dynamic so to speak so was not sure.

Thanks for any help on this

Onn

[Govert van Drimmelen]

Hi Onn,

The 'Dynamic Arrays' feature that has been added to Excel 365 versions is exactly what you are looking for.

In these versions, if MyFunc(2) returns an array, the result will spill into the adjacent cells.

MyFunc can also be an RTD wrapper, and everything works fine.

In older versions of Excel, before the 'Dynamic Arrays' support, you are in for various clumsy workarounds.

If you know the size of the result region, you can pre-select the cells A1,B1,C1 and enter the formula as an 'array formula' using the Ctrl+Shift+Enter keystroke.

The result will then cover the selected cells and the formula will be displayed with braces as "={MyFunc(2)}".

That's more tricky for the user, but you get the array result into the sheet.

However, if the function wraps an RTD call, there are some problems with array formulas with a memory leak in Excel where the internal RTD calls are not done right.

There is also a hack where you automatically run a macro to expand the formula to an array formula of the right size, but that doesn't work with RTD at all.

Before the 'Dynamic Arrays' era, I normally suggested restructuring such a case into a single-cell RTD call that returns a handle of some sort to an internal data structure, and then an array formula which takes the handle and returns as a non-RTD function the result array. This is also a bit clumsy to use on the sheet, but at least can be made reliable.

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/6e275c54-e6fb-4cd6-9a5f-b352b258301dn%40googlegroups.com.

[onn]

hi Govert - thanks so much for your reply and the information. Understood on the evolution of things and the solution with the single-cell RTD using a handle makes sense.

In your description I can almost feel the battle scars in the days prior to Office 365's support for dynamic arrays. I can now clearly see how this new capability is elegant and opens up a simple solution to many use cases.

Really love the work you are doing, looks great

Thanks again for taking the time to reply

Onn

You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/oRGAWSQ6gB8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/CABCjBXNUbXNye%2B-bABSJOrFREqYnE-K0EkWYOUQkG4N6uANACQ%40mail.gmail.com.