VBA Shell

This tutorial will demonstrate how to use the VBA Shell function.

We can use the VBA Shell function to call a separate, executable program from inside a VBA program. For example, if we need to open Notepad from Excel, we can use the VBA Shell function to do so.  If the Shell call succeeds, it returns the Windows TaskID value of the program it called. If the Shell call fails, it returns zero.

Shell has two input parameters: a required pathname for the program to call, and an optional windowstyle value controlling the style of the window where the program will run. The pathname value can include the program path / directory and arguments.

Call Shell

This code can form part of a macro to run Notepad by using the VBA Call command to call the Shell function.

Call Shell("notepad", vbNormalFocus)

For example:

 

Shell Wait

We can use the VBA Wait command to delay calling the Shell command for a specific period of time.

    Application.Wait (Now + TimeValue("00:00:05"))
    Call Shell("notepad", vbNormalFocus)

Therefore 5 second will pass before the Shell command is called.

Returning an Error from the Shell Function

If we have an error in our code when calling the Shell function, and error will be returned and our code will go into debug mode.

For example, in this macro, we have spelt “note pad” incorrectly.

  Call Shell("note pad", vbNormalFocus)

The result of running this macro will be:

 

Open an Existing File With Shell

If we have a specific file that we wish to open with the Shell command, we can include the file name in our code.

Call Shell("Notepad.exe C:\demo\shell_test.txt", vbNormalFocus)

 

If we spell the file name incorrectly, the file will not be found and a message box will appear asking us if we wish to create a new file.

 

 

Parameters used by the Shell function

The Shell function has 2 parameters – the name of the program to call, and the windows style that the program is going to use.  We have been using the vbNormalFocus in the examples above which means that when the program (in this case Notepad) is opened, it has the focus and it opens in the default position and size on the PC.

The Shell offers five other options:

vbHide                                 Hides the window and sets focus on that window

vbMinimizedFocus         Displays the window as an icon with focus

vbMaximizedFocus        Opens the program in a maximized window with focus

vbNormalNoFocus          Restores the window at its most recent position and size

vbMinimizedNoFocus   Displays the window as an icon and the currently active window stays active

Returning a Process ID from the Shell Command

When we run the Shell Command, it returns a Process or Task ID.  We can store the Process ID in a variable, and use that Process ID in running another command – for example, the TaskKill command to close the Notepad file.

Sub TestPID
   Dim ProcessID as integer
   processID = Shell("notepad", vbNormalFocus)
   Call Shell("Taskkill /F /PID " + CStr(processID))
   MsgBox ("Notepad ProcessID = " + CStr(processID))
End Sub

In the first line, NotePad is opened and the Process ID is assigned a value by Windows.  We store this value in the ProcessID variable.  We then use TaskKill to force Notepad to close the instance of NotePad that we have just opened.   The /F switch forces Notepad to end, and the /PID switch tells TaskKill to look for the Notepad Process ID value. The CStr function converts ProcessID to the string format that Shell, and MsgBox on the next line, both need.

Calling Other Programs With  Shell

Shell will open any other Windows program. For example, this code calls Excel, and opens Excel file ‘example_workbook.xlsx’:

Call Shell("Excel ""C:\DEMO\example_workbook.xlsx""", vbNormalFocus)

This shows the opened file:

 

ShellExecute and ShellExecuteEx vs the Shell Command

The Windows programming space offers ShellExecute and ShellExecuteEx functions that call external programs from software code. Compared to the VBA Shell function, these Windows functions offer more flexibility, but VBA does not support them and therefore this article does not cover them.