Flushing the Keyboard Buffer

MorganO · Mar 26, 2007

Hello all.

I've run into a problem and I cannot resolve. I am designing an Excel Application in VBA. During the execution of the VBA code, the user will press different keys on the keyboard. These keystrokes are determined by using the GetAsyncKeyState Function. The problem happens when I exit from the VBA code and move back to a worksheet outside the code execution.

Lets say a user has pressed the 'Q' and 'X' key just prior to the final VBA subroutine running and releasing the worksheets back to the user. On the worksheet, in the selected cell will appear the 'Q' and 'X'. For some reason these keystrokes are not 'flushed' from the keyboard buffer prior to the code completion and are being transfered to the active worksheet. This is really annoying if I am testing the code in the VBA editor, because the spurious keystrokes will be placed in my VBA code, causing me to search for the out of place characters.

Any advice you could give on resolving this issue would be greatly appreciated!

Owen Morgan

Norie · Mar 26, 2007

Owen

Can we see the code?

And can you tell us what you are using GetAsyncKeyState for?

MorganO · Mar 26, 2007

Norie,

Showing the full code would be impossible here. Basically, I enable the function at the beginning of a module followed by a call to the function within a subroutine:

Code:

Declare Function GetAsyncKeyState Lib "user32.dll" (ByVal vKey As Long) As Long

Sub GameLoop()
... bunch of code here
If GetAsyncKeyState(VK_ENTER) <> 0 
... more code here
End Sub

One thing I have tried is assigning GetAsysncKeyState to a variable and then checking the value of the variable:

Code:

Sub GameLoop()
... bunch of code here
KeyStroke = GetAsyncKeyState(VK_ENTER)
If Keystroke <> 0 then...
... more code here
End Sub

This gives me the same problems.

I am using this function because I need to access the keyboard input to move on-screen graphics for a game I am developing. This is the only way I have found to accomplish this during the running of VBA code.

Thanks in advance for your help!

Norie · Mar 26, 2007

Well the only thing I can seem to find is the SetKeyboardState which is described thus in my Windows API guide.

The SetKeyboardState function copies a 256-byte array of keyboard key states into the calling thread’s keyboard-input state table.

I'm not sure if it'll actually clear the keyboard buffer but you might find this link interesting.

It appears that the OP was having a similar problem to yours.

Joe Was · Mar 26, 2007

I dont know if clearing the Office, Windows and Excel copy/paste history/clipboards will help but here is how you do it:

'Sheet module code
Private Declare Function apiOpenClipboard Lib "user32" _
Alias "OpenClipboard" _
(ByVal hwnd As Long) _
As Long

Private Declare Function apiEmptyClipboard Lib "user32" _
Alias "EmptyClipboard" _
() As Long

Private Declare Function apiCloseClipboard Lib "user32" _
Alias "CloseClipboard" _
() As Long

'Standard Module code
Private Declare Sub Sleep Lib "kernel32.dll" ( _
ByVal dwMilliseconds As Long)

