将VBA查找函数改为数组字典事件，优化10万条Excel数据查询速度

核心思路 #

将MASTERVALIDATION表的CODE与ITEM数据一次性加载到内存字典中（CODE作为唯一键，ITEM对应值），利用字典O(1)的查找效率替代原数组公式/VBA函数的遍历匹配，大幅降低处理器占用，彻底解决卡顿问题。同时通过工作表事件触发匹配，仅在输入CODE时执行查找操作。

实现代码 #

1. 标准模块（存储公共字典，避免重复加载数据）

' 声明全局字典对象，仅初始化一次
Public dictCodeToItem As Object

Sub InitDict()
    Dim wsMaster As Worksheet
    Dim arrData As Variant
    Dim i As Long
    
    Set dictCodeToItem = CreateObject("Scripting.Dictionary")
    ' 不区分大小写匹配，需区分则改为vbBinaryCompare
    dictCodeToItem.CompareMode = vbTextCompare
    
    Set wsMaster = ThisWorkbook.Worksheets("MASTERVALIDATION")
    ' 一次性将数据加载到数组（比逐单元格读取效率提升百倍）
    arrData = wsMaster.Range("A2:B" & wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Row).Value
    
    ' 遍历数组填充字典
    For i = LBound(arrData, 1) To UBound(arrData, 1)
        If arrData(i, 2) <> "" Then ' 假设CODE在B列，ITEM在A列，按需调整列位置
            ' 重复CODE保留最后一条的ITEM值
            dictCodeToItem(arrData(i, 2)) = arrData(i, 1)
        End If
    Next i
End Sub

2. SEARCH工作表事件代码

双击SEARCH工作表标签打开代码窗口，粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCode As Range
    Dim cell As Range
    
    ' 限定仅处理CODE输入列（假设CODE输入在A列，按需调整）
    Set rngCode = Intersect(Target, Me.Columns("A"))
    If rngCode Is Nothing Then Exit Sub
    
    ' 关闭事件触发防止递归，关闭屏幕刷新提速
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    For Each cell In rngCode
        If cell.Value <> "" Then
            ' 字典存在对应CODE则返回ITEM，否则留空
            cell.Offset(0, 1).Value = IIf(dictCodeToItem.Exists(cell.Value), dictCodeToItem(cell.Value), "")
        Else
            cell.Offset(0, 1).Value = ""
        End If
    Next cell
    
    ' 恢复事件与屏幕刷新
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

3. 工作簿打开事件（自动初始化字典）

双击ThisWorkbook对象，粘贴以下代码：

Private Sub Workbook_Open()
    ' 打开工作簿时自动加载字典数据
    InitDict
End Sub

注意事项 #

• 按需调整代码中列的对应关系：确保MASTERVALIDATION表的ITEM、CODE列，以及SEARCH表的输入/输出列与代码一致
• 若MASTERVALIDATION表数据更新，需手动执行InitDict宏重新加载字典
• 可通过修改dictCodeToItem.CompareMode切换是否区分大小写匹配

内容的提问来源于stack exchange，提问作者roy