Timing Delays in VBA

Question

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

Examination

It's not clear to me what is specifically your problem. Are the freezes? Is because the freeze is below 1 sec? – Unsegregated

Answer 1

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

Goodspeed

This does something different. Application.Wait holds the main UI thread but DOES continue doing background things like recalculating – Siebert

I just need to get the code to pause without it waiting for 24 hours. Thanks for the help. Also i noticed that the time only has 2 decimal places. Does this mean the lowest amount of time you can wait is 0.01 seconds? – Examination

Why did you make this a function instead of a sub? It does not return a result. – Gwendolin

Note to @SteveMallory and warning to others: The edit to address the midnight issue is wrong. See meta.#409371 . – Gough

@Gough then post your correction as a new answer. At the very least you should include your reasoning in a comment on this answer itself – Mehta

@Mehta - See the link in my comment for a brief description of the error and what I did to try to fix it. – Gough

@Gough I saw your post but if it gets deleted then there's no useful information in the actual relevant place. Someone wanting to look up how to fix their timer likely isn't interested in reading your complaints about the review queue – Mehta

Okay, I posted a new answer with the correction. I wanted to edit the accepted answer because few people will see my answer and they'll be stuck with this buggy code. But when I tried to edit, I got the message, "The edit queue is full at the moment - try again in a few minutes!" (Different from the problem complained about to meta.) I tried again a few times, and didn't get through, so I went ahead and posted the answer. – Gough

Answer 2

If you are in Excel VBA you can use the following.

Application.Wait(Now + TimeValue("0:00:01"))

(The time string should look like H:MM:SS.)

Peppie

The tag says VBA, it does not include Excel. Wait is not available in MS Access, for one, and probably a number of other applications that use VBA. – Denisse

Wow, You are correct, I apologize. I don't know why I was thinking Excel. Maybe cause I recognized the original code from the Excel Help. Well regardless, hopefully anyone that sees this will see that it's at least simple to implement in Excel. I don't know WHY they didn't implement it in Access though.. But you're correct I've ran into that problem too. – Peppie

Either way, the OP still employed the Wait method, therefore he has access to it, no regard to where he is, he has Application.Wait and THIS is SOOO much simpler than the higher voted answer – Scarlatti

I think the tone of the answer is to blame for the negativity of the comments. – Cheerly

I didn't mean it as condescending, just as a general criticism of complicated approaches instead of looking into what's already there to use. Saves a lot of time and effort. – Peppie

Answer 3

You can copy this in a module:

Sub WaitFor(NumOfSeconds As Long)
Dim SngSec as Long
SngSec=Timer + NumOfSeconds

Do while timer < sngsec
DoEvents
Loop

End sub

and whenever you want to apply the pause write:

Call WaitFor(1)

I hope that helps!

Pyromania

Isn't is assuming every DoEvents takes exactly one second to be executed? – Unsegregated

Tiago I am not sure I understand what you mean. The Number of Seconds are declared in the parenthesis. – Pyromania

Nevermind, just noticed now that this Timer is an internal VBA function (that I wasn't aware of). Noticed as well that's pretty much the same solution as proposed by @Steve Mallory. Either way, thanks for let me know about this new function =] – Unsegregated

Answer 4

Have you tried to use Sleep?

There's an example HERE (copied below):

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub Form_Activate()    

frmSplash.Show
DoEvents
Sleep 1000
Unload Me
frmProfiles.Show

End Sub

Notice it might freeze the application for the chosen amount of time.

Unsegregated

This won't work on x64 Office without a tiny tweak, Also the Timer function in excel is a Single so does allow fractions of a second in most answers – Keirakeiser

This is probably what every other implementation does under the hood. +1 for Win32 API. – Except

Answer 5

Access can always use the Excel procedure as long as the project has the Microsoft Excel XX.X object reference included:

Call Excel.Application.Wait(DateAdd("s",10,Now()))

Andel

Answer 6

Your code only creates a time without a date. If your assumption is correct that when it runs the application.wait the time actually already reached that time it will wait for 24 hours exactly. I also worry a bit about calling now() multiple times (could be different?) I would change the code to

 application.wait DateAdd("s", 1, Now)

Siebert

Answer 7

Another variant of Steve Mallorys answer, I specifically needed excel to run off and do stuff while waiting and 1 second was too long.

