Pause Code in VBA
Even wonder, how it would be great if we could run the code as per our time permits because till that time we would manage to complete the other work and make that data ready for further job. This is the need to any kind of tasks where we need to run one set of code and work on other things as per code requirement and till the other portion of the code runs, we would be ready with the data we need to process with the same code. This can be done with Sleep and Wait application of VBA. By Sleep and Wait, we can pause the code at any given time. The time can be in seconds or time as per clock.
Syntax of VBA Pause in Excel
The syntax for VBA Pause is as follows:
Examples to Pause VBA Code in Excel
Below are the different examples to Pause VBA code in Excel.
Example #1 – VBA Pause
In this example, we will see how VBA Wait works. As we can see Wait application allows Time as Boolean. Which means if the value we feed is correct then we would get it as TRUE else FALSE.
To see how VBA Pause will work with Wait application in VBA,
Step 1: First, we would need a Module where we’ll be writing the code for this. So go to Insert menu and select Module from dropdown option as shown below.
Step 2: Now write the subcategory in the name of VBA Pause or in any other name as per your choice as shown below.
Code:
Sub VBAPause1() End Sub
For the VBA Pause, we need some set of text which we would like to print with a pause. Let’s consider that text be “Get… Set… Go…!!”. So we will select 3 different cells where we will keep these text words separately in each cell.
Step 3: Select the first range of cell as A1 or any other cell as per choice, and to give it the value, select Value function and give it a value as we fixed above. Or choose any value as per choice.
Code:
Sub VBAPause1() Range("A1").Value = "Get..." End Sub
Step 4: Assign the rest of the values in the same manner as we did for the first value. Here we are choosing cells from A1 to C1 as our range.
Code:
Sub VBAPause1() Range("A1").Value = "Get..." Range("B1").Value = "Set…" Range("C1").Value = "Go..!!!" End Sub
Step 5: By any chance, if we run the complete code, we would get all the values Get. Set…Go..!!! in one go from cell A1 to C1 as shown below.
Step 6: Now, if we want to get pause in between the whole pause, we would need to use the application Wait as shown below. We will be using this application just above the last range.
Code:
Sub VBAPause1() Range("A1").Value = "Get..." Range("B1").Value = "Set…" Application.Wait ( Range("C1").Value = "Go..!!!" End Sub
Step 7: In the bracket of Wait application we can write time in 24HRs format or in AM/PM format. We are considering 24HRs format. Now write the time in HH:MM:SS format when we want to run the rest of the code after a pause.
Code:
Sub VBAPause1() Range("A1").Value = "Get..." Range("B1").Value = "Set…" Application.Wait ("12 :26 :00") Range("C1").Value = "Go..!!!" End Sub
Step 8: Now compile the code and run it again. We will see, at cell A1 we got cell value as “Get…” and still the cursor is running on wait.
Step 9: And once the clock reaches at 12:25:00 Hours then, the rest of the cell values at cell B1 and C1 will again appear apparently as shown below.
Example #2 – VBA Pause
There is another way by which we can pause the code without adding or changing the time in Wait application multiple times. For this, we would refer the same code which we have seen in example-1.
Step 1: Now in the bracket of Wait application write Now() with TimeValue which we want to give. Let’s consider the pause of 30 seconds.
Code:
Sub VBAPause2() Range("A1").Value = "Get..." Range("B1").Value = "Set…" Application.Wait (Now() + TimeValue("00:00:30")) Range("C1").Value = "Go..!!!" End Sub
Step 2: So when we run the code, VBA will consider the current time and will add 30 secs pause. And it will work in a similar way as it worked in example-1. For check, run the code.
First, we will get the first cell Value at A1.
And after 30 secs pause, it will give the rest of the values at cell B1 and C1.
Example #3 – VBA Pause
In this example, we will see how VBA Pause works with Sleep application. Below is the syntax of Sleep application which allows ByVal dwMilliseconds only as LongPtr.
Now, this value is actually in VBA. We need to enter prebuilt code which is available in MS Docs as shown below.
Step 1: Now write the subcategory for VBA Pause as shown below.
Code:
Sub VBAPause3() End Sub
Step 2: For this, we would need to define 2 variables for Starting and Sleeping time as String as shown below.
Code:
Sub VBAPause3() Dim Starting As String Dim Sleeping As String End Sub
Step 3: Assign time function to the first variable which is Starting.
Code:
Sub VBAPause3() Dim Starting As String Dim Sleeping As String Starting = Time End Sub
Step 4: And use Msgbox to see the value stored in the Starting variable.
Code:
Sub VBAPause3() Dim Starting As String Dim Sleeping As String Starting = Time MsgBox Starting End Sub
Step 5: Now use Sleep application and allot the number of milliseconds till we want to sleep the functions.
Code:
Sub VBAPause3() Dim Starting As String Dim Sleeping As String Starting = Time MsgBox Starting Sleeping = Time End Sub
Step 6: Now in a similar way, give Time application to second variable Sleeping. And use MsgBox to see the value stored in the Sleeping variable as shown below.
Code:
Sub VBAPause3() Dim Starting As String Dim Sleeping As String Starting = Time MsgBox Starting Sleeping = Time MsgBox Sleeping End Sub
At last, compile the code and run the code by clicking on the Play button located below the menu bar as shown below. We will get the message box with the time which will be our Starting time.
Once we click on Ok, after 5000 Milliseconds, we will see another message box with Sleeping time as shown below.
The time of sleeping will start just after we click on the Ok button. It is not the time difference between Starting and Sleeping time. In fact, it is the time after we click on the Ok button of a first message box.
Pros of VBA Pause
- It is very useful when we have multiple functions to be performed with some time interval.
- This can be used in analysis and calculation purpose.
Things to Remember
- Process such as VBA Pause cannot be done by recording the macro.
- It is always better to use the process shown in example-2 for the Wait application where we will be safe from changing the time again and again instead of just keeping the pause of some seconds post after the current time.
- Sleep application from example-3 allows only Milliseconds
- The prebuild code shown at the starting of example-3 is an important part to apply as it will satisfy the Sleep application’s syntax.
Recommended Articles
This is a guide to VBA Pause. Here we discuss how we can pause the code at any given time using Wait and Sleep Application. You may also go through our other suggested articles –
