Clear the Immediate Window
Until now there has been no elegant way to clear the Immediate Window in Excel from code. Has this age-old problem finally been solved? According to OneDayWhen, it has. And I tested it successfully.
Paste the below code into a standard module and call the ClearImmediateWindow sub whenever you need to clear the Immediate Window. Nice work, Jamie.
‘<--- Excel Version --->
‘ Code in a standard module
Option Explicit
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_ACTIVATE As Long = &H6
Private Const WM_KEYDOWN As Long = &H100
Private Const VK_CONTROL As Long = &H11
Private Const KEYSTATE_KEYDOWN As Long = &H80
Private m_KeyboardState(0 To 255) As Byte
Private m_hSaveKeystate As Long
Sub ClearImmediateWindow()
Dim hChild As Long
Dim hParent As Long
Dim strCaptionVbe As String
Const CLASS_VBE As String = “wndclass_desked_gsk”
Const CLASS_IMMEDIATE As String = “VbaWindow”
Const CAPTION_IMMEDIATE As String = “Immediate”
‘ Get handle to Immediate Window
strCaptionVbe = Excel.Application.VBE.MainWindow.Caption
hParent = FindWindow(CLASS_VBE, strCaptionVbe)
hChild = FindWindowEx(hParent, ByVal 0&, _
CLASS_IMMEDIATE, CAPTION_IMMEDIATE)
If hChild = 0 Then
MsgBox “Immediate Window not found.”
Exit Sub
End If
‘ Activate Immediate Window
PostMessage hChild, WM_ACTIVATE, 1, 0&
‘ Simulate depressing of CTRL key
GetKeyboardState m_KeyboardState(0)
m_hSaveKeystate = m_KeyboardState(VK_CONTROL)
m_KeyboardState(VK_CONTROL) = KEYSTATE_KEYDOWN
SetKeyboardState m_KeyboardState(0)
DoEvents
‘ Send CTRL+A (select all) and Delete keystokes
PostMessage hChild, WM_KEYDOWN, vbKeyA, 0&
PostMessage hChild, WM_KEYDOWN, vbKeyDelete, 0&
‘ Schedule cleanup code to run
Application.OnTime Now + TimeSerial(0, 0, 0), “DoCleanUp”
End Sub
Sub DoCleanUp()
‘ Restore keyboard state
GetKeyboardState m_KeyboardState(0)
m_KeyboardState(VK_CONTROL) = m_hSaveKeystate
SetKeyboardState m_KeyboardState(0)
End Sub
‘
Hi Dick,
I tried the code and had some weird behaviour.
After writing to and then clearing the immediate window the function keys stopped working. No F5 or F9.
It also generates the “Can’t execute in Break mode” error if you insert a Break point, which sort of defeats the point of clearing the window as you can only clear it once and can not debug. Or maybe I’m doing something wrong.
FYI using XL2K on W2K
Andy: Okay, maybe I shouldn’t have said that I tested it. I get the same behavior. Also, after the “Can’t execute in break mode” error, I can’t Alt-F11 from Excel and it seems like the F8 key is stuck (extended selection) but there’s no EXT in the status bar. XL2k, Win98SE.
It’s probably not a good idea to enter break mode while this code is executing i.e. before the cleanup code has run. This is a common feature of low level code e.g. never try to step though code that subclasses a window’s messages!
You will have keyboard problems if the cleanup code does not run for any reason. I guess it will behave as if the CTRL key is stuck down.
I anticipated the code being used to clear the Immediate Window in advance of later using the Immediate Window in VBA code. For example,
Sub Main()
Debug.Print “Dirty window.”
ClearImmediateWindow
Application.OnTime Now + TimeSerial(0, 0, 0), “MainContinues”
End Sub
Sub MainContinues()
Debug.Print “This in a clear window.”
End Sub
After executing the above code, pressing F5 brings up a Goto dialog and F9 does nothing. I don’t usually use the F keys but they seem to me to be unaffected.
Any comments on how to reproduce unwanted behavior are most welcome here.
Thanks,
Jamie.
–
Jamie: The F5 and F9 references are in the VBE - to run and toggle breakpoint, respectively. Good guess on the Control key, that seems to be exactly what it’s doing.
Immediate Window????
???Dick?blog???????????????????VBA??????????
?????????????????Caption?English Version?Japanese Version????????????
?…
Sorry Dick, I mistook about these 3 same trackbacks!
Please delete 2 of 3. :p
Simply forgot to set the keyboardstate back to original (non-down) setting
m_KeyboardState(VK_CONTROL) = m_hSaveKeystate
SetKeyboardState m_KeyboardState(0)
Simply forgot to set the keyboardstate back to original (non-down) setting
m_KeyboardState(VK_CONTROL) = m_hSaveKeystate
SetKeyboardState m_KeyboardState(0)
I was just curious about this line:
Application.OnTime Now + TimeSerial(0, 0, 0), “DoCleanUp”
Is this not the same as just calling the DoCleanUp procedure?
Why use Application.OnTime if we’re doing it right now anyway?
Following adaptation works in all language versions AND searches a bit harder for the immediate window..
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
I’ve tried both versions published on this page and was unsuccesfull in getting the code to run properly. Even after modifiying the registry as prescribed in the second code listing. I am using excel 2003 French version. Any hint someone?
Greeting
I made a sample code using SendInput Function.
It works in Breake mode.
Can someone test if it works or not since my XP is Japanese version?
(Excel 2002 / XP Pro SP2)
http://blog.livedoor.jp/mokurin/archives/50219503.html
–
Option Explicit
Type INPUT_TYPE
dwType As Long
xi(0 To 23) As Byte
End Type
Type KEYBDINPUT
wVk As Integer
wScan As Integer
dwFlags As Long
time As Long
dwExtraInfo As Long
End Type
Declare Function SendInput Lib “user32″ _
(ByVal nInputs As Long, pInputs As INPUT_TYPE, _
ByVal cbSize As Long) As Long
Declare Sub CopyMemory Lib “kernel32″ Alias “RtlMoveMemory” _
(Destination As Any, Source As Any, ByVal Length As Long)
Const INPUT_KEYBOARD = 1
Const KEYEVENTF_KEYUP = &H2
Const VK_CONTROL = &H11
Sub proClearImmediate()
Dim inputevents(0 To 7) As INPUT_TYPE
Dim keyevent As KEYBDINPUT
Dim myArray As Variant
Dim n As Integer
‘Set key name to send
myArray = Array(VK_CONTROL, vbKeyG, vbKeyA, vbKeyDelete)
For n = 0 To UBound(myArray)
keyevent.wVk = myArray(n) ‘key name
keyevent.wScan = 0
keyevent.dwFlags = 0 ‘0 means press the key down
keyevent.time = 0
keyevent.dwExtraInfo = 0
inputevents(n).dwType = INPUT_KEYBOARD
CopyMemory inputevents(n).xi(0), keyevent, Len(keyevent)
Next
For n = 0 To UBound(myArray)
keyevent.wVk = myArray(n)
keyevent.wScan = 0
keyevent.dwFlags = KEYEVENTF_KEYUP ‘release the key
keyevent.time = 0
keyevent.dwExtraInfo = 0
inputevents(n + UBound(myArray) + 1).dwType = INPUT_KEYBOARD
CopyMemory inputevents(n + UBound(myArray) + 1).xi(0), keyevent, Len(keyevent)
Next
‘place the events into the stream
SendInput n + UBound(myArray) + 1, inputevents(0), Len(inputevents(0))
End Sub
this works in Excel 10
Sub ClearImmediateWindow()
Application.SendKeys “^g”, True
Application.SendKeys “^a”, True
Application.SendKeys “{DEL}”, True
End Sub
this works in excel 2002
Sub ClearImmediateWindow1()
Application.SendKeys “^g”, True
Application.SendKeys “^a”, True
Application.SendKeys “{DEL}”, True
End Sub
‘My proposition is:
Public Sub ClearImmediateWindow()
Application.SendKeys “^g^a{DEL}{F7}”, True
DoEvents
End Sub
‘{F7} - cursor return to Code Window
‘DoEvents - ending clearing ImmediateWindow before execution next code lines
Hello,
Found this thread with an internet search. Inspired me to write next code:
Sub ClearImmediateWindowNL()
‘Works on PC with Dutch version menu
Application.SendKeys “%d”, True
Application.SendKeys “d”, True
Application.SendKeys “^a”, True
Application.SendKeys “{DEL}”, True
End Sub
Sub ClearImmediateWindowUS()
‘Should work on PC with US version menu
Application.SendKeys “%g”, True
Application.SendKeys “a”, True
Application.SendKeys “^a”, True
Application.SendKeys “{DEL}”, True
End Sub
* Good Luck *
Can anyone explain why the Immediate window needs to be clear?
Why not just click anywhere in the window, press Ctrl-A and then Delete button?
Hi JP, the reason why you may wish to clear the immediate window via code is if you have several procedures or sections of code within a procedure that have DEBUG.PRINT lines. Personally I use several lines of debug.print within my code to take note of certain values so I can check that the procedure/function is working correctly. Sometimes you may only want to concentrate on what certain actions are doing so it would be handy to clear the immediate window at some point so you only see the latest actions.