Excel RTD function cannot return an array

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.

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: Or, Excel can interpet the following string as an array with 2 rows and 5 columns: 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:

1. 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
2. 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) = "{1,2,3;10,20,30}"
   Else
      aUpdates(1, n) = "{""a"",""b"",""c"";""d"",""e"",""f""}"
   End If
   					

3. Build the RTD server from the Visual Basic project.
4. 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.
5. 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
   					

6. Close the Visual Basic for Applications Editor to return to the Excel workbook.
7. Select a two-row by three-column range of cells on the worksheet.
8. Type the following formula, and then press CTRL+SHIFT+ENTER to enter it as an array formula:
   =parseArrayData(RTD("ExcelRTD.RTDFunctions",,"AAA"))
9. 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.

For more information about RealTimeData servers, click the following article numbers to view the articles in the Microsoft Knowledge Base: