Passing a Date Argument to OnTime

[Jaafar Tribak]

It seems that passing a Date arg to the OnTime Method doesn't work... Any ideas ?

Option Explicit

Private dNextRun As Date

Private Sub Workbook_Open()
    RunPeriodicMacro Interval:=TimeValue("00:00:02")  'run every 2 secs.
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime dNextRun, "'" & Me.CodeName & ".RunPeriodicMacro " & 0 & "'", Schedule:=False
End Sub

Private Sub RunPeriodicMacro(ByVal Interval As Date) 
    dNextRun = Now + Interval 
    Debug.Print "NextRun @: ", dNextRun 
    Application.OnTime dNextRun, "'" & Me.CodeName & ".RunPeriodicMacro " & Interval & "'", Schedule:=True
End Sub

 

[RoryA]

Try enclosing the date arg in # symbols and make sure to format it US style.

 

[Jaafar Tribak]

Try enclosing the date arg in # symbols and make sure to format it US style.

Thanks but I couldn't figure out how to pass the litteral time value to the sub ... the compiler keeps changing the value.
I know the US date format is mm/dd/yy but I am not sure about the US time format .
Thanks.

 

[RoryA]

It should just be h:mm:ss format. Alternatively can you pass it as a straight Double?

 

[Jaafar Tribak]

It should just be h:mm:ss format. Alternatively can you pass it as a straight Double?

Initially, I declared the interval as a Double and it worked but, out of interest, I want to know why I can't make it work with a Date type argument.

BTW, the VBE automatically changes the argument #00:00:02# to #12:00:02 AM# for both as Double or as Date which is understandable because litteral time is not a time interval value it is an actual clock time. However, the resulting format is counter-intuitive

The following works without the need to use litteral '#' time because the argument is declared as Double.

Private Sub Workbook_Open()
    RunPeriodicMacro Interval:=TimeValue("00:00:02")
End Sub

Private Sub RunPeriodicMacro(Interval As Double)
    dNextRun = Now + Interval
    Debug.Print "NextRun @: " & vbTab & dNextRun
    Application.OnTime dNextRun, "'" & Me.CodeName & ".RunPeriodicMacro " & Interval & "'", Schedule:=True
End Sub

Declaring the argument as Date type still doesn't work for me even when enclosing it in # symbols.

 

[RoryA]

This works for me:

Option Explicit

Private dNextRun As Date

Private Sub Workbook_Open()
    RunPeriodicMacro Interval:=TimeValue("00:00:02")  'run every 2 secs.
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime dNextRun, "'" & Me.CodeName & ".RunPeriodicMacro " & 0 & "'", Schedule:=False
End Sub

Private Sub RunPeriodicMacro(ByVal Interval As Date)
    dNextRun = Now + Interval
    Debug.Print "NextRun @: ", dNextRun
    Application.OnTime dNextRun, "'" & Me.CodeName & ".RunPeriodicMacro #" & Interval & "#'", Schedule:=True
End Sub

 

[Jaafar Tribak]

Yes! That works.

I was actually enclosing the argument when calling the sub:
RunPeriodicMacro Interval:=#12:00:02 AM#
When passed to the argument variable, the "#" symbols are removed that's why it didn't work.

Thank you for your help.