5 Ways to Use the Immediate Window in Excel VBA

The Immediate Window in Excel VBA is a very useful tool to help us step through, check and debug our code. In this blog post we explore 5 ways you could use the Immediate Window.

It is called the Immediate Window because you can ask questions about your code, run statements and get immediate results. This can help us to understand what our macro is doing, and why and where it may be going wrong.

Watch the video tutorial below, or continue reading the full tutorial.

View the Immediate Window in Excel VBA

First of all, we need to view the Immediate Window. From the Visual Basic Editor window, click the View menu and then Immediate Window, or press Ctrl + G on the keyboard.

The Immediate Window appears at the bottom of the screen, below the Code Window by default. This can however, be moved and resized as you wish.

1. Ask your Macro Questions

Probably the most common way people use the Immediate Window, is to ask your macro questions.

To do this, you start the Immediate Window statement with a question mark (?).

For example, if you want to know how many worksheets in the active workbook you could enter.

?Activeworkbook.Worksheets.Count

The ability to question your macro is very useful in large macros and when stepping through the code.

At any point, asking a question to get feedback on the current status of an object such as how many sheets the currently active workbook has, or the name of the current sheet can help to diagnose issues and understand complicated sections of code.

To find the name of the current sheet you would enter.

?Activesheet.Name

The image below shows these statements being used in the Immediate Window.

2. Print Information to the Immediate Window in Excel VBA

Another reason to use the Immediate Window is to print information to it. So when the macro is running it writes into the Immediate Window.

This can help us see what is happening at specific points in the code.

In the example below, we have a basic macro that is looping through the sheets of a workbook. And maybe we want to double check that the code works, and it is successfully activating each sheet of the workbook.

The following line of code is added into the appropriate point of the macro so that information is printed to the Immediate Window.

Debug.Print sht.Name

This information is printed when the macro is run, or when you step through the macro.

A lot of people use message boxes to report the current status of variables, and objects while the macro runs. But message boxes need to be closed each time and interfere with the running of the macro.

By printing into the Immediate Window you avoid that interference and can focus on debugging your code.

3. Use the Immediate Window in Excel VBA to Execute Code

You even execute code statements within the Immediate Window.

This could be a helpful way of checking your syntax, without embedding it within your macro and having to step through it.

It also enables you to perform an action while you step through a macro. You could use this to test your macros reaction to a statement you enter.

For example, if you simply enter the line below into the Immediate window and press Enter it would protect the active worksheet.

Activesheet.Protect

And this statement would change the name of the active worksheet to the value of cell A1.

Activesheet.Name = Range("A1").value

4. Check and Change the Value of your Variables

One of the key fundamental skills in Excel VBA is to be able to work with variables. The Immediate Window can be a huge help here as it enables us to test, and even change, the variables during the execution of the macro.

When debugging a macro it is extremely useful to be able to see what our variables are doing at different stages of the code.

There are a few ways we can do this in Excel VBA including the brilliant Locals Window, but the Immediate Window takes it a step further by allowing us to change the variables value.

The macro in the image below is a simply one to remove the blank rows from a list. A variable named r is used to store the current row in the loop.

In the Immediate Window while stepping through the code we tested the current value of the r variable by typing the line below.

?r

We then entered the following line to change the value of r to 13. This was done so that we could skip rows that we did not want to test.

r=13

5. Run another Macro from the Immediate Window

We saw that the Immediate Window can be used to execute code statements earlier. It can also be used to run other macros.

We can obviously run macros using buttons in the Excel environment, or by using the View Macros window to name just two ways.

However by running the macros using these methods you are not given the opportunity to provide the macro with the parameters it may need.

Take this basic macro example below. It changes the fill colour of a cell using the colour provided as a parameter (referred to as lColour here).

Sub ChangeColour(lColor As Long)
ActiveCell.Interior.Color = lColor
End Sub

Entering the line below in the Immediate Window will run the ChangeColour macro and use the index number for a shade of blue as the parameter.

ChangeColour(15123099)

This enables me to check the macro functionality independently before it is used within larger macro projects.

More Excel VBA Tutorials

• 6 Ways to find the last row of a list in Excel VBA
• Email an Excel workbook as an attachment – Excel macro
• Macro to export all sheets as PDF