Excel VBA函数在Sub中正常运行，工作表调用时返回#VALUE!错误

核心原因 #

1. UDF禁止交互式操作：当在工作表单元格调用自定义函数（UDF）时，Excel不允许函数执行MsgBox这类弹窗交互，一旦触发就会直接返回#VALUE!错误——而测试用的Sub是独立宏，不受这个限制，所以能正常运行。
2. UDF中打开外部工作簿的风险：在UDF的计算上下文里打开外部工作簿，容易引发计算冲突，还会拖慢Excel的计算效率，甚至触发未知错误。

解决方法 #

方法1：移除弹窗，优化错误处理（快速修复） #

把函数里的MsgBox换成返回Excel错误值的逻辑，同时添加稳定性优化：

Function SumNumberWorkbook(workbookName As String, zelle As String, gesuchteZahl As String) As Variant
    Dim total As Long
    Dim targetWorkbook As Workbook
    Dim targetFilePath As String
    Dim ws As Worksheet
    
    total = 0
    targetFilePath = ThisWorkbook.Path & "\" & workbookName
    
    If Dir(targetFilePath) <> "" Then
        ' 临时禁用屏幕更新和事件，减少干扰
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        ' 以只读模式打开目标工作簿，避免锁定
        Set targetWorkbook = Workbooks.Open(targetFilePath, ReadOnly:=True)
        For Each ws In targetWorkbook.Sheets
            If Not ws Is Nothing Then
                ' 先判断单元格是否为空，避免空值报错
                If Not IsEmpty(ws.Range(zelle).Value) Then
                    If InStr(1, CStr(ws.Range(zelle).Value), gesuchteZahl, vbTextCompare) > 0 Then
                        total = total + 1
                    End If
                End If
            End If
        Next ws
        targetWorkbook.Close False
        
        ' 恢复正常设置
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    Else
        ' 返回Excel标准错误值，替代弹窗
        SumNumberWorkbook = CVErr(xlErrValue)
        Exit Function
    End If
    SumNumberWorkbook = total
End Function

方法2：用ExecuteExcel4Macro读取数据（推荐） #

这种方法不需要打开外部工作簿，完全适配UDF的运行环境，更稳定高效：

Function SumNumberWorkbook(workbookName As String, zelle As String, gesuchteZahl As String) As Variant
    Dim total As Long
    Dim targetFilePath As String
    Dim wsName As String
    Dim targetCellValue As Variant
    Dim sheetCount As Integer
    Dim i As Integer
    
    total = 0
    targetFilePath = ThisWorkbook.Path & "\" & workbookName
    
    ' 先检查文件是否存在
    If Dir(targetFilePath) = "" Then
        SumNumberWorkbook = CVErr(xlErrValue)
        Exit Function
    End If
    
    ' 获取目标工作簿的工作表总数
    sheetCount = ExecuteExcel4Macro("GET.WORKBOOK(1,'" & targetFilePath & "')")
    
    For i = 1 To sheetCount
        ' 获取第i个工作表的名称
        wsName = ExecuteExcel4Macro("GET.WORKBOOK(2,'" & targetFilePath & "'," & i & ")")
        ' 读取指定单元格的值，捕获可能的错误
        On Error Resume Next
        targetCellValue = ExecuteExcel4Macro("'" & targetFilePath & "]" & wsName & "'!" & zelle)
        On Error GoTo 0
        
        ' 判断是否包含目标数字
        If Not IsError(targetCellValue) Then
            If InStr(1, CStr(targetCellValue), gesuchteZahl, vbTextCompare) > 0 Then
                total = total + 1
            End If
        End If
    Next i
    
    SumNumberWorkbook = total
End Function

额外注意点 #

• 工作表公式的参数分隔符要匹配Excel语言版本：你用的;是德文版本的正确分隔符，无需修改。
• 方法2可以读取隐藏工作表的内容，不需要额外设置。

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