所以我有一个非常简单的用Visual Basic编写的UDF,用于excel.它计算你的约.税.让我说我这样使用它：
=Taxes(I23-I18,I24-I20,"Married")
如果我输入它,效果很好.现在如果我保存工作表并重新启动excel,单元格现在显示#VALUE！如果我选择公式并再次按回车键,则重新计算它.我究竟做错了什么？不应该使用Application.Volatile但我正在尝试创意……
Private Type TaxBracket
Perc As Double
Floor As Currency
Limit As Currency
End Type
Public Function Taxes(gross1 As Currency,gross2 As Currency,filingStatus As String) As Currency
Application.Volatile True
Dim brackets(6) As TaxBracket
Dim stdDeduction As Currency
Dim ssTaxRate As Double
Dim medicareTaxRate As Double
Dim Tax As Double
stdDeduction = 5700
ssTaxRoof = 106800
ssTaxRate = 0.062
medicareTaxRate = 0.0145
Tax = medicareTaxRate * (gross1 + gross2)
Tax = Tax + IIf(gross1 < ssTaxRoof,ssTaxRate * gross1,ssTaxRate * ssTaxRoof)
Tax = Tax + IIf(gross2 < ssTaxRoof,ssTaxRate * gross2,ssTaxRate * ssTaxRoof)
brackets(0).Perc = 0.1
brackets(1).Perc = 0.15
brackets(2).Perc = 0.25
brackets(3).Perc = 0.28
brackets(4).Perc = 0.33
brackets(5).Perc = 0.35
If filingStatus = "Single" Then
brackets(0).Floor = 0
brackets(1).Floor = 8375
brackets(2).Floor = 34000
brackets(3).Floor = 82400
brackets(4).Floor = 171850
brackets(5).Floor = 373650
brackets(0).Limit = 8375
brackets(1).Limit = 34000
brackets(2).Limit = 82400
brackets(3).Limit = 171850
brackets(4).Limit = 373650
brackets(5).Limit = 1000000000
Tax = Tax + incoMetaxes(gross1,brackets,stdDeduction) + incoMetaxes(gross2,stdDeduction)
ElseIf filingStatus = "Married" Then
brackets(0).Floor = 0
brackets(1).Floor = 16750
brackets(2).Floor = 68000
brackets(3).Floor = 137300
brackets(4).Floor = 209250
brackets(5).Floor = 373650
brackets(0).Limit = 16750
brackets(1).Limit = 68000
brackets(2).Limit = 137300
brackets(3).Limit = 209250
brackets(4).Limit = 373650
brackets(5).Limit = 1000000000
Tax = Tax + incoMetaxes(gross1 + gross2,stdDeduction * 2)
Else
Taxes = "N/A"
Return
End If
Taxes = Tax
End Function
Private Function incoMetaxes(gross As Currency,brackets() As TaxBracket,deduction As Currency) As Currency
Dim Tax As Double
Dim taxable As Double
Tax = 0
taxable = gross - deduction
For i = 0 To 5
If taxable > brackets(i).Limit Then
Tax = Tax + (WorksheetFunction.Min(taxable,brackets(i).Limit) - brackets(i).Floor) * brackets(i).Perc
Else
If taxable > brackets(i).Floor Then
Tax = Tax + (taxable - brackets(i).Floor) * brackets(i).Perc
Else
'tax = tax
End If
End If
Next i
incoMetaxes = Tax
End Function