Excel VBA自定义函数中Select Case语句返回#Value错误排查

问题现象 #

在Excel单元格调用自定义函数FedTaxMFJ计算联合申报（MFJ）的联邦税额时，单元格返回#Value错误。调试发现，无论传入的TaxableAmt参数值是什么，代码都会在第一个Case语句处直接结束Case块和函数。

错误原因 #

1. 税级最大税额计算逻辑错误：原代码中BrktNMax直接用税率乘以税级上限，正确逻辑应为「税级区间长度×税率」，导致后续税额累加计算错误。
2. 单元格引用不规范：部分Range对象未读取.Value属性（如第五个Case中的Worksheets("Taxes_Setup").Range("A7")），导致引用对象而非数值，触发类型错误。
3. Select Case范围冗余且易出错：重复引用工作表单元格作为区间范围，不仅效率低，还可能因单元格值类型问题导致匹配异常；同时未处理超出所有税级的情况，无返回值时Excel会返回#Value。

修复方案 #

1. 预读取税级参数到变量：一次性读取所有税率、税级上下限到变量，避免重复访问工作表，减少错误概率。
2. 修正税级最大税额计算：按「(税级上限-税级下限)×税率」计算各税级的最大应缴税额。
3. 简化Select Case逻辑：利用税级递增特性，仅判断应纳税额是否小于等于当前税级上限，无需重复写完整区间。
4. 添加异常处理：针对超出税级范围或负数的应纳税额，设置默认返回值。

修复后的完整代码 #

Option Explicit

Public Function FedTaxMFJ(TaxableAmt As Double) As Double
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Taxes_Setup")
    
    ' 读取所有税级参数到变量
    Dim rate1 As Double, lower1 As Double, upper1 As Double
    Dim rate2 As Double, lower2 As Double, upper2 As Double
    Dim rate3 As Double, lower3 As Double, upper3 As Double
    Dim rate4 As Double, lower4 As Double, upper4 As Double
    Dim rate5 As Double, lower5 As Double, upper5 As Double
    Dim rate6 As Double, lower6 As Double, upper6 As Double
    Dim rate7 As Double, lower7 As Double, upper7 As Double
    
    rate1 = ws.Range("A3").Value
    lower1 = ws.Range("D3").Value
    upper1 = ws.Range("E3").Value
    
    rate2 = ws.Range("A4").Value
    lower2 = ws.Range("D4").Value
    upper2 = ws.Range("E4").Value
    
    rate3 = ws.Range("A5").Value
    lower3 = ws.Range("D5").Value
    upper3 = ws.Range("E5").Value
    
    rate4 = ws.Range("A6").Value
    lower4 = ws.Range("D6").Value
    upper4 = ws.Range("E6").Value
    
    rate5 = ws.Range("A7").Value
    lower5 = ws.Range("D7").Value
    upper5 = ws.Range("E7").Value
    
    rate6 = ws.Range("A8").Value
    lower6 = ws.Range("D8").Value
    upper6 = ws.Range("E8").Value
    
    rate7 = ws.Range("A9").Value
    lower7 = ws.Range("D9").Value
    upper7 = ws.Range("E9").Value
    
    ' 计算各税级的最大应缴税额
    Dim brkt1Max As Double, brkt2Max As Double, brkt3Max As Double
    Dim brkt4Max As Double, brkt5Max As Double, brkt6Max As Double
    
    brkt1Max = (upper1 - lower1) * rate1
    brkt2Max = (upper2 - lower2) * rate2
    brkt3Max = (upper3 - lower3) * rate3
    brkt4Max = (upper4 - lower4) * rate4
    brkt5Max = (upper5 - lower5) * rate5
    brkt6Max = (upper6 - lower6) * rate6
    
    ' 根据应纳税额计算最终税额
    Select Case TaxableAmt
        Case Is <= upper1
            FedTaxMFJ = (TaxableAmt - lower1) * rate1
        Case Is <= upper2
            FedTaxMFJ = brkt1Max + (TaxableAmt - lower2) * rate2
        Case Is <= upper3
            FedTaxMFJ = brkt1Max + brkt2Max + (TaxableAmt - lower3) * rate3
        Case Is <= upper4
            FedTaxMFJ = brkt1Max + brkt2Max + brkt3Max + (TaxableAmt - lower4) * rate4
        Case Is <= upper5
            FedTaxMFJ = brkt1Max + brkt2Max + brkt3Max + brkt4Max + (TaxableAmt - lower5) * rate5
        Case Is <= upper6
            FedTaxMFJ = brkt1Max + brkt2Max + brkt3Max + brkt4Max + brkt5Max + (TaxableAmt - lower6) * rate6
        Case Is >= lower7
            FedTaxMFJ = brkt1Max + brkt2Max + brkt3Max + brkt4Max + brkt5Max + brkt6Max + (TaxableAmt - lower7) * rate7
        Case Else
            ' 处理负数等异常值
            FedTaxMFJ = 0
    End Select
End Function

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