In this post I will teach you how to pause or delay macro for specific amount of time. This will be very useful specially when you developing web scraping applications. So I will explain you how to use this technique using a sample web scraping application.
So when you click the button in above application, program will update the share price of each and every company and then it will wait 10 minutes before refresh the share prices again. So the process will continue until the user save the file and close it. However if you need you can put a stop button to stop the process as well. Now let’s see how to do this pausing/waiting part. So this code should be added just before you go to the next loop. So the structure of the whole code should look like this
|
Loop start Code to extract data from web Code to pause the program Loop end |
So now I will explain you how to do this pausing part step by step. In this example I’m going to pause the program for 10 minutes.
First we need two variables to Store time.
|
Dim CurrentTime As Date Dim EndingTime As Date |
Then assign current time to the first variable.
|
CurrentTime = Now |
Then we assign time we want to resume the program to the second variable.
|
EndingTime = Now + TimeValue("00:10:00") |
Here you can change the time to control the gap between two loops. Format is hh:mm:ss
in this example I have set 10 minutes delay between two loops. Next you can use do until loop to pause the program until the time we need to resume it.
|
Do Until CurrentTime >= EndingTime Loop |
Inside that Do Until Loop you need to use following line to give the control to the user and for other programs.
|
DoEvents |
And also within the loop you need to update first variable to current time. Then only program can track whether current time is similar or higher than resume time.
|
CurrentTime = Now() |
So here is the full code to pause the program for 10 minutes
|
'------------------------------- 'Wait for 10 minutes '------------------------------- Dim CurrentTime As Date Dim EndingTime As Date CurrentTime = Now EndingTime = Now + TimeValue("00:10:00") Do Until CurrentTime >= EndingTime DoEvents CurrentTime = Now() Loop |
There are various methods to pause VBA programs. But there is an advantage of using this method. Because in this method during the delay time user gets the control to do any changes to the excel file. Because in some other methods user can't do any work in excel while it is waiting. This method specially useful for web scraping applications. Because user can analyse the results during delay time. But if you don't want to give control to the user while program is paused then you can use techniques described in this post. They are quite straightforward.
Sleep and Wait FunctionsWant to know how to extract data from websites automatically? Read this.
Web Scraping