2

I need to run a large procedure (RunLargeSub) every second, and this procedure takes about 0.5s to complete. I'm currently using Application.OnTime, but won't this result in RunLargeSub being called every ~1.5s ? Is it possible to fix this to 1.0s?

Sub Update()
    RunLargeSub
    NextTick = Now + TimeValue("00:00:01")
    Application.OnTime NextTick, "Update"
End Sub


Sub Stop()
    On Error Resume Next
    Application.OnTime NextTick, "UpdateClock", , False
    On Error GoTo 0
End Sub

All help much appreciated.

Michael Mathews
  • 21
  • 1
  • 6
  • 3
    `but won't this result in RunLargeSub being called every ~1.5s ?` Not sure I understand... lets say the macro is called at 12:01:01. the macro finishes by `12:01:01:30`. Next the macro is called at 12:01:02 so how is it `~1.5s`? – Siddharth Rout Jul 31 '12 at 13:27
  • If it's called at 12:01:01, then isn't NextTick set to 12:01:02:30? (ie 1.5s later?) Though now I look at it, I could just set NextTick's value before calling RunLargeSub, right? – Michael Mathews Jul 31 '12 at 14:01
  • No. Next tick count will be set for 12:01:02. Next tick will not depend on the running of the runlargesub. – Siddharth Rout Jul 31 '12 at 14:16
  • Oh ok, just to clarify: Nexttick gets updated to 12:01:01 + 1s, no matter how long RunLargeSub takes? Thanks so much. – Michael Mathews Jul 31 '12 at 14:18
  • @SiddharthRout: What am I missing? If NextTick is set to Now+1sec **after** RunLargeSub finishes, why does the execution time of RunLargeSub not affect the length of the total time used? I'm quite confused... – I'm with Monica Sep 14 '12 at 06:37

1 Answers1

1

You declare NextTick after RunLargeSub. Thats why it takes 1.5s. If you declare it before RunLargeSub it will take exactly 1s. But then you'll have problem if RunLargeSub takes more then 1s because the main sub will stop executing. I'm not sure if onTime accepts fractions of a second but you can do that:

Sub Update()
    RunLargeSub
    NextTick = Now + TimeValue("00:00:01")/2
    Application.OnTime NextTick, "Update"
End Sub

It should run RunLargeSub and in half a second will call the sub again.

Spas
  • 840
  • 16
  • 13