From [https://www.excelcampus.com/vba/vba-immediate-window-excel/]

5 Ways to Use the VBA Immediate Window in Excel

• Jon Acampora
•  September 23, 2014

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.

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.

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.

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.

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.

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.

 VBA Immediate Window Examples.xlsm (109.9 KB)

#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.

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

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.

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.

 VBA Immediate Window Examples.xlsm (109.9 KB)

#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.

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.

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.