为 AutoFilter 结果创建智能标记和事件代码

本页内容

	业务方案
	AutoFilter
	开发 AutoFilter 的事件机制
	使用智能标记
	小结

业务方案

在熟悉技术之前，回顾并查看代码实现业务价值的方式始终是一个不错的主意。尽管本文创建的示例并非具备完整功能的业务解决方案，但它确实形成了解决方案的基础。为了理解业务方案，假设用户具有一个包含数以百计的员工的电子表格，表格中列出了这些员工最近的业绩考核，在此例中考核以一些数字进行表示。正如我们期望的那样，电子表格用户最可能希望筛选列表中的结果以将重点放在选择多名员工或只选择一名员工上。由此，用户可能希望采取某项措施。例如，用户可以发现具有最佳业绩的前三名员工，然后可以采取某项措施，如向他们发送祝贺电子邮件或查看关于他们业绩的详细信息。

本文的示例使用 Microsoft® Excel 的 AutoFilter 功能和智能标记技术支持该业务方案。使用智能标记操作来提供根据员工姓名和业绩分数采取的操作。使用 AutoFilter 功能，以便通过智能标记只标注匹配条件的员工。其优点在于当只选择了一名或几名员工时，并不会使用智能标记标出大量的员工。

返回页首

AutoFilter

筛选是快速查找和使用某个范围中的数据子集的简单方法。筛选后的范围仅显示符合用户或代码提供的搜索条件的行。Microsoft Excel 提供了用于筛选范围的两种主要方法：用于简单条件的 AutoFilter 和用于较复杂条件的高级筛选。与排序不同，筛选不会重新排列单元格范围。相反，筛选只是临时隐藏不匹配搜索条件的行。一旦 Excel 筛选行，您就可以在不重新排列或移动范围子集的情况下，对其进行编辑、设置格式、插入图表和打印等操作。

当使用 AutoFilter 命令后，在筛选范围的列标签右侧会出现箭头（参见图 1）。如果您所排序的范围超过 1000 个唯一行，那么在单击列的箭头时，只有前 1000 个唯一项会出现在下拉列表中（与图 2 中的类似），知道这一点很重要。

图 1. 将筛选应用到单元格以将箭头添加到列标题

图 2. 使用下拉列表选择可见的行

您可以将筛选应用到某个范围等级。

要将筛选应用到某个单元格范围，请遵循下列步骤：

可以使用自定义的 AutoFilter 来显示包含不同值的行。还可以使用自定义的 AutoFilter 来显示符合某列的多个条件的行。例如，您可能要在特定范围内显示包含值（如 Fuller 值）的行。

以编程方式筛选范围

尽管本文中的示例并没有以编程方式应用筛选，但是您可能希望将该示例扩展为使用编程方式来进行筛选。从这个角度讲，学习 Excel VBA 中AutoFilter 对象的基本知识非常有用。以编程方式将筛选应用到某个范围的最常用方式就是使用 AutoFilter 方法。使用 AutoFilter 方法的常规语法如下所示：

<expression>.AutoFilter(Field, Criteria1, _ Operator, Criteria2, VisibleDropDown) 

该方法接受五个参数，所有参数都是可选的。忽略所有参数只需切换指定区域中下拉箭头的显示。以下表格列出了这些参数及其说明：

以下示例使用该方法来筛选开始于 Sheet1 上的单元格 C4 的列表以只显示第一列中字段等于字符串“Davolio”的项。屏幕将显示第一个字段的下拉箭头。

Worksheets("Sheet1").Range("C4").AutoFilter _
  Field:=1, _
  Criteria1:="Davolio", _
  VisibleDropDown:=True

如果希望查看应用到工作表中的筛选，您可以检索 AutoFilter 属性或访问其属性。属性如下所示：

使用 AutoFilter 属性，可以在工作表中查找筛选范围及其设置。以下代码循环查找所有筛选并确定它们当前是否已应用。

Dim oAutoFilter As AutoFilter
Dim oFilter As Filter
Set oAutoFilter = ActiveSheet.AutoFilter
For Each oFilter In oAutoFilter.Filters
  Debug.Print oFilter.On
Next oFilter

返回页首

开发 AutoFilter 的事件机制

在业务解决方案中使用 AutoFilter 功能的一个受限制的方面就是，在 Excel 对象模型中没有直接相关联的事件。换言之，当用户或通过编程方式应用或更改筛选时，没有用于该活动的明确事件。限制在于：在多数情况下，知道筛选的列表非常有用，以便出于其他目的，可以将筛选结果作为组进行访问。同样，知道何时取消筛选、再次显示完整列表或是否更改了现有筛选也非常有用。

正如在业务方案中解释的那样，本文中的示例使您能够筛选列表。然后，可以在应用筛选后，针对特殊目的来操作可见的行。在此例中，筛选出的行由智能标记标出。智能标记具有可以从 Actions 菜单应用的操作（参见图 3），这些操作可以使用显示的单元格中的文本。

