VBA -[知识点]: 字典

记录一次VBA字典的使用

部分代码

Public Sub MainRun()
    '1. 获取数据集，存储到字典中
    Set dict = CreateObject("Scripting.dictionary")
    Set dictUse = CreateObject("Scripting.dictionary")
    
    Sheets("数据").Select
    Range("J3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    ColumnsNum = Selection.Count
    
    For i = 1 To ColumnsNum
        k = Cells(3, i + 9) & "-" & Cells(5, i + 9) & "-" & Cells(6, i + 9) & "-" & Cells(7, i + 9)
        v = Cells(8, i + 9)
        dict.Add k, v
    Next i
    '2. 获取Key list
    KeyList = dict.keys
    startColumns = 10
    
    '3. 遍历Key list进行数据运算
    For Each KeyItem In KeyList
        shortKey = Left(KeyItem, Len(KeyItem) - 1)
        key3Field = Left(shortKey, Len(shortKey) - 1)
        If Not dictUse.exists(key3Field) Then
            Sheets("控件").Cells(3, startColumns) = key3Field
            dictUse.Add key3Field, key3Field
            aSuffix = Right(KeyItem, 1)
            For i = 5 To 8 Step 1
                steps = Sheets("控件").Cells(i, 6)
                oneKey = shortKey & steps
                anotherSteps = Sheets("控件").Cells(i, 7)
                    anotherKey = shortKey & anotherSteps
                ' 保证两个值都存在的情况下进行数据运算
                If dict.exists(anotherKey) And dict.exists(oneKey) Then
                    Debug.Print dict(oneKey) - dict(anotherKey)
                    Sheets("控件").Cells(i, startColumns) = dict(oneKey) - dict(anotherKey)
                End If
            Next
            startColumns = startColumns + 1
        End If
        
    Next
    
    Sheets("控件").Select
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

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