Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

The Immediate Window (Ctrl+G)

The Immediate window is an interactive debugging tool that is always available for you to use. To display the Immediate window in the VBE, press the Ctrl+G shortcut key, or choose View > Immediate Window from the VBE menu. You can do almost anything in the Immediate window that you can do in your VBA project, either at design time or while in break mode, including the following:

Calling procedures

Checking or changing the value of variables

Instantiating and testing classes

Running single-line loops

The Immediate window is the more powerful cousin of the most basic debugging technique of all; message box debugging. Message box debugging is typically the first debugging method you learn as a VBA programmer. It involves placing message boxes at various locations within your code, each of which display the values of one or more variables and/or location information. The Immediate window enables you to do everything you can do with message box debugging and much more, without the intrusive message boxes.

Debug.Print

The Debug.Print statement is the Immediate window's direct equivalent of message box debugging. The Debug.Print statement prints the value of the expression that follows it to the Immediate window. Two sample Debug.Print statements are shown in Listing 16-4.

Listing 16-4. Sample Debug.Print Statements

Dim sSheetTab As String
Dim wkbBook As Workbook
Set wkbBook = Application.ActiveWorkbook
sSheetTab = sSheetTabName(wkbBook, gsSHEET_TIME_ENTRY)
Debug.Print wkbBook.Name
Debug.Print sSheetTab

The results of these Debug.Print statements are shown in Figure 16-4.

Figure 16-4. Output From the Debug.Print Statements

As you can see, the output from each Debug.Print statement begins on a new line in the Immediate window. If you are familiar with VBA text I/O functions, note that the Debug.Print statement supports most of the same formatting features supported by the text I/O Print# statement.

It is uncommon to use extensively formatted output from the Debug.Print statement in the Immediate window, so we do not cover these features in any detail other than to say that you can include multiple expressions in a Debug.Print statement separated by commas and they will all be printed on the same line in the Immediate window separated by tabs.

Making the Best Use of the Immediate Window

There are two primary ways in which the Immediate window is used. It can be used as a simple collection point for the output of Debug.Print statements that post results as your code is running normally, or it can be used as an interactive tool while you are stepping through your code. There are two ways to use the immediate window interactively:

To evaluate a variable or expression
This is accomplished by entering a question mark character (?) followed by the variable or expression that you want to evaluate. The Immediate window is typically used for one-time evaluations. If you want to evaluate the variable or expression multiple times you should add a watch instead. We'll explain how to do this in The Watch Window section later in the chapter. Figure 16-5 shows the Immediate window being used to evaluate the value in a worksheet cell that is used in the next line of code to be executed in the module below it.

Figure 16-5. Using the Immediate Window to Evaluate an Expression

[View full size image]

To execute code
This can include changing the value of variables that are currently being used in your application, modifying application settings, calling procedures in your code and almost anything else you could normally do in VBA. The only difference between evaluating expressions and executing code using the Immediate window is that you leave out the question mark when executing code. Placing your cursor anywhere within the line of code and pressing enter causes the line of code to be executed.

One common task involving code execution in the immediate window is modifying the value of the Application.Cursor property. During long-running VBA procedures, the Excel cursor will often flicker back and forth between an hourglass and the default pointer. This can be confusing to the user, so you force the cursor to display an hourglass at the beginning of the entry point procedure and reset it to its default at the end of the entry point procedure. The problem arises when you want to debug something within this procedure. Even in break mode the cursor setting you make is persistent, and it applies to the VBE as well as the Excel interface. The solution is to use the Immediate window to change the Application.Cursor property back to its default value, as shown in Figure 16-6.

Figure 16-6. Executing a Line of Code in the Immediate Window

[View full size image]

Another excellent example of the ability to execute code in the Immediate window is running loops that print out information. Anything you can fit on a single line can be run in the Immediate window and you can string multiple lines of VBA code together by separating them with the colon character (:). Figure 16-7 shows an example of running a loop that prints out the names of all open workbooks in the Immediate window.

Figure 16-7. Executing a Loop in the Immediate Window

Keep in mind that the Immediate window is fully functional even during design time. It's the perfect environment for testing specific lines of code you are unsure about. The Immediate window should become the most commonly used debugging tool in your arsenal.