VBA 針對Excel中無Time元件的替代方法

範例1: myTimer.rar

範例2: myTimer2.rar


VBA 針對Excel中無Time元件的替代方法
我們有時候需要每隔一段時間就去執行一段程序，例如顯示時間。這時候要用到計時器，在VBA我們可以用SetTimer及KillTimer兩個API或用OnTime方法來做。
下面例子在作用中的工作表A1顯示現在時間
1.使用SetTimer、KillTimer
Private Declare Function SetTimer Lib "user32" (ByVal hWnd As _
                                                Long, ByVal nIDEvent As Long, ByVal uElapse As Long, _
                                                ByVal lpTimerFunc As Long) As Long

Private Declare Function KillTimer Lib "user32" (ByVal hWnd As _
                                                 Long, ByVal nIDEvent As Long) As Long
Dim hTimer
Sub TimerProc()
    Range("A1") = Format(Now, "hh:mm:ss AM/PM")
End Sub
Sub Init(Interval&)
    hTimer = SetTimer(0, 0, Interval, AddressOf TimerProc)
End Sub
Sub Terminate()
    Call KillTimer(0, hTimer)
End Sub
 Sub beginTest()
    Init 1000
End Sub2.使用Application.OnTime
Public NextTime As Date
Sub UpdateTime()
    ActiveSheet.Range("a1") = Time
    NextRun
End Sub
Sub NextRun()
    NextTime = Now + 1 / 86400
    Application.OnTime NextTime, "UpdateTime"
End Sub
Sub BeginShow()
    UpdateTime
End Sub
Sub endShow()
    Application.OnTime NextTime, "UpdateTime", schedule:=False
End Sub