澳门美高梅娱乐城

应用OnTime部署履行进程

作者:admin 发布时间:2017-06-21
你可能需要设计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.