Immediate window
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Immediate window
How is the Immediate Window used in the VBA Editor? Maybe I mean.. what is the purpose for it?
Don
-
- Administrator
- Posts: 12856
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Immediate window
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
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
StuartR
-
- Administrator
- Posts: 79669
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Immediate window
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:
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
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Immediate window
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...
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Immediate window
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
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
-
- Administrator
- Posts: 12856
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Immediate window
That Macro cannot run in the immediate window. You run it in an ordinary module but it sends its output to the immediate window.dasadler wrote:...To Hans: I tried that sample macro and got only a compile error - see screenshot
StuartR
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Immediate window
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.
Thinks like:
? strPath & strFile
to ascertain the value of my variables, I find very handy.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 79669
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Immediate window
Here is a screenshot illustrating Stuart's reply:dasadler wrote:To Hans: I tried that sample macro and got only a compile error - see screenshot
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
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Immediate window
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
-
- Administrator
- Posts: 79669
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Immediate window
You have to click inside the macro in the module window, instead of clicking on the module name. Then press F5.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Immediate window
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
-
- Administrator
- Posts: 79669
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Immediate window
You must have done something wrong then. Try this sample workbook. (Make sure macros are enabled)
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 79669
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Immediate window
There is probably a lot of text in the Immediate window.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.
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
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Immediate window
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?
where in the macro does it reference A1 - A4?
Don
-
- Administrator
- Posts: 79669
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Immediate window
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.
For i = 1 To 4
...
Next i
causes i to assume the value 1, then 2, 3, and finally 4.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Immediate window
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
-
- Administrator
- Posts: 79669
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Immediate window
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.
You can use whichever is most convenient.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA