5 Ways to Use the VBA Immediate Window in Excel

The VBA Immediate Window is a great tool that can help any Excel user, even if you are not writing macros. Learn how to get answers about your Excel file, quickly run macros, debug your code, and more. Free file to download contains VBA code samples.

The VBA Immediate Window is an awesome tool that allows you to get immediate answers about your Excel files, and quickly execute code.  It is built into the Visual Basic Editor, and has many different uses that can be very helpful when writing macros, debugging code, and displaying the results of your code.

VBA Immediate Window Excel Worksheets Count

Every Excel user can benefit from the Immediate Window, even if you're not writing macros.  This post will explain 5 different uses for the Immediate Window.  Once you understand the capabilities of this tool, you will find yourself using it all the time.

Where is the Immediate Window?

The Immediate window is located in the Visual Basic Editor window.

Open Visual Basic Editor and Immediate Window Excel Mac

The fastest way to get to the Immediate Window is to:

  1. Press Alt+F11 (hold Alt key, then press F11 key) from anywhere in Excel.  The Visual Basic Editor window will open.  (Mac version is Fn+Alt+F11)
  2. Pressing Ctrl+G opens the Immediate Window and places the text cursor in it.  Begin typing your code.  (Mac version is Ctrl+Cmd+G)

When you open the VB Editor (Alt+F11) you might see the Immediate Window automatically appear in the bottom right corner.  This is its default location.  If it's not there you can press Ctrl+G or View menu > Immediate Window.

This Blank Box is Magical!

When you click inside the Immediate Window you will just see a blank box with the text cursor flashing.  At first glance this doesn't look too exciting, but the Immediate window can be a very powerful and useful tool.

What Does the Immediate Window Do

Think of it like a blank cell in a worksheet.  It's pretty boring until you add a formula to it, right?  Well the Immediate Window is very similar, so let's look at 5 examples that will help you get the most out of this magical box.

#1 – Get Info About The Active Workbook

The simplest use for the Immediate window is to quickly get information about the workbook that you currently have open and active in the background.  You can evaluate any line of VBA code in the Immediate Window, and it will immediately give you the result.

For example, to find out how many sheets are in the active workbook, type the following line of code in the Immediate window and then press the Enter key.

?Activeworkbook.Worksheets.Count

The answer will be displayed on the next line of the Immediate window, directly under the code.

VBA Immediate Window Excel Worksheets Count

Ask a question, any question…

Putting the question mark (?) at the beginning of the statement tells the Immediate window that we are asking it a question, and expecting a result.

The following screencast shows a few examples of how we can use the Immediate window to get the value, number format, and fill color of the active cell.

Excel VBA Immediate Window Question Mark Examples

Notice that the Immediate Window displays the intellisense as I type.  Intellisense is the drop-down menu that displays the properties, methods, and members of the object I'm referencing.  This makes it very fast and easy to type code in the Immediate Window.

You can download the free sample workbook that contains a few more useful examples.

#2 – Execute a Line of VBA Code

You don't have to write a whole macro if you just need to perform one line of code to your workbook.

Remove the question mark at the front of the statement and the Immediate Window will execute or perform that line of code.

VBA Immediate Window Execute Line of Code in Excel
Selection.HorizontalAlignment = xlCenterAcrossSelection
The image above shows how to format the selected cells with the Center Across Selection alignment.

You can also use the following line of code to make a worksheet “very hidden”.

Worksheets(“Sheet1”).Visible = xlVeryHidden

Another example is to hide the contents of a cell by making its font color the same as its fill (background) color.

Range(“A1”).Font.Color = Range(“A1”).Interior.Color
Hide Cell Contents with Font and Interior Color

I use this line of code in Tab Hound's Table of Contents tool to hide some settings stored in cell A1.  Even if the user changes the fill color of the sheet, the contents in cell A1 will still be hidden after the code is run.

#3 – Run a Macro

You can run a macro from the Immediate Window by typing the name of the macro (procedure), and pressing Enter.

