immediately!

[LisaGreen]

Hi,

I've posted before on this subject but I thought maybe someone has a solution now.

Does anyone know of code.. very likely to be API based.. that does not use sendkeys and does not try to scroll information, to clear the immediate window *reliably* please?

TIA
Lisa

[DocAElstein]

Hi Lisa
This does use send keys. I know you asked for one that doesn’t use them. But this has so far always worked for me in a few Excel Versions.

But as a lot of people tell me that send keys is inherently unreliable, I would not like to say it’s reliable. I guess that’s why you are asking for a code that does not use them.
Thought it might be worth adding here anyway. - I came up with it after a lot of trial and error with other coding using send keys that were not always reliable for me
I have used it mostly on Office 2013 and lower
( You run Sub ImmediateWindowOpenClearCloseProgramatically() )

Alan

Code: Select all

 Sub ImmediateWindowOpenClearCloseProgramatically()
    If Application.VBE.MainWindow.Visible = False Then Application.SendKeys Keys:="%{F11}" ' VB Editor window  Alt+F11
 Application.SendKeys Keys:="^g" ' Immediate Window  Ctrl+g
 DoEvents: DoEvents
 Debug.Print "Hello," & vbCr & vbLf & "Hopefully this will all be cleared in a few seconds"
 MsgBox Prompt:="Hello, click OK , then after this procedure is finished I open the Immediate window which theoretically I should have already done"
 DoEvents: DoEvents
 Application.OnTime earliesttime:=Now + TimeValue("0:00:03"), procedure:="WipeMy_" ' Clear Immediate Window
 Application.OnTime earliesttime:=Now + TimeValue("0:00:06"), procedure:="Trash_t" ' Close windows
End Sub
Public Sub WipeMy_() ' Clear Immediate Window
 Application.SendKeys "^g ^a {DEL} " ' {HOME}"
End Sub
Public Sub Trash_t() ' Close Windows
 On Error Resume Next ' Add the "Name"  for your Excel language version
 Application.VBE.Windows("Immediate").Close ' English Excel
 Application.VBE.Windows("Direktbereich").Close ' German Excel
 On Error GoTo 0
 Let Application.VBE.MainWindow.Visible = False
End Sub

Ref
https://eileenslounge.com/viewtopic.php ... 21#p247121


_._____________________________

