|
|
楼主 |
发表于 2007-7-27 11:16
|
显示全部楼层
<p>谢谢大家支持</p><p>实际工作中发现年终奖计税也有需要，所以把VBA方案作了一下改进，现和大家分享一下：</p><p>“工具”--》“宏”--》“Visual Basic编辑器”--》“插入”--》“模块”<br/><br/><font color="#c7c7c7">=============复制以下代码至编辑窗口================</font></p><p><font face="宋体" color="#000000"></font></p><p><font color="#000000"><font color="#008000">'计算个人收入调节税(Individual Income Adjustment Tax)<br/></font>Function iiatax(x, y, Optional z = 0)<br/></font><font color="#008000">'y=1 计算中国公民工薪所得税<br/>'y=2 计算外国公民工薪所得税<br/>'y=3 计算劳务所得税<br/>'z 可选，年终奖，用以计算年终奖所得税</font></p><p><font color="#000000">If IsNumeric(x) = False Then<br/> MsgBox ("请检查计税工资是否为数值！")<br/> iiatax = 0<br/> Exit Function<br/>End If</font></p><p><font color="#000000">If x < 0 Then<br/> MsgBox ("计税工资为负，重新输入！")<br/> iiatax = 0<br/> Exit Function<br/>End If</font></p><p><font color="#000000">Dim basicnum As Integer, annualbonus As Single<br/>Dim downnum As Variant, upnum As Variant, ratenum As Variant, deductnum As Variant, laowudeduct As Variant<br/>Dim gongxin!, laowu!, nianjiang!<br/>downnum = Array(0, 500, 2000, 5000, 20000, 40000, 60000, 80000, 100000) '定义累进区间下限<br/>upnum = Array(500, 2000, 5000, 20000, 40000, 60000, 80000, 100000, 100000000) '定义累进区间上限<br/>ratenum = Array(0.05, 0.1, 0.15, 0.2, 0.25, 0.3, 0.35, 0.4, 0.45) '定义累进税率<br/>deductnum = Array(0, 25, 125, 375, 1375, 3375, 6375, 10375, 15375) '定义累进速算扣除数</font></p><p><font color="#000000">Select Case y<br/> Case 1<br/> basicnum = 1600 '中国公民个税起征点<br/> Case 2<br/> basicnum = 4800 '外籍公民个税起征点<br/> Case 3 '劳务所得税<br/> downnum = Array(0, 4000, 20000, 50000)<br/> upnum = Array(4000, 20000, 50000, 100000000)<br/> ratenum = Array(0.2, 0.2, 0.3, 0.4)<br/> deductnum = Array(0, 0, 2000, 7000)<br/> laowudeduct = Array(800, x * 0.2, x * 0.2, x * 0.2)<br/> For i = 0 To UBound(downnum)<br/> If x > downnum(i) And x <= upnum(i) Then laowu = Round((x - laowudeduct(i)) * ratenum(i) - deductnum(i), 2)<br/> Next i<br/> Case Else<br/> MsgBox ("请输入合法参数！")<br/> Exit Function<br/>End Select</font></p><p><font color="#000000">If x >= 0 And x < basicnum Then<br/> gongxin = 0<br/>End If<br/>For i = 0 To UBound(downnum)<br/> If x - basicnum > downnum(i) And x - basicnum <= upnum(i) Then gongxin = Round((x - basicnum) * ratenum(i) - deductnum(i), 2)<br/>Next i</font></p><p><font color="#000000">If z <> 0 Then '有输入年终奖数据，计算年终奖税额<br/> If x < basicnum Then<br/> annualbonus = z - basicnum + x<br/> Else<br/> annualbonus = z<br/> End If<br/> For i = 0 To UBound(downnum)<br/> If annualbonus / 12 > downnum(i) And annualbonus / 12 <= upnum(i) Then nianjiang = Round(annualbonus * ratenum(i) - deductnum(i), 2)<br/> Next i<br/>Else<br/> nianjiang = 0<br/>End If</font></p><p><font color="#000000">If y = 1 Or y = 2 Then laowu = 0<br/>If y = 3 Then gongxin = 0<br/>iiatax = gongxin + laowu + nianjiang<br/>End Function</font><br/><br/><font color="#c7c7c7">=============复制以上代码至编辑窗口================</font></p><p><font color="#ff0000">应用示例——</font></p><p> </p>
[此贴子已经被作者于2007-9-21 12:35:02编辑过] |
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2007-3-6 10:31
</p>
发表于 2007-3-6 10:42
发表于 2007-3-6 12:10
