You are reading the article Pausing Or Delaying Vba Using Wait, Sleep Or A Loop updated in September 2023 on the website Nhunghuounewzealand.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Pausing Or Delaying Vba Using Wait, Sleep Or A Loop
You may want some way of pausing or delaying VBA code execution and you can do this with two functions called Wait and Sleep. You can also do this using a loop, and we will look at that approach too, but first we’ll look at the functions available to Excel.
Why would you pause the code? Maybe you need to wait for another task to finish, for instance if you made a call to a Windows API/shell function. Or you may want to wait for the user to update data in the sheet, or you just want to run a macro at a set time.
Download the Workbook With Sample VBA Code
Enter your email address below to download the sample workbook.
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
Application.Wait
The .Wait method is available within Excel as a VBA function, as opposed to Sleep (see below). You can use it to specify that a macro is paused for a specific period of time.
This example makes the macro pause for approximately 10 seconds:
Application.Wait (Now + TimeValue("0:00:10"))
Or you can pause execution until a specific time e.g. this will pause a macro until 11am:
Application.Wait "11:00:00"
Wait does not accept delays of less than 1 second.
Sleep
Sleep is a Windows API function, that is, it is not part of VBA it is part of the Windows operating system. But we can access it by using a special declaration statement in our VBA.
This declaration statement serves two purposes. Firstly, it tells Excel where to find the function, secondly it allows us to use the 32bit version of the function in 32bit Excel, and the 64bit version of the function in 64bit Excel.
The Declare statement looks like this
#If VBA7 Then ' Excel 2010 or later Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr) #Else ' Excel 2007 or earlier Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long) #End If
Note : I’ve tested this code in Excel 2010 and 2013 only.
You can read more about these type of Declare statements and 32bit/64bit Office on Microsoft’s MSDN site
Sleep allows us to pause a macro for X milliseconds, which is a better resolution than Wait which has a minimum delay of 1 second. So to pause a macro for 5 seconds using Sleep we write this
Sleep 5000
Loops
The big drawback of using Wait or Sleep, is that Excel locks you out until the wait/sleep period has finished. You can use CTRL+BREAK to interrupt the macro, but Excel won’t accept input from the keyboard or mouse whilst paused using Wait or Sleep.
Events are suspended and anything you have scheduled using Application.OnTime is also delayed until the pause has finished. If you didn’t know what was happening, it looks like Excel has hung whilst Sleep or Wait are in effect. Background processes like printing and recalculation do carry on though.
To overcome this drawback you can use a loop to pause VBA execution, and also allow other things to happen whilst waiting. Macro execution isn’t actually paused, it’s just not doing anything other than running some loop commands.
A simple loop would look something like this
Sub WasteTime(Finish As Long) Dim NowTick As Long Dim EndTick As Long EndTick = GetTickCount + (Finish * 1000) Do NowTick = GetTickCount DoEvents End Sub
We write a sub called WasteTime, which when called from another sub or function has a value passed into it which is the number of seconds that we want it to do nothing, like so:
WasteTime(10)
The key here is the DoEvents method. DoEvents tells Excel to check if there is anything else the system wants to do like accept input from the keyboard or mouse. In this example, my macro is executing but allows the user to type into the worksheet. After approximately 10 seconds a message is then displayed.
The GetTickCount function is another Windows API function that we access by using another Declare statement. It returns the number of milliseconds since the computer started up.
The parameter, Finish that we pass into the WasteTime sub, is the number of seconds we want to delay code execution. To convert this to milliseconds, we multiply Finish by 1000.
Timer Resolution
In my examples I have used the word approximately when describing the delays I’m trying to achieve. I say approximately because the actual duration of the pause in the execution of the code depends on the resolution of the timer on your computer.
I could give you a very complicated description why this is so, or you could Google it, but let’s just say, if you want to delay something by 10s, then you will delay it for around 10s. It’ll only be out by a few milliseconds, which is perfectly fine for the type of things I am doing. I just wouldn’t use it to time Usain Bolt.
Disclaimer – Please test the code yourself, it may not work in your environment. All code provided as is without any warranty
You're reading Pausing Or Delaying Vba Using Wait, Sleep Or A Loop
Update the detailed information about Pausing Or Delaying Vba Using Wait, Sleep Or A Loop on the Nhunghuounewzealand.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!