EDUCBA

Home » VBA » Blog » VBA Tips » VBA Sleep

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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
Note: Before using sleep function there is one thing we need to keep in mind. We need to use this function in modules, not in the objects of excel. To use VBA to ensure that developer’s tab is turned on from the files tab in the options section.

How to Use Excel VBA Sleep Function?

We will learn how to use a VBA Sleep function with few examples in excel.

You can download this VBA Sleep Excel Template here – VBA Sleep Excel Template

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.

VBA Sleep Example 1-1

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

VBA Sleep Example 1-2

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)

VBA Sleep Example 1-3

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

VBA Sleep Example 1-4

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

VBA Sleep Example 1-5

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

VBA Sleep Example 1-6

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

VBA Sleep Example 1-7

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

Result of Example 1-8

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

Result of Example 1-9

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)

VBA Sleep Example 3-1

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

VBA Sleep Example 2-2

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

VBA Sleep Example 2-3

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

VBA Sleep Example 2-4

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

VBA Sleep Example 2-5

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

VBA Sleep Example 2-6

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

VBA Sleep Example 2-7

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

VBA Sleep Example 2-8

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.

Result of Example 2-9

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

Result of Example 2-10

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:

VBA Sleep Example 3-1

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)

VBA Sleep Example 3-2

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

VBA Sleep Example 3-3

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

VBA Sleep Example 3-4

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

VBA Sleep Example 3-5

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

VBA Sleep Example 3-6

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

Result of Example 3-7

Also, we can check that sheet 1 is renamed.

Result of Example 3-8

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

Result of Example 3-9

The second sheet is also renamed.

Result of Example 3-10

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
2 Shares
Share
Tweet
Share