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.
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
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 dTime
will be used to cancel the scheduled task as was used to create it.
If no further task has been scheduled i.e. by an error in MacroTimeTest then the Workbook close event will handle the error.
To see the debug output look at the immediate window in the VBA Editor (Ctrl+G)
精彩评论