'Wait for the specified number of milliseconds while processing the message pump
'This allows excel to catch up on background operations
Sub WaitFor(milliseconds As Single)

    Dim finish As Single
    Dim days As Integer

    'Timer is the number of seconds since midnight (as a single)
    finish = Timer + (milliseconds / 1000)
    'If we are near midnight (or specify a very long time!) then finish could be
    'greater than the maximum possible value of timer. Bring it down to sensible
    'levels and count the number of midnights
    While finish >= 86400
        finish = finish - 86400
        days = days + 1
    Wend

    Dim lastTime As Single
    lastTime = Timer

    'When we are on the correct day and the time is after the finish we can leave
    While days >= 0 And Timer < finish
        DoEvents
        'Timer should be always increasing except when it rolls over midnight
        'if it shrunk we've gone back in time or we're on a new day
        If Timer < lastTime Then
            days = days - 1
        End If
        lastTime = Timer
    Wend

End Sub

Keirakeiser

Thanks for this function, it is the most complete here is caters for days. I've voted it up. – Clermontferrand

Answer 8

The Timer function also applies to Access 2007, Access 2010, Access 2013, Access 2016, Access 2007 Developer, Access 2010 Developer, Access 2013 Developer. Insert this code to to pause time for certain amount of seconds

T0 = Timer
Do
    Delay = Timer - T0
Loop Until Delay = 1 'Change this value to pause time in second

Thain

Answer 9

The handling of midnight in the accepted answer is wrong. It tests for Timer = 0, which will almost never happen. It should instead test for Timer < Start. Another answer tried a correction of Timer >= 86399, but that test can also fail on a slow computer.

The code below handles midnight correctly (with a bit more complexity than Timer < Start). It also is a sub, not a function, because it doesn't return a value, and variables are singles because there is no need for them to be variants.

Public Sub pPause(nPauseTime As Single)

' Pause for nPauseTime seconds.

Dim nStartTime As Single, nEndTime As Single, _
    nNowTime As Single, nElapsedTime As Single

nStartTime = Timer()
nEndTime = nStartTime + nPauseTime

Do While nNowTime < nEndTime
    nNowTime = Timer()
    If (nNowTime < nStartTime) Then     ' Crossed midnight.
        nEndTime = nEndTime - nElapsedTime
        nStartTime = 0
      End If
    nElapsedTime = nNowTime - nStartTime
    DoEvents    ' Yield to other processes.
  Loop

End Sub

Gough

Answer 10

I used the answer of Steve Mallory, but I am affraid the timer never or at least sometimes does not go to 86400 nor 0 (zero) sharp (MS Access 2013). So I modified the code. I changed the midnight condition to "If Timer >= 86399 Then" and added the break of the loop "Exit Do" as follows:

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 >= 86399
            ' Crossing midnight
            ' PauseTime = PauseTime - Elapsed
            ' Start = 0
            ' Elapsed = 0
            Exit Do
        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

Stolzer

This won't wait for the specified number of seconds if midnight gets hit. It fixes the problem but doesn't behave as expected – Keirakeiser

Answer 11

On Windows timer returns hundredths of a second... Most people just use seconds because on the Macintosh platform timer returns whole numbers.

Boyar

Answer 12

With Due credits and thanks to Steve Mallroy.

I had midnight issues in Word and the below code worked for me

Public Function Pause(NumberOfSeconds As Variant)
 '   On Error GoTo Error_GoTo

    Dim PauseTime, Start
    Dim objWord As Word.Document

    'PauseTime = 10 ' Set duration in seconds
    PauseTime = NumberOfSeconds
    Start = Timer ' Set start time.

    If Start + PauseTime > 86399 Then 'playing safe hence 86399

    Start = 0

    Do While Timer > 1
        DoEvents ' Yield to other processes.
    Loop

    End If

    Do While Timer < Start + PauseTime
        DoEvents ' Yield to other processes.
    Loop

End Function

Guesswork

Answer 13

For MS Access: Launch a hidden form with Me.TimerInterval set and a Form_Timer event handler. Put your to-be-delayed code in the Form_Timer routine - exiting the routine after each execution.

E.g.:

Private Sub Form_Load()
    Me.TimerInterval = 30000 ' 30 sec
End Sub

Private Sub Form_Timer()

    Dim lngTimerInterval  As Long: lngTimerInterval = Me.TimerInterval

    Me.TimerInterval = 0

    '<Your Code goes here>

    Me.TimerInterval = lngTimerInterval
End Sub

"Your Code goes here" will be executed 30 seconds after the form is opened and 30 seconds after each subsequent execution.

Close the hidden form when done.

Perspicuous