2022.8.18 註記：由於 Excel 執行環境改變，這個程式不得不進行大改，敬請期待。

(2018年11月26日更新，原發表於2018 年4月24日下午)

UDN 部落格荒廢多年，偶爾仍會收到朋友來信向我要 VBA 程式，實在是因為威阿北年近60，現在的工作與「程式設計」距離已經非常遙遠，真的要感謝大家看得起威阿北。回顧這些年來，最常被要到的就是「把 (股市看盤) DDE 帶進 Excel 的資料每隔 x 分鐘記錄一次」。今(2018)年初又有朋友要這支程式，於是把程式做了一些加強，寫了這篇分享出來。幾個月下來，接到更多來信，不得不找出時間來修改程式，目前已更新到第2.5 版，因此也再次來修改這篇文章。

(2018/11/29) 
留言可能需等很久很久才會被發現，若有疑問請 email 給我:
 
way2cheng@gmail.com (但每天只看一次信箱) 

需要這個新版程式的朋友們，可以來信向我要xls檔。

來信主旨請寫明：「我要依固定時間抄錄DDE v2.5」

使用操作方法概要

這個程式的目的是按照固定間隔時間，把DDE傳遞行情程式傳入工作表的資料值抄到另一張工作表去，每一次寫成一列。在它的 Control 工作表(控制頁) 上有一個按鈕，按一次就開始計時，再按一次會停住，再按就會繼續 ... ...(依此類推)。

在 Control 工作表(控制頁)上有一個儲存格裡面是 "00:01:00"，表示現在的設定是每隔一分鐘記錄一次，例如你想每隔一分半鐘記錄一次，把它改成 "00:01:30" 就可以了。但是你必須先把 DDE 導入 "DDE-Input" 工作表 (DDE資料頁)，當VBA程式執行的時候，DDE程式傳入的數值就會被VBA程式抄錄到Record工作表 (記錄頁) 裡面。

如下圖，你可以看到我的程式預先寫好的是抄錄哪些儲存格裡的值，若要修改程式記錄哪些儲存格，你需閱讀程式碼並增修程式碼。

接下來描述如何建立這個程式：

Step-1. 建立「資料頁」DDE-Input工作表

在 Excel 活頁簿裡建立一個「DDE-Input」工作表，將 DDE 的欄位拉進這張工作表。(「DDE-Input」是我舉例用的名稱，有些 DDE 會按照它自己的預設名稱建立工作表，請注意將我以下程式做相對應的修改) 例如下圖，是把三個商品標的的名稱、成交價、成交量分別傳入A、B、C欄的2、3、4列：

Step-2. 建立「記錄頁」Record工作表

在 Excel 活頁簿裡建立一個Record工作表，它的第一列內容輸入欄位標題就可以了，我們要讓 VBA 程式將資料依次抄到這個頁面的第2列、第3列 ... ... 依此類推。

Step-3. 建立「控制頁」Control工作表

在「控制頁」Control工作表的A1、A2、A3、A5 儲存格打些字，如下圖，程式將要把每次執行記錄的時間顯示在 B1 儲存格，把下一次將要執行記錄的時間顯示在 B2儲存格，而 B3 儲存格則會顯示下一次會執行抄錄的時候會把資料抄到「記錄頁」Record工作表的第幾列。

在 B3 儲存格則必須輸入我們希望它間隔多久執行抄錄一次。格式是「hh:mm:ss」，請注意要以「’」(鍵盤 Enter 鍵左邊那個鍵) 為開頭，否則 Excel 會把資料轉換成 "日期時間值"(年月日時分秒)。請看下圖Excel編輯列 ( fx 右邊) 的顯示。

最重要的是，要在這個工作頁插入兩個按鈕，把一個上面的字改成「開始記錄」。如下圖：

怎麼插入按鈕？

