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.
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.
Tutorial - VBA Application.Wait
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.
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:
- Create an Awesome Excel Splash Screen For Your Spreadsheet
- Make Excel Talk with Application.Speech.Speak
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 ready to free up your time in the office, subscribe using the form below.