图 3. 使用智能标记标出的筛选结果

从整体上看该过程，主要有三件事情需要进行配置或进行编码以使示例正常工作。首先，必须创建一个事件机制以确定工作簿中的活动是否为用户应用、更改或取消筛选的结果。其次，必须在代码中确定符合搜索条件以及那些不符合的单元格。第三，根据单元格所属的组，必须应用或取消显示（因为它们符合筛选条件）或隐藏的智能标记。

要创建事件机制，必须强制工作表每次重新计算更改的筛选条件。目标事件是 Workbook_SheetCalculate 事件。只要用户重新计算工作表或者用户在图表上绘制任何更改的数据时，就会激发该事件。通过将像 NOW() 这样的函数放在工作表上任意位置的单元格（除筛选的单元格范围内）中，可以轻松地强制重新计算。因此，在工作表中所做的任何更改都会触发目标事件。

使用 AutoFilter 范围

在该事件激发后，下一步就是区分引起该事件的活动类型。例如，是取消了筛选条件还是应用了新的筛选条件？这点非常重要，因为只有当应用筛选时显示的单元格才会以智能标记标出。实现该操作的最好方法就是首先设置 Range 对象变量，该变量代表可以进行筛选的值的整个列表。使用 Workbook_Open 事件以在工作表中发生任何其他活动之前对该值进行设置。全局声明和事件代码如下所示：

Private rngA As Range
Private lngLargeCount As Long
Private Const CUSTOM_SMART_TAG_TYPE As String = _
  "urn:schemas-microsoft-com:office:afnames#custom"
Private Sub Workbook_Open()
  Set rngA = GetRange()
  lngLargeCount = rngA.Count
End Sub 

声明包括三个变量，一个用于刚刚介绍过的范围，一个用于保存该范围内的行数。稍后，您可以在本文的程序中使用这个声明，而非重复地使用对象引用，并且便于使用简单变量。第三个变量是一个字符串常量，它包含用于标出工作表中选定单元格的自定义智能标记类型。有一些特定的智能标记操作与这种定义在智能标记 DLL 中的类型相关联。通过应用该类型，智能标记操作就可用于标记为使用该类型的每个单元格。在此种方式中，智能标记 DLL 不包含识别器。如果它包含，则可能会有问题，因为智能标记识别器会识别所有员工的名字，而不管他们是否位于筛选列表中。对于要提供所需的最终用户功能的当前示例，必须避免这种情况。将员工名字标记为可以在 VBA 解决方案中辨别的上下文的一部分，这种技术可以在不要求大量识别时使用。

Workbook_Open 事件包含调用自定义过程的代码（如下所示），该过程返回 Range 对象，此对象由给定工作表筛选的当前状态的可见单元格填充。当用户打开工作簿时，由于没有应用任何筛选，该范围包含列表中的所有单元格。

Private Function GetRange() As Range
  Set GetRange = _
  Range("Sheet1!_FilterDatabase"). _
  SpecialCells(xlCellTypeVisible)
End Function

该代码最吸引人的部分就是对 Range 对象的引用使用命名区域 _FilterDatabase。这是在打开 AutoFilter 时，Excel 在工作簿中创建的隐藏命名区域。它引用列表中的所有单元格。通过使用带有 xlCellTypeVisible 常量的 SpecialCells 方法，Excel 只返回可见单元格。如果用户进行不具任何选择的筛选，那么所有单元格都是可见的，即返回整个列表。这个 GetRange 过程会进行重复调用，这样当应用筛选时，只能检索可见的单元格集合。

下一步是在 Workbook_SheetCalculate 事件中编写代码，该事件是我们用于响应工作表中的活动的事件。该代码以下面的方式开始：

  Dim rngB As Range
  Dim rngTemp As Range
  Dim arrCellGroups() As String
  Dim i As Integer
  Dim j As Integer
  Set rngB = GetRange()
  If rngA.Address = rngB.Address Then
    Exit Sub
  End If

使用第一个变量以获得可见单元格的集合，给定 AutoFilter 的状态。为此，调用 GetRange 方法。记住，在 Workbook_SheetCalculate 事件激发前，rngA 包含单元格范围。如果该范围包含作为筛选结果显示的单元格，那么它包含使用智能标记标注的单元格，但该事件可能会由于其他原因而引发，例如在不相关的单元格中进行输入。比较两个范围的地址，rngA 表示范围的“之前”状态，rngB 表示“之后”状态。如果地址相同，那么工作簿中的活动与 AutoFilter 无关，该过程会立即退出。如果地址不匹配，那么就认为 AutoFilter 中出现了更改。

返回页首

使用智能标记

使用两个范围变量可以让我们对 AutoFilter 进行更改之前和之后的完整列表状态进行比较。如果扩展列表以包括所有值，则必须取消所有现有的智能标记。无论 AutoFilter 中发生何种更改，重要的是取消所有现有的智能标记，以便可以访问并标注当前可见的单元格。使用以下例程，可以取消现有的智能标记：

