Introduction | Example | Tutorial | Applications | Comments

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

Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

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! ;-)


Comments

How do you plan on using the VBA Application.Wait method? Let me know in the comments below!

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.

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

I hope you’ll reach out to me via my VBA Consulting page if you have an idea for a macro, but you’d like some help.


About The VBA Tutorials Blog

Ryan Wells

The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.