Excel External Adapter

Contents

Excel Adapter Prerequisites
Installation Considerations
Introduction
Configuration File
Using and Running the Excel Adapter
Creating a StreamBase-Ready Spreadsheet
Publishing to StreamBase from Excel
Setting the Excel Security Level
Changing the Excel Refresh Interval

The StreamBase Adapter for Microsoft Excel lets you use a StreamBase application in combination with an Excel spreadsheet to process tuple data in real time, and display the data dynamically. The Excel Adapter is bidirectional, and can both receive data from a StreamBase application or publish data to a running StreamBase application.

The Excel Adapter installation includes a sample, described in Microsoft Excel Adapter Sample.

Excel Adapter Prerequisites

The StreamBase Adapter for Microsoft Excel has the following requirements:

  • The Excel Adapter kit requires Microsoft Excel 2002 (Excel XP) or later.

  • The PC running Excel must have a working network connection to the instance of StreamBase Server hosting the StreamBase application from which your spreadsheet reads data (or to which it publishes data).

  • To open some of this Adapter's sample Excel files, your Excel macro security settings must be set to medium or lower. Follow the steps in Setting the Excel Security Level.

  • Only one Excel Adapter can be registered for use on one PC at the same time. If you have two or more StreamBase installations, each with its own Excel Adapter, you might need to re-install the Excel Adapter for the StreamBase version you want to use. Re-installing re-registers the Excel Adapter's DLL as the currently active one for the PC.

Installation Considerations

The StreamBase Adapter for Microsoft Excel has a separate installation kit from StreamBase itself, and is available for supported Microsoft Windows platforms only. Install the Excel Adapter kit as follows:

  • The Excel Adapter kit must be installed on any PC that will run an Excel spreadsheet that reads from or writes to a StreamBase Server instance. (The Excel Adapter kit's installer registers the Excel Adapter's DLL so that Excel can locate the Adapter when it is called in a spreadsheet cell.)

  • For production PCs, the Excel Adapter kit is a standalone package that does not require either StreamBase Studio or StreamBase Server to be installed on the same machine.

  • For development PCs, and for running the Excel Adapter kit's sample, you can install StreamBase and the Excel Adapter kit on the same PC.

The Excel Adapter kit's installer follows the same installation directory standards as StreamBase. Thus, even in a standalone installation with only the Excel Adapter kit installed, look for the Excel Adapter's files in: 

C:\Program Files\StreamBase Systems\StreamBase.n.m

where n.m are the major and minor StreamBase release numbers.

Introduction

The StreamBase Adapter for Excel is an implementation of a Microsoft Real Time Data (RTD) server, as described on Microsoft's site. The adapter can be used in two modes, where the data flows in opposite directions:

  • Dequeuing tuples from one or more StreamBase application output streams and passing them to Excel.

  • Publishing tuples from Excel to a StreamBase application.

The preceding diagram illustrates how a StreamBase application, running in a StreamBase Server instance, receives a stream of tuples from a data feed, processes the data, and emits tuples on one or more output streams. The RTD server, listening for tuples from StreamBase Server, receives the data and passes it to Excel. In Excel, an RTD() function determines how each tuple field of interest is displayed in the spreadsheet. The diagram also shows the publishing option, where tuples are enqueued from an Excel spreadsheet into a StreamBase application for processing there.

In Excel, you can use a combination of two techniques to organize the data on your spreadsheet:

  • Historical topics are associated with the relative order of tuples in the output stream. For example, one row can be set to display the most recent output tuple; the next row might display the previously output tuple, and so on. In a typical historical data display where rows are set up to display tuple numbers sequentially, tuples appear to scroll up or down the spreadsheet.

  • Filtered topics are associated with specific values appearing in tuple fields. For example, a row might display only tuples that contain a certain ticker symbol. At run time, filtered data is updated in static rows.

The adapter includes a configuration file that can be modified to control environment and performance parameters.

Configuration File

The configuration file for the Excel Adapter is an XML file named sb-microsoft-excel.sbconf. It is installed in the same directory as the adapter's DLL, which is typically in streambase-install-dir/bin. The adapter's configuration file specifies the connection URI for StreamBase Server, logging parameters, and the number of tuples buffered per stream by the adapter.

The configuration file is divided into two sections. The first section describes parameters for connecting to StreamBase. The second section contains the logging and adapter buffering parameters.

StreamBase Section

