Select the product you need help with

Excel RTD function cannot return an array

Article ID: 286258 - View products that this article applies to.

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.

Back to the top | Give Feedback

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:

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

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
(http://support.microsoft.com/kb/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) = "{1,2,3;10,20,30}"
Else
   aUpdates(1, n) = "{""a"",""b"",""c"";""d"",""e"",""f""}"
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.
Type the following formula, and then press CTRL+SHIFT+ENTER to enter it as an array formula:
=parseArrayData(RTD("ExcelRTD.RTDFunctions",,"AAA"))
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.