( If you just want to clear the window, not close it, then comment out the close line

Code: Select all

Sub ImmediateWindowOpenClearCloseProgramatically()
    If Application.VBE.MainWindow.Visible = False Then Application.SendKeys Keys:="%{F11}" ' VB Editor window  Alt+F11
 Application.SendKeys Keys:="^g" ' Immediate Window  Ctrl+g
 DoEvents: DoEvents
 Debug.Print "Hello," & vbCr & vbLf & "Hopefully this will all be cleared in a few seconds"
 MsgBox Prompt:="Hello, click OK , then after this procedure is finished I open the Immediate window which theoretically I should have already done"
 DoEvents: DoEvents
 Application.OnTime earliesttime:=Now + TimeValue("0:00:03"), procedure:="WipeMy_" ' Clear Immediate Window
 'Application.OnTime earliesttime:=Now + TimeValue("0:00:06"), procedure:="Trash_t" ' Close windows
End Sub

[ChrisGreaves]

Does anyone know of code.. very likely to be API based.. that does not use sendkeys and does not try to scroll information, to clear the immediate window *reliably* please?

Well, Lisa, this is your lucky day.
I have a solution. It is one-line of VBA:-

Code: Select all

Application.Quit (wdSaveChanges)

In twenty-five years stretching from Win95/Office 97 through to Win10/Office2003 that is the only guaranteed method I have heard of.

Depending on your overall goal, you may want to have loaded MSWord through a batch file so that the batch file reloads MSWord.
I have such a batch file.

The batch file logic is not difficult. Back when I was converting 000s of WordPerfect5.1 files per night, my Win95 boot sequence fired up both Word97 and Excel97.
Word97 examined its LOG file and from the file of document names, picked up one document later than the latest file named in the LOG file - that latest file was the one which, on being opened by Word97, hung waiting for a response for a non-standard message box.
Meanwhile, Excel97 was monitoring the Word97's LOG file, and after two (or maybe five) minutes of inactivity, Excel pulled, as we say in the trade, the plug, causing Win95 to reboot, ...

From your point of view all you need do is determine where your fail/restart points are and act accordingly.
Cheers
Chris

[DocAElstein]

That closes the Immediate Window by virtue of closing everything, doesn’t it, Chris. Or have I missed the point. I was assuming Lisa wanted to clear the Immediate Window , rather than clear immediately your word window..
(Immediate Window = that thing that comes up when you hit Ctrl+g from within the VB editor )
I may have missed the point. Wouldn't be the first time....

[HansV]

From Use VBA to Clear Immediate Window?:

Code: Select all

Private Declare PtrSafe _
            Function FindWindowA Lib "user32" ( _
                            ByVal lpClassName As String, _
                            ByVal lpWindowName As String _
                            ) As LongPtr
Private Declare PtrSafe _
            Function FindWindowExA Lib "user32" ( _
                            ByVal hWnd1 As LongPtr, _
                            ByVal hWnd2 As LongPtr, _
                            ByVal lpsz1 As String, _
                            ByVal lpsz2 As String _
                            ) As LongPtr
Private Declare PtrSafe _
            Function PostMessageA Lib "user32" ( _
                            ByVal hwnd As LongPtr, _
                            ByVal wMsg As Long, _
                            ByVal wParam As LongPtr, _
                            ByVal lParam As LongPtr _
                            ) As Long
Private Declare PtrSafe _
            Sub keybd_event Lib "user32" ( _
                            ByVal bVk As Byte, _
                            ByVal bScan As Byte, _
                            ByVal dwFlags As Long, _
                            ByVal dwExtraInfo As LongPtr)

Private Const WM_ACTIVATE As Long = &H6
Private Const KEYEVENTF_KEYUP = &H2
Private Const VK_CONTROL = &H11

Sub ClearImmediateWindow()
    Dim hwndVBE As LongPtr
    Dim hwndImmediate As LongPtr
    hwndVBE = FindWindowA("wndclass_desked_gsk", vbNullString)
    hwndImmediate = FindWindowExA(hwndVBE, ByVal 0&, "VbaWindow", "Immediate")
    PostMessageA hwndImmediate, WM_ACTIVATE, 1, 0&
    keybd_event VK_CONTROL, 0, 0, 0
    keybd_event vbKeyA, 0, 0, 0
    keybd_event vbKeyA, 0, KEYEVENTF_KEYUP, 0
    keybd_event VK_CONTROL, 0, KEYEVENTF_KEYUP, 0
    keybd_event vbKeyDelete, 0, 0, 0
    keybd_event vbKeyDelete, 0, KEYEVENTF_KEYUP, 0
End Sub

Warning: it works for me to clear the Immediate window, but Excel appears to become unstable after running the code.

[ChrisGreaves]

That closes the Immediate Window by virtue of closing everything, doesn’t it, Chris.

No, Alan, you are correct. It does clear the Immediate Window in a brutal way.
We were asked to "Clear the immediate Window".
Now I grant that it is reasonable to expect that the user really wants to simulate a human issuing Ctrl+G, assuming some automated process allowed them to intervene, and I would bet money on that!
That said, it is also reasonable to subscribe to the remote possibility that our human is automating some process, perhaps from a third-party application, that dumps useable data into that Immediate Window.
Supposing such an event happened apparently randomly, about once an hour.
Then we wouldn't object too much to a slight delay (2 minutes) while a system restarted itself, providing that the captured data was useful.

I was only trying to Think Outside The Immediate Window Box.

Cheers
Chris

[LisaGreen]

I love this place!! LOLOL!!!

Yes Hans. I got the same problem. I wonder if a multi method approach will work.

One of the solutions winds up reinstating the kb state after.. but does it with application ontime.

I have yet to go through all the snippets, but as I mentioned.. most of them are similar.. some to the point of being identical!

Lisa

[ChrisGreaves]

I love this place!! LOLOL!!!

I hate it. Here it is, 0145 my time, and I am now wide awake sitting up in bed without a coffee, responding to a thread about clearing a window when my windows are still clogged with snow from the mini-blizzard of two days ago.

I rather dislike responding to a question with "Why do you want to do that?", because I get asked it so many times. "I just DO" is not a good answer, but there is often a third -party (software or client) that imposes constraints on us.

So I'll ask.

"Does anyone know of code ..." Which suggests to me that you are looking for a programming solution to a specifically VBA problem. VBA because you are talking about the Immediate Window, which is a feature of Microsoft VBA, and so your programmable solution will have at least some initial part of it written in VBA, if only to call the API part, if it is necessary.
That of course, will lead us on to the question "why not clear the Immediate Window manually using the old fingers and Ctrl+G, which, as you have already guessed, leads on to the next question "Why do you want to clear the immediate Window when, as heralded in my first response in this thread, clearing of the window happens as soon as we shut down the MS application?"

"that does not use sendkeys ..." which suggests to me that you, like me, have found that SendKeys has not worked for you since you stopped installing Word2.0/6.0 and started using the new-fangled '97 with VBA. Which leads inexorably along the path of "Why a programmed solution when, if you are running unattended code, the Immediate Window contents will scroll off the top?". Of course the Immediate Window may be reporting some event every 10,000 records, in which case perhaps you want only to glance at "where it is up to" as you walk past to get another bushel of peas to shell, but if that is the case, then why not announce the status with Application.Status or Application.Caption? At least those two can be cleared without Sendkeys and without using an API application.

Although what is the point of clearing data if no-one is there to see it? And if no-one is there to see it, why display it in the first place? And if the automated "clear window" is taking place, how do you stop it clearig the windw just seconds before you glance at that window?

"and does not try to scroll information ..." ... why “scrolling the Immediate Window”? To the best of my knowledge, scrolling the immediate Window does not clear the window. I can scroll up and down manually using the PgUp and PgDn keys (and Ctrl+Home etc) but scrolling in no way clears the Immediate Window; it just shoves the mess up and down. Manually tapping the <Enter> key gives the Immediate Window the appearance of being cleared, since it pushes data (not information) out of view. From this I wander back into “Clearing the Immediate Window”, ...

"to clear the immediate window ..." ... which is tantamount to a privacy concern. I am an extreme case of non-privacy as far as the immediate Window goes - live alone (and now we know why “lol: “lol: “lol:) and so the contents of the Immediate Window can be viewed by no-one but me. The only thing I would NOT want myself to see is the computer’s reasoning behind its next move in chess, and I can avoid seeing that just by not opening the Immediate Window in the first place. I can imagine your client having the application set up in an open office, not wanting passers-by to witness this years management bonus figures, but then why have that Immediate Window open in the first place?

With eager breath, I await your resaonning.
Chris

[DocAElstein]

I love this place!! LOLOL!!!

Being occasionally able to usefully further a discussion is a refreshing change, from the norm of immediately being blocked, closed, chastised etc.. so quickly before that happens, Lol .. :) ..
I can think of some ideas of Why….. …..( why doing these sort of things to the Immediate Window programmatically ( I think the question Why?, is the jist of some of Chris’s comments , but remember I am very slow to get the point – my handicap, - no one else’s fault ) )
I have found the Immediate Window , (and necessarily to have the VB Editor open and lurking as well), in VBA code development and debugging a very useful and , IMO , very under-used tool.
If I was a teacher of VBA coding, its one of the first things I would beat into the head of my students with great physical violence if necerssary, along with using the macro recorder.
Along those same lines, the frustrations I have sometime felt in the reluctance of forum OPs to use the Immediate Window, is what led me to come up with the code snippet I offered. That has helped me sometimes to ram the Immediate Window down some OPs throat by including variations of it in a coding I passed on to them
So that’s why I, would welcome, any coding that reliable does those or other things to the Immediate Window.

