Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Pause Program Execution In VBA - Sleep and Wait Functions

Sometimes we need to pause the execution of our program for specific time. In VBA, we can use few methods to do this. Wait and Sleep functions are two popular methods use by VBA developers.
I will show you how to use these two methods from below.

So if you want to use Wait method, it is very simple. You can do it like this.

Sub WaitForOneTenSeconds()

Application.Wait (Now + TimeValue("0:00:10"))

End Sub

So this will pause the code for 10 seconds.

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 top of your module. So this is how you can pause the program for 10 seconds using Sleep function.

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If

Sub WaitForTenSeconds()

Sleep 10000     'You need to input the time in milliseconds

End Sub

So functionality of both of these methods are very 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 thing you need to know about these two functions. That is, if you use these methods you will notice that these functions suspend all other activities of Microsoft Excel. Even you can't click in the excel sheets. But you can work in other applications.



But sometimes you may need to pause the program for a longer time. And while program is paused, you may need to do some work in excel application like typing, navigate between sheets, scroll the sheets etc. We don't often need this kind of requirement. But it may needed specially in web scraping applications. Because sometime user need to analyze gathered data while web scraping program runs. However there is no in-built function in VBA for this. But we can create our own custom pausing method. Read this post if you want to know how.

How to pause a macro for specific time

Contact Form

Name

Email *

Message *

Popular Posts