添加LGDCategory参数后VBA函数返回#VALUE!的修复咨询

问题描述 #

原代码未加入LGDCategory变量时可正常运行，但添加该变量后，代码返回#VALUE!错误。实际代码包含大量If-Then语句，相关代码片段如下：

Option Explicit
Option Compare Text
Function Default(RiskCategory As String, LGDCategory As String, Rating As String, Duration As Double)
SDuration2 = WorksheetFunction.Max(1, Duration)
Dim Duration_rounded_up As Double, Duration_rounded_down As Double
Duration_rounded_up = WorksheetFunction.RoundUp(Duration, 0)
Duration_rounded_down = WorksheetFunction.RoundDown(Duration, 0)
Dim PDCumulative As Double

If RiskCategory = "Corporate" And LGDCategory = "1stLienBond" Then
    If Rating = "AAA" Then
        If SDuration2 >= 1 And SDuration2 < 2 Then
            PDCumulative = (0.000102 - 0) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 1) + 0
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 2 And SDuration2 < 3 Then
            PDCumulative = (0.000102 - 0.000102) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 2) + 0.000102
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 3 And SDuration2 < 4 Then
            PDCumulative = (0.00029 - 0.000102) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 3) + 0.000102
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 4 And SDuration2 < 5 Then
            PDCumulative = (0.000801 - 0.00029) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 4) + 0.00029
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 5 And SDuration2 < 6 Then
            PDCumulative = (0.001296 - 0.000801) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 5) + 0.000801
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 6 And SDuration2 < 7 Then
            PDCumulative = (0.001815 - 0.001296) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 6) + 0.001296
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 7 And SDuration2 < 8 Then
            PDCumulative = (0.002362 - 0.001815) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 7) + 0.001815
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 8 And SDuration2 < 9 Then
            PDCumulative = (0.002937 - 0.002362) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 8) + 0.002362
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 9 And SDuration2 < 10 Then
            PDCumulative = (0.003543 - 0.002937) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 9) + 0.002937
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 10 And SDuration2 < 11 Then
            PDCumulative = (0.004182 - 0.003543) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 10) + 0.003543
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 11 And SDuration2 < 12 Then
            PDCumulative = (0.004854 - 0.004182) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 11) + 0.004182
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 12 And SDuration2 < 13 Then
            PDCumulative = (0.005535 - 0.004854) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 12) + 0.004854
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 13 And SDuration2 < 14 Then
            PDCumulative = (0.005915 - 0.005535) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 13) + 0.005535
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 14 And SDuration2 < 15 Then
            PDCumulative = (0.00632 - 0.005915) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 14) + 0.005915
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 15 And SDuration2 < 16 Then
            PDCumulative = (0.00675 - 0.00632) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 15) + 0.00632
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 16 And SDuration2 < 17 Then
            PDCumulative = (0.007206 - 0.00675) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 16) + 0.00675
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 17 And SDuration2 < 18 Then
            PDCumulative = (0.007365 - 0.007206) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 17) + 0.007206
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 18 And SDuration2 < 19 Then
            PDCumulative = (0.007365 - 0.007365) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 18) + 0.007365
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 19 And SDuration2 < 20 Then
            PDCumulative = (0.007365 - 0.007365) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 19) + 0.007365
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 19 And SDuration2 < 20 Then
            PDCumulative = 0.007365
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
        ElseIf SDuration2 >= 20 And SDuration2 < 50 Then
            PDCumulative = 1 - (1 - 0.007365) * ((1 - 0.007365) / (1 - 0.007365)) ^ (Duration - 20)
            Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
End If
End If
End If

错误原因分析 #

1. 除零运算错误：当Duration为整数时，Duration_rounded_up - Duration_rounded_down结果为0，此时计算PDCumulative会触发除零错误，直接返回#VALUE!。
2. 未覆盖所有代码路径：当RiskCategory不等于"Corporate"、LGDCategory不等于"1stLienBond"，或Rating不等于"AAA"时，函数未给Default赋值，Excel会因函数无返回值显示#VALUE!。
3. 重复条件判断：代码中存在两个完全相同的ElseIf SDuration2 >= 19 And SDuration2 < 20分支，逻辑冗余可能导致意外执行问题。
4. 参数传递问题：调用函数时，LGDCategory参数可能为空、类型不匹配（如传入数值而非字符串），导致条件判断失败。

解决方法 #

1. 处理除零情况 #

在计算PDCumulative前，先判断差值是否为0，避免除零：

If Duration_rounded_up - Duration_rounded_down <> 0 Then
    PDCumulative = (上限值 - 下限值) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 区间起始) + 下限值
Else
    PDCumulative = 上限值 '整数时直接取区间上限值
End If

2. 添加默认返回值 #

在函数开头初始化Default为默认值，确保所有路径都有返回结果：

Function Default(RiskCategory As String, LGDCategory As String, Rating As String, Duration As Double) As Double
    '初始化默认返回值
    Default = 0
    '后续代码...
End Function

3. 修正重复条件 #

删除重复的ElseIf SDuration2 >= 19 And SDuration2 < 20分支，简化逻辑。

4. 验证输入参数 #

确保调用函数时，LGDCategory参数传入有效的字符串值，无空值或类型错误。

修正后代码示例 #

Option Explicit
Option Compare Text
Function Default(RiskCategory As String, LGDCategory As String, Rating As String, Duration As Double) As Double
    Dim SDuration2 As Double
    SDuration2 = WorksheetFunction.Max(1, Duration)
    Dim Duration_rounded_up As Double, Duration_rounded_down As Double
    Duration_rounded_up = WorksheetFunction.RoundUp(Duration, 0)
    Duration_rounded_down = WorksheetFunction.RoundDown(Duration, 0)
    Dim PDCumulative As Double
    
    '初始化默认返回值
    Default = 0

    If RiskCategory = "Corporate" And LGDCategory = "1stLienBond" Then
        If Rating = "AAA" Then
            If SDuration2 >= 1 And SDuration2 < 2 Then
                If Duration_rounded_up - Duration_rounded_down <> 0 Then
                    PDCumulative = (0.000102 - 0) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 1)
                Else
                    PDCumulative = 0.000102
                End If
                Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
            ElseIf SDuration2 >= 2 And SDuration2 < 3 Then
                PDCumulative = 0.000102
                Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
            '其他区间按相同逻辑处理除零情况...
            ElseIf SDuration2 >= 20 And SDuration2 < 50 Then
                PDCumulative = 1 - (1 - 0.007365) * ((1 - 0.007365) / (1 - 0.007365)) ^ (Duration - 20)
                Default = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.45396 * 10000
            End If
        End If
    End If
End Function

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