Clear Immediate Window
Tod
What code will clear the contents of the Immediate Window?
Dave Peterson
I think someone wrote code for this a few years ago. You might want to search
Google.
I just did and found this:
Try this
Public Sub ClearImmediateWindow()
Dim MyApp As Application
Set MyApp = Application
MyApp.VBE.Windows.Item("Immediate").SetFocus
SendKeys "^a"
SendKeys "{del}"
End Sub
Yannick Grant
COGNICASE SERVICES-CONSEILS
gra...@cognicase.ca
But there was a warning from Tom Ogilvy that instead of clearing the immediate
window, it cleared his module (code) window. Tom's warning was to save first!
Tod wrote:
>
> Here's another question similar to my other easy one. <g>
>
> What code will clear the contents of the Immediate Window?
--
Dave Peterson
ec3...@msn.com
John
"Dave Peterson" <ec3...@msn.com> wrote in message
news:3C36447E...@msn.com...
Bill Manville
> What code will clear the contents of the Immediate Window?
>
This will, though the use of SendKeys is unpleasant
Sub ClearDebugWindow()
Dim i As Integer
' put in some test data
For i = 1 To 10
Debug.Print i
Next
' go to VB editor
Application.CommandBars.FindControl(Id:=1695).Execute
' ensure debug window is displayed and active
Application.VBE.CommandBars.FindControl(Id:=2554).Execute
SendKeys "^{HOME}+^{END}{DEL}"
End Sub
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
Dave Peterson
you can pop in a couple of:
Debug.print myvariablenamehere
Then when the line gets executed (usually by stepping (F8) through the code),
you can see what the variable contained.
You can also use it to just type in commands.
If you've done one of these:
application.screenupdating = false
at the top of the macro and then break out of it, you can go to the immediate
window and type in:
application.screenupdating = true
and you'll be back to normal.
====
You can also use it to quickly find the value of some xl constant:
From my immediate window:
?xltoright
-4161
or:
?application.International(xldecimalseparator)
.
(shows a period)
(? is short for print--hold over from BASIC a long time ago.)
--
Dave Peterson
ec3...@msn.com
Dana DeLouis
ideas?
I too am looking for a way to clear the immediate window, but I have not had
much luck.
Let me explane. The use of Send Keys is a popular way to do this from
Google searches that I have done also.
Bill M. also had a very interesting technique for clearing the Immediate
window.
The problem that I have is that both techniques work well, but only when the
actual "Deletion" of the immediate window is the very last command of a
macro. If the code is suppose to continue, then the window is usually not
cleared.
I have been trying to find a way for a long time with no luck.
For example, here is your code, but after the window is cleared, a simple
"Hello" is added to the immediate window.
This just simulates one's code continuing after the window is cleared.
However, on my system, the word "Hello" never shows up.
Public Sub ClearImmediateWindow()
Dim MyApp As Application
Set MyApp = Application
MyApp.VBE.Windows.Item("Immediate").SetFocus
SendKeys "^a"
SendKeys "{del}"
Debug.Print "Hello!"
End Sub
'= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Bill's code works well also, but not if the code continues....
Sub ClearDebugWindow()
Dim i As Integer
' put in some test data
For i = 1 To 10
Debug.Print i
Next
' go to VB editor
Application.CommandBars.FindControl(ID:=1695).Execute
' ensure debug window is displayed and active
Application.VBE.CommandBars.FindControl(ID:=2554).Execute
SendKeys "^{HOME}+^{END}{DEL}"
Debug.Print "This is the only line"
End Sub
'= = = = = = = = = = = = = = = = = = = = = = = = = = = =
According to documentation on SendKeys, one might want to add True to make
the process wait until the command is finished.
However, this has never worked for me with the Immediate window.
I believe it should be...
SendKeys "^(a)", True
SendKeys "{Del}", True
...but adding True never works...it makes it actually worse for some reason
when clearing the Window.
'= = = = = = = = = = = = = = = = = = = = = = = = = = = =
In my study, I have read that using the Caption property is not the best
thing to use for the Immediate window.
( i.e. ... Item("Immediate")... )
I don't remember why, or where this reference was. ( I usually try to
remember sources like this.)
The suggestion was to use the Type property.
This is what I use, which is very similar to your code...
Sub ClearImmediateWindow()
Dim objWindow As Object
Dim objImmediateWindow As Object
Dim j As Long
' Fill with some data
For j = 1 To 10
Debug.Print "Junk text"
Next j
For Each objWindow In ThisWorkbook.VBProject.VBE.Windows
If objWindow.Type = vbext_wt_Immediate Then
Set objImmediateWindow = objWindow
Exit For
End If
Next
objImmediateWindow.Visible = True
objImmediateWindow.SetFocus
SendKeys "^(a)"
SendKeys "{Del}"
End Sub
'= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Here is an interesting addition to your code that demonstrates some of the
problems with clearing this window.
You will notice that you can observer the numbers going to 6,000, then the
window is cleared.
The window should have been cleared before the numbers are placed in the
Immediate window.
However, the deletion occurs "After" the numbers are placed there.
It is this behavior that explains the warning that Tom mentioned.
Suppose your code uses "SendKeys "{del}" and then continues.
As your code in the VB module continues, this may at times have the new
focus.
When the "Del" finally kicks in, it may delete the code window.
This is a problem that I have seen, and tom alluded to.
Public Sub ClearImmediateWindow_Demo2()
Dim MyApp As Application
Dim j As Long
Debug.Print "Before deletion"
Set MyApp = Application
MyApp.VBE.Windows.Item("Immediate").SetFocus
SendKeys "^a"
SendKeys "{del}"
For j = 1 To 6000
Debug.Print j
Next
Debug.Print "My Program stuff!"
' ** {del} usually kicks in here **
End Sub
'= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Well, I would like to go on, but this is getting too long.
I have tried with Application.Wait..., and a long For - Next loop to try to
have the code work.
However, nothing that I have done works.
Do you or anyone else have any thoughts on this? I would be interested in
any feedback. Thanks.
--
Dana DeLouis Windows Me & Office XP
"Dave Peterson" <ec3...@msn.com> wrote in message
news:3C36447E...@msn.com...
Dave Peterson
I have no ideas. In fact, I'm gonna weasel on this one and just use: ctrl-A,
del and click back.
As for the Sendkeys stuff. Were you stepping through the code or just running
it?
I've noticed that my sendkeys don't work as expected while stepping, but work ok
if I run from Tools|macros...
But I guess I just don't see the use of something like this. If I'm running
from a button, then I'm not usually monitoring the immediate window. If I'm
monitoring the immediate window, then I'll stay away from Sendkeys.
(but I do find it interesting that you can (partially) do this kind of stuff in
the VBE.)
--
Dave Peterson
ec3...@msn.com
Dana DeLouis
> I've noticed that my sendkeys don't work as expected while stepping, but
work ok
> if I run from Tools|macros...
I have the same problem. Sendkeys does not work for me either if stepping
thru the code.
Works better while the code is running. I do not know why.
> But I guess I just don't see the use of something like this.
I was looking for a easy way to clear the immediate window while code is
running.
I thought it would make debugging nicer by clearing out old junk.
Excel XP has introduced a new element to the Immediate window.
When I start the VBA editor, I have leftover junk that is put there while
Excel starts.
The VBA editor has stuff like the following...
I assume that everyone else gets this same information when Excel starts???
I was looking for a way to clear this out also.
[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs] >
[PickPlatform] <
[PickPlatform] >
[VerifyOpen] <
[VerifyOpen] > 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs] >
[auto_open] >
(If anyone doesn't get this in Office XP, is there something you did to turn
it off???)
I have read in other Office newsgroups that there really is not a guaranteed
method to clear the Immediate window.
The feeling I got from others is that they were hoping Office XP would
support a more direct method of clearing the Immediate window.
Anyway, just some thoughts. :>)
<snip>
Victor Eldridge
Although the following may create even more mystery, it does succeed at
clearing then writing to the debug window. Hope it helps...
Sub ClearImmediateWindow()
Application.VBE.Windows("Immediate").SetFocus
SendKeys "^a"
SendKeys "{del}"
Application.OnTime Now, "PrintHello"
End Sub
Sub PrintHello()
Debug.Print "Hello!"
End Sub
Bill Manville
> The VBA editor has stuff like the following...
> I assume that everyone else gets this same information when Excel starts???
>
FWIW, That stuff comes from the "Analysis Toolpak - VBA" add-in.
Dave Peterson
I see that Bill Manville answered the question about the analysis toolpak
stuff. (I wondered where it came from, too. So this thread was useful to me
<vbg>.)
I didn't mean to be too much of an **** when I wrote my message. I enjoy
reading your messages. You offer a very nice slant to most problems/solutions.
I apologize if you were offended. Heck, I'll apologize even if you weren't.
(and I hope you weren't!)
Take care.
--
Dave Peterson
ec3...@msn.com
Chip Pearson
some time, and have come to two conclusions: 1) MS goofed big time by not
creating a command to do it, 2) you can't SendMessage to that window.
I've tried using SendMessage, with every message I can think of (e.g, WM_CLEAR,
WM_ERASEBKGRND, WM_KEYUP/KEYDOWN, and several variations on the WM_PAINT theme),
and nothing seems to work. I looked at the message queue using Spy++ when I
manually cleared it, and tried to send those exact messages, but they just don't
take.
I really do think that SendKeys is the only way to clear the Immediate Window.
Sad, but true. If anyone can find a non-SendKeys way to clear the Window, I'll
give you an autographed bottle of my home-brewed hot sauce.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
"Dave Peterson" <ec3...@msn.com> wrote in message
news:3C3B8191...@msn.com...
jamieuk
> Sad, but true.
Is this considered cheating?
Debug.Print String(200, vbCrLf)
Debug.Print "Alone"
Jamie.
Dana DeLouis
Window gets written to from the "Analysis Toolpak - VBA" add-in (Excel XP).
I thought for sure that since data gets written to the Window, that
Microsoft would have included a Method to clear it. I guess not. :>(
I think that Victor had a great suggestion on breaking the program into
multiple parts. I took his idea and expanded on it if anyone is interested.
I have seen other program broken up into 2 or more parts that are separated
not be a Call statement, but by an OnTime command.
I think Rob Bovey's excellent "Code Cleaner" program had to resort to this
type of technique. I don't fully understand the reason he gave for it
though.
Anyway, If one really want to clear the Immediate Window, I think the
following idea appears more reliable.
One of the disadvantages is that some variables may have to be Public
variables.
The idea here is that you run the main program up until you need to clear
the Immediate Window.
Then Clear the Window, and continue the program running a different sub.
Again, not the best solution, but it does appear to work more reliably.
Hopefully, Microsoft will make this easier in the next release.
' = = = = = = = = = = = = = = = = = = = = = = = = = =
Option Explicit
Dim j As Long
Const Pi As Double = 3.14159
Dim Remember ' Remember Window that has Focus
' = = = = = = = = = = = = = = = = = = = = = = = = = =
Sub Main_Program()
' Fill with some data
For j = 1 To 100
Debug.Print j & "Junk Line"
Next j
' Just a pause to view Immediate Window
Application.Wait (Now + TimeSerial(0, 0, 2))
ClearImmediateWindow
Application.OnTime Now, "Continue"
' End of Part 1 of Program
End Sub
' = = = = = = = = = = = = = = = = = = = = = = = = = =
Sub Continue()
' Program continues here...
' Start of Part 2 of Program
' Set Focus to what it was...
Remember.SetFocus
Debug.Print String(20, "= ")
Debug.Print "*** My Debug Window ***"
Debug.Print String(20, "= ")
Debug.Print "Variable Value"
Debug.Print " J:"; j
Debug.Print " Pi:"; Pi
Debug.Print String(20, "= ")
' Continue with program...
End Sub
' = = = = = = = = = = = = = = = = = = = = = = = = = =
' = = = = = = = = = = = = = = = = = = = = = = = = = =
Sub ClearImmediateWindow()
Dim objWindow As Object
Dim objImmediateWindow As Object
' Remember current Focus before changing
Set Remember = ThisWorkbook.VBProject.VBE.ActiveWindow
For Each objWindow In ThisWorkbook.VBProject.VBE.Windows
If objWindow.Type = vbext_wt_Immediate Then
Set objImmediateWindow = objWindow
Exit For
End If
Next
objImmediateWindow.Visible = True
objImmediateWindow.SetFocus
SendKeys "^(a)"
SendKeys "{Del}"
End Sub
' = = = = = = = = = = = = = = = = = = = = = = = = = =
One limitation is the following. Using "Remember" works fine if the program
is started from the VBA editor.
If the program is run from a worksheet, the program will end with the focus
in the VBA editor.
I am not sure how to adjust it so that the focus is returned to the
Workbook.
Does anyone have an idea on how to adjust for this? Thanks.
--
Dana DeLouis Windows Me & Office XP
"Chip Pearson" <ch...@cpearson.com> wrote in message
news:OwVM6AKmBHA.1948@tkmsftngp04...
> I've been playing around with the problem of clearing the Immediate Window
for
> some time, and have come to two conclusions: 1) MS goofed big time by not
> creating a command to do it, 2) you can't SendMessage to that window.
>
> I've tried using SendMessage, with every message I can think of (e.g,
WM_CLEAR,
> WM_ERASEBKGRND, WM_KEYUP/KEYDOWN, and several variations on the WM_PAINT
theme),
> and nothing seems to work. I looked at the message queue using Spy++ when
I
> manually cleared it, and tried to send those exact messages, but they just
don't
> take.
>
> I really do think that SendKeys is the only way to clear the Immediate
Window.
> Sad, but true. If anyone can find a non-SendKeys way to clear the Window,
I'll
> give you an autographed bottle of my home-brewed hot sauce.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com ch...@cpearson.com
<snip>
Tushar Mehta
active (top) window? In XL2002, it did not change the focus to the
Immediate window. Worse, it selected and deleted the contents of my
code module, and then faulted since the PrintHello sub was now gone!
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <3c3aac0b$0$93...@echo-01.iinet.net.au>, Victor Eldridge
<v...@ultratrace.com.au> wrote
Dave Peterson
======this didn't seem to make any difference at all
Public Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long)
As Long
then later:
dim hwnd as long
hwnd = FindWindow("XLMAIN", Application.Caption)
If hwnd <> 0 Then
SetForegroundWindow hwnd
End If
=====Neither did this
AppActivate Application.Caption
=====This one swapped from the VBE to xl, but swapped right back
SendKeys "%{F11}"
=====This one actually closed the VBE, but the it reopened.
SendKeys "%q"
=====
I ran them all from within the VBE (xl2002).
======
(Now, I think I like Ctrl-A, delete even better! <vvbg>
--
Dave Peterson
ec3...@msn.com
Victor Eldridge
After playing around some more, I've found that the command,
" Application.VBE.Windows("Immediate").SetFocus "
will only activate the immediate window if it's docked. If the window is
free-floating, then the active codepane receives the focus, with no errors
being raised. Dangerous stuff.
Perhaps this is why Bill Manville? used the following to activate the
window,
" Application.VBE.CommandBars.FindControl(ID:=2554).Execute "
which appears to work reliably, be the window docked or not.
Regards,
v...@ultratrace.com.au
"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
news:MPG.16a7d2b75...@msnews.microsoft.com...