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

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

try the following code.

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


"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

very nice i never thought of sendkeys

"Anant Basant" wrote:

try the following code.

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


"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

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" wrote in message
...
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

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" wrote:

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" wrote in message
...
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

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)



"Mike H" wrote in message
...
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" wrote:

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" wrote in message
...
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

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

"Bob Phillips" wrote in message
...
| 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" wrote in message
| ...
| 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
|
|
|

I have it now, Thanks again

"Bob Phillips" wrote:

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)



"Mike H" wrote in message
...
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" wrote:

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" wrote in message
...
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

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


"Mike H" wrote in message
...
very nice i never thought of sendkeys

"Anant Basant" wrote:

try the following code.

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


"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

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)



"Jim Rech" wrote in message
...
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

"Bob Phillips" wrote in message
...
| 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" wrote in message
| ...
| 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
|
|
|

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


"Bob Phillips" wrote in message
...
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" wrote in message
...
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

Yes it worked fine

"Rick Rothstein (MVP - VB)" wrote:

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


"Mike H" wrote in message
...
very nice i never thought of sendkeys

"Anant Basant" wrote:

try the following code.

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


"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

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" wrote in message
...
Yes it worked fine

"Rick Rothstein (MVP - VB)" wrote:

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


"Mike H" wrote in message
...
very nice i never thought of sendkeys

"Anant Basant" wrote:

try the following code.

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


"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

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)" wrote:

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" wrote in message
...
Yes it worked fine

"Rick Rothstein (MVP - VB)" wrote:

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


"Mike H" wrote in message
...
very nice i never thought of sendkeys

"Anant Basant" wrote:

try the following code.

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


"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

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

Rick


"Mike H" wrote in message
...
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)" wrote:

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" wrote in message
...
Yes it worked fine

"Rick Rothstein (MVP - VB)" wrote:

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


"Mike H" wrote in message
...
very nice i never thought of sendkeys

"Anant Basant" wrote:

try the following code.

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


"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

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
Hmm! When I try that, I keep getting this message... "No Access to Visual
Basic Project".

Rick


"Mike H" wrote in message
...
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)" wrote:

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" wrote in message
...
Yes it worked fine

"Rick Rothstein (MVP - VB)" wrote:

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


"Mike H" wrote in message
...
very nice i never thought of sendkeys

"Anant Basant" wrote:

try the following code.

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


"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

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

"Rick Rothstein (MVP - VB)" wrote:

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

Rick


"Mike H" wrote in message
...
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)" wrote:

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" wrote in message
...
Yes it worked fine

"Rick Rothstein (MVP - VB)" wrote:

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


"Mike H" wrote in message
...
very nice i never thought of sendkeys

"Anant Basant" wrote:

try the following code.

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


"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

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



"Rick Rothstein (MVP - VB)" wrote:

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

Rick

"Mike H" wrote in message
...
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)" wrote:

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" wrote in message
...
Yes it worked fine

"Rick Rothstein (MVP - VB)" wrote:

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


"Mike H" wrote in message
...
very nice i never thought of sendkeys

"Anant Basant" wrote:

try the following code.

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


"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






--

Dave Peterson

Try

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

Mike

"Rick Rothstein (MVP - VB)" wrote:

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

Rick


"Mike H" wrote in message
...
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)" wrote:

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" wrote in message
...
Yes it worked fine

"Rick Rothstein (MVP - VB)" wrote:

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


"Mike H" wrote in message
...
very nice i never thought of sendkeys

"Anant Basant" wrote:

try the following code.

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


"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 wrote:
I read that in your last post and Googled it but got no clues

"Rick Rothstein (MVP - VB)" wrote:

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

Rick

Goto Tools - Macro - Security - Trusted Publishers tab.

Check the box "Trust Acces to Visual Basic Project"

--
Adrian C

Okay, that worked. Thanks.

A question, though... since that option's default was to be unchecked (at
least, I'm assuming that since I never visited that tab before), what
"dangers" does checking it open my system up to?

Rick


"Dave Peterson" wrote in message
...
In xl2003 menu system:
Tools|Macro|Security|Trusted Publishers tab
Check "Trust access to Visual Basic Project"



"Rick Rothstein (MVP - VB)" wrote:

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

Rick

"Mike H" wrote in message
...
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)" wrote:

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" wrote in message
...
Yes it worked fine

"Rick Rothstein (MVP - VB)" wrote:

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


