clear intermediate window automatically

Steven Deng

unread,

Oct 31, 2004, 2:16:52 AM10/31/04

to

Hello,

I tried a couple methods posted on the internet, yet to find a working one.
If you have something working, can you share? Thank you!

Sincerely,
Steven Deng

Bob Phillips

unread,

Oct 31, 2004, 4:12:14 AM10/31/04

to

Steven,

Manually - Ctrl-A in the immediate window and then delete

Auto - get a copy of MZ-Tools a free VBE add-in.

--

HTH

RP

"Steven Deng" <zhen...@gmail.com> wrote in message
news:Ej0hd.70976$nl.47494@pd7tw3no...

keepITcool

unread,

Oct 31, 2004, 5:05:57 AM10/31/04

to

You'll find a working code here..
http://www.dicks-blog.com/excel/2004/06/clear_the_immed.html

I remember that i made some mods on it.
it didnt always work depending on docking position
and international settings.

I'll find my mods or recreate 'm.
Be back later.

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

"Steven Deng" <zhen...@gmail.com> wrote :

keepITcool

unread,

Oct 31, 2004, 8:31:14 AM10/31/04

to

"Steven Deng" <zhen...@gmail.com> wrote :

et voila!

I've adapted Jamie Collins original as found on Dick Kuslieka's Blogs...

1) It searches a bit harder for the immediate pane

2) It can be used in 'NonEnglish' Excel
No longer hardcoded for "Immediate" and CtrlA.

It does require "Access to the Visual Basic Project"
but so does Jamie's original :)

Have fun!

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

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

Steven Deng

unread,

Oct 31, 2004, 11:45:06 AM10/31/04

to

"Bob Phillips" <bob.ph...@notheretiscali.co.uk>
OzGh4my...@TK2MSFTNGP15.phx.gbl...

> Steven,
>
> Manually - Ctrl-A in the immediate window and then delete
>
> Auto - get a copy of MZ-Tools a free VBE add-in.
>

Thanks Bob. MZ-Tools is a very cool tool, and I have installed it. I am
wondering if I can call the "clear immdiate window" from my code using this
addin. I should try to find the function interface of the MZ-Tools.

Sincerely,
Steven Deng

Steven Deng

unread,

Oct 31, 2004, 12:07:20 PM10/31/04

to

Hello,

Thank you for the reply! It does work!

Sincerely,

Steven Deng

Jim May

unread,

Oct 31, 2004, 1:05:10 PM10/31/04

to

Would one paste this code into their Personal.xls file so that it is always
available?
TIA,

"keepITcool" <xrrcv...@puryyb.ay> wrote in message
news:Xns959393C55F7...@207.46.248.16...

keepITcool

unread,

Oct 31, 2004, 4:30:15 PM10/31/04

to

of course you could.

Two indispensable (free) VBE utilities worth downloading:

Smart Indenter. (Bullen)
Keeps your code and indenting nice and tidy.

MZtools 3.0 for VBA. (Quintero)
(MZ tools also has an Immediate WindowCleaner.
however you cant use it on a floating pane!

But it has dozens of other smart things...

It does not expose any functionality you might
use to automate.

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

"Jim May" <jm...@cox.net> wrote :

Jim May

unread,

Oct 31, 2004, 4:40:13 PM10/31/04

to

I asked because everytime I open Excel (XP) and in the VBE Ctrl G the window
contains:

[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs] >
[PickPlatform] <
[PickPlatform] >
[VerifyOpen] <
[VerifyOpen] > 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs] >
[auto_open] >

If I cut and paste your furmished procedures above into say (a new) module2
of my Personal.xls -- What remaining steps should I perform (and how often)
to rid the above? Sorry, but I'm a near novice here...
TIA,

"keepITcool" <xrrcv...@puryyb.ay> wrote in message

news:Xns9593E4FC0D2...@207.46.248.16...

keepITcool

unread,

Oct 31, 2004, 4:55:32 PM10/31/04

