Get most from VBA Immediate Window

What is VBA Immediate Window ?

As name suggests, it is a window or pane where you can quickly or Immediately test a line of VBA code.

By learning how to use Immediate Window one can quickly test a code line before implementing it in a Sub or a Function or can output variable value of a sub or function.

It is one of the tool you can use to debug or find issues in your code.

How to make VBA Immediate Window visible ?

To make VBA Immediate Window visible first open VBA IDE pressing ALT+F11.

Then in active VBA IDE press CTRL+G or click on View tab then click Immediate window.

view immediate window in VBA IDE

In the below image blue border eclosed part is immediate window. White space indicated by red arrow is where you write code line for testing or where output will be printed.

What is VBA Immediate window

Various ways to use Immediate Window

To use Immediate window you first need to know two types of VBA statement or code line.

Method or Action code line / statement
Output or asking question code line /statement

For Method or Action type code line no need to prefix ? before code, just write code line place cursor at end of the code line then hit ENTER key to execute code line.

For output type code line prefix ? before code, place cursor at end of the code line then hit ENTER key to output a value below.

Quick immediate window use demo

Output type code line

Write ?ThisWorkbook.Worksheets.Count code in immediate window and place cursor at end of this line then hit ENTER key, you will get count of workhsheet in the below line.

Similarly ?Range("B3").Value will give cell B3 value

?Selection.ADDRESS (FALSE,FALSE) will give relative address of selected range

?ThisWorkbook.FullName will give full name of workbook along with path

?date will give today’s date , ?34*2 will give 68 , ?2<=1 will give False

VBA immediate window question code (1)

Method or Action type code line

Test below lines from your side in immediate window. Place cursor at end of the line and hit ENTER to run code.

Worksheets(2).select will select second sheet from left of a workbook.

Activecell.Value=3 write 3 in activecell.

Sheet1.Range("A1").Interior.Color=vbYellow will color cell A1 of Shee1 code name to yellow.

Execute Sub and Function

In the below image in Sheet1 logLine sub is written. To run this sub using immediate window do below things.

Debug.Print is used to print value or string to immediate window.

In immediate window write Sheet1.logLine , place cursor at the end of this code line and hit Enter key.
This will output “this is test”.

Here Sheet1 is code name of sheet in which is this logLine sub is present.

If you have more than one workbooks open then make sure first to double click on that workbook or VBAProject in VBA IDE before running any Sub of that workbook using immediate window.

immediate window to run subroutine in vba

In the below image IsOddNumber Sub is written in Sheet1. This function takes single number input and outputs bollean value (True or False).

Since it is a function you need to prefix ? beore code line in the immediate window.

Write below in the immediate window, place cursor at the end then hit ENTER

?Sheet1.IsOddNumber(2)

test function output in VBA immediate window

Run multiple lines of code in VBA Immediate Window

In the above example you have seen only single line got tested in Immediate windown. But you can run or test multiple code lines as a single Sub or Function.

In immediate window the below code is written

For Each sht In ThisWorkbook.Worksheets:Debug.Print sht.Name:Next sht
Here each code line or statement is separate by collon (:) . Place cursor at end then press ENTER key.

multiple code line run in VBA immediate window

Above thing can be alternatively performed like below image

Sub printSheetsName()
  For Each sht In ThisWorkbook.Worksheets
    Debug.Print sht.Name
  Next sht
End Sub

run sub in Immediate window

Set or change a variable value using Immediate window

If you run the below sub then you will get “apple” in the msgbox (message box).

Sub printFavFruit()
 favfruit = "apple"
 MsgBox favfruit
End Sub

But you can change favFruit variable value using Immediate window.

Press F8 to run Sub line by line. Just before running Msgbox line ,run favfruit=”grapes” line in immediate window.

This will change favfruit variable value from “apple” to “grapes”, and in msgbox “grapes will be displayed.

set variable value using vba immediate window

You may also like to read and learn the below topics

How to run macro in Excel|Create macro button

Create Excel VBA User Defined Function (UDF / custom function)

How to calculate percentage in Excel? ( Discount, GST, Margin, Change/growth, Markup)