如何编写Excel VBA函数实现跨60+工作表的XLOOKUP查询？

我有一个包含60+工作表（APP1至APP60）的Excel工作簿，需在主工作表中使用XLOOKUP查询并返回目标值（因返回值位于首列，VLOOKUP无法适用）。嵌套XLOOKUP因数量过多易出错，我修改了用于VLOOKUP的VBA代码以适配XLOOKUP，但自定义函数XLOOKUPWORKBOOK返回错误值0，请求帮助排查修复。

原嵌套XLOOKUP示例：

=XLOOKUP([@Forms],APP1!C:C,APP1!A:A,
XLOOKUP([@Forms],APP2!C:C,APP2!A:A,
XLOOKUP([@Forms],APP3!C:C,APP3!A:A,
XLOOKUP([@Forms],APP4!C:C,APP4!A:A))))

注：我使用德语版Excel，函数已转译为英文格式，原版本用分号替代逗号。

原代码问题分析 #

• 仅修改了lookup_array的工作表关联，return_array仍指向原工作表（主表），导致跨表查询时返回数组不匹配
• On Error Resume Next掩盖了XLOOKUP未找到值的错误，可能导致value_to_return错误赋值
• 可选参数使用[if_not_found]的引用方式不正确，且未设置合理默认值
• 未处理所有工作表都未找到值的情况，此时value_to_return为空，VBA会默认返回0

修复后的VBA代码 #

Function XLOOKUPWORKBOOK( _
    lookup_value As Variant, _
    lookup_col As String, _
    return_col As String, _
    Optional if_not_found As Variant = "未找到", _
    Optional match_mode As Integer = 0, _
    Optional search_mode As Integer = 1 _
) As Variant

    Dim mySheet As Worksheet
    Dim value_to_return As Variant
    Dim ws_lookup_range As Range
    Dim ws_return_range As Range
    
    ' 初始化返回值为未找到状态
    value_to_return = if_not_found
    
    ' 仅遍历符合命名规则的工作表（APP1到APP60），提升效率
    For Each mySheet In ActiveWorkbook.Worksheets
        If mySheet.Name Like "APP#" Or mySheet.Name Like "APP##" Then
            On Error Resume Next
            ' 绑定当前工作表的查询列与返回列范围
            Set ws_lookup_range = mySheet.Range(lookup_col & ":" & lookup_col)
            Set ws_return_range = mySheet.Range(return_col & ":" & return_col)
            
            ' 执行XLOOKUP查询
            value_to_return = WorksheetFunction.XLookup(lookup_value, ws_lookup_range, ws_return_range, if_not_found, match_mode, search_mode)
            On Error GoTo 0
            
            ' 找到匹配值则终止遍历
            If value_to_return <> if_not_found Then Exit For
        End If
    Next mySheet
    
    XLOOKUPWORKBOOK = value_to_return
End Function

关键修改说明 #

• 将原代码中的lookup_array和return_array改为列标识字符串（如"C"、"A"），确保每个工作表都能正确引用对应列
• 限定遍历范围为APP1至APP60格式的工作表，避免遍历无关工作表浪费资源
• 初始化返回值为if_not_found，解决未找到值时返回0的问题
• 修正可选参数的引用方式，设置合理默认值
• 优化错误捕获逻辑，避免错误被无差别掩盖

使用方法 #

在主工作表中输入公式（英文格式）：

=XLOOKUPWORKBOOK([@Forms],"C","A","未找到")

德语版Excel需将逗号替换为分号：

=XLOOKUPWORKBOOK([@Forms];"C";"A";"未找到")

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