Excel VBA: Wait Until a Process Complete

This article will show you how you can make your code wait until a process completes in Excel VBA.

Excel VBA: Wait Until a Process Completes (Quick View)

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 Overview to Wait Until a Process Completes in Excel VBA

Here is an overview of what we’ll actually do to make our code wait until a process completes.

VBA provides us with a built-in method called Wait. We’ll use this method to wait until the process completes.

But the problem is the method Wait takes a fixed time as the argument and then makes the code wait for that fixed time.

Now, how will we know how much time it needs to complete our process?

The answer is simple. We’ll use a Timer inside our code. We’ll activate the Timer once our desired process starts being executed. We’ll note the starting time.

Once the process completes, we’ll see the time associated with the Timer. Now we’ll the starting time from this time to get the time that the process took to complete.

Then we’ll use the VBA wait method to make the code for this time, to allow the process to finish completely.

So, 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

In case, the overview isn’t enough to understand the method clearly, here is a real-life example for you to see how we can make our VBA code wait until a process executes completely.

Below is a VBA code that extracts some data from a given PDF file.

⧭ 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.

• First, it takes the necessary inputs and opens the PDF.
• Next, it sends some short keys to copy data from the PDF.
• Next, it pastes the copied data into our worksheet.
• Finally, 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 has a tendency to overlap the processes. That is, it sometimes starts running a block of code before finishing the previous block of code successfully. 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.

Now, 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 of the processes 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

Now, run the code. 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.

Conclusion

Therefore, this is the method to wait until a process is completed in Excel VBA. Do you have any questions? Feel free to ask us in the comment section.

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