to

That code comes from Analysis Toolpak VBA, where
they 'forgot' to disable the debug print lines..

Many people dont use that addin at all. (ATPVBAEN.XLA)
If you dont automate Toolpak functions in VBA DISable it,
by removing it from the addin list.

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

"Jim May" <jm...@cox.net> wrote :

> I asked because everytime I open Excel (XP) and in the VBE Ctrl G the

Bob Phillips

unread,

Oct 31, 2004, 4:56:49 PM10/31/04

to

You could do, but remember that you will want to run it from the VB IDE, so
probably best to create a toolbar button, as MZ-Tools does. Here is how to
do it

First, insert a class module with this code

Private WithEvents CbE As CommandBarEvents

Private Sub CbE_Click(ByVal CommandBarControl As Object, _
Handled As Boolean, _
CancelDefault As Boolean)

Select Case CommandBarControl.Caption
Case "Clear Immediate Window": ClearImmediateWindow
End Select

End Sub

Sub AddNewMenuItem()
Dim oCB As CommandBar
Dim oCbCtl As CommandBarControl

Set oCB = Application.VBE.CommandBars("Standard")
Set oCbCtl = oCB.Controls.Add(temporary:=True)
With oCbCtl
.Caption = "Clear Immediate Window"
.Style = msoButtonCaption
End With
Set CbE = Application.VBE.Events.CommandBarEvents(oCbCtl)

End Sub

and then put this code in a standard module to load the commandbar

Public cCbE As clsCbE

Sub AddVBECommandbars()
Set cCbE = New clsCbE
cCbE.AddNewMenuItem
End Sub

Call AddVBECommandbars from you Workbok_Open

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Jim May" <jm...@cox.net> wrote in message
news:2P9hd.195680$a85.167619@fed1read04...

keepITcool

unread,

Oct 31, 2004, 5:56:24 PM10/31/04

to

Bob, great idea..

and he could even do without the classmodule...and do it all in
ThisWorkbook.(as it is an object module THUS a class module it allows
withevents objects.

I looked up the faceID for an Eraser icon, and minimalized your code
to following:

'<<Thisworkbook module>>

Private WithEvents CbE As CommandBarEvents

Sub VBEMenu()
With Application.VBE.CommandBars("Standard")
On Error Resume Next
.FindControl(Tag:="ImmClear").Delete
On Error GoTo 0

With .Controls.Add(temporary:=True)
.Tag = "ImmClear"

.Caption = "Clear Immediate Window"

.Style = msoButtonIcon
.FaceId = 1964
Set CbE = Application.VBE.Events.CommandBarEvents(.Control)
End With
End With
End Sub

Private Sub CbE_Click(ByVal CommandBarControl As Object, _

Handled As Boolean, CancelDefault As Boolean)
Select Case LCase$(CommandBarControl.Tag)
Case "immclear": ClearImmediateWindow
End Select
End Sub

Private Sub Workbook_Open()
VBEMenu
End Sub

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

"Bob Phillips" <bob.ph...@notheretiscali.co.uk> wrote :

Bob Phillips

unread,

Oct 31, 2004, 7:15:27 PM10/31/04

to

> and he could even do without the classmodule...and do it all in
> ThisWorkbook.(as it is an object module THUS a class module it allows
> withevents objects.

True, and a nice idea.

> I looked up the faceID for an Eraser icon, and minimalized your code
> to following:

That occurred to me, couldn't be bothered to look though<vbg>

Bob

Jamie Collins

unread,

Nov 1, 2004, 3:34:09 AM11/1/04

to

keepITcool wrote ...

> I've adapted Jamie Collins original as found on Dick Kuslieka's Blogs...
>
> 1) It searches a bit harder for the immediate pane
>
> 2) It can be used in 'NonEnglish' Excel
> No longer hardcoded for "Immediate" and CtrlA.
>

Nice work. I'm using your amendments now!

Jamie.

--