Using the Immediate Window

Before you start creating full-fledged VBA procedures (this awaits you in the next chapter!), begin with some warm-up exercises to build up your VBA vocabulary. How can you do this quickly and painlessly? How can you try out some of the newly learned VBA statements? Here are some short, interactive language exercises: enter a simple VBA instruction, and Excel will check it out and display the result in the next line. Let's begin with setting up your exercise screen.

1. In the Visual Basic Editor window, choose View | Immediate Window.

The Immediate window is used for trying out various instructions, functions, and operators present in the Visual Basic language before deciding to use them in your own VBA procedures. It is a great tool for experimenting with your new language. Instructions that you enter in this window immediately display results.

The Immediate window can be moved anywhere on the Visual Basic Editor screen, or it can be docked so that it always appears in the same area of the screen. The docking setting can be turned on and off on the Docking tab in the Options dialog box (Tools | Options).

To quickly access the Immediate window, simply press Ctrl+G while in the Visual Basic Editor screen. To close the Immediate window, click the Close button in the top right-hand corner of the window.

The Immediate window allows you to type VBA statements and test their results immediately without having to write a procedure. The Immediate window is like a scratch pad. Use it to try out your statements. If the statement produces the expected result, you can copy the statement from the Immediate pane into your procedure (or you can drag it right onto the Code window if it is visible).

Arrange the screen so that both the Microsoft Excel window and the Visual Basic window are placed side by side.

Figure 2-23: Positioning Microsoft Excel and Visual Basic windows side by side allows you to watch the execution of the instructions entered in the Immediate window.

3. In the Immediate window, type the instruction shown below, and press Enter:

Worksheets("Sheet2").Activate

When you press the Enter key, Visual Basic gets to work. If you entered the above VBA statement correctly, VBA activates a second sheet in the current workbook. The Sheet2 tab at the bottom of the workbook should now be highlighted.

4. In the Immediate window, type another VBA statement and make sure to press Enter when you're done:

Range("A1:A4").Select

As soon as you press Enter, Visual Basic highlights the cells A1, A2, A3, and A4 in the active worksheet.

5. Enter the following instruction in the Immediate window:

When you press Enter, Visual Basic places the number 55 in every cell of the specified range, A1:A4. Although the above statement is an abbreviated way of referring to the Range object, its full syntax is more readable: Range("A1:A4").Value = 55.

6. Enter the following instruction in the Immediate window: Selection.ClearContents

When you press Enter, VBA deletes the results of the previous statement from the selected cells. Cells A1:A4 are now empty.

7. Enter the following instruction in the Immediate window: ActiveCell.Select

When you press Enter, Visual Basic makes the cell A1 active.

Figure 2-24 shows all the instructions entered in the Immediate window in the above exercise. Every time you pressed the Enter key, Excel executed the statement on the line where the cursor was located. If you want to execute the same instruction again, click anywhere in the line containing the instruction and press Enter.

Immediate

m

Wo rks h eet s(" Sh e et2"). Ac t ¡vat e

-

Rsnge("A1 :A4"). Select

[A1:AA].Valus = 55

Selection ClearContents

ActiveCell.Select

mm_;__:

Instructions entered in the Immediate window are executed as soon as you press the Enter key.

Figure 2-24:

Instructions entered in the Immediate window are executed as soon as you press the Enter key.

For more practice, rerun the statements shown in Figure 2-24. Start from the instruction displayed in the second line of the Immediate window. Execute the instructions one by one by clicking in the appropriate line and pressing the Enter key.

Was this article helpful?

0 0

Post a comment