第5章 使用VBA开发自定义函数在第1章中曾经提到过,可以在VBA中创建的两种过程——子过程和函数过程。
在前面的例子中,我们创建和使用的都是子过程,它通常都可以完成某一种功能。
而函数过程则是为了完成某种计算,并返回一个计算结果。
在VBA中创建的函数过程不但可以在VB A中使用,而且还可以像其他 Excel内置工作表函数一样,在工作表的公式中使用。
本章将重点介绍创建自定义函数并在工作表公式中使用的方法。
5.1了解函数过程中的参数在Excel工作表公式中使用不同的函数时,通常都需要输入函数的参数,然后函数才能得出正确结果。
当然,有极少一部分函数不需要参数,例如时间函数Now,在单元格中输入“=Now()”并按【Enter】键后,将得到当前的时间。
在VBA中编写自定义函数时,也要根据函数的功能为自定义函数设计不定数量的参数,以便在使用中用户可以给函数参数赋值而获得想要的结果。
本节将介绍自定义函数参数的几种类型。
5.1.1不使用参数的函数自定义函数可以不使用任何参数,这通常在需要通过自定义函数返回一个信息时使用。
例如,下面的自定义函数返回当前工作簿的路径,它不需要使用任何参数:Function GetPath()GetPath = ActiveWorkbook.FullNameEnd Function当在单元格中输入“=GetPath()”并按【Enter】键后,将在单元格中显示当前工作簿的路径,如图5-1所示。
当在单元格输入等号“=”后,可以通过Excel 2007的自动完成功能在列表中找到自定义函数。
图5-1 使用无参数函数返回工作簿路径提示:与Excel内置的工作表函数一样,即使自定义函数不使用参数,但是在输入函数时也要包含一对圆括号。
5.1.2使用有—个参数的函数有时可能需要通过给定一个数值来获得结果。
例如,在使用Excel的工作表函数ABS 时,通过给定一个数字,返回它的绝对值。
那么在自定义函数时,也可以为函数设置一个参数,在公式中使用自定义函数时,也要输入一个参数,才能得出正确结果。
例如,下面的自定义函数通过用户输入一个数字,来求得该数字的阶乘:Function CountF(Num)Dim i As IntegerDim Total As LongTotal = 1For i = 1 To NumTotal = Total * iNext iCountF = TotalEnd 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 LongTotal = UPrice * AmountSelect Case TotalCase 0 To 20000GetBonus = Total * 0.06Case 20001 To 40000GetBonus = Total * 0.08Case ElseGetBonus = Total * 0.1End SelectEnd 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 IntegerDim Total As LongFor i = 1 To LargeNumTotal = Total + rge(Range, i)Next iLargePercent = Total * PercentEnd 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过程中使用该函数。
5.2.2在工作表公式中使用自定义函数当创建好自定义函数后,就可以像使用Excel内置工作表函数一样,来使用自定义函数。
在5.1节的例子中,已经介绍过可以在单元格中通过手工的方法来输入自定义函数。
如果您不喜欢这种方式,那么可以使用【插入函数】对话框。
具体操作如下:(1)单击要输入函数的单元格,然后单击公式栏左侧的【插入函数】按钮。
(2)打开【插入函数】对话框,选择【或选择类别】列表中的【用户定义】类别。
在【选择函数】列表框中可以看到当前可以使用的自定义函数,如图5-5所示。
图5-5 在【用户定义】类别中可以找到自定义的函数(3)选择好要使用的函数,然后单击【确定】按钮。
打开【函数参数】对话框,在该对话框中依次输入自定义函数的参数,如图5-6所示。
图5-6 在【函数参数】对话框中输入自定义函数的参数(4)输入好自定义函数的参数后,单击【确定】按钮,即可得到计算结果。
5.2.3在VBA过程中调用自定义函数除了在Excel工作表公式中使用自定义函数外,也可以在VBA其他过程中调用自定义函数过程。
由于函数过程通常都会返回一个值,因此,可以在子过程中将函数过程的计算结果赋值给一个变量,然后使用该变量再进行其他操作。
例如,下面的代码调用前面例子中的计算提成金额的函数过程“GetBonus”中,然后根据在单元格A1和B1中输入的单价和销售量,最后通过提示信息显示计算后的提成金额,如图5-7所示。
Sub 计算提成()Dim i As Integer, j As Integeri = ActiveSheet.Range("A1")j = ActiveSheet.Range("B1")MsgBox "您的提成金额为:" & GetBonus(i, j)End Sub图5-7 在VBA过程中调用函数过程5.2.4设置自定义函数的说明信息如果在【插入函数】对话框中选择的是Excel内置的工作表函数,那么会在该对话框的下方显示所选函数的说明信息。
但是如果选择的是自定义函数,则不会显示函数的信息,这需要用户手工设置。
具体操作如下:(1)打开包含自定义函数的工作簿,然后选择【开发工具】Ö【代码】Ö【宏】命令。
(2)打开【宏】对话框,在【宏名称】文本框中输入要添加说明信息的自定义函数名称,如图5-8所示。
(3)单击【选项】按钮,打开【宏选项】对话框,在【说明】文本框中输入自定义函数的说明信息,如图5-9所示。
图5-8 手工输入自定义函数名称图5-9 设置自定义函数的说明信息(4)单击【确定】按钮,完成自定义函数说明信息的设置。
以后在【插入函数】对话框选择该自定义函数时,即可看到说明信息,如图5-10所示。
图5-10 在选择自定义函数时可以看到说明信息提示:但是在设置自定义函数的参数时,用户无法为每个参数添加说明信息。
5.2.5共享自定义函数如果创建的自定义函数只供自己使用,那么可以将自定义函数保存到个人宏工作簿Pe rsonal.xlsb中,这样所有打开的工作簿中都可以使用该自定义函数。
在第21章介绍录制宏的内容时,也曾经提到过个人宏工作簿的相关概念。
如果您的Office安装到硬盘上的C分区,那么个人宏工作簿Personal.xlsb的默认位置是:C:\Documents and Settings\用户名\Application Data\Microsoft\Excel\XLStart上面的用户名是用户登录Windows操作系统时的用户名称。
如果要将在当前工作簿中创建的自定义函数给其他用户使用,那么需要将包含自定义函数的工作簿制作为一个加载项,然后让需要使用该自定义函数的用户安装该加载项即可。
5.3自定义函数实例本节将列举一些比较实用的自定义函数实例,它们在使用Excel时很有用。