Clearing Immediate window in VBA by code

[Madiya]

I am using some debug.print statements in my code which prints current
value of my variables in the immediate window.
When I run the code again, I have to manually select all and delete
the values from the immediate window.

Is there any way by which I can just run the code to clear the
immediate window?

Thanks and Regards,
Madiya

 

[Mike H]

Sadly you can't do it programatically

Mike

 

[Anant Basant]

try the following code.

Sub DelInImmedate()
Debug.Print Now
Application.SendKeys "^g ^a {DEL}"
End Sub

 

[Mike H]

very nice i never thought of sendkeys

try the following code.

Sub DelInImmedate()
Debug.Print Now
Application.SendKeys "^g ^a {DEL}"
End Sub

 

[Bob Phillips]

Yes you can, without SendKeys too. here is some code from KeepItCool a while
back

Option Explicit


'<--- Excel Version --->
' Code in a standard module


Private Declare Function GetWindow _
Lib "user32" ( _
ByVal hWnd As Long, _
ByVal wCmd As Long) As Long
Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx _
Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Declare Function GetKeyboardState _
Lib "user32" (pbKeyState As Byte) As Long
Private Declare Function SetKeyboardState _
Lib "user32" (lppbKeyState As Byte) As Long
Private Declare Function PostMessage _
Lib "user32" Alias "PostMessageA" ( _
ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long _
) As Long


Private Const WM_KEYDOWN As Long = &H100
Private Const KEYSTATE_KEYDOWN As Long = &H80


Private savState(0 To 255) As Byte


Sub ClearImmediateWindow()
'Adapted by keepITcool
'Original from Jamie Collins fka "OneDayWhen"
'http://www.dicks-blog.com/excel/2004/06/clear_the_immed.html


Dim hPane As Long
Dim tmpState(0 To 255) As Byte


hPane = GetImmHandle
If hPane = 0 Then MsgBox "Immediate Window not found."
If hPane < 1 Then Exit Sub


'Save the keyboardstate
GetKeyboardState savState(0)


'Sink the CTRL (note we work with the empty tmpState)
tmpState(vbKeyControl) = KEYSTATE_KEYDOWN
SetKeyboardState tmpState(0)
'Send CTRL+End
PostMessage hPane, WM_KEYDOWN, vbKeyEnd, 0&
'Sink the SHIFT
tmpState(vbKeyShift) = KEYSTATE_KEYDOWN
SetKeyboardState tmpState(0)
'Send CTRLSHIFT+Home and CTRLSHIFT+BackSpace
PostMessage hPane, WM_KEYDOWN, vbKeyHome, 0&
PostMessage hPane, WM_KEYDOWN, vbKeyBack, 0&


'Schedule cleanup code to run
Application.OnTime Now + TimeSerial(0, 0, 0), "DoCleanUp"


End Sub


Sub DoCleanUp()
' Restore keyboard state
SetKeyboardState savState(0)
End Sub


Function GetImmHandle() As Long
'This function finds the Immediate Pane and returns a handle.
'Docked or MDI, Desked or Floating, Visible or Hidden


Dim oWnd As Object, bDock As Boolean, bShow As Boolean
Dim sMain$, sDock$, sPane$
Dim lMain&, lDock&, lPane&


On Error Resume Next
sMain = Application.VBE.MainWindow.Caption
If Err <> 0 Then
MsgBox "No Access to Visual Basic Project"
GetImmHandle = -1
Exit Function
' Excel2003: Registry Editor (Regedit.exe)
' HKLM\SOFTWARE\Microsoft\Office\11.0\Excel\Security
' Change or add a DWORD called 'AccessVBOM', set to 1
' Excel2002: Tools/Macro/Security
' Tab 'Trusted Sources', Check 'Trust access..'
End If


For Each oWnd In Application.VBE.Windows
If oWnd.Type = 5 Then
bShow = oWnd.Visible
sPane = oWnd.Caption
If Not oWnd.LinkedWindowFrame Is Nothing Then
bDock = True
sDock = oWnd.LinkedWindowFrame.Caption
End If
Exit For
End If
Next
lMain = FindWindow("wndclass_desked_gsk", sMain)
If bDock Then
'Docked within the VBE
lPane = FindWindowEx(lMain, 0&, "VbaWindow", sPane)
If lPane = 0 Then
'Floating Pane.. which MAY have it's own frame
lDock = FindWindow("VbFloatingPalette", vbNullString)
lPane = FindWindowEx(lDock, 0&, "VbaWindow", sPane)
While lDock > 0 And lPane = 0
lDock = GetWindow(lDock, 2) 'GW_HWNDNEXT = 2
lPane = FindWindowEx(lDock, 0&, "VbaWindow", sPane)
Wend
End If
ElseIf bShow Then
lDock = FindWindowEx(lMain, 0&, "MDIClient", _
vbNullString)
lDock = FindWindowEx(lDock, 0&, "DockingView", _
vbNullString)
lPane = FindWindowEx(lDock, 0&, "VbaWindow", sPane)
Else
lPane = FindWindowEx(lMain, 0&, "VbaWindow", sPane)
End If