"Mike H" wrote in message
...
very nice i never thought of sendkeys

"Anant Basant" wrote:

try the following code.

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


"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






--

Dave Peterson

Back in the day when "macro viruses" were all the rage they sometimes
propagated programming the VBE itself. I think the misanthropes have mostly
moved to the web so not a great deal to worry about, and you can always
disable all macros when opening a workbook.

--
Jim
"Rick Rothstein (MVP - VB)" wrote in
message ...
| Okay, that worked. Thanks.
|
| A question, though... since that option's default was to be unchecked (at
| least, I'm assuming that since I never visited that tab before), what
| "dangers" does checking it open my system up to?
|
| Rick
|
|
| "Dave Peterson" wrote in message
| ...
| In xl2003 menu system:
| Tools|Macro|Security|Trusted Publishers tab
| Check "Trust access to Visual Basic Project"
|
|
|
| "Rick Rothstein (MVP - VB)" wrote:
|
| Hmm! When I try that, I keep getting this message... "No Access to
Visual
| Basic Project".
|
| Rick
|
| "Mike H" wrote in message
| ...
| 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)" wrote:
|
| 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" wrote in message
| ...
| Yes it worked fine
|
| "Rick Rothstein (MVP - VB)" wrote:
|
| 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
|
|
| "Mike H" wrote in message
| ...
| very nice i never thought of sendkeys
|
| "Anant Basant" wrote:
|
| try the following code.
|
| Sub DelInImmedate()
| Debug.Print Now
| Application.SendKeys "^g ^a {DEL}"
| End Sub
| --
| Regards,
| Anant
|
|
| "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
|
|
|
|
|
|
| --
|
| Dave Peterson
|

Thanks for the reply. I have it ask me whether to enable macros or not at
start up.. that should be protection enough, so I'll leave the option
checked.

Rick


"Jim Rech" wrote in message
...
Back in the day when "macro viruses" were all the rage they sometimes
propagated programming the VBE itself. I think the misanthropes have
mostly
moved to the web so not a great deal to worry about, and you can always
disable all macros when opening a workbook.

--
Jim
"Rick Rothstein (MVP - VB)" wrote in
message ...
| Okay, that worked. Thanks.
|
| A question, though... since that option's default was to be unchecked
(at
| least, I'm assuming that since I never visited that tab before), what
| "dangers" does checking it open my system up to?
|
| Rick
|
|
| "Dave Peterson" wrote in message
| ...
| In xl2003 menu system:
| Tools|Macro|Security|Trusted Publishers tab
| Check "Trust access to Visual Basic Project"
|
|
|
| "Rick Rothstein (MVP - VB)" wrote:
|
| Hmm! When I try that, I keep getting this message... "No Access to
Visual
| Basic Project".
|
| Rick
|
| "Mike H" wrote in message
| ...
| 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)" wrote:
|
| 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" wrote in message
| ...
| Yes it worked fine
|
| "Rick Rothstein (MVP - VB)" wrote:
|
| 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
|
|
| "Mike H" wrote in message
| ...
| very nice i never thought of sendkeys
|
| "Anant Basant" wrote:
|
| try the following code.
|
| Sub DelInImmedate()
| Debug.Print Now
| Application.SendKeys "^g ^a {DEL}"
| End Sub
| --
| Regards,
| Anant
|
|
| "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
|
|
|
|
|
|
| --
|
| Dave Peterson
|

You need to set a reference to the Extensibility library.

--
---
HTH

Bob


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



"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Bob Phillips" wrote in message
...
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" wrote in message
...
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

As it turns out, this answer...

Tools|Macro|Security|Trusted Publishers tab
Check "Trust access to Visual Basic Project"

which I got in the other sub-thread allowed the subroutine to work.

Thanks for posting the code, by the way... like others, I figured it was not
possible to clear the Immediate window via code. Pretty neat.

Rick


"Bob Phillips" wrote in message
...
You need to set a reference to the Extensibility library.

--
---
HTH

Bob


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



"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Bob Phillips" wrote in message
...
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" wrote in message
...
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

I know, I goofed!

--
---
HTH

Bob


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



"Rick Rothstein (MVP - VB)" wrote in
message ...
As it turns out, this answer...

Tools|Macro|Security|Trusted Publishers tab
Check "Trust access to Visual Basic Project"

which I got in the other sub-thread allowed the subroutine to work.