Alan

[ChrisGreaves]

If I was a teacher of VBA coding, ...

Alan, if you were my teacher of VBA coding, or even programming, or even computers in general, my hand would shoot up so quickly that it would pull up the corner of one side of my mouth, and I would ask you why I needed to write code to clear what is, by its name, a debugging tool, to empty the debugging tool of what is supposed to hold for us, vital data about what, if anything, went wrong.

If I was a teacher of driving, ...

Alan, if you were my driving instructor, my hand would shoot (etc etc) "Why are we emptying the windshield washer tank at the first sign of an impending snow-storm?"

If I was a teacher of cooking, ...

Alan, if you were my cooking mentor (etc etc) "Why are we washing yesterday's mixing bowls before we measure out the flour, eggs" No. Wait! Hang about a minute!!

I will save everyone, me mainly, time here by posting a rebuttal in Alan's defense, using my first two days in Computing as an embarrassing example.
May 1967. My last year at Uni. Uni has issued a decree (no typo there) that every maths student must attend a 2-day course in computer programming. (Looking back I can't see why. What can you learn in two days about punched cards?) I sat through the two most miserable days of my years at Uni while two pimply graduates up front tried to convince 300 of us Engineers and Physicists that we should write a program in FORTRAN II to calculate e^x. I sat there fingering my 16-page pamphlet of Trig and Maths functions (one shilling and sixpence at my high school bookshop six years before) wondering why anyone would waste two days, programming a computer, when all you had to do was turn to page 14 and look it up.
Of course, we didn't actually run our little program (which today I couldn't write, but BobH probably can) because FORTRAN II jobs were submitted on punched cards, and there were only two punched card keyboards for 300 of us pimply undergraduates, and those two keyboards were operated full-time by the DP staff for the jobs that ran day and night, except for an interval between 2am and 6am that was eagerly snapped up by an extremely keen student a month or two later, but that's enough about me for now).

So, as a keen and eager student, let me ask Alan about this: Why do we need to write program code to clear the debugging window? If the answer is “as a programming exercise to prepare us for inspecting the debugging window” my follow-up comment would be that never in my life have I dismantled a carburetor.
With love, or at the very least, fondness

(signed) “Eagerly Curious” of Bonavista.

[DocAElstein]

...., let me ask Alan about this: Why do we need to write program code to clear the debugging window? .....never in my life have I dismantled a carburetor.....

