43 thoughts on “The OnTime Method”

1. People in forums often ask (especially when coming from VB to VBA) where the Timer control is in Excel VBA. I normally stear them toward the Application.OnTime procedure, but sometimes it seems to not work as expected. I wonder if it’s because, as you mentioned, some other processes were still running and it was waiting its turn.

2. With regards to Application.OnTime Now, as Dick wrote it will run, so as soon as the current procedure is finished.
   It’s an interesting method. The following code would Reboot the current workbook.

   Sub RebootMe()
   Application.OnTime Now, “OpenMe”
   ThisWorkbook.Close SaveChanges:=False
   End Sub

   Private Sub OpenMe()
   Workbooks.Open ThisWorkbook.FullName
   End Sub

3. I am trying to print an Excel worksheet at midnight each night. I need to leave the workbook open all the time because it is pulling OPC data from another application.

   My script works the first time I call OnTime(), how do I get it to run again for the next day.

   Here is what I have so far.

   Sub OnTime()
   Application.OnTime (#12:01:00 AM#), “Print”

   End Sub

   Sub Print()
   Workbooks(“Test.xls”).Worksheets(“Sheet1?).Printout
   Call OnTime

   End Sub

4. Chuck,

   The trick is to write a subroutine that activates itself each time. This subroutine will restart each second:

   Option Explicit

   Dim NextTick

   Sub update()
   ‘ Call your application here

   ‘ Set up the next event one second from now
   NextTick = Now + TimeValue(“00:00:01?)
   Application.OnTime NextTick, “update”
   End Sub

   Getting it to shut up:

   Sub StopChart()
   ‘ Cancels the OnTime event (stops the clock)
   On Error Resume Next
   Application.OnTime NextTick, “update”, , False

   End Sub

5. I need help about the application on time.
   I need to run a formula every last day of the month
   Is there anyway to make this run without opening the Excel.
   Thanks
   Alice

6. without opening excel? i do think so. Look at task Schedules within windows. – maybe with a on open even in you ss, and some time/date cheching just in case?

7. Am having truoble stopping a procedure once started. here is what i have so far.

   Sub StartTimer()
   Application.OnTime Now, “Update”
   End Sub

   Sub EndTimer()
   Application.OnTime EarliestTime:=Now, _
   Procedure:=”Update”, Schedule:=False
   End Sub

   Sub Update()
   Application.Workbooks(1).Worksheets(1).Cells(6, 15).Value = Application.Workbooks(1).Worksheets(1).Cells(6, 15).Value + 1
   Application.OnTime Now + TimeValue(“00:00:01?), “Update”
   End Sub

8. Gareth,

   When you run EndTimer, the time you supply should be the exact same time that you supplied to StartTimer.
   What you are doing is supplying Now() to both, which will be different by the time EndTimer runs.

   You’ll need store Now() in a global variable and use that for both StartTimer and EndTimer.

   I have an example on my website: Schedule a macro to run.

   Cheers,
   Rob

9. Can you specify an argument after the procedure?
   Thank you!

10. I assigned a ontime macro to a shape, it will run normally once shape is clicked. but if click many
    times on the shape,then the scheduled time become uncontrollable ! Anyone knows how to prevent this from
    happening?

11. Hi

    I am using application.ontime function but I have a problem.

    When I schedule an event it starts the Procedute more then once. Sometimes it doesnt stop and again and again restarts it.

    Can it be because of the reloading of Form that the application.ontime function code is written..Lets say the code is in MAINMENU so after the macro is started the MainMenu.Frm is accessed lots of times and may be it is scheduling the same Procedure everytime the MainMenu is started? THis i silly but I cant really figure out why it doesnt function properly.

    Private Sub UserForm_Activate()

    If seansuyari = 1 Then Application.OnTime TimeValue(“09:29:00?), “seans1?

    End sub

12. Just a note on that logic:

    Private Sub UserForm_Activate()
    If seansuyari = 1 Then Application.OnTime TimeValue(“09:29:00?), “seans1?
    End sub

    TimeValue() will return a value of that time, with the date of Jan 01, 1900. Every time you call this, you’re scheduling a task to run over a century ago. From what you’ve said, I’m guessing it’s just queueing them up, and trying to execute them sequentially.

    It may sounds trite, but most of the issues on this thread are RTFM material.

13. It’s been a while since Max posed the question about parameters to the procedure specified in the OnTime method.

    See
    Using procedures with arguments in non-obvious instances:
    macros associated with shapes, forms, and commandbar elements, and called by the OnTime and OnKey methods
    http://www.tushar-mehta.com/excel/vba/xl%20objects%20and%20procedures%20with%20arguments.htm

14. Is there a way to use OnTime to schedule a procedure with passing parameters? For example: MyProc(Counter).

15. Any reply to Byron’s email?

    Tushar, i had a look at your website (and a very good one it is, too), but it only mentions passing static arguments, eg, you can only call myProc(7) rather than myProc(Counter) where Counter = 7

16. Byron, Masum: As I wrote in my reply to Masum’s email query, no, I don’t believe so. At the time it processes the OnAction specification, Excel doesn’t know anything about the VBA variables you may have declared.

17. this pasge was kinda idle…hopefully i’ll get an answer..

    im tryin to use the on time method…
    it simply doesn;t work.

    the sub is printing something out. some days it works, some days it doesn’t.
    makes me crazy…

    any ideas?

18. I found this but I can’t get it to work.

19. Sorry. Here’s the website. http://www.thecodenet.com/articles.php?id=10

20. Hi,
    When use OnTime method, how to specify a time of a particular day to call a procedure “Schedule”?

    For example, I created the following.
    Application.OnTime EarliestTime:=TimeValue(“8/19/2007 01:01:06?), _
    Procedure:=”Schedule”

    On 08/18/2007, 01:00:05, I executed the above. Since I specified that the OnTime method to be run on 08/19/2007 01:01:06, I was not expecting nothing to happen at 08/18/2007 01:01:06. However, the excel macro actually run at 08/18/2007 01:01:06, and procedure “Schedule” was executed.

    What should I do in order to make excel macro to call 08/19/2007 01:01:06, not at 08/18/2007 01:01:06?
    Thanks.
    8/18/2007 1:43am

21. id: Use datevalue()+timevalue() That will definitely create the date+time you want. Whether OnTime will honor the date specification is something I don’t know.

    For obvious reasons I don’t intend to test my suggestion. {grin}

22. Tushar, it works. Thanks. I did a test with the following:
    Application.OnTime DateValue(“8/22/2007?) + TimeValue(“02:30:06?), “Schedule”
    Application.OnTime DateValue(“8/23/2007?) + TimeValue(“00:01:06?), “Schedule”

    The code work perfectly.

23. Hello,

    I’m really struggling with getting the application.ontime command to work for me.

    I have

    sub nextorders()
    startTimer
    end sub

    sub startTimer()
    Application.OnTime Now + TimeValue(“00:00:2”), “nextorders”
    end sub

    It seems like the application.ontime command doesn’t actually go back to the beginning of the nextorders sub.

    I really appreciate your help.

    Bill

24. Bill: It works fine for me as posted.

25. In “This Workbook”, I have the code:
    Private Sub Workbook_Open()
    Application.OnTime TimeValue(“07:46:00?), “MakeSyncRequest”
    End Sub

    and in a module I have the code:
    Public Sub MakeSyncRequest()
    Application.OnTime TimeValue(“07:46:00?), “MakeSyncRequest”
    ‘ my code here
    End Sub

    however, this doesn’t work – at least not consistently. Any ideas? Thanks in advance…

26. Any ideas?…

    I am attempting to use OnTime to run a subroutine, except if another book is open at the time, it won’t find the subroutine. I’ve been trying something like this:

    Application.ontime now+timeserial(0,0,10), “‘Data File.xls’!Functions.waitingForSheet “”sheet1?”, “”runThisSub”””

    The waitingForSheet subroutine requires two string arguments. I don’t see what I am doing wrong, any help?

27. Bryan,

    the commandline requires apostrophes to encapsulate the arguments, but the filename should not be encapsulated: file.xls!’module.proc 12.3#,””arg””‘”
    Note the modulename is optional. Type declaration characters can be used to force
    data types

    to be able to stop the timed proc when you close the workbook you’ll need to store both the next timeserial AND the ‘commandline'(since it contains arguments)

    Option Explicit

    Dim dtNext As Date
    Dim szNext As String

    Sub StartTimed()
     dtNext = Now + TimeSerial(0, 0, 5)
     szNext = ThisWorkbook.Name & “!’module1.TimedProc True,”“text”“,123,empty,null'”
     Application.OnTime dtNext, szNext
    End Sub
    Sub StopTimed()
    On Error Resume Next
     If dtNext  0 Then
      Application.OnTime dtNext, szNext, , False
      dtNext = 0
     End If
    End Sub
    Sub TimedProc(bReRun As Boolean, ParamArray vArgs())
     Dim i As Integer
     If Not IsMissing(vArgs) Then
      For i = LBound(vArgs) To UBound(vArgs)
       Debug.Print TypeName(vArgs(i)),
      Next
      Debug.Print
     End If
     If bReRun Then
      StartTimed
     End If
    End Sub

28. Hello,

    i have a similar trouble than some of above, but the fact is that it doesn’t work. This is the code in a single module:

    Private Sub CommandButton1_Click()
    Application.OnTime Now + TimeValue(“00:00:02?), “Auto”
    End Sub
    Sub Auto()
    MsgBox (“¡BINGO!”)
    End Sub
    When i click on CommandButton1, after 2 secs, an error message tells that can’t find the macro Auto.

    Please, could anyone help me?

    Thanks a lot.

29. sub nextorders()
    startTimer
    end sub

    sub startTimer()
    Application.OnTime Now + TimeValue(“00:00:2?), “nextorders”
    end sub

    This doesn’t work for me!!!
    CANNOT RUN THE MACRO “D:BOOK1.XLSM’!NEXTORDERS’. THE MACRO MAY NOT BE AVAILABLE IN THIS WORKBOOK OR ALL MACROS MAY BE DISABLED.

    wth?? All macros are enabled, what’s the problem??

30. yes it working
    thanks

31. Hi,

    The ontime codes that I have below call the functions I specified twice sometimes even thrice depite having specified the time up to the second. May I know how I can limit the function to run only once?

    Sub ontime()
    Dim nexttick

    Application.ontime TimeValue(“21:31:01?), “FIRST”, TimeValue(“21:31:01?)
    Application.ontime TimeValue(“21:22:40?), “Second”, TimeValue(“21:22:40?)
    Application.ontime TimeValue(“21:22:59?), “Third”, TimeValue(“21:22:59?)
    nexttick = Now + TimeValue(“00:00:15?)
    Application.ontime nexttick, “ontime”
    End Sub

    Thanks in advance for the assistance

32. Hi,
    I am using function to forse Excel Macro everyday at 10:11.
    Everything works fine if the day is one of the workdays.
    On Saturday and Sunday it happens simply nothing.
    I tried much to find out the reason for that, no success.
    I don’t use any statement like “Weekday”, just daily repeating.

    Thanks in advance for any suggestions,
    Rudi

33. Is there a way to get a list of all active OnTime procedures in a given workbook?

34. Hi

    This won’t give you a list of programs, but if you have a list of possible programs, this will allow you to nominate your program name, and, if it has been scheduled, show when it was scheduled. Just run the program for each of your possibles by changing the value of the variable thisprog. In the current code, it looks for a program called “bbb”

    HTH

    Sub ccc()
    ‘nominate the name of the program you want to check
     thisprog = “bbb”
      On Error Resume Next
      For h = 0 To 23
        For i = 0 To 59
          For j = 0 To 59
            ‘remove the scheduled ontime activity
           Application.OnTime TimeValue(Format(h, “00”) & “:” & Format(i, “00”) & “:” & Format(j, “00”)), thisprog, , False
            If Err.Number = 0 Then
            ‘record the name and time of the program
             Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = TimeValue(Format(h, “00”) & “:” & Format(i, “00”) & “:” & Format(j, “00”))
              Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).Value = thisprog
            ‘restate the ontime program
             Application.OnTime TimeValue(Format(h, “00”) & “:” & Format(i, “00”) & “:” & Format(j, “00”)), thisprog
            End If
            Err.Clear
          Next j
        Next i
      Next h
      On Error GoTo 0
    End Sub

