|
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
|
