VBA准时取消计划 [英] VBA ontime cancel scheduling

问题描述

我编写了一个宏，该宏在工作簿首次打开时的每个工作日下午15:30运行.当工作簿关闭时，它将尝试在下次安排宏运行时自行打开.我试图将调度程序设置为false，但出现错误.下面的代码.有谁知道为什么这行不通?

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?

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

推荐答案

我认为您应该保留对时间的引用，以便您可以取消操作.您只能取消尚未执行的操作.

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.

在 ThisWorkbook 中输入以下内容以在15:59运行宏，直到关闭工作表

In ThisWorkbook enter the following to run the macro at 15:59 until the sheet is closed

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

然后将宏添加到模块

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

通过这种方式，将使用与创建任务相同的 dTime 值来取消计划任务.

This way the same value of dTime will be used to cancel the scheduled task as was used to create it.

如果尚未计划进一步的任务(例如，由于MacroTimeTest中的错误)，则工作簿关闭事件将处理该错误.

If no further task has been scheduled i.e. by an error in MacroTimeTest then the Workbook close event will handle the error.

要查看调试输出，请在VBA编辑器(Ctrl + G)的立即窗口中查看

To see the debug output look at the immediate window in the VBA Editor (Ctrl+G)

这篇关于VBA准时取消计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！