GetImmHandle = lPane


End Function


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Interseting,

I had always taken as read the information on Chip Pearson's site:-

Unfortunately, there is no way to programmatically clear the Immediate
Window. This is a shortcoming that has frustrated many programmers
http://www.cpearson.com/excel/Debug.htm

Thanks for the information

Mike

 

[Bob Phillips]

If you had MZ-Tools Mike, you would know it is possible, he has a button to
do it.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim Rech]

That's very impressive but the way I'd want to use this is in testing some
code that did a lot of Debug.Prints and I wanted to start with a blank slate
each time.


Debug.Print "AAAAAAAAAAAAAAAAAA"
ClearImmediateWindow
Debug.Print "BBBBBBBBBBBBB"

I would expect this to leave B's in the Immediate window but it is left
blank. Even Do Events and Application.Wait before the second Debug didn't
help. So is this actually useful I wonder? Example?

--
Jim

| Yes you can, without SendKeys too. here is some code from KeepItCool a
while
| back
|
| Option Explicit
|
|
| '<--- Excel Version --->
| ' Code in a standard module
|
|
| Private Declare Function GetWindow _
| Lib "user32" ( _
| ByVal hWnd As Long, _
| ByVal wCmd As Long) As Long
| Private Declare Function FindWindow _
| Lib "user32" Alias "FindWindowA" ( _
| ByVal lpClassName As String, _
| ByVal lpWindowName As String) As Long
| Private Declare Function FindWindowEx _
| Lib "user32" Alias "FindWindowExA" _
| (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
| ByVal lpsz1 As String, _
| ByVal lpsz2 As String) As Long
| Private Declare Function GetKeyboardState _
| Lib "user32" (pbKeyState As Byte) As Long
| Private Declare Function SetKeyboardState _
| Lib "user32" (lppbKeyState As Byte) As Long
| Private Declare Function PostMessage _
| Lib "user32" Alias "PostMessageA" ( _
| ByVal hWnd As Long, ByVal wMsg As Long, _
| ByVal wParam As Long, ByVal lParam As Long _
| ) As Long
|
|
| Private Const WM_KEYDOWN As Long = &H100
| Private Const KEYSTATE_KEYDOWN As Long = &H80
|
|
| Private savState(0 To 255) As Byte
|
|
| Sub ClearImmediateWindow()
| 'Adapted by keepITcool
| 'Original from Jamie Collins fka "OneDayWhen"
| 'http://www.dicks-blog.com/excel/2004/06/clear_the_immed.html
|
|
| Dim hPane As Long
| Dim tmpState(0 To 255) As Byte
|
|
| hPane = GetImmHandle
| If hPane = 0 Then MsgBox "Immediate Window not found."
| If hPane < 1 Then Exit Sub
|
|
| 'Save the keyboardstate
| GetKeyboardState savState(0)
|
|
| 'Sink the CTRL (note we work with the empty tmpState)
| tmpState(vbKeyControl) = KEYSTATE_KEYDOWN
| SetKeyboardState tmpState(0)
| 'Send CTRL+End
| PostMessage hPane, WM_KEYDOWN, vbKeyEnd, 0&
| 'Sink the SHIFT
| tmpState(vbKeyShift) = KEYSTATE_KEYDOWN
| SetKeyboardState tmpState(0)
| 'Send CTRLSHIFT+Home and CTRLSHIFT+BackSpace
| PostMessage hPane, WM_KEYDOWN, vbKeyHome, 0&
| PostMessage hPane, WM_KEYDOWN, vbKeyBack, 0&
|
|
| 'Schedule cleanup code to run
| Application.OnTime Now + TimeSerial(0, 0, 0), "DoCleanUp"
|
|
| End Sub
|
|
| Sub DoCleanUp()
| ' Restore keyboard state
| SetKeyboardState savState(0)
| End Sub
|
|
| Function GetImmHandle() As Long
| 'This function finds the Immediate Pane and returns a handle.
| 'Docked or MDI, Desked or Floating, Visible or Hidden
|
|
| Dim oWnd As Object, bDock As Boolean, bShow As Boolean
| Dim sMain$, sDock$, sPane$
| Dim lMain&, lDock&, lPane&
|
|
| On Error Resume Next
| sMain = Application.VBE.MainWindow.Caption
| If Err <> 0 Then
| MsgBox "No Access to Visual Basic Project"
| GetImmHandle = -1
| Exit Function
| ' Excel2003: Registry Editor (Regedit.exe)
| ' HKLM\SOFTWARE\Microsoft\Office\11.0\Excel\Security
| ' Change or add a DWORD called 'AccessVBOM', set to 1
| ' Excel2002: Tools/Macro/Security
| ' Tab 'Trusted Sources', Check 'Trust access..'
| End If
|
|
| For Each oWnd In Application.VBE.Windows
| If oWnd.Type = 5 Then
| bShow = oWnd.Visible
| sPane = oWnd.Caption
| If Not oWnd.LinkedWindowFrame Is Nothing Then
| bDock = True
| sDock = oWnd.LinkedWindowFrame.Caption
| End If
| Exit For
| End If
| Next
| lMain = FindWindow("wndclass_desked_gsk", sMain)
| If bDock Then
| 'Docked within the VBE
| lPane = FindWindowEx(lMain, 0&, "VbaWindow", sPane)
| If lPane = 0 Then
| 'Floating Pane.. which MAY have it's own frame
| lDock = FindWindow("VbFloatingPalette", vbNullString)
| lPane = FindWindowEx(lDock, 0&, "VbaWindow", sPane)
| While lDock > 0 And lPane = 0
| lDock = GetWindow(lDock, 2) 'GW_HWNDNEXT = 2
| lPane = FindWindowEx(lDock, 0&, "VbaWindow", sPane)
| Wend
| End If
| ElseIf bShow Then
| lDock = FindWindowEx(lMain, 0&, "MDIClient", _
| vbNullString)
| lDock = FindWindowEx(lDock, 0&, "DockingView", _
| vbNullString)
| lPane = FindWindowEx(lDock, 0&, "VbaWindow", sPane)
| Else
| lPane = FindWindowEx(lMain, 0&, "VbaWindow", sPane)
| End If
|
|
| GetImmHandle = lPane
|
|
| End Function
|
|
| --
| ---
| HTH
|
| Bob
|
|
| (there's no email, no snail mail, but somewhere should be gmail in my
addy)
|
|
|
| | > Sadly you can't do it programatically
| >
| > Mike
| >
| > "Madiya" wrote:
| >
| >> I am using some debug.print statements in my code which prints current
| >> value of my variables in the immediate window.
| >> When I run the code again, I have to manually select all and delete
| >> the values from the immediate window.
| >>
| >> Is there any way by which I can just run the code to clear the
| >> immediate window?
| >>
| >> Thanks and Regards,
| >> Madiya
| >>
|
|

 

