Run VBA macro to start at every 45th second-CodePudding

I want to start the vba macro to start to run every 45th second, If it got delayed then it has to start again at either at that minute 45th second or next minute 45th second and so on.. I wrote following 2 codes, but its not working. please guide

If Format(Now(), "ss") > 45 And Format(Now(), "ss") < 60 Then
         runtime = Now()
         Else
            x = Format(Now(), "ss")
            y = 45 - x
            runtime = Now()   TimeValue("00:00:" & y)
         End If
         Application.OnTime runtime, "testtimer"

or (in the below code getting type mismatch here)

 runtime = Now()   TimeValue("00:00:45")
 nextime = runtime   TimeValue("00:00:45")
 Application.OnTime runtime, "testtimer",nextime

CodePudding user response：

Working with the application.onTime event is always a bit tricky. The following code should do what you are after.

Sub macro_to_run()
    Debug.Print "at macro_to_run", Now
End Sub

Sub timed_procedure()
     
    ' read the position of the second hand from the syetem clock
    Dim secondHand As Byte
    secondHand = Second(Now)
    
    ' schedule this proc to run again either on the 45th second of this minute (if that is in the future)
    ' or the 45th second of the next minute
    If secondHand < 45 Then
            Application.OnTime DateAdd("s", 45   Minute(Now) * 60   Hour(Now) * 3600, Date), "timed_procedure"
    Else
            Application.OnTime DateAdd("s", 60   45   Minute(Now) * 60   Hour(Now) * 3600, Date), "timed_procedure"
    End If
    
    'execute the macro only if the current second is in the allowed window
    If secondHand >= 45 And secondHand <= 50 Then
        macro_to_run
    End If


End Sub