Macro that runs when System Clock changes minute?

  • Hi!


    I am looking for help to create a Macro that runs when system clock changes minute and then at every change of minute of system clock.


    For example, if I give the execute command to start the macro at 09:14:45 (HH:MM:SS), then its first run should be only at 09:15:00, then next run at 09:16:00 so on...


    I already have a Macro that runs every minute from initial run time, but it seems to be unreliable, because in case I open another sheet that takes lets say 20 seconds to open, the next macro run is delayed by 20 seconds.


    Thanx,


    Naira

  • Re: Macro that runs when System Clock changes minute?


    Quote from royUK;683636

    Attach the workbook or post the code that you are using


    Code
    Public Sub NextTime()
        Application.OnTime Now + TimeValue("00:01:00"), "NextTime"
            
        Sheets("SAVED DATA").Rows("4:4").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("SAVED DATA").Cells(4, 1) = Sheets("LIVE DATA").Cells(3, 1)
        Sheets("SAVED DATA").Cells(4, 2) = Sheets("LIVE DATA").Cells(3, 2)
        Sheets("SAVED DATA").Cells(4, 3) = Sheets("LIVE DATA").Cells(3, 3)
        Sheets("SAVED DATA").Cells(4, 4) = Sheets("LIVE DATA").Cells(3, 4)
        Sheets("SAVED DATA").Cells(4, 5) = Sheets("LIVE DATA").Cells(3, 5)
    End Sub



    Sample file attached.


    This is just a sample file to show that the code is run as soon I give the execute command and then every one minute from the time of first execution of the code.


    What I want is that even if I give the execute command to start the macro at 09:14:45 (HH:MM:SS), its first run should be only at 09:15:00, then next run at 09:16:00 so on... every time the system clock's minute changes. I can make the code run/ start at 09:15:00, but as said in the initial post, it seems to be unreliable, because in case I open another sheet that takes lets say 20 seconds to open, the next macro run is delayed by 20 seconds. Therefore, the next run is not after 60 seconds but after 60+20=80 seconds.

  • Re: Macro that runs when System Clock changes minute?


    Here is some code I recently used, you can adapt it to your needs. It effectively updates the time into B33 every minute at the time when the system clock changes.



    PS: I basically stole it from one of apo's posts :) http://www.ozgrid.com/forum/sh…81848&p=680112#post680112

  • Re: Macro that runs when System Clock changes minute?


    Stolen.. no.. :)


    I no doubt got the code or at least the idea from someone elses post on this or another forum.. I think that is the trick when at a learning stage of VBA (like myself).. try to study others code snippets.. and then apply them to different peoples problems..


    Personally.. I am hoping that a certain percentage of it all will stick in my mind and come as second nature.. so I can one day write code like the gurus on this forum do.. fluently and with the most efficiency possible..


    Although. i must admit.. it is a bit daunting.. when you see someone with literally 1000's of posts on a forum saying there is always more to learn..

  • Re: Macro that runs when System Clock changes minute?


    apo I wonder if you could download OP's workbook from Post #3 and then apply the code in Post #5 to OP's scenario. It's doing something odd and I can't figure out why. It is producing about 30-40 lines of data at time system clock changes. This is no doubt due to the modifications that I did to the code.

  • Re: Macro that runs when System Clock changes minute?


    Quote from holycow;684019

    apo I wonder if you could download OP's workbook from Post #3 and then apply the code in Post #5 to OP's scenario. It's doing something odd and I can't figure out why. It is producing about 30-40 lines of data at time system clock changes. This is no doubt due to the modifications that I did to the code.


    Hi holycow/apo,


    I am also noticing the same thing as pointed out by holycow.


    It seems that the solution suggested in post # 3 above is actually making the Macro run continuously between 59th second and 60th second. For example macro starts running at 09:00:59 and runs continuosly for 1 second till 09:01:00.

    This can be easily demonstrated if I copy the time in subsequent rows using mm:ss.0 format


    If I use a huge file to run this macro, it shows maybe one or 2 runs, but for a smaller file it can show up to 450 run.
    Below is a more simplified version of holycow's macro (sample file also attached) to show these continuous runs.

    Code
    Option Explicit
    Public dTime As Date
    Sub Clock()
        Sheets("Sheet1").Range("C4:C200").Value = Sheets("Sheet1").Range("C3:C200").Value
        dTime = Now + TimeValue("00:00:" & 59 - Right(Format(Time, "hh:mm:ss"), 2))
        Application.OnTime dTime, "Clock"
    End Sub


    Any debugs so that it runs only once?

  • Re: Macro that runs when System Clock changes minute?


    And here...


    I might suggest that naira reads this and learn something. After reading it, this thread should be updated with the URLs of any other tread started for this topic, or else confirm there are no others.


    _____________________________________________________________________________


    First lesson anyone who attempts replies on boards will learn: Avoid threads where the rules (which everybody indicates they have read) are ignored... You will save time that way.

  • Re: Macro that runs when System Clock changes minute?


    It wasn't easy but this has passed many crash tests



    Hope to see you back next time and don't forget the golden rule of crossposting ;) - make sure to provide links to the posts on other sites.

  • Re: Macro that runs when System Clock changes minute?


    Update:
    I will have to put the StopTimer part back in. What happens is if you save and close the workbook but leave Excel open when it reaches dTime the workbook will re-open to trigger the "clock" procedure.

  • Re: Macro that runs when System Clock changes minute?


    Request posted on http://www.excelforum.com/exce…ges-minute.html?p=3405466 and http://www.mrexcel.com/forum/excel-q...es-minute.html, since there was no reply for 2 days on this forum. Sorry for irritating people's sensitivities for not indicating the cross-post threads, but if one checked, they would know that each post was done after waiting for atleast a day. Also, this thread was bumped up waiting for a reply. Anyways, I guess no excuses for violating rules for not indicating cross-posts so Sorry once again. PS: Still waiting for a working solution, none has come forward from any of the 3 forums as on Sept-16th 2013.

  • Re: Macro that runs when System Clock changes minute?


    Quote from holycow;684090

    Hi Naira
    I think I have it working just give me a minute. Blacklist was harsh.
    :)

    Thank you for your time and effort Holycow! Yup, blacklist was a bit harsh for a first DUI violence ;) You actually managed to spoil an entire evening here, until I came back to apologize. Anyways, I guess it does come with the territory of people working hard to do some selfless help. Will test your new code and let you know in about 2-3 hours. Thanx once again.

  • Re: Macro that runs when System Clock changes minute?


    You should bear in mind that Windows is not a true multi-tasking system. It is a time share system where every process gets it's time allocation in turn* - so it may be difficult to get the exact precision you seem to want.


    Also, the Excel Timer is not an exact timer. It will fire roughly on schedule but can be prevented from firing by code that is already running, a cell actively being edited, and one or 2 other things... There is no way around this limitation in Excel itself.


    You can create a timer based on the Windows API which will fire accurately and on time every second, or even every minute - but you still have the basic problem that Excel may not be in a position where it can actually process that timer event.


    *Generally speaking.


    ____________________________________________________________________________________


    HolyCows' black list is a personal list - nothing to do with the board. I use one as well. It only takes a message like "Thanks for the reply but JoeBloggs gave the same answer on XYZ board a couple of hours ago..." before you start to think twice about even replying to cross posted threads, and totally ignoring anyone who doesn't add the links purely to save your own time.

  • Re: Macro that runs when System Clock changes minute?


    Sorry for late reply guys, but after some extensive testing over 2 days on live data and some minor tweaking, it seems that the Solution suggested by jindon above in #18 is the best available, since it only starts when the system clock minute changes and is also a one line solution.


    Thanks a lot for all your help guys.
    Thread solved.


    PS: Another forum has an option to mark the thread as solved. Can we have the same option here too in the thread tools?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!