Excel-VBA常用技巧-第09章.函数的使用

VBA常用技巧代码解析1VBA常用技巧目录VBA常用技巧...................................................................................................................................1第9章函数的使用...........................................................................................................2技巧1调用工作表函数求和.......................................................................................2技巧2查找最大、最小值...........................................................................................2技巧3不重复值的录入...............................................................................................4技巧4获得当月的最后一天.......................................................................................6技巧5四舍五入运算...................................................................................................75-1极小值修正法................................................................................................75-2调用工作表函数法.........................................................................................8技巧6使用字符串函数...............................................................................................8技巧7使用日期函数.................................................................................................10技巧8判断是否为数值.............................................................................................14技巧9格式化数值、日期和时间.............................................................................15技巧10个人所得税自定义函数...............................................................................17技巧11人民币大写函数...........................................................................................19技巧12列号转换为列标...........................................................................................21技巧13判断工作表是否为空表...............................................................................22技巧14查找指定工作表...........................................................................................23技巧15查找指定工作簿是否打开...........................................................................25技巧16取得应用程序的安装路径...........................................................................25技巧17数组的使用...................................................................................................2717-1代码运行时创建数组.................................................................................2717-2文本转换为数组........................................................................................2817-3使用动态数组去除重复值.........................................................................30VBA常用技巧代码解析2第9章函数的使用技巧1调用工作表函数求和在对工作表的单元格区域进行求和计算时，使用工作表Sum函数比使用VBA代码遍历单元格进行累加求和效率要高得多，代码如下所示。#001SubrngSum()#002DimrngAsRange#003DimdAsDouble#004Setrng=Range(A1:F7)#005d=Application.WorksheetFunction.Sum(rng)#006MsgBoxrng.Address(0,0)&单元格的和为&d#007EndSub代码解析：rngSum过程调用工作表Sum函数对工作表的单元格区域进行求和计算。在VBA中调用工作表函数需要在工作表函数前加上WorksheetFunction属性。应用于Application对象的WorksheetFunction属性返回WorksheetFunction对象，作为VBA中调用工作表函数的容器，在实际应用中可省略Application对象识别符。技巧2查找最大、最小值在VBA中没有内置的函数可以进行最大、最小值的查找，借助工作表Max、Min函数可以快速地在工作表区域中查找最大、最小值，如下面的代码所示。#001Subseeks()VBA常用技巧代码解析3#002DimrngAsRange#003DimmyRngAsRange#004Dimk1AsInteger,k2AsInteger#005DimmaxAsDouble,minAsDouble#006SetmyRng=Sheet1.Range(A1:F30)#007ForEachrngInmyRng#008Ifrng.Value=WorksheetFunction.max(myRng)Then#009rng.Interior.ColorIndex=3#010k1=k1+1#011max=rng.Value#012ElseIfrng.Value=WorksheetFunction.min(myRng)Then#013rng.Interior.ColorIndex=5#014k2=k2+1#015min=rng.Value#016Else#017rng.Interior.ColorIndex=0#018EndIf#019Next#020MsgBox最大值是:&max&共有&k1&个_#021&Chr(13)&最小值是:&min&共有&k2&个#022EndSub代码解析：seeks过程在工作表单元格区域中查找最大、最小值，并将其所在的单元格底色分别设置为红色和蓝色。第2行到第5行代码声明变量类型。第6行代码使用关键字Set将单元格引用赋给变量myRng。第7行到第19行代码遍历单元格区域，使用工作表Max、Min函数判断单元格数值是否是所在区域的最大、最小值，如果是，将其所在的单元格底色设置为红色或蓝色，并保存其数值和数量。第20、21行代码使用消息框显示最大、最小值数值和数量。运行seeks过程后将工作表区域最大、最小值所在的单元格的底色设置为红色或蓝色并用消息框显示其数值和数量，如图2-1所示。VBA常用技巧代码解析4图2-1查找最大、最小值技巧3不重复值的录入在工作表中录入数据时，有时希望能限制重复值的录入，比如在示例的A列单元格只能录入唯一的人员编号，此时可以利用工作表的Change事件结合工作表的CountIf函数来判断所录入的人员编号是否重复，示例代码如下。#001PrivateSubWorksheet_Change(ByValTargetAsRange)#002WithTarget#003If.Column1Or.Count1ThenExitSub#004IfApplication.CountIf(Range(A:A),.Value)1Then#005.Select#006MsgBox不能输入重复的人员编号!,64#007Application.EnableEvents=False#008.Value=#009Application.EnableEvents=TrueVBA常用技巧代码解析5#010EndIf#011EndWith#012EndSub代码解析：工作表的Change事件过程，使A列单元格只能录入唯一的人员编号。第4行代码使用工作表的CountIf函数来判断在A列单元格输入的人员编号是否重复。工作表的CountIf函数计算区域中满足给定条件的单元格的个数，语法如下：COUNTIF(range,criteria)参数range为需要计算其中满足条件的单元格数目的单元格区域。参数criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。在示例中以所录入的人员编号与A列单元格区域进行比较，如果CountIf函数的返回值大于1，说明录入的是重复编号。第5行代码，重新选择该单元格便于下一步清空后重新录入。第7、8、9行代码，清除录入的重复编号，在清除前将Application对象的EnableEvents属性设置为False，禁用事件。因为如果不禁用事件，那么在清除重复值的过程中会不断地触发工作表的Change事件，从而造成代码运行的死循环。经过以上的设置，在工作表的A列中只能录入唯一的人员编号，如果录入重复值会进行提示，如图3-1所示，点击确定后自动清除录入的重复编号。图3-1限制重复值的录入VBA常用技巧代码解析6技巧4获得当月的最后一天在实际工作中经常需要根据给定的日期计算其所属月份的最后一天，此时可以使用DateSerial函数完成计算，如下面的代码所示。#001SubSerial()#002DimDateStrAsByte#003DateStr=Day(DateSerial(Year(Date),Month(Date)+1,0))#004MsgBox本月的最后一天是&Month(Date)&月&DateStr&号#005EndSub代码解析：Serial过程配合使用了4个VBA内