Introduction | Example | Tutorial | Applications
Introduction - VBA Sleep
Use the VBA Sleep function to add a time delay to your macro. There are a number of ways to add pauses or delays to your macros, but the Sleep function is my favorite since it lets you pause your macros for milliseconds.
The Application.Wait method and most Do While loops won’t let you pause your macro for less than 1 second. If you want to add a half second delay, you’ll be much better off using the Sleep function.
This tutorial will help you get started adding time delays to your macro!
Example - VBA Sleep
How to Add a Time Delay for Less Than a Second
#If VBA7 Then Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64-Bit versions of Excel #Else Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32-Bit versions of Excel #End If Sub SleepDemo() Sleep 500 'milliseconds (pause for 0.5 second) 'resume macro End Sub
Make powerful spreadsheets with our free VBA Developer's Guide Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
Tutorial - VBA Sleep
VBA Sleep 64-bit and 32-bit
Sleep uses the kernel32.dll Windows API, which some people don’t like. They prefer to use the native Excel Application.Wait function. I’ll be the first to tell you, I’m not a fan of Application.Wait! You can only delay your macro in one second increments. Sometimes, a full second is a long time to wait!
Units are in Milliseconds
The VBA Sleep function accepts a positive whole number argument and the units are in milliseconds. To make sure that sinks in, let’s go through a few examples.
If you want to pause your macro for half a second, like in the example macro, you would type
To pause for 1 second, you would use
Sleep 1000. It’s important to remember to always convert your seconds to milliseconds when using the Sleep function.
I use the Sleep function all the time in my advanced VBA Tutorials, like my mouse control macro. However, it didn’t dawn on me until recently that I never wrote a tutorial to introduce the Sleep function! I hope today’s post closes that gap.
To inspire you, here are a few examples where I used VBA Sleep on the VBA Tutorials Blog:
- How to Control your Mouse with VBA
- Make Excel Talk with Application.Speech.Speak (see comments)
- Simulate a Button Click with a Rectangle Shape
- Macro to Enable Trust Access to the VBA Project Object Model
- How to Maximize a Window in your Left Monitor
How do you plan on using the VBA Sleep function? Sleep opens up a world of advanced automation possibilities. For more VBA tips, techniques, and tactics, subscribe to our VBA Insiders email series using the form below.