5 Ways to Use the VBA Immediate Window - Excel Campus (2023)

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, andhas many different uses that can be very helpful when writing macros, debugging code, and displaying the results of your code.

EveryExcel 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 BasicEditor 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 isCtrl+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

(Video) Excel VBA: Immediate Window - 5 Ways to Use it

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 isthe drop-down menu that displays the properties, methods, and members of the object I'm referencing. This makes it very fast and easyto type code in the Immediate Window.

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

#2 – Executea 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

(Video) Excel VBA - Immediate Window

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
5 Ways to Use the VBA Immediate Window - Excel Campus (7)

I use this line of code in Tab Hound's Table of Contentstool 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 containsarguments?

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 forwriting 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'reworking on.

The example aboveshows 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 availablein the Immediate Window, which makes it easy to specify the arguments for the macro.

(Video) 8 Keyboard Shortcuts for the VBA Editor in Excel

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

VBA Immediate Window Examples.zipDownload

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

(Video) 5 Killer Excel VBA Tips Everyone Should Know

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.

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.

VBA Immediate Window Examples.zipDownload

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 startlearning and writing code.

(Video) How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar (Part 3 of 4)

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

FAQs

How do I use Immediate windows in Excel? ›

Excel VBA: Immediate Window - 5 Ways to Use it - YouTube

What is the immediate window used for? ›

Use the Immediate window to debug and evaluate expressions, execute statements, and print variable values. The Immediate window evaluates expressions by building and using the currently selected project.

How do you pass VBA code step by step? ›

By pressing F8, you can single step through your code.

What are the potential benefits of using VBA? ›

The following are benefits of learning VBA in Microsoft Excel Macro.
  • Automates repetitive and routine tasks. ...
  • Accessibility to other users. ...
  • Reduces the formulas burden used in Excel reports. ...
  • Reduces the turnaround time. ...
  • Protects workbooks and hides worksheets. ...
  • Conclusion.
Dec 4, 2018

How do I use Immediate window in Excel VBA? ›

Run a macro (or a function)

While there are various ways to run a macro in Excel, one of the quick ways is using the immediate window. To run a macro, all you need to do is enter the name of the macro in the immediate window and hit the enter key (the cursor should be at the end of the macro name for this to work).

How do I use the immediate window in Access VBA? ›

To execute code in the Immediate window

Type a line of code in the Immediate window. Press ENTER to execute the statement.

What is difference between immediate window and window? ›

Similar to the Command window, the Immediate window lets you test the code without having to run it. The Intermediate window is used to evaluate, execute statements, or print variable values. To open the Immediate window, navigate to Debug | Windows and select Immediate.

Where is immediate window in Visual Studio? ›

Press Ctrl + Q to focus on Quick Launch text box. Type Immediate then choose the item that appears: Debug -> Windows -> Immediate ( Ctrl+Alt+I )

How do I print Immediate windows? ›

Steps to Open Immediate Window and See the Output

Press Ctrl + G or click on the 'View' menu in the VBA Editor. Choose the option 'Immediate Window. ' Place the cursor in the Window and again run the code.

What is VBA Why is it necessary to learn? ›

Visual Basic for Applications is a computer programming language developed and owned by Microsoft. With VBA you can create macros to automate repetitive word- and data-processing functions, and generate custom forms, graphs, and reports. VBA functions within MS Office applications; it is not a stand-alone product.

Is VBA important to learn? ›

With the knowledge of VBA programming in excel, you can work on long Microsoft excel task in seconds. In a corporate, it is important for all to have VBA skills. In a corporate, it is the normal part of work to prepare excels reports. VBA is the programming language of Microsoft.

What is the main advantage of VBA compared to macros? ›

Thanks to its capability to record macros in excel, VBA can help emulate the repetitive tasks you perform on a daily basis and re-execute them, seamlessly. If, in addition you have the programming knowledge to tweak the macro to account for variability in tasks, the possibilities become almost endless.

How do I write VBA code in Excel? ›

Insert VBA code to Excel Workbook

Open your workbook in Excel. Press Alt + F11 to open Visual Basic Editor (VBE). Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu. Copy the VBA code (from a web-page etc.)

How do I find VBA code in Excel? ›

Here's how you can find macros and VBA modules in your document:
  1. In Word or Excel, click View > Macro > View Macros. In PowerPoint, click View > Macro. In the Macro box, pick the macro you want to remove and click Delete.
  2. Press Alt+F11 to find macros in the VBA Editor.

How does VBA work in Excel? ›

VBA is used to automate tasks and perform several other functions beyond creating and organizing spreadsheets. For example, users need to automate some aspects of Excel, such as repetitive tasks, frequent tasks, generating reports, etc.

How do you write Hello World in VBA? ›

Using Access VBA, here is an easy way to do this. Open Access from the Windows Start Menu. To open Visual Basic, press Ctrl+G. In the Immediate window pane, enter Print MsgBox("Hello World"), and then press Enter.

How do I display a variable value in immediate window? ›

Working With Variables

To view the variable's value, simply type its name into the immediate window and press Enter. The value, "Hello world" will be displayed beneath the typed line, as in the image above. To access properties of a variable or value, use the member access operator (.) as you would within source code.

