vba - 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 的立即窗口来查看它的运行情况。