Pearson Software Consulting Services

     ShellAndWait - Waiting For A Shelled Process To Complete

This page has been replace by ShellAndWait.aspx
         Introduction To Shellnt>
You can use the
Shell command in VBA to start another program. Unlike programs started via Automation with the CreateObject function, you have no control over the Shell'd program. Your code can't issue commands to it, you can't stop it, nor can you directly determine when it ends.  Shell is essentially the same as running a program from the Run dialog on the Windows Start menu.

Shell And Wait Procedures
When you use the
Shell command in VBA to start another program, your VBA code continues executing immediately after starting the program with Shell. The VBA code does not wait for the program started by Shell to terminate.  There are any number of sources on the internet for "Shell And Wait" type procedures, and they are all essentially the same -- they call the WaitForSingleObject Windows API function to wait for the process to close. The ShellAndWaitSimple procedure described on this page and available in this workbook is typical of all the other shell and wait procedures out there.  It calls the Shell command, Opens the process, and then uses WaitForSingleObject to return when the  shell'd program terminates. Nothing special.

What is different on this page is the ShellAndWaitEnhanced procedure. This procedure allows you to display messages to the user on a control on a Userform. ShellAndWaitEnhanced will cycle through the messages you define, displaying each one for a prescribed interval.  You can use these messages to inform the user that the Shell'd program is still running and that the system has not become "hung".

I originally wrote ShellAndWaitEnhanced for a diagnostics  and "detect and repair" type program for a large application. The diagnostics program used Shell to run the SysInfo program to generate a complete system information report file that would be sent to tech support staff. SysInfo can take several minutes to generate a complete report, and I wanted to periodically notify the user that the diagnostics program was still running as it should and that the neither the program nor the system had become hung.  

This page describes two procedures, ShellAndWaitSimple and ShellAndWaitEnhanced, that you can use to cause your VBA code to wait until the Shell'd program terminates.  The code is too long to cleanly present on a web page. You can download the code in an example workbook here. You can also download the modShellAndWaitSimple and the modShellAndWaitEnahanced modules separately. These modules are entirely self-contained. You can import either or both of the modules into your project and use the code exactly as it is written. No modification or additional code is required. Each module contains the constants, variables, functions, and Windows API declares required by its function.

The ShellAndWaitSimple and ShellAndWaitEnhanced functions may be used in any VBA code. There is nothing in the code that restricts its usage to Excel.  You can use the procedures in Excel, Word, PowerPoint or any application that supports VBA. ShellAndWaitSimple requires in VBA5 and ShellAndWaitEnhanced requires in VBA6.  If you attempt to open and run code in the example workbook in Office 97, you'll get compiler errors in the modShellAndWaitEnhanced,  modTest,  and UserForm1 modules.  To work in Office 97, copy the modShellAndWaitSimple module to another workbook. 

Both ShellAndWaitSimple and ShellAndWaitEnhanced are available for Visual Basic 6. Download the entire VB6 Project here, or just the modShellAndWaitSimpleVB6 module here, or just the modShellAndWaitEnhanced module here. Each module is entirely self contains and may be imported into your VB6 project with no code modifications. In the VB6 modules on the Function version of ShellAndWaitSimple and ShellAndWaitEnhanced are included.

ShellAndWaitSimple

ShellAndWaitSimple is a procedure that simply waits for the Shell'd program to terminate.  The function declaration is

Public Function F_7_AB_1_ShellAndWaitSimple( _
    A_7_AB_1_CommandLine As String) As Boolean

You provide in the parameter A_7_AB_1_CommandLine the command or name of the program that will be executed by the Shell function.  No validation is performed to ensure that A_7_AB_1_CommandLine is a valid string for Shell. A error will occur and a message box will be displayed if A_7_AB_1_CommandLine is not valid.

F_7_AB_1_ShellAndWaitSimple will wait until program is terminated.  It returns a result of TRUE if the Shell'd program was successfully started and terminated. It returns a result of FALSE if an error occurred. In case of an error, it displays a message box indicating the nature of the error. To use F_7_AB_1_ShellAndWaitSimple simply call the procedure and receive its return code. For example,

Dim Result As Boolean
Dim CommandLine As String
CommandLine = "Notepad.exe"
Result = F_7_AB_1_ShellAndWaitSimple(CommandLine)

The Result variable will receive the return value of  F_7_AB_1_ShellAndWaitSimple . If an error occurs, Shell may or may not have started the program, and the shell'd program may or may not still be running. These conditions depend on the nature of the error. In any case, if an error occurs, a message box is displayed indicating the nature of the error.

ShellAndWaitSimple will work in Office97 or later.  It will also work in Visual Basic version 6 with no changes. It has not been tested in the NET framework.

 

