OnTime函数混淆

Question

我在下面写了VBA代码，每5秒执行一次，但它不起作用。

Option Explicit

Public RunWhen As Double
Public Const cRunIntervalSeconds = 5 ' two minutes
Public Const cRunWhat = "TheSub"  ' the name of the procedure to run

Sub StartTimer()
    RunWhen = Now + TimeValue(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub


Sub TheSub()
    ''''''''''''''''''''''''
    ' Your code here
    ''''''''''''''''''''''''
    MsgBox "hi this is working"
    StartTimer  ' Reschedule the procedure
End Sub

我在这里错过了什么？

另外，下面这样的简单表达式也不起作用

Sub test()
    Application.ontime (now() + TimeValue("0:0:5")),"test"
End Sub

Answer 1

您应该使用TimeSerial函数，而不是TimeValue。

Option Explicit

Public RunWhen As Double
Public Const cRunIntervalSeconds = 5 ' two minutes
Public Const cRunWhat = "TheSub"  ' the name of the procedure to run

Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat
End Sub


Sub TheSub()
    ''''''''''''''''''''''''
    ' Your code here
    ''''''''''''''''''''''''
    Debug.Print "hi this is working"
    StartTimer  ' Reschedule the procedure
End Sub

这是可行的，您可以查看VBE的即时窗口来查看它的运行情况。