Unwanted Worksheet Events? Maybe SpecialCells() Is To Blame!

I’ve written a lot on this blog about Range.SpecialCells() and how careful you have to be when using it. Here’s yet another caveat which I didn’t know about until another developer brought it to my attention.

Put some data in a worksheet (to avoid runtime errors for this test) and then put this code in that worksheet’s class module:

Option Explicit

Sub TestSpecialCells1()
    Cells.SpecialCells xlCellTypeBlanks
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox Target.Address
End Sub

If you run TestSpecialCells1() you’ll find that, provided a blank cell was found, the Worksheet_SelectionChange() event is raised – which is immensely annoying. The code clearly doesn’t select any cells so why should that event get raised? The answer is that the code behind Range.SpecialCells() is shoddy.

Range.SpecialCells() mirrors using many of the options in the  [F5 > Special] ‘Go To Special’ dialog box (shown below). When a user tries to access special cells via the interface it is quite reasonable that those cells are selected but, when we’re accessing them via code, it shouldn’t happen. To put it simply: Range.SpecialCells() is too tightly coupled to the user interface:

If you test your way through these options you’ll find that Range.SpecialCells() isn’t the only Range class member which exhibits this behaviour. Watch out for any of these:

• Range.ColumnDifferences()
• Range.CurrentArray
• Range.Dependents
• Range.DirectDependents
• Range.DirectPrecedents
• Range.Precedents
• Range.RowDifferences()
• Range.SpecialCells()

For some reason Range.CurrentRegion doesn’t – which is a good thing – but note that its Range.SpecialCells() equivalent, Range.SpecialCells(5), does. You’ll also find some more interesting behaviour which gives us an insight as to how it works. For example:

Sub TestSpecialCells2()
    Cells.SpecialCells xlCellTypeLastCell
End Sub

Range.SpecialCells(xlCellTypeLastCell) causes the Worksheet_SelectionChange() event to be raised twice! Why? Once to find the last row and once to find the last column so that it can then intersect them to get the last cell.

Until Microsoft fixes this event issue (and I seriously doubt it ever will), the workaround is to set Application.EnableEvents to False before using it and setting it back to True again after using it. Proper error handling needs to be in place to ensure Application.EnableEvents is reset correctly because a whole host of situations can cause these calls to throw a runtime error.