Thanks for posting the code, by the way... like others, I figured it was
not possible to clear the Immediate window via code. Pretty neat.

Rick


"Bob Phillips" wrote in message
...
You need to set a reference to the Extensibility library.

--
---
HTH

Bob


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



"Rick Rothstein (MVP - VB)" wrote
in message ...
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


"Bob Phillips" wrote in message
...
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" wrote in message
...
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

On Mar 24, 10:43*pm, "Bob Phillips" wrote:
You need to set a reference to the Extensibility library.

--
---
HTH

Bob

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

"Rick Rothstein (MVP - VB)" wrote in
. ..



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

"Bob Phillips" wrote in message
...
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\Securit y
* *' * *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" wrote in message
...
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- Hide quoted text -

- Show quoted text -

Its great !!!!!!!!!!!!!!!!!!!!!
I thought that I have a very simple problem.
I was watching curiously. There's a lot to learn when great minds are
at work.

Bob,
It works for me perfectly without setting any referance to the
Extensibility library.
Is this referance required anyway?

Regards,
Madiya

If I follow how it all works, the PostMessage API places various keystorkes
in the 'queue' which'll only get processed when the code completes. Hence
your B's get printed before all is deleeted deleted.

I assume this is also why the "restore keyboard state" is done with an
Ontime macro, in particular to reset Ctrl & Shift (ctrtl-shift-home
backspace).

Regards,
Peter T


"Jim Rech" wrote in message
...
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

Madiya,

No as I said to Rick, that was me goofing up. You just need to set Trust
Access To Visual Basic Project

--
---
HTH

Bob


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



"Madiya" wrote in message
...
On Mar 24, 10:43 pm, "Bob Phillips" wrote:
You need to set a reference to the Extensibility library.

--
---
HTH

Bob

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

"Rick Rothstein (MVP - VB)" wrote in
. ..



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

"Bob Phillips" wrote in message
...
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" wrote in message
...
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- Hide quoted text -

- Show quoted text -

Its great !!!!!!!!!!!!!!!!!!!!!
I thought that I have a very simple problem.
I was watching curiously. There's a lot to learn when great minds are
at work.

Bob,
It works for me perfectly without setting any referance to the
Extensibility library.
Is this referance required anyway?

Regards,
Madiya

The security setting Check "Trust access to Visual Basic Project" needs to
be checked due to this line -

sMain = Application.VBE.MainWindow.Caption

I'm pretty sure it'd be possible to get the handle of the Immediate Window
(ultimate objective) via other means, which would avoid the concern over
that setting. Although maybe it's also required for other stuff, I'd need to
look more, eg the docking.

Regards,
Peter T

"Rick Rothstein (MVP - VB)" wrote in
message ...
As it turns out, this answer...

Tools|Macro|Security|Trusted Publishers tab
Check "Trust access to Visual Basic Project"

which I got in the other sub-thread allowed the subroutine to work.

Thanks for posting the code, by the way... like others, I figured it was
not
possible to clear the Immediate window via code. Pretty neat.

Rick

Just curiosity, have you or anyone actually ever received a malicious virus
in an Excel xls/a.

A couple of years ago I searched the big name antivirus sites and the only
mention I could find was documented last century, I think a file for XL95

Regards,
Peter T

"Jim Rech"
Back in the day when "macro viruses" were all the rage they sometimes
propagated programming the VBE itself. I think the misanthropes have
mostly
moved to the web so not a great deal to worry about, and you can always
disable all macros when opening a workbook.

--

Sorry for not scrolling done this far, Peter<g. Nope, I have never
personally received a file with a virus attached. I had someone send me one
he got years ago just so I could look it over As you say it seems so....
twentieth century. First Word and then Outlook were much more popular, as I
recall. But even those seem to have faded away.

--
Jim
"Peter T" <peter_t@discussions wrote in message
...
| Just curiosity, have you or anyone actually ever received a malicious
virus
| in an Excel xls/a.
|
| A couple of years ago I searched the big name antivirus sites and the only
| mention I could find was documented last century, I think a file for XL95
|
| Regards,
| Peter T
|
| "Jim Rech"
| Back in the day when "macro viruses" were all the rage they sometimes
| propagated programming the VBE itself. I think the misanthropes have
| mostly
| moved to the web so not a great deal to worry about, and you can always
| disable all macros when opening a workbook.
|
| --
|
|