VBA Sleep

VBA Sleep Function

Sleep function in VBA is actually a windows function. It is similar to wait function in VBA. It is used to slow down or pause or we can say halt the running of a specific code by some specified time. Sleep function needs to be called in VBA while declaring it in the code. How we do that is we will learn in today’s topic.

As explained above VBA Sleep is a windows function and it is present in the kernel database of windows. The method of declaring and calling sleep function in VBA is different for both 32 bit operating systems and 64 bit operating systems. It is basically a windows API function.

The syntax to use VBA Sleep function is as follows:

Sleep (Time in Mili Seconds)

So if we need to slow down or halt the code for 1 sec we need to write the code as:

Sleep 1000

1000 is the mili seconds which equals to 1 second and it will slow down the code for 1 sec. If we want to slow down the code for 5 seconds the code will be:

Sleep 5000

The declaration for sleep function is as follows:

#If VBA7 Then ' Excel 2010 or later
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
#Else ' Excel 2007 or earlier
Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
#End If

How to Use Excel VBA Sleep Function?

VBA Sleep Function – Example #1

What we are going to do in this example is we will pop up a message to the user that macro will stop for five seconds. And exactly after five seconds, we want a second message to pop up which says macro resumed.

Follow the below steps to use Sleep Function in Excel VBA:

Step 1: Go to the Developer tab and click on Visual Basic to open VB Editor.

Step 2: Once the VB Editor is open click on Insert Tab and then click on modules to insert a new module.

Step 3: Now use the declaration statement to use sleep function. As I am using windows 64-bit operating system I will use the declaration statement for the same.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Step 4: Now declare the sub-function to start writing the code.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample()

End Sub

Step 5: Use Mgsbox function to display the message that macro is going to be paused for five seconds.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample()

MsgBox "MAcro going to be paused for five seconds"

End Sub

Step 6: Use Sleep function to pause the macro for five seconds.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample()

MsgBox "MAcro going to be paused for five seconds"
Sleep 5000

End Sub

Step 7: Now use msgbox function to display the message that macro has been resumed.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample()

MsgBox "MAcro going to be paused for five seconds"
Sleep 5000
MsgBox "Macro has been resumed"

End Sub

Step 8: Run the code from the run button provided or press F5 to see the result. We see the first message is displayed.

Step 9: Once we click on Ok and wait for five seconds we see another message.

There was a pause for five seconds between both the messages.

VBA Sleep Function – Example #2

Now what we are going to do in another example is that I have four variables A, B, C and D. First, I want to add the value of A and B and display it and after 5 seconds I want to display the value of the addition of A, B, C, and D.

Follow the below steps to use Sleep Function in Excel VBA:

Step 1: Go to the Developer tab and click on Visual Basic to open VB Editor.

Step 2: Once the VB Editor is open click on Insert Tab and then click on modules to insert a new module.

Step 3: Now use the declaration statement to use sleep function. As I am using windows 64-bit operating system I will use the declaration statement for the same.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Step 4: Now declare the sub-function to start writing the code.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample1()

End Sub

Step 5: Declare six variables A, B, C ,D, X and Y to store values.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample1()

Dim A, B, C, D, X, Y As Integer

End Sub

Step 6: Give Random Values to A, B, C, and D.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample1()

Dim A, B, C, D, X, Y As Integer
A = 10
B = 15
C = 20
D = 25

End Sub

Step 7: Store the value of A + B in X.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample1()

Dim A, B, C, D, X, Y As Integer
A = 10
B = 15
C = 20
D = 25
X = A + B

End Sub

Step 8: Display the value of X.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample1()

Dim A, B, C, D, X, Y As Integer
A = 10
B = 15
C = 20
D = 25
X = A + B
MsgBox X

End Sub

Step 9: Now use the sleep function to have a pause for five seconds.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample1()

Dim A, B, C, D, X, Y As Integer
A = 10
B = 15
C = 20
D = 25
X = A + B
MsgBox X
Sleep 5000

End Sub

Step 10: Now in variable Y store the value of X +C + D and display it.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample1()

Dim A, B, C, D, X, Y As Integer
A = 10
B = 15
C = 20
D = 25
X = A + B
MsgBox X
Sleep 5000
Y = X + C + D
MsgBox Y

End Sub

Step 11: Run the above code from the run button provided or by pressing the F5 key and see the result. We see the first message is displayed as.

Step 12: Press OK and the macro waits for five seconds and display the next result.

VBA Sleep Function – Example #3

In this example, we want to rename two worksheets sheet 1 and sheet 2 as Anand and Aran respectively. But the time duration between both should be five seconds. Basically, we want the macro to pause after renaming sheet 1 and then rename sheet 2. Currently, both the sheets are named as follows:

Follow the below steps to use Sleep Function in Excel VBA:

Step 1: Go to the Developer tab and click on Visual Basic to open VB Editor.

Step 2: Once the VB Editor is open click on Insert Tab and then click on modules to insert a new module.

Step 3: Now use the declaration statement to use sleep function. As I am using windows 64-bit operating system I will use the declaration statement for the same.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Step 4: Now declare the sub-function to start writing the code.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample2()

End Sub

Step 5: Activate worksheet 1 and rename it by the following code:

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample2()

Worksheets("Sheet1").Activate
Worksheets("Sheet1").Name = "Anand"
MsgBox "Sheet 1 renamed"

End Sub

Step 6: Now use sleep function to use delay for five seconds.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample2()

Worksheets("Sheet1").Activate
Worksheets("Sheet1").Name = "Anand"
MsgBox "Sheet 1 renamed"
Sleep 5000

End Sub

Step 7: Now rename the sheet 2 by the following code.

Code:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Sample2()

Worksheets("Sheet1").Activate
Worksheets("Sheet1").Name = "Anand"
MsgBox "Sheet 1 renamed"
Sleep 5000
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Name = "Aran"
MsgBox "Sheet 2 renamed"

End Sub

Step 8: Now run the code and we see the first message is displayed.

Also, we can check that sheet 1 is renamed.

Step 9: Press ok and wait for five seconds for the next message and second sheet to be renamed.

The second sheet is also renamed.

Things to Remember

• VBA Sleep is a window function so in order to use we need to use declaration statements.
• There are different declaration statements for different types of operating systems.
• Simply using VBA Sleep freezes the macro for the time duration provided.
• The time parameter given to VBA sleep function is in milliseconds.

Recommended Articles

This is a guide to VBA Sleep Function. Here we discuss how to use Excel VBA Sleep Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA VLOOKUP
2. VBA IsError
3. VBA Copy Paste
4. VBA CDEC