如何无需打开工作簿实现VLookup？VBA函数优化需求

针对你每次调用Workbooks.Open打开查找表导致效率低下的问题，以下是三种无需（或后台静默打开）工作簿的解决方案，按易用性和效率排序：

方法1：使用Excel外部引用公式（最简单高效） #

直接构造指向关闭工作簿的VLOOKUP公式，用Evaluate函数执行，完全不需要打开目标文件，是最轻量化的方案。

修改后的函数示例：

Function GetScopeFilename(axsunpart As String, sweeprate As Double)
    Dim position As Long
    Dim lookupPath As String
    Dim lookupRange As String
    Dim vlookupFormula As String
    
    ' 定义查找表的路径和数据范围
    lookupPath = "C:\Users\Documents\LookupTable.xlsx"
    lookupRange = "'Scope Filename'!A1:D4"
    
    ' 确定VLookup返回的列位置
    Select Case sweeprate
        Case 50
            position = 2
        Case 100
            position = 3
        Case 200
            position = 4
        Case Else
            MsgBox "未指定有效扫描速率，请检查参数后重试。"
            GetScopeFilename = ""
            Exit Function
    End Select
    
    ' 构造外部引用的VLOOKUP公式（注意路径和表名的单引号包裹）
    vlookupFormula = "VLOOKUP(""" & axsunpart & """, '" & lookupPath & "'!" & lookupRange & ", " & position & ", FALSE)"
    
    ' 执行公式并处理结果
    Dim result As Variant
    result = Application.Evaluate(vlookupFormula)
    
    ' 处理查找不到的情况
    GetScopeFilename = IIf(IsError(result), "", result)
End Function

注意点：

• 如果文件路径包含空格，必须用单引号包裹整个[文件路径]工作表名部分
• 用Application.Evaluate替代WorksheetFunction.VLookup，避免找不到值时直接抛出运行时错误

方法2：使用ADO连接查询（适合复杂场景） #

通过ADO直接读取Excel文件的数据，相当于后台执行数据库查询，完全不打开Excel窗口，适合多函数复用或大数据量查询场景。

先封装一个通用查询函数，再在业务函数中调用：

' 通用函数：从关闭的Excel文件中查询单个值
Function QueryClosedExcel(filePath As String, sqlQuery As String) As Variant
    Dim conn As Object, rs As Object
    Dim result As Variant
    
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    ' 适配Excel 2007及以上版本的连接字符串
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    
    ' 执行SQL查询
    rs.Open sqlQuery, conn
    
    ' 获取查询结果
    result = IIf(rs.EOF, "", rs.Fields(0).Value)
    
    ' 清理资源
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    
    QueryClosedExcel = result
End Function

' 修改后的业务函数
Function GetScopeFilename(axsunpart As String, sweeprate As Double)
    Dim position As Long
    Dim filePath As String
    Dim sql As String
    
    filePath = "C:\Users\Documents\LookupTable.xlsx"
    
    ' 确定返回列位置
    Select Case sweeprate
        Case 50
            position = 2
        Case 100
            position = 3
        Case 200
            position = 4
        Case Else
            MsgBox "未指定有效扫描速率，请检查参数后重试。"
            GetScopeFilename = ""
            Exit Function
    End Select
    
    ' 构造SQL语句（F1对应A列，F2对应B列，以此类推；HDR=YES时也可以用表头名称）
    sql = "SELECT F" & position & " FROM [Scope Filename$A1:D4] WHERE F1 = '" & axsunpart & "'"
    
    ' 调用通用查询函数
    GetScopeFilename = QueryClosedExcel(filePath, sql)
End Function

注意点：

• 如果Excel文件没有表头，将连接字符串中的HDR=YES改为HDR=NO
• 确保目标文件未被其他程序锁定，否则会连接失败

方法3：后台静默打开工作簿（备选方案） #

如果以上两种方法不适用，可以用GetObject在后台打开工作簿（不显示窗口），用完后立即关闭，比直接Workbooks.Open减少屏幕闪烁和交互开销：

Function GetScopeFilename(axsunpart As String, sweeprate As Double)
    Dim wbSrc As Workbook, ws As Worksheet, position As Long
    Dim filePath As String
    
    filePath = "C:\Users\Documents\LookupTable.xlsx"
    
    ' 关闭屏幕更新，避免闪烁
    Application.ScreenUpdating = False
    ' 后台打开工作簿
    Set wbSrc = GetObject(filePath)
    Set ws = wbSrc.Worksheets("Scope Filename")
    
    ' 确定返回列位置
    Select Case sweeprate
        Case 50
            position = 2
        Case 100
            position = 3
        Case 200
            position = 4
        Case Else
            MsgBox "未指定有效扫描速率，请检查参数后重试。"
            GetScopeFilename = ""
            ' 清理资源并恢复设置
            wbSrc.Close SaveChanges:=False
            Application.ScreenUpdating = True
            Exit Function
    End Select
    
    ' 执行VLookup并处理错误
    Dim result As Variant
    result = Application.VLookup(axsunpart, ws.Range("A1:D4"), position, False)
    GetScopeFilename = IIf(IsError(result), "", result)
    
    ' 关闭工作簿，不保存
    wbSrc.Close SaveChanges:=False
    ' 恢复屏幕更新
    Application.ScreenUpdating = True
End Function

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