RTD returning arrays

S

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?
 
K

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 :
 
S

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
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top