35. But, there all codes to be written plz specify someone

36. I would like to use OnTime to schedule an cancel events. But in my application, I can not trust that VBA will remember global variables. VBA may crash or be stopped and lose the global values. So I may need to cancel events with out knowledge of when they were scheduled.

    Is there some DLL that exposes an interface to the Windows event queue that would allow me to inspect all currently-scheduled events?

37. Two questions:
    1.
    Have written Application.OnTime macro that works; however, I must close the worksheet and reopen it, enabling macros, before it WILL work. If I change the execution time, or any other change to the macro, I must close and reopen again. I want to insert a check box onto my Excel spreadsheet that will allow me to enter an execution time for the OnTime macro to run, and then have it run at that time without having to close and reopen the worksheet every time.

    2.
    How can I get the time provided either through the check box or InputBox to be utilized in the Application.OnTime statement?

    Thank you for your help!

38. Hi,

    I have one excel sheet which has ontime event of 5 mins to run some code.

    This code works fine when in debugging mode. But if I lock my PC or minimize excel windows and start working something else, this code just pauses at beginning and as soon as I activate excel window, this code runs.

    This code involves opening and closing of one userform and i think userform is culprit.

    Pls help to solve this issue as this fails my whole automation idea….

39. I am using an excel worksheet tag to signify that a waittime function
    has been called, so if the user re-opens the workbook (which saved the
    tag), it just restarts the waittime. The purpose, is if a user misses
    three password entries for their sign-in w/in my workbook, it locks them
    out of the “application” until the time passes. On their first missed
    entry, they’re given the option to quit trying to sign-in, but one of
    the three strikes is logged. After 10 minutes, I want the tag to reset.
    If the workbook is re-started, say through computer shut-down, the open
    workbook function re-starts the timeout, if three strikes exist. But if
    I don’t reset the tag after 10 minutes of the sign-in sheet sitting idle,
    then 2 more attempts, even if deciding to cancel attempted sign-in, will
    call the time-out. If a user successfully signs in, it will be reset.
    I need an on-time function, that if the one or two strikes are tagged,
    and the sign-in sheet sits idle (no objects clicked / only objects are
    available on that worksheet), the tag will be reset to “0?. But if a
    user happens to click an object (all of which don’t run their macro by
    determination of time-out in their macro’s first few lines), just as the
    ontime macro gets called, I don’t want an error. Kinda like making sure
    the ontime macro runs after whatever code is already being executed. I
    saw something to the like in the previous posts, I just want to make sure
    I am reading it right, and that it would be executed everytime…

