XL5: How to Manipulate Macro Operation With Key Strokes

• Microsoft Excel for Windows, version 5.0

SUMMARY

In Microsoft Excel, you can create a macro that performs different operations depending on which keys you press when you run the macro (for example, if you want your macro to perform one operation when you press the CTRL key and another if you don't press the CTRL key).

To control the operation of a macro, you can use one key (for example the CTRL key) or a combination of keys (for example CTRL+ALT). Your macro will test for the state of these keys (pressed or not pressed) to determine which action to take.

MORE INFORMATION

To create a macro that responds to different key states, use an IF function. The IF function allows you to control your macro based on the results of calls it makes to the Windows environment. These calls to the Windows environment allow the IF function to identify the state of the keys you want to use. To return the key state of one or more keys, use the Windows function, GetKeyState().

You can use the above procedure to change the operation of macros that are already assigned to objects, buttons, menu commands, or hot keys.

NOTE: Making this kind of change will not affect the normal behavior of the object to which the macro is assigned.

You can use any keys as long as they are not either of the following:

• The same keys that are used to start the macro.

  -or-

• Keys that change how Microsoft Excel interacts with the object to which your macro is assigned.

The GetKeyState() Function

The GetKeyState() function returns a number indicating the current state of a specific key at the time the DLL call is made. A negative number indicates that the key is pressed, a positive number indicates that the key is not pressed.

The following GetKeyState() Visual Basic code is an example that returns the current state of a key and takes action based on the key state returned.

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

WARNING: The Declare Function statements listed in the following macro are sensitive. If used incorrectly, these functions may cause a general protection (GP) fault in Windows or cause serious software problems.

'Type the following two lines as a single line in the module
Declare Function GetKeyState Lib "USER" (ByVal nVirtKey As Integer) _

Const SHIFT_KEY = 16 Const CTRL_KEY = 17 Const ALT_KEY = 18

Sub find_key()

   'Checks key states to see if both CTRL and ALT Keys are pressed
   If GetKeyState(CTRL_KEY) < 0 And GetKeyState(ALT_KEY) < 0 Then
     MsgBox "CTRL + ALT KEYS PRESSED"
   'Checks key states to see if only CTRL key is pressed
   ElseIf GetKeyState(CTRL_KEY) < 0 Then
     MsgBox "CTRL KEY PRESSED"
   'Checks key states to see if only ALT key is pressed
   ElseIf GetKeyState(ALT_KEY) < 0 Then
     MsgBox "ALT KEY PRESSED"
   'Checks key states to see if only SHIFT key is pressed
   ElseIf GetKeyState(SHIFT_KEY) < 0 Then
     MsgBox "SHIFT KEY PRESSED"
   End If

End Sub

The argument used to call the DLL function GetKeyState() is a number indicating which key to test. The number used is the virtual key code that the GetKeyState function uses to identify which key to test.

When you write your macro, you should be aware of the following:

• You should check for key state at the beginning of the macro. The GetKeyState() function returns the state of the key at the time the DLL function is called not at the time the macro is run. So, it is possible to miss the key state if you don't check for it right away.
• The test you use for checking which key is pressed must follow correct programming logic.
• Your macro should not check for too many key possibilities. The more possible key combinations you check for, the longer it takes your macro to find the right condition and, therefore, the greater the possibility that the key will no longer be pressed.

REFERENCES

"Microsoft Windows Software Developers Kit Reference," Volume 1, pages 1-30, 4-183

"Microsoft Windows Software Developers Kit Reference," Volume 2, Appendix A

"Microsoft Quick C for Windows," pages 341, 935-938