Of course you can also do this by pressing the F5 key or the Run Macro button in the VB Editor, but what if your macro contains arguments?

A macro cannot be run from within the procedure if it contains arguments.  However, you can call the macro from the Immediate Window.

The example below is a simple macro that enters the current date (Now) in the cell , and changes the font color to blue (Color = vbBlue).  This macro requires two arguments to be passed to it to run, the worksheet name and cell address where the date stamp will be entered.

Run VBA Macro from Immediate Window with Arguments Excel

For a macro like this you will typically be calling it from another macro and specifying the arguments in the macro that is calling it.  But if you just want to test the macro that contains arguments, you can use the Immediate Window to call it.

This is great for writing and debugging code.  You might not want to run the entire stack of procedures (macros) in the code, but you can use the Immediate Window to only call that specific macro you're working on.

The example above shows how you can specify the arguments after the macro name.  For arguments that are string variables (text), you will need to wrap the variable in quotation marks.

As you can see in the image, the intellisense is available in the Immediate Window, which makes it easy to specify the arguments for the macro.

The code in the image above is included in the free sample file you can download below.

#4 – View Debug.Print Info

Have you ever seen VBA code on the internet that contains a line similar to the following?

Debug.Print xxxxx

With that “xxxxx” being some variable that the code is calculating or producing.

Debug.Print is telling VBA to print that information in the Immediate Window.  This can be useful when you want to see the value of a variable in a certain line of your code, without having to store the variable somewhere in the workbook or show it in a message box.  It is especially useful when you are writing or debugging code.

The example below is a macro that loops through all the sheets in the workbook and checks to see if each sheet is empty (not used).  If the sheet is empty then it is listed in the Immediate Window using Debug.Print.

Excel VBA Immediate Window Debug Print List All Empty Sheets Example

The ultimate goal of this macro may be to delete all empty (blank) sheets in the workbook, but we might want to test the code first before actually deleting any sheets.  The Debug.Print line creates a list of empty sheets in the Immediate Window, and we can then manually check each of those sheets to make sure they are really blank.

#5 – Get or Set a Variable's Value

The Immediate Window can also be used to get answers about the procedure (macro) that is currently running.  If you are stepping through your code (F8) or add a break point (F9) or add a STOP line in your code, then the code will pause.  When the code is paused you can use the Immediate Window to get information about any variables or objects that are referenced in the code.

The following example is a macro that creates a list of all the sheets in the active workbook.  There are plenty of different ways to write this code, but in this example I use the “lSht” variable in a For Next loop to loop through the worksheets and then add the sheet name to the active sheet.

Excel VBA Macro List All Sheets - Evaluate Variable Immediate Window

I added a break point (F9) in the code to pause the code when that line is executed.  With the code paused, the Immediate Window can be used to evaluate or change variables.  In the image above I used the question mark to check the value of the lSht variable.  Then I used the variable to get the sheet name of the sheet that is currently being processed in the loop.

Finally I changed the lSht variable to 5 by using the equals sign (lSht = 5).  This will effectively skip some of the sheets in the loop because I changed the variable from 2 to 5.

This is a simple example, but the Immediate Window can come in handy when stepping through code.

Undock the Immediate Window

Pete asked a great question in the comments below.  He wanted to know how I made the Immediate Window float on top of the Excel application in some of the screenshots.

This screencast shows you how you can undock the Immediate Window.

Docking the VBA Immediate Window

Here are the step-by-step instructions:

  1. Left-click and hold on the top bar of the immediate window.
  2. Drag it out of the VB Editor window. The immediate window becomes a free floating window that you can put on top of Excel.
  3. To re-dock it, double-click on the top bar of the immediate window.

You can do this with any of the windows in the VB Editor including the Project, Properties, Locals, and Watch windows.  Many developers will setup their VB Editor differently than the default layout.  Some prefer to have the project and properties windows on the right side instead of the left.  The VB Editor window gives you a lot of flexibility to customize your view.