While rngA.SmartTags.Count > 0
 rngA.SmartTags(1).Delete
Wend

因为该例程只取消可以从指定范围内的单元格进行访问的智能标记，工作表中的其他智能标记将保留不变。因此，该代码必须确定您是否需要添加所有智能标记。如果当前可见的单元格数量与整个未筛选的列表中可见单元格数量不同，那么一定存在要求使用智能标记标出的一些单元格。该过程的以下部分会发现这些单元格并将智能标记添加到其中。

If rngB.Count <> lngLargeCount Then
    arrCellGroups = Split(rngB.Address, ",")
    For i = 0 To UBound(arrCellGroups)
      Set rngTemp = Range(arrCellGroups(i))
      If rngTemp.Rows.Count > 1 Then
        For j = 1 To rngTemp.Rows.Count
          If rngTemp.Cells(j, 1).Row > rngB.Cells(1, 1).Row Then
          rngTemp.Cells(j, 1).SmartTags.Add (CUSTOM_SMART_TAG_TYPE)
          End If
        Next j
      Else
        rngTemp.Cells(1, 1).SmartTags.Add (CUSTOM_SMART_TAG_TYPE)
      End If
    Next i
  End If

将智能标记添加到单元格相当简单，但是该代码中的大部分内容会对可见单元格范围地址进行分类，确定哪些单元格需要智能标记。在应用筛选后，可见单元格范围的一个奇妙之处是返回该地址的方式。例如，请查看图 4，并且注意可见单元格的行号。

图 4. 注意在设置 AutoFilter 后的可见单元格

第一行（行号为 4）包含列标题。第二行为 5，第三行为 9，最后一行为 13。只有前两行是连续的。当检索可见单元格范围的地址时，它看起来如下所示：$B$4:$C$5,$B$9:$C$9,$B$13:$C$13。此处显示了三组不同的单元格。它们是 B4:C5、B9:C9 和 B13:C13。正如您所见，因为前两行是连续的，所以它们位于同一组中。其他行由于不连续，所以都位于它们自己的组中。如果第 12 行确实包含匹配该条件的值，那么其单元格引用就会包括在最后一组的单元格引用中，生成 B12:C13。执行分隔这些组的任务，以标识每行中的一个单元格，然后将智能标记添加到其中。

通过将该范围的整个地址放在一个字符串变量中，该代码可以实现上述任务。然后，您可以使用拆分函数以标识由逗号分隔的每个组，并将该组放在字符串数组内其自己的值中，如下所示：

    arrCellGroups = Split(rngB.Address, ",")

然后，该代码依次通过数组中每个元素，并将新的 Range 对象的地址设置为数组元素的值。

    Set rngTemp = Range(arrCellGroups(i))

对于只包含一行的范围（例如 B9:C9 或 B13:C13），添加智能标记可以直接完成。可以按下面的格式将智能标记添加到第一行、第一列的单元格：

    rngTemp.Cells(1, 1).SmartTags.Add (CUSTOM_SMART_TAG_TYPE)

对于包含多行的范围，必须完成更多的工作。连续的行会引起一个小的挑战，因为在大多数情况下，您必须完成的所有操作就是依次通过范围内的每一行，并将智能标记添加到相应行的第一列中的单元格。但是，如果第二行可见，那么依次通过每行可能会包括整个范围的第一行（该行包含列标题）。这样就出现如图 4 所示的那样第 5 行包括在其中的情况。通过使用以下带有条件逻辑的循环，可以避免将第一行包括在内：

    For j = 1 To rngTemp.Rows.Count
      If rngTemp.Cells(j, 1).Row > rngB.Cells(1, 1).Row Then
      rngTemp.Cells(j, 1).SmartTags.Add (CUSTOM_SMART_TAG_TYPE)
      End If
    Next j

For . . . Next 结构的内部逻辑确保正在处理的行号与整个范围的第一行的行号不重合。如果行号比较大，则会添加智能标记。在所有情况下，都将会使用之前介绍的常量中指定的智能标记类型。

智能标记的使用假设您安装并正确注册了包含使用目标类型的类的智能标记 DLL。只需将单元格标记为所需类型，您就可以使用所有的智能标记操作了。在此例中，DLL 中的目标操作类可以使用从单元格中检索到的员工名字，以收集更详细的信息并将其提供给用户。

返回页首

小结

尽管没有与 AutoFilter 功能相关的特定事件，但是仍然可以构建响应 AutoFilter 活动的解决方案。电子表格中的筛选列表是一项常见的任务。用户可以启动该活动，或者可以在更详细的业务解决方案中以编程方式来实现。其中最有用的方法就是使用自定义的事件机制来找到在应用筛选之后的可见行，然后为用户提供他们可以基于该数据所采取措施的列表。智能标记是为用户提供操作列表的有效途径。它们毫不显眼，并且易于使用。通过简单地使用特定类型添加智能标记，您可以为用户提供适用于他们筛选列表结果的上下文选项。

转到原英文页面

返回页首