VBA字典：职场人的高效神器

VBA字典：职场人的高效神器

引用

CSDN

等

9

来源

1.

https://blog.csdn.net/h_XUYUAN/article/details/124656978

2.

https://blog.csdn.net/Javachichi/article/details/139282805

3.

https://zhuanlan.zhihu.com/p/631136470

4.

https://blog.csdn.net/m0_74737305/article/details/133823967

5.

https://blog.csdn.net/weixin_39917791/article/details/111126163

6.

https://www.cnblogs.com/new-june/p/15816460.html

7.

https://www.webzonl.com/shujufenxi/2209.html

8.

https://vbayyds.com/docs/excelvba/shuzu_zidian

9.

http://www.360doc.com/content/22/1017/18/71551544_1052093799.shtml

在职场中，数据处理是一项必不可少的技能。而VBA字典作为一款强大的工具，能够帮助职场人士快速完成数据去重、统计和匹配等工作。通过简单的代码实现复杂的任务，让工作效率大幅提升。无论是初学者还是资深职场人，掌握VBA字典都能让你在工作中游刃有余。

什么是VBA字典？

VBA字典是一种键值对（Key-Value）数据结构，类似于其他编程语言中的Map或HashMap。它允许你通过键来快速查找对应的值，非常适合用于数据处理和临时存储。

如何创建和使用VBA字典？

首先需要创建字典对象：

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

然后可以添加键值对：

dict.Add "A", 100
dict.Add "B", 200

检查键是否存在：

If dict.Exists("B") Then
    MsgBox "Key B exists. Value: " & dict("B")
End If

获取所有键和值：

Dim keys As Variant
Dim items As Variant
keys = dict.Keys
items = dict.Items

遍历字典：

Dim key As Variant
For Each key In dict.Keys
    Debug.Print "Key: " & key & ", Value: " & dict(key)
Next

场景一：数据去重

假设你有一列数据，需要去除重复值。使用VBA字典可以轻松实现：

Sub removeDuplicates()
    Dim myDic As Object, i As Integer, sht As Worksheet, maxRow As Integer, totalCnt As Integer
    Set myDic = CreateObject("scripting.dictionary")
    Set sht = ThisWorkbook.Sheets("Sheet1")
    maxRow = sht.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To maxRow
        If Not myDic.Exists(sht.Cells(i, 1).Value) Then
            myDic.Add sht.Cells(i, 1).Value, ""
        End If
    Next
    
    '方法一：利用transpose转置函数将一维数组转为一个N行一列的多维数组，找一个同样尺寸的range接收这个数组
    totalCnt = myDic.Count
    sht.Range("D2:D" & totalCnt) = Application.Transpose(myDic.Keys())
    
    '方法二：用for each方法直接遍历一维数组的每个元素，依次存入特定单元格
    i = 2
    For Each Name In myDic.Keys
        sht.Cells(i, 4).Value = Name
        i = i + 1
    Next
End Sub

场景二：替代VLOOKUP函数

VBA字典可以轻松实现VLOOKUP的功能，而且效率更高：

Sub myVlookup()
    Dim myDic As Object, i As Integer, sht As Worksheet, maxRow As Integer
    Set myDic = CreateObject("scripting.dictionary")
    Set sht = ThisWorkbook.Sheets("Sheet1")
    maxRow = sht.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To maxRow
        If Not myDic.Exists(sht.Cells(i, 1).Value) Then
            myDic.Add sht.Cells(i, 1).Value, sht.Cells(i, 3).Value
        End If
    Next
    maxRow = sht.Cells(Rows.Count, 5).End(xlUp).Row '读取第五列的最后一行行号
    For i = 2 To maxRow
        sht.Cells(i, 6).Value = myDic.Item(sht.Cells(i, 5).Value) '根据第五列的key，将对应的item写入第六列
    Next
End Sub

场景三：实现反向查找

VBA字典还可以实现从右到左的反向查找，这是VLOOKUP无法做到的：

Sub reverseLookup()
    Dim myDic As Object, i As Integer, sht As Worksheet, maxRow As Integer
    Set myDic = CreateObject("scripting.dictionary")
    Set sht = ThisWorkbook.Sheets("Sheet1")
    maxRow = sht.Cells(Rows.Count, 2).End(xlUp).Row
    For i = 2 To maxRow
        myDic.Add sht.Cells(i, 2).Value, sht.Cells(i, 1).Value
    Next
    maxRow = sht.Cells(Rows.Count, 4).End(xlUp).Row
    For i = 2 To maxRow
        sht.Cells(i, 5).Value = myDic.Item(sht.Cells(i, 4).Value)
    Next
End Sub

CompareMode属性

字典的CompareMode属性可以设置键的比较方式，例如是否区分大小写：

dict.CompareMode = vbTextCompare '不区分大小写
dict.CompareMode = vbBinaryCompare '区分大小写

错误处理

在操作字典时，需要注意错误处理，例如避免重复添加键：

On Error Resume Next
dict.Add "A", 100
If Err.Number <> 0 Then
    MsgBox "Key already exists!"
End If
On Error GoTo 0

1. 效率高：相比传统函数，VBA字典的查找速度更快
2. 灵活性强：可以实现复杂的数据处理逻辑
3. 易用性好：通过键值对的方式，代码更易理解和维护
4. 功能强大：可以实现VLOOKUP无法完成的反向查找等功能

对于职场人士来说，掌握VBA字典不仅能提升工作效率，还能让你在数据处理方面更加得心应手。建议大家多加练习，将VBA字典应用到实际工作中，相信你会感受到它带来的便利。