VBA Shell

The VBA Shell function runs a command in the operating system shell.

The shell refers to the interface, typically where you type commands, to run programs. This is called a command line interface or CLI.

In Windows, the shell is commonly known as the Command Prompt. To access it, click on the Windows button and type cmd (Windows 10). Windows finds the program for you, so click on it to start it.

In other versions of Windows the process for starting the Command Prompt is similar. Just search for cmd.

Once you are in the command prompt you can type commands like dir to list the folder contents:

Or you can start programs, like Notepad:

Commands like dir, copy, del etc are known as internal commands because they are built into the shell - they are part of the code that forms the shell, not separate programs.

Programs like Excel, Notepad etc are known as external commands because they are programs in their own right, but can be called or executed from the shell.

The method for calling internal and external programs using the VBA Shell function is different.

You can also use the Shell to run scripts like batch files, PowerShell scripts, PERL, Python etc.

VBA Shell Syntax

The syntax for calling Shell is

Shell (Program,WindowStyle)

Program can be the name of an internal or external command or a script. It can contain any arguments or switches required by the program, as well as the drive and path to the program itself

WindowStyle determines how the window of the called program behaves. WindowStyle is optional but if it is omitted, the program starts minimized with focus. You can specify the WindowStyle using a constant or the actual numeric value, as shown here:

Focus is where keyboard input is sent to. If focus is on Excel and you type, the characters appear in Excel. If focus is on Notepad, the characters appear in Notepad.

When you use Shell it returns a Variant (Double) data type that contains the process ID of the program you called. You can use this PID to terminate the program later.

If your attempt to run a program with Shell was unsuccessful, it returns 0.

Examples of Using Shell

External Commands

After we declare a Variant variable called PID, we call Shell to start Notepad like this:

PID = Shell("notepad", vbNormalFocus)

Using vbNormalFocus starts Notepad with its most recent size and position, and changes focus to it.

To close the same instance of Notepad:

PID = Shell ("TaskKill /F /PID " & PID, vbHide)

If you wanted to open Notepad with a specific file then supply the filename, and path if needed:

PID = Shell("notepad c:\MyFiles\TextFile.txt", vbNormalFocus)

If you are using a shell that doesn't understand spaces in file names or paths, then you need to wrap the file name/path in two sets of double quotes, inside the double quotes that delimit the Program string:

I'm using Windows 10 and don't have that issue though.

But if you had wanted to open a file

c:\My Files\Text File.txt

and your shell required that this be wrapped in "", then you'd write the string like this

PID = Shell("notepad ""c:\My Files\Text File.txt""", vbNormalFocus)

The same goes for any path you need to specify for the actual command/script name. In this example I'm calling a batch file (Text Parser.bat) to process the text file (Text File.txt):

PID = Shell("""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt""", vbNormalFocus)

All of those """ look a bit strange but let me explain. The first and last " mark the beginning and end of the string that specifies the program being called, including any parameters, switches and file(s) it will use:

"""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt"""

If we remove those " we are left with the Program string itself, which is composed of two separate strings, one for the path\batch file (red), and the other for the path\file the batch file will use (blue).

""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt""

When this is passed to the Shell one of the double " is removed so what is actually seen in the Shell is

"c:\My Scripts\Text Parser.bat" "c:\My Files\Text File.txt"

Which looks like two normally delimited strings.

Internal Commands

To call an internal command like dir, you must start an instance of the actual shell, which in Windows is cmd.exe. You then say that you want to use the dir command. The /k switch specifies that cmd should not terminate after dir has finished. You can terminate cmd later.

PID = Shell("cmd /k dir", vbNormalNoFocus)

Asynchronous Execution

Calls to the Shell are executed asynchronously, VBA will make the call and then continue without waiting for whatever program you called to finish whatever job you asked it to do.

This probably isn't an issue if you are just trying to open a text file in Notepad. But if you are say, trying to list files in a directory and then you want to import the resultant CSV into Excel, you need to make sure that the CSV file is complete before you do that import.

One way to do this would be to make your VBA sleep or pause.

Error handling

Make sure that you use error handling when making Shell calls. Just in case the program or file you want isn't in the location you expect, or is missing altogether.

Download Sample Workbook

The sample workbook I've prepared contains several examples of VBA Shell calls including the use of error handling and terminating programs you have started.