WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Sleep Function

VBA Sleep Function

Excel VBA Sleep Function

VBA Sleep function is a windows function present under windows DLL files which is used to stop or pause the macro procedure from running for a specified amount of time after that certain amount of we can resume the program.

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 excel 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 the SLEEP function.

vba-sleep

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.. The sleep function allows users to pause our macro code for milliseconds. Using this, we can delay the process of macro code.

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

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.

vba sleep code

Example

Before I show you the way of writing the code, let me tell you about the sleep function a little more. It 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.

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

Example #1

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

Code:

#Sub Sleep_Example1()

End Sub

vba sleep function example 1.1

Declare two variables as a string.

Code:

Dim StartTime As String
Dim EndTime As String

vba sleep function example 1.2

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

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

StartTime = Time

sleep example 1.3

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

Code:

StartTime = Time
MsgBox StartTime

vba sleep function example 1.4

Now we will pause the code for 10 seconds using the sleep function. As I told, it pauses 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

sleep example 1.5

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

sleep example 1.6

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.

example 1.8

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.

example 1.7

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 pause the code running.

Example #2 – 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 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

example 2.1

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.

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

example 2.2

This code will display you 2 message boxes, 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 –

  • CSTR Function in Excel VBA
  • VBA WorkBook
  • What is a Long Data Type in VBA?
  • Excel VBA BreakPoint
8 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Sleep Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More