Excel揭秘24:使用SpecialCells方法带来的后果

学习Excel技术,关注微信公众号:

excelperfect

在编写VBA代码处理工作表单元格时,经常会使用到SpecialCells方法来获取特定单元格,然而,使用该方法会引起一些“副作用”,我们还是了解清楚比较好些。

下面通过一个示例来说明,如下图1所示的工作表。

图1

将下面的代码输入到该工作表的代码模块中:

Sub TestSpecialCells1()

Cells.SpecialCells xlCellTypeBlanks

End Sub

Private Sub Worksheet_SelectionChange(ByVal TargetAs Range)

MsgBox Target.Address

End Sub

如果运行TestSpecialCells1过程,则会发现如果找到空白单元格,就会引发Worksheet_SelectionChange,如下图2所示,这点比较讨厌。代码显然不会选择任何单元格,那么为什么要引发该事件呢?其实质是Range.SpecialCells背后的代码设计不佳造成的。

图2

Range.SpecialCells对应着Excel的“定位条件”功能。在Excel中按下F5键,单击“定位条件”按钮,可以看到如下图3所示的“定位条件”对话框。当用户尝试通过该对话框访问特殊单元格时,可以选择这些单元格,但是当我们通过代码访问它们时,就不会发生。

图3

如果通过这些选项进行测试,你会发现Range.SpecialCells并不是唯一表现出此行为的Range类成员。注意以下任何一项:

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

出于某种原因,Range.CurrentRegion不会(这是一件好事),但注意,它的Range.SpecialCells等效项Range.SpecialCells(5)则会。并且还会发现一些更有趣的行为,这使我们对它的工作方式有了更深入的了解。例如:

Sub TestSpecialCells2()

Cells.SpecialCells xlCellTypeLastCell

End Sub

Cells.SpecialCells xlCellTypeLastCell会导致Worksheet_SelectionChange事件触发2次。为什么?一次查找最后一行,一次查找最后一列,以便它们相交以获得最后一个单元格。

在Microsoft发现并解决此事件问题之前,解决方法是在使用前将Application.EnableEvents设置为False,并在使用后再次将其设置为True。需要进行适当的错误处理以确保正确重置Application.EnableEvents,因为大量情况可能导致这些调用引发运行时错误。

注:本文学习整理自colinlegg.wordpress.com,一个很有意思的博客网站,作者探讨了很多使用Excel时不为人知的地方。