40. Hello
    I need help to write code in VBA. My cod in VB 6 is working, but in VBA vipWin i dont have time control like in VB 6… I am really struggling with this for days. I have created serial communication for RFID in VB 6 with MSCom and Timer, but VBA doesn’t have timer… PLS HELP!!!! This is my code for communication in VB 6 with timer and MSComm:
    Private Sub MSComm()
    T1.Enabled = True
    end Sub

    Private Sub T1_Timer()

    Dim buffer As Variant
    Dim i As Integer
    Dim Arrbyte() As Byte
    Dim iTemp As Integer
    Dim sTemp As String
    Dim strINPUT As String
    Dim info As String

    If cmbTimes.ListIndex = 0 And optIdentify.Value = True Then
    T1.Enabled = True
    Else
    T1.Enabled = False
    End If

    If ViseKartica = True Then
    If prababa = False And cmbTimes.ListIndex = 0 Then
    MSComm.Output = Chr(“&H” & “0A”) & Chr(“&H” & “FF”) & Chr(“&H” & “02?) & Chr(“&H” & “44?) & Chr(“&H” & “B1?) ‘Ciscenje bafera
    prababa = True
    ElseIf cmbTimes.ListIndex 0 Then
    MSComm.Output = Chr(“&H” & “0A”) & Chr(“&H” & “FF”) & Chr(“&H” & “02?) & Chr(“&H” & “44?) & Chr(“&H” & “B1?)
    End If
    MSComm.Output = Chr(“&H” & “0A”) & Chr(“&H” & “FF”) & Chr(“&H” & “02?) & Chr(“&H” & “80?) & Chr(“&H” & “75?) ‘Identifikacija

    Command5.Caption = brojac

    If cmbTimes.ListIndex = 0 Then
    cmdIdentify.Enabled = False
    cmdStop.Enabled = True
    Else
    cmbTimes.ListIndex = brojac
    If brojac < 1 Then
    cmbTimes.Locked = False
    cmbInterval.Locked = False
    T1.Enabled = False
    cmdIdentify.Enabled = True
    cmdStop.Enabled = False

    Pls help to solve this issue !!!

