網路城邦
上一篇 回創作列表 下一篇   字體:
[Excel VBA] 固定間隔時間記錄DDE傳入資料之「預約開關版」
2019/06/18 23:31:11瀏覽2121|回應1|推薦2

這篇文章要介紹的這個程式主要用途是「把 (股市看盤) DDE 帶進 Excel 的資料每隔 x 分鐘記錄一次」曾有朋友使用我寄給他的 "依固定時間抄錄DDE v2.5" 之後,來信希望能有預約何時開始的功能,於是做了這個版本。可以設定從何時開始,依照固定間隔時間把DDE傳入 Excel 工作表儲存格的資料抄錄到另一張工作表去,到了設定停止的時間就不再繼續抄錄。

在這篇文章裡,我不詳述關於如何能依照固定間隔抄錄,請參閱我另一篇文章:[Excel VBA] 固定間隔時間記錄DDE傳入的資料。本文將解說的是關於它設定開始時間、停止時間的邏輯與控制方法。

操作功能概述

(圖1-0 初始狀態)

如圖 1-0,每當這個活頁簿檔案被開啟,裡面的巨集被允許執行的時候,Control 工作表的 "本次執行時間" 和 "下次執行時間" 會被清除,"預設開始時間" 會被設為當時的 10 分鐘後,"預設結束時間" 會被設為當時的 1 小時又 10 分鐘後,這些是我在程式裡寫好的。把 "預設開始時間" 和 "預設結束時間" 改成自己想希望的時間,每次抄錄間隔多久,開始要抄錄到 Record 工作表的第幾列,這三個儲存格確定之後,按下右上角「設定開始/結束時間」鈕,頁面上預約的狀態顯示 "已預約","本次執行時間" 會顯示何時按下按鈕的, "下次執行時間" 就是預約要開始執行抄錄的持時間。右上角那個按鈕的功能則變成「取消預約(停止記錄)」,如圖1-1。

(圖1-1 設定預約後)

然後,這個活頁簿檔案要保持開著,提供DDE資訊的看盤程式也要開著,到了預約的開始時間,就會看到工作表顯示抄錄的狀態。如圖1-2。

(圖1-2 抄錄進行中)

到了預設結束時間,"本次執行時間" 會顯示結束前最後一次執行抄錄的時間,"下次執行時間" 會顯示空白,按鈕會恢復為「設定開始/結束時間」鈕,預約狀態恢復為 "無預約"。若是在尚未到預設結束時間按下「取消預約(停止記錄)」鈕,則 "本次執行時間" 會顯示按鈕的時間,如圖1-3。

(圖1-3 按下「取消預約(停止記錄)」鈕後)

抄錄開始與結束的控制

這套程式包括三個副程式:
  • Wookbook_Open():程式的初始值設定
  • CommandButton1_Click()Control 工作表右上那顆按鈕的功能,包括設定預約與取消預約
  • (在 module1 中) procRecord():執行抄錄,並且預約下一次執行抄錄。
依固定時間抄錄的基本原理是利用 Excel VBA 的 Application.OnTime 方法 (method),CommandButton1_Click() 副程式利用 Application.OnTime 預約第一次要執行抄錄副程式 procRecord() 的時間,在v2.5版中,這個時間就是按鈕的當下,在這個預約開關版裡,這個時間就是我們指定的 "開始時間"。

當預約的時間到的時候,系統時計就呼叫 procRecord() 來執行抄錄。然後 procRecord() 按照設定好的間隔時間,透過 Application.OnTime 預約它自己下一次要執行的時間。然後,它自己每次都會替自己預約下一次的執行,直到 "結束時間" 到的時候,它就不再預約下一次。

這套程式採用一個布林 (boolean) 型別的公共變數(Public Variable) bScheduled 來表示預約的狀態,讓三個副程式都可以讀取做邏輯判斷,與改變它的值來控制程式的流程。底下在Workbook_Open() 裡可以看到,因為活頁簿檔案剛開啟還沒預約,所以把 bScheduled 的初值設為 False (習慣刻意寫這句,其實 VBA boolean 變數的預設值為 False)。

(圖2 Workbook_Open() 程式碼)

在 module1 最上面宣告公共變數的地方可以看到,我用了兩個 Date 型態的公共變數:dScheduledStartTime、dScheduledStopTime 來記住設定的 "開始時間" 和 "結束時間",讓 procRecord() 被執行的時候可以取得。CommandButton1_Click() 按鈕副程式會把 Control 工作表儲存格裡的 "開始時間" 和 "結束時間" 存入這兩個變數。當 procRecord() 執行的時候,判斷是不是已經過了 "結束時間",如果時間還沒過 (Now() < dScheduledStopTime ) 就執行抄錄,然後計算下一次應該何時執行(dNextTime),若下一次應該執行的時間沒有超過 "結束時間" 就預約下一次:

        Application.OnTime dNextTime, "procRecord"

