VBA: 字典的介紹與執行個體，vba字典介紹執行個體

VBA: 字典的介紹與執行個體，vba字典介紹執行個體
基本語句

Sub dictest()' 其他可供參考的詳細解說： http://club.excelhome.net/thread-868892-1-1.html' 字典簡介' 字典對象相當於一種聯合數組，它是由具有唯一性的關鍵字（Key）和它的項（Item）聯合組成' VBA字典有6個方法Add , Keys, Items, Exists, Remove, RemoveAll' VBA字典有4個屬性Count , Key, Item, CompareMode' 建立字典"d"Dim d As ObjectSet d = CreateObject("Scripting.Dictionary")' 添加d.Add "a", "example1"d.Add "b", 9'd.Add "b", 7 'add重複key會報錯d("b") = 7    '這樣會直接覆蓋,不會報錯'd.Item("b") = 7 '與上者相同d("c") = "example2"'通常使用Sting文本字串作為字典key關鍵詞，也可以使用數字、數值作為key'速度：純數字>純文字字元>>>文本/數值混合型' 輸出Cells(1, 1) = d("a")'Cells(1, 1) = d.Item("a") '與上者相同Cells(1, 2) = d("b")Cells(1, 3) = d("c")' 字典成員個數Cells(1, 4) = d.Count'移除d.Remove ("b")'d.RemoveAll  '移除所有' key是否存在Cells(2, 1) = d.Exists("b")d.Add "b", 7Cells(2, 2) = d.Exists("b")' 替換keyd.Key("a") = "e"'所有key所在的數組和所有item所在的數組d_keys = d.KeysCells(3, 1) = d_keys(0)Cells(3, 2) = d_keys(1)d_items = d.ItemsCells(4, 1) = d_items(0)Cells(4, 2) = d_items(1)'更改字串關鍵字比較時所使用的比較模式'd.CompareMode = 0(二進位）/1(文本)/2(資料庫）End Sub

結果如下

執行個體

Sub dictest1()Dim data_count As Longdata_count = 188Dim data_name, data_income, idata_name = Application.Transpose(Worksheets("資料").Cells(2, 1).Resize(data_count, 1))data_income = Application.Transpose(Worksheets("資料").Cells(2, 2).Resize(data_count, 1))Dim d1 As ObjectSet d1 = CreateObject("Scripting.Dictionary")For i = 1 To data_count    If d1.exists(data_name(i)) Then        d1.Item(data_name(i)) = d1.Item(data_name(i)) + data_income(i)    Else        d1.Item(data_name(i)) = data_income(i)    End IfNextd1_keys = d1.keysd1_items = d1.itemsWorksheets("結果").Cells(2, 1).Resize(UBound(d1_keys) + 1, 1) = Application.Transpose(d1_keys)Worksheets("結果").Cells(2, 2).Resize(UBound(d1_items) + 1, 1) = Application.Transpose(d1_items)End Sub