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

It's not clear to me what is specifically your problem. Are the freezes? Is because the freeze is below 1 sec? — Tiago Cardoso, Aug 05 '11 at 18:25

score 37 · Answer 1 · edited Oct 19 '16 at 03:20

37

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.)

edited Oct 19 '16 at 03:20

D A Vincent

344
2
18

answered Sep 26 '11 at 01:00

TheFuzzyGiggler

909
1
7
14

7

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. – Fionnuala Sep 26 '11 at 08:07
2

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. – TheFuzzyGiggler Sep 26 '11 at 10:45
3

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 – PsychoData Nov 04 '13 at 18:34
4

I think the tone of the answer is to blame for the negativity of the comments. – BlueTrin Mar 05 '14 at 15:34
1

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. – TheFuzzyGiggler Mar 07 '14 at 02:20

score 26 · Accepted Answer · edited Jun 27 '13 at 20:06

26

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

edited Jun 27 '13 at 20:06

DACrosby

10,150
3
34
49

answered Aug 05 '11 at 18:07

Steve Mallory

4,059
1
25
31

1

This does something different. Application.Wait holds the main UI thread but DOES continue doing background things like recalculating – Eddy Aug 05 '11 at 18:22
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? – Lagg Master Aug 05 '11 at 20:02
5

The Timer becomes 0 at midnight (and never goes above 86400). So if this function is executed around midnight it will execute forever or until user interrupt. (Learned it hard way.) – nsg May 06 '13 at 13:37
@nsg Thanks. Updated the answer. – Steve Mallory May 06 '13 at 14:09
Why did you make this a function instead of a sub? It does not return a result. – Mark Petereit Feb 20 '17 at 13:53

score 13 · Answer 3 · answered Aug 05 '11 at 18:25

13

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!

answered Aug 05 '11 at 18:25

Financial Economist

653
1
7
9

Isn't is assuming every DoEvents takes exactly one second to be executed? – Tiago Cardoso Aug 05 '11 at 18:34
Tiago I am not sure I understand what you mean. The Number of Seconds are declared in the parenthesis. – Financial Economist Aug 05 '11 at 18:54
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 =] – Tiago Cardoso Aug 05 '11 at 19:05

score 7 · Answer 4 · answered Aug 05 '11 at 18:20

7

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.

answered Aug 05 '11 at 18:20

Tiago Cardoso

2,019
1
13
31

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 – James Barrass Aug 19 '15 at 09:32
2

This is probably what every other implementation does under the hood. +1 for Win32 API. – Motes Aug 23 '16 at 02:22

score 3 · Answer 5 · edited Aug 02 '16 at 17:22

3

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()))

edited Aug 02 '16 at 17:22

David Rogers

2,284
2
30
68

answered Feb 11 '14 at 16:06

user3298002

59
1

score 2 · Answer 6 · answered Aug 19 '15 at 09:30

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

Thanks for this function, it is the most complete here is caters for days. I've voted it up. — HarveyFrench, Jan 23 '17 at 17:16

score 2 · Answer 7 · answered Jul 02 '16 at 23:41

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

score 1 · Answer 8 · edited Jul 23 '15 at 03:42

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

This won't wait for the specified number of seconds if midnight gets hit. It fixes the problem but doesn't behave as expected — James Barrass, Aug 19 '15 at 09:22

score 1 · Answer 9 · answered Aug 05 '11 at 18:24

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)

score 0 · Answer 10 · answered Mar 11 '14 at 21:53

0

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

answered Mar 11 '14 at 21:53

Peter

9

score 0 · Answer 11 · edited Nov 18 '15 at 14:15

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

score 0 · Answer 12 · answered Dec 06 '18 at 19:38

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.

Timing Delays in VBA

12 Answers12

Linked

Related