1、声明一个字典对象:
Dim dict As Object Set dict = CreateObject('Scripting.Dictionary')
2、添加键值对到字典:
dict.Add 'key', 'value'
3、从字典中获取值:
value = dict('key')
4、检查字典中是否存在某个键:
If dict.Exists('key') Then ' 存在该键 End If
6、删除字典中的键值对:
dict.Remove 'key'
6、遍历字典中的所有键: For Each key In dict.Keys Debug.Print key Next key
7、遍历字典中的所有值:
For Each value In dict.Items Debug.Print value Next value
8、遍历字典中的所有键值对:
For Each key In dict.Keys Debug.Print key, dict(key) Next key
9、获取字典中的键的数量:
count = dict.Count
10、清空字典:
dict.RemoveAll
11、判断字典是否为空:
If dict.Count = 0 Then' 字典为空 End If
12、合并两个字典:
Dim dict1 As Object Set dict1 = CreateObject('Scripting.Dictionary') dict1.Add 'key1', 'value1'
Dim dict2 As Object Set dict2 = CreateObject('Scripting.Dictionary') dict2.Add 'key2', 'value2'
For Each key In dict2.Keys dict1(key) = dict2(key) Next key
13、查找字典中的最大键:
maxKey = Application.WorksheetFunction.Max(dict.Keys)
14、查找字典中的最小键:
minKey = Application.WorksheetFunction.Min(dict.Keys)
15、查找字典中的最大值:
maxValue = Application.WorksheetFunction.Max(dict.Items)
16、查找字典中的最小值:
minValue = Application.WorksheetFunction.Min(dict.Items)
17、将字典转换为数组:
Dim arr() As Variant arr = dict.Items
18、将数组转换为字典:
Dim arr() As Variant arr = Array('key1', 'value1', 'key2', 'value2')
Dim dict As Object Set dict = CreateObject('Scripting.Dictionary') For i = LBound(arr) To UBound(arr) Step 2 dict(arr(i)) = arr(i + 1) Next i
19、将字典的键转换为数组:
Dim keys() As Variant keys = dict.Keys
20、将字典的值转换为数组:
Dim values() As Variant values = dict.Items
21、将字典的键值对转换为字符串:
Dim str As String str = Join(dict.Items, ', ')
22、将字符串转换为字典:
Dim str As String str = 'key1:value1, key2:value2'
Dim dict As Object Set dict = CreateObject('Scripting.Dictionary') arr = Split(str, ', ') For i = LBound(arr) To UBound(arr) keyValue = Split(arr(i), ':') dict(keyValue(0)) = keyValue(1) Next i
23、将字典保存到文件:
Dim fso As Object Set fso = CreateObject('Scripting.FileSystemObject') fso.CreateTextFile('dict.txt').Write Join(dict.Items, vbCrLf)
24、 从文件读取字典:
Dim fso As Object Set fso = CreateObject('Scripting.FileSystemObject') str = fso.OpenTextFile('dict.txt').ReadAll arr = Split(str, vbCrLf) For i = LBound(arr) To UBound(arr) keyValue = Split(arr(i), ':') dict(keyValue(0)) = keyValue(1) Next i
25、将字典保存到Excel工作表:
Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets.Add ws.Range('A1').Resize(dict.Count, 2).Value = Application.Transpose(dict.Items)
26、从Excel工作表读取字典:
Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets('Sheet1') arr = ws.Range('A1').CurrentRegion.Value For i = LBound(arr, 1) To UBound(arr, 1) dict(arr(i, 1)) = arr(i, 2) Next i
27、将字典保存到JSON文件:
Dim json As Object Set json = CreateObject('MSXML2.DOMDocument') json.LoadXML '' For Each key In dict.Keys node = json.createElement('item') node.setAttribute 'key', key node.setAttribute 'value', dict(key) json.documentElement.appendChild node Next key json.Save 'dict.json'
28、从JSON文件读取字典:
Dim json As Object Set json = CreateObject('MSXML2.DOMDocument') json.Load 'dict.json' For Each node In json.SelectNodes('/root/item') dict(node.getAttribute('key')) = node.getAttribute('value') Next node
29、将字典保存到数据库表:
Dim db As Object Set db = CreateObject('ADOX.Catalog') db.Tables.Add 'DictTable' db.ActiveConnection = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\;Extended Properties='Excel 8.0;HDR=YES;IMEX=1'' db.Execute 'INSERT INTO DictTable (Key, Value) SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=C:\Temp\].[Sheet1$]'
30、从数据库表读取字典:
Dim db As Object Set db = CreateObject('ADOX.Catalog') db.ActiveConnection = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\;Extended Properties='Excel 8.0;HDR=YES;IMEX=1'' Set rs = db.Execute('SELECT * FROM DictTable') Do While Not rs.EOF dict(rs('Key')) = rs('Value') rs.MoveNext Loop
31、将字典保存到XML文件:
Dim xml As Object Set xml = CreateObject('MSXML2.DOMDocument') xml.LoadXML '' For Each key In dict.Keys node = xml.createElement('item') node.setAttribute 'key', key node.setAttribute 'value', dict(key) xml.documentElement.appendChild node Next key xml.Save 'dict.xml'
32、 从XML文件读取字典:
Dim xml As Object Set xml = CreateObject('MSXML2.DOMDocument') xml.Load 'dict.xml' For Each node In xml.SelectNodes('/root/item') dict(node.getAttribute('key')) = node.getAttribute('value') Next node
33、将字典保存到文本文件:
Dim fso As Object Set fso = CreateObject('Scripting.FileSystemObject') fso.CreateTextFile('dict.txt').Write Join(dict.Items, vbCrLf)
34、从文本文件读取字典:
Dim fso As Object Set fso = CreateObject('Scripting.FileSystemObject') str = fso.OpenTextFile('dict.txt').ReadAll arr = Split(str, vbCrLf) For i = LBound(arr) To UBound(arr) keyValue = Split(arr(i), ':') dict(keyValue(0)) = keyValue(1) Next i
35. 将字典保存到CSV文件:
Dim fso As Object Set fso = CreateObject('Scripting.FileSystemObject') fso.CreateTextFile('dict.csv', True).Write Join(dict.Items, ',')
36. 从CSV文件读取字典:
Dim fso As Object Set fso = CreateObject('Scripting.FileSystemObject') str = fso.OpenTextFile('dict.csv').ReadAll arr = Split(str, ',') For i = LBound(arr) To UBound(arr) Step 2 dict(arr(i)) = arr(i + 1) Next i
|