Results 1 to 3 of 3
  1. #1
    Cecil Rhoades

    Application.OnTime (97sr2)

    I need to schedule a macro to run every n minutes. I will use Application.OnTime, unless there is a better method. However, I would like more info than I can find at MSDN or in Help or in 3 books I have.

    If OnTime is a method of the Application object, does it trigger regardless of whether the workbook is open?

    What happens if Excel is not avaiable at the target time. Will the macro run the next time I open Excel?
    To cancel a scheduled macro, it appears as though I need to know the exact scheduled time. Is this correct?


  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    South Carolina, USA
    Thanked 0 Times in 0 Posts

    Re: Application.OnTime (97sr2)

    Application.OnTime will only trigger the event if the workbook that scheduled the event is open. If VBA code in a workbook schedules an OnTime event, and the workbook is closed before the schedule time arrives, that event is canceled. It will not cause Excel to start nor will the event happen the next time the workbook is opened.

    Since the workbook must be open when the time for the event arrives, Excel can not be unavailable. Excel may be delayed in executing the event code because other higher priority tasks have control of the machine.

    Yes, you do need to specify the exact time when the OnTime event is to occur. To schedule a VBA routine to run in 15 minutes from now, you add 15 minutes to the current time like this:

    <pre> Application.OnTime Now() + TimeValue("00:15:00"), "DoSomething"

    OnTime also only schedules one event. To have a routine executed repetively, then the VBA routine must issue another OnTime to reschedule itself.
    Legare Coleman

  3. #3
    Cecil Rhoades

    Re: Application.OnTime (97sr2)

    A delayed thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts