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

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

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

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
|
|
|

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
|
|
|

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

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

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

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

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

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

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