Introduction | Example | Tutorial | Applications

Introduction - VBA Application.Wait

Use the VBA Application.Wait method to pause your macro for a specific amount of time. Application.Wait is a simple way to delay your macro by a fixed number of seconds.

This isn’t the first tutorial I’ve posted about how to pause your macro. A few months ago I told you how to use VBA Sleep to add a time delay to your macro.

While VBA Sleep is still my favorite method for pausing a macro, it’s a bit more complicated than Application.Wait. When you know you’ll be pausing for more than 1 second, the Application.Wait method is a great way to do it because it’s easy to remember!

Stick with me and I’ll show you how it works.

Example - VBA Application.Wait

Pausing your macro for a certain number of seconds

Sub ApplicationWaitDemo()
Application.Wait (Now + TimeValue("00:00:05")) 'wait 5 seconds from now
MsgBox "It's been 5 seconds!"
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

Tutorial - VBA Application.Wait

Big Picture

Do you remember when I said Application.Wait was easy to remember? Well, I wasn’t kidding. There’s really not much left for me to explain!

The VBA Application.Wait method only accepts one argument: time. To wait 5 seconds, like I did in the demo above, you feed Application.Wait the current time with the Now function. Then, you use the TimeValue function to add 5 seconds to the current time. Once the 5 seconds elapses, your macro will continue. In the example demo, that means you’ll get a MsgBox telling you it’s been 5 seconds.

TimeValue argument

The syntax of the TimeValue function is the most difficult piece to remember. The TimeValue function converts a string to a time. Well, technically it returns the serial number equivalent of the time, but that’s beside the point. All you need to know is that, for the purposes of delaying your macro, your string should be entered in a format like hh:mm:ss, where hh represents hours, mm represents minutes, and ss represents seconds.

Because the TimeValue function accepts arguments in the “hh:mm:ss” format, you’re not able to use it to pause your macro for less than 1 second. You would need to use the VBA Sleep function if you want pauses for fractions of a second.

The important thing to remember is that, if you want to delay your macro by a specific amount of time, you must add the results of the TimeValue function to the current time calculated using the Now function, like I did in the example macro. Otherwise, your macro could be paused until the middle of the night!

Don’t worry, if you accidentally use the wrong syntax, you can stop your macro with the Esc key or the Break key.

Application Ideas - VBA Application.Wait

To inspire you, here are a few examples where I used the VBA Application.Wait method on the VBA Tutorials Blog:

These are actually some of my most popular posts. Are they the most popular because I used Application.Wait? Probably not, but who knows! Maybe when you use Application.Wait, you’ll be popular, too! ;-)

How do you plan on using the VBA Application.Wait method? It really can be quite helpful!

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

When you’re ready to free up your time in the office, subscribe using the form below.