In Excel’s VBA, both the Wait and Sleep functions are used to introduce delays or pauses in the execution of code.
Suppose, you are creating a macro to automate data updates from an external source. It’s wise to ensure that you don’t flood the external server with requests. So, you must decide to introduce a delay between each data update to give the server some time to process each request.
In this post, we will know the process of using the Wait and Sleep functions and how they differ in their behavior and use cases.
Significance of Controlled Timing in Excel Macros
Controlled timing while building a macros is important in Excel to prevent abrupt transitions or overwhelming actions. It’s like orchestrating a well-timed performance that avoids sudden changes and disruptions.
Below is a list of scenarios focusing why controlled timing in beneficial:
- Data Processing with External Interfaces: Synchronizing data processing with external interfaces, like APIs or web services, ensures that data consistency is maintained.
- Iterative Calculations: In cases where macros involve complex data transformations, introducing short pauses can improve readability and help users follow the logic of the calculations step by step.
- Updating External Data Sources: When your macro interacts with external servers or databases to retrieve or update data, introducing pauses between requests can prevent overloading the server and potentially causing access issues.
VBA Wait Function
The Wait function in Excel’s VBA is not a built-in function, rather it’s a custom parameter used to pause the execution of code for a certain period of time. The syntax of the Wait function is:
Here, the time parameter should always be in an Excel Time Format. For Example, 24-Hour Time Format (14:30), 12-Hour Time Format with AM/PM (2:30:45 PM), Short Time Format (2:30), Long Time Format with Seconds (2:30:45), and many more.
How To Use the Wait Function in Excel
Here are the steps to use the Wait function in Excel:
- Open the Visual Basic Editor from the Developer Tab.
- Launch the Wait Function using the Application dot parameter. For Example: Application.Wait (Time).
- Specify the Time parameter.
- Execute the Code by pressing the F5 button.
Now, Let’s have a look at some examples of using the Wait function in Excel’s VBA.
Example 1: Pause a Code Until a Specific Date and Time
This example demonstrates how to pause the code execution until a specific date and time, such as September 1, 2023, at 3:30 PM. Here’s the code attached:
Sub PauseUntilSpecificDateTime() Dim targetDateTime As Date targetDateTime = #9/1/2023 3:30:00 PM# ' September 1, 2023, at 3:30 PM Do While Now < targetDateTime DoEvents ' Allow Excel to handle other events during the loop Loop ' Continue with your code here End Sub
Example 2: Pause a Code Until a Condition is Met
Suppose, we are waiting for a cell value to change to “Ready”. In that case, we can use the following VBA code:
Sub PauseUntilConditionMet()
Dim readyCell As Range
Set readyCell = Range("A1") ' Change to the target cell
Do While readyCell.Value <> "Ready"
DoEvents ' Allow Excel to handle other events during the loop
Loop
' Continue with your code here
End Sub
Example 3: Pause and Display a Countdown Timer For 10 Seconds
This example shows how to create a countdown timer that displays the remaining seconds before continuing the code execution. Here’s the attached code:
Sub CountdownTimer() Dim secondsToWait As Integer secondsToWait = 10 ' Wait for 10 seconds Dim endTime As Double endTime = Timer + secondsToWait Do While Timer < endTime Application.StatusBar = "Waiting: " & Format(endTime - Timer, "0") DoEvents Loop Application.StatusBar = False ' Continue with your code here End Sub
Example 4: Create a Code That Tells Time After Every Minute (Till 10 Loops)
The code we are about to show you will speak the current time every minute for a total of 10 times. The Application.Wait function is used to pause the code for the specified time interval, and Application.Speech.Speak is used to provide the spoken output.
Public Sub TalkingTime() Dim i As Integer Dim waitTime As Long waitTime = 60000 ' 1 minute in milliseconds For i = 1 To 10 Application.Wait Now + TimeSerial(0, 0, waitTime) Application.Speech.Speak "The Time is " & Format(Time, "hh:mm AM/PM") Next i End Sub
VBA Sleep Function
The Sleep function in Excel works by using the Windows API to introduce a delay in the VBA code. It pauses the execution of the program for a specified number of milliseconds. The syntax of the Sleep function is-
How To Use the Sleep Function in Excel’s VBA
There is no native Sleep function provided by Excel’s VBA. However, you can use the Sleep function from the Windows API to introduce a delay in your Excel VBA code.
Here are the steps to use the sleep function in Excel:
- Declare the Sleep Function inside the Visual Basic Editor. This tells Excel that you want to use a function from an external library.
- Use the Sleep Function. The function takes a single argument (Delay), the number of milliseconds to pause the execution.
Example of using Sleep Function:
Sub DelayExample() ' Sleep for 3 seconds (3000 milliseconds) Sleep 3000 ' Continue with your code here after the delay End Sub
Explanation: When you enter the Sleep function, it pauses the execution of your VBA code for the specified number of milliseconds. During this time, your program doesn’t perform any actions, and the CPU is effectively idle. This can be useful for creating time delays between actions, scheduling tasks, or introducing pauses for various purposes.
Let’s look at some more examples on how you use the Sleep function more efficiently.
Example 1: Pause Application for a Specific Duration (e.g., 10 Seconds)
#If VBA7 Then Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) #Else Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) #End If Sub PauseForDuration() Dim durationMilliseconds As Long durationMilliseconds = 10000 ' 10 seconds Sleep durationMilliseconds MsgBox "Pause is over!" End Sub
Example 2: Halt the Code for a User-Defined Delay Using InputBox
#If VBA7 Then
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
Sub UserDefinedDelay()
Dim delayMilliseconds As Long
On Error Resume Next ' Avoid error if user cancels the InputBox
delayMilliseconds = InputBox("Enter delay in milliseconds:", "User-Defined Delay")
On Error GoTo 0 ' Re-enable error handling
If delayMilliseconds > 0 Then
Sleep delayMilliseconds
MsgBox "User-defined delay is over!"
Else
MsgBox "Invalid delay value."
End If
End Sub
Example 3: Pause Before Taking an Action
#If VBA7 Then Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) #Else Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) #End If Sub PauseBeforeAction() Dim pauseDuration As Long pauseDuration = 5000 ' 5 seconds MsgBox "Waiting for " & pauseDuration / 1000 & " seconds..." Sleep pauseDuration MsgBox "Action after the pause!" End Sub
These examples demonstrate various scenarios where the Sleep function is used to introduce delays in Excel VBA code.
The Sleep function effectively pauses the execution of the code for the specified duration, allowing you to create delays between actions or implement timed behaviors.
Remember that using Sleep can make the application unresponsive during the delay.
What is the difference between Wait and Sleep in VBA?
Although the role of the two functions may seem similar, there are some noticeable differences between the two.
Here are the differences between the Wait and Sleep functions in Excel:
| Aspect | Application.Wait | Sleep Function from Windows API |
| Purpose | Introduces a delay in code execution. | Introduces a delay in code execution. |
| Availability | Built-in function in Excel VBA. | Requires declaring the function from the Windows API. |
| Syntax | Application.Wait TimeValue(expression) | Declare Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)
Sub DelayUsingSleep() Sleep durationInMilliseconds End Sub |
| CPU Usage | Consumes CPU resources during delay. | Minimal CPU usage during delay. |
| Application Responsiveness | May make Excel unresponsive | Does not impact Excel’s responsiveness during delay. |
| Flexibility | Allows waiting for a specific time or interval. | Only pauses for a specified duration. |
| User Interaction | Might block user interaction. | Does not block user interaction. |
| Error Handling | Can be interrupted by errors. | Not interrupted by most errors. |
| Application Dependency | Depends on the Excel application context. | Requires Windows API support. |
FAQ
Question: How to Sleep 0.5 seconds in VBA?
Answer: To sleep for a fraction of a second (e.g., 0.5 seconds) using the Sleep function from the Windows API in VBA Excel, you need to convert the time from seconds to milliseconds since the Sleep function works with milliseconds.
Question: What is the difference between sleep () and delay ()?
Answer: In Excel, both sleep () and delay () functions are used to introduce a pause or delay in your code execution. However, they are implemented differently and have distinct characteristics. For example the delay () function might block user interaction, whereas the sleep () function won’t block the user interaction.
Conclusion
The VBA wait and sleep commands are used to pause or delay the progress of any code. You can use these two commands interchangeably. However, using these functions can consume CPU resources. So, it is wise to use this function only when needed.
