第5章 使用VBA开发自定义函数

第5章 使用VBA开发自定义函数

在第1章中曾经提到过，可以在VBA中创建的两种过程——子过程和函数过程。在前面的例子中，我们创建和使用的都是子过程，它通常都可以完成某一种功能。而函数过程则是为了完成某种计算，并返回一个计算结果。在VBA中创建的函数过程不但可以在VB A中使用，而且还可以像其他 Excel内置工作表函数一样，在工作表的公式中使用。本章将重点介绍创建自定义函数并在工作表公式中使用的方法。

5.1了解函数过程中的参数

在Excel工作表公式中使用不同的函数时，通常都需要输入函数的参数，然后函数才能得出正确结果。当然，有极少一部分函数不需要参数，例如时间函数Now，在单元格中输入“=Now()”并按【Enter】键后，将得到当前的时间。

在VBA中编写自定义函数时，也要根据函数的功能为自定义函数设计不定数量的参数，以便在使用中用户可以给函数参数赋值而获得想要的结果。本节将介绍自定义函数参数的几种类型。

5.1.1不使用参数的函数

自定义函数可以不使用任何参数，这通常在需要通过自定义函数返回一个信息时使用。例如，下面的自定义函数返回当前工作簿的路径，它不需要使用任何参数：Function GetPath()

GetPath = ActiveWorkbook.FullName

End Function

当在单元格中输入“=GetPath()”并按【Enter】键后，将在单元格中显示当前工作簿的路径，如图5-1所示。当在单元格输入等号“=”后，可以通过Excel 2007的自动完成功能在列表中找到自定义函数。

图5-1 使用无参数函数返回工作簿路径

提示：与Excel内置的工作表函数一样，即使自定义函数不使用参数，但是在输入函数时也要包含一对圆括号。

5.1.2使用有—个参数的函数

有时可能需要通过给定一个数值来获得结果。例如，在使用Excel的工作表函数ABS 时，通过给定一个数字，返回它的绝对值。那么在自定义函数时，也可以为函数设置一个参数，在公式中使用自定义函数时，也要输入一个参数，才能得出正确结果。

例如，下面的自定义函数通过用户输入一个数字，来求得该数字的阶乘：

Function CountF(Num)

Dim i As Integer

Dim Total As Long

Total = 1

For i = 1 To Num

Total = Total * i

Next i

CountF = Total

End Function

在工作表中输入该函数时，要求输入一个参数，例如，输入“=CountF(5)”，按【Ente r】键后，将得到给定参数值的阶乘，如图5-2所示。

图5-2 使用一个参数的函数计算数字的阶乘

5.1.3使用多个参数的函数

如果需要参与计算的条件较多，一个参数不够用时，那么可以在自定义函数中设置多个参数。例如，可以创建一个自定义函数，根据给定的商品单价和销售数量，计算员工的销售提成金额。当销售额小于20000时，以销售额的6%作为提成金额；当销售额在20001到40000之间时，以销售额的8%作为提成金额；如果销售额大于40000，那么以销售额的10%作为提成金额。下面的自定义函数正是用来计算这种提成方法的：

Function GetBonus(UPrice, Amount)

Dim Total As Long

Total = UPrice * Amount

Select Case Total

Case 0 To 20000

GetBonus = Total * 0.06

Case 20001 To 40000

GetBonus = Total * 0.08

Case Else

GetBonus = Total * 0.1

End Select

End Function

在公式中输入上面的自定义函数GetBonus，并指定函数中的两个参数，商品单价和销售量，将得到提成金额，如图5-3所示。

图5-3 通过两个参数的自定义函数计算销售提成

提示：如果两个参数仍不够，还可以设置更多个参数，其创建和使用方法与包含两个参数的自定义函数是相同的。

5.1.4使用整个区域作为参数的函数

在Excel内置工作表函数中，有些函数需要用户提供表示区域的参数，然后根据给定的区域返回某个符合条件的值。例如，对于Large函数，它可以返回指定区域中的第几个最大的值。但是如果要计算区域中前n大的值之和的百分之几，那么使用包含Large函数的公式是相当麻烦的。

例如，要计算区域A1:D4中前3大的数值的10%，那么需要使用下面这个公式：=(LARGE(A1:D4,1)+LARGE(A1:D4,2)+LARGE(A1:D4,3))*10%

如果现在要计算区域A1:D4中前5大的数值的15%，那么修改上面的公式是不是很麻烦呢？这时可以通过自定义函数来简化公式输入的麻烦。

Function LargePercent(Range, LargeNum, Percent)

Dim i As Integer

Dim Total As Long

For i = 1 To LargeNum

Total = Total + rge(Range, i)

Next i

LargePercent = Total * Percent

End Function

上面的公式使用参数Range指定要参加计算的单元格区域，然后通过LargeNum给定要参加计算的前几大的值的数量，通过Percent参数指定用于计算的百分比值。在工作表中输入上面的自定义函数，并指定3个参数，即可得到计算结果，如图5-4所示。

图5-4 使用区域参数进行复杂计算

5.2创建与使用自定义函数

创建自定义函数需要在VBE窗口中的标准模块中进行，不能将自定义函数的代码写到ThisWorkbook模块或工作表（例如Sheet1）模块中。如果在这些模块中创建自定义函数，那么Excel将无法了解用户创建的是自定义函数。

5.2.1创建自定义函数

通过5.1节的几个例子，相信您已经大致了解自定义函数是如何工作的。本节将介绍创建自定义函数的通用步骤，其实创建过程是非常简单的，具体操作如下：（1）启动Excel 2007，选择【开发工具】Ö【代码】Ö【Visual Basic】命令。如果没有【开发工具】选项卡，可添加该选项或直接按【Alt+F11】组合键。

（2）打开VBE窗口，在工程资源管理器中插入一个模块（右键单击后选择【插入】Ö【模块】命令）。一定不要在ThisWorkbook或Sheet模块中输入自定义函数的代码。

（3）在右侧的代码窗口中，输入“Function”，然后在同一行输入函数名，按【Enter】键，自动加上函数过程的外壳。

（4）在Function和End Function之间输入自定义函数的代码。

完成自定义函数的创建后，即可在工作表公式中或其他VBA过程中使用该函数。