ÇÔ¸Õ¡ÒÃÊÃéÒ§à«ÔÃì¿àÇÍÃì RealTimeData ÊÓËÃѺ Excel

Microsoft Excel áÊ´§ãËÁèá¼è¹§Ò¹¿Ñ§¡ìªÑ¹ RTD «Ö觪èÇÂãËé¤Ø³àÃÕ¡ãªéà«ÔÃì¿àÇÍÃì Automation ¤ÍÁâ¾à¹¹µìÇѵ¶ØẺ¨ÓÅͧ (COM) ÊÓËÃѺÇѵ¶Ø»ÃÐʧ¤ì㹡Òô֧¢éÍÁÙÅã¹àÇÅÒ¨ÃÔ§ º·¤ÇÒÁ¹Õé͸ԺÒÂÇÔ¸Õ¡ÒÃãªé Visual Basic à¾×èÍÊÃéÒ§à«ÔÃì¿àÇÍÃì RealTimeData ÊÓËÃѺãªé¡Ñº¿Ñ§¡ìªÑ¹ RTD ¢Í§ Excel

¿Ñ§¡ìªÑ¹¢Í§á¼è¹§Ò¹ RTD ÁÕäÇÂҡóìµèÍ仹Õé:ÍÒÃì¡ÔÇàÁ¹µìááprogidáÊ´§¶Ö§µÑÇ·Õè´éÒ¹â»Ãá¡ÃÁÃÐºØ (ProgID) ¢Í§à«ÔÃì¿àÇÍÃì RealTimeData ¡Ãкǹ¡ÒÃà«ÔÃì¿àÇÍÃì:ÍÒÃì¡ÔÇàÁ¹µì·Õèºè§ªÕéª×èͧ͢à¤Ã×èͧ·ÕèÃѹà«ÔÃì¿àÇÍÃì RealTimeData ÍÒÃì¡ÔÇàÁ¹µì¹ÕéÊÒÁÒöà»ç¹ÊµÃÔ§·ÕèÇèÒ§ ËÃ×ÍäÁè¶éÒà«ÔÃì¿àÇÍÃì RealTimeData ãËé àÃÕ¡ãªéÀÒÂã¹ ÍÒÃì¡ÔÇàÁ¹µì·ÕèàËÅ×ÍÍÂÙèà¾Õ§á¤èáÊ´§¾ÒÃÒÁÔàµÍÃì·ÕèÊè§ä»Âѧà«ÔÃì¿àÇÍÃì RealTimeData áµèÅЪش·ÕèäÁè«éӡѹ¢Í§¾ÒÃÒÁÔàµÍÃìàËÅèÒ¹Õéá·¹ËÑÇ˹Öè§ "¢éÍ ËÁÒÂàÅ¢ËÑÇ¢éÍ"«Öè§ÁÕ¡ÒÃàª×èÍÁâ§" ¾ÒÃÒÁÔàµÍÃìÁÕ case-sensitive áÊ´§µÑÇÍÂèÒ§àªè¹ µèÍ仹ÕéáÊ´§¡ÒÃàÃÕ¡ä»Âѧà«ÔÃì¿àÇÍÃì RTD ·Õè¨Ð·ÓãËéÃËÑʢͧËÑÇ¢éÍ·ÕèᡵèÒ§ËÒ¡ÊÒÁ:ã¹ãºÊÑè§ÊÓËÃѺà«ÔÃì¿àÇÍÃì Automation COM ·Õèà»ç¹ à«ÔÃì¿àÇÍÃì RealTimeData ÊÓËÃѺãªé¡Ñº¿Ñ§¡ìªÑ¹ RTD ¢Í§ Excel ¨Ðµéͧ»¯ÔºÑµÔ¡ÒÃIRTDServerÍÔ¹à·ÍÃìà¿« à«ÔÃì¿àÇÍÃì·Õèµéͧ»¯ÔºÑµÔµÒÁÇÔ¸Õ¡Ò÷Ñé§ËÁ´IRTDServer:

ServerStartServerTerminateConnectDataDisconnectDataheartBeatRefreshData

ÊÃéÒ§à«ÔÃì¿àÇÍÃì RealTimeData µÑÇÍÂèÒ§

