Een RealTimeData-server voor Excel maken
Samenvatting
Microsoft Excel biedt een nieuwe werkbladfunctie, RTD, waarmee u een COM-automatiseringsserver (Component Object Model) kunt aanroepen om gegevens in realtime op te halen. In dit artikel wordt beschreven hoe u Visual Basic gebruikt om een RealTimeData-server te maken voor gebruik met de rtd-functie van Excel.
Meer informatie
De werkbladfunctie RTD heeft de volgende syntaxis:
=RTD(ProgID,Server,String1,[String2],...)
Het eerste argument, ProgID, vertegenwoordigt de Programmatic Identifier (ProgID) van de RealTimeData-server. Het argument Server geeft de naam aan van de computer waarop de RealTimeData-server wordt uitgevoerd; dit argument kan een null-tekenreeks zijn of worden weggelaten als de RealTimeData-server lokaal moet worden uitgevoerd. De resterende argumenten vertegenwoordigen gewoon parameters die naar de RealTimeData-server moeten worden verzonden; elke unieke combinatie van deze parameters vertegenwoordigt één 'onderwerp', waaraan een onderwerp-id is gekoppeld. Parameters zijn hoofdlettergevoelig. Het volgende illustreert bijvoorbeeld aanroepen naar de RTD-server die drie afzonderlijke onderwerp-id's zouden opleveren:
=RTD("ExcelRTD.RTDFunctions",,"AAA", "10")
=RTD("ExcelRTD.RTDFunctions",,"AAA", "5")
=RTD("ExcelRTD.RTDFunctions",,"aaa", "5")
Als u wilt dat een COM Automation-server een RealTimeData-server is voor gebruik met de rtd-functie van Excel, moet de IRTDServer-interface worden geïmplementeerd. De server moet alle methoden van IRTDServer implementeren:
ServerStart: Aangeroepen wanneer Excel het eerste RTD-onderwerp voor de server aanvraagt. ServerStart moet een 1 retourneren bij een geslaagde bewerking en een negatieve waarde of 0 bij een fout. De eerste parameter van de ServerStart-methode is een callback-object dat de RealTimeData-server gebruikt om Excel op de hoogte te stellen wanneer er updates van de RealTimeData-server moeten worden verzameld.
ServerTerminate: aangeroepen wanneer Excel geen RTD-onderwerpen meer nodig heeft van de RealTimeData-server.
ConnectData: wordt aangeroepen wanneer Excel een nieuw RTD-onderwerp opvraagt bij de RealTimeData-server.
DisconnectData: aangeroepen wanneer Excel geen specifiek onderwerp meer nodig heeft.
HeartBeat: Aangeroepen door Excel als er een bepaald interval is verstreken sinds de laatste keer dat Excel op de hoogte werd gesteld van updates van de RealTimeData-server.
RefreshData: aangeroepen wanneer Excel een vernieuwing van onderwerpen aanvraagt. RefreshData wordt aangeroepen nadat de server Excel heeft laten weten dat er updates bestaan en retourneert het aantal onderwerpen dat moet worden bijgewerkt, samen met de onderwerp-id en -waarde voor elk onderwerp.
Een voorbeeld van een RealTimeData-server maken
In het volgende voorbeeld ziet u hoe u een RealTimeData-server maakt en gebruikt met Microsoft Excel 2002. Deze server biedt gewoon een teller die elke 10 seconden op een werkblad wordt bijgewerkt. De server accepteert maximaal twee onderwerptekenreeksen. De eerste onderwerptekenreeks kan AAA, BBB en AAA zijn; een andere onderwerptekenreeks wordt als ongeldig beschouwd en de server retourneert #VALUE! naar de functie RTD. De tweede tekenreeks is een numerieke waarde die aangeeft hoe de retourwaarde moet worden verhoogd. Als de tweede tekenreeks wordt weggelaten, wordt de increment-waarde standaard ingesteld op 1. Als de tweede tekenreeks niet numeriek is, retourneert de server #NUM! naar de functie RTD.
Start een nieuw ActiveX DLL-project in Visual Basic.
Klik in het menu Project op Verwijzingen, selecteer de objectbibliotheek voor uw versie van Excel en klik op OK. Selecteer bijvoorbeeld een van de volgende opties:
- Voor Microsoft Office Excel 2007 selecteert u Microsoft Excel 12.0-objectbibliotheek.
- Voor Microsoft Office Excel 2003 selecteert u Microsoft Excel 11.0-objectbibliotheek.
- Voor Microsoft Excel 2002 selecteert u Microsoft Excel 10.0-objectbibliotheek.
Klik in het menu Project op Eigenschappen van Project1. Wijzig de projectnaam in ExcelRTD en klik op OK.
Wijzig de eigenschap Name van de klassemodule Class1 in RTDFunctions. Voeg de volgende code toe aan RTDFunctions:
Option Explicit Implements IRtdServer 'Interface allows Excel to contact this RealTimeData server Private m_colTopics As Collection Private Function IRtdServer_ConnectData(ByVal TopicID As Long, Strings() As Variant, GetNewValues As Boolean) As Variant '** ConnectData is called whenever a new RTD topic is requested 'Create a new topic class with the given TopicId and string and add it to the 'm_colTopics collection Dim oTopic As New Topic m_colTopics.Add oTopic, CStr(TopicID) oTopic.TopicID = TopicID oTopic.TopicString = Strings(0) If UBound(Strings) >= 1 Then oTopic.SetIncrement Strings(1) 'For this example, the initial value for a new topic is always 0 IRtdServer_ConnectData = oTopic.TopicValue Debug.Print "ConnectData", TopicID End Function Private Sub IRtdServer_DisconnectData(ByVal TopicID As Long) '** DisconnectData is called whenever a specific topic is not longer needed 'Remove the topic from the collection m_colTopics.Remove CStr(TopicID) Debug.Print "DisconnectData", TopicID End Sub Private Function IRtdServer_Heartbeat() As Long '** Called by Excel if the heartbeat interval has elapsed since the last time ' Excel was called with UpdateNotify. Debug.Print "HeartBeat" End Function Private Function IRtdServer_RefreshData(TopicCount As Long) As Variant() '** Called when Excel is requesting a refresh on topics. RefreshData will be called ' after an UpdateNotify has been issued by the server. This event should: ' - supply a value for TopicCount (number of topics to update) ' - return a two dimensional variant array containing the topic ids and the ' new values of each. Dim oTopic As Topic, n As Integer ReDim aUpdates(0 To 1, 0 To m_colTopics.Count - 1) As Variant For Each oTopic In m_colTopics oTopic.Update aUpdates(0, n) = oTopic.TopicID aUpdates(1, n) = oTopic.TopicValue n = n + 1 Next TopicCount = m_colTopics.Count IRtdServer_RefreshData = aUpdates Debug.Print "RefreshData", TopicCount & " topics updated" End Function Private Function IRtdServer_ServerStart(ByVal CallbackObject As Excel.IRTDUpdateEvent) As Long '** ServerStart is called when the first RTD topic is requested Set oCallBack = CallbackObject Set m_colTopics = New Collection g_TimerID = SetTimer(0, 0, TIMER_INTERVAL, AddressOf TimerCallback) If g_TimerID > 0 Then IRtdServer_ServerStart = 1 'Any value <1 indicates failure. Debug.Print "ServerStart" End Function Private Sub IRtdServer_ServerTerminate() '** ServerTerminate is called when no more topics are needed by Excel. KillTimer 0, g_TimerID '** Cleanup any remaining topics. This is done here since ' IRtdServer_DisconnectData is only called if a topic is disconnected ' while the book is open. Items left in the collection when we terminate ' are those topics left running when the workbook was closed. Dim oTopic As Topic For Each oTopic In m_colTopics m_colTopics.Remove CStr(oTopic.TopicID) Set oTopic = Nothing Next Debug.Print "ServerTerminate" End Sub
Klik in het menu Project op Klassemodule toevoegen. Wijzig de eigenschap Naam van de klassemodule in Onderwerp en wijzig de eigenschap Instancing in Privé. Voeg de volgende code toe aan de klassemodule Onderwerp:
Option Explicit Private m_TopicID As Long Private m_TopicString As String Private m_Value As Variant Private m_IncrementVal As Long Private Sub Class_Initialize() m_Value = 0 m_IncrementVal = 1 End Sub Friend Property Let TopicID(ID As Long) m_TopicID = ID End Property Friend Property Get TopicID() As Long TopicID = m_TopicID End Property Friend Property Let TopicString(s As String) s = UCase(s) If s = "AAA" Or s = "BBB" Or s = "CCC" Then m_TopicString = s Else m_Value = CVErr(xlErrValue) 'Return #VALUE if not one of the listed topics End If End Property Friend Sub Update() On Error Resume Next 'the next operation will fail if m_Value is an error (like #NUM or #VALUE) m_Value = m_Value + m_IncrementVal End Sub Friend Sub SetIncrement(v As Variant) On Error Resume Next m_IncrementVal = CLng(v) If Err <> 0 Then m_Value = CVErr(xlErrNum) 'Return #NUM if Increment value is not numeric End If End Sub Friend Property Get TopicValue() As Variant If Not (IsError(m_Value)) Then TopicValue = m_TopicString & ": " & m_Value Else TopicValue = m_Value End If End Property
Selecteer Module toevoegen in het menu Project. Voeg de volgende code toe aan de nieuwe module:
Public Declare Function SetTimer Lib "user32" (ByVal hWnd As Long, _ ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long Public Declare Function KillTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long) As Long Public Const TIMER_INTERVAL = 5000 Public oCallBack As Excel.IRTDUpdateEvent Public g_TimerID As Long Public Sub TimerCallback(ByVal hWnd As Long, ByVal uMsg As Long, ByVal idEvent As Long, ByVal dwTime As Long) oCallBack.UpdateNotify End Sub
Klik in het menu Bestand op ExcelRTD.dll maken om het onderdeel te maken.
De RTD-server in Excel gebruiken
Start een nieuwe werkmap in Microsoft Excel.
Voer in cel A1 de volgende formule in en druk op Enter:
=RTD("ExcelRTD.RTDFunctions","AAA", 5)
De oorspronkelijke retourwaarde is 'AAA: 0'. Na vijf seconden wordt de waarde bijgewerkt naar 'AAA: 10' en na 10 seconden wordt de waarde bijgewerkt naar 'AAA:15', enzovoort.
Voer in cel A2 de volgende formule in en druk op Enter:
=RTD("ExcelRTD.RTDFunctions","BBB", 3)
De eerste retourwaarde is 'BBB: 0'. Elke vijf seconden wordt de celwaarde verhoogd met 3.
Voer in cel A3 de volgende formule in en druk op Enter:
=RTD("ExcelRTD.RTDFunctions","AAA", 5)
De oorspronkelijke retourwaarde komt overeen met de inhoud van cel A1, omdat dit hetzelfde 'onderwerp' is dat wordt gebruikt in A1.
Voer in cel A4 de volgende formule in en druk op Enter: =RTD("ExcelRTD.RTDFunctions",,"AAA", 10)
De oorspronkelijke retourwaarde is 'AAA: 0'. Om de vijf seconden wordt de celwaarde verhoogd, net als de andere cellen. Houd er rekening mee dat de retourwaarde niet overeenkomt met de inhoud van cel A1 of A3 omdat de combinatie van parameters die aan de server worden doorgegeven, anders is.
Voor deze afbeelding is de RTD-server gecompileerd en gebruikt Excel de runtime-versie van het onderdeel. Voor foutopsporingsdoeleinden kunnen RTD-servers worden uitgevoerd vanuit de Visual Basic IDE.
Uitvoeren in de foutopsporingsmodus:
- Sluit Microsoft Excel af en schakel over naar het project in Visual Basic.
- Druk op F5 om het onderdeel te starten. Als het dialoogvenster Projecteigenschappen wordt weergegeven, klikt u op OK om de standaardoptie Wachten op onderdelen te selecteren.
- Zorg ervoor dat het venster Direct in Visual Basic wordt weergegeven. Wanneer u formules invoert in de cellen en terwijl de cellen worden bijgewerkt, bekijkt u de inhoud van het venster Direct in Visual Basic om te zien welke acties de verschillende gebeurtenissen activeren.
Opmerking
Met betrekking tot de DisconnectData-gebeurtenis
Hoewel Excel een abonnee is van uw RTD-server, wordt de gebeurtenis DisconnectData geactiveerd wanneer er geen onderwerp meer nodig is (bijvoorbeeld wanneer u een RTD-formule in een cel verwijdert of wist). In Excel wordt DisconnectData echter niet aangeroepen voor elk onderwerp voor de RTD-server wanneer de werkmap wordt gesloten of Excel wordt afgesloten; in plaats daarvan wordt alleen ServerTerminate aangeroepen. Wanneer u een RTD-server maakt, moet u coderen voor alle benodigde opschoning van onderwerpen of andere objecten wanneer de ServerTerminate-gebeurtenis wordt geactiveerd.
(c) Microsoft Corporation 2001, Alle rechten voorbehouden. Bijdragen van Lync B. Turner, Microsoft Corporation.