ShellAndWaitEnhanced

The ShellAndWaitEnhanced is a procedure that waits for the Shell'd program to terminate, in the same manner as ShellAndWaitSimple, but it will periodically display messages on a Userform's control . These messages can indicate to the user that the Shell'd program is still running and that the system has not become hung.   You can define whatever messages, and as many messages, as you desire. The messages in the A_7_AB_1_MessageArray array will be cycled at the interval specified by A_7_AB_1_MessageCycleTimeSeconds -- each message is displayed for this many seconds.  If you are calling the ShellAndWaitEnhanced procedure from within a form, that form may be displayed either modally or modelessly. If you call ShellAndWaitEnhanced from a standard code module, and use a form only to display the messages, that form must be displayed modelessly.

The function declaration for ShellAndWaitEnhanced is

Public Function F_7_AB_1_ShellAndWaitEnhanced( _
    A_7_AB_1_CommandLine As String, _
    A_7_AB_1_MessageControl As MSForms.Control, _
    A_7_AB_1_MessageCycleTimeSeconds As Long, _
    A_7_AB_1_MessageArray As Variant, _
    A_7_AB_1_DoEventsMilliseconds As Long, _
 
    ByRef A_7_AB_1_ErrorNumber As Long, _
    ByRef A_7_AB_1_ErrorText As String, _
    Optional A_7_AB_1_MessageOnClose As String = vbNullString) As Boolean

where

A_7_AB_1_CommandLine
is the command line to be passed to the Shell function. This must be a valid command string. No validation is performed to ensure it is valid.  An error will occur and a message box will be displayed if the command string in invalid.

A_7_AB_1_MessageControl
is the control on a VBA Userform (typically a TextBox or Label control) in which status messages are to be displayed. Whatever control you use, that control must support either the Text or Caption property. An error will occur if the control does not support one of these properties. The messages to be displayed in A_7_AB_1_MessageControl are passed to the procedure in the A_7_AB_1_MessageArray array. The message control may be within another containing control such as a Frame control.

A_7_AB_1_MessageCycleTimeSeconds
is the number of seconds that each message in  A_7_AB_1_MessageArray will be displayed. The procedure cycles through the array, displaying each message for A_7_AB_1_MessageCycleTimeSeconds seconds. When the end of the array is reached, the procedure loops back to the beginning of the array.

A_7_AB_1_MessageArray
is an array of strings, each of which is a message to be displayed in the A_7_AB_1_MessageControl. Each message in  A_7_AB_1_MessageArray will be displayed for A_7_AB_1_MessageCycleTimeSeconds seconds. When the end of the array is reached, the procedure cycles back to the beginning of the array. There is no limit to the number of messages in the array.  An error will occur if  A_7_AB_1_MessageArray is not an array or it its data type is not a String.

A_7_AB_1_DoEventsMilliseconds
is the number of milliseconds that the procedure will wait before calling DoEvents to let other threads execute. A typical value is 500, every half second. If this parameter is less than or equal to zero, the default value of 500 is used.

ShellAndWaitEnhanced requires Office 2000 or later.  It has not been tested in the NET environment.

A_7_AB_1_ErrorNumber
is a reference to a Long type variable that will receive the error number if an error occured.

A_7_AB_1_ErrorText
is a reference to a String type variable that will receive the descriptive error text associated with  A_7_AB_1_ErrorNumber.

A_7_AB_1_MessageOnClose
is a message to display in the message control when the Shell'd program terminates. If this is omitted or is vbNullString, no closing message is  displayed in the control.


Downloads

VBA Versions

Download the source code workbook here, which includes both the modShellAndWaitSimple and modShellAndWaitEnhanced modules plus an example UserForm.

Download only the modShellAndWaitSimple.bas code module. You can Import this module to your existing VBAProject.

Download only the modShellAndWaitEnhanced.bas code module. You can Import this module to your existing VBAProject.

VB6 Versions
Download the entire VB6 Project here. This includes a test userform frmTest and the two module files. The test form allows you to select either ShellAndWaitSimple or ShellAndWaitEnhanced, and in the case of ShellAndWaitEnhanced, whether the message control is passed as a refrence to a form control or as an HWnd.

Download only the modShellAndWaitSimpleVB6.bas code module. This contains only the ShellAndWaitSimple function and  supporting functions and declarations.   

Download only the modShellAndWaitEnhancedVB6.bas code module. This contains only the ShellAndWaitEnhanced function and supporting functions and declaratiolns.

Full documentation is provided in all the download files.

 

 

 

 

     
     

 

Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New   
Links   Legalese And Disclaimers
chip@cpearson.com

Copyright 1997-2007  Charles H. Pearson