使用OnTime安排执行过程

使用OnTime安排执行过程

你可能需要设计Excel工作簿定期并自动地运行一个过程。例如，你可能想每隔几分钟从数据源中更新数据。使用VBA，你能执行Excel应用程序的OnTime方法指令Excel在给定的时间去运行某过程。通过编写代码使程序自已调用OnTime方法，能使VBA代码定期自动执行。本篇文章描述处理这种情况的VBA过程。

介绍
OnTime方法要求指定日期和时间以及要运行的过程作为参数，重要的是要记住具体地告诉Excel什么时候运行这个过程而不是从当前时间的偏差。为了取消一个未执行的OnTime过程，你必须经过该过程计划要运行的确切的时间，你不能够告诉Excel取消下一个计划执行的过程。因此，建议将安排过程开始运行的时间存放在一个公共的(或全局)变量中，该变量作用于所有的代码。然后，你能使用所存储时间的变量去安排运行或取消事件。下面的示例代码存储了所运行过程的名称和重复执行的时间间隔在公共的常量中，当然这不是必需的。
Public RunWhen As Double
Public Const cRunIntervalSeconds = 120   ' two minutes
Public Const cRunWhat = "The_Sub"

开始一个定时的过程
为开始这个过程，使用一个名为 StartTimer的子程序。代码如下：
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub
将比当前时间多两分钟的日期和时间存放在RunWhen变量中，然后调用OnTime方法指令Excel何时运行cRunWhat过程。
“The_Sub”是一个字符串变量，Excel将在合适的时间运行该过程。下面是该过程代码示例：
Sub The_Sub()
'
' your code here
'
StartTimer
End Sub
注意The_Sub子程序的最后一行调用了StartTimer过程，再次重复运行这个过程。并且当The_Sub过程下次被OnTime调用时，将再次调用StartTimer来重复执行它自已。这就是如何执行周期循环的方法。

停止一个定时的过程
有时，当关闭工作簿时或者满足某个条件时需要停止定时执行的过程。由于OnTime方法是Application对象的一部分，简单地关闭已创建事件的工作簿不会取消对OnTime的调用。一旦Excel自身保持运行，它将执行OnTime过程，并且在必要时会自动打开该工作簿。
为了停止OnTime过程，必须对OnTime方法指定确切的时间，这就是我们将时间作为公共的变量存放在RunWhen中的原因。否则，没办法知道过程计划执行的确切时间。(所计划的时间像OnTime方法中的一把“钥匙”，如果没有它，就没有通往事件的入口)
下面是一个名为StopTimer的子过程，它将停止要执行的OnTime过程。
Sub StopTimer()
  On Error Resume Next
  Application.OnTime earliesttime:=RunWhen, _
    procedure:=cRunWhat, schedule:=False
End Sub
这个子程序使用了和StartTimer子程序相同的OnTime语法，将schedule参数设置为False告诉Excel取消该过程的执行。你可能想在Auto_Close宏或Workbook_BeforeClose事件中包括一个对该过程的调用。在StopTimer子程序中，使用On Error Resume Next语句忽略当你企图删除一个不存在的过程时可能产生的任何错误。

使用Windows计时器
除了Excel的OnTime方法外，你能使用Windows API库提供的Timer函数。在某些情况下，使用API过程比使用OnTime方法更容易：第一，你告诉Windows需要计时器发生的时间间隔而不是某天的特定时间；第二，API过程将自动更新，计时器将每隔一段时间发生直到你告诉它停下为止。
这些过程需要在Office2000或更新的版本中运行，因为我们使用了AddressOf函数，他们不会在Excel97或更早的版本中运行。
为使用Windows计时器，将下面的代码放在一个标准代码模块中。
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)
  '
  ' The procedure is called by Windows. Put your
  ' timer-related code here.
  '
End Sub
执行StartTimer过程开始计时。变量TimerSeconds指明计时器之间间隔有多少秒。这个值可能少于1。注意SetTimer过程在毫秒范围内取值，于是当我们调用SetTimer时我们通过将TimerSeconds乘以1000来增加间隔值。Windows每隔一段计时器发生的时间来调用TimerProc过程。你可以将这个过程命名为你想要的名字，但你必须如例子中所示声明这些参数变量。如果你变换了过程的名称，那么要确保你也变换了SetTimer中的名字。Windows将传递下面的值到TimerProc过程：
HWnd Excel应用程序的Windows句柄。一般可忽略这个参数。
uMsg 值为275。一般可忽略这个参数。
nIDEvent 这个值通过SetTimer获得TimerID变量返回。如果你不止一次调用SetTimer，你能检查nIDEvent参数确定哪次调用SetTimer导致调用该过程。
dwTimer 计算机运行的毫秒数。相同的值通过GetTickCount Windows过程被返回。
调用EndTimer过程来停止计时器循环，这个过程调用KillTimer，通过SetTimer返回的值传递给它。
API计时器和Excel的Ontimer过程间有两个重要的区别：第一，API计时器有着更精确的时间间隔(达到1秒或更少)；第二，即便Excel处在编辑模式(也就是说，当你正在编辑单元格时)，API计时器也将执行。注意，当Excel处于编辑模式时，如果TImerProc企图修改工作表单元格，Excel将会立即退出。(By fanjy in 2006-6-18)

