为什么函数中的Excel VBA Select Case语句出现#Value错误？

k4emjkb1 于 2023-03-20 发布在其他

关注(0)|答案(1)|浏览(132)

尝试在单元格调用的用户定义函数中执行选择案例，以计算传递参数的联邦税额，但在所述调用单元格中得到#value错误。
调试始终在第一个case语句处结束case块和函数，而不管作为函数参数传递的值是什么
Referenced Table, tab name, #value error, and calling function used

Option Explicit

Public Function FedTaxMFJ(TaxableAmt As Double) As Double

Dim Brkt1Max As Double, Brkt2Max As Double, Brkt3Max As Double, Brkt4Max As Double, Brkt5Max As Double, Brkt6Max As Double, Brkt7Max As Double
Brkt1Max = ThisWorkbook.Worksheets("Taxes_Setup").Range("A3").Value * ThisWorkbook.Worksheets("Taxes_Setup").Range("E3").Value
Brkt2Max = ThisWorkbook.Worksheets("Taxes_Setup").Range("A4").Value * ThisWorkbook.Worksheets("Taxes_Setup").Range("E4").Value
Brkt3Max = ThisWorkbook.Worksheets("Taxes_Setup").Range("A5").Value * ThisWorkbook.Worksheets("Taxes_Setup").Range("E5").Value
Brkt4Max = ThisWorkbook.Worksheets("Taxes_Setup").Range("A6").Value * ThisWorkbook.Worksheets("Taxes_Setup").Range("E6").Value
Brkt5Max = ThisWorkbook.Worksheets("Taxes_Setup").Range("A7").Value * ThisWorkbook.Worksheets("Taxes_Setup").Range("E7").Value
Brkt6Max = ThisWorkbook.Worksheets("Taxes_Setup").Range("A8").Value * ThisWorkbook.Worksheets("Taxes_Setup").Range("E8").Value

    Select Case TaxableAmt
    
        Case ThisWorkbook.Worksheets("Taxes_Setup").Range("D3").Value To ThisWorkbook.Worksheets("Taxes_Setup").Range("E3").Value
            FedTaxMFJ = ThisWorkbook.Worksheets("Taxes_Setup").Range("A3").Value * (TaxableAmt - ThisWorkbook.Worksheets("Taxes_Setup").Range("D3").Value)
            
            
        Case ThisWorkbook.Worksheets("Taxes_Setup").Range("D4").Value To ThisWorkbook.Worksheets("Taxes_Setup").Range("E4").Value
            FedTaxMFJ = Brkt1Max + ThisWorkbook.Worksheets("Taxes_Setup").Range("A4").Value * (TaxableAmt - ThisWorkbook.Worksheets("Taxes_Setup").Range("D4").Value)
            
        
        Case ThisWorkbook.Worksheets("Taxes_Setup").Range("D5").Value To ThisWorkbook.Worksheets("Taxes_Setup").Range("E5").Value
            FedTaxMFJ = Brkt1Max + Brkt2Max + ThisWorkbook.Worksheets("Taxes_Setup").Range("A5").Value * (TaxableAmt - ThisWorkbook.Worksheets("Taxes_Setup").Range("D5").Value)
                      
            
        Case ThisWorkbook.Worksheets("Taxes_Setup").Range("D6").Value To ThisWorkbook.Worksheets("Taxes_Setup").Range("E6").Value
            FedTaxMFJ = Brkt1Max + Brkt2Max + Brkt3Max + ThisWorkbook.Worksheets("Taxes_Setup").Range("A6").Value * (TaxableAmt - ThisWorkbook.Worksheets("Taxes_Setup").Range("D6").Value)
            
            
        Case ThisWorkbook.Worksheets("Taxes_Setup").Range("D7").Value To ThisWorkbook.Worksheets("Taxes_Setup").Range("E7").Value
            FedTaxMFJ = Brkt1Max + Brkt2Max + Brkt3Max + Brkt4Max + Worksheets("Taxes_Setup").Range("A7") * (TaxableAmt - Worksheets("Taxes_Setup").Range("D7"))
            
        
        Case ThisWorkbook.Worksheets("Taxes_Setup").Range("D8").Value To ThisWorkbook.Worksheets("Taxes_Setup").Range("E8").Value
            FedTaxMFJ = Brkt1Max + Brkt2Max + Brkt3Max + Brkt4Max + Brkt5Max + ThisWorkbook.Worksheets("Taxes_Setup").Range("A8").Value * (TaxableAmt - ThisWorkbook.Worksheets("Taxes_Setup").Range("D8").Value)
            
            
        Case ThisWorkbook.Worksheets("Taxes_Setup").Range("D9").Value To ThisWorkbook.Worksheets("Taxes_Setup").Range("E9").Value
            FedTaxMFJ = Brkt1Max + Brkt2Max + Brkt3Max + Brkt4Max + Brkt5Max + Brkt6Max + ThisWorkbook.Worksheets("Taxes_Setup").Range("A9").Value * (TaxableAmt - ThisWorkbook.Worksheets("Taxes_Setup").Range("D9").Value)
            
    End Select
            
End Function

excel

来源：https://stackoverflow.com/questions/75717623/why-am-i-getting-a-value-error-from-an-excel-vba-select-case-statement-in-funct

1条答案

按热度按时间

zaqlnxep1#

解决方案是声明并初始化case语句代码中使用的所有range对象。例如：

tenpctlow = ThisWorkbook.Worksheets("Taxes_Setup").Range("D3").Value
tenpcthigh = ThisWorkbook.Worksheets("Taxes_Setup").Range("E3").Value
tenpct = ThisWorkbook.Worksheets("Taxes_Setup").Range("A3").Value

Select Case TaxableAmt
    Case tenpctlow To tenpcthigh
        FedTaxMFJ = tenpct * (TaxableAmt - tenpctlow)

赞(0）回复(0）举报 2023-03-20

我来回答

为什么函数中的Excel VBA Select Case语句出现#Value错误？

1条答案

相关问题

热门标签

最新问答