41. marc says:
    March 29, 2008 at 8:04 pm
    sub nextorders()
    startTimer
    end sub

    sub startTimer()
    Application.OnTime Now + TimeValue(“00:00:2?), “nextorders”
    end sub

    This doesn’t work for me!!!
    CANNOT RUN THE MACRO “D:BOOK1.XLSM’!NEXTORDERS’. THE MACRO MAY NOT BE AVAILABLE IN THIS WORKBOOK OR ALL MACROS MAY BE DISABLED.

    wth?? All macros are enabled, what’s the problem??

    sub nextorders()
    Application.OnTime Now + TimeValue(“00:00:00?), “startTimer”
    end sub

    sub startTimer()
    Application.OnTime Now + TimeValue(“00:00:(HEREISTHEPROBLEM)2?), “nextorders”
    end sub

    >>>>

    sub startTimer()
    Application.OnTime Now + TimeValue(“00:00:02?), “nextorders”
    end sub

42. Mark,
    The problem is that you have to specify the sheet in front of the function. Like this, for example : “sheet1.nextorders”.
    I hope this was useful for you.

    sub nextorders()
    startTimer
    end sub

    sub startTimer()
    Application.OnTime Now + TimeValue(“00:00:2?), “nextorders”
    end sub

    This doesn’t work for me!!!
    CANNOT RUN THE MACRO “D:BOOK1.XLSM’!NEXTORDERS’. THE MACRO MAY NOT BE AVAILABLE IN THIS WORKBOOK OR ALL MACROS MAY BE DISABLED.

    wth?? All macros are enabled, what’s the problem??

43. Sorry for the 3 years delay:)

Posting code? Use <pre> tags for VBA and <code> tags for inline.