In a VBA macro, how can I clear the Immediate Window before doing a series
of Debug.Prints?

How can I home down or append to the bottom of the Immediate Window?

I would like this to work whether or not the Immediate Window is open.

I am using Excel 2003 on Win XP SP3.

I can do this manually, of course. But I would like the macro to do it.

In response to a similar question in Aug and Sep 2008, Bob Phillips offered:

Sub ClearImmediateWindow()
Application.SendKeys "^g ^a {DEL}"
End Sub

CharlotteE followed up (in Aug), saying that it needed to be preceded with:

Application.VBE.MainWindow.Visible = True
Application.VBE.MainWindow.SetFocus

But when I try to put that together:

1. I get an error on the first .Visible statement, namely "method VBE of
object _Application failed".

2. Without the Application.VBE statements, the .SendKeys clears the editor
pane, not the Immediate Window.

(Unless I open the Immediate Window manually and execute the macro from
the Immediate Window. Not what I want.)

With the Immediate Window closed (or open, at my discretion) and with the VB
editing pane visible, I want to put the cursor in the following macro and
press F5 to start execution.

Sub Doit()
Dim x as Double ...etc...
' clear Immediate Window here
Application.SendKeys "^g ^a {DEL}"
For i = ...
....statements includig Debug.Print...
Next i
End Sub

It might relevant to note that I have the Immediate Window in a mode where
it is "floating" (not docked?). That is, it is not the bottom pane of the
VBA window, which I think is its default position. The Immediate Window
partially overlays the VBA window when both are open.

You need to have trusted source option set in the worksheet menu

Tools - Macro - security - Trusted source - Trust Access to visual Basic
Project

"JoeU2004" wrote:

In a VBA macro, how can I clear the Immediate Window before doing a series
of Debug.Prints?

How can I home down or append to the bottom of the Immediate Window?

I would like this to work whether or not the Immediate Window is open.

I am using Excel 2003 on Win XP SP3.

I can do this manually, of course. But I would like the macro to do it.

In response to a similar question in Aug and Sep 2008, Bob Phillips offered:

Sub ClearImmediateWindow()
Application.SendKeys "^g ^a {DEL}"
End Sub

CharlotteE followed up (in Aug), saying that it needed to be preceded with:

Application.VBE.MainWindow.Visible = True
Application.VBE.MainWindow.SetFocus

But when I try to put that together:

1. I get an error on the first .Visible statement, namely "method VBE of
object _Application failed".

2. Without the Application.VBE statements, the .SendKeys clears the editor
pane, not the Immediate Window.

(Unless I open the Immediate Window manually and execute the macro from
the Immediate Window. Not what I want.)

With the Immediate Window closed (or open, at my discretion) and with the VB
editing pane visible, I want to put the cursor in the following macro and
press F5 to start execution.

Sub Doit()
Dim x as Double ...etc...
' clear Immediate Window here
Application.SendKeys "^g ^a {DEL}"
For i = ...
....statements includig Debug.Print...
Next i
End Sub

It might relevant to note that I have the Immediate Window in a mode where
it is "floating" (not docked?). That is, it is not the bottom pane of the
VBA window, which I think is its default position. The Immediate Window
partially overlays the VBA window when both are open.

"Joel" wrote:
You need to have trusted source option set in the worksheet menu
Tools - Macro - security - Trusted source - Trust Access to visual Basic
Project

Thanks.

In my version of Excel (Excel 2003 11.5612.5606, part of MS Office Sm Busn
Ed 2003, mfr installed, no patches that I remember unless they are included
in Win XP SP3), the "path" is Tools Macro Security Trusted Publishers
"Trusted access to Virtual Basic Project".

I presume you are talking about the same thing.

That definitely helped insofar as I no longer get a runtime error for the
Application.VBE statements.

However, the previously-suggested statements still do not do seem to work.
See the test macro below.

Aha!....

If I put a Stop statement after the SendKeys statement, it does what I want
(after I continue execution) even without the Application.VBE statements and
without enabling "Trusted acces to VBProject".

But if I replace the Stop statement with a delay of as much as 1 sec, it
still does not work.

What's going on?

What should the Application.VBE statements do? Why should I need them?

Finally, a nitpick.... Even when the SendKeys statement works (i.e. after
continuing from a Stop statement), the output of the first line is shifted
right by one character. I suspect it is a space, or perhaps it is the DEL
character.

How can I avoid that?

Of course, there is an obvious work-around, namely adding an initial
Debug.Print with no arguments. But I'd prefer not to.


' ---------------------
' Test macro

#Const doSetFocus = False
#Const doSendKeys = True

Private nRun As Integer

Sub doit()
nRun = nRun + 1
#If doSetFocus Then ' ****why do I need this?
' set Excel Macro Security Trusted Publishers Trusted access to VB
Project
Application.VBE.MainWindow.Visible = True
Application.VBE.MainWindow.SetFocus
#End If
#If doSendKeys Then
Application.SendKeys "^g ^a {DEL}"
Stop ' ****want to avoid this
#End If
Debug.Print '**** want to avoid this
For i = 1 To 10
Debug.Print "line "; i
Next i
Debug.Print "test "; nRun
End Sub


----- original message -----

"Joel" wrote in message
...
You need to have trusted source option set in the worksheet menu

Tools - Macro - security - Trusted source - Trust Access to visual Basic
Project

"JoeU2004" wrote:

In a VBA macro, how can I clear the Immediate Window before doing a
series
of Debug.Prints?

How can I home down or append to the bottom of the Immediate Window?

I would like this to work whether or not the Immediate Window is open.

