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:

GoToSpecial

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.

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , , , , , , , , , , . Bookmark the permalink.

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

  1. Pingback: Excel Roundup 20160314 - Contextures BlogContextures Blog

  2. Rory says:

    I guess that ties into the fact you can’t use those in UDFs – although the CurrentRegion one seems to be an anomaly.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s