Get key presses while VBA code is running using PeekMessage and TranslateMessage

This post is one of a series providing implementation examples of Windows API Functions, Types, Enums and Consts using VBA. The code in this post can be used as-is, however, if you regularly (or even just occasionally) work with Windows API declarations in VBA, you may want to see the posts Automatically add Windows API declaration(s) and Using 'F1' to view Windows API web pages which explain some of the functionality that can be added to the VBE by VBE_Extras.

The PeekMessage Windows API Function retrieves windows messages from a message queue (optionally removing them or leaving them in the message queue). It has parameters that allow the calling code to define:

• The specific window for which the message queue is to be 'peeked' (hWnd)

• The type of message to be filtered such that only specific types of message are checked (wMsgFilterMin and wMsgFilterMax)

• What is done with the message, if a message is found ... either remove it from the queue or leave it in the queue (wRemoveMsg)

Additionally, PeekMessage has an 'out' parameter (lpMsg) which is a pointer to a MSG Type ('out' doesn't exist in VBA but it is similar to ByRef ... essentially, it allows PeekMessage to update the parameter and effectively return a value) which is updated with information about the message if one is found that meets the criteria supplied in the other parameters.

What are these 'messages'? Windows (the OS) uses messages to tell windows (ie the individual windows on your screen) to perform certain actions. They can contain all sorts of information but the ones we are specifically interested in for this example are keyboard messages. When a key is pressed, the OS sends a message to the active window to tell it that the key has been pressed. And, in this example, that's what we want to know. To learn more about windows messages and the types of messages that exist, see About Messages and Message Queues.

The TranslateMessage Windows API Function translates virtual-key messages into character messages (and posts that message back to the window's message queue). For this example, it means we can use the VBA Chr() Function to get the character relating to the key press from the MSG Type. TranslateMessage has only 1 parameter which is a pointer to a MSG Type (lpMsg) ... handily (and on purpose!) matching the parameter of PeekMessage with the same name.

So here is some code to demonstrate this ...

The core code that uses the two Windows API Functions is in TryGetKeyPress. It is documented to explain what is happening, but to briefly summarise:

• It 'peeks' once to look for a key press message in the host application's window message queue

• If such a message is found then it is removed from the message queue, translated into a character message and put back into the message queue

• It then 'peeks' for a second time to look for the character message and, if found, returns it

Note that the code, as written, will work in Excel. You will need to adjust the Application.hWnd line for other applications ... Application.hWndAccessApp for Access, Application.ActiveWindow.Hwnd for Word or see my Get the application window handle in any app (well ... in Excel, Word, Access, PowerPoint or Outlook) post.

You can run the TestKeyPresses Sub to test this code. This does nothing for 5 seconds unless the space bar is pressed in which case it exits early and shows a MsgBox.

The TestKeyPresses Sub must be run from the host-application user interface, not from the VBE (see the header comments of the Sub for why). In Excel, Word, PowerPoint and Outlook you can use Alt+F8 and then select the Sub. In Access, I guess you can run it via a button on a Form or via a Macro (having made TestKeyPresses a Function instead of a Sub).