Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Excel RTD function cannot return an array

View products that this article applies to.

This article was previously published under Q286258

↑ Back to the top


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

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:
  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}"
       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:
  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.

↑ Back to the top


For more information about RealTimeData servers, click the following article numbers to view the articles in the Microsoft Knowledge Base:
286259� Security settings and Excel RealTimeData servers
285888� How to use an Excel RTD server with DCOM

↑ Back to the top

Keywords: KB286258, kbinfo

↑ Back to the top

Article Info
Article ID : 286258
Revision : 5
Created on : 5/14/2007
Published on : 5/14/2007
Exists online : False
Views : 488