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.
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? Let me know in the comments below!
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
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.Follow