Excel自定义函数C_VaR调试正常但单元格调用返回#VALUE!错误求助

问题背景 #

我编写了一个计算条件VaR的Excel自定义函数（UDF）C_VaR，它基于指定的起止日期（StartDate、EndDate）、ISIN代码和百分位值计算结果，价格数据存储在名为Cijene工作表的tblCijene表格中。该函数在VBA调试模式下运行正常，但在单元格中调用时会抛出#VALUE!错误。添加错误处理后发现，问题出在设置rngWorkRange的代码行，报错信息为Object variable or With block variable not set。

错误原因分析 #

这个错误的核心原因是：rngStartDate、rngEndDate或rngTargetFund其中一个对象未被正确赋值（即Find方法未找到匹配项，返回了Nothing），导致后续调用它们的Row/Column属性时触发对象引用错误。调试模式与UDF调用模式的行为差异，通常由以下几点导致：

• Find方法在UDF环境下的默认参数（如匹配方式、大小写敏感度）与调试时不一致
• 日期格式不匹配导致日期查找失败
• 部分匹配而非完全匹配，导致找到错误的目标列或日期行

解决思路及修正方案 #

1. 优化Find方法的匹配逻辑 #

为Find方法明确指定关键参数，确保匹配逻辑稳定：

• 使用LookAt:=xlWhole强制完全匹配，避免部分匹配导致的错误结果
• 添加MatchCase:=False取消大小写敏感，适配ISIN代码的大小写不统一情况
• 日期查找时将日期转为序列号（CLng(DateValue)），避免格式差异导致的匹配失败
• 指定LookIn:=xlValues而非xlFormulas，确保基于单元格实际值查找

2. 添加对象存在性检查 #

在构建rngWorkRange之前，先检查三个关键查找对象是否不为Nothing，若有任意一个为空，直接返回#VALUE!错误，避免后续代码崩溃。同时添加起止日期的行号校验，确保起始日期在结束日期之前。

3. 优化参数传递类型 #

将原函数的Range类型参数改为对应的值类型（日期用Date，ISIN用String，百分位值用Double），减少UDF环境下Range对象的潜在兼容性问题，同时简化代码逻辑。

4. 补充边界错误处理 #

添加除以0的校验（当没有收益率低于百分位数时），避免触发#DIV/0!错误。

修正后的完整代码 #

Function C_VaR(ByVal StartDate As Date, ByVal EndDate As Date, ByVal ISIN As String, ByVal PercentileValue As Double) As Variant
    Dim rngStartDate As Range
    Dim rngEndDate As Range
    Dim rngTargetFund As Range
    Dim wsPrices As Worksheet
    Dim tbl As ListObject
    Dim rngDates As Range
    Dim rngFunds As Range
    Dim rngWorkRange As Range
    Dim arrTemp() As Variant
    Dim arrPrices() As Variant
    Dim arrReturns() As Variant
    Dim i As Long
    Dim dblPercentile As Double
    Dim dblSum As Double
    Dim lngCounter As Long
    
    On Error GoTo Errhandler
    
    ' 初始化工作表和表格对象
    Set wsPrices = ThisWorkbook.Worksheets("Cijene")
    Set tbl = wsPrices.ListObjects("tblCijene")
    Set rngDates = tbl.ListColumns(1).Range
    Set rngFunds = tbl.HeaderRowRange
    
    ' 查找起止日期，转成序列号确保格式匹配
    Set rngStartDate = rngDates.Find(what:=CLng(StartDate), LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    Set rngEndDate = rngDates.Find(what:=CLng(EndDate), LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    ' 查找目标ISIN，完全匹配不区分大小写
    Set rngTargetFund = rngFunds.Find(what:=ISIN, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    
    ' 检查关键对象是否存在
    If rngStartDate Is Nothing Or rngEndDate Is Nothing Or rngTargetFund Is Nothing Then
        C_VaR = CVErr(xlErrValue)
        Exit Function
    End If
    
    ' 确保起始日期在结束日期之前
    If rngStartDate.Row > rngEndDate.Row Then
        C_VaR = CVErr(xlErrNum)
        Exit Function
    End If
    
    Set rngWorkRange = wsPrices.Range(wsPrices.Cells(rngStartDate.Row, rngTargetFund.Column), wsPrices.Cells(rngEndDate.Row, rngTargetFund.Column))
    
    ' 读取价格数据到数组
    arrTemp = rngWorkRange.Value
    ReDim arrPrices(1 To UBound(arrTemp, 1))
    For i = 1 To UBound(arrPrices)
        arrPrices(i) = arrTemp(i, 1)
    Next i
    
    ' 计算收益率数组
    ReDim arrReturns(1 To UBound(arrPrices) - 1)
    For i = 1 To UBound(arrReturns)
        arrReturns(i) = arrPrices(i + 1) / arrPrices(i) - 1
    Next i
    
    ' 计算百分位数及条件VaR
    dblPercentile = Application.WorksheetFunction.Percentile_Exc(arrReturns, PercentileValue)
    dblSum = 0
    lngCounter = 0
    For i = LBound(arrReturns) To UBound(arrReturns)
        If arrReturns(i) < dblPercentile Then
            dblSum = dblSum + arrReturns(i)
            lngCounter = lngCounter + 1
        End If
    Next i
    
    ' 处理无匹配收益率的情况
    If lngCounter = 0 Then
        C_VaR = CVErr(xlErrDiv0)
    Else
        C_VaR = dblSum / lngCounter * (-1)
    End If
    
    Exit Function
    
Errhandler:
    C_VaR = CVErr(xlErrValue)
End Function

内容的提问来源于stack exchange，提问作者Jurica Božić