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:

1. Ctrl + C for copy
2. 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

1. 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 > 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