delay or wait (1 Viewer)

kitty77

Registered User.
Local time
Today, 04:27
Joined
May 27, 2019
Messages
693
What is the best way to delay VBA code? I would like to delay or wait for 3 seconds before it goes to the next line.

Thanks...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:27
Joined
Oct 29, 2018
Messages
21,357
Hi. I think Colin (isladogs) has a demo or article around here showing all the possible options. Let me see if I can find it.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 04:27
Joined
Apr 27, 2015
Messages
6,280
While DBG is looking, may I ask exactly what the line of code before the delay is?
 

GinaWhipp

AWF VIP
Local time
Today, 04:27
Joined
Jun 21, 2011
Messages
5,901
I have some code (not sure where it came from originally) that I have used....
SQL:
Sub Wait(dblSeconds As Double)
'To use Wait(10)
    Dim I As Double
    
    For I = 1 To dblSeconds * 100
        DoEvents      ' handle events
        Sleep (10)    ' suspend process without a performance hit
    Next

End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:27
Joined
Feb 28, 2001
Messages
26,996
Kitty77, I won't say this NEVER makes sense - but can you tell us WHY you want that delay?
 

kitty77

Registered User.
Local time
Today, 04:27
Joined
May 27, 2019
Messages
693
I have a form that uses a barcode reader to enter a number, then it advances to a new record. Makes it easy to enter a lot of entries.
However, it happens so fast, you don't get a chance to see the number. So, would like to slow it down to see the number before it goes to a new record.
 

isladogs

MVP / VIP
Local time
Today, 08:27
Joined
Jan 14, 2017
Messages
18,186
The article of mine that DBG was thinking of should have been DoEvents vs dbIdle.RefreshCache
if you want to pause sufficiently to let a process complete, DoEvents is probably your best best.
However to pause for a specified time, use the Sleep API which Gina's code depends on.
If you need it, I can supply the API code tomorrow
 

Micron

AWF VIP
Local time
Today, 04:27
Joined
Oct 20, 2018
Messages
3,476
For this particular case is there a benefit to using an API over something like

Code:
Public Function Pause(dblInterval As Double)
Dim Start As Double

Start = Timer
Do While Timer < Start + interval
Loop

End Function
My guess is that the API is more flexible from the standpoint of being usable with Do Events if required (which it is not required here). However, shouldn't new uses of this API be written for 64 bit as well?
 

deletedT

Guest
Local time
Today, 08:27
Joined
Feb 2, 2019
Messages
1,218
Kitty77, I won't say this NEVER makes sense - but can you tell us WHY you want that delay?
I had a different situation than @kitty77 if you're interested.
I had a report that had to be printed from different printers according to a specific situation.
I used to
  1. change application.printer
  2. print the report
  3. change back application.printer to what it was.
It takes around a second or less when a report sends its content to the printer, but it was too late and the job was sent to the printer after it was back to what it was. So I had to :
  1. change printer,
  2. send the job to printer,
  3. wait a while until the job actually was sent to printer
  4. change back application's printer back to what it was.
I tried DoEvents, but it wasn't enough. I had to use a timer and wait 2 seconds.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 08:27
Joined
Jan 14, 2017
Messages
18,186
OK here's the API code and Wait function that I use occasionally.
Place in a standard module

SQL:
'###############################################
#If VBA7 Then 'A2010 or later (32/64-bit)
    Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)      
#Else 'A2007 or earlier
    Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#End If
'###############################################

Sub Wait(N As Integer)
'creates a delay while other code executes
'N = number of seconds to wait

    Dim i As Integer
    For i = 1 To N
        Sleep 1000              'sleep function from api library file
    Next
    DoEvents
End Sub

NOTE: If all users have VBA7 (A2010 or later), you only need the first part of the API declaration for both 32-bit & 64-bit users

P.S. The SQL code option was certainly colourful in this case! :)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:27
Joined
Feb 28, 2001
Messages
26,996
Then of course there is the idea of just creating a modal message box with the information in the box using the vbOKOnly option. It will do nothing until you click OK or hit the enter key. The question in my mind is whether 3 seconds is enough to see anything or too long or just right. The "Goldilocks zone" for human perception is kind of variable, I think.

Code:
MsgBox "Serial Number is " & txtTheSerialNumber, vbOKOnly, "Press ENTER to continue"
 

Micron

AWF VIP
Local time
Today, 04:27
Joined
Oct 20, 2018
Messages
3,476
The question in my mind is whether 3 seconds is enough to see anything or too long or just right.
Then there is another question - how many times does a user have to click OK after each entered record before they retaliate by placing a thumb tack upside down on your desk chair?
 

kitty77

Registered User.
Local time
Today, 04:27
Joined
May 27, 2019
Messages
693
Yeah, pressing enter is not practical. Just need a delay...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:27
Joined
Feb 28, 2001
Messages
26,996
Every situation is different. That might have been a viable choice, but if not, then so be it.
 

kitty77

Registered User.
Local time
Today, 04:27
Joined
May 27, 2019
Messages
693
Of course. I appreciate all help and solutions...
 

apr pillai

AWF VIP
Local time
Today, 13:57
Joined
Jan 20, 2005
Messages
735
Public Function Pause(dblInterval As Double) Dim Start As Double Start = Timer Do While Timer < Start + interval Loop End Function
Code:
Public Function Pause(dblInterval As Double) 
Dim Start As Double 
Start = Timer 
Do While Timer < Start + interval 
   DoEvents 'Pass control to the processor to finish other waiting tasks in queue
Loop 
End Function
 

Micron

AWF VIP
Local time
Today, 04:27
Joined
Oct 20, 2018
Messages
3,476
I don't think so. The whole purpose of this function is to halt the other process, such as when the next query takes over and prevents a form from repainting i.e. the screen doesn't update. If your suggestion is that Do Events allows screen updates then the function does that without it. What you don't want is for Do Events to allow the query to take over before the screen updates.
 

Users who are viewing this thread

Top Bottom