VBA自定义函数FirstDC调试正常，工作表调用返回#VALUE!

核心问题 #

工作表自定义函数（UDF）有严格的权限限制，无法执行修改Excel界面状态（如窗口可见性、屏幕刷新）的操作，同时原代码的工作簿引用、错误处理逻辑也存在问题，导致单元格调用时返回#VALUE!。

具体修复步骤 #

1. 移除UDF不支持的界面操作
   Excel禁止UDF修改ScreenUpdating、窗口可见性这类全局状态，直接删除以下代码：

Application.ScreenUpdating = False
Set win = ActiveWorkbook.Windows(1)
win.Visible = False
Application.ScreenUpdating = True

2. 正确打开并隐藏查找工作簿
   在Workbooks.Open时直接设置Visible:=False，无需后续修改窗口，同时添加ReadOnly:=True避免文件锁定：

Set wb = Workbooks.Open(Filename:="\\dtc\files\ECS-Customer\Enterprise Solution Design and Implementation\Macro Files\Serving DC Lookup.xlsx", _
                       ReadOnly:=True, Visible:=False)

3. 改用无错误的XLookup调用方式
   使用Application.XLookup替代WorksheetFunction.XLookup，前者找不到匹配时返回错误值而非抛出运行时错误，更适合UDF的错误判断：

Dim Result As Variant ' 改为Variant类型，容纳错误值
Result = Application.XLookup(SC, rngSC, rngDC, "N/A")

4. 优化错误处理与资源释放
   确保无论查询成功或失败，查找工作簿都会被关闭，避免残留。同时调整返回值逻辑，处理000的情况。

5. 处理参数为单元格的情况
   当函数参数是单元格引用时，先提取其值，避免类型不匹配：

If TypeName(SC) = "Range" Then SC = SC.Value

修复后的完整代码 #

Option Explicit
Function FirstDC(SC As Variant) As Variant
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rngSC As Range
    Dim rngDC As Range
    Dim Result As Variant
    
    ' 处理单元格引用参数
    If TypeName(SC) = "Range" Then
        SC = SC.Value
    End If
    
    ' 打开查找工作簿（只读、隐藏）
    On Error Resume Next
    Set wb = Workbooks.Open(Filename:="\\dtc\files\ECS-Customer\Enterprise Solution Design and Implementation\Macro Files\Serving DC Lookup.xlsx", _
                           ReadOnly:=True, Visible:=False)
    On Error GoTo ErrHandler
    
    If wb Is Nothing Then
        FirstDC = "N/A"
        Exit Function
    End If
    
    Set ws = wb.Sheets("Terms Load Points")
    Set rngSC = ws.Range("A2:A250")
    Set rngDC = ws.Range("F2:F250")
    
    ' 调用XLookup，找不到返回"N/A"
    Result = Application.XLookup(SC, rngSC, rngDC, "N/A")
    
    ' 格式化结果
    If Result <> "N/A" Then
        ' 处理空值或0的情况
        If Len(Trim(Result)) = 0 Or Result = 0 Then
            FirstDC = "N/A"
        Else
            FirstDC = "'" & Right("000" & Result, 3)
        End If
    Else
        FirstDC = "N/A"
    End If
    
CloseWB:
    ' 确保工作簿关闭
    If Not wb Is Nothing Then
        wb.Close SaveChanges:=False
    End If
    Exit Function
    
ErrHandler:
    FirstDC = "N/A"
    Resume CloseWB
End Function

补充说明 #

• 测试时确保查找工作簿的路径无误，且Excel有权限访问该网络路径。
• 如果查找工作簿经常被使用，可以考虑提前打开并隐藏，避免UDF每次调用都重复打开关闭，提升性能。

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