关于字典的36段vba代码
原创 兔兔excel 学点vba 2024-04-27 00:01 广东
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