Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts

    Deactivate Timer Macro

    Hi Friends,

    I got this little routine from somewhere in the internet and like to know how to deactivate it.

    Here's the code:

    Sub CLOCK()
    Dim dtSaveTime As Date
    dtSaveTime = Now + TimeValue("00:05:00")
    Application.OnTime dtSaveTime, "CLOCK"
    Call A
    End Sub

    As you know by now VBA is not my world.

    Best Regards,
    Wolfgang

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Wolfgang,

    You cannot just deactivate it, delete it, or comment it out until you are sure that the routine is not being called from another routine. If so, a runtime error will be generated. I would open every module (Standard, Worksheet, Workbook, and Userform, and do a search for "Clock". In the VB editor, select Find in the Edit menu and do a search. Alternately, you can select to search entire project instead of just the module if you don't mind jumping around. By the nature of the routine, you will most likely find it in the workbook module. Since this routine runs every 5 minutes and calls a procedure "A", it is important to investigate what the subroutine "A" does because it will no longer be called as well.

    HTH,
    Maud

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Good Morning Maude,

    thanks for you answer.
    What I had in mind is something like this:
    A button which will start the process and another which will stop it.
    Clock is a single routine which does not appear anywhere else.

    Best regards,
    Wolfgang

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Wolfgang

    To cancel a pending OnTime event, you must provide the exact time that it is scheduled to run.
    In order to do this, the scheduled run time must be stored in a Public Variable.
    I assume that in your case this has been stored in the Public variable dtSaveTime.
    (Note, each OnTime event needs its own when-to-run value)

    To stop a pending OnTime event, use something like this:

    Code:
    sub stopCLOCK()
    On Error Resume Next
    Application.OnTime EarliestTime:=dtSaveTime, _
    	Procedure:=CLOCK, _
            Schedule:=False
    End Sub
    You could attach this code to a button to stop your process.

    zeddy

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Zeddy,
    many thanks for your answer.
    I used your code and after running it I got the following Error message:
    Compile Error: Expected Function or variable

    Please advise
    Best Regards,

    Wolfgang

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Wofgang

    You will get such an error if there is a missing routine named CLOCK or a missing public varaiable named dtSaveTime.
    My example code is based on your posting which has this procedure (i.e. subroutine) named CLOCK, and a variable named dtSaveTime.
    If your procedure containing the Application.OnTime is NOT named sub CLOCK(), then amend my code to refer to the correct name.

    Perhaps your code module doesn't have dtSaveTime declared as a Public Variable?
    In which case, at the very top of the module just add this:

    Public dtSaveTime


    zeddy

  7. #7
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Zeddy,

    Sorry to tell you that the error message remains the same...

    Best regards,

    Wolfgang

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Wolfgang

    Oooops! I forgot to put double quotes round the procedure, i.e. should have been as follows:

    Code:
    Sub stopCLOCK()
    On Error Resume Next
    Application.OnTime EarliestTime:=dtSaveTime, _
        Procedure:="CLOCK", _
            Schedule:=False
    End Sub
    try this!

    zeddy

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts

    A shorter version

    Wolfgang,
    Sorry for the confusion. Didn't know what your intent was when you said "Deactivate". I have shortened Zeddy's code a bit to stop the recycling.

    It is important to realize that if you deactivate the Clock routine, you are also disabling A Routine. Is it needed?
    Code:
    Public dtSaveTime As Date
    
    Sub CLOCK()
    dtSaveTime = Now + TimeValue("00:0:15")
    Application.OnTime dtSaveTime, "Clock"
    Call A
    End Sub
    
    Sub stopCLOCK()
    Application.OnTime dtSaveTime, "Clock", , False
    End Sub
    
    Public Sub A()
        MsgBox "Hello"
    End Sub
    By the way, you will notice the time change to 15 sec intervals to speed up testing
    Last edited by Maudibe; 2013-02-05 at 22:39. Reason: added comment about time set

  10. #10
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Good Morning Maude and Zeddy,
    I added the double quotes as suggested by Zeddy and the error message is gone but the clock keeps running!

    I also implemented your macros Maude and the message "Hello" comes up right away and goes into a constant loop which can't be stopped.

    Let me explain what my intention is, please:
    The "A" routine refreshes the Web-data of 8 workbooks as far as their Stock Exchange values go according to the value set at TimeValue.
    So, it will run forever because of the lack of a STOP macro and I need to close the workbook which holds the Clock-routine.

    Hopefully I made myself a little bit clearer and I thank both of you for your patience.

    Best regards,

    Wolfgang

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Wolfgang

    If the clock keeps running after it has been 'stopped', then something is causing the clock to start again - probably in your routine "A"????

    Check the attached sample file:
    Clicking the [Start Clock] button will cause a routine to run, which just displays a 'hello' message box (which you need to click OK to dismiss). This will continue every 10 seconds.

    If you then click the [Stop Clock] button, then NO further 'hello' message boxes will appear.

    zeddy
    Attached Files Attached Files

  12. The Following User Says Thank You to zeddy For This Useful Post:

    wolfgang (2013-02-07)

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Wolfgang,
    When you attempted the code that I posted, I assume that you ran the Clock routine to start it in motion. Did you also run the StopClock routine once things were in motion to stop it? The StopClock routine will not run automatically as I did not tie it to a button. However, When I did, both of our codes did work fine.

  14. The Following User Says Thank You to Maudibe For This Useful Post:

    wolfgang (2013-02-07)

  15. #13
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi again,

    Finally I was able to find the "Root-Cause" of all this confusion, it is the "CALL A".

    Since I call a routine with the same name Excel got confused and simply denied any service from this point on. My routine looks for 8 external open workbooks and refreshes their links to the web.
    I just deleted your Public Sub A() MsgBox "Hello" End Sub code and everything runs now as expected, as well as Zeddy's code.
    Apologies for the fuzz that I created and I deeply express my appreciation to the two of you!!!

    Best regards,

    Wolfgang

  16. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Wolfgang,

    You are more han welcome. Suggestion for the future: Use more unique names for routines that give an indication of what they do. You will have a less chance of duplicate naming and easier troubleshooting as you have already found out.

    Maud

Posting Permissions

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