VBA UDF返回#VALUE! [英] VBA UDF returning #VALUE!
问题描述
所以我有一个非常简单的UDF,用Visual Basic编写,用于在excel中使用。它计算你的约税。让我们说我这样使用:
So I have a pretty simple UDF written in visual basic for use in excel. It calculates your approx. taxes. Lets say I use it as such:
=Taxes(I23-I18,I24-I20,"Married")
如果我在其中输入内容很好。现在如果我保存表并重新启动excel,现在单元格#VALUE!如果我选择公式并再次输入,它会重新计算它。我究竟做错了什么? Application.Volatile不应该需要,但我正在尝试想法...
If I type this in it works great. Now if I save the sheet and restart excel the cell now says #VALUE! If I select the formula and hit enter once again it recalculates it fine. What am I doing wrong? Application.Volatile shouldn't be needed but I was trying ideas...
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, brackets, 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, brackets, 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
推荐答案
除了使用货币数据类型(可能应该使用双精度或变体,因为这是Excel使用的),您的UDF的外观确定。
通过UDF获取#Value的通常原因是其中一个输入参数无法转换为正确的类型。如果您的输入单元格在工作簿打开时不包含数值,您将获得#Value。
这可能是由于计算序列问题导致的,其中一个上游先例单元在第一次调用该函数时未被计算。 br />尝试将输入参数声明为变量而不是货币,并添加一些临时debug.print语句以在立即窗口中显示输入参数。
Your UDF's look OK, apart from using Currency data types (probably should be using doubles or variants since that is what Excel uses).
The usual reason for getting #Value with a UDF is that one of the input arguments cannot be converted to the correct type. If your input cells do not contain numeric values when the workbook opens you would get #Value.
This might be caused by calculation sequence problems resulting in one of the upstream precedent cells being uncalculated the first time the function is called.
Try declaring the input parameters as variant rather than currency and add some temporary debug.print statements to show the input parameters in the Immediate window.
这篇关于VBA UDF返回#VALUE!的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!