Quick Jump
Introduction | Example | Tutorial | Applications | Comments

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

Did you find this helpful?

Please support wellsr.com by downloading an add-in below. Your support helps me continue to post VBA tutorials like this one.

$15

Mouse to Macro

Record your mouse clicks and cursor movements and convert them directly into VBA macros so you can automate your mouse.

FREE

wellsrPRO

Import hundreds of macro examples from wellsr.com without ever leaving Excel and organize your personal macro library with this free add-in.

$50

CF Shapes

Build stunning dashboards by dynamically controlling your shapes with this add-in that enables conditional formatting for shapes.


Tutorial - VBA Sleep

VBA Sleep 64-bit and 32-bit

The SleepDemo macro above works for both 32-bit and 64-bit versions of Excel. This is accomplished by the public declarations pasted at the top of the macro, which must be placed before any subs in your module. The good news is you don’t have to remember the exact syntax! By downloading wellsrPRO, you can automatically import the sample macro exactly as it’s written without typing a thing. :-)

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 Sleep 500.

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.


Application Ideas

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:


Comments

How do you plan on using the VBA Sleep function? Sleep opens up a world of advanced automation possibilities. Let me know your plans by leaving a comment!

When you’re done, please share this article with your friends on Facebook, Twitter, and Google+. Sharing articles with others is how wellsr.com will continue to grow, so I genuinely appreciate it.

I hope you’ll reach out to me via my VBA Consulting page if you’re working on a macro, but you’re too busy to get it done.

If you’re ready to free up your time in the office, visit my Excel Add-ins page for some powerful automation products.


About Ryan Wells

Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA products, including Mouse To Macro. See more Excel products, including online courses and books, by visiting the Excel VBA Store.