VBA Sleep

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 part of other macro procedure or it could be input for the current macro. How can you pause the program when it is running? We can pause the code of procedure for some time specified by the user and after that certain amount of we can resume the program. We can do this in VBA by using SLEEP in Excel VBA function. In this article, we will see how to use VBA SLEEP function to pause your macro.

What Does VBA Sleep Function Do?

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

If you think we have a built-in function called SLEEP in Excel VBA then you are wrong because in VBA there is no such function called sleep rather we have a function called Sleep as a windows function. By entering a special set of code we can actually call this sleep function in VBA. In fact, sleep is a function present inside Windows DLL files, so we need to declare the nomenclature of API before the start of the VBA sub procedure. Below is the VBA code.

Code:

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

Copy the above and paste in your module before you start writing the macro codes. It should be pasted like this in your module.

Example of VBA Sleep Function

Before I show you the way of writing the code let me tell you about the sleep function little more. SLEEP function delays the process in milliseconds. So 1 second is equal to 1000 milliseconds, if you want to pause for 10 seconds it should be 10000 milliseconds.

Once the API code pasted before the start of Sub procedure, create a macro name.

Code:

#Sub Sleep_Example1()

End Sub

Declare two variables as a string.

Code:

Dim StartTime As String
Dim EndTime As String

For StartTime variable assign the value of TIME function. Note: TIME function returns the current time.

Code:

StartTime = Time

Now we will display this message in the message box VBA.

Code:

StartTime = Time
MsgBox StartTime

Now we will pause the code for 10 seconds using sleep function. As I told sleep function pause the code in milliseconds so to pause for 10 seconds we need to use 10000 milliseconds.

Code:

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.

Code:

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 will hold macro beginning time and ending time. Run this macro, at first we will see macro starting time i.e. current time in your system.

Click on OK, it will sleep for 10 seconds. You can see the buffer symbol.

After 10 seconds it will start to resume the code so, it will show the end time i.e. after waiting for 10 seconds what’s the current time now.

Now you can see macro started at 10:54:14 and ended at 10:54:24 i.e. exactly the 10-second difference is there. In those 10 seconds, VBA paused the code running.

Sleep Function in Loops

Sleep is best used with loops in VBA. For example, I want to insert serial numbers from 1 to 10 using Do while loop in VBA.

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

Code:

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 below code.

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 you 2 message box, the first one will show the starting time and the second one will show the end time.

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

Recommended Articles

This has been a guide to VBA Sleep Function. Here we discuss How to use Sleep in Excel VBA along with a practical example and downloadable excel template. Below are some useful excel articles related to VBA –

• Change Font Color in VBA
• StrComp Function in Excel VBA
• CSTR Function in Excel VBA
• VBA Like Operator
• Work With VBA WorkBook
• Long Data in Excel
• Timer Function in VBA
• VBA For Each Loop Example.
• Apply Break in VBA