What is local window in VBA? ›

The Locals window is a useful window where you can see the values of all declared variables. This window automatically updates as you debug line by line or from breakpoint to breakpoint. The window has three main columns. Under the Expression column, it shows the name of the variable.

How do I test VBA code in access? ›

Steps To Create A VBA To Run A Query In MS Access
  1. Step 1: Add an Access Form. First of all, open your MS access application, after then add an Access Form. ...
  2. Step 2: Place a Button. In next step, you need to put a button on the Form. ...
  3. Step 3: Open the VBA Screen. ...
  4. Step 4: Write the VBA to Run the Query. ...
  5. Step 5: View the Results.

Which of the following can be useful while debugging VBA code? ›

Some of the debugging tools available in VBA Editor include stepping over, stepping out, breakpoints, and debug print.

Which of the following is most useful for executing VBA statements directly and for debugging your code? ›

The Immediate window is extremely useful and allows you to test and execute small snippets of code. Lets you run any VBA statement, manually while a program is running.

What is Alt F11 in Excel? ›

Alt+F11 opens the Microsoft Visual Basic For Applications Editor, in which you can create a macro by using Visual Basic for Applications (VBA). F12 Displays the Save As dialog box.

How do I enable Alt F11 in Excel? ›

alt+F11 shortcut to open the Visual Basic Editor (VBA) - YouTube

How do I open Alt F11 in Excel? ›

Keyboard Shortcut to Open the Visual Basic Editor

The easiest way to open the Visual Basic editor is to use the keyboard shortcut – ALT + F11 (hold the ALT key and press the F11 key). As soon as you do this, it will open a separate window for the Visual Basic editor.

What is F1 f2 F3 F4 F5 F6 F7 f8 F9 F10 F11 F12? ›

The function keys or F-keys on a computer keyboard, labeled F1 through F12, are keys that have a special function defined by the operating system, or by a currently running program. They may be combined with the Alt or Ctrl keys.

What is F3 in Excel? ›

F3: Displays the Paste Name dialog box. Available only if names have been defined in the workbook. Shift+F3: Displays the Insert Function dialog box. F5.

What does Ctrl Shift F3 do? ›

The Microsoft docs tell us that once you get to the final dialog after the last boot during the Windows install process, one can enter Audit mode using Ctrl + Shift + F3 keys.

What is Alt F8? ›

Alt+F8 Opens the macro dialog box.

Does Alt F1 anything? ›

This makes you easily switch between logged in users on a multi-user system. Any TTY where no user is logged in will run a console (text) TTY. When you go to gdm ( F1 ) and log in, you will automatically be sent back to the TTY you are already logged in (in your case probably TTY-2).

What does Alt F1 do in Excel? ›

F1 Key
  • Alt + F1. If you use "Alt and F1" Keys together then it will insert a new chart in your excel and will open the chart options. ...
  • Ctrl + F1. You can minimize or maximize the ribbon of excel by pressing "Ctrl & F1" Keys together. ...
  • Shift + F1. Unfortunately, "Shift + F1" Keys alone does not provide any output. ...
  • Alt + F2.

What is VBA in Word? ›

The Visual Basic button opens the Visual Basic Editor, where you create and edit VBA code. Another button on the Developer tab in Word and Excel is the Record Macro button, which automatically generates VBA code that can reproduce the actions that you perform in the application.

Where do I find VBA code in Excel? ›

Hold the Alt key, and press the F11 key, to open the Visual Basic Editor. In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects. Right-click on the ThisWorkbook object, and choose View Code.

What does Alt and F10 do? ›

In many of these apps, shortcuts are also shown in a tooltip if you hold the mouse pointer over a button. In others, pressing the Alt or F10 keys shows available keyboard shortcuts. If a letter is underlined in a menu, press the Altkey and the underlined key together instead of choosing that menu item.

Videos

1. Excel VBA Basics #10 - Looping through a database and analyzing cells based on criteria
(ExcelVbaIsFun)
2. Excel Video 415 Debugging with the Immediate Window
(PivotTableGuy)
3. 3 Tips for Writing Formulas with VBA Macros in Excel
(Excel Campus - Jon)
4. How to Design and Code an Excel VBA Application Like a Pro
(Excel Macro Mastery)
5. Stop Wasting Time in Excel
(Excel University)
6. Get EXCEL data using SQL in VBA!
(SimpleExcelVBA)
Top Articles
Latest Posts
Article information

Author: Jeremiah Abshire

Last Updated: 02/06/2023

Views: 6223

Rating: 4.3 / 5 (54 voted)

Reviews: 93% of readers found this page helpful

Author information

Name: Jeremiah Abshire

Birthday: 1993-09-14

Address: Apt. 425 92748 Jannie Centers, Port Nikitaville, VT 82110

Phone: +8096210939894

Job: Lead Healthcare Manager

Hobby: Watching movies, Watching movies, Knapping, LARPing, Coffee roasting, Lacemaking, Gaming

Introduction: My name is Jeremiah Abshire, I am a outstanding, kind, clever, hilarious, curious, hilarious, outstanding person who loves writing and wants to share my knowledge and understanding with you.