WallStreetMojo

WallStreetMojo

WallStreetMojo

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

VBA Wait Function

Excel VBA Wait Function

VBA Wait is a built-in function used to pause the code from executing for a specified amount of time, it is very similar to what we do in a sleep command and to pause a code we use application.wait method.

Some of the codes require sometime before progress to the next line of code due to other tasks to be completed. In these cases, we need to stop the code from being executed and pause for some time, then proceed with the execution. We can pause the code to be executed in two ways, the first one is the “Sleep” method, and the second one is the “Wait” method. In our earlier article, we have discussed the “VBA Sleep” method to pause the VBA code.

“Wait,” as the name itself says, it will hold the macro code to be executed to a specified time frame. Using this method, we need to specify the time our code should pause. We will see examples next.

The syntax of the WAIT function is as follows.

vba wait function

We need to mention the amount of time our code should pause. As you can see in the end, it says Boolean. This means it returns the result as Boolean values, i.e., TRUE or FALSE.

Until the specified time arrived, it says FALSE, and the moment specified time arrived, it returns TRUE.

This is unlike the SLEEP function because WAIT is a built-in function where SLEEP is a Windows Function. Before we access the SLEEP function, we need to mention the below code at the top of the module. But WAIT doesn’t require this.

Code:

#If VBA7 Then

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

‘For 64 Bit Systems

#Else

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

‘For 32 Bit Systems

End If

Examples to use Excel VBA Wait Function

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

Example #1

Assume you are working in an excel mid-day at 14:30:00, and you want your code to be paused until the time becomes 14:40:00. You can use the below code.

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:

Sub Wait_Example1()

Application.Wait "14:40:00"

End Sub

vba wait example 1.1

The code will stop your excel from working until the time reaches 14:40:00 in your operating system. Providing time like this is dangerous because we don’t always work from 14:30:00. It keeps varying all the time.

Let’s say whenever you are running the code. You want to wait for 2 minutes, how do you refer to this in your code?

So, we can use the VBA NOW function with TIME VALUE function to enter the specified time from the current time.

Just to remind you, the NOW () function returns the current date and time as per your computer system. TIMEVALUE function represents the time from 00:00:00 to 23:59:59 i.e. 11:59:59 P.M in 24 hours format. It converts the string value to a time value.

For Example NOW () + TIMEVALUE (00:02:30) means Current Time + 2 min 30 sec.

If the current time is 14:25:30, then it becomes 14:28:00.

To stop or pause your code from being executed from the current time to the next 10 minutes, you can use the below code.

Code:

Sub Wait_Example2()

Application.Wait (Now() + TimeValue("00:10:00"))

End Sub

vba wait example 1.2

It is important to use a NOW () function for accurate pause. Otherwise, there are chances your excel workbook paused until midnight. However, we can come out of the pause method at any point in time by pressing the Esc key or Break Key.

Example #2

Wait for 10 Seconds Every time Loop Runs

The wait method is well used with loops. There are situations where you may require to wait for 10 seconds every time the loop runs. For example, look at the below data.

example 2.2

To calculate Profit = (Sales – Cost), you want to create a loop, and after every loop, you want to wait for 10 seconds to check whether the result is accurate or not. The below code will do that.

Code:

Sub Wait_Example3()

Dim k As Integer

For k = 2 To 9
Cells(k, 4).Value = Cells(k, 2) - Cells(k, 3)
Application.Wait (Now() + TimeValue("00:00:10"))
Next k

End Sub

example 2.1

This code will calculate the profit column line by line. After the completion of the first line, it will wait for 10 seconds before it calculates the next line.

example 2.3

VBA Sleep vs. VBA Wait

VBA SLEEP VBA WAIT
It is not a VBA built-in function, needs a special code to access this function. It is a VBA built-in function, doesn’t require any special code to access this function.
Sleep requires milliseconds as the time frame. Wait requires a regular time frame.
We can delay the code in milliseconds. We can delay only in whole seconds.

Recommended Articles

This has been a guide to VBA Wait Function. Here we discuss how to use the wait method to pause the code from running with the help of practical examples and downloadable excel sheets. You can learn more about VBA from the following articles –

  • VBA Intersect
  • Split Function in VBA
  • Free VBA Course
  • Operators in VBA
0 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 Wait Excel Template

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