Macro that runs every minute when System Clock changes minute?

[naira]

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, using

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

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

 

[Worf]

Hi
See if this example is useful, adjust the times to your liking:

Sub EveryMinute()
Dim tsec$, i%, j%


Do
    tsec = Right(Time, 2)
    j = 0
    For i = 1 To 100
        j = j + 1       ' wait a little
    Next
Loop Until tsec = "00"      ' minute change
For i = 1 To 2
    
    Application.OnTime Now + TimeValue("00:00:30"), "Ramones"
 
Next


End Sub
 
Sub Ramones()
    Range("f" & Rows.Count).End(xlUp).Offset(1).Value = Time
    Application.Wait Now + TimeValue("00:00:20")
End Sub

 

[naira]

Hi Worf,
Thanx for your reply.
Tried your code but it goes into some sort of endless loop and excel hangs.

Also, Application.Wait would stop me from other things that I want to do during the 20 seconds wait. Can we avoid Application.wait?

Could you please check your code again and help me out?

 

[Worf]

New version follows. Note that the macro will only return control to you when system clock changes minute, this does not mean hanging...

Sub EveryMinute()
Dim tsec$, i%, j%


Do
    tsec = Right(Time, 2)
    j = 0
    For i = 1 To 100
        j = j + 1       ' wait a little
    Next
Loop Until tsec = "00"      ' minute change


For i = 1 To 3          ' schedules three calls
    
    Application.OnTime Now + (TimeValue("00:00:20")) * i, "Ramones"
 
Next


End Sub
 
Sub Ramones()
    Range("f" & Rows.Count).End(xlUp).Offset(1).Value = Time
End Sub

 

[rollis13]

Cross-post: Macro that runs when System Clock changes minute?

 

[Worf]

Rollis
Thanks for pointing it out.

Naira
I will not blacklist you, but please read the forum rules, specially Rule #10. The link is on my signature below.

We welcome your future posts...

 

[naira]

Rollis
Thanks for pointing it out.

Naira
I will not blacklist you, but please read the forum rules, specially Rule #10. The link is on my signature below.

We welcome your future posts...

Sorry for not indicating cross-posts. Request Posted on http://www.ozgrid.com/forum/showthre...goto=newpost** and Macro that runs every minute when System Clock changes minute?, but posted here also since no working solution given on other 2 forums as on Sept-16th 2013.

 

[Worf]

Hi Naira</SPAN></SPAN>
Did you try the code on post #4? If it is not working, could you post your test macro?</SPAN></SPAN>
Also, if you get a final answer on other forum, please let us know.</SPAN></SPAN>

 

[naira]

Sorry for late reply guys, but after some extensive testing and some minor tweaking, it seems that the Solution suggested by jindon in post #18 at Macro that runs when System Clock changes minute? - Page 2 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?

 

[Worf]

Hi Naira
- To download that file at OzGrid one must be a member there. Could you post the solution here?

- I don't think this forum has the solved option enabled, and it would be nice; I suggest it at the thread title, like this:

[Solved] Help! How can I have Excel make me a cup of tea????