In this Article
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("notepad", vbNormalFocus)
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.
Dim ProcessID as integer
processID = Shell("notepad", vbNormalFocus)
Call Shell("Taskkill /F /PID " + CStr(processID))
MsgBox ("Notepad ProcessID = " + CStr(processID))
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.