若下一次應該執行的時間會超過 "結束時間",則不預約下一次,並且把 bScheduled 變數值改成 False,表示接下來是 "無預約" 的狀態。在圖3的程式碼裡,你會看到此時也會將CommandButton1上面自字 (caption) 改成「設定開始/結束時間」,因為現在是 "無預約" 的狀態接下來可已再次設定開始/結束時間,當然,再次按鈕的時候,Control 工作表 "預約開始時間" 和 "預約結束時間" 必須合乎邏輯。

(圖3 procRecord() 程式碼)

時間邏輯

關於 "時間",這個程式有以下幾個考慮:

  • 兩次抄錄的 "間隔時間" 不小於一分鐘:前一版就已有做這樣的限制,這是依據經驗,間隔太短比較容易出問題。若你實際需要更短的間隔,可以試著把CommandButton1_Click() 程式碼(如圖4) 裡面最開頭的一段 If ... ... Else 刪掉,或者乾脆用我寫的 OnCalculate 版。
  • 現在時間<開始時間<結束時間:設定的結束時間必須比開始時間晚,開始時間必須比按鈕被按下的當時時間晚。這三個時間的關係必須符合這樣的邏輯,否則就認定使用者要求設定的時間有誤。
  • 預約開始的時間不要太接近按鈕的時候:在無預約的狀態按鈕被按下的時候,程式會以 Application.OnTime 方法預約 procRecord 的第一次執行,而若剛剛才預約,所設的時間 dScheduleStartTime 立刻就到了,可能會產生一些問題,因此在 CommandButton1_Click() 中有一段程式碼是先產生一個距離當時至少 30 秒的時刻,若  dScheduleStartTime 的間隔比這個時間近,則以此時刻為 Application.OnTime 方法預約 procRecord() 的第一次執行(開始時間) 。 

圖4

(圖4 CommandButton1_Click() 程式碼)

按鈕功能的切換

面談到過,CommandButton1 的初始功能是用來「設定開始/結束時間」,當它被按下之後,bScheduled 變數值被設為 True,表示是已有預約,並將按鈕改為「取消預約(中止抄錄)」的功能。而當 procRecord() 每次執行之後會判斷若要預約下一次,則下一次是否已經超過結束時間,若是會超過結束時間,則將 bScheduled 變數值設為 False,表示 "無預約",將整個程式恢復為初始狀態,而將 CommandButton1 恢復為「設定開始/結束時間」的功能。

CommandButton1_Click() 裡面可以看到,當按紐被按下的時候,程式是以 If 敘述句判斷 bScheduled 變數值,而來決定按鈕當時的功能。在 "已預約" (bScheduled 為 True) 的狀態,就要呼叫 Application.OnTime 方法取消的 procRecord() 預約 (抄錄自然也就不會繼續下去了),寫法就像預約的時候一樣,只是末尾要有一個參數值為 "False" (表示要做的是 "取消"):

        Application.OnTime dNextTime, "procRecord", ,False

特別提醒注意的是,必須告訴 Application 方法所要取消的是 "何時"的預約,這就是我安排一個公共變數 dNextTime 的原因,因為公共變數不僅是可讓各程式模組存取/修改,在VBA 程式執行的全程它都不會消失,所以此時 dNextTime 變數記著上次呼叫 Application.OnTime 方法預約要讓 procRecord() 執行的時間。

PS. Application.OnTime 方法的用法,請參閱官方文件:

https://docs.microsoft.com/zh-tw/office/vba/api/excel.application.ontime

*Note

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

需要這個新版程式的朋友們,可以來信向我要xls檔。
來信主旨請寫明:「我要依固定時間抄錄DDE v2.6 預約開關版

本文相關文章連結如下:

[Excel VBA] 固定間隔時間記錄DDE傳入的資料

[Excel VBA] 記錄 DDE 傳入的 (每個) tick

[Excel VBA] 抄錄 DDE OnCalculate 之預約開關版

[Excel VBA]如何把個股每5分鐘的成交價格記錄下來?

( 知識學習其他 )
回應 推薦文章 列印 加入我的文摘
上一篇 回創作列表 下一篇

引用
引用網址:http://classic-blog.udn.com/article/trackback.jsp?uid=WayCheng&aid=127542618

 回應文章

【無★言】家喻戶曉的中國人
等級:8
留言加入好友
2019/06/19 07:58
「需要這個新版程式的朋友們,可以來信向我要xls檔」

不是xlsm檔?
ThisIsTheWay(WayCheng) 於 2019-06-25 19:01 回覆:

謝謝您的詢問,這是很好的一個問題。

Excel 2007開始用 .XLSM 儲存含有巨集的活頁簿,不含巨集的活頁簿檔案則用 .XLSX 我現在都是用 Excel 2007 寫 VBA,我會儲存成 2003 版的 .XLS 檔寄給大家,避免用舊版軟體的讀者無法使用。
使用新版軟體的讀者收到檔案後,可以打開檔案,另存成 .XLSM 檔。