Advent Day 20 Ms Access Gurus
Home Code

VBA > API > Sleep

Pause executing VBA to let something else happen, like wait for a form to close, or something external like Transfer Spreadsheet from Excel, or copy and move files.

It is much better to Sleep than to loop DoEvents because DoEvents is very resource-intense and slows down the process you are waiting for.

Screen shot

Examples


wait for 1 second:

Call SleepMilliSeconds(1000)

wait for 10 seconds:

Call SleepMilliSeconds(10000)

wait for 1/2 second:

Call SleepMilliSeconds(500)

try this in the Debug Window (Ctrl-G)

?Now : SleepMilliSeconds(1000) : ? Now & " sleep is over"

Use Space Colon Space ( : ) to delimit statements in the Debug (Immediate) Window so you can do multiple things. You can also write loops in here.

Logic

Pass the number of milliseconds (1/1000th of a second) to pause. Behind the scenes, conditional compilation is used to declare the Windows Sleep API differently depending on which version of the editor (VBA7, VBA6) is running.

API = application programming interface

Parameter

Code

'*************** Code Start *****************************************************
' Purpose  : wait a specified number of milliseconds using the Windows Sleep API   
' Author   : crystal (strive4peace) 
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm

'--------------------------------------------------------------------------------

'                              API Declaration
'--------------------------------------------------------------------------------
'
'declare Sleep API 
#If VBA7 Then
   Private Declare PtrSafe Sub Sleep _
      Lib "kernel32"  _
      (ByVal dwMilliseconds As Long) 
#Else
   Private Declare Sub Sleep _
      Lib "kernel32"  _
      (ByVal dwMilliseconds As Long)   
#End If

'--------------------------------------------------------------------------------

'                              SleepMilliSeconds
'--------------------------------------------------------------------------------
' 
Public Sub SleepMilliSeconds( pnMilliseconds As Long)
'pause further execution for specified milliseconds
'one millisecond = 1/1000th of a second   
   Sleep pnMilliseconds 
End Sub
'
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed.
'   You may not sell this code alone, or as part of a collection.
'   Use at your own risk.  
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

References

Docs / Office VBA Reference / Language reference / Reference / Statements / Declare

Help: Declare statement

Docs / Office VBA Reference / Language reference / Reference / Statements / Call

Help: Call statement

Back Story

Today is one day before the longest night of the year, so sleep seemed to be appropriate ... although, you might be shopping, wrapping presents, getting ready for or hosting guests, planning or going to a party, or something else holiday related ~ so you actually get less sleep!

Share with others

here's the link to copy:

https://MsAccessGurus.com/VBA/Code/API_Sleep.htm

unsecure:    http://MsAccessGurus.com/VBA/Code/API_Sleep.htm

Do you have something to say or share?

It is interesting to hear from you. Was something not clear? Did you find a bug? Is an explanation wrong or not sufficient? Do you want the code do more (there is always more)?

Some of you write to say thanks and tell me what you're doing with Access ... its nice to get an echo back. I want you and others to be good with Access, and other Office applications like Excel, Word, and PowerPoint ... and Windows. Take advantage of the strengths in each to manage your information wisely.

Are you a developer? Do you want to share? Email to ask about getting your pages added to the code index.

When we communicate, collaborate, and appreciate, we all get better. Thank you. Email me at info@msAccessGurus.com

Goto Top