VBA – Debug.Print and the Immediate Window

Associated Files Download Links

The VBA Immediate Window is used to quickly run lines of individual Excel code, as well as get information about  Excel files. This tool allows you to test individual lines of code, which is useful when you are writing and debugging code. The output is displayed in the Immediate Window.

Displaying the Immediate Window

You need to display the Immediate Window first. In order to do this, you would press Alt-F11 to enter the Visual Basic Editor. Once you have inserted a module. Press Ctrl-G to view the Window.

You should see the following:

Viewing the Immediate Window in VBA

Executing Lines of Code

One of the things that the Immediate Window allows you to do is test a line of code. The following code will show you how to use the Immediate Window to fill Cell A1 of the Active Sheet with a light orange fill color. Type in the following line and then press Enter on your keyboard:

Testing Lines of Code Using the Immediate Window in VBA

The result is:

Using the Immediate Window in VBA to Fill a Cell

Questions and the Immediate Window

You can use the Immediate Window to get information about your workbook. You can do this by using the question mark. If you enter a statement preceded by a question mark then the Immediate Window will deliver the appropriate answer. Let’s say you have the number 5, in cell A1 which is selected. The following code will allow you to use the Immediate Window to get this value:

The result is:

Asking Questions and Using the Immediate Window

Run a Macro from the Immediate Window

You can run a macro from the Immediate Window by typing in the name of the macro and pressing Enter. If your macro contains arguments then you can use the Immediate Window and pass the arguments to the macro through the Immediate Window. The following code shows you how to call a macro named CountWorksheets from the Immediate Window:

Using the Immediate Window to Call Macros

After pressing Enter, the result is shown on the worksheet in Cell A1.

Using Debug.Print

You can use Debug.Print as part of your sub procedures and this tells the Immediate Window to display certain information. This is used when you don’t want to display values or results in a cell in the workbook itself or in a message box. The following code uses the Debug.Print statement as part of a sub procedure and displays the results in the Immediate Window.

The result when you press F5 to run the macro is shown in the Immediate Window:
Using Debug.Print to Display Results in VBA

Using the Immediate Window When Running Code

You can also use the Immediate Window to set or get a variable’s value while you are stepping through your code:

The following code has a  breakpoint that is inserted as shown below:

Getting Variables Using the Immediate Window

If you enter ?SName in the Immediate Window while stepping into your code using F8, at the break point you will get the following result:

Stepping into your code and using VBA

You may also like some of this related content...

Ads