INFO: Excel RTD Function Cannot Return an Array (286258)

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, it is possible for a well-written RTD server to return a single value. This design does not compromise performance, because Excel only needs to make a single call to the RTD server to retrieve all the data from the RTD server.

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. You can then use a Visual Basic for Applications (VBA) function to parse the string so that the data can be returned to the worksheet as an array. Excel has a string format that it can interpret as an array by using the Evaluate method. 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 the following string as an array with 2 rows and 5 columns:
The following steps illustrate a technique that you can use for parsing a string of this type that is returned from an RTD Server into multiple cells.

1. Create the Visual Basic project for the RTD Server described in the following Knowledge Base article:

   285339 HOWTO: 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
   					

   with:

   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. Start a new workbook in Excel 2002 or Office Excel 2003 and 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 add the following code 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 formula

   =parseArrayData(RTD("ExcelRTD.RTDFunctions",,"AAA"))

   and press CTRL+SHIFT+ENTER to enter it as an array formula.
9. After the first update notification, each of the six cells contains a unique number. And, after the second update notification, each cell contains a unique character.