Private Declare Function FindWindowEx Lib "user32.dll" _
Alias "FindWindowExA" (ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Private Declare Function PostMessage Lib "user32.dll" Alias _
"PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long

Private Const WM_LBUTTONDOWN As Long = &H201&
Private Const WM_LBUTTONUP As Long = &H202&

'creates a long variable out of two words
Private Function MakeLong(ByVal nLoWord As Integer, ByVal nHiWord As Integer) As Long
MakeLong = nHiWord * 65536 + nLoWord
End Function

Sub ClearOfficeClipboard()
Dim hMain&, hExcel2&, hClip&, hWindow&, hParent&
Dim lParameter&, sTask$

sTask = Application.CommandBars("Task Pane").NameLocal

'Handle for XLMAIN
hMain = Application.hwnd

'Find the OfficeClipboard Window
'2 methods as we're not sure if it's visible
'ONCE it has been made visible the windowclass is created
'and remains loaded for the duration of the instance
Do
hExcel2 = FindWindowEx(hMain, hExcel2, "EXCEL2", vbNullString)

hParent = hExcel2: hWindow = 0
hWindow = FindWindowEx(hParent, hWindow, "MsoCommandBar", sTask)
If hWindow Then
hParent = hWindow: hWindow = 0
hWindow = FindWindowEx(hParent, hWindow, "MsoWorkPane", vbNullString)

If hWindow Then
hParent = hWindow: hWindow = 0
hClip = FindWindowEx(hParent, hWindow, "bosa_sdm_XL9", vbNullString)
If hClip > 0 Then
Exit Do
End If
End If
End If
Loop While hExcel2 > 0

If hClip = 0 Then
hParent = hMain: hWindow = 0
hWindow = FindWindowEx(hParent, hWindow, "MsoWorkPane", vbNullString)
If hWindow Then
hParent = hWindow: hWindow = 0
hClip = FindWindowEx(hParent, hWindow, "bosa_sdm_XL9", vbNullString)
End If
End If

If hClip = 0 Then
ClipWindowForce
hParent = hMain: hWindow = 0
hWindow = FindWindowEx(hParent, hWindow, "MsoWorkPane", vbNullString)
If hWindow Then
hParent = hWindow: hWindow = 0
hClip = FindWindowEx(hParent, hWindow, "bosa_sdm_XL9", vbNullString)
End If
End If

If hClip = 0 Then
MsgBox "Cant find Clipboard window"
Exit Sub
End If

lParameter = MakeLong(120, 18)
Call PostMessage(hClip, WM_LBUTTONDOWN, 0&, lParameter)
Call PostMessage(hClip, WM_LBUTTONUP, 0&, lParameter)
Sleep 100
DoEvents

End Sub

Sub ClipWindowForce()
Dim octl

With Application.CommandBars("Task Pane")
If Not .Visible Then
Application.ScreenUpdating = False

Set octl = Application.CommandBars(1).FindControl(ID:=809, recursive:=True)

If Not octl Is Nothing Then octl.Execute
.Visible = False
Application.ScreenUpdating = True
End If
End With
End Sub

Sub myClr()
Call ClearOfficeClipboard
apiOpenClipboard(0)
apiEmptyClipboard
apiCloseClipboard
Application.CutCopyMode=False
End Sub

Note: the toolbar ID changes with each version of Excel so you may need to run code to test for the right ID first for:

Set octl = Application.CommandBars(1).FindControl(ID:=809, recursive:=True)

MorganO · Mar 27, 2007

Thank you gentlemen!

I will give these a try and see if it works.

Cheers.

Owen

MorganO · Mar 28, 2007

Ok, I've followed the research trails provide above, but have been unable to solve this problem to my full satisfaction.

From further testing I've done, I've found that during VBA execution any keys pressed on the keyboard are held in some 'buffer' and will be output to the location where the cursor was located just prior to the execution of the VBA code. I have been able to affect this somewhat using the 'DoActions' command, but the Keystrokes STILL must output to somewhere.

If a Userform/Msgbox (with no active input boxes) is activated, any keystrokes pressed prior to the activation are effectively deleted and do not output to the last cursor position. It seems that the userform/msgbox act as a 'Null' form, in effect sending the keystrokes to the null device.

So, what I've decided to do is pop up an informational userform when my VBA code ends to 'null out' the keystrokes. It's not quite as elegant as I was hoping for, but it does fix the problem.

Just wanted to let anyone else who runs into this problem know of my solution.

Take care.

Owen

Joe Was · Mar 29, 2007

This may help?
It is the code to use a self-closing Script MsgBox in VBA for Excel. You may try using it with say a 2 second delay and see if this traps the null for you?
See link:

http://www.mrexcel.com/board2/viewtopic.php?t=179951

MorganO · Apr 17, 2007

Follow up: After much gnashing of the teeth and internet research I have found a way to flush the keyboard buffer. It is through the use of a windows API called: Peekmessage

Here is the code I've found to do this:

Code:

'Type to hold the coordinates of the mouse pointer
Private Type POINTAPI
  x As Long
  y As Long
End Type
'Type to hold the Windows message information
Private Type MSG
  hWnd As Long
 'the window handle of the app
  message As Long
 'the type of message (e.g. keydown)
  wParam As Long
 'the key code
  lParam As Long
  'not used
  time As Long
  'time when message posted
  pt As POINTAPI
  'coordinate of mouse pointer
End Type
  'Look in the message buffer for a message

Private Declare Function PeekMessage Lib "user32" Alias "PeekMessageA" (ByRef lpMsg As MSG, ByVal hWnd As Long, ByVal wMsgFilterMin As Long, ByVal wMsgFilterMax As Long, ByVal wRemoveMsg As Long) As Long
 'Translate the message from a key code to a ASCII code
Private Declare Function TranslateMessage Lib "user32" (ByRef lpMsg As MSG) As Long
 'Windows API constants
Private Const WM_CHAR As Long = &H102
Private Const WM_KEYDOWN As Long = &H100
Private Const PM_REMOVE As Long = &H1
Private Const PM_NOYIELD As Long = &H2

 'Check for a key press
Public Function CheckKeyboardBuffer() As String
 'Dimension variables
 Dim msgMessage As MSG
 Dim hWnd As Long
 Dim lResult As Long
 'Get the window handle of this application
 hWnd = ApphWnd
 'See if there are any "Key down" messages
 lResult = PeekMessage(msgMessage, hWnd, WM_KEYDOWN, WM_KEYDOWN, PM_REMOVE + PM_NOYIELD)
 'If so ...
 If lResult <> 0 Then
 '... translate the key-down code to a character code,
 'which gets put back in the message queue as a WM_CHAR
 'message ...
 lResult = TranslateMessage(msgMessage)
 '... and retrieve that WM_CHAR message
 lResult = PeekMessage(msgMessage, hWnd, WM_CHAR, WM_CHAR, PM_REMOVE + PM_NOYIELD)
 'Return the character of the key pressed,
 'ignoring shift and control characters
 CheckKeyboardBuffer = msgMessage.wParam
  End If
End Function

To flush the buffer I just call the function CheckKeyboardBuffer():

Code:

Flushkey = CheckKeyboardBuffer()

before I exit my code. This works because the 'PM_REMOVE ' parameter in the PeekMessage Function removes the last character from the keyboard buffer of the currently active window (in my case Excel).

Take Care.

Owen

Greg Truby · Apr 17, 2007

An interesting thread to be sure and I shall make note of it so that should I ever need to flush the keyboard buffer, I'll have a reference. But to handle this, could you not, upon exiting the main gameplay loop, have simply set some kind of global boolean booCleanUpOnAisle7Verne or something, then select some dead cell and let Excel dump the keystrokes into it? Then use the WS/WB Change Event handler to check the boolean, toggle events, .ClearContents and reset the boolean? I'll freely admit that I have a bad habit of putting blinders on myself and getting wrapped up in having to solve a problem in a particular way myself. But would this not have worked?

Flushing the Keyboard Buffer

MorganO

Active Member

Excel Facts

Norie

Well-known Member

MorganO

Active Member

Norie

Well-known Member

Joe Was

MrExcel MVP

MorganO

Active Member

MorganO

Active Member

Joe Was

MrExcel MVP

MorganO

Active Member

Greg Truby

MrExcel MVP

Similar threads

Forum statistics

Share this page

Flushing the Keyboard Buffer

Active Member

Excel Facts

Well-known Member

Active Member

Well-known Member

MrExcel MVP

Active Member

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock