vba 如何制作一个在Excel中定期执行的宏？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/211715/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make a macro which executes periodically in Excel?
提问by supermedo
How does one execute some VBA code periodically, completely automated?
如何定期、完全自动化地执行一些 VBA 代码？
回答by Joe
You can use Application.OnTime to schedule a macro to be executed periodically. For example create a module with the code below. Call "Enable" to start the timer running.
您可以使用 Application.OnTime 来安排要定期执行的宏。例如，使用下面的代码创建一个模块。调用“启用”以启动计时器运行。
It is important to stop the timer running when you close your workbook: to do so handle Workbook_BeforeClose and call "Disable"
关闭工作簿时停止计时器运行很重要：这样做处理 Workbook_BeforeClose 并调用“禁用”
Option Explicit
Private m_dtNextTime As Date
Private m_dtInterval As Date
Public Sub Enable(Interval As Date)
Disable
m_dtInterval = Interval
StartTimer
End Sub
Private Sub StartTimer()
m_dtNextTime = Now + m_dtInterval
Application.OnTime m_dtNextTime, "MacroName"
End Sub
Public Sub MacroName()
On Error GoTo ErrHandler:
' ... do your stuff here
' Start timer again
StartTimer
Exit Sub
ErrHandler:
' Handle errors, restart timer if desired
End Sub
Public Sub Disable()
On Error Resume Next ' Ignore errors
Dim dtZero As Date
If m_dtNextTime <> dtZero Then
' Stop timer if it is running
Application.OnTime m_dtNextTime, "MacroName", , False
m_dtNextTime = dtZero
End If
m_dtInterval = dtZero
End Sub
Alternatively you can use the Win32 API SetTimer/KillTimer functions in a similar way.
或者，您可以以类似的方式使用 Win32 API SetTimer/KillTimer 函数。
回答by Adam Davis
There is an application method that can be used for timing events. If you want this to occur periodically you'll have to 'reload' the timer after each execution, but that should be pretty straightforward.
有一种应用方法可以用于计时事件。如果您希望这种情况定期发生，您必须在每次执行后“重新加载”计时器，但这应该非常简单。
Sub MyTimer()
Application.Wait Now + TimeValue("00:00:05")
MsgBox ("5 seconds")
End Sub
-Adam
-亚当
回答by Fionnuala
You could consider the Windows Task Scheduler and VBScript.
您可以考虑使用 Windows 任务计划程序和 VBScript。
回答by CABecker
I do this all the time, I used to use the "OnTime" method as shown above but it renders the machine you are running the code on useless for other things, because Excel is running 100% of the time. Instead I use a modified hidden workbook and I execute with windows Task Scheduler and in the thisworkbook workbook_open function call the macro from your personal workbook, or open and run another workbook with the code in it. After the code has run you can call the application.quit function from the hidden workbook and close ecxel for the next run through. I use that for all my on 15 minute and daily unattended reporting functions.
我一直这样做，我曾经使用如上所示的“OnTime”方法，但它会使您正在运行代码的机器对其他事情无用，因为 Excel 100% 的时间都在运行。相反，我使用修改后的隐藏工作簿，并使用 Windows 任务计划程序执行，并在 thisworkbook workbook_open 函数中调用您个人工作簿中的宏，或者打开并运行另一个包含代码的工作簿。代码运行后，您可以从隐藏的工作簿调用 application.quit 函数并关闭 ecxel 以进行下一次运行。我将它用于我所有的 15 分钟和每日无人值守报告功能。
