VBA 准时取消调度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1674467/
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
VBA ontime cancel scheduling
提问by
I have written a macro that runs at 15:30pm every workday when a workbook is first opened. When the workbook is closed it tries to open itself the next time the macro is scheduled to run. I have tried to turn the scheduler to false and am getting an error. Code below. Has anyone any ideas why this isn't working?
我编写了一个宏，该宏在每个工作日第一次打开工作簿时的下午 15:30 运行。当工作簿关闭时，它会在下一次计划运行宏时尝试打开自己。我试图将调度程序设置为 false，但出现错误。代码如下。有没有人知道为什么这不起作用？
Thanks
谢谢
Private Sub Workbook_Open()
Application.OnTime TimeValue("15:30:00"), "MacroTimeTest"
End Sub
public dtime as date
Sub MacroTimeTest()
dtime = (Format(Application.Evaluate("workday(today(), 1)"), "DD/MM/YY") & " " & TimeValue("15:30:00"))
'other code has been deleted doesn't affect dtime variable
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'I have tried replacing false with 0 etc but it didn't make a difference
Application.OnTime earliesttime:=dtime, procedure:="MacroTimeTest", schedule:=False
End Sub
回答by pjp
I think that you should keep a reference to the time so that you can cancel the action. You can only cancel an action if it hasn't already executed.
我认为您应该保留对时间的引用，以便您可以取消操作。您只能取消尚未执行的操作。
In ThisWorkbookenter the following to run the macro at 15:59 until the sheet is closed
在ThisWorkbook输入以下内容以在 15:59 运行宏，直到工作表关闭
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo CouldNotCancel
Application.OnTime dTime, "MacroTimeTest", , False
Debug.Print "Cancelled task to run at " & dTime
Debug.Print "Workbook close"
Exit Sub
CouldNotCancel:
Debug.Print "No task to cancel"
End Sub
Private Sub Workbook_Open()
Debug.Print "Workbook open"
dTime = TimeValue("15:59:00")
Debug.Print "Next run time " & dTime
Application.OnTime dTime, "MacroTimeTest"
End Sub
Then add your macro to a Module
然后将您的宏添加到模块
Option Explicit
Public dTime As Date
Public Sub MacroTimeTest()
'schedule next run
dTime = TimeValue("15:59:00")
'schedule next run
Debug.Print "Scheduling next run at " & dTime
Application.OnTime dTime, "MacroTimeTest"
Debug.Print "Running macro"
End Sub
This way the same value of dTimewill be used to cancel the scheduled task as was used to create it.
这样，dTime将使用与创建计划任务相同的值来取消计划任务。
If no further task has been scheduled i.e. by an error in MacroTimeTest then the Workbook close event will handle the error.
如果没有安排进一步的任务，即 MacroTimeTest 中的错误，则工作簿关闭事件将处理该错误。
To see the debug output look at the immediate window in the VBA Editor (Ctrl+G)
要查看调试输出，请查看 VBA 编辑器中的即时窗口 (Ctrl+G)
