關(guān)于Dictionary字典用法的學習與筆記

1、關(guān)于dictionary字典用法的學習與筆記總結(jié)：字典一般為二列組成的二維數(shù)組,其對象值默認為Item.a(Key,Item)字典方法（Dictionary):根據(jù)關(guān)鍵字(key)在字典中查找字:關(guān)鍵字Key 不能重復(fù)檢索:Item 可以重復(fù)通俗講：key如關(guān)健字、查尋字 Item即如解釋內(nèi)容Dictionary屬性和方法的亮點(與Collection對象相比):1.key屬性可隨時更改:If dic.Exists(OldKey) Then dic.Key(OldKey) = NewKey2.item屬性可隨時更改:If dic.Exists(OldKey) Then dic.Item(Old

2、Key) = NewItem3.Keys方法可得到一個下界為0的一維數(shù)組: Dim ss=dic.keys4.items方法也可得到一個下界為0的一維數(shù)組:Dim ss=dic.Items5. Remove 方法可直接刪除Dictionary對象的某一個元素,對于VB的一維數(shù)組來說,省去了不少編碼的煩惱6. Removeall方法可直接刪除Dictionary對象的全部元素7.對于使用Keys和items方法得到的數(shù)組,可以使用VBA的數(shù)組的全部技巧進行處理,如Filter(),Join()函數(shù)及工作表函數(shù)Transpose(),Max(),Min(),Large()等的使用.8.Count屬

3、性為Dictionary數(shù)組與EXCEL工作表的相互賦值提供了方便.9.鍵值的唯一性使得Dictionary對象在統(tǒng)計"不重復(fù)"方面的問題得心應(yīng)手,而item屬性可更改更使得Dictionary對象在數(shù)據(jù)匯總上大顯身手.10.VBA的一些控件如listbox,combobox,單元格的數(shù)據(jù)有效性,自定義序列及圖表的序列,xvalue屬性等也與數(shù)組有一定的聯(lián)系,使得Dictionary對象也有用武之地.具體步驟學習：Dictionary對象的引用:方法1:在工具->引用菜單中,選擇Scripting Runtime Library:然后在模塊中按以下方式引用:Dim d

