Results 1 to 6 of 6
  1. #1
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Application.OnTime (97 -> 2002)

    I have a one-time project where I need to run a macro every 15 minutes for 24 hours.
    It is not ultra-critical and not worth too much effort but a few questions if I may.

    I have tested the following so I know it is going to work:

    Application.OnTime TimeValue("17:00:00"), "MyMacro"
    Application.OnTime TimeValue("17:15:00"), "MyMacro"

    Is the simplest thing going to be to simply create 96 entries?
    (I may need to stop everything and start again, hence the specified times forcing a consistent 15 minute interval.)

    Or do I simply reschedule the next Ontime for 'Now' plus 15 mins?

    How do I stop or pause the OnTime 'thing' running?

    TIA

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Application.OnTime (97 -> 2002)

    Leif,

    You can set this for a given time of day (e.g 17:00:00) or after a certaim time has elapsed. The following construct will run MyMacro in 15 minutes. If MyMacro contains a call to the procedure with this code, it it will reset the OnTime to run again 15 minutes later. So you can keep resetiing OnTime until say a certain condition is met.

    Application.OnTime Now + TimeValue("00:15:00"), "MyMacro"


    Andrew

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.OnTime (97 -> 2002)

    In the declaration section in the top of your module:

    Dim dNextTime as Double

    Sub StartIt()
    dNextTime=Now + Timevalue("00:15:00")
    Application.Ontime dNextTime,"MyMacro"
    End Sub

    Sub MyMacro()
    'Do Your Stuff
    dNextTime=Now + Timevalue("00:15:00")
    Application.Ontime dNextTime,"MyMacro"
    End Sub

    Sub StopIt()
    Application.OnTime EarliestTime:=dNexttime, Procedure:="MyMacro", Schedule:=False
    End Sub

    You need to have the *exact* time an ontime macro is scheduled to be able to cancel it, hence the dNexttime variable to keep that time.

    Note, that when you're in debug mode your ontime chain will break because you'll get an error message as soon as the ontime method tries to run the cheduled sub (something like "Cannot execute code in break mode"). Because your sub is therefor not run, a new Ontime is not set and your process halts.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.OnTime (97 -> 2002)

    Jan, Andrew,
    Thanks to you both for the replies - I think you given me enough to achieve what I'm after.

    I'll know in about 28 hours <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Cheers

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Application.OnTime (97 -> 2002)

    I have a one-time project where I need to run a macro every 15 minutes for 24 hours

    What - you aren't going to stay at your desk and click on a button for 24 hours? Where is your dedication? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.OnTime (97 -> 2002)

    Ah! This was for use at another site where we were doing some environmental testing and I needed to upload some data on a serial link through the night.
    This, of course, allowed me not to shirk from my duties here <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    Anyway, it worked well in the end. Started yesterday afternoon and went exactly to plan until 1:00pm today. Why? - because I was saving the data with a file named after the date and time and forgot to make the time element based on a 24hr day <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>. Just as well I was back there to manually take over . . . <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

Posting Permissions

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