Code often needs to pause and wait for something else to happen. There are a number of options to do this.
In Excel, you can use Application.Wait
This has the downside not being very accurate (the example above will pause for a maximum of 5 seconds, the actual pause ending between 4 and 5 seconds at the point that a 'new' second starts. This will also lock up Excel from a users point of view (ie the user interface will freeze and eventually may display an 'application not responding' message) and is only available in Excel.
An alternative that works in Excel, Word, PowerPoint and Outlook is the following
This also has the advantage of not locking up the application from a users point of view but has the same problem of a lack of accuracy, ending between 4 and 5 seconds at the point that a 'new' second starts.
So what can the Windows API do to help with this? The Sleep function uses the Windows API to pause processing for a more accurate time than VBA will measure, accurate to around 15 milliseconds.
The Sleep function is one of the simplest Windows API functions - for details, see https://docs.microsoft.com/en-us/windows/win32/api/synchapi/nf-synchapi-sleep.
In your module declaration section add the following code:
And then the following function in the main body of the module:
Example usage:
This is not limited to Excel and it is accurate to a number of milliseconds. However, as with Application.Wait, it does freeze the user interface, so you'd only want to use this for a very short pause.
The answer, then, is to use the following procedure.
Example usage
This works on Excel, Word, PowerPoint and Outlook. It is accurate to 100ths of a second. It does not lock up the user interface. And no need to use the Windows API.
Limitations: Windows only
Comments