RTD returning arrays

[srplatt]

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?

 

[Tom Ogilvy]

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

 

[keepITcool]

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 :

 

[srplatt]

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

 

[Jens Thiel]

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.