Excel-VBA基础（17）：创建自定义函数并存为模板调用示例

需求

日常工作中经常用到比Excel内置函数复杂的数据计算，而且使用频率非常高，有时候可以通过增加过渡数据，利用内置函数而解决，但有时候通过内置函数解决不了。此时可以用自定义函数的方法解决。

解决方案

在工作簿中增加一个模块，在其中用“Function 函数名称（参数列表）…End Function”定义一个函数过程，将Excel内置函数难以实现的功能都设置在函数过程中，在工作表中调用该函数即可。

将此工作簿另存为模板文件“*.xltm”，在新建工作簿时选择该模板，即可重用此前设计的自定义函数。

1、个人所得税的自定义函数应用

个人所得税计算公式：应交个人所得税＝计税工资×税率－速算扣除值

税率和速算扣除值根据计税工资而有不同的档次，如下表所示（示例用，非真实税率）。

计税工资（元）	税 率	速算扣除值（元）
500	0.05	0
2000	0.1	25
5000	0.15	125
20000	0.2	375
40000	0.25	1375
60000	0.3	3375
80000	0.35	6375

制作自定义函数

本实例设计流程：将个人所得税的计算公式移植到函数过程中，利用“Select Case…Case…End Select”控制语句，判断各档次计税工资对应的税率和速算扣除数值，然后根据公式“应交个人所得税＝计税工资×税率－速算扣除值”计算出个人所得税，将计算结果再返回自定义函数。

具体的操作过程如下：

1、新建工作簿，选择【开发工具】→【Visual Basic】命令，进入VBE编辑窗口

2、在工程资源窗口中的任一对象上右击，弹出快捷菜单，选择【插入】|【模块】命令，插入一个模块“模块1”，如下图所示。

3、在“模块1”中输入如下宏代码。

'将计税工资所在的单元格传值给函数
Function calTax(ByVal SalaryCell As Range)
     Dim TaxRatio As Single		'定义税率变量
     Dim Taxlng As Long			'定义速算扣除变量
     Dim Salary As Single		'定义计税工资变量
     
     '从计税工资单元格中取得应税工资数值
     Salary = SalaryCell.Value
     '判断计税工资所属的档次，每个档次对应不同的税率和速算扣除值
     Select Case Salary			'判断Salary变量值
     Case 0						'变量Salary＝0
          TaxRatio = 0
          Taxlng = 0
     Case Is <= 500				'变量Salary<= 500
          TaxRatio = 0.05
          Taxlng = 0
     Case Is <= 2000				'变量Salary<= 2000
          TaxRatio = 0.1
          Taxlng = 25
     Case Is <= 5000				'变量Salary<= 5000
          TaxRatio = 0.15
          Taxlng = 125
     Case Is <= 20000			'变量Salary<= 20000
          TaxRatio = 0.2
          Taxlng = 375
     Case Is <= 40000			'变量Salary<= 40000
          TaxRatio = 0.25
          Taxlng = 1375
     Case Is <= 60000			'变量Salary<= 60000
          TaxRatio = 0.3
          Taxlng = 3375
     Case Is <= 80000			'变量Salary<= 80000
          TaxRatio = 0.35
          Taxlng = 6375
     End Select
     '计税工资×税率－速算扣除值＝应交个人所得税
     calTax = Salary * TaxRatio - Taxlng
End Function

程序代码中重点语法介绍：

（1）函数中参数数值的传递有两种方法：按地址传递和按数值传递。

按地址传递参数（Byref）的方法效率较高，因为在过程内所有按地址传递的参数所花费的传递时间与空间的总数（4字节）都是一样的，而不管参数的数据类型。

按值传递参数（Byval）会在过程内部消耗掉2～16字节，取决于参数的数据类型。对于大的数据类型按值传递会比小的数据类型需要稍长的时间。因此，String和Variant数据类型通常不按值来传递参数。

使用按地址传递参数的方法，将使过程访问到实际的变量。结果，过程可改变变量的真正值，不声明传递参数的方法时，默认按地址传递参数。

使用按值的方法时会将原来的变量复制一份，当做参数传递过去，改变过程内部的参数时将不会影响到原来的变量。例如函数：

Function Factorial(ByVal MyVar As Integer)     '按值传递MyVar变量值
     MyVar = MyVar – 1　 'MyVar变量减去1
     If MyVar = 0 Then　 '判断MyVar变量值，如果等于0，给函数返回值1
         Factorial = 1
         Exit Function 　　'退出函数
     End If
     Factorial = Factorial(MyVar) * (MyVar + 1)　　 '调用函数自身循环
End Function “testVar”

再制作一个测试过程，调用函数Factorial，变量S经过函数调用后，数值发生改变，但Factorial函数并没有改变原来有数值。

Sub testVar()				'测试按值传递参数
     Dim S As Integer		'声明整数变量
     S = 5			'给测试变量赋值
     Debug.Print Factorial(S)	' 显示 120 （5 的因子）
     Debug.Print S		'显示 5，Factorial函数并没有改变原来有数值
End Sub

（2）利用Select…Case…End Select分支判断语句判断计税工资所属的档次，每个档次对应不同的税率和速算扣除值。

语句“Select Case Salary”首先确定判断条件是工资变量值“Salary”，然后用多个类似于“Case Is <= 5000”的语句判断工资变量“Salary”值是不是小于等于指定的数值，如果符合条件，则给税率变量“TaxRatio”和速算扣除变量“Taxlng”赋予不同的数值。

（3）函数的返回值。语句“calTax = Salary * TaxRatio - Taxlng”将个人所得税的计算结果赋予“calTax”函数本身，根据编程的需要，有时可以用类似于“Function calTax(ByVal SalaryCell As Range) as String ”的形式，直接指定函数“calTax”的类型。

调用自定义函数

如下所示，在工资表的T5单元格中输入自定义函数“=calTax(S5)”，单元格S5作为参数传递给自定义函数“calTax”，即可得到所应扣除的个人所得税金额。

有两种办法将单元格T5的公式复制到T6和同列的其他行单元格：

（1）拖动单元格T5右下角的句柄

到同列的其他单元格，释放鼠标即可。

（2）复制单元格T5，选中同列其他需要计算个人所得税的单元格，粘贴即可。

如何重复使用自定义函数

1．将包含有自定义函数的工作簿另存为模板文件并使用，具体操作步骤如下：

（1）、单击【文件】-【另存为】命令，弹出如图3-3所示的对话框，在窗口下方的【保存类型】下拉列表框中选择项目【Excel 启用宏的模板(*.xltm)】选项，将包含自定义函数“calTax”的工作簿另存为“自定义函数.xltm”，模板文件自动保存在当前用户的模板目录下。

2、新建工作簿时调用包含有自定义函数的模板

（1)、单击【文件】，选择【新建】命令，弹出如下图所示的【新建工作簿】对话框。

（2）、在【个人】标签下，选择“自定义函数.xltm”，单击【确定】按钮，即可创建以文件“自定义函数.xltm”为模板的新的工作簿。

（3）在新的工作簿中用户可以直接调用自定义函数“calTax”，以模板文件为基础制作新的工作表和内容，节省大量人力。

总结

类似于个人所得税这样的函数，在日常工作中可能经常用到。每个行业都有自己的特殊计算要求，均可做成自定义函数，再另存为模板，普及到每个操作人员，这样就可以统一规范计算方法，并且一劳永逸。