Microsoft KB Archive/286258

= Excel RTD function cannot return an array =

Article ID: 286258

Article Last Modified on 5/14/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-



This article was previously published under Q286258



SUMMARY
Microsoft Excel 2002, Microsoft Office Excel 2003, and Microsoft Office Excel 2007 provide a new worksheet function that is named RTD. RTD allows you to call a Microsoft Component Object Model (COM) Automation server to retrieve data in real time. The RTD function cannot return an array. If the RTD function is used as an array formula, all of the cells in the array return #VALUE.



MORE INFORMATION
The inherent flexibility of a RealTimeData (RTD) server and the RTD function makes using them in array formulas unnecessary. The RTD function can take an arbitrary number of strings or topics to specify the data that is to be displayed in a cell. By using additional topic strings, a well-written RTD server can return a single value. Excel must only make a single call to the RTD server to retrieve all the data from the RTD server. Therefore, this design does not compromise performance.

With this in mind, you may sometimes need an RTD server to return an array of values for a single topic, and you may also need that array of values to be parsed into multiple cells. In this case, the RTD server can return a single string that contains all of the values. Then, you can use a Microsoft Visual Basic for Applications (VBA) function to parse the string so the data can be returned to the worksheet as an array. Excel can use Evaluate method to interpret its string format as an array. This string is enclosed in braces, columns are separated by commas, and rows are separated by semicolons. For example, Excel can interpret the following string as an array with 3 rows and 4 columns:

{1, 2, 3, 4; 10, 20, 30, 40; 100, 200, 300, 400}

Or, Excel can interpet the following string as an array with 2 rows and 5 columns:

{&quot;a&quot;, &quot;b&quot;, &quot;c&quot;, &quot;d&quot;, &quot;e&quot;; &quot;aa&quot;, &quot;bb&quot;, &quot;cc&quot;, &quot;dd&quot;, &quot;ee&quot;}

The following procedue illustrates a technique that you can use for parsing a string of this type that is returned from an RTD Server into multiple cells.

To do this, follow these steps:  Create the Visual Basic project for the RTD Server described in the following Knowledge Base article:

285339 How to create a RealTimeData server for Excel

  In the RTDFunctions class module of the project, replace the following line in the IRtdServer_RefreshData function: aUpdates(1, n) = oTopic.TopicValue Replace the code with the following line of code: Static bToggle As Boolean bToggle = Not (bToggle) If bToggle Then aUpdates(1, n) = &quot;{1,2,3;10,20,30}&quot; Else aUpdates(1, n) = &quot;{&quot;&quot;a&quot;&quot;,&quot;&quot;b&quot;&quot;,&quot;&quot;c&quot;&quot;;&quot;&quot;d&quot;&quot;,&quot;&quot;e&quot;&quot;,&quot;&quot;f&quot;&quot;}&quot; End If                    Build the RTD server from the Visual Basic project. In Excel 2002, in Office Excel 2003, or in Office Excel 2007, start a new workbook, and then press ALT+F11 to open the Visual Basic for Applications Editor.  Insert a new code module into the Visual Basic for Applications project, and then add the following code example to the code module. Function parseArrayData( arrayData as String ) as Variant parseArrayData = Evaluate( arrayData ) End Function  Close the Visual Basic for Applications Editor to return to the Excel workbook. Select a two-row by three-column range of cells on the worksheet.</li> Type the following formula, and then press CTRL+SHIFT+ENTER to enter it as an array formula:

=parseArrayData(RTD(&quot;ExcelRTD.RTDFunctions&quot;,,&quot;AAA&quot;))

</li> After the first update notification, each of the six cells contains a unique number. After the second update notification, each cell contains a unique character.</li></ol>

<div class="references_section">