I am using Excel 2003 on Win XP SP3.

I can do this manually, of course. But I would like the macro to do it.

In response to a similar question in Aug and Sep 2008, Bob Phillips
offered:

Sub ClearImmediateWindow()
Application.SendKeys "^g ^a {DEL}"
End Sub

CharlotteE followed up (in Aug), saying that it needed to be preceded
with:

Application.VBE.MainWindow.Visible = True
Application.VBE.MainWindow.SetFocus

But when I try to put that together:

1. I get an error on the first .Visible statement, namely "method VBE of
object _Application failed".

2. Without the Application.VBE statements, the .SendKeys clears the
editor
pane, not the Immediate Window.

(Unless I open the Immediate Window manually and execute the macro
from
the Immediate Window. Not what I want.)

With the Immediate Window closed (or open, at my discretion) and with the
VB
editing pane visible, I want to put the cursor in the following macro and
press F5 to start execution.

Sub Doit()
Dim x as Double ...etc...
' clear Immediate Window here
Application.SendKeys "^g ^a {DEL}"
For i = ...
....statements includig Debug.Print...
Next i
End Sub

It might relevant to note that I have the Immediate Window in a mode
where
it is "floating" (not docked?). That is, it is not the bottom pane of
the
VBA window, which I think is its default position. The Immediate Window
partially overlays the VBA window when both are open.

I can get the pane. Have't figured out how to clear the pane

Sub test()
For i = 1 To 10
Debug.Print "line "; i
Next i
Set XVBE = Application.VBE
For Each Win In XVBE.Windows
If Win.Caption = "Immediate" Then
a = 1
Numrows = Win.Collection.Count

'this doesn't work.
' For i = Numrows To 1 Step -1
' a = Win.ClearContents
' Next i

End If

Next Win

End Sub

"JoeU2004" wrote:

"Joel" wrote:
You need to have trusted source option set in the worksheet menu
Tools - Macro - security - Trusted source - Trust Access to visual Basic
Project

Thanks.

In my version of Excel (Excel 2003 11.5612.5606, part of MS Office Sm Busn
Ed 2003, mfr installed, no patches that I remember unless they are included
in Win XP SP3), the "path" is Tools Macro Security Trusted Publishers
"Trusted access to Virtual Basic Project".

I presume you are talking about the same thing.

That definitely helped insofar as I no longer get a runtime error for the
Application.VBE statements.

However, the previously-suggested statements still do not do seem to work.
See the test macro below.

Aha!....

If I put a Stop statement after the SendKeys statement, it does what I want
(after I continue execution) even without the Application.VBE statements and
without enabling "Trusted acces to VBProject".

But if I replace the Stop statement with a delay of as much as 1 sec, it
still does not work.

What's going on?

What should the Application.VBE statements do? Why should I need them?

Finally, a nitpick.... Even when the SendKeys statement works (i.e. after
continuing from a Stop statement), the output of the first line is shifted
right by one character. I suspect it is a space, or perhaps it is the DEL
character.

How can I avoid that?

Of course, there is an obvious work-around, namely adding an initial
Debug.Print with no arguments. But I'd prefer not to.


' ---------------------
' Test macro

#Const doSetFocus = False
#Const doSendKeys = True

Private nRun As Integer

Sub doit()
nRun = nRun + 1
#If doSetFocus Then ' ****why do I need this?
' set Excel Macro Security Trusted Publishers Trusted access to VB
Project
Application.VBE.MainWindow.Visible = True
Application.VBE.MainWindow.SetFocus
#End If
#If doSendKeys Then
Application.SendKeys "^g ^a {DEL}"
Stop ' ****want to avoid this
#End If
Debug.Print '**** want to avoid this
For i = 1 To 10
Debug.Print "line "; i
Next i
Debug.Print "test "; nRun
End Sub


----- original message -----

"Joel" wrote in message
...
You need to have trusted source option set in the worksheet menu

Tools - Macro - security - Trusted source - Trust Access to visual Basic
Project

"JoeU2004" wrote:

In a VBA macro, how can I clear the Immediate Window before doing a
series
of Debug.Prints?

How can I home down or append to the bottom of the Immediate Window?

I would like this to work whether or not the Immediate Window is open.

I am using Excel 2003 on Win XP SP3.

I can do this manually, of course. But I would like the macro to do it.

In response to a similar question in Aug and Sep 2008, Bob Phillips
offered:

Sub ClearImmediateWindow()
Application.SendKeys "^g ^a {DEL}"
End Sub

CharlotteE followed up (in Aug), saying that it needed to be preceded
with:

Application.VBE.MainWindow.Visible = True
Application.VBE.MainWindow.SetFocus

But when I try to put that together:

1. I get an error on the first .Visible statement, namely "method VBE of
object _Application failed".

2. Without the Application.VBE statements, the .SendKeys clears the
editor
pane, not the Immediate Window.

(Unless I open the Immediate Window manually and execute the macro
from
the Immediate Window. Not what I want.)

With the Immediate Window closed (or open, at my discretion) and with the
VB
editing pane visible, I want to put the cursor in the following macro and
press F5 to start execution.

Sub Doit()
Dim x as Double ...etc...
' clear Immediate Window here
Application.SendKeys "^g ^a {DEL}"
For i = ...
....statements includig Debug.Print...
Next i
End Sub

It might relevant to note that I have the Immediate Window in a mode
where
it is "floating" (not docked?). That is, it is not the bottom pane of
the
VBA window, which I think is its default position. The Immediate Window
partially overlays the VBA window when both are open.