µÑÇÍÂèÒ§µèÍ仹Õé͸ԺÒÂÇÔ¸Õ¡ÒÃÊÃéÒ§ áÅÐãªéà«ÔÃì¿àÇÍÃì RealTimeData ¡Ñº Microsoft Excel 2002 à«ÔÃì¿àÇÍÃì¹Õé¨ÐáÊ´§µÑǹѺ·ÕèÁÕ¡ÒûÃѺ»Ãا·Ø¡ æ 10 ÇÔ¹Ò·Õº¹á¼è¹§Ò¹ à«ÔÃì¿àÇÍÃì·ÕèÂÍÁÃѺÊÒÂÍÑ¡¢ÃТͧËÑÇ¢éͤÍÅÑÁ¹ìÊͧ ÊÒÂÍÑ¡¢ÃÐáá¢Í§ËÑÇ¢éÍÊÒÁÒö AAA, BBB áÅÐ CCC ʵÃÔ§ËÑÇ¢éÍÍ×è¹ã´ æ ¨Ð¶×Íà»ç¹äÁè¶Ù¡µéͧ áÅÐà«ÔÃì¿àÇÍÃìÊ觡ÅѺ #VALUE àÁ×è͵éͧ¡Òÿѧ¡ìªÑ¹ RTD ÊÒÂÍÑ¡¢ÃзÕèÊͧÁÕ¤èÒµÑÇàÅ¢·ÕèáÊ´§¶Ö§ÇÔ¸Õ incremented ¤èÒÊ觤׹ ¶éÒÁÕàÇé¹ÊÒÂÍÑ¡¢ÃзÕèÊͧ à¾ÔèÁ¤èÒàÃÔèÁµé¹·Õè 1 ¶éÒÊÒÂÍÑ¡¢ÃзÕèÊͧäÁèãªèµÑÇàÅ¢ à«ÔÃì¿àÇÍÃìÊ觡ÅѺ #NUM àÁ×è͵éͧ¡Òÿѧ¡ìªÑ¹ RTD

1. àÃÔèÁµé¹â¤Ã§¡Òà ActiveX DLL ãËÁèã¹ Visual Basic
2. 㹡ÒÃProjectàÁ¹Ù ¤ÅÔ¡ÍéÒ§ÍÔ§:àÅ×Í¡äźÃÒÃÕÇѵ¶ØÊÓËÃѺÃØ蹢ͧ Excel áÅШҡ¹Ñé¹ ¤ÅÔ¡µ¡Å§. µÑÇÍÂèÒ§àªè¹ àÅ×͡˹Ö觵èÍ仹Õé:
   • ÊÓËÃѺ Microsoft Office Excel 2007 àÅ×Í¡¡ÒÃäźÃÒÃÕÇѵ¶Ø Microsoft Excel 12.0.
   • ÊÓËÃѺ Microsoft Office Excel 2003 àÅ×Í¡¡ÒÃäźÃÒÃÕÇѵ¶Ø Microsoft Excel 11.0.
   • ÊÓËÃѺ Microsoft Excel 2002 àÅ×Í¡¡ÒÃäźÃÒÃÕÇѵ¶Ø Microsoft Excel 10.0.
3. 㹡ÒÃProjectàÁ¹Ù ¤ÅÔ¡¤Ø³ÊÁºÑµÔ Project1. ¡ÒÃà»ÅÕè¹á»Å§¹Ñ鹪×èͧ͢â¤Ã§¡ÒÃàÁ×è͵éͧ¡ÒÃExcelRTDáÅéÇ ¤ÅÔ¡µ¡Å§.
4. ¡ÒÃà»ÅÕè¹á»Å§¹Ñ鹪×èÍ:¤Ø³ÊÁºÑµÔ¢Í§âÁ´ÙŤÅÒClass1àÁ×è͵éͧ¡ÒÃRTDFunctions. à¾ÔèÁÃËÑʵèÍ仹Õéà¾×èÍ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
   
   					

5. 㹡ÒÃProjectàÁ¹Ù ¤ÅÔ¡à¾ÔèÁâÁ´ÙŤÅÒÊ. à»ÅÕè¹âÁ´ÙŤÅÒª×èÍ:¤Ø³ÊÁºÑµÔ¡ÒÃËÑÇ¢éÍáÅÐÁÕ¡ÒÃà»ÅÕè¹á»Å§¹Ñé¹instancing¤Ø³ÊÁºÑµÔ¡ÒÃÊèǹµÑÇ. à¾ÔèÁÃËÑʵèÍ仹Õéä»ËÑÇ¢éÍâÁ´ÙŤÅÒÊ:

   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
   					

6. 㹡ÒÃProjectàÁ¹Ù ¡ÒÃàÅ×Í¡à¾ÔèÁâÁ´ÙÅ. à¾ÔèÁÃËÑʵèÍ仹Õéã¹âÁ´ÙÅãËÁè:

   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
   					

7. 㹡ÒÃá¿éÁ:àÁ¹Ù ¤ÅÔ¡µÃǨÊͺ ExcelRTD.dllàÁ×è͵éͧÊÃéÒ§Êèǹ»ÃСͺ

ãªéà«ÔÃì¿àÇÍÃì RTD ã¹ Excel

