记录一次VBA字典的使用

部分代码

Public Sub MainRun()'1. 获取数据集，存储到字典中Set dict = CreateObject("Scripting.dictionary")Set dictUse = CreateObject("Scripting.dictionary")Sheets("数据").SelectRange("J3").SelectRange(Selection, Selection.End(xlToRight)).SelectColumnsNum = Selection.CountFor i = 1 To ColumnsNumk = Cells(3, i + 9) & "-" & Cells(5, i + 9) & "-" & Cells(6, i + 9) & "-" & Cells(7, i + 9)v = Cells(8, i + 9)dict.Add k, vNext i'2. 获取Key listKeyList = dict.keysstartColumns = 10'3. 遍历Key list进行数据运算For Each KeyItem In KeyListshortKey = Left(KeyItem, Len(KeyItem) - 1)key3Field = Left(shortKey, Len(shortKey) - 1)If Not dictUse.exists(key3Field) ThenSheets("控件").Cells(3, startColumns) = key3FielddictUse.Add key3Field, key3FieldaSuffix = Right(KeyItem, 1)For i = 5 To 8 Step 1steps = Sheets("控件").Cells(i, 6)oneKey = shortKey & stepsanotherSteps = Sheets("控件").Cells(i, 7)anotherKey = shortKey & anotherSteps' 保证两个值都存在的情况下进行数据运算If dict.exists(anotherKey) And dict.exists(oneKey) ThenDebug.Print dict(oneKey) - dict(anotherKey)Sheets("控件").Cells(i, startColumns) = dict(oneKey) - dict(anotherKey)End IfNextstartColumns = startColumns + 1End IfNextSheets("控件").Select
End Sub

1. VBA 中创建一个字典对象

Set dict = CreateObject("Scripting.dictionary")

2. 字典对象添加key 和 value

dict.Add key,value

3. 获取字典对象的key list

dict.keys

4. 判断是否存在某个key

dict.exists(key)

5. 修改字典值

dict(key) = newdata

6. 修改字典key 名称

dict.key(key) = newkeyname

6. 删除某个item

dict.remove key

7. 删除所有item

dict.removeall
运行结果:

>>> 如果你觉得我的文章对你有用，不妨 【点赞】 加 【关注】，你的支持是我持续写作的动力，thank you! <<<