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:
- How to Open the VBA Editor in Excel
- Exploring each VBA Editor Menu in Excel
- Exploring each VBA Editor Toolbar in Excel
- Getting Started with the Project Explorer
- Getting Started with the Properties Window
- Exploring the VBA Editor’s Code Window
- Getting Started with the Locals Window
- Getting Started with the Watch Window
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.
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.
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.
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.
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.
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.
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.
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).
- 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:
- How to Open the VBA Editor in Excel
- Exploring each VBA Editor Menu in Excel
- Exploring each VBA Editor Toolbar in Excel
- Getting Started with the Project Explorer
- Getting Started with the Properties Window
- Exploring the VBA Editor’s Code Window
- Getting Started with the Locals Window
- Getting Started with the Watch Window