Building Excel Real-Time Data Components in Visual Basic .NET
Microsoft® Excel 2002
Microsoft® Visual Basic® .NET Beta 2
Summary: This article walks you through building a real-time data server for Microsoft Excel 2002 using Visual Basic .NET Beta 2. (14 printed pages)
What is Excel Real-Time Data?
Why Use .NET for Excel RTD?
The Visual Basic .NET RTD Server
Developing the Real-Time Data Server
Keeping Track of RTD Data
Implementing the RTD Server
Testing and Debugging the RTD Server
The release of Microsoft® Office XP opened up a new world for developers. Smart tags have gotten all the press but they aren't the only cool new developer feature in Office. Microsoft Excel 2002, a member of the Office XP suite, introduces a way to get real-time data into your spreadsheets in an efficient, reliable, and robust manner—Excel real-time data (RTD) components. This real-time data can include cell references and can also be used in formulas, calculations, and charts.
But Office XP isn't the only thing new for developers coming from Microsoft this year. Also in the news is Microsoft® Visual Studio® .NET, a new development environment built on the Microsoft .NET initiative. Part of Visual Studio .NET is an awesome list of feature updates to the Visual Basic language, called Microsoft Visual Basic .NET.
In this article, we will walk through the process of building an RTD component in Visual Basic .NET Beta 2. Along the way, we'll look at how COM interoperability works in Visual Basic .NET and point out some of the differences between Visual Basic .NET and Visual Basic 6.0, as well as how some of the data types interact between COM and the .NET Framework class libraries. This article is intended for Visual Basic developers who are somewhat familiar with Excel 2002 RTD but are new to Visual Basic .NET. For common questions and answers regarding real-time data components, see Real-Time Data: Frequently Asked Questions. If you want to get a detailed overview of building real-time data components with Visual Basic 6.0, see Building a Real-Time Data Server in Excel 2002.
Excel real-time data (RTD) components are new to Excel 2002. RTD components and their accompanying infrastructure are designed to allow real-time data to be populated in an Excel spreadsheet efficiently, robustly, and without dropping an update. While there were ways to get real-time data into Excel before RTD, ranging from Dynamic Data Exchange (DDE) to custom macros and add-ins that ran continuously in a background loop, these methods were, at best, inelegant and, at worst, unreliable and prone to stop responding. It was also difficult to drive recalculations and charts from these methods, as well as impossible to use cell references. RTD in Excel 2002 resolves these issues.
RTD servers are Component Object Model (COM) Dynamic Link Libraries (DLLs) that implement a specific interface provided by Excel 2002, the IRTDServer interface. The RTD server is instantiated by Excel when a user enters a new function (RTD) into Excel and specifies the RTD server's programmatic ID (ProgID). Once the server is instantiated, Excel gives it a reference to a callback object and communication occurs via a push-pull mechanism; that is, the RTD server notifies Excel that data has changed (push) and, when appropriate, Excel requests the changed data from the RTD server (pull). This push-pull architecture allows for RTD to function even if there is a modal dialog box displaying or if there is something else happening that would normally prevent an update.
The IRTDServer interface is simple and clean; the tough part can be in the implementation. However, once you've gotten the hang of it, a (very) simple RTD server can be written quickly and with minimal trouble. The challenge is then in implementing the data feed into the Excel RTD component!
There are some good, compelling reasons for using .NET to create Excel real-time data components. First, the tight integration with .NET XML Web services gives us new avenues to explore for our real-time data feeds—imagine a stock ticker RTD component that gets stock quotes from an XML Web service or a weather RTD component that gets the latest weather from yet another XML Web service. Also, the .NET Framework class libraries expose enhanced network functionality, such as multicasting, that previously required low-level socket coding. Last, but certainly not least, you can use a higher level network API such as Microsoft DirectPlay®, which, when integrated with the .NET Framework's integrated and easy-to-use multithreading, allows you to quickly and easily design worker threads to process the incoming data while processing Excel requests for updates on another thread. In this case, it is also easy to write a true multi-threaded service in the .NET Framework for your DirectPlay server. These advanced network protocols allow you to efficiently distribute real-time data across an enterprise environment without overloading the network infrastructure.
The RTD server that we will be building will be relatively simple. It will be a server that takes stock symbols and randomly updates the stock price. It will also take an argument to return the opening price for the stock (which doesn't get updated) or the last price for the stock (which gets updated randomly). This will allow us to worry less about the code that is doing the updates and concentrate more on the implementation of the components in Visual Basic .NET. In doing this, we will look at COM interoperability in Visual Basic .NET and go over the steps for using COM interoperability. We will also explore how to debug our Visual Basic .NET RTD server from the Visual Studio .NET Integrated Development Environment (IDE).
To begin our Visual Basic .NET RTD server, we will first need to start Visual Studio .NET Beta 2 and create a new Visual Basic .NET Class Library project, naming it VBNetRTD. Once that is done, we will definitely want to rename the default class that Visual Basic .NET creates for us from Class1 to something more descriptive like StockQuote. We will also need to add a reference to the Microsoft Excel 10.0 Object Library; to do this, click Add Reference on the Project menu, click Add Reference, and click the COM tab. In the list, double-click Microsoft Excel 10.0 Object Library, and click OK.
Figure 1. The Add Reference dialog box (click image to see larger picture)
After clicking OK, you will get the following message: "Could not find a primary interop assembly for the COM component 'Microsoft Excel 10.0 Object Library.' A primary interop assembly is not registered for this type library. Would you like to have a wrapper generated for you?" You will need to click Yes to this dialog and allow Visual Basic .NET to create the interoperability assembly; otherwise, you won't be able to implement the IRTDServer interface. Once this is done, a new assembly is created for you with the runtime callable wrappers that will be used to call the COM objects from the managed .NET environment. For more information and details on the import process and alternatives, see Importing a Type Library as an Assembly in the .NET Framework Developer's Guide.
Now that we have a reference to the Excel type library, we will build our RTD server component. In order to allow Excel types to be referenced directly without qualification—as well as some other namespaces that we'll need for our project—we'll add the following imports to the header of the class:
Imports Excel '<-- Excel object library Imports System.Timers '<-- For our timers. Imports System.Runtime.InteropServices '<-- For our interop attributes. Imports System.Collections '<-- For our collections of quotes.
While it isn't required to use the Imports statements in our class, it greatly simplifies variable declaration because we don't have to fully qualify our object by namespace (for example, we can use Timer instead of System.Timers.Timer). Note that, unlike Visual Basic 6.0, simply adding a reference to the library will not allow us to do this. Instead, the reference includes the assembly in the compilation; in order to allow unqualified reference to types, the namespace (the closest equivalent in .NET to a type library) must be specifically imported in the very beginning of the class file. The Imports statement must precede any other declarations except Option statements. The Imports statement is not a replacement for adding references to required components.
Now that we have set up the imports, we will need to start implementing the IRTDServer interface on our StockQuote class. Like Visual Basic 6.0, this is accomplished using the Implements keyword and specifying the interface that we want to implement (IRTDServer).
We'll also need to add some attributes to the class for use by COM Interop services: ProgID and ComVisible.
The ProgID attribute allows you to specify the ProgID that will be used when the class is registered with COM. This is not required and, if not specified on the COM-registered class, the .NET Framework COM registration utility (regasm.exe) will create the ProgID for you. However, we want to control the ProgID and have it appear as Stock.Quote since it will be easier for us to train our end users to use it that way.
The attributes that are associated with a class must also be on the same line of code as the class declaration (this holds true for subroutines and functions as well), so we will use the line continuation characters to make them a little more readable. Also, since our class will be used by COM, we'll need to make sure that we have a default constructor that takes no arguments. By the time we're done, our class looks like this:
Imports Excel '<-- Excel's object library. Imports System.Timers '<-- For our timers. Imports System.Runtime.InteropServices '<-- For our interop attributes. Imports System.Collections '<-- For our collections of quotes. <ComVisible(True), ProgId("Stock.Quote")> _ Public Class StockQuote Implements IRtdServer 'Default constructor with no arguments. 'Required for COM interop. Public Sub New() End Sub End Class
Now that we have the class set up, we need to add the interface members for the IRTDServer interface. While we could type them in manually, we will allow the Visual Basic .NET IDE to create the procedure definitions for us. The process for this is exactly the same as in Visual Basic 6.0; select the interface from the Class Name list on the left side of the code editor, and select each procedure from the Method Name list on the right side of the code editor. This allows us to quickly and easily define all of our procedures with the proper syntax and types.
One significant difference between Visual Basic 6.0 and Visual Basic .NET that is readily apparent is the declaration of implemented methods. In Visual Basic 6.0, this would be a private method and named
InterfaceName_MethodName; for example, the RTD server's Heartbeat method would be declared
Private Function IRTDServer_Heartbeat() as Long. In Visual Basic .NET, however, the implemented method is specified using the Implements keyword with the method and specifying the interface and member that the function is implementing:
Public Function Heartbeat() As Integer Implements Excel.IRtdServer.Heartbeat.
Before we move too fast, though, we need to give some thought to a couple of things that will support our RTD server. First, we need to determine how we will keep track of the state for our stock quotes—the topic IDs, the ticker symbol, and the data—as well as how the RTD server keeps track of the topics. To do this, we'll take advantage of a new feature of Visual Basic .NET—inner classes. An inner class is one that is declared within another class, and by declaring it as Private, it is accessible only from that class. Our class will have properties for the topic ID, ticker symbol, and last trade, as well as a method for updating the value. Since we will need to have a ticker symbol for this class to do anything, we will make this a required argument in the class's constructor and also use this constructor to set an initial opening price for the stock. When this is done, our new class should have the following code in our inner class:
Private Class RTDData 'Private variables for the properties. Private m_strTicker As String Private m_sngLast, m_sngOpen As Single Private m_intTopicID As Integer = -1 'Constructor. Public Sub New(ByVal NewTicker As String) Dim rdm As Random = New Random() 'Set ticker and topic ID. m_strTicker = NewTicker 'Use a random number for the opening price. m_sngOpen = rdm.NextDouble() * 100 'Set the last price to the opening price. m_sngLast = m_sngOpen End Sub Public ReadOnly Property Ticker() As String Get Return m_strTicker End Get End Property Public ReadOnly Property Last() As Single Get Return m_sngLast End Get End Property Public ReadOnly Property Open() As Single Get Return m_sngOpen End Get End Property Public Property TopicID() As Integer Get Return m_intTopicID End Get Set(ByVal Value As Integer) m_intTopicID = Value End Set End Property Public Sub Update(ByVal rdm As Random) 'Get the price change. Dim sngPriceChange = rdm.NextDouble() * 5 If rdm.Next(1, 10) < 5 Then 'Drop in price. m_sngLast -= sngPriceChange Else 'Increase in price. m_sngLast += sngPriceChange End If End Sub End Class
There are a couple of things that are new to Visual Basic .NET worth pointing out here. First, when we declared m_sngLast and m_sngOpen, we only specified the data type (Single) once. In Visual Basic 6.0, m_sngLast would be a variable of type Variant; only m_sngOpen would be of type Single. Visual Basic .NET changes this behavior—both are Single values. Also note the structure of the constructor (
Public Sub New). A constructor is called when the class is created—like
Class_Initialize—but can take arguments that are passed in from the creator when the class is instantiated and can be overloaded, allowing for multiple constructors. If a constructor is not specified, Visual Basic .NET will create a default constructor with no arguments for you. However, if you define any constructor at all, Visual Basic .NET will not create this default constructor. Therefore, by defining only a constructor that takes arguments, these arguments (in our case, there's only one, but there can be several) must be supplied to instantiate the class. Last, but not least, we also have the ability to do arithmetic operator shortcuts (+= and -=), as we do in the Update subroutine with m_sngLast. These arithmetic operator shortcuts allow for easier expression of addition and subtractions; for example the statement
x += 1 is equivalent to
x = x + 1. While this doesn't add any functionality, it is more expressive, easier to write, and more consistent with other modern programming languages.
Once we have our data storage class in place, we'll also need to have a way to keep track of all of the RTDData classes that are created, we'll use an old-fashioned Visual Basic Collection, which is still supported in Visual Basic .NET. This will be declared as a private member variable in our StockTicker class and instantiated in the declaration. We'll also need to declare a Timer to simulate our stock "updates" as well as our reference to the Excel-supplied IRTDUpdateEvent class. Like our collection, these will be private member variables in our StockTicker class and we will call them m_tmrUpdate and m_xlRTDUpdate, respectively. Since we're interested in the timer's Elapsed event, we'll also declare the timer using the WithEvents keyword. By the time we have finished this, the Declarations section of the StockQuote class should be similar to the following:
Private m_xlRTDUpdate As Excel.IRTDUpdateEvent Private WithEvents m_tmrTimer As Timer Private m_colRTDData As Collection = New Collection()
Now we are finally ready to begin implementing the RTD server. There are several things that we need to do, but we will try to take them on one bit at a time. First, we will do the easiest method on the interface: Heartbeat. From this function, we simply need to return a 1, which lets Excel know that our RTD server is still "alive". Note that to do this, we will use the new Return keyword. The Return keyword will return the value specified from the current function just as setting the value of the function does (for example, Heartbeat = 1). However, the Return keyword will then exit the function immediately, returning control to the calling procedure.
Public Function Heartbeat() As Integer _ Implements Excel.IRtdServer.Heartbeat Return 1 End Function
Next, we will implement the ServerStart method. First, we need to set m_xlRTDUpdate to have a reference to the CallbackObject object. We'll also instantiate and prepare the timer that we will use to simulate updates in our sample component and get it set up, though we won't start it until later. When we instantiate it, we will use its constructor to set the default interval of 2000 milliseconds (2 seconds) and then set the AutoReset property to True, which causes the timer to continually fire. Finally, we also need to return 1 from the method to let Excel know that all is well. While we're at it, we'll also implement ServerTerminate, which will clean up all of our references. Notice that we no longer need to use the Set keyword when working with object references. If, out of habit, you type Set anyway, don't worry—Visual Basic .NET will remove it for you.
Public Function ServerStart(ByVal CallbackObject As Excel.IRTDUpdateEvent) _ As Integer Implements Excel.IRtdServer.ServerStart 'Hold a reference to the callback object. m_xlRTDUpdate = CallbackObject 'Create the time with a 2000 millisecond interval. m_tmrTimer = New Timer(2000) m_tmrTimer.AutoReset = True 'All is well, return 1. Return 1 End Function Public Sub ServerTerminate() Implements Excel.IRtdServer.ServerTerminate 'Clear the RTDUpdateEvent reference. m_xlRTDUpdate = Nothing 'Make sure the timer is stopped. If m_tmrTimer.Enabled Then m_tmrTimer.Stop() End If m_tmrTimer = Nothing End Sub
Now we need to work with the RTD server's ConnectData and DisconnectData methods. These methods will be the Excel way to let the RTD server know when a user has requested a new quote, as well as when they are done. In ConnectData, we'll check to see if we already have the related RTDData class for the requested ticker in our collection and create it if we do not. The new Visual Basic .NET structured exception handling now gives us the ability to implement this using complex error handling schemes that were difficult, if not impossible, with Visual Basic 6.0. Structured exception handling uses Try…Catch code blocks and allows error handling schemes to be nested without difficulty—a significant improvement over Visual Basic 6.0 On Error GoTo error handling. We'll also need to check the input to see if the user requested the stock's opening price or the price at the last trade through arguments in the Excel RTD function. If there is an unexpected error in the routine, we will want to display some text indicating that there was an error to the user. In DisconnectData, we will simply remove the RTDData class from our collection if we do not need it anymore.
Public Function ConnectData(ByVal TopicID As Integer, _ ByRef Strings As System.Array, _ ByRef GetNewValues As Boolean) As Object _ Implements Excel.IRtdServer.ConnectData Dim strValue, strTicker As String Dim objRTDData As RTDData 'Make sure that the timer is started. If Not m_tmrTimer.Enabled Then m_tmrTimer.Start() End If GetNewValues = True Try strTicker = UCase(Strings(0)) 'First argument is the ticker. strValue = LCase(Strings(1)) 'Second argument is the type. 'Check the value and act appropriately. If strValue = "last" Then 'They want the last value. 'Check if the data object was created. Try objRTDData = m_colRTDData.Item(strTicker) Catch 'Item wasn't found – create. objRTDData = New RTDData(strTicker) 'Add to collection. m_colRTDData.Add(objRTDData, strTicker) End Try If objRTDData.TopicID = -1 Then 'We want this one's topic ID for later updates. objRTDData.TopicID = TopicID End If Return CObj(objRTDData.Last) ElseIf strValue = "open" Then 'They want the opening price. 'Check if the data object was created. Try objRTDData = m_colRTDData.Item(strTicker) Catch 'Item wasn't found – create. objRTDData = New RTDData(strTicker) 'Add to collection. m_colRTDData.Add(objRTDData, strTicker) End Try Return CObj(objRTDData.Open) Else 'Unrecognized value requested. 'Tell the user. Return "Unrecognized value requested" End If Catch 'Any unexpected error. Return "ERROR IN QUOTE" End Try End Function Public Sub DisconnectData(ByVal TopicID As Integer) _ Implements Excel.IRtdServer.DisconnectData 'User no longer wants the quote. 'Loop over the quotes and try to find it. Dim objRTDData As RTDData For Each objRTDData In m_colRTDData If objRTDData.TopicID = TopicID Then 'Found it ... remove it. m_colRTDData.Remove(objRTDData.Ticker) End If Next 'Stop the timer if we are done. If m_colRTDData.Count = 0 And m_tmrTimer.Enabled Then m_tmrTimer.Stop() End If End Sub
Before we get too far ahead of ourselves and implement the RefeshData method, we need to write the code that will do our random updates. As in Visual Basic 6.0, we will select the member variable from the ClassName list on the left side of the code editor and the Elapsed event from Method Name list on the right side of the code editor. This will automatically create the event definition for us to code. This method will simply loop over the items that are currently in the collection of RTDData items and call the Update method on each, passing a shared Random class to the method. We will also call UpdateNotify to let Excel know that we have new data ready for display and processing. Notice that the syntax for the event is slightly changed in Visual Basic .NET; now we must specifically declare with the event this method handles, rather than it being implied in the declaration. While this may seem like additional typing that isn't necessary, it adds a tremendous amount of flexibility. As long as the events have the same signature, Visual Basic .NET can share event handlers between different events and objects by adding the additional events to the Handles declaration, separating each by a comma.
Private Sub m_tmrTimer_Elapsed(ByVal sender As Object, _ ByVal e As ElapsedEventArgs) Handles m_tmrTimer.Elapsed Dim objData As RTDData 'Create a shared randomizer. Dim rdmRandomizer As Random = New Random() 'Call update for each stock quote. For Each objData In m_colRTDData objData.Update(rdmRandomizer) Next 'Tell Excel that we have updates. m_xlRTDUpdate.UpdateNotify() End Sub
Now, finally and at long last, we are ready to implement the RefreshData method on the IRTDServer interface. This is the key method on the interface; this is the method that Excel calls to get the latest values when it is ready for them (after, of course, our RTD server has called UpdateNotify). This method returns a two-dimensional variant array structure. The first dimension of this structure will contain the topic ID to update and the second dimension contains the new value for the topic. Unlike Visual Basic 6.0, this is not declared as Variant(); instead the return is declared as System.Array, the base array class of the .NET Framework. Also, when we declare the variable that we will be using to build the return value in the body of the method, we cannot use the Variant data type—Visual Basic .NET does not have this data type anymore as it is not a part of the .NET Common Type System. Instead, we will use the Object data type when declaring the array. If you use any other data type, you will get an error in Excel. Since the only data that changes is the last price value, these are the only topics that we will be updating—the open price never updates.
Public Function RefreshData(ByRef TopicCount As Integer) _ As System.Array Implements Excel.IRtdServer.RefreshData Dim intItemCount As Integer Dim aRetVal(1, m_colRTDData.Count - 1) As Object Dim i As Integer For i = 1 To m_colRTDData.Count Dim curItem As RTDData = m_colRTDData.Item(i) If curItem.TopicID <> -1 Then 'Update the topic with the latest value. aRetVal(0, i - 1) = curItem.TopicID aRetVal(1, i - 1) = curItem.Last intItemCount += 1 End If Next 'Tell Excel how many topics we updated. TopicCount = intItemCount 'Return the updates. Return aRetVal End Function
Now that we have finished the code for our Excel real-time data server, it is time to test it in Excel. Instead, we need to tell Visual Studio .NET which application to start for the debug session (Excel) and then start the debug session. To do this, we will right-click on the project in the Solution Explorer window and click Properties. To set the debugging settings, expand the Configuration Properties folder and click Debugging. In the Start Action area, click Start External Program and enter the path to Microsoft Excel (for example, C:\Program Files\Microsoft Office\Office10\EXCEL.EXE).
Figure 2. The Solution Property Pages dialog box: debugging options (click image to see larger picture)
We will also need to change the build settings so that the assembly is registered for COM interop by Visual Studio .NET when it gets compiled. To do this, click Build under the Configuration Properties folder and check Register for COM Interop. When you click Apply, Visual Studio .NET will display the following message: "The assembly must be strongly named if it is to be registered for COM interop. Do you want to give the assembly a strong name now?" Clicking Yes will generate a key for the assembly and give it a strong name; this is required for COM-visible assemblies. For more information on strong names and key files, see Creating and Using Strong-Named Assemblies in the MSDN Library.
Figure 3. The Solution Property Pages dialog box: build options (click image to see larger picture)
Now that we have everything set up, it is time to run the project and test the RTD server by going to the Debug menu and choosing Start (or pressing F5). This will compile the assembly, attach the debugger, and start Excel. To test our RTD server, we need to use the RTD function in Excel. Looking back at ConnectData, we know that we need to supply two additional arguments to the RTD server: the ticker and the type of quote (open or last). The syntax, therefore, for the RTD function to call our Stock Quote RTD server will be:
=RTD("Stock.Quote", , [Ticker], [Type (Open|Last)])
To test our RTD Server, we will put the following values in a blank Excel worksheet:
|1||=RTD("Stock.Quote", , "MSFT", "Open")||=RTD("Stock.Quote", , "MSFT", "Last")|
This gives us a simple spreadsheet that shows the RTD components in action. However, since RTD is a true Excel function, we can use cell references and base calculations on the result. For example, we could integrate the results from our RTD server with charts, use AutoFill—everything that you've become used to doing in Excel. The screen shot below shows some of this.
Figure 4. Excel Workbook with graphs and calculations based on RTD (click image to see larger picture)
Once you understand how COM interop works in .NET, as well as some of the new features, creating a real-time data server in Visual Basic .NET is not difficult at all, although it is different from Visual Basic 6.0. This allows developers to leverage the full functionality and richness of the .NET Framework from their Office-based applications, opening the door to exciting new possibilities.