For example, if I’m using my VBA code to open a .exe file or any other format file (such as Word or PowerPoint), and I want a delay in my code so that I can see that the file has opened correctly. This can easily be done using the inbuilt SLEEP and WAIT commands in Excel VBA. Let’s look at a couple of examples you can use to delay or pause your VB code in Excel.
Using VBA WAIT Command to Delay or Pause the Code
Using the WAIT method in Excel VBA, you can stop the code execution for a specific amount of seconds, minutes, and hours, all up to the specified time (say 3 PM or 9 AM) Below is the VBA code that would use the wait command to delay the code execution by 5 seconds and then shows the message box with the text “Let’s Go” I have used the Application.Wait method within an IF Then condition, which only shows us the message box once the wait time is over. Note that the Application.Wait method needs a time input so that it knows what time it needs to wait before moving to the next line of code. In this example code, I have used Now + TimeValue(“00:00:05”) – where Now is the time when the code is executed (which is automatically picked up from your system setting), and then a 5-second delay is added to it. Caution: When you use the WAIT command in VBA, while the delay is in progress, you will not be able to do anything in your Excel file. It would be as if your Excel has frozen, and it would only unfreeze once the wait time is over. Below is how my cursor changes when the code is running, and I can’t select anything in the worksheet. You can also use the WAIT method to delay your code execution till a specific time. For example, below is the VB code that would keep code execution on hold till the time on your system is 11:30 AM I strongly recommend you create a backup copy when working with the Wait or the Sleep command in Excel VBA.
Using VBA SLEEP Command to Delay or Pause the Code
Another way to pause a delay of the VBA code is by using the SLEEP command. Sleep is a Windows function and not a VBA method. Therefore, if you want to use the SLEEP method in your code, you will first have to reference the Windows DLL that has the SLEEP command so that we can use it in our VBA code. This is pretty straightforward. All you have to do is add a standard line of code above your actual VBA code that you want to run (see the code below, you can copy it from my code) Below is the VBA code that would use the sleep command to pause the code for 10 seconds In the above code, the first seven lines #If VBA7….#End If is the declaration that refers to kernel32 DLL file of Windows. Only after we have made this declaration can be used the SLEEP function in our VBA code (else it won’t work) Unlike the WAIT function, where you need to specify the time value till you want the code to be delayed, the SLEEP function takes the delay value in milliseconds. In our code, I have used Sleep (5000), this would pause my code for 5 seconds. If you want to pause your code for one second, you can use Sleep (5000) Just like with the WAIT function, when you pause the code using the SLEEP function, Excel would freeze, and you won’t be able to do anything while the delay is in progress. WAIT and SLEEP commands in Excel can have a few milliseconds of variation in the delay. So if you want to delay your VBA code for 1 second, it may be a little over or under that (it can be off by a few milliseconds). This hasn’t been an issue in any projects I worked with. In this tutorial, I showed you how to pause or delay your VBA code in Excel by using the WAIT or SLEEP commands. With the WAIT command, you need to specify the time till which you want to delay the code execution, and with SLEEP, you need to specify the time period itself for which you want to pause the code execution. Other Excel articles you may also like:
How to Create a Stopwatch in Excel (Basic + ToastMasters Style) Excel VBA Events – An Easy (and Complete) Guide Matrix Falling Numbers Effect in Excel using VBA. How to Automatically Insert Date and Time Stamp in Excel. Using Loops in Excel VBA. How to Open Excel Files Using VBA (Examples) Using VBA FileSystemObject (FSO) in Excel