(以 Excel 2007 為準) 按 Alt+F11 進入 VBA 編輯畫面，首先按工具列中間的 "設計模式" 鈕，然後選擇 [插入]-[自訂表單]，畫面會出現一個如下圖中上的空白表單 (UserForm)，我們沒有要做表單，而是要點工具列最右邊那個扳手鎚子鈕 (如下圖右上) 叫出「控制項工具箱」。點控制項其中的 "按鈕" 工具，然後切換Excel視窗畫面到 Contol工作表，用滑鼠拖曳的方式 "畫出" 按鈕來。

在 Control 工作表畫出來的按鈕表面顯示的是 "CommmandButton 1"，用滑鼠指標指著按鈕點滑鼠右鍵，選「內容」，畫面會出現 "屬性" 視窗，我們就可以在裡面修改按鈕的標題 (Caption)。請注意，在 "設計模式" 才能修改按鈕的標題，如果此時不是，請你按VBA編輯畫面工具列中間的 "設計模式" 鈕。在v2.5新版裡，這個按鈕是開始與停止的切換開關，等一下在程式碼裡面就會看到將它的caption如何改來改去的切換 (按一次改成「停止記錄」, 再按又改回「開始記錄」)。

 

Step-4. 輸入按鈕的程式碼

用滑鼠指標指著按鈕點滑鼠右鍵，選「檢視程式碼」，畫面會出現 "程式碼" 視窗，把這兩個按鈕按被按下的時候要執行的程式碼輸入進去。就像上圖右半邊那個大視窗，裡面的程式碼如下：

※注意：受到 UDN 部落格的限制 (不可以使用鍵盤Enter鍵左邊的‘ 字元)，我把程式碼裡面的註解都改成用[ ]方括號括起來。(整行的註解仍然使用 Rem)

Rem 「開始記錄/停止記錄」鈕

Private Sub CommandButton1_Click()

Dim dCurrentTime As Date

Dim iCurrentMin, iCurrentSec As Integer

 

If TimeValue(Worksheets("Control").Cells(5, 2).Value) < TimeValue("00:01:00") Then

    MsgBox ("間隔時間太短，請設定至少一分鐘")

ElseIf Not bScheduled Then      [若現在是未預約狀態，則做預約]

    dCurrentTime = Now()      [取現在時刻]

    Worksheets("Control").Cells(1, 2).Value = dCurrentTime   [顯示出執行設定當時的時刻]

    iCurrentMin = Minute(dCurrentTime)  [取 "分" 的部份]

    iCurrentSec = Second(dCurrentTime)  [取 "秒" 的部份]

 

    Select Case iCurrentSec      [此 select case 句子是為避免按鈕後太快執行第一次抄錄]

    Case Is <= 25:="" p="">

        iCurrentSec = 30         [這分的30秒執行]

    Case Is > 25, Is <= 50:="" p="">

        iCurrentSec = 0          [下一分的0秒執行]

        iCurrentMin = iCurrentMin + 1

    Case Is > 50:

        iCurrentSec = 30         [下一分的30秒執行]

        iCurrentMin = iCurrentMin + 1

    End Select

 

    If (Worksheets("Control").Cells(3, 2).Value) < 2 Or _

        Worksheets("Control").Cells(3, 2).Value = "" Then

        Worksheets("Control").Cells(3, 2).Value = 2  [避免 "下次抄錄至此列：" 右邊那格的值為 0 或 1 或空格]

    End If

 

    dNextTime = CDate(Year(dCurrentTime) & "-" & Month(dCurrentTime) & "-" & Day(dCurrentTime) _

                & " " & Hour(dCurrentTime) & ":" & iCurrentMin & ":" & iCurrentSec)

    Worksheets("Control").Cells(2, 2).Value = dNextTime [在控制頁顯示第一次預約執行的時間]

    sInterval = Worksheets("Control").Cells(5, 2).Value  [設定間隔時間，格式為 hh:mm:ss]

   

    Application.OnTime dNextTime, "procRecord"            [預約到了 "dNextTime" 就執行 procRecord()]

   

    bScheduled = True

    Worksheets("Control").Cells(4, 2).Value = bScheduled  [在控制頁呈現預約狀態]

    CommandButton1.Caption = "停止記錄"                  [變成停止記錄鈕]

