如何让VBA实现的XLOOKUP达到工作表公式的执行速度？

1. 频繁跨工作簿访问整列数据：循环里每次调用XLOOKUP都要读取外部工作簿的整列（$A:$A、$J:$J），整列包含超100万行数据，每次都要扫描这么大的范围；而工作表公式的XLOOKUP会内部优化数据范围，批量处理时效率更高。
2. 频繁读写单元格：循环中每次单独写入Range("J1").Offset(i, 0)，VBA与Excel界面的交互开销极大，每一次单元格读写都会触发Excel内部刷新，累积起来拖慢速度。
3. 未关闭Excel后台冗余功能：运行代码时Excel默认会更新屏幕、触发事件、自动计算公式，这些都会额外消耗系统资源。

Sub OptimizedLookUpMacro()
    Dim prevWB As Workbook
    Dim currWS As Worksheet
    Dim catData(1 To 4) As Variant
    Dim currAData As Variant
    Dim resultArr() As String
    Dim lastRowCurr As Long, lastRowPrev As Long
    Dim i As Long, j As Long, k As Long
    
    ' 关闭Excel后台开销功能
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    ' 打开外部工作簿并赋值变量
    Set prevWB = Workbooks.Open("#Censored#\Previous Non-Rotating Materials Report.xlsx")
    Set currWS = ThisWorkbook.ActiveSheet ' 可替换为指定工作表，如ThisWorkbook.Sheets("当前报告表")
    
    ' 获取当前工作表A列实际数据行数（避免整列扫描）
    lastRowCurr = currWS.Cells(currWS.Rows.Count, "A").End(xlUp).Row
    If lastRowCurr < 2 Then GoTo Cleanup ' 无数据直接退出
    
    ' 读取当前工作表A列数据到内存数组
    currAData = currWS.Range("A2:A" & lastRowCurr).Value
    
    ' 读取外部工作簿四个分类的A-J列数据到内存数组（仅取实际使用范围）
    For j = 1 To 4
        With prevWB.Sheets("Category" & j)
            lastRowPrev = .Cells(.Rows.Count, "A").End(xlUp).Row
            catData(j) = .Range("A1:J" & lastRowPrev).Value
        End With
    Next j
    
    ' 初始化结果数组
    ReDim resultArr(1 To UBound(currAData, 1), 1 To 1)
    
    ' 循环处理每个物料，在内存数组中匹配评论
    For i = 1 To UBound(currAData, 1)
        Dim material As String
        material = currAData(i, 1)
        resultArr(i, 1) = ""
        
        For j = 1 To 4
            For k = 1 To UBound(catData(j), 1)
                If catData(j)(k, 1) = material Then
                    resultArr(i, 1) = resultArr(i, 1) & catData(j)(k, 10) ' J列是第10列
                    Exit For ' 找到匹配后退出当前分类循环
                End If
            Next k
        Next j
    Next i
    
    ' 一次性写入结果到J列，减少界面交互
    currWS.Range("J2:J" & lastRowCurr).Value = resultArr
    
Cleanup:
    ' 关闭外部工作簿（不保存）
    prevWB.Close SaveChanges:=False
    
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    
    ' 释放对象变量
    Set prevWB = Nothing
    Set currWS = Nothing
End Sub

• 内存数组操作：把所有需要用到的数据一次性读到内存数组中，彻底避免反复跨工作簿访问和频繁读写单元格，内存操作的速度比Excel界面交互快几个数量级。
• 缩小数据范围：用End(xlUp)获取实际有数据的最后一行，不再扫描整列，减少无意义的计算。
• 关闭后台冗余功能：运行代码期间关闭屏幕更新、事件触发和自动计算，消除额外性能损耗。
• 内存内匹配替代XLOOKUP调用：直接在内存数组中循环匹配，避免VBA调用Excel工作表函数的额外开销。

内容的提问来源于stack exchange，提问作者Jakub Szyguła