Sometimes we need to pause the execution of our program for a specific time. In VBA, we can use a few methods to do this. VBA Wait method and Sleep functions are two popular methods used by VBA developers.
Why you need to use Sleep or Wait in VBA
-
Sleep or wait method let other applications complete their processes.
For example if you are developing a web scraping program, the Internet explorer should get sufficient time to load the webpage. -
To get input from users
Sometimes you may want to let the user input data to a VBA form before completing the rest of the process. -
Let the user to use another program manually
Using VBA we can automate various applications like MS Word, Powerpoint, Internet Explorer etc. But there are third party applications which we can not automate using VBA. In such cases we can use sleep function or wait method and let the user complete the middle process manually. Once that part is completed the macro can resume from that point.
Now Let’s look at how to use these two methods in VBA programs.
This is how to use the Wait method to pause a macro for 10 seconds.
|
Sub WaitForTenSeconds() Application.Wait (Now + TimeValue("0:00:10")) End Sub |
However Sleep is a windows function. It is not a VBA function like Wait. So you have to declare the name of the API at the top of your module. So this is how you can pause the program for 10 seconds using the Sleep function.
|
#If VBA7 Then Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal duration_Milliseconds As LongPtr) 'For 64 Bit Systems #Else Public Declare Sub Sleep Lib "kernel32" (ByVal duration_Milliseconds As Long) 'For 32 Bit Systems #End If Sub WaitForTenSeconds() Sleep 10000 'You need to input the time in milliseconds End Sub |
Here is a question lot of people get
Should I use a VBA sleep or Wait
First of all Sleep is not a VBA function. Sleep is a windows function. You should import it from Kernel32.dll
The kernel is the lowest level of any operating system. And it is the core or the central component of an operating system. It starts when the computer starts and keeps loaded until it is turned off. kernel manages the CPU resources, memory resources and processes of the computer. It also contains device drivers. Therefore when you do networking or use the file system, they all go through the kernel.
So functionality of both of these methods are the same. However Wait is more accurate than Sleep. But Sleep is more flexible as you can give the time in milliseconds. If you use Wait method, shortest time you can pause the program is 1 second.
Also there is another important aspect you need to consider. That is, if you use sleep or wait in VBA programs, you will notice that these functions suspend all other activities of Microsoft Excel. You can't even click in the excel sheets. However, you can work in other applications.
But sometimes you may need to pause the program for a longer time. And while the program is paused, you may need to do some work in the Excel application like typing, navigating between sheets, scrolling through the sheets etc. We don't often need this. But it may be needed specially in web scraping applications. Because sometimes users need to analyze gathered data while the web scraping program runs. However there is no VBA function or VBA method available for this. So we need to create our own custom pausing method. Read this post if you want to know how.