Else  [若是已預約狀態，則取消預約]

    Application.OnTime dNextTime, "procRecord", , False  [取消預約]

    bScheduled = False  [把這個公共變數值設為False, 才可再次被預約]

    Worksheets("Control").Cells(2, 2).Value = ""  [清除控制頁顯示的下次預約執行時間]

    Worksheets("Control").Cells(4, 2).Value = bScheduled   [在控制頁呈現預約狀態]

    CommandButton1.Caption = "開始記錄"                   [變成開始記錄鈕]

End If

End Sub

 

這個副程式的關鍵功能是利用Application.OnTime 來預約下一次執行，本文最後會再詳細解說。

 Step-5. 建立共用模組程式碼

 在 VBA 編輯畫面選[插入]功能表裡面的[模組]，畫面會出現一個 "Module1" 程式碼視窗，把如下的程式碼輸入進去：

Rem 公用變數宣告                    用途

Public sInterval As String       [記住間隔時間]

Public iRecToRow As Integer   [記住下次要寫到哪一列]

Public dNextTime As Date      [記住上次預約要在何時執行, 要取消預約的時候需要此值]

Public bScheduled As Boolean  [記住預約的狀態]

Sub procRecord()

Rem 此副程式可被「開始記錄/停止記錄」鈕的程式預約執行，當它被執行時，

Rem 會把 DDE傳進來的資料抄到 Record 工作表，然後預約下次執行

 

ThisWorkbook.Worksheets("Control").Cells(1, 2).Value = Now()       [本次實際執行的時刻]

iRecToRow = ThisWorkbook.Worksheets("Control").Cells(3, 2).Value  [要抄錄到此列]

 

With ThisWorkbook.Worksheets("DDE-Input")

    ThisWorkbook.Worksheets("Record").Cells(iRecToRow, 1).Value = Now()

    ThisWorkbook.Worksheets("Record").Cells(iRecToRow, 2).Value = .Cells(3, 2).Value  [標的-2 成交價]

    ThisWorkbook.Worksheets("Record").Cells(iRecToRow, 3).Value = .Cells(3, 3).Value  [標的-2 成交量]

    ThisWorkbook.Worksheets("Record").Cells(iRecToRow, 4).Value = .Cells(2, 3).Value  [標的-1 成交量]

    ThisWorkbook.Worksheets("Record").Cells(iRecToRow, 5).Value = .Cells(4, 3).Value  [標的-3 成交量]

End With

 

ThisWorkbook.Worksheets("Control").Cells(3, 2).Value = iRecToRow + 1 [下次要寫到下一列]

dNextTime = Now() + TimeValue(sInterval)            [下次要執行的時間]

ThisWorkbook.Worksheets("Control").Cells(2, 2).Value = dNextTime  [在控制頁顯示預定下次執行的時刻]

 

Application.OnTime dNextTime, "procRecord"          [預約下次執行]

 

ThisWorkbook.Worksheets("Control").Cells(4, 2).Value = bScheduled  [在控制頁呈現預約狀態]

End Sub 

為何要指定 ThisWorkbook?

這個副程式裡面，所有指定活頁簿的程式碼都必須指定活頁簿， 因為它會被設定預約執行，當它被執行的時候這個活頁簿可能是在 "背景"，也就是說，當時的 ActiveWorkbook 不一定是這個活頁簿，當這種情形發生的時候，在 ActiveWorkbook 裡會找不到程式裡指定要存取的 worksheet (Control, DDE-Input, Record)， Excel就會跳出 "執行階段錯誤 9"(陣列索引超出範圍) 的錯誤訊息 (程式裡不寫明活頁簿的話，預設就是針對 ActiveWorkbook，也就是當時正在前景的活頁簿)

 Step-6. 輸入 Open() 程式碼