The streambase element of the configuration file contains one required uri element and zero or more optional uriref elements.

  • uri specifies the default URI of the StreamBase Server instance the adapter connects to..

  • Each uriref element specifies an alternate StreamBase Server URI. Zero or more uriref elements can be present in the configuration file, depending on the number of StreamBase Servers from which tuples will be dequeued.

    Alternate StreamBase URIs can then be referenced on a per-cell basis in a spreadsheet using the URIREF tag, as described below in RTD() Function Reference. If a URIREF tag contains an empty value, the default URI is used.

    At run time, the adapter attempts to connect to all StreamBase Servers represented by uriref elements. If you no longer use a particular server, remove its uriref element from this section.

Adapter Section

The adapter element contains three parameters:

  • buffer-size specifies the number of tuples the adapter buffers per stream and unique FILTER expression, awaiting retrieval by Excel. (See RTD() Function Reference for a description of the FILTER tag.) When the adapter receives data from StreamBase Server, it saves it in a buffer and notifies Excel. When Excel is ready for the data, it calls the adapter to retrieve it. If Excel is busy performing a calculation or waiting on a modal dialog box, it may take a while to respond to the adapter.

    The adapter continues to buffer tuples for the stream until the buffer-size threshold is reached. At this point, the adapter begins to discard old tuples to make room for new ones. The larger the value of this parameter, the less likely it is that the adapter will discard data. However, no value can guarantee the adapter will never encounter this overflow condition. Note that a larger buffer size increases the load on the system and the resources used by the adapter.

  • The second and third parameters, log-file and log-level, specify the name of the log file and the logging verboseness level. By default, the log file has the same name as the adapter file with an extension of .log. The log file is written in the same directory as the adapter's DLL file, unless the parameter contains an absolute path starting with a drive letter and colon.

Using and Running the Excel Adapter

The Excel Adapter's RTD server is loaded automatically by Excel whenever a spreadsheet cell contains an RTD() function that references the adapter's program ID (StreamBase.RTD). StreamBase provides no mechanism to run the adapter independently of Excel.

