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.

How to Pause for Specific Amount of Time in Excel VBA

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 Functions

Want to know how to extract data from websites automatically? Read this.

Web Scraping

Contact Form

Name

Email *

Message *

Popular Posts