Categories: Excel

Excel VBA 移除重複的陣列元素教學與範例

使用 Dictionary 或 Collection 自動刪除陣列中所有重複的元素，只留下不重複的項目。

問題

假設我們有類似以下這樣的 Excel 表格，原始資料包含許多的項目，各個項目以逗號分隔，其中可能會包含重複的項目，我們想要刪除重複出現的項目，只留下不重複的部分，該怎麼處理？

重複的原始資料

解法

若要處理像這樣的重複性資料，可以先用 VBA 的 Split 將每個項目切成陣列，再使用一些去除重複性元素的 VBA 程式設計技巧，把重複的項目刪除，最後再將結果填回 Excel 表格中即可。

在開始撰寫 VBA 程式之前，請先啟用 Excel 內建的開發人員工具，接著建立一個新的 VBA 巨集程式，準備好 VBA 程式的開發環境之後，就可以開始撰寫 VBA 的程式了。

要刪除 VBA 陣列中重複的元素，有很多種方式，以下分別介紹使用 Dictionary 與 Collection 的方法。

`Dictionary` 方法

Dictionary 是一種儲存「鍵」（key）與「值」（value）對應的資料結構，一個「鍵」只會對應到一個「值」。

我們可以將陣列的所有元素都作為「鍵」（「值」使用固定值），放入 Dictionary 之中，然後再取出所有的「鍵」，這樣重複的陣列元素就會自然被刪除了，以下是實作的程式碼：

' 使用 Dictionary 刪除陣列重複元素
Function RemoveDupDict(rawArray As Variant) As Variant
  Dim i As Long
  Dim dict As Scripting.Dictionary

  ' 建立一個 Dictionary
  Set dict = CreateObject("Scripting.Dictionary")

  ' 將陣列的每個元素取出來，放入 Dictionary 中
  For i = LBound(rawArray) To UBound(rawArray)
    dict.Item(rawArray(i)) = 1
  Next i

  ' 傳回 Dictionary 的「鍵」
  RemoveDupDict = dict.Keys
End Function

在執行含有 Dictionary 的程式碼時，若出現「使用者自訂型態尚未定義」的編譯錯誤，請啟用 VBA 的「Microsoft Scripting Runtime」引用項目即可解決。

`Collection` 方法

Collection 儲存的資料是不重複的元素，若將重複的元素放入 Collection 的時候，會產生錯誤。

我們可以將所有的陣列元素放入 Collection 時，忽略重複元素所產生的錯誤，這樣就可以去除重複的元素了，以下是實作的程式碼：

' 使用 Collection 刪除陣列重複元素
Function RemoveDupColl(rawArray As Variant) As Variant
  Dim i As Long
  Dim coll As New Collection
  Dim arr() As Variant

  ' 忽略重複的錯誤
  On Error Resume Next

  ' 將陣列元素放入 Collection 中
  For i = LBound(rawArray) To UBound(rawArray)
    coll.Add CStr(rawArray(i)), CStr(rawArray(i))
  Next i

  ' 清除錯誤
  Err.Clear

  ' 設定結果陣列大小
  ReDim arr(LBound(rawArray) To coll.Count - LBound(rawArray) - 1)

  ' 取出 Collection 的元素，放入結果陣列中
  i = LBound(arr)
  For Each item In coll
    arr(i) = item
    i = i + 1
  Next

  RemoveDupColl = arr
End Function

刪除 VBA 陣列重複元素

有了以上刪除陣列重複元素的函數之後，就可以直接應用在我們的問題上面了，接下來只要把 Excel 表格中的資料取出來，使用上面的函數刪除重複元素，再把結果寫回 Excel 表格中即可。

For i = 2 To 6

  ' 取出原始資料
  rawData = Cells(i, 1).Value

  ' 檢查原始資料（除錯用）
  Debug.Print ("原始資料：" & rawData)

  ' 以逗點分割資料
  fruitArray = Split(rawData, ",")

  ' 檢查分割的資料（除錯用）
  Debug.Print ("分割後的資料：")
  For Each f In fruitArray
    Debug.Print (f)
  Next f

  ' 刪除重複元素
  fruitArray = RemoveDupDict(fruitArray)
  'fruitArray = RemoveDupColl(fruitArray)

  ' 檢查結果（除錯用）
  Debug.Print ("刪除重複的資料：")
  For Each f In fruitArray
    Debug.Print (f)
  Next f

  ' 將結果輸出至 Excel 表格中
  Cells(i, 2).Value = Join(fruitArray, ",")

Next i