为什么我的 application.ontime 在 if 语句中不起作用？VBA

Question

我有一个申请。像下面这样的在线例程。

Sub timer()
If Hour(Time) <= 15 Or Hour(Time) >= 22 Then
    On Error Resume Next
    Application.OnTime EarliestTime:=ThisWorkbook.Worksheets("Sheet1").Range("K5"), Procedure:="dataextract", Schedule:=False
    On Error GoTo 0
    ThisWorkbook.Worksheets("Sheet1").Range("K5") = Now + TimeSerial(0, WorksheetFunction.Floor(Minute(Now), 5) + 5 - Minute(Now), 1 - Second(Now))
    Application.OnTime EarliestTime:=ThisWorkbook.Worksheets("Sheet1").Range("K5"), Procedure:="dataextract", Schedule:=True
End If

End Sub

我希望这个例程在day 0上的晚上10点到day 1上的下午4点之间运行，然后在day 1上的晚上10点到day 2上的下午4点自行重启，以此类推。代码在下午4点停止，但不会在晚上10点重新启动。正如你所看到的，我从来没有打开schedule:=false，所以我不明白为什么它不重启…?

Answer 1

我确定我在这里犯了一个错误，但是你应该明白…

Sub timer()
    Const PROC As String = "dataextract"
    Const INTV_MIN As Long = 5
    Dim rTime As Range
    Set rTime = ThisWorkbook.Worksheets("Sheet1").Range("K5")
    On Error Resume Next
    Application.OnTime EarliestTime:=rTime.Value, Procedure:=PROC, Schedule:=False
    On Error GoTo 0
    If Hour(Time) <= 15 Or Hour(Time) >= 22 Then
        '5min from now
        rTime.Value = Now + TimeSerial(0, _
                        WorksheetFunction.Floor(Minute(Now), INTV_MIN) + INTV_MIN - Minute(Now), _
                        1 - Second(Now))
    Else
        '10pm today
        rTime.Value = Date + TimeSerial(22, 0, 0)
    End If
    Application.OnTime EarliestTime:=rTime.Value, Procedure:=PROC, Schedule:=True
End Sub