VBA中使用计时器的两种方法

VBA程序运行中，你可能需要自动执行某些代码或者定时执行某些计划任务，如自动保存，这就要使用定时器来完成。这里水文工具集给出VBA中使用计时器的两种方法，其一是使用Application对象的OnTime方法来运行任务，其二是通过Windows API函数来完成，具体实例如下：

一、采用Application.OnTime实现计时器的方式

'================================
' VBA采用Application.OnTime实现计时器
'
' 
'================================
Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "TheSub"  ' the name of the procedure to run
Sub StartTimer()
    RunWhen = Now + TimeSerial(0,0,cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub
Sub TheSub()
    StartTimer  ' Reschedule the procedure
End Sub
Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen,Procedure:=cRunWhat, _
        Schedule:=False
End Sub

二、采用Windows API函数实现计时器的方式

'================================
' VBA采用Windows API实现计时器
'
' 
'================================
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 TimerID As Long
Public TimerSeconds As Single

Sub StartTimer()
    TimerSeconds = 1 ' how often to "pop" the timer.
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
    On Error Resume Next
    KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
        ByVal nIDEvent As Long, ByVal dwTimer As Long)
    
    ''''''
    ' This procedure is called by Windows. Put your
    ' code here.
    ''''''
End Sub