SendKeys in VBA

The SendKeys statement in VBA is one of the more interesting features because you can use it to send keystrokes directly to the user interfacejust as if you were entering the keystrokes yourself. This means you can use SendKeys to perform all sorts of tasks that you might not ordinarily be able to perform using other statements. For example, you could use SendKeys to modify the Ribbon interface programmatically.

Unfortunately, SendKeys can also cause problems for the VBA developer. I’ve received more than a few e-mails regarding SendKeys examples not working properly. For example, Listing 5-8 in VBA for Dummies causes a lot of woe for many readers. The natural inclination is to want to watch the macro run from within the Visual Basic for Applications editor, but you can’t do this with SendKeys. The SendKeys statement always sends its output to the active windowno matter which window that might be. So, if you have the Visual Basic for Applications editor selected, the SendKeys output goes to that window, rather than the Excel window where the output would normally go when a user is running the macro.

So, just how do you run this macro? If you want to see the macro work in the debugger, you begin by setting a breakpoint in the editor. Simply click in the margin area on the left side of the editor and you’ll see a breakpoint added like the one shown here:

SendKeys01

The breakpoint line appears highlighted in red. You also see a red circle in the left margin. When you run the macro, it’ll stop at the ActiveRows = ActiveWindow.RangeSelection.Rows.Count line.

At this point, you can run the macro. Open the Developer tab and you’ll see a Macros button in the Code group as shown here:

SendKeys02

Click the Macros button and you’ll see the Macro dialog box shown here:

SendKeys03

Select the ChangeAllRooms macro from the list. Click Run. The macro will run to the breakpoint you selected.

Now, if you run the macro shown in Listing 5-8, you’ll see that SendKeys is actually broken in VBA in Office 2010 (it worked fine in earlier versions of Office). The key combination is sent, but usually after the rest of the macro has run. The second part of the callthe part that tells VBA to wait until the SendKeys call is processed before proceeding is broken. Consequently, the code to tell Excel to select a column by pressing the Ctrl+Shift+Down Arrow keys doesn’t work. The key combination is sent to the window, but long after the need for the key combination has passed. If there is someone who has a solution to this problem, let me know at John@JohnMuellerBooks.com. In the meantime, you can get this example to work using the following code instead.

Public Sub ChangeAllRooms()
    Dim ActiveRows As Integer   ' Number of active rows.
    Dim Counter As Integer      ' Current row in process.
     
    ' Select the first data cell in the worksheet.
    Range("A5").Select
     
    ' Use SendKeys to select all of the cells in the column.
    ' This call is broken in Office 2010, so I commented it out.
    'SendKeys "^+{DOWN}", True
    ' Select the range manually for the example instead.
    Range("A5", "A10").Select
     
    ' Get the number of rows to process.
    ActiveRows = ActiveWindow.RangeSelection.Rows.Count
     
    ' Reset the cell pointer.
    Range("C5").Select
     
    ' Keep processing the cells until complete.
    For Counter = 5 To ActiveRows + 5
     
        ' Call the Sub created to change a single cell.
        MakeChoice3
         
        ' Move to the next cell.
        Range("C" + CStr(Counter)).Select
    Next
End Sub

OK, now that you have working code, you can run the example. Here are the results you should see from Listing 5-8.

SendKeys04

 

Author: John

John Mueller is a freelance author and technical editor. He has writing in his blood, having produced 99 books and over 600 articles to date. The topics range from networking to artificial intelligence and from database management to heads-down programming. Some of his current books include a Web security book, discussions of how to manage big data using data science, a Windows command -line reference, and a book that shows how to build your own custom PC. His technical editing skills have helped over more than 67 authors refine the content of their manuscripts. John has provided technical editing services to both Data Based Advisor and Coast Compute magazines. He has also contributed articles to magazines such as Software Quality Connection, DevSource, InformIT, SQL Server Professional, Visual C++ Developer, Hard Core Visual Basic, asp.netPRO, Software Test and Performance, and Visual Basic Developer. Be sure to read John’s blog at http://blog.johnmuellerbooks.com/. When John isn’t working at the computer, you can find him outside in the garden, cutting wood, or generally enjoying nature. John also likes making wine and knitting. When not occupied with anything else, he makes glycerin soap and candles, which comes in handy for gift baskets. You can reach John on the Internet at John@JohnMuellerBooks.com. John is also setting up a website at http://www.johnmuellerbooks.com/. Feel free to take a look and make suggestions on how he can improve it.