Immediate window

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Immediate window

Post by dasadler »

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

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Immediate window

Post by 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.
StuartR


User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Immediate window

Post by 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
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Immediate window

Post by 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...
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Immediate window

Post by 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
You do not have the required permissions to view the files attached to this post.
Don

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Immediate window

Post by StuartR »

dasadler wrote:...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.
StuartR


User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Immediate window

Post by 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.
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Immediate window

Post by HansV »

dasadler wrote: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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Immediate window

Post by 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.
Don

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Immediate window

Post by HansV »

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

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Immediate window

Post by dasadler »

same result - nothing
Don

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Immediate window

Post by 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.
Don

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Immediate window

Post by HansV »

You must have done something wrong then. Try this sample workbook. (Make sure macros are enabled)
MacroDemo.xls
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Immediate window

Post by HansV »

dasadler wrote: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.
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Immediate window

Post by 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?
Don

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Immediate window

Post by 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.
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Immediate window

Post by 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
Don

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Immediate window

Post by 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.
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Immediate window

Post by dasadler »

Got it. Thanks you.
Don