Tag: Visual Basic for Applications

Review of Mastering VBA

A lot of people have asked about the next book to read after reading VBA for Dummies. Yes, the current 5th edition of VBA for Dummies still works fine as a starting point, even with issues such as dealing with the Ribbon to consider. In fact, you can find some great updates to VBA for Dummies on my blog. However, the fact of the matter is that readers have been asking for more, which is where Mastering VBA by Richard Mansfield comes into play. This is the next book you should get if you want to move on from what VBA for Dummies shows you to writing applications with greater functionality. For example, a lot of you have requested more information about creating forms and Chapters 13 through 15 will help you in this regard. Richard has done an outstanding job of moving you to the next step of creating the complex forms required for robust applications.

Another common request that Mastering VBA addresses is the need for security. While VBA for Dummies helps you understand the need for basic security, Mastering VBA takes the process several steps further and could help prevent breaches given the modern computing environment (one that didn’t exist when I wrote VBA for Dummies). Chapter 18 begins the process by emphasizing the need to build well-behaved code. After all, if your code doesn’t behave, there isn’t any set of security measures that will protect it from harm. Chapter 19 goes on to help you understand the essentials of good security, especially with all the modern threats that cause problems for developers today.

At 924 pages (versus 412 for VBA for Dummies), Richard is also able cover some topics in detail that would have been nice to have in my own book. Readers have complained about having to go online to view object model details for the various Office applications in my book. Mastering VBA provides coverage of the object model as part of the book so you can work through it without having to go anywhere else. It’s a convenience issue—readers really shouldn’t have to look for essentials like the object model online, but every author has to face space limitations when putting a book together. The object model material is spread out across the book, but there really isn’t any way to organize it so that it all appears together. This is one time when you’ll need to actually use the table of contents and index to find the material you need.

As with all the books in the Mastering series, this one has questions at the end of each chapter. These questions are designed to help you master the skills learned in the chapter. You find the answers for each of the questions in the back of the book. This makes Mastering VBA an excellent option for the classroom. More importantly, it gives you another way to learn the material in the book. The longer I write books, the more I come to realize that one or two methods of learning simply won’t do the job. This book usually provides three or four ways to learn each task, which means that you have a higher probability of actually mastering the material (as defined by the title).

For all of you who have been asking for the next book after VBA for Dummies, Mastering VBA is the one that gets my recommendation. Until I actually have time to write a book that specifically addresses the concerns in the reader e-mails I’ve received, this book is your best option. No, it doesn’t address every e-mail request that I’ve received, especially with regard to form creation, but it does answer a considerable number of them. Of course, I’ll look forward to your continued interest in my book and I hope you keep those e-mails coming my way!

 

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 [email protected] 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.