VBA Shell Function

If you’ve ever needed to open Notepad, run a script, or launch another program from inside Excel or Access, the VBA Shell function is exactly what you need. It allows you to control external applications directly from your macro, making your automation more powerful and seamless.

📌 VBA Shell Function – Purpose, Syntax, and Examples

Building on that, the ‘Shell’ function in VBA allows you to run external programs, scripts, or commands directly from your macro.

🎯 Purpose

The primary purpose of the Shell function is to launch external applications or commands from VBA. This can be helpful for:

• Running reports and opening them in default apps
• Launching cleanup scripts or batch files
• Automating multi-program workflows

Syntax

Shell(pathname[,windowstyle])

Arguments

• pathname (Required) – The full path or command of the application or file you want to run.
• windowstyle (Optional) – An integer specifying how the window appears.
  Common values:
  • 0 – Hidden
  • 1 – Normal focus (default)
  • 2 – Minimized focus
  • 3 – Maximized focus
  • 4 – Normal (without focus)

Example: Open a File in Notepad

Let’s say you have a VBA program that downloads a report from a website and saves it as a text file. You want to open this text file in Notepad after it has been saved. You can use the Shell function to achieve this:

Sub OpenReportInNotepad()
    Dim reportPath As String
    reportPath = "C:\Users\username\Documents\Report.txt"
    Shell "notepad.exe """ & reportPath & """", vbNormalFocus
End Sub

In this example:

• The report path is assigned to a variable.
• The Shell function opens Notepad and loads the specified text file.
• Quotation marks are handled with “”” to support file paths containing spaces.

Notes and Best Practices

• The Shell function works only on Windows OS.
• Paths containing spaces must be wrapped in double quotes.
• You can also run .bat, .cmd, .vbs, .js, and other scripts.
• Use Chr(34) as an alternative to embed quotes:

  Shell Chr(34) & "C:\Program Files\MyApp\MyApp.exe" & Chr(34)

• The function returns a Process ID, which can be used with Windows API to monitor or control the launched program.

Tip: Chr(34) represents a double-quote character in VBA (“).

Understanding VBA Shell Function with Examples

🧪 Example 1: Running an External Program using Shell Function

The Shell function in Visual Basic for Applications (VBA) is used to execute an external program or a Windows command from within VBA. It returns the Process ID (PID) of the launched application, which can be used with Windows API functions to control or monitor that process.
This example shows how to launch Notepad, retrieve its Process ID, and wait for it to close before continuing the macro using the WaitForSingleObject API function.

Code:

#If VBA7 Then
    Private Declare PtrSafe Function OpenProcess Lib "kernel32" ( _
        ByVal dwDesiredAccess As Long, _
        ByVal bInheritHandle As Long, _
        ByVal dwProcessId As Long) As Long

    Private Declare PtrSafe Function WaitForSingleObject Lib "kernel32" ( _
        ByVal hHandle As Long, _
        ByVal dwMilliseconds As Long) As Long

    Private Declare PtrSafe Function CloseHandle Lib "kernel32" ( _
        ByVal hObject As Long) 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 WaitForSingleObject Lib "kernel32" ( _
        ByVal hHandle As Long, _
        ByVal dwMilliseconds As Long) As Long

    Private Declare Function CloseHandle Lib "kernel32" ( _
        ByVal hObject As Long) As Long
#End If

Sub RunAndWaitForNotepad()
    Dim processID As Long
    Dim hProcess As Long
    Dim result As Long

    ' Launch Notepad
    processID = Shell("notepad.exe", vbNormalFocus)
    MsgBox "Launched Notepad with Process ID: " & processID

    ' Constants
    Const SYNCHRONIZE = &H100000
    Const INFINITE = &HFFFFFFFF

    ' Open handle to the process
    hProcess = OpenProcess(SYNCHRONIZE, 0, processID)

    If hProcess <> 0 Then
        MsgBox "Waiting for Notepad to close..."
        ' Wait until Notepad is closed
        result = WaitForSingleObject(hProcess, INFINITE)
        CloseHandle hProcess
        MsgBox "Notepad has been closed."
    Else
        MsgBox "Unable to open process handle."
    End If
End Sub

Explanation:

• Shell(“notepad.exe”, vbNormalFocus) launches Notepad and returns its Process ID.
• OpenProcess gets a handle to the running Notepad process using its ID.
• WaitForSingleObject pauses the macro until Notepad is closed by the user.
• CloseHandle releases the process handle to clean up system resources.

When to Use the Process ID:

The Process ID alone is not very useful unless paired with API calls. With APIs, you can:

• Wait for a program to complete (as above)
• Check its status
• Force it to close (with TerminateProcess — use carefully)

Conclusion:

While the Shell function is commonly used to run programs, using the Process ID with API functions unlocks advanced process control. This is particularly helpful when your macro depends on another program to complete before proceeding.

Let me know in the comments if you’d like to see more real-world use cases of automating external programs through VBA!

🧾 Example 2: Opening a File Using Shell Function

The Shell function can also be used to open a specific file using its default program. For example, if a user wants to open a Word document, they can use the Shell function to launch the document in Microsoft Word.

Code:

Sub OpenFile()
    Dim documentPath As String
    documentPath = "C:\Users\username\Documents\Sample.docx"
    Shell "explorer """ & documentPath & """"
End Sub

1. The first line of code declares a Sub procedure named OpenFile.
2. In the second line, the documentPath variable is declared as a String data type. This variable will contain the path of the document to be opened.
3. The path of the document is specified in the third line, in this example, it is a Word document located in the Documents folder.
4. The Shell function is then used in the fourth line to open the file’s default program, in this case, Microsoft Word. The explorer command is used to open a file explorer window, and the file path is enclosed within double quotation marks, as shown in the code. This is done to handle any spaces in the file path.
5. The End Sub statement marks the end of the procedure.

Explanation: In this example, the Shell function is used to open a specific document in its default program. The document path is specified in the documentPath variable and is then passed as an argument to the Shell function. This will open the document using its default program, which is Word in this case.

⚙️ Example 3: Launching Multiple Programs Using Shell Function

The Shell function allows you to launch more than one program from VBA. This can be helpful when automating a task that requires opening multiple applications — for example, launching Notepad and Calculator together.

You can also capture and use the Process IDs of each application to control or monitor them using Windows API, if needed.

Code:

Sub LaunchMultiplePrograms()

' Launches multiple programs using the Shell function.
Dim pid1 As Long
Dim pid2 As Long

' Launch Notepad
pid1 = Shell("notepad.exe", vbNormalFocus)

' Launch Calculator
pid2 = Shell("calc.exe", vbNormalFocus)

' Display process IDs
MsgBox "Notepad Process ID: " & pid1 & vbCrLf & "Calculator Process ID: " & pid2

End Sub

Explanation:

• Shell(“notepad.exe”, vbNormalFocus) launches Notepad and returns its Process ID.
• Shell(“calc.exe”, vbNormalFocus) launches Calculator and returns its Process ID.
• The MsgBox displays both process IDs with a line break (vbCrLf).

💡 Although this example only displays the PIDs, they can be used with API functions like WaitForSingleObject or TerminateProcess to interact with these programs programmatically.
💡To wait for both programs to finish before continuing, you can use Windows API calls like in Example 1. Let me know if you’d like to see a combined example that waits for both.

🪟 Example 4: Specifying the Window State using Shell Function

The Shell function allows you to control how the launched program appears on screen. By default, it opens in the foreground (vbNormalFocus). However, you can specify other states like minimized, maximized, or hidden using Shell window style constants such as vbMinimizedFocus, vbMaximizedFocus, and vbHide.

This is useful when running background tools silently without disrupting the user.

Code:

Sub SpecifyWindowState()

' Launches Notepad in a hidden window state
Dim processID As Long
processID = Shell("notepad.exe", vbHide)

End Sub

📌 Useful for background tasks without UI interruptions.

Explanation:

• vbHide tells VBA to run the program in a hidden window.
• Notepad will still be running in the background, but it won’t be visible to the user.
• The process ID is returned, which you can use to track or terminate the hidden process if needed.

Use Case Example:

You might use vbHide to:

• Run a script or helper app that doesn’t require user interaction.
• Avoid cluttering the desktop with temporary apps.

If you want to wait until the hidden process completes, pair this with API functions as demonstrated earlier in Example 1.

🗂️ Example 5: Running a Batch File Using the Shell Function

The Shell function can be used to run batch files (.bat) from within VBA code. This is useful when you need to automate tasks that involve executing a sequence of commands in a batch file, such as file management or system maintenance tasks.

Code:

Sub RunBatchFile()

'Launches a batch file to delete temporary files using the Shell function.
Dim batchFilePath As String
batchFilePath = "C:\Scripts\CleanTempFiles.bat"
Shell batchFilePath, vbNormalFocus

End Sub

🧼 Great for automating disk cleanup, backups, or admin tasks.
How this works:

1. In this example, a batch file located at “C:\Scripts\CleanTempFiles.bat” is executed using the Shell function.
2. The Shell function takes the path of the batch file as its argument. The second argument, vbNormalFocus, specifies that the batch file should run with normal window focus.
3. The batch file might contain commands to delete temporary files or run any other system commands that can be handled by a batch file.
4. When this code is run, the batch file will execute, performing its predefined tasks, such as cleaning temporary files, and display the result in a normal window.

Explanation: The Shell function in this example demonstrates how you can use it to run external scripts, such as batch files, directly from your VBA code. This opens up many possibilities for automating file and system management tasks within VBA.

📥 Download the Example File

Want to try the VBA Shell function examples yourself? Download our ready-to-use .xlsm file that contains:
✅ A clean intro sheet with instructions
✅ 5 fully documented Shell function examples:

• Run and wait for Notepad to close
• Open a file in its default program
• Launch multiple programs
• Launch a program in a hidden state
• Run a batch file (.bat)

🔧 All macros are easy to access and edit — just press Alt + F11 to explore the VBA code.

Download: analysistabs – VBA Shell Function Examples (.xlsm)

💡 Tip: This file works only on Windows as it uses Windows-specific Shell and API functions.

🧠 Conclusion

The VBA Shell function is a powerful automation tool for triggering external programs and commands from within your Excel or Access macros. Whether you’re running a quick report viewer, launching multiple applications, or managing system tasks with batch files, understanding the Shell function gives you access to a wider automation ecosystem beyond Excel.

💬 Have questions or want more advanced Shell automation examples (e.g., TerminateProcess or error handling)? Leave a comment!