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

Posted in Uncategorized

23 thoughts on “Clear the Immediate Window

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

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

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

    –

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

  5. Immediate Window????
    ???Dick?blog???????????????????VBA??????????
    ?????????????????Caption?English Version?Japanese Version????????????

    ?…

  6. Simply forgot to set the keyboardstate back to original (non-down) setting

    m_KeyboardState(VK_CONTROL) = m_hSaveKeystate
    SetKeyboardState m_KeyboardState(0)

  7. Simply forgot to set the keyboardstate back to original (non-down) setting

    m_KeyboardState(VK_CONTROL) = m_hSaveKeystate
    SetKeyboardState m_KeyboardState(0)

  8. 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?

  9. 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)
    ‘ HKLMSOFTWAREMicrosoftOffice11.0ExcelSecurity
    ‘ 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

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

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

  12. this works in Excel 10

    Sub ClearImmediateWindow()

    Application.SendKeys “^g”, True
    Application.SendKeys “^a”, True
    Application.SendKeys “{DEL}”, True

    End Sub

  13. this works in excel 2002

    Sub ClearImmediateWindow1()

    Application.SendKeys “^g”, True
    Application.SendKeys “^a”, True
    Application.SendKeys “{DEL}”, True

    End Sub

  14. ‘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

  15. 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 *

  16. 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?

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

  18. While this doesn’t actually “clear” the Immediate Window in the way one might want, it does produce an equivalent looking result which should be sufficient for most purposes…

    Sub ClearImmediateWindow()
    Debug.Print String(200, 10)
    End Sub

  19. Putting it all together. The cursor back to the top of the immediate window ! Works fine in Access 2000

    Sub ClearImmediateWindow()
    Debug.Print String(200, 10)
    SendKeys “^g ^{HOME} {F7}”
    End Sub

  20. After testing the code placed in the comment, I realized that Immediate Window was not really clean. While there were many lines in the Immediate Window, only the top lines were excluded.

    The code below seems to solve the problem.

    Sub ClearImmediateWindow()
    SendKeys “^g ^{HOME}”
    DoEvents
    SendKeys “+^{END}”
    DoEvents
    SendKeys “{DEL}”
    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *