In this tutorial, we’re going to teach you how to get the most out of the VBA Immediate Window. The Immediate Window is located in the VBE (VB editor), and it allows the user to immediately evaluate expressions, execute blocks of code, change variables during execution, and look at the information output from Debug.Print statements found throughout the code.

Most people only use the VBA Immediate Window to view the output of the Debug.Print statement, but this tutorial is going to prove that it’s far more powerful than that. Mastering the Immediate Window really will make your next VBA project a lot easier.


Opening the Immediate Window

The first step to successfully using the VBA Immediate Window is actually locating it. If you cannot see it in your default VBE view, you can add it by navigating to View > Immediate Window. The commonly assigned keyboard shortcut is Ctrl+G. Note the shortcut only works when the VBE is in focus (otherwise you will get the Go To dialog box in Excel).

View Menu Dropdown with Immediate Window Highlighted
The View Menu Dropdown with the VBA Immediate Window option highlighted

Once you open it, you will get a blank box that says “Immediate” in the upper left corner. It can be docked or undocked in the VBE. Using it undocked makes it feel more independent, and you can use it while the rest of the VBE is hidden offscreen. With that said, it’s still in the same layer as the VBE, so if you put the VBE behind the Excel window, you won’t be able to see the Immediate Window.

Undocked View of Immediate Window over the VBE
The VBE with the Immediate Window undocked


Querying Values

One of the big benefits of the Immediate Window is the ability to check the value of a variable when execution is paused (in debug mode). You can do the same thing in the Locals Window and the Watch Window, but if you have several variables instantiated, it might be hard to find yours.

To find the value of a variable, say in the middle of this execution, simply type a question mark followed by the variable name:

Immediate Window and Locals Window with query over the paused code

In the image above, the VBA Immediate Window is used to query the values of variables x and agg while the macro is paused. The Locals Window is shown for comparison.

Finding information through the Locals Window becomes even more burdensome when you are working with large objects that have several properties or that may contain child objects. For example, in this code we’re looking for the font color of cell A1. To find the color in the Locals Window, we’d have to navigate through a huge list of Range properties and child objects. Finding the color in the VBA Immediate Window, however, is much easier:

Immediate Window and Locals Window with query over the paused code

The Immediate Window and Local Window above both display the same cell property (a red font), but the Immediate Window is clearly much easier to read.

In fact, if you declare your variables, you will even get Intellisense inside the Immediate Window, so you don’t have to remember all the properties and methods when you use it. The important thing to remember is that you can only query variables when your macro execution is paused, or in debug mode.


Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

Changing Variables During Paused Execution

For anyone used to developing in an IDE (integrated development environment), you will know different kernels can be active at the same time. Unfortunately in VBA, there is only one kernel, so you cannot have different instances of the same VBA program running. However, the Immediate Window lets you directly modify the kernel while the code is paused.

If you don’t know what a software development kernel is, no worries. It’is basically just a name for the program as it lives in computer memory (RAM). The Locals Window gives you a glimpse into the kernel. When you use a VBA macro, the variables are added to a central program that stores everything. And this program can be changed directly with the VBA Immediate Window.

Code, Immediate, and Local Windows open with the variables changed

With a counter of three, we should have agg = 6 and x = 3 , but we’ve modified the kernel via the Immediate Window. This means we’ve actually changed how the rest of the program will run. When we run the next step, x will be 501 (after Next x) and agg will be one.

Note the original code is only adding positive numbers, so a negative agg value is impossible through the function itself. With the Immediate Window, we can make it possible, though.

This may seem like a pointless example, but it can actually be pretty helpful when debugging complex equations or functions.


Executing Standalone Code

If you know how to do something quickly in VBA but don’t want to open a module and create a sub name, you can run code directly in the Immediate Window. This includes things like for-loops and if-statements.

You can even instantiate variables directly in the Immediate Window without needing any prior modules running.

Immediate and Local with Direct Instantiation

We can see that no variables are instantiated within a program (otherwise they would appear in the Locals Window). In other words, our macro isn’t running. Even without a program running, we’re able to use the Immediate Window to instantly evaluate the expression x+y. We simply defined x and y directly in the Immediate Window’s kernel before summing them.

