More ways to use the vba immediate window

The immediate window is commonly used for querying of values during debugging. It can however also be used for ad-hoc changes to your program and for learning purposes.

• Open the immediate window
• Get the value of variables or object properties
• Using Debug.Print
• Set a value for an object property
• Loop and print values
• Clear the immediate window

Open the immediate window

To open the Immediate window, from the Visual Basic menu select View » Immediate window or press Ctrl-G.

Get the value of variables or object properties

When you are in Debug mode you can get the current value of your variable by entering a question mark followed by the variable name and pressing enter. Note that in case of standard variables and properties of object variables you can also see its value by hovering the mouse pointer above it.

To get the value of a variable, enter ? followed by the variable and press enter:

?str

Using the Code VBA toolbar is an easy way to enter ?-expressions to get the values of properties during debugging, see animation...

Here we show how we can at runtime use the Code VBA Object menu to determine the Address of the UsedRange, apparently feeling insecure about it's definition.

1. Create and Set an object variable for the active worksheet: From the Code VBA toolbar, select Excel (Application) » Properties » ActiveSheet;
2. Set a debug point at the end of the procedure, start the procedure with F5;
3. Place the cursor in the Immediate window;
4. Now, under the Code VBA toolbar Object menu you will see the new variable wsActiveSheet;
5. From the Code VBA toolbar, select Object » UsedRange » Properties » Address...;
6. A Property Dialog pops up allowing you to specify details on the Address value you want. Press OK to use the defaults;
7. The query ?wsActiveSheet.UsedRange.Address is inserted in the Immediate window;
8. After pressing Enter, the actual address is returned. Apparently UsedRange starts from A1 and ends after the bottom-right value of the worksheet.

Using Debug.Print

By including Debug.Print lines you can produce a trace of the sequence of steps and values of variables...

Debug.Print "Start Proc1"
Debug.Print "str=" & str

etcetera, returning:

Start Proc1
str=hello
dat1=4jun22
End Proc1

The Immediate window will not be oped by default if values are written to it, you should open it manually before execution starts.

With Code VBA, a fast way to insert a Debug.Print statement including what you want to be output to the Immediate window: from the toolbar, select Code VBA » Error  » Debug.Print followed by selecting your required variables or properties of available objects.

Set a value for an object property

Using the Immediate window combined with the Code VBA toolbar you can set the properties of any available Object, more direct and possibly even simpler then by using the Excel ribbon. The menu was used to create the code below. The screencast below shows setting a border LineStyle. Note that the tooltips explain the purpose of the object or property and even give its current value.

ActiveCell.Borders.LineStyle = xlDashDotDot

Loop and print values

You can't run a block of code in the Immediate window since it only executes a line at a time after you press Enter. A work around is to put all lines of the block of code on a single line indicating line breaks using :

for each sht in Sheets : Debug.Print sht.Name : Next

Note that in the Immediate window you may not declare your variables, using the "dim" keyword will generate an error.

Clear the Immediate window

To clear the Immediate window, put the cursor in it and press Ctrl-A, Ctrl-X.