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

Source: Internet
Author: User

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


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.