用滑鼠指標在專案視窗裡雙擊(double-click) "ThisWorkbook"，然後在它的程式碼視窗右上的下拉選單裡面選擇 "Open"，就可以輸入Open() 副程式如下：

Private Sub Workbook_Open()

Rem 此副程式名稱不可改。每當活頁簿檔案被開啟的時候，

Rem Excel 自動會執行此副程式一次。

 

    bScheduled = False    [預約狀態設為 False]

    Worksheets("Control").Cells(1, 2).Value = ""  [清除控制頁顯示的本次執行時間]

    Worksheets("Control").Cells(2, 2).Value = ""  [清除控制頁顯示的下次預約執行時間]

    Worksheets("Control").Cells(4, 2).Value = bScheduled  [在控制頁呈現預約狀態]

    Worksheets("Control").CommandButton1.Caption = "開始記錄"

End Sub

 Step-7. 關閉 "設計模式"，儲存檔案

 改好之後，需再按一次 "設計模式" 那個按鈕，回到 "非設計模式" 狀態，回到控制頁面，按我們做的「開始記錄」按鈕，程式才可以有作用。按「停止記錄」按鈕程式就會停止記錄，若再按「開始記錄」按鈕，它就會繼續記錄。提醒您，別忘了要儲存檔案。

當然，你的 DDE 一定要啟動，如果你不知道如何適當的設置 DDE，請參閱 DDE 提供者的文件，或詢問他們的客服中心。

 

關鍵程式碼：OnTime() method

 這個程式的關鍵是我們用 Application 這個物件(object) 的 OnTime 方法(method) 來排程 (schedule)。它的用法如下：

 

 

每次當我們呼叫它的時候，我們告訴它到了什麼時候就要執行我們指定的 "程序" (subroutine，在我們這裡專案的程式碼中就是 "抄錄")，而在那個 "程序" 裡，我們設計好了要再呼叫 OnTime 來指定下一次是要什麼時候執行這個 "程序"。在我的程式裡，我用了 Excel VBA 的 Public 變數來傳遞預約執行的時間，當然，我們也可以把資料放在儲存格裡傳遞，程式裡我故意都寫了。

[排程]那個參數，預設為 True，換言之，不寫的話就是要預約執行時間。如果要取消指定的預約，[排程]就要寫 "False"。需注意的是，當我們關掉這個活頁簿的時候，若 OnTime  已預約了下一次的執行，則時間到的時候它仍會執行 "程序"，除非我們將 Excel 關掉。

Application.OnTime 這個 method的語法是可以有四個參數 (parameters)，而我在這裡省略了第三個參數。我想你已經看出來，最後那個 True 或 False 是用來決定 "要預約" 或是 "取消預約"。也就是說，若是到了 "下次執行時間" 的時候就執行  "抄錄" (副程式) 或 取消先前的預約。第三個參數是一個時間值，這個時間的意思是，如果 "下次執行時間" 當時電腦太過忙碌，則 "最遲" 等到什麼時候？超過此時間還不能執行的話，就不要執行或取消了。而若省略第三個參數，Excel 就會繼續一直等，等到電腦有空執行或取消為止。

*Note 1：

(2018/11/29)
留言可能需等很久很久才會被發現，若有疑問請 email 給我 
way2cheng@gmail.com (但每天只看一次信箱) 

需要這個新版程式的朋友們，可以來信向我要xls檔。
來信主旨請寫明：「我要依固定時間抄錄DDE v2.5」
(我會寄 最新版 給你)

* Note 2：

陸續做了幾種不同的版本，寫成幾篇解說文章，請點選底下連結閱讀：

固定間隔時間記錄DDE傳入資料之「預約開關版」

記錄 DDE 傳入的 (每個) tick

抄錄 DDE OnCalculate 之預約開關版