Excel Vba Sleep Function To Pause Your Macro Code

Excel VBA Sleep Function

There are situations where we need to pause our macro running process to complete other sets of tasks. Other sets of tasks could be part of our coding or other Macro procedures, or they could be input for the current excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more. How can you pause the program when it is running? We can pause the procedure code for some time specified by the user. After that certain amount, we can resume the program. We can do this in VBA by using the SLEEP function.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Sleep Function (wallstreetmojo.com)

What Does VBA Sleep Function Do?

SLEEP, as the name itself, says, “sleep for some time,” “rest for some time,” “pause for a time,” time off for some time,” etc. So, for example, the Sleep function allows users to pause our macro code for milliseconds. Using this, we can delay the process of Macro code.

Below is the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

Code:

Please copy the above and paste it into your module before you start writing the macro codes. We should paste it like this in the module.

Example

Before we show you how to write the code, let us explain the Sleep function a little more. It delays the process in milliseconds. So, 1 second is equal to 1,000 milliseconds. So, if you want to pause for 10 seconds, it should be 10,000 milliseconds.

Example #1

Once we paste the API code before the start of the sub procedure, create a Macro name.

#Sub Sleep_Example1()

End Sub

Declare two variables as a string.

Dim StartTime As String Dim EndTime As String

For the StartTime variable, assign the value of the TIME function. Note: TIME in excelTIME In ExcelTime is a time worksheet function in Excel that is used to calculate time based on the inputs provided by the user. The arguments can take the following formats: hours, minutes, and seconds.read more function returns the current time.

StartTime = Time

Now, we will display this message in the message box VBAMessage Box VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

StartTime = Time MsgBox StartTime

We will pause the code for 10 seconds using the Sleep function. As we said, it pauses the code in milliseconds to pause for 10 seconds. So, we need to use 10,000 milliseconds.

Sub Sleep_Example1()

Dim StartTime As String Dim EndTime As String

StartTime = Time MsgBox StartTime

Sleep (10000)

End Sub

Now, use the second variable, EndTime, and assign the current time.

Sub Sleep_Example1()

Dim StartTime As String Dim EndTime As String

StartTime = Time MsgBox StartTime

Sleep (10000)

EndTime = Time MsgBox EndTime

End Sub

Now, two variables: StartTime and EndTime. It will hold macro beginning and end times. Run this Macro. At first, we will see the Macro starting time: the current time in your system.

Click on “OK.” It will sleep for 10 seconds. Next, you can see the buffer symbol.

After 10 seconds, it will resume the code to show the end time. After waiting for 10 seconds, what’s the current time now?

Macro started at 10:54:14 and ended at 10:54:24, i.e., the 10-second difference. In those 10 seconds, VBA pausedVBA PauseVBA Pause helps to pause a code from executing for a particular period. You can pause the VBA code for a specific period by using two functions, and those functions are “Wait” & “Sleep.”read more the code running.

Example #2 – Sleep Function in Loops

Sleep is used best with loops in VBA. For example, we want to insert serial numbers from 1 to 10 using the Do while loop in VBALoop In VBAA VBA loop in excel is an instruction to run a code or repeat an action multiple times.read more.

After inserting the one number, my code should wait for 3 seconds, so when the loop runs 10 times, it should be 30 seconds.

Sub Sleep_Example2()

Dim k As Integer

k = 1

Do While k <= 10

Cells(k, 1).Value = k

k = k + 1

Sleep (3000)

‘1000 milliseconds is 1 second so 3000 is equal to 3 seconds

Loop

End Sub

Run this code, and you have to wait for a minimum of 30 seconds to complete the process.

To track the exact time, use the below code.

Sub Sleep_Example2()

Dim k As Integer Dim StartTime As String Dim EndTime As String

StartTime = Time MsgBox “Your Code Started at " & StartTime

k = 1 Do While k <= 10 Cells(k, 1).Value = k k = k + 1 Sleep (3000) ‘1000 milliseonds is 1 second so 3000 is equal to 3 seconds Loop

EndTime = Time MsgBox “Your Code Ended at " & EndTime

End Sub

This code will display two message boxes. The first one will show the starting time. In addition, the second one will show the end time.

Note: While running this code, you cannot use Excel. Even the escape key will not work.