Why am I getting a #Value error from an Excel VBA Select Case Statement in Function?

Question

Trying to do a select case in a user defined function called by a cell to calculate the federal tax amount for the passed argument, but getting a #value error in said calling cell.

Debugging always ends the case block and function at the first case statement regardless of the value passed as the function arg

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

Answer 1

The solution is to declare and initialize all of the range objects being used in the case statement code. For example:

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)