附：原文
Scheduling Procedures With OnTime

You may need to design your Excel workbooks to run a procedure periodically, and automatically. For example, you may want to refresh data from a data base source every few minutes. Using VBA, you can call upon the OnTime method of the Excel Application object to instruct Excel to run a procedure at a given time.   By writing your code to call the OnTime method by itself, you can have VBA code automatically execute on a periodic basis. This page describes the VBA procedures for doing this.

Introduction
As arguments, the OnTime method takes a specific date and time, and a procedure to run. It is important to remember that you tell Excel specificially when to run the procedure, not an offset from the current time. In order to cancel a pending OnTime procedure, you must pass in the exact time that the procedure is scheduled to run. You can't tell Excel to cancel the next scheduled procedure. Therefore, it is advisable to store the time that the procedure is schedule to run in a public (or global) variable, which is available to all your code. Then, you can use the time stored in that variable to schedule or cancel the event. The example code will also store the name of the procedure to run and the reschedule interval in public constants, although this is not required.
Public RunWhen As Double
Public Const cRunIntervalSeconds = 120   ' two minutes
Public Const cRunWhat = "The_Sub"
Starting A Timer Process
To start the process, use a procedure called StartTimer, similar to the code shown below.
Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
  schedule:=True
End Sub
This stores the date and time two minutes from the current time in the RunWhen variable, and then calls the OnTime method to instruct Excel when to run the cRunWhat procedure.
Since is a string variable containing "The_Sub", Excel will run that procedure at the appropriate time. Below is a sample procedure:
Sub The_Sub()
'
' your code here
'
StartTimer

End Sub
Note that the last line of The_Sub calls the StartTimer procedure. This reschedules the procedure to run again. And when the The_Sub procedure is called by OnTime the next time, it will again call StartTimer to reschedule itself. This is how the periodic loop is implemented.
Stopping A Timer Process
At some point, you or your code will want to stop the timer process, either when the workbook is closed or when some condition is met.   Because the OnTime method is part of the Application object, simply closing the workbook which created the event will not cancel a call to OnTime. As long as Excel itself remains running, it will execute the OnTime procedure, opening the workbook if necessary.
To stop an OnTime procedure, you must pass the exact scheduled time to the OnTime method. This is why we stored the time in the RunWhen public variable . Otherwise, there would be no way of knowing exactly what time the process was schedule for. (The scheduled time works like a "key" to the OnTime method. Without it, there is no way to access that event.)
Below is a procedure called StopTimer which will stop the pending OnTime procedure.
Sub StopTimer()
  On Error Resume Next
  Application.OnTime earliesttime:=RunWhen, _
    procedure:=cRunWhat, schedule:=False
End Sub
This procedure uses the same OnTime syntax used in the StartTimer procedure, except that it has the schedule parameter set to False, which tells Excel to cancel the procedure.   You may want to include a call to this procedure from the Auto_Close macro or Workbook_BeforeClose event procedure. The StopTimer procedure uses an On Error Resume Next statement to ignore any error that might be generated if you attempt to cancel a non-existent procedure.
Using Windows Timers
In addition to Excel's OnTime method, you can use the Timer functions provided by the Windows API library. In some ways, using the API procedures are easier than OnTime. First, you tell Windows the interval at which you want to the timer to "pop" rather than a specific time of day. And next, the API procedure will automatically reschedule itself. The timer will "pop" every interval until you tell it to stop.
These procedures require that you are using Office 2000 or later, because we use the AddressOf function. They will not work in Excel 97 or earlier.
To use Windows timers, put the following code in a standard code module.
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)
  '
  ' The procedure is called by Windows. Put your
  ' timer-related code here.
  '
End Sub

Execute the StartTimer procedure to begin the timer. The variable TimerSeconds indicates how many seconds should be between timer "pops". This value may be less than 1. Note that the SetTimer procedure takes a value in milliseconds, so we multiply TimerSeconds by 1000 when we call SetTimer. The procedure TimerProc will be called by Windows every time the timer pops. You can name this procedure anything you want, but you must declare the argument variables exactly as shown in the example. If you change the name of the procedure, be sure to change the name in the call to SetTimer as well. Windows will pass the following values to the TimerProc procedure:
HWnd         The Windows handle of the Excel application. Generally, you can ignore this.
uMsg         The value 275. Generally, you can ignore this.
nIDEvent   The value returned by SetTimer to the TimerID variable. If you have made more than one call to SetTimer, you can examine the nIDEvent argument to determine which call SetTimer to resulted in the procedure being called.
dwTimer     The number of milliseconds that the computer has been running. This is the same value that is returned by the GetTickCount Windows procedure.
To stop the timer loop, call the EndTimer procedure. This procedure calls KillTimer, passing it the value returned by SetTimer.
There are two significant differences between the API timer and Excel's OnTimer procedure. First, the API timer is much more accurate at time intervals of 1 second or less. Second, the API timer will execute even if Excel is in Edit Mode (that is, when you are editing a cell). Note, though, that Excel will quit immediately if TimerProc attempts to modify a worksheet cell while Excel is in Edit Mode.