The following is the suggested workflow for setting up an Excel spreadsheet that works with the StreamBase Excel Adapter. These steps presume you will read from a StreamBase application and display the output in Excel, but the workflow steps are similar if you want to publish from Excel to a StreamBase application.

  1. Examine your StreamBase application and decide which output streams, and which fields in those streams, to include in the spreadsheet. Prepare a list of the stream names and field names that you want to display.

    (If you don't yet have a working StreamBase application, stop now to develop and test your application first.)

  2. Start an instance of StreamBase Server running your application. You can do this from StreamBase Studio or from a StreamBase Command Prompt.

  3. Start streaming input data through your application. This can be live data, if your application is set up to connect to a live data feed, or it can be a simulated data feed played back with the sbfeedsim command.

  4. In Excel, open a new spreadsheet or a spreadsheet that you want to augment with StreamBase data. Start entering RTD() formulas in cells as described in the next section.

  5. To get the first RTD() function to show StreamBase data, you may have to save the spreadsheet or switch away from Excel to another application, then back. Once data in one cell is correctly resolved from the RTD server, RTD() formulas in other cells will also resolve correctly.

  6. Continue to develop your spreadsheet. You can style any cell containing an RTD() formula like any other Excel cell, and you can use the resolved data in a cell in the formulas of other cells.

Creating a StreamBase-Ready Spreadsheet

This section describes how to set up an Excel spreadsheet to receive data from a StreamBase application.

Use the Excel built-in real time data function RTD() to communicate with a running StreamBase application. The simplest use of RTD() is with four comma-separated arguments, where the first argument is always "StreamBase.RTD", and the second argument is always blank: 

RTD("StreamBase.RTD", , STREAM:stream-name, FIELD:field-name)

For example: 

RTD("StreamBase.RTD", , STREAM:All_Transactions, FIELD:price)

Specify the target output stream's name in the third argument. In the fourth argument, specify the name of a field in the specified output stream.

As described in the previous section, if your StreamBase application is running and receiving an input data stream, the RTD() formula resolves to the specified value, and you see values filling the cell with the RTD() formula.

If you see #N/A in a cell that contains an RTD() formula, it can mean one of several things:

  • StreamBase Server is not running, or is not running on the host and port specified in the adapter configuration file described in Configuration File above.

  • The URIREF value you entered in a cell formula is not present in the adapter configuration file.

  • You entered an invalid STREAM or FIELD name in a cell.

  • No tuple matching the specified filter criteria has been received from StreamBase Server.

StreamBase Server Connections

If you open a spreadsheet with RTD() functions when StreamBase Server is not running, the spreadsheet opens normally, but each cell with an RTD() function attempts to connect to the server periodically. Until connections are successful, all spreadsheet cells referencing that server display the value #N/A. When a server connection is eventually established, the RTD adapter begins to dequeue tuples from StreamBase Server, at which point cells referencing the server are written with values retrieved from these tuples.

If the adapter loses its connection to StreamBase Server, all the spreadsheet cells referencing that server receive the value #N/A until a connection with the server can be reestablished.

RTD() Function Reference

Enter RTD() functions in Excel spreadsheet cells using the following syntax: 

=RTD("StreamBase.RTD", , [,"URIREF:[uri_reference]"] [,"META:meta_tag"], 
"STREAM:stream_name", "FIELD:field_name" [,"TUPLENUM:tuple_number"] 
[,"FILTER:filter_expression"] [,"LOCALFILTER: fieldname==filtervalue"]

  • StreamBase.RTD is the Program ID of the RTD server provided by the StreamBase Excel Adapter. This value is registered with Windows when the adapter is installed.

  • The second parameter is left blank, because the Excel Adapter always runs on the same system as Excel.

  • URIREF is optional. If present, it causes Excel to dequeue tuples from the StreamBase Server instance whose URI is specified by the corresponding uriref element in the adapter configuration file. Otherwise, the default StreamBase URI is used, as specified by the adapter configuration file's uri element. If a URIREF tag is used with an empty value ("URIREF:"), the default StreamBase URI is used for that spreadsheet cell.

  • META is optional. If present, it retrieves StreamBase metadata. For details, see Retrieving StreamBase Metadata.

  • STREAM is required and specifies the name of the output stream to which the tuple is enqueued.

  • FIELD is required and names the schema field whose value you want displayed in the current spreadsheet cell.

  • TUPLENUM and FILTER are optional. If present, they are used to select a specific tuple in one of the following ways:

    Historical

    Use "TUPLENUM:" followed by a 1- to 3-digit number (or an Excel expression that evaluates to the number) to retrieve historical information from a tuple field. A value of 1 (the default) selects the most recent tuple dequeued by the adapter. A value of 2 selects the next most recent tuple, and so on. The value provided should not exceed the buffer-size value in the adapter section of the adapter's configuration file.

    Filtered

    Use "FILTER:" followed by a filter expression to select tuples with fields containing specific values. The filter expression is an arbitrary expression referencing zero or more tuple fields that conform to the StreamBase expression language.

    Locally Filtered

    Use "LOCALFILTER: fieldname==filtervalue" to select tuples with string fields containing specific values.

    Notes

    Limit the number of FILTERs used in your spreadsheet, because each unique filter value consumes resources on the StreamBase Server. LOCALFILTER is the preferred mechanism to route tuples to specific spreadsheet cells based on the values in a single tuple string field. Therefore, use FILTER to limit the volume of data retrieved from StreamBase Server, and use LOCALFILTER when the entire stream is being consumed and subsequently dispersed within the spreadsheet.

    The PIN:field=value syntax provided in previous versions of the Excel Adapter is preserved for backward compatibility. However, StreamBase Systems recommends replacing instances of PIN with FILTER or LOCALFILTER.

For general information about the RTD function, see Excel Help.

The following figure shows the rtd.xls Excel spreadsheet provided in the sample StreamBase application installed with this Adapter.

In the preceding figure, notice that the spreadsheet is organized into several grids. The first group, Ticker Stream, displays ten rows of tuples in the historical mode, where the top row represents the most recent output tuple. A new output tuple replaces this one in the top position, and the current tuple moves one row down. In this way, tuples scroll down as data is received, disappearing beyond the bottom row. The historical grid can show as many tuples at one time as the number of rows you create.

The second block of five rows, Latest Values, contains filtered fields. Filtered data is updated in place instead of moving in the grid. For example, the AAPL row displays only fields for tuples whose symbol is AAPL, and it is updated each time an AAPL tuple is emitted on the output stream. Thus, the grid shows data from only the most recent AAPL tuple.

The third and fourth groups combine the filtering and historical modes. Large Volumes displays the last five trades exceeding 9000 shares, while Microsoft Buy History displays the last five buy orders for Microsoft stock.

The RTD function examples in the section below elaborate on the Excel functions demonstrated in this figure.

RTD() Function Examples

Here are several examples of RTD() functions for use with the Excel Adapter:

  • =RTD("StreamBase.RTD",,"STREAM:All_Transactions","FIELD:time")

    The cell containing this function displays the time field from the most recent tuple dequeued from the All_Transactions output stream.

    Remember that tuple fields with the StreamBase timestamp data type display in Excel as a floating point number by default. You must use Format > Cells in Excel to apply one of the Time formats to see the timestamp data displayed correctly.

  • =RTD("StreamBase.RTD",,"STREAM:All_Transactions","FIELD:price",CONCATENATE("TUPLENUM:", ROW()-2))

    The cell containing this function displays the price field from a tuple dequeued from the All_Transactions output stream. The tuple number to be displayed is calculated from the position of the row in which the function occurs. The Excel formula ROW()-2, when placed in row 4, resolves to 2. Thus, the last argument for this formula when placed in row 4 is TUPLENUM: 2, which references the next most recent tuple received.

    This example also demonstrates the advantage of using an Excel formula. In some cases, instead of hard-coding a tuple value, you can define rows by copying them from the row above, and the same Excel expression is used to order the tuples displayed in each row.

  • =RTD("StreamBase.RTD",,"STREAM:All_Transactions", "FIELD:price", "LOCALFILTER:symbol==""" & 
    $A17 & """")

    The cell containing this function displays the price field from the most recent tuple, dequeued from the All_Transactions output stream, whose symbol matches the value in column A of row 17.

    In the figure above, this example RTD() function is used in cell D17 to retrieve the most recent DELL stock price.

    As in the previous RTD() example, using an Excel formula whenever possible, instead of directly coding tuple values or grid coordinates, can make it easier to edit and maintain your spreadsheet. In this case, if you copy the cell containing this formula (or the entire row) to a new row below it, Excel automatically updates the A17 row value to the new row number.

  • =RTD("StreamBase.RTD",,"STREAM:All_Transactions", "FIELD:time", CONCATENATE("TUPLENUM:", ROW()-33), 
    "FILTER:symbol == ""MSFT"" && buy == true")

    Cell B33 in the figure above contains this function and displays the time field from the second most recent tuple, dequeued from the All_Transactions output stream, whose symbol matches MSFT and whose buy value is true.

  • =RTD("StreamBase.RTD",,"URIREF:server1", "STREAM:All_Transactions", "FIELD:time"

    This function causes tuples to be dequeued from an alternate StreamBase Server instance. The adapter configuration file must contain a corresponding uriref element in the streambase section, as shown in the following example: 

    <uriref name="server1" value="sb://remotehost:12345"/>

  • =IF((RTD("StreamBase.RTD",,"STREAM:All_Transactions","FIELD:buy")=TRUE),"Buy","Sell")

    You can use RTD() functions as part of larger cell formulas. In the sample provided with the Excel Adapter, the buy field is returned as a Boolean value. Instead of displaying the Boolean TRUE or FALSE, the formula above evaluates the specified RTD() function, and displays "Buy" if it evaluates to true or "Sell" if it evaluates to false.

Retrieving StreamBase Metadata

In addition to retrieving real-time data from StreamBase streams, the Excel Adapter can be used to retrieve metadata about the URIREF tags, StreamBase Server status, stream names, and field names, types, and sizes. The META tag is used in spreadsheet cells to retrieve StreamBase metadata. The following table lists the sub-tags that can be used with the META tag.

Metadata Tag Example Usage Returns...
URIREF_COUNT =RTD("StreamBase.RTD",,"META:URIREF_COUNT") The number of uriref elements found in the adapter configuration file, plus one to represent the default StreamBase URI.
URIREF_NAME:n =RTD("StreamBase.RTD",,"META:URIREF_NAME:1") The name of the corresponding uriref in the adapter configuration file. Use a value 1 to retrieve the name of the default StreamBase uriref tag, a value of 2 to retrieve the name of the first uriref element, and so forth.
URI:n =RTD("StreamBase.RTD",,"URIREF:server1","META:URI") The StreamBase URI associated with the specified URIREF tag.
SERVER_STATUS =RTD("StreamBase.RTD",,"URIREF:server1", "META:SERVER_STATUS") The running status (true or false) of the server specified by the URIREF tag.
INPUT_STREAM_COUNT =RTD("StreamBase.RTD",,"URIREF:server1", "META:INPUT_STREAM_COUNT") The number of input streams present on the server specified by the URIREF tag.
OUTPUT_STREAM_COUNT =RTD("StreamBase.RTD",,"URIREF:server1", "META:OUTPUT_STREAM_COUNT") The number of output streams present on the server specified by the URIREF tag.
STREAM_COUNT =RTD("StreamBase.RTD",,"URIREF:server1", "META:STREAM_COUNT") The total number of streams present on the server specified by the URIREF tag.
INPUT_STREAM_NAME:n =RTD("StreamBase.RTD",,"URIREF:server1", "META:STREAM_NAME:1") The name of the nth input stream on the server specified by the URIREF tag.
OUTPUT_STREAM_NAME:n =RTD("StreamBase.RTD",,"URIREF:server1", "META:STREAM_NAME:1") The name of the nth output stream on the server specified by the URIREF tag.
STREAM_NAME:n =RTD("StreamBase.RTD",,"URIREF:server1", "META:STREAM_NAME:1") The name of the nth stream on the server specified by the URIREF tag.
FIELD_COUNT =RTD("StreamBase.RTD",,"URIREF:server1", "STREAM:All_Transactions","META:FIELD_COUNT") The number of fields within the stream specified by the STREAM tag on the server specified by the URIREF tag.
FIELD_NAME:n =RTD("StreamBase.RTD",,"URIREF:server1", "STREAM:All_Transactions","META:FIELD_NAME:1") The name of the nth field within the stream specified by the STREAM tag on the server specified by the URIREF tag.
FIELD_TYPE:n =RTD("StreamBase.RTD",,"URIREF:server1", "STREAM:All_Transactions","META:FIELD_TYPE:1") The type of the nth field within the stream specified by the STREAM tag on the server specified by the URIREF tag.
FIELD_SIZE:n =RTD("StreamBase.RTD",,"URIREF:server1", "STREAM:All_Transactions","META:FIELD_SIZE:1") The size of the nth field within the stream specified by the STREAM tag on the server specified by the URIREF tag.

Publishing to StreamBase from Excel

The Excel Adapter supports publishing as well as retrieving data. In a publishing spreadsheet, data flows from Excel to the running StreamBase application. Excel publishing requires several lines of Visual Basic for Applications (VBA) code that run in response to Excel events, connecting to the Excel adapter's StreamBase.RTDPub interface and invoking its Enqueue method. The rtd-publish.xls sample spreadsheet that ships with the Excel Adapter contains this VBA code and illustrates how to publish tuples to StreamBase.

Publish Method Syntax

The adapter's Enqueue method takes two parameters. The first parameter is an array of variants containing the field names and values of the tuple to be published along with information that enables the adapter to route the tuple to the appropriate StreamBase Server and stream. Each array element contains a tag and value delimited with a colon (such as STREAM:InputStream1). All but the FIELD tag can appear at most once in the array. The valid tags are listed below:

  • URIREF:uri_reference is optional. If present, uri_reference refers to the URI value of the corresponding uriref element in the adapter configuration file.

  • STREAM:stream_name is required and contains the name of the stream to which the tuple is enqueued.

  • FIELD:field_name=field_value can appear zero or more times in the array. Each entry contains a field name and the corresponding value to be enqueued to that field. Tuple fields for which no FIELD tag is supplied are written with null values.

The second parameter to the Enqueue method is an OUT parameter of type Long that contains the status of the enqueue operation as a COM HRESULT. For example, the status will be S_OK if the operation was successful, or E_INVALIDARG if the format of the variants in the first parameter are not in the expected format described above.

Enabling Publishing in Excel

Follow the steps below to publish tuples from an Excel spreadsheet to a running StreamBase application:

  1. Create a StreamBase application with an input stream. The VBA code below assumes the stream of interest is named InputStream1 and has a 20-character string field named TextField1. Start your application.

  2. Open an Excel spreadsheet that will be used to publish tuples to your StreamBase application.

  3. Decide on an event source for publishing tuples to StreamBase. The code below assumes a button click serves this purpose. Thus, add a Command Button to the spreadsheet and name it CommandButton1. (For instructions on adding controls such as buttons to a spreadsheet, refer to Excel's Add an ActiveX control help topic.)

  4. Right-click on the tab named Sheet1 at the bottom of the spreadsheet and select View Code. This brings up the Visual Basic IDE.

  5. Add a reference to the adapter's type library by clicking Tools → References and enabling the sb-microsoft-excel 1.0 Type Library checkbox. If sb-microsoft-excel 1.0 Type Library does not appear in the list, browse for it at C:\Program Files\StreamBase Systems\StreamBase.n.m\bin\sb-microsoft-excel.dll.

  6. Add code that responds to a click on CommandButton1 by publishing a tuple to StreamBase. For example: 

    Public appObj As StreamBase.RTDPub
Private Sub CommandButton1_Click()
    Dim status As Long
    Dim Tuple(1) As Variant
    Tuple(0) = "STREAM:InputStream1"
    Tuple(1) = "FIELD:TextField1=Hello World!"
    If (appObj Is Nothing) Then
        Set appObj = New StreamBase.RTDPub
    End If
    appObj.enqueue Tuple, status
End Sub

  7. Return to the spreadsheet and click CommandButton1 to enqueue a tuple to StreamBase.

  8. Confirm that tuples are being published to your StreamBase application by running sbc dequeue from a StreamBase Command Prompt window.

Setting the Excel Security Level

Excel might display a dialog warning that macros in the spreadsheet are not digitally signed. This can occur if you are using Microsoft Excel 2002 (Excel XP) or if you open the sample spreadsheets other than rtd.xls using any supported version of Excel. To resolve the problem, lower your Excel security settings as follows:

  1. Click OK to dismiss the error message.

  2. In Excel, select Tools → Macro → Security.

  3. In the Security dialog's Security Level tab, change the security level to Medium, and click OK.

  4. In Excel, select File → Save to save your settings. Exit and restart Excel.

Now each time you open the spreadsheet, Excel displays a different dialog with another security warning about macros. Click Enable Macros to dismiss the warning.

Changing the Excel Refresh Interval

The dynamic display of data using the StreamBase Adapter for Excel is influenced by a number of factors. These include the rate at which tuples flow in and out of the StreamBase Server and the performance of your RTD server.

Another variable is how frequently Excel accepts data from the RTD server, referred to as the Excel refresh interval in this document. If your Excel spreadsheet data is being updated too slowly, you can try changing the refresh interval to speed up the frequency of refreshing, as described in this topic.

Note

The information in this topic is adapted from the Microsoft MSDN Library, which documents many aspects of configuring and tuning RTD servers.

When Does Excel Check for Updates?

Recall that tuples flow from the StreamBase Server output stream through the RTD server before being consumed by the Excel spreadsheet. Excel is not continually open to updates; it accepts them only at set intervals (the refresh interval). If the RTD server has an update and Excel is busy (such as when you are editing a cell), it might wait longer than the set refresh interval. If the refresh interval has passed, Excel then retrieves the data changed since its last refresh.

How Do I Configure the Refresh Interval In Excel?

The refresh interval is set by default to two seconds. To modify the refresh interval, use either the Excel Visual Basic editor or the Registry:

  • If the refresh interval is set to -1, this is considered manual mode, and Excel checks for updates only when Excel.Application.RTD.RefreshData is called.

  • If the refresh interval is set to zero, Excel checks for updates every chance it gets.

  • If the refresh interval is set to something greater than zero, Excel waits at least that number of milliseconds between checks for updates.

Caution

If updates come in so frequently that Excel is continuously updating values and doing calculations, Excel might end up in a state where it never gives the user a chance to do anything, effectively getting in a hung state. If this happens, set the Excel refresh interval higher.

To change the refresh interval through the Excel Visual Basic editor:

  1. In Excel, start the Visual Basic Editor (by pressing ALT+F11 or clicking Visual Basic Editor from the Macro menu (Tools menu)).

  2. In the Immediate window (press Ctrl-G or click Immediate Window on the View menu), type this code: Application.RTD.ThrottleInterval = 1000

  3. Make sure your cursor is on the line that you just typed, and then press Enter.

  4. To verify that it is set correctly, type this line of if code in the Immediate window:

    ? Application.RTD.ThrottleInterval

  5. If you put your cursor at the end of this line and press Enter, it should display 1000. In this way you know that your throttle interval is set correctly.

To change the refresh interval through the registry, set the following registry key. It is a DWORD and is in milliseconds: 

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel
        \Options\RTDThrottleInterval

Note

The version number in this key corresponds to the version of Excel you are running. The 10.0 value in the example above is for Excel 2003. Use 10.0 for Excel 2002 or 12.0 for Excel 2007.

Back to Top ^