VBA provides us with a built-in method called Wait. We’ll use this method to wait until the process completes. The problem is the method Wait takes a fixed time as the argument and then makes the code wait for that fixed time. We’ll use a Timer inside our code. We’ll activate the Timer once our desired process starts being executed, and we’ll note the starting time. Once the process is completed, we’ll see the time associated with the Timer. Now we’ll start from this time to get the time that the process took to complete.
Use the VBA wait method to make the code for this time, to allow the process to finish completely.
The final structure of our VBA code will look something like this:
⧠VBA Code:
Sub Wait_Until_a_Process_Completes()
Starting_Time = Timer
'Code of the Process
Total_Time = Timer - Starting_Time
Application.Wait (Total_Time)
End Sub
An Example to Wait Until a Process Completes in Excel VBA
⧠VBA Code:
Sub Extract_Data_from_PDF()
Set MyWorksheet = ActiveWorkbook.Worksheets("Sheet1")
Application_Path = "C:\Program Files\Adobe\Acrobat DC\Acrobat\Acrobat.exe"
PDF_Path = "E:\ExcelDemy\standardnormaltable.pdf"
Shell_Path = Application_Path & " """ & PDF_Path & """"
Call Shell(pathname:=Shell_Path, windowstyle:=vbNormalFocus)
SendKeys "%vpc"
SendKeys "^a"
SendKeys "^c"
Range("A1").PasteSpecial Paste:=xlPasteAll
Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)
End Sub
The code contains a number of processes within it.
- It takes the necessary inputs and opens the PDF.
- Sends some short keys to copy data from the PDF.
- It pastes the copied data into our worksheet.
- It kills the process.
Note that for the code to run successfully, each process has to be executed successfully first. We can’t afford to send the short keys until the PDF file opens completely.
Or we can’t afford to use the Paste method until all the data is copied successfully.
VBA tends to overlap processes. That is, it sometimes starts running a block of code before successfully finishing the previous block of code. As a result, codes involving complex processes often encounter unknown errors.
As with this example, if you run this code 10 times, it’ll show an error 2 to 3 times.
If you allow the code to wait for each of the processes to complete successfully, and then start the next process, you’ll never encounter an error.
Each time you run the code, it’ll run smoothly.
Here is the modified code. We’ve allocated a Timer with each process and made the code wait until they complete successfully.
⧠VBA Code:
Sub Extract_Data_from_PDF()
Starting_Time = Timer
Set MyWorksheet = ActiveWorkbook.Worksheets("Sheet1")
Application_Path = "C:\Program Files\Adobe\Acrobat DC\Acrobat\Acrobat.exe"
PDF_Path = "E:\ExcelDemy\standardnormaltable.pdf"
Shell_Path = Application_Path & " """ & PDF_Path & """"
Call Shell(pathname:=Shell_Path, windowstyle:=vbNormalFocus)
Total_Time = Timer - Starting_Time
Application.Wait (Total_Time)
Starting_Time = Timer
SendKeys "%vpc"
SendKeys "^a"
SendKeys "^c"
Total_Time = Timer - Starting_Time
Application.Wait (Total_Time)
Starting_Time = Timer
Range("A1").PasteSpecial Paste:=xlPasteAll
Total_Time = Timer - Starting_Time
Application.Wait (Total_Time)
Starting_Time = Timer
Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)
Total_Time = Timer - Starting_Time
Application.Wait (Total_Time)
End Sub
Run the code any number of times, it’ll never show an error. Each time you run it, it’ll be executed smoothly.
Things to Remember
Waiting is a good thing unless you use it unnecessarily. It’s only wise to use the VBA Wait method for codes with long complex processes. If it’s a small code with simple processes, no need to use it. It’ll only lengthen the running time, and as a result, will waste your time. So be a bit wise to use it.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Related Articles
- Excel VBA Wait Milliseconds
- How to Use Excel VBA DoEvents Function
- Excel VBA DoEvents and Wait Properties
- Excel VBA DoEvents Alternative
- How to Use Excel VBA to Wait Until Refresh Complete
What declaration format do you use for the timer… The variables are undefined.
Dear JESSE BATES,
Thanks for your comment. Declaring variable types is not mandatory in VBA. VBA by default assigns the necessary data type to any variable, you just need to put the values. In the given code, the timer variable contains the data type Double.
Please let us know if you have any other queries.
Thanks!
Thanks, I try to use here:
Starting_Time = Timer
ActiveWorkbook.RefreshAll
Total_Time = Timer – Starting_Time
Application.Wait (Total_Time)
…..other lines of code
but it doesn’t work. As Queries are updated in background, the Total_Time is not enough to end the process. And other lines of code are executed before ending the refresh process. Any idea?
I know that I can uncheck the flag where any query has “Enable background refresh”, but I’d like not to use it for many reasons.
Thank you, Alberto, for your question. I am replying on behalf of ExcelDemy. You can manually set the waiting time by using-
ActiveWorkbook.RefreshAll
Application.Wait (Now + TimeValue(“0:00:05”))
…..other lines of code
Here, it will wait for five seconds. You modify it to suit your needs.
I have this code. but the timer is not working
Sub RunPythonScript()
Starting_Time = Timer
Dim exe, pth
Dim fileToOpen As Variant
Dim wsMaster As Worksheet
Dim wbTextImport As Workbook
Dim myDir As String
myDir = “C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF”
exe = “C:\Users\uie34719\AppData\Local\Programs\Python\Python39\python.exe”
pth = “C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF\Main_Network_Script-WithCSV.py”
ChDir “C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF”
Shell “cmd.exe /c “”””” & exe & “”” “”” & pth & “”””””, vbNormalFocus
Total_Time = Timer – Starting_Time
MsgBox Total_Time
Application.OnTime (Total_Time), “OkGo”, schedule:=True
End Sub
Sub OkGo()
Range(“E2:H1048576”).ClearContents
fileToOpen = “C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF\1CSV_OUTPUT.csv”
Workbooks.OpenText fileToOpen
Set wbTextImport = ActiveWorkbook
Set wsMaster = ThisWorkbook.Worksheets(“Main_Template”)
wbTextImport.Worksheets(1).Range(“A1”).CurrentRegion.Copy wsMaster.Range(“E2”)
wbTextImport.Close False
End Sub
Hello Kitonski,
Here, I made some fixes. Please try this updated code:
Used the shell.Run command which is synchronous (True as the last parameter), ensuring the Python script completes before the VBA code proceeds.
Using the Now function to calculate the start and end times accurately.
To be safe used the Shell execution uses the correct quotation marks to avoid syntax errors.
Regards
ExcelDemy