[VBA] How to Wait for an External Application to Close (Windows API)

2025年12月29日

Introduction

When you launch an external application using the Shell function in VBA, VBA only starts the application and immediately proceeds to the next line of code without waiting for it to finish. However, there are times when you want to pause the macro until the user finishes a task, such as editing and saving a file in Notepad.

This “waiting for an external process” can be achieved by combining several Windows API functions. This article explains the advanced technique of monitoring a process started by Shell and waiting for VBA execution to resume only after that process ends.

VBA Sample Code to Wait for External App

This macro launches the Windows “Calculator” and pauses VBA processing until the user closes the Calculator window. Once the Calculator is closed, a message is displayed.

The Declare statements and Const definitions must be placed at the very top of the module (before any procedures like Sub).

'--- Declare API functions and constants at the top of the module ---
' Compatible with both 64-bit and 32-bit systems
#If VBA7 Then
    Private Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As LongPtr
    Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As LongPtr, lpExitCode As Long) As Long
    Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As Long
#Else
    Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
    Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
#End If

' Permission to query process information
Private Const PROCESS_QUERY_INFORMATION = &H400
' Constant indicating the process is still active
Private Const STILL_ACTIVE = &H103


' Launch Calculator and wait until it closes
Sub WaitForProcessToClose()
    Dim taskID As Long
    Dim processHandle As LongPtr
    Dim exitCode As Long
    
    MsgBox "Starting Calculator. The next message will appear when you close Calculator."
    
    '--- 1. Launch Calculator with Shell and get the Task ID ---
    taskID = Shell("calc.exe", vbNormalFocus)
    
    '--- 2. Get the Process Handle from the Task ID using OpenProcess ---
    processHandle = OpenProcess(PROCESS_QUERY_INFORMATION, 0, taskID)
    
    '--- 3. Poll the process exit code ---
    Do
        ' Get the current exit code of the process
        GetExitCodeProcess processHandle, exitCode
        ' Release CPU so Excel doesn't freeze
        DoEvents
    ' Continue looping as long as the exit code is STILL_ACTIVE
    Loop While exitCode = STILL_ACTIVE
    
    '--- 4. Release the Process Handle using CloseHandle ---
    CloseHandle processHandle
    
    MsgBox "Calculator has finished."
End Sub

Explanation of the Code

1. Shell Function

Shell starts the application and returns its Task ID (taskID). This ID is the number Windows uses to identify that specific process.

2. OpenProcess API Function

OpenProcess uses the Task ID to obtain a “Process Handle” (processHandle) that allows VBA to interact with that process. A handle is like a “permit” to access the process.

PROCESS_QUERY_INFORMATION: Requests permission to query information about the process.

3. GetExitCodeProcess API Function and Do…Loop

This is the core part that monitors the termination of the process.

GetExitCodeProcess: Retrieves the termination status of the specified process handle (processHandle) and stores it in the exitCode variable.
STILL_ACTIVE: A special constant value returned by GetExitCodeProcess meaning the process is still running.
Do…Loop While exitCode = STILL_ACTIVE: As long as exitCode equals STILL_ACTIVE (meaning the Calculator is still open), the loop continues. When the user closes the Calculator, the value of exitCode changes (usually to 0), and the loop ends.
DoEvents: This command must be included inside the loop. Without DoEvents, Excel would not have the resources to perform other tasks, making it look like it has frozen.

4. CloseHandle API Function

The process handle obtained with OpenProcess must be released using CloseHandle when it is no longer needed. Neglecting this can lead to memory leaks, so always include this step.

Summary

In this article, we explained an advanced technique using Windows API to pause VBA execution until an external application launched by Shell is closed.