VBA跨工作簿取值函数返回#VALUE!错误求助排查

核心问题分析 #

• 路径格式错误：你传给Workbooks.Open的路径带了[Wb1.xlsx]，这是Excel单元格直接引用外部文件的格式，不是VBA打开文件的正确路径，正确路径应为完整的文件路径（如"X:\Folder1\2024\Folder2\Wb1.xlsx"）。
• UDF执行限制：在单元格公式中使用UDF打开工作簿，Excel的安全机制或计算上下文可能会阻止操作；且200个单元格反复打开关闭工作簿，会导致效率极低。
• 公式语法错误：你的公式中&Zdroje!$A56&"13"前面多了一个&，正确写法是Zdroje!$A56&"13"。

修正后的UDF代码（带错误处理） #

添加错误捕获可以避免返回#VALUE!，同时给出明确的错误提示：

Function get_ExtVal(file As String, sheet As String, cell As String) As Variant
    Dim Wb1 As Workbook
    Dim value As Variant
    
    ' 关闭屏幕更新和事件，避免干扰
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    On Error GoTo ErrorHandler ' 捕获错误
    
    ' 打开工作簿（注意路径不能带[]）
    Set Wb1 = Workbooks.Open(file, ReadOnly:=True)
    ' 获取单元格值
    value = Wb1.Sheets(sheet).Range(cell).Value
    
Cleanup:
    ' 确保工作簿关闭，恢复设置
    If Not Wb1 Is Nothing Then Wb1.Close SaveChanges:=False
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    get_ExtVal = value
    Exit Function

ErrorHandler:
    ' 错误时返回提示
    value = "错误：" & Err.Description
    Resume Cleanup
End Function

修正后的单元格公式 #

把路径改成不带[]的完整路径，同时修正拼接语法：

=IF(get_ExtVal("X:\Folder1\2024\Folder2\Wb1.xlsx"; "Sheet1"; Zdroje!$A56&"13") > 0; get_ExtVal("X:\Folder1\2024\Folder2\Wb1.xlsx"; "Sheet1"; Zdroje!$A56&"218"); "")

更高效的批量处理方案 #

如果要处理200个单元格，反复打开关闭工作簿会非常慢，建议一次性读取所有需要的数据到内存数组，再用函数调用：

1. 在模块中添加全局数组存储数据：

Dim extData As Variant ' 存储外部工作簿的数据
Dim extWbPath As String ' 记录已加载的文件路径

Sub LoadExtData(filePath As String, sheetName As String)
    Dim Wb1 As Workbook
    Application.ScreenUpdating = False
    Set Wb1 = Workbooks.Open(filePath, ReadOnly:=True)
    ' 读取整个工作表数据到数组（按需调整范围，比如UsedRange）
    extData = Wb1.Sheets(sheetName).UsedRange.Value
    extWbPath = filePath
    Wb1.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub

2. 修改UDF优先使用内存中的数据：

Function get_ExtValFast(file As String, sheet As String, cell As String) As Variant
    Dim rng As Range
    ' 如果数据未加载或路径变化，重新加载
    If extWbPath <> file Then
        LoadExtData file, sheet
    End If
    
    On Error GoTo ErrorHandler
    Set rng = Range(cell)
    ' 从数组中取值（注意数组下标从1开始）
    get_ExtValFast = extData(rng.Row, rng.Column)
    Exit Function

ErrorHandler:
    get_ExtValFast = "错误：单元格不存在"
End Function

3. 使用前先运行LoadExtData加载数据，然后在单元格中调用：

=IF(get_ExtValFast("X:\Folder1\2024\Folder2\Wb1.xlsx"; "Sheet1"; Zdroje!$A56&"13") > 0; get_ExtValFast("X:\Folder1\2024\Folder2\Wb1.xlsx"; "Sheet1"; Zdroje!$A56&"218"); "")

额外注意事项 #

• 确保外部文件路径正确，文件未被其他程序锁定。
• 如果Excel启用了宏安全限制，需要允许宏运行。
• Wb1是否支持宏不影响读取单元格值，因为只是读取数据，不需要执行Wb1中的宏。

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