Immediate window

[dasadler]

How is the Immediate Window used in the VBA Editor? Maybe I mean.. what is the purpose for it?

[StuartR]

I use it for all sorts of things that can't be done in the user interface, as well as for testing commands.

For example

• in Excel I might use it to set the visibility of a worksheet to be "Very Hidden"
• In Word I might use it to check the number of rows in a large table

It can also be used to change the value of a variable while single stepping VBA code, and probably for lots more things that I haven't thought of.

[HansV]

You can test single lines of code, and use the ? operator (the shortcut for Debug.Print). Here are a few examples. In each case, type the line in the Immediate window and press Enter.

Calculate the number of seconds in a day:

? 24 * 60 * 60

In Excel, get the address of the current selection:

? Selection.Address

In Word, make the currently selected text bold:

Selection.Font.Bold = True

If you're having problems with a macro you're writing, you can write intermediate results to the Immediate window. Here is a simple Excel example that outputs the values of A1:A4 to the Immediate window:

Code: Select all

Sub MyMacro()
  Dim i As Integer
  For i = 1 To 4
    Debug.Print Cells(i, 1).Value
  Next i
End Sub

[Rudi]

Hi dasalder,

I use the window when I am having to create range references that I plan to store in a Range Variable. I build the range up systematically to ensure that the correct reference will be assigned to the variable.

For instance:
Range("A1").CurrentRegion.Rows(1).Select
then I modify it further and press enter again to check the reference...

Range("A1").CurrentRegion.Rows(1).Offset(0,Range("A1").CurrentRegion.Columns.Count).Select
Then further...

Range("A1").CurrentRegion.Rows(1).Offset(0,Range("A1").CurrentRegion.Columns.Count).Resize(Range("A1").CurrentRegion.Rows.count+1).Select

Lastly, remove the .Select at the end and copy the "accurate" reference to the end of the Set Variable reference in the module.

This is where I use the Immediate Window the most...

[dasadler]

Thanks everyone - I need to study this a bit more to really understand how to use it effectively.

To Hans: I tried that sample macro and got only a compile error - see screenshot

[StuartR]

...To Hans: I tried that sample macro and got only a compile error - see screenshot

That Macro cannot run in the immediate window. You run it in an ordinary module but it sends its output to the immediate window.

[VegasNath]

I don't utilise the immediate window much, though I am using it more recently than ever before, and realising what a useful tool that it is. It's a subject I need to study in detail to realise the full benefits.

Thinks like:

? strPath & strFile

to ascertain the value of my variables, I find very handy.

[HansV]

To Hans: I tried that sample macro and got only a compile error - see screenshot

Here is a screenshot illustrating Stuart's reply:

x84.png

You see the macro in a code module named Module1.
I have run the macro by clicking in it and pressing F5.
The result is visible in the Immediate window: the values of cells A1:A4 in the active worksheet have been listed.

You can't put macros in the Immediate window, only single lines of code.

[dasadler]

Odd, doesn't work for me. I select the module on the left, press F5, receive the macro window then select mymacro and click run. As far as I can tell, there is no response.

[HansV]

You have to click inside the macro in the module window, instead of clicking on the module name. Then press F5.

[dasadler]

same result - nothing

[dasadler]

Something is happening as the immediate widow seems to have a lot of line feeds or carriage returns since the vertical scroll bar decreases in length but there is no display in the immediate window.

[HansV]

You must have done something wrong then. Try this sample workbook. (Make sure macros are enabled)

MacroDemo.xls

[HansV]

Something is happening as the immediate widow seems to have a lot of line feeds or carriage returns since the vertical scroll bar decreases in length but there is no display in the immediate window.

There is probably a lot of text in the Immediate window.
Click in the Immediate window.
Press Ctrl+A to select all text.
Press Delete to remove the text.
Click inside the macro in the module window.
Press F5 to run it.

[dasadler]

the sample you provided works fine. the only difference between your workbook and mine is that yours had text in A1 - A4. I added content to A1-A4 in mine and it worked. Guess that was the problem all along.

where in the macro does it reference A1 - A4?

[HansV]

Cells(i, 1) is the cell in row i and column 1, i.e. column A. The loop

For i = 1 To 4
...
Next i

causes i to assume the value 1, then 2, 3, and finally 4.

[dasadler]

Okay - I understood the loop, I just didn't realize it was (row, column)... Seems like I have seen other code where similar loops were used and it was like "A"&i to indicate A1 - A4

[HansV]

In VBA, there are basically two ways to refer to a cell. For example, you can refer to cell C5 as Range("C5") and also as Cells(5, 3). In the sample macro, I could have used Range("A" & i) instead of Cells(i, 1). The result would have been exactly the same.

You can use whichever is most convenient.

[dasadler]

Got it. Thanks you.