I sometime did stuff that made no sense to me at the time, from instinct or madness, which I guess are similar characteristic: I had an old Yamaha RS125 DX stinky 2-stroke Motorbike I loved, after working all summer in a Pea-Picking field for a slave wage to buy it. I dismantled the carburettor and a few other things which actually seemed stupid, even to me, at the time. It saved my bacon later when I needed to do it quickly.
I don’t always know why I do things. On balance in the (very) long run it has proved to have been a very good idea.. Of those that really unkindly criticised me at the time, most aren’t around anymore, and they weren’t all older., That encourages me as time goes on…, and I do goes on as well… and often they don’t.. (I not meaning you, Chris, - all power to your undoubted long term resilience :) )

Clearing the VBA editor is not actually my favourite option, (getting it up and closing it was the main thing I was usually intersted in - at the time that seemd to be as troublesome with send keys as clearing it. That was originally why I came up with my code snipppet - to force an OP to see it/ be aware of it). I would prefer to be able to scroll around in it and add things to it programmatically. For the demonstration purposes that would suit me better. I agree it would be better to keep stuff there, but when doing a demo or trying to teach, I might prefer to take some stuff out of view temporarily.

[Peter T]

@Hans, The link you cited for your code also includes an earlier approach by Jamie Collins and KeepItCool which has always worked well for me. I could re-post it unwrapped and tidied up for VBA7 (also needs a DoEvents) but I don't think I have the privileges to post formatted code, or at least I can't see how to do it.

[HansV]

You can place [code] and [/code] tags around code. For example

[code]Sub Test()
    MsgBox "Hello World"
End Sub[/code]

becomes

Code: Select all

Sub Test()
    MsgBox "Hello World"
End Sub

[Peter T]

Ahha, simple as that, thank you Hans

Code: Select all

Option Explicit
' Adapted for VBA7 for use in Office 2010 or later by PeterT
' For 2007 or earlier delete all 'PtrSafe' and replace all 'LongPtr' with Long'
Private Declare PtrSafe Function GetWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal wCmd As Long) As LongPtr
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
Private Declare PtrSafe Function GetKeyboardState Lib "user32" (pbKeyState As Byte) As Long

Private Declare PtrSafe Function SetKeyboardState Lib "user32" (lppbKeyState As Byte) As Long
Private Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long

Private Const GW_HWNDNEXT As Long = 2
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 aka "OneDayWhen"
'http://www.dicks-blog.com/excel/2004/06/clear_the_immed.html  ' < link n/a  PeterT

    Dim hPane As LongPtr
    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&
    DoEvents ' helps if the cursor was left between lines, added by PeterT

    '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 LongPtr
'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 As LongPtr, lDock  As LongPtr, lPane As LongPtr


    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..'
        ' Excel 2010: Options / Trust Center / Trust Center Settings /
        '    Macro Settings / Trust Access to the VBA project  PT
    End If

    On Error GoTo errExit
    
    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, CLngPtr(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, GW_HWNDNEXT)
                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

errExit:
End Function

[LisaGreen]

Thank you Peter T!
What version of Excel/Word are you using please?

Does it also work if the cursor in the I/Window is above some lines? That is, in the middle of the output rather than at the bottom?

Lisa

Oops.. Mouth before brain.. just reread your code.. But the second Q still applies!

Lisa

[DocAElstein]

( You can get that n/a link from a few captures of it at http://web.archive.org/
Example:
http://web.archive.org/web/200408161929 ... immed.html
http://web.archive.org/web/200412101753 ... te-window/
http://web.archive.org/web/20040816192911/http://www.dicks-blog.com/excel/2004/06/clear_the_immed.html
http://web.archive.org/web/20041210175320/http://www.dicks-blog.com/archives/2004/06/09/clear-the-immediate-window/ )

[Peter T]

@ Lisa
#1 I've used this in just about all versions over the years, tested in 2016-64 before posting. I normally run it from a button on the VBE menu along with other related stuff.
#2 Good question, and why I added the DoEvents. Without it only text from the cursor to the top gets deleted, at least for me.

@ DocAElsten, Excellent, thanks for tracking down the original.

Peter T

[DocAElstein]

Yous welcome. That web archive is a bit hit and miss. Sometimes on one of their archived pages you can keep clicking on links on that page and keep going further and further… , it can be fantastic, like in a time machine. Other times you can’t find for love nor money what you want, and/or any links on an archived page don’t work and take you no further, so if they have more than one capture of a page, like
http://web.archive.org/web/2019*/http:/ ... immed.html
http://web.archive.org/web/2019*/http://www.dicks-blog.com/excel/2004/06/clear_the_immed.html
, then it’s often worth checking a few.

[LisaGreen]

Nice link Alan! I could get lost very easilly!

Lisa