Idle time is often wasted time, however, sometimes you just need to wait for certain events to happen before you can continue code execution. VBA extends a couple of approaches to managing your idle time – the most popular approach is the Sleep procedure. The VBA Sleep procedure pauses code execution for a certain amount of time putting the whole processes in a type of coma. It is one of the most popular approaches to pausing code execution, and at the same time simplest one. As I will try to prove – there are better, more productive approaches to pausing your code execution or utilizing potentially application idle time.
'Sleep for 1 second
Call Sleep(1000)
'Wait for 1 second
Call Application.Wait( DateAdd("s", 1, Now) )
What is the difference between both? And when to use the VBA Sleep function as opposed to the Application Wait VBA function?
VBA Sleep function
Let us start by introducing the VBA Sleep function. The Sleep function pauses the entire process for a certain delay specified in milliseconds. The drawback of using VBA Sleep is that it freezes Excel until the delay is done. This means that you cannot continue working on your spreadsheet and that Sleep is not to be used for long durations of time as in some extreme cases might cause Excel to assume the application has crashed.
Definition and Syntax
The Sleep function is not available by default in VBA, and has to be imported from the kernel32 library. For 64 Bit procedures we need to make sure to append the PtrSafe statement.
#If VBA7 Then
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal milliseconds As LongPtr) 'MS Office 64 Bit
#Else
Public Declare Sub Sleep Lib "kernel32" (ByVal milliseconds as Long) 'MS Office 32 Bit
#End If
Parameters
| Parameter | Variable Type | Description |
|---|---|---|
| milliseconds | Long | The amount of milliseconds the process should be paused for |
VBA Sleep example
The Sleep function is pretty straightforward to use:
Sleep 1000 'Sleep for 1000 milliseconds = 1 sec
Other VBA Sleep examples
'Sleep for 10 seconds Sleep 10000 'Sleep for 1 minute Sleep 60000 '60 * 1000 'Sleep for 1 hour Sleep 3600000 '3600 * 1000
Pros and Cons
The pros of using the Sleep function:
- Easy to use
- Precise sleep intervals (in milliseconds)
The cons of using the Sleep function:
- Requires importing Sleep function
- Freezes the entire process i.e. Excel does not respond
- There is no way to stop the Sleep function
The problem with the Sleep function is that it freezes your process entirely – preventing any input or interaction with your application (even breaking code execution). Why not use that time more productively? Or let the user cancel code execution?
VBA Application.Wait function
The VBA Application.Wait is a native VBA function that pauses code execution until a certain time is reached. As opposed to VBA Sleep, the Application Wait procedure does not freeze Excel during the pause. This means you can keep working on your Excel Workbook during the delay. Do remember, however, that during macro execution no changes can be undone (CTRL+Z will not undo changes made during macro running). What is more Application Wait does not allow you to wait for periods shorter than a single second.
Syntax
The syntax of the VBA Application.Wait function is:
Application.Wait( time )
Parameters
| Parameter | Variable Type | Description |
|---|---|---|
| time | Variant | The time when the function should return e.g. 15:30:00 for 3:30 pm. |
VBA Application.Wait example
The Application.Wait function is similarly pretty easy to use, although a little different than what you might expect with the Sleep function. This is because Application.Wait will wait until a time is reached instead of waiting for a precise interval of time.
Application.Wait "15:30:00"
This approach is of course less practical as usually you want to wait simply for a precise interval of time, like say 3 seconds. That is why we need to aid ourselves with the use of either the DateAdd function or the TimeValue function:
Application.Wait DateAdd("s", 1, Now) 'Wait for 1 second
'same as
Application.Wait Now + TimeValue("0:00:01) 'Wait for 1 second
What does the function above do? It adds 1 second to the current clock time and asks VBA to wait until that moment to return from the function. You can similarly wait for longer periods of time.
Other VBA Wait examples
'Wait for 10 seconds
Application.Wait DateAdd("s", 10, Now)
'Wait for 1 minute
Application.Wait DateAdd("n", 1, Now)
'Wait for 1 hour
Application.Wait DateAdd("h", 1, Now)
'Wait for 1 minute 30 seconds
Application.Wait DateAdd("s", 90, Now) '00:01:30 = 60 + 30
'Wait for 1 hour 2 minutes and 30 seconds
Application.Wait DateAdd("s", 3750, Now) '01:02:30 = 3600 + 120 + 30
Pros and Cons
The pros of using the Application.Wait function:
- Fairly easy to use (a little less obvious than Sleep)
- You can break the function at any time (does not freeze the entire process)
- Available natively from VBA
The cons of using the Application.Wait function:
- Allows you to wait for intervals shorter no shorter than 1 second
The Application.Wait function is a little less obvious to use, but won’t freeze your VBA Project allowing you to hit Esc at any time and resume control over your project.
VBA DoEvents
If you thought those were your only options – you were wrong. VBA thankfully allows you to also use another function called DoEvents. The function seemingly….does nothing. Seriously, it doesn’t do anything more that handle all MS Office events. Now why would we want to use the DoEvents function you might ask? Well, remember that Application.Wait does not allow you to wait for intervals shorter than 1 second? On the other hand Sleep freezes your application entirely although being more granular.
Want an example of the VBA DoEvents function?
DoEvents 'That's it!
DoEvents is especially useful when you have a lot of computations going on but want to keep your VBA Project responsive WITHOUT introducing significant (1 second) delays.
VBA DoEvents example
Usually when running a macro you will want to turn off ScreenUpdating focusing your macro only on your computations. This however may seem to your user like the application has frozen. DoEvents is to be used in pair with the ScreenUpdating property:
Application.ScreenUpdating = False 'Turn off screen updating '...code here... DoEvents 'Refresh the screen "on demand" '...code here...
Usually you might want to use it like this:
Application.ScreenUpdating = False 'Turn off screen updating '...code here... Do Until someThingHappened = True 'I am waiting for something to happen DoEvents Loop '...code here...
Conclusions
Let’s summarize our findings:
Use Application.Wait for > 1 second intervals. Application.Wait can be used well to pause code execution without freezing the application as long as you need to pause for more that 1 second (integer values).
Use DoEvents to refresh the screen on demand. Don’t want your user to get restless? Want to pause for a minimal interval just to refresh your application screen? Don’t bother with Sleep and use DoEvents instead!
Use Sleep for precise intervals. Need to wait for 500 milliseconds – no more no less? Ok… seems like you are stuck with the Sleep function.
