RTD returning arrays

I'd like RTD to return an array of values from RefreshData(). This
article states that this CANNOT be done directly:
http://support.microsoft.com/kb/q286258/

Instead they recommend returning the array as a string with this format
{1, 2, 3, 4; 10, 20, 30, 40; 100, 200, 300, 400}, where columns are
delimited by commas and rows by semicolons.

The string formatted version of the array can then be passed to
Evaluate() which will parse the string and populate the cells.

However there is a limitation that restricts the string length passed
to Evaluate to 256 chars
(http://www.decisionmodels.com/calcsecretsh.htm). This seems pretty
severe as it keeps the array size uselessly small.

Is there a better way to return an array from RTD and have it displayed
in the spreadsheet?

Build your own function that parses the string and builds an array.

--
Regards,
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> I'd like RTD to return an array of values from RefreshData(). This
> article states that this CANNOT be done directly:
> http://support.microsoft.com/kb/q286258/
>
> Instead they recommend returning the array as a string with this format
> {1, 2, 3, 4; 10, 20, 30, 40; 100, 200, 300, 400}, where columns are
> delimited by commas and rows by semicolons.
>
> The string formatted version of the array can then be passed to
> Evaluate() which will parse the string and populate the cells.
>
> However there is a limitation that restricts the string length passed
> to Evaluate to 256 chars
> (http://www.decisionmodels.com/calcsecretsh.htm). This seems pretty
> severe as it keeps the array size uselessly small.
>
> Is there a better way to return an array from RTD and have it displayed
> in the spreadsheet?
>

not heavily tested..

Sub ff()
Dim v
v = StringToArray("a,b,c,d,e;aa,bb,cc,dd,ee;-1,-0.1,0,0.1,1")
ActiveCell.Resize(UBound(v, 1) + 1, UBound(v, 2) + 1) = v
End Sub

Function StringToArray(s) As Variant
Const sdR$ = ";", sdC$ = ","
Dim ar, ac
Dim r&, c&

ar = Split(s, sdR)
ac = Split(ar(0), sdC)

ReDim res(0 to UBound(ar), 0 to UBound(ac))
For r = 0 To UBound(ar)
ac = Split(ar(r), sdC)
For c = 0 To UBound(ac)
res(r, c) = ac(c)
Next
Next
StringToArray = res
End Function




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

> Build your own function that parses the string and builds an array.

Hi Tom,

I had considered that, but was hoping there was an off-the-shelf
solution.

I don't understand why, since the array returned from RefreshData()
holds System.Objects, I can't pass back a System.Array object (which is
derived from System.Object)?

If that works, I should be able to do something like:

Array array =
(Array)thisApplication.WorksheetFunction.RTD("MyRtd","",.....)

Thanks,
Steve

Steve,

you definitely cannot return an array directly, but you can use an "array
handle" (e.g. the topic ID) and a worksheet function to "explode" the
handle.

Jens.

--
http://ManagedXLL.net/
Replace MSDN with my first name when replying to my email address!



<[email protected]> wrote in message
news:[email protected]...
> I'd like RTD to return an array of values from RefreshData(). This
> article states that this CANNOT be done directly:
> http://support.microsoft.com/kb/q286258/
>
> Instead they recommend returning the array as a string with this format
> {1, 2, 3, 4; 10, 20, 30, 40; 100, 200, 300, 400}, where columns are
> delimited by commas and rows by semicolons.
>
> The string formatted version of the array can then be passed to
> Evaluate() which will parse the string and populate the cells.
>
> However there is a limitation that restricts the string length passed
> to Evaluate to 256 chars
> (http://www.decisionmodels.com/calcsecretsh.htm). This seems pretty
> severe as it keeps the array size uselessly small.
>
> Is there a better way to return an array from RTD and have it displayed
> in the spreadsheet?
>