Monday, April 13, 2020

VBA - Application.SendKeys

Application.SendKeys is a way to simulate your keyboard strokes through VBA. For example: you know that we can press Ctrl+1 to show the Format Cells dialogue box in Excel. Without manually pressing these keys, you can let the macro press them.

Syntax

Application.SendKeys (Keys, Wait)

Keys: Key or key combination you want to simulate. You have to always enclose the keys in double quotes (to represent as text).

Wait: Optional. True to have Microsoft Excel wait for the keys to be processed before returning control to the macro. False (or omitted) to continue running the macro without waiting for the keys to be processed.
Each key on the keyboard is represented by one or more characters. For detailed listing of the keys, see below picture. All other keys are represented by respective alphabets.


SendKeys always works on the active window hence you should absolutely make sure that the application and window you are targeting is active at the time of running the macro.

Example of Application.SendKeys

We will use this method to run a spell check on the active sheet. The key board sequence to perform a spell check Excel 2010 is Alt+r+s. Let’s simulate the same using SendKeys method. Refer to above table for the keys to be used.

Alt is represented by percent sign, and other letters by same letters. Below is the code for our macro.
Sub SpellCheck()
Application.SendKeys ("%rs")
End Sub
There are alternates to use Sendkeys which are better. For example in the above illustrative case, you can use VBA to call up the Spell Check by using a direct command, that is straight forward and better to use. So use the Application.SendKeys sparingly and only when you have no other option. Happy Learning!!