vb.net – VBA UDF返回#VALUE!
发布时间:2020-12-17 00:13:58 所属栏目:大数据 来源:网络整理
导读:所以我有一个非常简单的用Visual Basic编写的UDF,用于excel.它计算你的约.税.让我说我这样使用它: =Taxes(I23-I18,I24-I20,"Married") 如果我输入它,效果很好.现在如果我保存工作表并重新启动excel,单元格现在显示#VALUE!如果我选择公式并再次按回车键,则
所以我有一个非常简单的用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
除了使用货币数据类型之外,你的UDF看起来还不错(可能应该使用双打或变体,因为这是Excel使用的). 使用UDF获取#Value的通常原因是其中一个输入参数无法转换为正确的类型.如果输入单元格在工作簿打开时不包含数值,则会得到#Value.这可能是由计算序列问题引起的,导致上次先前单元格之一在第一次调用函数时未计算.尝试将输入参数声明为variant而不是currency,并添加一些临时的debug.print语句以在Immediate窗口中显示输入参数.
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
相关内容
- [bigdata-096] slf4j 一个抽象的java log 框架
- VB.NET GDI+ 天气预报
- mybatis文件映射之利用association进行关联查询(二)
- 宝塔部署时,出现“open_basedir restriction in effect”错
- hdu 1250 Hat's Fibonacci(大数加法)
- java – 无法解析org.springframework.context.Applicatio
- Golang+Android文件上传(多文件上传、附带请求参数)
- perl进程之间的perl Win32信号处理
- Delphi stdCall意义
- 用 Inkscape 做 SVG 给 TPath