Deprecated: pathinfo(): Passing null to parameter #1 ($path) of type string is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/urvanov-syntax-highlighter/class-urvanov-syntax-highlighter-langs.php on line 86
Deprecated: pathinfo(): Passing null to parameter #1 ($path) of type string is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/urvanov-syntax-highlighter/class-urvanov-syntax-highlighter-langs.php on line 86
When running macros, I have often found the need for an Excel wait or pause, mainly so that other things can happen. DoEvents surely helps for waiting for things to happen within Excel, but sometimes you need to wait for processes to start or finish outside of Excel before moving onto other steps in VBA.
Why Wait?
I am a big fan of automating processes, especially with VBA in Excel. For example, I update and save a spreadsheet to SharePoint that a Power BI model is using. I have a Power Automate process set up that automatically refreshes the Power BI model whenever this particular spreadsheet is updated. The challenge is that Power BI temporary locks the workbook as it reads it, so if I am updating a few times in a short amount of time, Excel needs to wait until the SharePoint file is unlocked.
Also, I have another process where I call a Python script and once that is done, I continue on with VBA code to process the workbook Python outputs. I need Excel to wait while the Python code runs. Side note: let me know if you would like me to write more about these other automated processes.
The Solution
Good news – making Excel wait is very easy! Here is the simple code:
|
1 |
Application.Wait (Now + TimeValue("0:00:50")) |
This will have Excel wait for 50 seconds since the time is represented as (H:MM:SS). You can adjust as needed.
Bonus
In my code, I don’t need it to wait every time – only when it runs into an issue saving the workbook on SharePoint. Here is how I have set my code using an error handler in my VBA macro.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
On Error GoTo waitforupdate lsc.SaveAs Filename:= _ "https://sharepoint.com/sites/Reporting/Milestones/Status.xlsx" _ , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False lsc.Close Exit Sub waitforupdate: Application.Wait (Now + TimeValue("0:00:50")) Resume Next |
I hope this helps you! Please comment or reach out if you would like to see other topics covered. I truly enjoy writing about Excel tips and tricks, especially VBA and automation!
Addressed in this post (to help others find this):
- How to pause Excel
- How to make Excel pause
- Make VBA pause
- Make VBA wait
- Make Excel wait
- VBA code wait, VBA macro wait
- VBA code pause, VBA macro pause
- VBA wait for another application