Excel

Create a Delay

Ease of Use

Easy

Version tested with

2000, 2002 

Submitted by:

Justinlabenne

Description:

Create a pause or a delay within your code 

Discussion:

Useful for flashing text or animated text, SendKeys usage, and others. These codes halt a process for a specified amount of time. Used as an alternative to Application.Wait, they provide an easier way to slow down your code is a visual effect is needed. There are many uses, and these are often used in dummy loops for progress bars, etc... The {Wait} code is a bit more accurate as the Sleep function uses the processor's ticks so it may vary slightly on different machines. 

Code:

instructions for use 

			
Option Explicit 
 'API declaration to suspend operation for a specified time (Milliseconds)
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 
 
 
Or 
 
Option Explicit 
 
Sub Wait(tSecs As Single) 
     '   Timer to create a pause
    Dim sngSec As Single 
     
    sngSec = Timer + tSecs 
    Do While Timer < sngSec 
        DoEvents 
    Loop 
End Sub

How to use:

  1. Open an Excel Workbook
  2. Copy the code
  3. Press Alt + F11 to open the Visual Basic Editor (VBE)
  4. Select INSERT > MODULE from the menubar
  5. API Calls always go at the top of the module! (Declarations Section)
  6. Paste code into the right pane
  7. Press Alt+Q to return to Excel
  8. Save workbook before any other changes
  9. NOTE: This can go into other code modules as well
 

Test the code:

  1. Where you need to create a delay in your code add the line
  2. Sleep 500& into our code
  3. where 500 is the number of milliseconds to delay (1/2 second)
  4. or if your using the {Wait} code,
  5. Wait 0.5 is equal to a 1/2 second delay
 

Sample File:

Sleep_API.zip 10.5KB 

Approved by mdmackillop


This entry has been viewed 424 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express