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
- Querying Values
- Changing Variables
- Executing Standalone Code
- Clearing the Window and the Kernel
- Debug.Print Statements
- Conclusion
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).
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.
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:
In the image above, the VBA Immediate Window is used to query the values of variables
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
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.
Make powerful macros with our free VBA Developer Kit Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.
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.
With a counter of three, we should have Next x) and
Note the original code is only adding positive numbers, so a negative
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.
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
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
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 xThe 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
for x = 0 to 500:agg = agg+x:next x
?agg
125250After 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
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.
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 fnameUsually 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! Subscribe using the form below to keep one step ahead of your peers.
