Exploring the VBA Editor’s Immediate Window

by Sola Bode
in Up and Running with Office VBA
May 11, 2020

The Excel VBA coding dojo, the VBA Editor, has a myriad of very useful windows. Here, you'll explore its Immediate Window which takes code snapshots.

10 minutes read

This article is part of the Visual Tour of Excel’s Coding Dojo series, exploring each of the Excel VBA Editor’s menus, toolbars, and windows. Here, we’ll take a close look at the Excel VBA editor’s Immediate window.

“Perfectionism is a disease. Procrastination is a disease. ACTION is the cure.”
Richie Norton

If you wish to skip to any of the other posts in the series, click on any of the topics listed below:

Meet your Mat! The VBA Editor

Most great martial artists first had to get intimate with their dojo. Likewise, to master Excel VBA, you’ll have to get comfy loitering in its coding dojo, the VBA Editor. Naturally, the first step is to open the VBA editor.

“The Dojo is the only place you can die and come back to life, so use it.”
kyoshi Larry Mabson (American karateka)

I, for one, know how daunting the Excel VBA editor can appear to beginners – just take a look at the overview below! However, looks can be deceiving; at least that’s true here. I say that because anyone familiar with programming IDEs will tell you that the VBA editor is one of the more user-friendly IDEs out there.

IDE stands for Integrated Development Environment. They are software that consolidates application development tools.

Laying out the different areas and windows of the Excel VBA Editor (VBE)

Most developers only use a few of the VBA Editor’s menus, toolbars, and windows. As a result, they miss out on several tools that would make their work less stressful and enhance their productivity.

But you are not most developers, are you? For one thing, you are here, so I take it you would like to max out your VBA programming potential. Brilliant! You are well on your way there.

Immediate Window – Overview

The Immediate window opens below the Code window, but you can drag and place it as required. Also, the window is not visible by default. To display the window, you select it from the View menu or click its icon on the Debug toolbar. Also, you can press the “Ctrl + G” or “Alt + V + I” keyboard shortcuts.

Displaying the Immediate Window from the View menu or Debug toolbar

The aptly named Immediate window instantly executes or evaluates a single line of code or expression. VBA newbies are often unaware of this window. However, old hands find it indispensable for code writing, debugging, and testing.

As shown in the image below, the Immediate window is akin to a digital whiteboard or scratchpad. Just type an expression or line of code, then hit Enter to run it, and the result promptly displays on the next line.

Immediate Window – Uses

Note that the Undo command does not affect changes made through the Immediate window. So, be very careful with this powerful tool. That said, some examples of the window’s uses include the following.

Get Information about the Active Workbook and its Objects

For example, you can find out the first worksheet’s name as shown in the image below. To do this, prefix the query by a question mark (‘?’), with no spaces between the mark and the query, and hit Enter.

Also, the Auto List Members feature helps complete such Immediate window queries. It does so by listing object members (i.e., properties and methods) while you type the code.

Getting active workbook information in the Immediate window with the help of the Auto List Members VBA Editor feature

Quickly Run and Test Lines of Code

You can run and test lines of code in the Immediate window, including those not found in any of your VBA project’s existing procedures. To do this, type the line of code into the window, do it as if you were in the Code window, and hit Enter.

To alter/set variable or property values in the Immediate window, use the assignment operator (‘=’). The image below illustrates how to change the name of the first worksheet and revert to the original name using the Immediate window.

Renaming the active workbook’s first worksheet from the Immediate window

Arguments are input data of specific value and type passed to procedures (i.e., functions or subroutines) upfront. A procedure won’t run until you assign values to all its required (not optional) arguments.

Run and Test Procedures in Object-Independent or Standalone Code Modules

To do this, enter the procedure’s name and hit Enter. Fill-in procedure arguments while typing the macro’s name in the Immediate window, before hitting Enter.

Running macros without arguments in the Immediate window

Running macros with arguments in the Immediate window

The VBA Editor works in any one of three modes – Design, Run (a.k.a. Runtime) and Break modes. These modes complement the primary development stages – coding and GUI (graphic user interface) design, running/testing, and debugging.

Enables the Instant Display of Variable and Property Values at Runtime

Placing Debug.Print statements at desired lines in a procedure in the Code window enable output viewing in the Immediate window at runtime. Also, the same effect occurs by entering Debug.Print statements directly in the window and pressing Enter.

The image below shows how Debug.Print statements and the Immediate window can help you validate code and find errors.

Debug.Print statements added to code in the Code window display variable/property values in the Immediate window, at Runtime

Enables the Querying/Altering of Variable and Property Values in Break Mode

In Break mode, the Immediate window enables querying or altering of variable or property values. To query/get variable or property values, prefix the variable/property with a question mark (‘?’). And, to alter/set variable or property values, use the assignment operator (‘=’). In either case, hit Enter after typing the variable/property or the value. The image below illustrates.

Querying and altering variable/property values in the Immediate window, in Break mode

The top half of the figure’s Immediate window gets the values of variables ‘x’ and ‘y’ (i.e., 5 and 10, respectively). Meanwhile, the bottom half sets variables ‘x’ and ‘y’ to new values (i.e., 2 and 3, respectively), resulting in a new sum outputted by the Debug.Print statement in the Code window.

Things to Note when using the Immediate Window

When running procedures in the Immediate window, you should note that:

The Auto Quick Info feature helps define procedures with arguments in the Immediate window. It does so by displaying the expected argument’s description (see image below).

The Auto Quick Info feature helps define procedures with arguments in the Immediate window

Procedures with arguments can’t be run directly from the Code window (i.e., with the F5 key or Run Sub/UserForm button on the Standard Toolbar). But, you can run such procedures in the Immediate window as you’ve seen earlier in this article. Naturally, this is quite useful in writing/debugging code. It lets you focus on a single procedure and avoid running all the procedures that are ahead of it in the Call Stack.

The Call Stack is a queue of all procedures scheduled for execution in the current scope. Often, you’ll link your procedures using Call statements. So, completing a task will require running a series of procedures (think of them as sub-tasks). The Call Stack lets you can see this queue of procedures.

Only procedures in object-independent modules can be run from the Immediate window. A compilation error, ‘Sub or Function not defined,’ occurs when procedures in the Microsoft Excel Objects folder of the Project Explorer are run from the window.

Module names (seen in the Properties window) must differ from the names of all the procedures in your VBA project. Otherwise, a compilation error, ‘Expected variable or procedure, not module,’ occurs. In such situations, just rename either the module or the erring procedure and you’ll be good to go.

The Long and Short of It

So, there you have it! Now, you know all you need to incorporate the Immediate window into your development workflow. The Immediate window gives you a snapshot of the situation behind the scenes in your code.

The window isn’t displayed by default in your VBA editor, so you’ll need to select it from the View menu or click its icon on the Debug toolbar, or press “Ctrl + G” or “Alt + V + I” keyboard shortcuts.

You can also check out the Office Dev Center page for more info on the VBA editor’s Immediate window.

As I mentioned earlier, this article is part of the Visual Tour of Excel’s Coding Dojo series exploring each of the Excel VBA Editor’s menus, toolbars, and windows. To check out any of the other posts in the series, click on topic links below: