Contents ...
udn網路城邦
[Excel VBA] 固定間隔時間記錄DDE傳入的資料
2018/04/24 19:13
瀏覽1,966
迴響1
推薦3
引用0

(20181126日更新,原發表2018 424日下午)

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 會按照它自己的預設名稱建立工作表,請注意將我以下程式做相對應的修改) 例如下圖,是把三個商品標的的名稱、成交價、成交量分別傳入ABC欄的234列:

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

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

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

在「控制頁」Control工作表的A1A2A3A5 儲存格打些字,如下圖,程式將要把每次執行記錄的時間顯示在 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)。它的用法如下:

 

Application.OnTime(最早執行時間, 程序, [最遲執行時間], [Schedule])

 

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

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

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

*Note

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

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

有誰推薦more
迴響(1) :
1樓. Bill
2018/11/22 19:01

您好,想跟您索取最新[Excel VBA] 固定間隔時間記錄DDE傳入的資料

程式碼,謝謝

(tman1520@gmail.com)

Bill,

麻煩您寫信到我的 email 信箱: 

way2cheng@gmail.com

通常是在晚上的時候,所有的來信我會一起回覆。

謝謝!

Way Cheng 敬覆

ThisIsTheWay2018/11/29 09:48回覆
發表迴響

會員登入