SendKeys for Office 2010 Revisited

Some time ago, I wrote a post entitled, “SendKeys in VBA.” In that post, I described a problem where the SendKeys example in Listing 5-8 of VBA for Dummies isn’t working as expected. It turns out that a lot of people are having this problem and not just with Excel—the problem seems to happen with other Office products as well. I’ve played with the macro in Listing 5-8 quite a lot and while it does work in Office 2007 SP2, it doesn’t work in Office 2010 as written.

Apparently, this problem isn’t unique to Office 2010 either. I’ve found threads online that indicate that the problem also existed in early versions of Office 2007, but was fixed in SP2 after a lot of people complained. Wandering around the Internet has proven interesting because I’ve found other blog posts that try to fix the problem, but didn’t work on my system for whatever reason. The bottom line is that SendKeys is broken and quite a few people know it.

Since my original post, I’ve tried several experiments, none of which resolve the problem, but some of which serve to highlight the true essence of the problem. For example, I tried to break the code in Listing 5-8 apart into room selection and row processing pieces. Here is the room selection piece:

Sub SelectRooms()
    ' Select the first data cell in the worksheet.
    Range("A5").Select
     
    ' Use SendKeys to select all of the cells in the column.
    VBA.SendKeys "+^{DOWN}", True
End Sub

and here’s the row processing piece:

Sub ProcessRows()
    Dim ActiveRows As Integer   ' Number of active rows.
    Dim Counter As Integer      ' Current row in process.
     
    ' 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

If you run these pieces one at a time, the example will work just as it did with earlier versions of Office. However, that means running two separate macros instead of one, which does cut down on the usability of the technique. The SendKeys function appears to perform its task at the end of the macro, whenever the end of the macro occurs. Initially, I thought that calling these two macros in succession might do the trick, so I created this piece of code:

Sub ChangeAllRooms2()
    ' Select the rooms.
    SelectRooms
     
    ' Process the selections.
    ProcessRows
End Sub

Unfortunately, the result is the same as before. The SendKeys function really does appear not to output anything until the macro is finished. However, because I’m a glutton for punishment, I tried a few other things. For example, I thought perhaps that the wait part of the SendKeys call was to blame, so I created a manual wait like this:

Sub WaitForSendKeys(Timeout As Integer)
    ' Create a variable to hold the wait interval.
    Dim WaitTime
     
    ' Set the interval.
    WaitTime = TimeSerial(Hour(Now()), _
                          Minute(Now()), _
                          Second(Now()) + Timeout)
     
    ' Wait for the event to happen.
    Application.Wait WaitTime
End Sub

I then modified the original macro to look like this:

Public Sub ChangeAllRooms3()
    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.
    SendKeys "+^{DOWN}"
     
    ' Add a wait interval.
    WaitForSendKeys 5
     
    ' 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

The results are that the macro runs a lot slower, but still doesn’t do the job. The only way that SendKeys works is to have it at the end of the macro. In short, you need to execute the code in Listing 5-8 in two parts in order to make it work as originally described in the book. As an alternative, you can still use the code in my previous post. The fix that seems to work best is not to use SendKeys at all.

Microsoft has been quiet about this whole issue. Nowhere have I found any word from Microsoft on the problem with SendKeys and I doubt there is a fix in the offing. If someone has a suggestion for fixing this problem, I’m all ears. Please contact me at John@JohnMuellerBooks.com with full details of your fix. Make sure you’ve tested your code using Office 2010 on a Vista or Windows 7 system. I’ll be sure to give full credit to anyone who does come up with a fix for this problem.

 

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