[Mike H]

I have it now, Thanks again

If you had MZ-Tools Mike, you would know it is possible, he has a button to
do it.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

Did that code work for you? All that happens for me is the date and time are
printed in the Immediate window... nothing is cleared.

Rick

 

[Bob Phillips]

Before I got MZ-Tools, I created a VBE button so that I could clear it at
will


Private WithEvents VBEEvents As CommandBarEvents

Private Sub Workbook_Open()

With Application.VBE.CommandBars("Standard")
On Error Resume Next
.FindControl(Tag:="VBEClear").Delete
On Error GoTo 0

With .Controls.Add(temporary:=True)
.Tag = "VBEClear
.Caption = "Clear Immediate Window"
.Style = msoButtonIcon
.FaceId = 1964
Set VBEEvents = Application.VBE.Events.CommandBarEvents(.Control)
End With
End With
End Sub

Private Sub VBEEvents_Click(ByVal CommandBarControl As Object, _
Handled As Boolean, _
CancelDefault As Boolean)
If CommandBarControl.Caption = "Clear Immediate Window" Then
ClearImmediateWindow
End If
End Sub

But I agree it would be nice to do it in-line. I am sure I had some other
code as well, I'll check it out and see if it allows a programmatic call.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

I must be doing something wrong... I placed that code in a Module and
whether I call ClearImmediateWindow from a Sheet's macro or a UserForm's
event procedure (a button Click event for my test), I get a message saying
"No Access to Visual Basic Project". What am I missing here?

Rick

 

[Mike H]

Yes it worked fine

Did that code work for you? All that happens for me is the date and time are
printed in the Immediate window... nothing is cleared.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Where are you running it from? I tried placing it in a Sheet's code window
(as a macro) and running it either directly from there using the Run button
or from a worksheet using Alt+F8. I also tried running it from the Click
event of a CommandButton on a UserForm. Neither method cleared the Immediate
window.

Rick

 

[Mike H]

I put it in an ordinary module then called it with this in sheet1 code window
to test it

Sub stance()
Dim x As Long
For x = 1 To 10
Debug.Print x
Next
ClearImmediateWindow
End Sub

Mike

 

[Rick Rothstein \(MVP - VB\)]

Hmm! When I try that, I keep getting this message... "No Access to Visual
Basic Project".

Rick

 

[Rick Rothstein \(MVP - VB\)]

Okay, there is a subroutine name mix up here. Using Bob Phillips' code
(which has the subroutine named ClearImmediateWindow), I get the error
message I just posted to you. Using Anant's DelInImmedate subroutine (which
this sub-thread deals with and which I think you meant to use in your
example code), I don't get the error message, but the Immediate window
doesn't clear either... all that happens is the date/time is printed in the
Immediate window.

Rick

 

[Mike H]

I read that in your last post and Googled it but got no clues

 

[Dave Peterson]

In xl2003 menu system:
Tools|Macro|Security|Trusted Publishers tab
Check "Trust access to Visual Basic Project"

 

[Mike H]

Try

Tools|Options|security|Macro Security|Trusted Publishers
Check Trust access to VB project

Mike