Clear the Immediate Window

Posted on by Dick Kusleika

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.

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

    ?…

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

    m_KeyboardState(VK_CONTROL) = m_hSaveKeystate
    SetKeyboardState m_KeyboardState(0)

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

    m_KeyboardState(VK_CONTROL) = m_hSaveKeystate
    SetKeyboardState m_KeyboardState(0)

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

  10. Following adaptation works in all language versions AND searches a bit harder for the immediate window..

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

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

  13. this works in Excel 10

    Sub ClearImmediateWindow()

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

    End Sub

  14. this works in excel 2002

    Sub ClearImmediateWindow1()

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

    End Sub

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

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

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

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

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

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

  22. Pingback: Limpar a Janela imediata do VBE no Access « Access por exemplo

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

  24. The given code doesn’t work, could you please update the post with code that works? And if tested successfully, could you please specify with which Excel version?

  25. In Excel 2010, it made Excel “Not Responding”. I’ve cleaned up the HTML in the main post and in keepITcool’s comment, but honestly I don’t recommend you attempt to clear the IW programmatically. Do it manually or print to a file instead of the IW.

  26. These codes are working quite well for me. ( I may need in addition sometimes to add some double DoEvents in other places such as before or after message boxes or shapes, but usually I can come up with some combination that works consistently )

    Sub ImmediateImediate()
Application.SendKeys Keys:="^g"
Debug.Print "Don't blink, or you will miss this!.. ( hopefully :-) )"
Application.OnTime earliesttime:=Now + TimeValue("0:00:01"), procedure:="WipeMy_"
Application.OnTime earliesttime:=Now + TimeValue("0:00:02"), procedure:="Trash_t"
End Sub
Public Sub Trash_t()
 On Error Resume Next
 Application.VBE.Windows("Immediate").Close ' English Excel
 Application.VBE.Windows("Direktbereich").Close ' German Excel
End Sub
Public Sub WipeMy_()
 Application.SendKeys "^g ^a {DEL} "
End Sub

    
Alan
  27. Pingback: Zoom sur la fenêtre exécution en VBA - XLérateur

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.