字典的36段vba代码

最新推荐文章于 2025-05-05 19:52:53 发布

ribut9225

最新推荐文章于 2025-05-05 19:52:53 发布

阅读量651

点赞数

CC 4.0 BY-SA版权

文章标签： excel

原文链接：https://mp.weixin.qq.com/s?__biz=MzUzOTk1MDI3NA==&mid=2247485915&idx=1&sn=5d5af04ee914c0ea25844b103af8b889&chksm=fac1e476cdb66d6090c76552298cee0ac2143d1b617c401f5088aaccb1e7a339ca5a21994b2a&mpshare=1&scene=23&srcid=0427Sew3F9EaKIXSlgzKnq8I&sharer_shareinf

本文详细介绍了使用VBA在Excel中进行字典操作，包括创建、添加、删除键值对，遍历，合并字典，以及将字典数据保存到各种格式（如JSON、数据库、文本文件等）的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

关于字典的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