4、ic As New Dictionary或者Dim dic2 As New Scripting.Dictionary方法2:使用CreateObject方法,如:Dim dic As ObjectSet dic=CreateObject("scripting.dictionary")更多數(shù)情況下,我們可以忽略變量的定義,直接使用其屬性和方法,如開頭的例子: Dim i As IntegerWith CreateObject("scripting.dictionary")For i = 2000 To 2100If Day(DateSerial(i, 3,

5、 0) = 29 Then .Add i, NothingNextMsgBox Join(.keys, " ")End WithDictionary 對象的屬性和說明語法:object.CompareMode = compareCompareMode 設(shè)定或返回鍵的字符串比較模式（僅用于 VBScript） VbUseCompareOption -1 值比較bBinaryCompare 0 二進制比較vbTextCompare 1 文字比較vbTextCompare 2 數(shù)據(jù)庫比較方法 說明Add(key,item) 增加鍵/條目對到 DictionaryExists(ke

6、y) 如果指定鍵存在，返回 True，否則返回 FalseItems() 返回一個一唯所有條目的數(shù)組Keys() 返回一個一唯所有關(guān)鍵值的數(shù)組Remove(key) 刪除一個指定鍵的條目或鍵RemoveAll() 刪除全部鍵及條目語法:object.Add key, item添加一對相對應(yīng)的關(guān)鍵字和條目到 Dictionary 對象key不能重復(fù)，item可以重復(fù)語法:object.Exists(key)如果在 Dictionary 對象中指定的關(guān)鍵字存在，返回 True，若不存在，返 回 False語法:object.Keys返回一個數(shù)組，該數(shù)組包含一個 Dictionary 對象中的全部已

7、有的關(guān)鍵字。 object.Key(key) = newkey如果在更改某個 key 時，沒有找到 key，則會出現(xiàn)運行時錯誤語法:object.Items返回一個包含 Dictionary 對象中所有條目的數(shù)組object.Item(key) = newitem如果在改變某個 item 時，沒有找到 key，則用指定的newitem創(chuàng)建一個新 的 key.語法:object.Remove(key)從一個 Dictionary 對象中刪除一個關(guān)鍵字和條目的字典元素如果指定的關(guān)鍵字和條目對不存在，則發(fā)生一個錯誤語法:object.RemoveAllDictionary 對象中刪除所有關(guān)鍵字和條目

8、的字典元素語法:object.Countd.count返回 Dictionary 里的鍵/條目對的數(shù)量,不能為d.keys.count或d.Item.count,只能為d.count字符串處理函數(shù)：Split函數(shù)Split(字符串,分割符)返回由分割符分割字符成組成的一維數(shù)組Join函數(shù)Join(一維數(shù)組,連接符)返回由一維數(shù)組連接符連接成的字符串Filter函數(shù)Filter(數(shù)組,"查找值")返回一個下標從零開始的一維數(shù)組，它包含符號合過濾要求、指定數(shù)目的子字符 串，置空非數(shù)組ubound(temp)返回-1比較模式的區(qū)別:Sub macro1()Dim d As New

9、 Dictionary, s, i As Longs = "aa","Aa","aA"On Error Resume Nextd.CompareMode = BinaryCompare'二進制方式比較,即a,A是不同字符For i = 1 To 3d.Add s(i), ""NextDebug.Print Join(d.Keys, vbCrLf); vbCrLf; "d.Count=" & d.CountEnd Sub返回:aaAaaAd.Count=3Sub macro2()D

10、im d As New Dictionary, s, i As Longs = "aa","Aa","aA"On Error Resume Nextd.CompareMode = TextCompare'文本方式比較,即a,A是相同字符For i = 1 To 3d.Add s(i), ""NextDebug.Print Join(d.Keys, vbCrLf); vbCrLf; "d.Count=" & d.CountEnd Sub返回:aad.Count=1例子：應(yīng)用實例1（

11、順序顯示1-100）：Sub usage()Dim dic As Object, i As LongSet dic = CreateObject("Scripting.Dictionary")For i = 1 To 100dic.Add i, ""NextMsgBox Join(dic.keys, ",")Set dic=NothingEnd Sub應(yīng)用實例2（顯示1-100中含3的整數(shù)）：Sub usage2()Dim dic As Object, i As LongSet dic = CreateObject("Scr

12、ipting.Dictionary")For i = 1 To 100dic.Add i, ""NextMsgBox Join(Filter(dic.keys, "3"), vbCrLf)Set dic=NothingEnd Sub應(yīng)用實例3（WORKSHEET中A列顯示1-10000）：Sub usage3()Dim dic As Object, i As Long, arrSet dic = CreateObject("Scripting.Dictionary")For i = 1 To 10000dic.Add i,

13、""Nextarr = WorksheetFunction.Transpose(dic.keys)a1.Resize(UBound(arr), 1) = arrSet dic = NothingEnd Sub應(yīng)用實例4 (WORKSHEET中A列顯示1 - 10000，B列逆序顯示): Sub usage4()Dim dic As Object, i As Long, arrSet dic = CreateObject("Scripting.Dictionary")For i = 1 To 10000dic.Add i, 10001 - iNextarr

14、 = WorksheetFunction.Transpose(dic.keys)a1.Resize(UBound(arr), 1) = arrarr = WorksheetFunction.Transpose(dic.items)b1.Resize(UBound(arr), 1) = arrSet dic = NothingEnd Sub應(yīng)用實例5 (WORKSHEET中A列顯示1 - 100000中被6除余1和5 的數(shù)字): Sub usage5()Dim dic As Object, i As Long, arrSet dic = CreateObject("Scripting.

15、Dictionary")For i = 1 To 100000dic.Add i & IIf(Abs(i Mod 6 - 3) = 2, "", ""), ""Nextarr = WorksheetFunction.Transpose(Filter(dic.keys, "")a1.Resize(UBound(arr), 1) = arra:a.Replace "", ""Set dic = NothingEnd Sub應(yīng)用實例6 (不重復(fù)值提取):Sub U

16、sage6()'以下代碼將A：N列的所有文本，數(shù)字不重復(fù)的復(fù)制到O列：Dim r As Range, c As RangeSet r = Sheets("sheet1").a:n.SpecialCells(xlCellTypeConstants, 23)With CreateObject("scripting.dictionary")For Each c In rIf Not .exists(c.Value) Then .Add c.Value, ""NextSheets("sheet1").o1.Resi

17、ze(.Count, 1) = WorksheetFunction.Transpose(.keys) End WithEnd SubSub Usage6_2()'跨表不重復(fù)值提取,將表All的D列數(shù)據(jù)提取到表temp的A列 Application.ScreenUpdating = FalseDim r As Range, arrWorksheets("All").SelectWith CreateObject("scripting.dictionary")For Each r In Range("D3:D" & Ran

18、ge("A65536").End(xlUp).Row)If Not .exists(r.Value) Then .Add r.Value, NothingNextWorksheets("temp").SelectCells.ClearRange("a2").Resize(.Count, 1) = WorksheetFunction.Transpose(.keys)End WithApplication.ScreenUpdating = TrueEnd Sub應(yīng)用實例7 (COMBOBOX賦值):Private Sub UserForm

19、_Initialize()Dim dic As Object, i As Long, arrSet dic = CreateObject("Scripting.Dictionary")For i = 1 To 1000dic.Add i, ""NextUserForm1.ComboBox1.List = dic.keysSet dic = NothingEnd Sub'應(yīng)用實例8 (字符頻率統(tǒng)計):'本例統(tǒng)計圓周率前500位中各數(shù)字出現(xiàn)的頻率并顯示在WORKSHEET的前兩行 Sub Usage8()Const pi As String

20、="3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117067982148086513282306647093844609550582231725359408128481117450284102701938521105559644622948954930381964428810975665933446128475648233786783165271201909145648566923460348610454326648213393607260

21、24914127372458700660631558817488152092096282925409171536436789259036001133053054882046652138414695194151160943305727036575959195309218611738193261179310511854807446237996274956735188575272489122793818301194912"Dim i As Long, temp As String, dic As ObjectSet dic = CreateObject("scripting.di

22、ctionary")For i = 3 To Len(pi)temp = Mid(pi, i, 1)If Not dic.exists(temp) Thendic.Add temp, 1Elsedic(temp) = dic(temp) + 1End IfNexta1:a2 = WorksheetFunction.Transpose(Array("Number", "出現(xiàn)次數(shù)")b1.Resize(1, dic.Count) = dic.keysb2.Resize(1, dic.Count) = dic.itemsSet dic = Nothi

23、ngEnd Sub本例統(tǒng)計某字符串中各字符出現(xiàn)的頻率并顯示在WORKSHEET的前兩行 Sub Usage8_2()Const s As String = "在VBA中有一個數(shù)據(jù)字典即dictionary功能很好，運行速度比較快，掌握以后可以替代一些其他查找功能，現(xiàn)向老師請教數(shù)據(jù)字典即dictionary的基本原理是怎樣的，它適合于哪些情況之下可以運用，在運用過程中應(yīng)當注意哪些問題。"Dim i As Long, temp As String, dic As ObjectSet dic = CreateObject("scripting.dictionary&qu

24、ot;)For i = 1 To Len(s)temp = Mid(s, i, 1)If Not dic.exists(temp) Thendic.Add temp, 1Elsedic(temp) = dic(temp) + 1End IfNexta1:a2 = WorksheetFunction.Transpose(Array("字符", "出現(xiàn)次數(shù)")b1.Resize(1, dic.Count) = dic.keysb2.Resize(1, dic.Count) = dic.itemsSet dic = NothingEnd Sub應(yīng)用實例9 列出

25、一個工作簿中所有已使用的自定義函數(shù)需要添加對VB項目的信任Sub UDFSOFACTIVEWORKBOOK()Dim sh As Worksheet, r As Range, dic As Object, i As Long, temp As String, VBcomp, s() As String, UDF As StringFor i = 1 To ActiveWorkbook.VBProject.VBComponents.CountSet VBcomp = ActiveWorkbook.VBProject.VBComponents(i)If VBcomp.Type = 1 Then t

26、emp = temp & VBCrLf & VBcomp.CodeModule.Lines(1, 65536)Nexts = Split(temp, VBCrLf)temp = ""For i = 0 To UBound(s)If s(i) Like "Function * As *" Then temp = temp & "" & "=" & Trim(Split(Split(s(i), "(")(0), "Function")(1)

27、 & "(" '->All functions with or without parameters NextSet dic = CreateObject("scripting.dictionary")For Each sh In SheetsFor Each r In sh.UsedRangeIf r.HasFormula ThenIf InStr(temp, "" & Split(r.Formula, "(")(0) > 0 ThenUDF = r.Formula &

28、 "udf"ElseUDF = ""End IfIf Not dic.exists(r.Formula) Then dic.Add r.Formula, UDFEnd IfNextNextDebug.Print "All functions used in activesheet" & VBCrLf & String(50, "-") & VBCrLf & Join(dic.keys, VBCrLf) & VBCrLf & VBCrLf '列出一個工作簿中所有

29、函數(shù)Debug.Print "All user define functions used in activesheet" & VBCrLf & String(50, "-") & VBCrLf & Replace(Join(Filter(dic.items, "udf"), VBCrLf), "udf", "") '列出一個工作簿中所有已使用的自定義函數(shù)Set dic = NothingEnd Sub應(yīng)用實例10 列出Word 文檔中所用的全部字體集合(

30、在WORD VBA中使用) Sub Usage10()Dim myRange As Range, str_Result As String, str_TempWith CreateObject("scripting.dictionary")On Error Resume NextFor Each str_Temp In Application.FontNamesSet myRange = ActiveDocument.ContentWith myRange.Find.ClearFormatting.Font.NameFarEast = str_TempIf .Font.NameFarEast <> "" ThenIf .Execute(findtext:="*", MatchWildcards:=True, Wrap: