VBA: Introduction and examples of dictionaries, and examples of vba dictionaries

VBA: Introduction and examples of dictionaries, and examples of vba dictionaries
Basic statement

Sub dictest () 'Other detailed explanations for reference: http://club.excelhome.net/thread-868892-1-1.html' dictionary introduction' dictionary object is equivalent to a Union array, which is composed of a unique Key) combined with its Items, 'vba dictionary has six methods: Add, Keys, Items, Exists, Remove, removeall' VBA dictionary has four attributes: Count, Key, Item, compareMode 'create Dictionary "d" Dim d As ObjectSet d = CreateObject ("Scripting. dictionary ") 'add d. add "a", "example1" d. add "B", 9 'd. add "B", 7'add duplicate key will report the error d ("B") = 7', which will directly overwrite and will not report the error 'd. item ("B") = 7' is the same as above d ("c") = "example2" 'usually uses the Sting text string as the dictionary key keyword, you can also use numbers and numeric values as the key' speed: numbers only> plain text characters> text/numeric hybrid 'output Cells (1, 1) = d ("") 'cells (1, 1) = d. item ("a") 'is the same as Cells (1, 2) = d ("B") Cells (1, 3) = d ("c ") 'Number of dictionary members Cells (1, 4) = d. count 'remove d. remove ("B") 'd. removeall' remove all 'keys for Cells (2, 1) = d. exists ("B") d. add "B", 7 Cells (2, 2) = d. exists ("B") 'to replace keyd. key ("a") = "e" 'array of all keys and array of all items 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) 'comparison mode used to change string keyword comparison 'd. compareMode = 0 (Binary)/1 (text)/2 (database) End Sub

The result is as follows:

Instance

Sub dictest1 () Dim data_count As Longdata_count = 188Dim data_name, data_income, idata_name = Application. transpose (Worksheets ("data "). cells (2, 1 ). resize (data_count, 1) data_income = Application. transpose (Worksheets ("data "). 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 ("result "). cells (2, 1 ). resize (UBound (d1_keys) + 1, 1) = Application. transpose (d1_keys) Worksheets ("result "). cells (2, 2 ). resize (UBound (dmo-items) + 1, 1) = Application. transpose (d1_items) End Sub