Launching Applications

There's more than one way to launch an application. In fact, there are at least five ways you can manually start a program: via the Start | Programs menu, a shortcut menu, the Run command, the MS-DOS window, or by double-clicking an executable file in Windows Explorer.

This section assumes that you are familiar with the manual techniques of launching applications and that you are anxious to experiment with additional techniques to start applications from inside the Microsoft Excel Visual Basic Editor window.

Let's begin with the simplest of all—the Shell function. This function allows you to start any program directly from a VBA procedure. Suppose that your procedure must open Windows Notepad. To launch Notepad, all you need is one statement between the keywords Sub and End Sub. Or better yet, you can type the following statement in the Immediate window and press Enter to see the result immediately:

Shell "notepad.exe", vbMaximizedFocus

In the above statement, "notepad.exe" is the name of the program that you want to start. This name should include the complete path (the drive and folder name) if you have any concerns that the program may not be found. Notice that the program name is in double quotes. The second argument of the Shell function can be omitted. This argument specifies the window style (that is, how the program will appear once it is launched). In the above example, Notepad will appear in a maximized window. If the window style is not specified, the program will be minimized with focus (see Table 9-1 below).

Table 9-1: Window styles used in the Shell function

Window Style Constant

Value Window Appearance vbHide 0

vbNormalFocus 1

vbMinimizedFocus 2 (default setting)

vbMaximizedFocus 3

vbNormalNoFocus 4

vbMinimizedNoFocus 6

Window is hidden

Normal size with focus

Minimized with focus (this is the default setting)

Maximized with focus Normal without focus Minimized without focus

If the Shell function is capable of launching the specified executable file, it returns a number called the Task ID. This number uniquely identifies the application that has been launched. If the Shell function is unsuccessful (that is, it cannot start the specified program), Visual Basic generates an error.

If you want to work with the program launched by the Shell function, do not enter any other statements in the procedure after the Shell function. The Shell function starts the program asynchronously. That means that Visual Basic starts the program specified by the Shell function, and immediately after launching, it returns to the procedure to continue with the execution of the remaining instructions (therefore not giving you a chance of working with the application).

How would you use the Shell function to launch the Control Panel?

1. Open a new workbook and save it as Chap09.xls.

2. In the Visual Basic Editor window, insert a new module in the Chap09.xls VBA project.

3. Rename the project WorkWApplets, and change the module name to ShellFunction.

4. Enter the StartPanel procedure shown below:

Sub StartPanel()

Shell "Control.exe", vbNormalFocus End Sub

The Control Panel contains several icons. Each of these icons performs one or more tasks. As you know, behind every icon, there is a program that is activated when the user double-clicks the icon or selects the icon with the arrow keys and then presses Enter. As a rule, you can check what filename drives a particular icon by looking at the icon's properties. Unfortunately, the icons in the Control Panel have the Properties option disabled. You can, however, find out the name of the control panel icon file by creating a shortcut to the icon. For example, before you create a procedure that changes the regional settings in your computer, let's find out the name of the file that activates this icon.

1. From the Start menu, choose Settings, and then Control Panel.

2. In the Control Panel window, right-click the Regional Options icon and choose Create Shortcut from the shortcut menu.

3. Click Yes to place the shortcut on the desktop.

4. Close the Control Panel window.

5. Back on the desktop, click the Shortcut to Regional Options icon with the right mouse button and choose Properties.

6. In the Properties window, click the Shortcut tab and then click the Change Icon button.

Figure 9-1:

Each Control Panel icon has a file with the .cpl extension.

Figure 9-1:

Each Control Panel icon has a file with the .cpl extension.

7. Write down the name of the .cpl file (Control Panel Library) or a Dynamic Link Library file (.dll) and close all the windows that were opened in this exercise. Table 9-2: Some of the files that activate Control Panel icons

Icon in the Control Panel

.cpl or .ddl File

Phone and Modem Options

Add/Remove Programs

Network and Dial-up Connections

32-Bit ODBC



Users and Passwords Date/Time Regional Options Internet Options










Icon in the Control Panel

.cpl or .ddl File

Sounds and Multimedia Properties






The ChangeSettings procedure shown below demonstrates how to launch the Control Panel's Regional Settings icon using the Shell function. Notice that the arguments of the Shell function must appear in parentheses if you want to use the returned value later in your procedure.

1. Enter the current module of the ChangeSettings procedure, as shown below:

Sub ChangeSettings() Dim nrTask nrTask = Shell("Control.exe intl.cpl", vbMinimizedFocus) Debug.Print nrTask End Sub

2. Run the ChangeSettings procedure several times, each time supplying a different .CPL file according to the listing presented in Table 9-2. You may want to modify the above procedure as follows:

Sub ChangeSettings2() Dim nrTask

Dim iconFile As String iconFile = InputBox("Enter the name of the CPL or DLL file:") nrTask = Shell("Control.exe " & iconFile, vbMinimizedFocus) Debug.Print nrTask End Sub

If a program you want to launch is a Microsoft application, instead of the Shell function, it's more convenient to use the Visual Basic ActivateMicro-softApp method. This method is available from the Microsoft Excel Application object. For example, to launch PowerPoint from the Immediate window, all you need to do is type the following instruction and press Enter:

Application.ActivateMicrosoftApp xlMicrosoftPowerPoint

Notice that the ActivateMicrosoftApp method requires a constant to indicate which program to start. The above statement starts Microsoft PowerPoint if it is not already running. If the program is already open, this instruction does not open a new occurrence of the program; it simply activates the already running application. You can use the following constants with the ActivateMicrosoftApp method. The name of the constant indicates the application name.

Application Name

Access FoxPro Mail

PowerPoint Project Schedule Word

Constant xlMicrosoftAccess xlMicrosoftFoxPro xlMicrosoftMail xlMicrosoftPowerPoint xlMicrosoftProject xlMicrosoftSchedulePlus xlMicrosoftWord

0 -1

Post a comment