1. àÃÔèÁµé¹ÊÁØ´§Ò¹ãËÁèã¹ Microsoft Excel
2. ã¹à«ÅÅì A1 »é͹ÊٵõèÍ仹Õé áÅШҡ¹Ñé¹ ¡´á»é¹ ENTER:
   = RTD("ExcelRTD.RTDFunctions",,"AAA", 5)
   ¤×¹¤èÒàÃÔèÁµé¹à»ç¹ " AAA: 0 " ËÅѧ¨Ò¡ËéÒÇÔ¹Ò·Õ ¤èÒÍѾഡÒà " AAA: 10 " áÅÐËÅѧ¨Ò¡ 10 ÇÔ¹Ò·Õ ¤èÒÍѾഠ"AAA:15" áÅÐÍ×è¹ æ
   
   
3. ã¹à«ÅÅì A2 »é͹ÊٵõèÍ仹Õé áÅéÇ¡´ ENTER:
   = RTD("ExcelRTD.RTDFunctions",,"BBB", 3)
   ¤×¹¤èÒàÃÔèÁµé¹à»ç¹ " BBB: 0 " ÇÔ¹Ò·Õ·ÕèËéÒ·Ø¡¤èÒã¹à«ÅÅì·Õèà¾ÔèÁ â´Â 3
   
   
4. ã¹à«ÅÅì A3 »é͹ÊٵõèÍ仹Õé áÅéÇ¡´ ENTER:
   = RTD("ExcelRTD.RTDFunctions",,"AAA", 5)
   ¤×¹¤èÒàÃÔèÁµé¹µÃ§¡Ñºà¹×éÍËҢͧà«ÅÅì A1 à¹×èͧ¨Ò¡à»ç¹ËÑÇà´ÕÂÇ "¢éÍ" ·Õèãªéã¹ A1
   
   
5. ã¹à«ÅÅì A4 »é͹ÊٵõèÍ仹Õé áÅС´ Enter:
   = RTD("ExcelRTD.RTDFunctions",,"AAA", 10)
   ¤×¹¤èÒàÃÔèÁµé¹à»ç¹ " AAA: 0 " ·Ø¡ æ ËéÒÇÔ¹Ò·Õà¾ÔèÁ¤èҢͧà«ÅÅì¡Ñºà«ÅÅìÍ×è¹ æ 㹡Ò÷ӧҹ â»Ã´ÊѧࡵÇèÒ ¤èÒ·ÕèÊ觤׹äÁèµÃ§¡Ñºà¹×éÍËҢͧà«ÅÅì A1 ËÃ×Í A3 ä´éà¹×èͧ¨Ò¡ªØ´¢Í§¾ÒÃÒÁÔàµÍÃì·ÕèÊ觼èÒ¹ä»Âѧà«ÔÃì¿àÇÍÃìÍ×è¹

ÊÓËÃѺÀÒ¾»ÃСͺ¹Õé à«ÔÃì¿àÇÍÃì RTD ¤ÍÁä¾Åì áÅзӡÒà Excel ¶Ù¡ãªéà»ç¹àÇÅÒàÃÕ¡ãªéÃØ蹢ͧ¤ÍÁâ¾à¹¹µì For debugging purposes, RTD servers can be run from the Visual Basic IDE.

To run in debug mode:

1. Quit Microsoft Excel and switch to the project in Visual Basic.
2. Press F5 to start the component. ¶éÒ¡ÒäسÊÁºÑµÔ¢Í§â¤Ã§¡ÒáÅèͧâµéµÍº»ÃÒ¡¯¢Öé¹ ¤ÅÔ¡µ¡Å§to select the default option ofWait for components to be created.
3. Make sure that the Immediate window in Visual Basic is displayed. As you enter formulas in the cells and as the cells are updated, examine the contents of the Immediate window in Visual Basic to see which actions are triggering the different events.

Note Regarding the DisconnectData Event

While Excel is a subscriber to your RTD server, it triggers theDisconnectDataevent when it no longer needs a topic (for example, when you delete or clear an RTD formula in a cell). However, Excel does not callDisconnectDataon each topic for the RTD server when the workbook is closed or Excel quits; instead, Excel calls onlyServerTerminate. When you are creating an RTD server, you should code for any necessary clean-up of topics or other objects when theServerTerminateevent fires.

(c) Microsoft Corporation 2001, All Rights Reserved. contributions â´Â Lori B. Turner, Microsoft Corporation

ÊÓËÃѺ¢éÍÁÙÅà¾ÔèÁàµÔÁ ãËé¤ÅÔ¡ËÁÒÂàÅ¢º·¤ÇÒÁµèÍ仹Õé à¾×èÍ´Ùº·¤ÇÒÁã¹°Ò¹¤ÇÒÁÃÙé¢Í§ Microsoft::