What are SendKeys in VBA?
Contents
An Introduction to Automation
Every user of a computer has some mundane task to do every day. For example, you may have to execute something at a particular time on your machine or check the value of some stock or commodity every minute if you doing financial trading. If only these tasks were automated, it would save a lot of time and effort for us, wouldn’t it?
Several tools are available in the market today to automate such tasks. Visual Basic for Applications is one such coding language. It is event driven, and can do wonders when used with any of our MS Office (host) applications. It also has a macro recording feature which beginners can use to create their projects or code.
A Small Challenge
As we run VBA code, there is a possibility for user intervention to be required for the code to continue running. It could be in the form of a user’s input, or a mouse click on an unexpected window that pops up, or hitting the enter key on an “ok” button. Many of these situations are difficult to trace out properties and automate properly.
A Solution: SendKeys
VBA offers us a solution to these issues by providing us with the SendKeys method. This statement imitates keyboard typing during code run-time.
Syntax to use the SendKeys method:
SendKeys <string or keystroke> , [ <wait>]
Where
Wait is the boolean value depicting the mode of wait.
If the value is “True”, the control will wait for the keystoke to be sent and the action to be completed.
If the value is “False”, the control will immediately move on to the next statement in the code after sending the keystroke.
String or keystroke is the character or group of characters that need to be typed at that particular moment. This parameter can also be an ASCII key equivalent as shown in the below table. The first three keys shift, ctrl, and alt can be used with the respective key codes wherever a combination of keys needs to be used. For example:
- Ctrl + C for copy
- Ctrl + V for paste
S.no | Key | Code |
1 | SHIFT | “+” |
2 | CTRL | “^” |
3 | ALT | “%” |
4 | Backspace | {Backspace}, {BKSP}, {BS} |
5 | Break | {Break} |
6 | Caps Lock | {CapsLock} |
7 | Delete | {Delete}, {Del} |
8 | Down Arrow | {Down} |
9 | End | {End} |
10 | Enter | {Enter}, ~ |
11 | Escape | {Esc} |
12 | Help | {Help} |
13 | Home | {Home} |
14 | Insert | {Insert}, {Ins} |
15 | Left Arrow | {Left} |
16 | Num Lock | {NumLock} |
17 | Page Down | {Pgdn} |
18 | Page Up | {Pgup} |
19 | Print Screen | {PrtSc} |
20 | Right Arrow | {Right} |
21 | Scroll Lock | {ScrollLock} |
22 | Spacebar | {Space} or try ” “ |
23 | Tab | {Tab} |
24 | Up Arrow | {Up} |
25 | F1 – F16 | {F1} … {F16} |
26 | TRUE | TRUE |
27 | FALSE | FALSE |
This is a substitute for manual typing or keystrokes. Sending keystrokes to the open and active window is definitely a feasible option, but should be only be considered an alternative method. It is advisable to use this only when any other form of communication is not supported by the application. One example of such an application is Notepad or Notepad++.
Some simple examples
- This program opens a notepad, types “Hello World!” and saves the file with the name “TRIAL” without changing the default path.
Public Sub NotePad_Test() 'Declaration of variable Dim varRet As Variant 'Initiation of variable i.e. to open a notepad application from Windows varRet = Shell("NotePad.exe", vbNormalFocus) 'Activation of that notepad AppActivate varRet 'Sending keystrokes to write to the notepad Application.SendKeys "Hello World!", True 'Hit the enter key using sendkeys method. This is to go to a new line Application.SendKeys "~", True 'this sends an (Alt + F + A) which is the shortcut of (File &gt; Save As) . 'Then it follows the filename "TRIAL" and ten hits on enter ( equivalent to pressing the save button). Application.SendKeys "%FATRIAL~", True End Sub
2. This program edits a cell using one of the predefined function keys.
Sub Edit_a_cell() ' Select a cell in the sheet Range("A1").Select 'Edit the cell Application.SendKeys "{F2}" End Sub
3. This program inserts a row above a selected row using the SendKeys method.
Sub Row_Insertion() 'Select the 2nd row of the active worksheet ThisWorkbook.ActiveSheet.Rows("2:2").Select 'Shortcut to insert a new row above is "Alt+I+R". Trying this with sendkeys Application.SendKeys "%ir" End Sub
Drawbacks to SendKeys
Following is an example depicting a failure of the Sendkeys method. Here I’m trying to copy the value of a cell and paste it in another nearby cell using the SendKeys method. But, I encounter some challenges and the method doesn’t provide me with successful results.
But I’m able to achieve it through a simple value assignment. The same is also clearly explained in a video below.
Sub copy_Paste() 'Select a cell for copying and wait for it to be selected Range("A1").Select Application.Wait (Now() + TimeValue("00:00:05")) 'copy using sendkeys and wait for it to get copied Application.SendKeys "^c", True Application.Wait (Now() + TimeValue("00:00:05")) 'select a cell for pasting and wait for it to get selected Range("B1").Select Application.Wait (Now() + TimeValue("00:00:05")) 'paste using sendkeys and wait for it to get pasted. Application.SendKeys "^v", True Application.Wait (Now() + TimeValue("00:00:05")) 'simple assignment resolves the issue Range("b1").Value = Range("a1").Value End Sub
Watch the below video completely to understand and try out the examples spelled out above.
In the above examples and video, you might have seen that in spite of trying some long wait statements after each line of code, the copy and paste actions do not take place as expected. The same was achieved through a single line of code in a jiffy. That’s the drawback of the SendKeys method.
Conclusion
SendKeys is a good alternative for cases where your window does not respond to code language or does not support UI interaction, but the job still has to be done. This is partially equivalent to doing manual work, so VBA gives us an advantage using this method.
There is a possibility that SendKeys will end up executing on a different window in the event of unexpected pop ups or network issues. Hence, it is a best practice to have automations using SendKeys run in the presence of the user — otherwise called “attended automation.” This will help identify and clear out unexpected interruptions during the program’s run and ensure the end result is met as intended.
See also: Calling the Shell in VBA
One thought on “What are SendKeys in VBA?”