If you start running the program immediate_window_one(), x will be overwritten by the value in the program (first zero then incremented by one thereafter).

This kind of single-line code implementation is useful if you know a VBA command off-hand but implementing it in the GUI is tedious or you just don’t know how to do it. Case in point: Range("A23:D82").Value = 10. This operation hardly requires the need to go through the trouble of making a sub just to populate the range, but trying to do it manually would be tedious (even with Array input (Ctrl+Shift+Enter)).

Instead, all you have to do is type the command into your Immediate Window and press Enter. All the values in the range A23:D82 will be set to 10. See? The Immediate Window can do a lot more than just helping you debug your VBA code…

Executing More Than One Line

Pressing Enter executes the line of code where your cursor currently resides. What if you want to use a more complex structure, like a for-loop? Entering it like module code will result in errors.

For x = 0 To 500
    agg = x + agg
Next x

The Immediate Window views the first line as For x = 0 to 500 and tries to execute that. But this expression is only part of what you were really trying evaluate. The Immediate Window isn’t smart enough to read all the way to the Next x statement before evaluating, so the code doesn’t work. The Immediate Window is only seeing the first line. So how do you run multiple lines of code directly from the Immediate Window?

You use colons (:) to enter more than one line of code on the same line! Instead of writing each line on separate lines, you write them all on one line and separate them by colons. This makes the Immediate Window behave just like a regular macro would. Type these lines one at a time into your Immediate Window and you’ll see 125250 printed to the next line of your window.

for x = 0 to 500:agg = agg+x:next x
?agg
 125250

After running this loop, you can write something like Range("A1").Interior.Color = x and you will change the backgroud of Cell A1 to red. The variable x actually has a value of 501, which happens to make the cell red.


Clearing the Immediate Window and Kernel

To clear the Immediate Window of code and text, you can simply erase all the text as you would any other text. However, the variables directly instantiated will remain until they are cleared as well. To “reset the kernel”, press the Reset Button in the VBE. This will basically reset any variables you’ve used in modules or that you’ve instantiated directly in the Immediate Window. It stops code in debug mode (as you’re very likely aware), but it also clears out the kernel.

Highlight of Reset Button in VBE
The Reset (or Stop) Button in the VBE Toolbar

Warning: I don’t recommend storing variables and changes in the Immediate Window for very long. If you’re playing around with the kernel variables without recording their changes somewhere and you accidentally press the Stop Button, all of your work will be erased with absolutely no way to recover it .


Debug.Print Statements

If you’ve visited online forums looking for help, you’ve likely encountered some code like this:

Debug.Print fname
'lots and lots of code (Block 1)
Debug.Print fname
'some more code (Block 2)
Debug.Print fname

Usually fname is giving a wrong result, and the user is trying to figure out why. In this example, the code is spitting out the variable in three different places using the Debug.Print statement. The programmer can then pinpoint whether the error is occurring in Code Block 1 or Code Block 2. He or she would also be able to tell if fname is already wrong before even entering those blocks. But where do we find the output of the Debug.Print statement? The output is printed to the Immediate Window, of course!

To avoid confusing yourself, it is a good idea to clear the Immediate Window of text (using the backspace or delete keys) before running code with lots of Debug.Print statements. It is also advisable to write something like Debug.Print "Block 1: " & fname to help you keep track of where each Debug.Print statement is coming from.


Conclusion

The Immediate Window in VBA is extremely useful for debugging using the Debug.Print statement, but it can do so much more than that. In this tutorial, we taught you how to change variables mid-execution and how to run one-off VBA commands without ever making a module. You can change colors or populate cells in bulk all from the Immediate Window. It is so useful, in fact, you might want to show some of your colleagues how powerful it is and maybe you can convince them to get into programming. They will appreciate the practicality and ease of use.

Of course if they’re serious about learning VBA, send them our way for their first tutorials!

If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program and share this article on Google+, Twitter and Facebook, then leave a comment below to keep the discussion going.

This article was written by Cory Sarver on behalf of The VBA Tutorials Blog.

About The VBA Tutorials Blog

Ryan Wells

The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.