字典的36段vba代码

本文详细介绍了使用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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值