Download

You can download the workbook that contains the code for all these examples in the link below.

Conclusion

The Immediate Window is a very versatile tool that can really help when writing and debugging macros.  It's a great way to get some quick answers about your file or application.  If you are new to VBA, the Immediate Window will be very useful as you start learning and writing code.

What do you use the Immediate Window for?  Please leave a comment below with your suggestions, or any questions.

114 comments

Your email address will not be published. Required fields are marked *

  • Great tips! I didn’t realize how powerful the Immediate Window could be for quickly testing code snippets. I’m definitely going to start using it more in my VBA projects. Thanks for sharing these insights!

  • Great tips! I love how the VBA Immediate Window can simplify debugging and testing snippets. Using it for quick calculations is a game changer—I hadn’t thought of that before! Thanks for sharing these insights!

  • Great tips! I never realized how powerful the Immediate Window could be for debugging. The examples you provided are really helpful for beginners like me. Thanks for sharing!

  • This post was so helpful! I never realized how much I could accomplish using the VBA Immediate Window. The examples you provided made it clear and easy to follow. I can’t wait to implement these tips in my projects. Thank you for sharing!

  • Great tips on utilizing the VBA Immediate Window! It’s such a powerful tool for debugging and testing code snippets. I especially appreciate how you explained the use of variable inspection—definitely a game changer for faster troubleshooting. Looking forward to more insights like this!

  • Great tips! I’ve always overlooked the Immediate Window in VBA, but these suggestions will definitely enhance my debugging process. Thanks for sharing such practical information!

  • Great tips! I especially found the debugging techniques using the Immediate Window really helpful. It’s amazing how much time it can save when testing code on-the-fly. Thanks for sharing these insights!

  • Great insights on utilizing the VBA Immediate Window! I especially found the debugging tips helpful. It’s amazing how a simple tool like this can streamline the coding process in Excel. Thanks for sharing!

  • Great tips! I particularly found the example of using the Immediate Window for debugging really helpful. It’s a game-changer for troubleshooting code snippets. Looking forward to implementing these techniques in my projects!

  • Great insights on using the VBA Immediate Window! I especially loved the tips on debugging and evaluating expressions. It’s amazing how much efficiency it adds to my workflow in Excel. Thanks for sharing these practical uses!

  • Great insights on using the VBA Immediate Window! I never realized how powerful it could be for debugging and testing code snippets. The tip about printing variable values in real-time was especially helpful. Looking forward to applying these techniques in my next project! Thanks for sharing!

  • Great tips on using the VBA Immediate Window! I especially found the debugging techniques really helpful. It’s amazing how much time we can save by utilizing this tool effectively. Thanks for sharing!

  • Great tips! I often overlook the Immediate Window, but your examples really highlight its potential. I especially liked the debugging suggestions – I’ve already tried them and they made a noticeable difference. Thanks for sharing!

  • Great insights on the Immediate Window! I’ve been using it for basic debugging, but you’ve opened my eyes to its potential for real-time testing and variable viewing. Can’t wait to try out the tips you’ve shared! Thanks for the informative post!

  • Great tips on utilizing the VBA Immediate Window! I never realized how much it could streamline my debugging process. Can’t wait to implement these strategies in my projects. Thanks for the insights!

  • Great tips! I never realized how powerful the Immediate Window could be for debugging and testing my code. The examples you provided make it so much easier to understand. Thanks for sharing!

  • Great post! The tips on using the VBA Immediate Window have really helped me streamline my debugging process. I especially found the method for quickly checking variable values super useful. Keep up the fantastic work!

  • This post was super helpful! I never realized how powerful the VBA Immediate Window could be for debugging and quick testing. The tips you shared will definitely improve my workflow in Excel. Thanks for breaking it down so clearly!

  • Great tips on utilizing the VBA Immediate Window! I especially found the debugging techniques helpful. It’s amazing how much time it can save when troubleshooting code. Looking forward to trying these out in my next project!

Generic filters

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter