Excel Macro code

Discussion in 'Trading Software' started by WilliamV, Sep 24, 2005.

  1. WilliamV


    How can this code be modified in order to stop a macro from running every 5 minutes?

    Public dTime As Date
    Sub StopTimer()
    On Error Resume Next
    dTime = Now + TimeValue("00:05:00")
    Application.OnTime dTime, "Macro2", False

    End Sub

    Any help is greatly appreciated.
  2. Not sure what the problem is... Sub StopTimer() is something you've written. Where is it being called from?

    In the caller do something like...

    If some_condition then
    result = Application.Run macroname
    end if

    maybe some_condition could be an external file containing a time offset, or 0. Even easier, a cell on some worksheet! If it's 0 or an invalid time offset, don't run the macro (or do whatever is needed to stop it if it's running).

    Now get rid of Application.OnTime in StopTimer(), which as written is definately misnamed.

    Good luck
  3. This morn I was working in xlXP(sp3) and l looked up Application.Ontime. According to the last example in the docs, you can specify the previous time value (exactly as used to start the macro) to cancel it.

    So instead of my previous extemporaneous suggestion, based on docs you could use a Static or a Public to stash the time last used to start the macro. Then use that stashed value to stop it. You will still need some conditional to determine whether or not to restart the macro or stop it. Something UNTESTED like...

    Public vntLastStartTime As Variant

    Sub StopTimer()
    If JaneStopThisCrazyThing
    Application.OnTime vntLastStartTime , "Macro2", False
    vntLastStartTime = 0
    dTime = Now + TimeValue("00:05:00")
    Application.OnTime dTime, "Macro2", False
    vntLastStartTime = dtime
    End if
    End Sub

    Using this concept, it might be useful to add an argument to the Sub that would be the conditional.

    Good luck

  4. / I had the same code...it works
    Time is Seconds tho
  5. In Macro2
    U have to run the the other one again
    ontime Macro I think