Use VBA to Clear Immediate Window?
Solution 1
Below is a solution from here
Sub stance()
Dim x As Long
For x = 1 To 10
Debug.Print x
Next
Debug.Print Now
Application.SendKeys "^g ^a {DEL}"
End Sub
Solution 2
or even more simple
Sub clearDebugConsole()
For i = 0 To 100
Debug.Print ""
Next i
End Sub
Solution 3
SendKeys is straight, but you may dislike it (e.g. it opens the Immediate window if it was closed, and moves the focus).
The WinAPI + VBE way is really elaborate, but you may wish not to grant VBA access to VBE (might even be your company group policy not to).
Instead of clearing you can flush its content (or part of it...) away with blanks:
Debug.Print String(65535, vbCr)
Unfortunately, this only works if the caret position is at the end of the Immediate window (string is inserted, not appended). If you only post content via Debug.Print and don't use the window interactively, this will do the job. If you actively use the window and occasionally navigate to within the content, this does not help a lot.
Solution 4
Much harder to do that I'd envisaged. I found an version here by keepitcool that avoids the dreaded Sendkeys
Run this from a regular module.
Updated as initial post missed the Private Function Declarations - poor copy and paste job by yours truly
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
Solution 5
Here is a combination of ideas (tested with excel vba 2007) :
' * (this can replace your day to day calling to debug)
Public Sub MyDebug(sPrintStr As String, Optional bClear As Boolean = False)
If bClear = True Then
Application.SendKeys "^g^{END}", True
DoEvents ' !!! DoEvents is VERY IMPORTANT here !!!
Debug.Print String(30, vbCrLf)
End If
Debug.Print sPrintStr
End Sub
I do not like deleting the Immediate content (fear of deleting the code by accident, so the above is a hack on some of the code you all wrote.
This handles the problem Akos Groller writes about above: "Unfortunately, this only works if the caret position is at the end of the Immediate window"
The code opens the Immediate window (or puts the focus on it), sends a CTRL+END, followed by a flood of newlines, so the previous debug content is not in sight.
Please note, that DoEvents is crucial, otherwise the logic would fail (the caret position would not move in time to the end of the Immediate window).
![Alpha](https://i.stack.imgur.com/I1nX2.png?s=256&g=1)
Alpha
Updated on July 05, 2022Comments
-
Alpha almost 2 years
Does anyone know how to clear the immediate window using VBA?
While I can always clear it myself manually, I am curious if there is a way to do this programmatically.
-
Alpha about 12 yearsThanks~~ It's much harder than I expected though :D
-
Blaz Brencic about 12 yearsI'm not very fond of VBA so I prefer shorter solutions. :) I'm glad this helps you although question was posted few months ago. :)
-
PowerUser over 11 yearsOdd. It looks simple enough. But when I run this from Access 2007, it's turning my NumLock off. Anyone know why?
-
toxicate20 over 11 yearswhy would you dislike sendkeys?
-
Akos over 11 yearsIn theory, things can happen at the user interface between selecting the window and posting keys to it. So the message may deliver somewhere else. More realistic, you don't get errors if you targeted at a different application or version and the keys can do something completely different. Of course, as shorthand it's OK (instead of you pressing Ctrl-g Ctrl-a Del, why could VBA not do it?), but I would not deploy something to users with SendKeys if I can avoid it.
-
Stephan over 11 yearsnice idea +1, but the declaration of the Function GetWindow and GetKeyboardState is missing -1 :)
-
DangerMouse over 11 yearsDoesn't work when used from code as expected since the clearing commands are queued and only happen after the calling code finishes.
-
onedaywhen almost 11 yearsBlast from the past: this is code I wrote a decade or more ago!
-
brettdj almost 11 years@onedaywhen you are KeepitCool? :). The other day I answered a question here on multiple goal seek code - that I wrote a decade ago on aother forum. Getting older quickly :)
-
Admin almost 11 yearsThe only problem with the code is that you can re-print to the immediate window after execution. Nothing will show
-
Cool Blue over 9 yearsJust for the record: SENDKeys changes keyboard settings such as NUMLOCK for example... its annoying to say the least
-
Brian Burns almost 9 yearsThis happened to me - the ctrl+a delete wound up going to a code module window somehow and wiped out my code, which I didn't realize until it was too late to restore it. I don't recommend the SendKeys approach at all.
-
jaysoncopes over 8 yearsOnly problem with this solution is that
Application.SendKeys
seems to be very unpredictable. For instance, when using this in a different subprocedure, the initialization of my Userform will be effected (as weird as that sounds). While this is shorter, it sounds a little risky. -
Brian Powell over 8 yearsI understand the other solutions are a lot more thorough, but I'm not sure why you were downvoted. This is the solution that worked for me - I just literally needed to clear it out, and putting a few blanks in worked great. Thanks!
-
Austin almost 8 yearsFor those wondering, the shortcut keys are
Ctrl+G
(to activate the Immediate window), thenCtrl+A
(to select everything), thenDel
(to clear it). -
spinjector almost 8 yearsA word of caution when using Microsoft Visual Basic for Applications Extensibility (VBE): some malware recognition systems will flag it as malware. The reason being it can be used for malicious purposes, for instance to spread malware through DOC, XLS, and any file type that uses VBA. I recently tried to send an XLS file with VBE in it, and GMAIL flagged it as malware and wouldn't let me send it. So I put it in a password-protected ZIP file, and found those aren't allowed either. But I should add McAffee and Kaspersky is ok with it, I've used both and they do not flag my XLS files with VBE.
-
J. Chomel almost 8 yearsHi Mike! Your answer clearly is worth a little more explanation. Kindly refer to stackoverflow.com/help/how-to-answer .
-
nateAtwork over 6 yearsWow, 'Application.OnTime' is very powerful. I didn't know VBA could accept higher order functions!
-
ashleedawg over 6 yearsIt's [annoyingly] always the smallest coding things I'm trying to do that end up taking me the longest & frustrating me most -- Like searching Repeatedly, for Years, for This Exact Answer.......and you're gonna tell me it's been sitting right here for six years?? *sigh*
-
hornetbzz over 6 yearsDirty but Funny way at least :-)
-
zhekaus over 6 yearsThere is no SendKeys method in Application in MS Access 2016. You should run simply SendKeys ...
-
zhekaus over 6 yearsI discourage to use this method. VBE is a weird thingy and sometimes it tries to clear the code (or something else) instead of the Immediate.
-
Jay Killeen over 5 yearsYeah don't try to F8 step through this code... assuming you are running it within a module, the Immediate window will not SetFocus and all you will do is delete all code in your module window. Gotta just trust it works and call it without stepping.
-
Jay Killeen over 5 yearsTry changing the second line to
If Application.VBE.ActiveWindow.Caption = "Immediate" Then Application.SendKeys "^a {DEL} {HOME}"
-
Jay Killeen over 5 yearsOr better yet...
Application.VBE.ActiveWindow.Caption = "Immediate" And Application.VBE.ActiveWindow.Visible
-
Jay Killeen over 5 yearsTry using something like
If Application.VBE.ActiveWindow.Caption = "Immediate" And Application.VBE.ActiveWindow.Visible Then Application.SendKeys "^a {DEL} {HOME}"
to reduce unpredictability. -
K.Dᴀᴠɪs over 5 yearsHad to go to the
Trust Center
and enable the checkbox: Trust access to the VBA project object model. If you all don't see me again, it's because some malicious code took over my PC and I threw it off a cliff. But nice solution xD -
jbobbins almost 5 yearsNot good...this cleared all the code in my module! (Excel 2010)
-
Karthick Ganesan almost 4 years@AustinD - However, as per this article,
+
seems to belong toCtrl
and^
corresponds toShift
. So,.SendKeys
and.OnKeys
map keycodes differently? -
hennep over 3 yearsClearImmediateWindow called from a button click event works fine. When I call it before a lengthy operation, the contents of the immediate window are only selected, not deleted. DoEvents does not make any difference. Any idea why the delete key does not work in that particular case?
-
ProfoundlyOblivious over 3 years@hennep The immediatee window might be losing focus before the keypress event is processed. If so, then adding DoEvents before a keypress will be ineffective; try adding it as the last line in the Sub. If that doesn't help then i would try replacing it with a small delay of 10ms (as the last line)
-
hennep over 3 yearsNeither of the two options work and the the immediate window still has the focus when the procedure (open excel and read cells from a worksheet) is finished.
-
ProfoundlyOblivious over 3 years@hennep I do not why your code fails, but I may be able to figure it out if you create a new question with a minimal reproduceable example and post a link to it in these comments.
-
DSlomer64 over 3 yearsCtrl-Z restores! (Happened to me a dozen times while trying to execute some of the above code!!)
-
DSlomer64 over 3 yearsWorked for me, too, Excel 16, Win10.
-
DSlomer64 over 3 yearsThe line with "next..." winds up being in the Immediate window after execution ends (unless this is the point of "next...", to suggest "carrying on" is what's "next"). Of course, commenting out the line with "next..." delivers a cleared Immediate window. And so does putting ClrImmediate AFTER the line with "next...". I would also put .SetFocus at end of "With" line and lose the word "With" and the line "End With".
-
DSlomer64 over 3 yearsBut I'd still add
If Application.VBE.ActiveWindow.Caption = "Immediate" Then _
beforeSendKeys
, just in case. -
john ktejik over 2 yearsThis deleted all my code lol