vbatimerwaitpausetimedelay

Timing Delays in VBA


I would like a 1 second delay in my code. Below is the code I am trying to make this delay. I think it polls the date and time off the operating system and waits until the times match. I am having an issue with the delay. I think it does not poll the time when it matches the wait time and it just sits there and freezes up. It only freezes up about 5% of the time I run the code. I was wondering about Application.Wait and if there is a way to check if the polled time is greater than the wait time.

   newHour = Hour(Now())
   newMinute = Minute(Now())
   newSecond = Second(Now()) + 1
   waitTime = TimeSerial(newHour, newMinute, newSecond)
   Application.Wait waitTime

Solution

  • I use this little function for VBA.

    Public Function Pause(NumberOfSeconds As Variant)
        On Error GoTo Error_GoTo
    
        Dim PauseTime As Variant
        Dim Start As Variant
        Dim Elapsed As Variant
    
        PauseTime = NumberOfSeconds
        Start = Timer
        Elapsed = 0
        Do While Timer < Start + PauseTime
            Elapsed = Elapsed + 1
            If Timer = 0 Then
                ' Crossing midnight
                PauseTime = PauseTime - Elapsed
                Start = 0
                Elapsed = 0
            End If
            DoEvents
        Loop
    
    Exit_GoTo:
        On Error GoTo 0
        Exit Function
    Error_GoTo:
        Debug.Print Err.Number, Err.Description, Erl
        GoTo Exit_GoTo
    End Function