Macro Timer - Run based on the actual time not just every random minute.

[Weered]

I think I worded the title right lol?

So I have a macro already coded that operates in any time interval I want based on Sub StartTimer() RunWhen Now + the serial string blah blah. It works great. My problem is that I need it to run on the minute based on the time of day? Does that make sense? Not just a minute from when I start the timer. So say right now it is 12:30:30 being Hour/Minute/Second. So if I started the timer now it wouldn't operate until 12:31:00 and continue on every minute after that.

I just need it to be a bit more accurate as it is recording open and close stock price on the minute. Yes I realize I could just wait with my hand on the button for the minute to change but bare with me here lol. I am trying to recreate charts on the Metatrader platform in excel using live data and I need very specific prices. Here is the entire macro below.

Public RunWhen As DoublePublic Const cRunIntervalSeconds = 60
Public Const cRunWhat = "Mess"
Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
                       Schedule:=True
End Sub
Sub Mess()
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("Analysis Data")
    .Range("A3").Value = Time
    .Range("C1") = .Range("C3") + 1
    Set Crng = .Range("B6:AQ6")
    Set Prng = .Range("$B$9").End(xlUp).Offset(1, 0)
    Crng.Copy
    .Range("B8").PasteSpecial Paste:=xlPasteValues
    .Range("B8:AQ8").Copy
    .Range("B9:AQ9").Insert Shift:=xlDown
    .Range("B6000:AQ6000").Delete Shift:=xlUp
    Application.ScreenUpdating = True
End With
 
    StartTimer




End Sub
Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
                       Schedule:=False
End Sub

 

[AlphaFrog]

[color=darkblue]Public[/color] RunWhen [color=darkblue]As[/color] [color=red]Date[/color]
[color=darkblue]Public[/color] [color=darkblue]Const[/color] cRunWhat = "Mess"
    
[color=darkblue]Sub[/color] StartTimer()
   [COLOR=#ff0000] RunWhen = TimeValue(Format(Now, "h:mm")) + TimeSerial(0, 1, 0)[/COLOR]
    Application.OnTime EarliestTime:=RunWhen, _
                       Procedure:=cRunWhat, _
                       Schedule:=[color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Weered]

That worked great thanks!

 

[Weered]

I need to alter it a bit. I need it to run on second 59 or so as opposed to perfectly on the minute. But changing it to the code below just makes it run like a standard timer at any random 59 seconds not 59 on the minute of the current time.

Public RunWhen As DatePublic Const cRunWhat = "Mess"
    
Sub StartTimer()
    RunWhen = TimeValue(Format(Now, [COLOR=#FF0000]"h:mm:ss")) + TimeSerial(0, 0, 59)[/COLOR]
    Application.OnTime EarliestTime:=RunWhen, _
                       Procedure:="Mess", _
                       Schedule:=True

End Sub

 

[Weered]

Double post

 

[AlphaFrog]

Try this...

RunWhen = TimeValue(Format(Now, "h:mm")) + TimeSerial(0, 0, 59)

 

[Weered]

It only runs once. Likely because the format isn't (Now, "h:mm:ss") Which also does not want to work right.

Hmmm...

 

[AlphaFrog]

try...

RunWhen = TimeValue(Format(Now, "h:mm:59"))

 

[Weered]

No dice. I used an OFFSET set up to look the extra second back in time in the live data feed. It works now.

Thanks for the prompt help! This site is fantastic!