為 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 活動的解決方案。電子表格中的篩選列表是一項常見的任務。用戶可以啟動該活動，或者可以在更詳細的業務解決方案中以編程方式來實現。其中最有用的方法就是使用自定義的事件機制來找到在應用篩選之后的可見行，然后為用戶提供他們可以基于該數據所采取措施的列表。智能標記是為用戶提供操作列表的有效途徑。它們毫不顯眼，并且易于使用。通過簡單地使用特定類型添加智能標記，您可以為用戶提供適用于他們篩選列表結果的上下文選項。

轉到原英文頁面

返回頁首