搜索
1函数的使用技巧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()2#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所示。3图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=True4#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限制重复值的录入5技巧4获得当月的最后一天在实际工作中经常需要根据给定的日期计算其所属月份的最后一天，此时可以使用DateSerial函数完成计算，如下面的代码所示。#001SubSerial()#002DimDateStrAsByte#003DateStr=Day(DateSerial(Year(Date),Month(Date)+1,0))#004MsgBox本月的最后一天是&Month(Date)&月&DateStr&号#005EndSub代码解析：Serial过程配合使用了4个VBA内置函数Year、Month、Day和DateSerial完成计算并使用消息框显示当月最后一天的日期。Year、Month和Day函数分别返回代表指定日期的年、月、日的整数，语法如下：Year(Date)Month(Date)Day(Date)其中参数Date可以是任何能够表示日期的Variant、数值表达式、字符串表达式或它们的组合。DateSerial函数返回包含指定的年、月、日的Variant(Date)，语法如下：DateSerial(year,month,day)其中参数year、month、day分别表示指定的年、月、日。为了指定某个日期，DateSerial函数中的每个参数的取值范围应该是可接受的，即日的取值范围应在1-31之间，而月的取值范围应在1-12之间。但是，当一个数值表达式表示某日之前或其后的年、月、日数时，也可以为每个使用这个数值表达式的参数指定相对日期。当任何一个参数的取值超出可接受的范围时，它会自动地在可接受的时间单位进行调整，例如本例中的day参数设置为0，则被解释成month参数指定月的前一天，即表达式Month(Date)+1指定的下一个月的前一天，也就是本月的最后一天。运行Serial过程结果如图4-1所示。图4-1获得当月的最后一天6技巧5四舍五入运算在实际工作中经常需要对数值或计算结果进行四舍五入运算，此时可以使用VBA内置的Round函数。Round函数返回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果，语法如下：Round(expression[,numdecimalplaces])参数expression是必需的，要进行四舍五入运算的数值表达式。参数numdecimalplaces是可选的，数字值，表示进行四舍五入运算时，小数点右边应保留的位数。如果忽略，则Round函数返回整数。但是VBA内置的Round函数在对数值进行四舍五入运算时实行的是Bankre舍入，而不是算术舍入。按Bankre舍入规则，如果保留位数的下一个数字正好是5则其后没有其他有效数字，则按保留位最后一位“偶舍奇入”的方法进行处理。比如Round(1.5)的保留位最后为1，是奇数，小数位的5入上去，因此Round(1.5)的运算结果是2；而Round(4.5)的保留位最后为4，是偶数，小数位的5舍去，因此Round(4.5)的运算结果是4而不是5。Bankre舍入规则虽然有其合理性，但不符合实际工作的需要。在实际应用中使用以下两种方法避免Bankre舍入：5-1极小值修正法在使用Round函数时对需要舍入的数值先加上极小值再调用VBA内置的Round函数，如下面的代码所示。#001SubaTestRound()#002MsgBoxRound(4.5)=&Round(4.5)&Chr(13)&Round(4.5)=&Round(4.5+0.0000001)#003EndSub代码解析：aTestRound过程分别调用VBA内置的Round函数和加上极小值再调用VBA内置的Round函数在洗染店框中显示两者运算结果，如图5-1所示。图5-1加上极小值进行运算结果7从运算结果中可以发现，加上极小值后Round(4.5)已正确运算为5而不是4。5-2调用工作表函数法还可以使用工作表函数Round代替VBA内置的Round函数。工作表函数Round和VBA内置的Round函数的用法相同，但它采用算术舍入而不是Bankre舍入，所以不会有“偶舍奇入”的问题，如下面的代码所示。#001SubbTestRound()#002MsgBoxRound(4.5)=&Round(4.5)&Chr(13)&Round(4.5)=&Application.Round(4.5,0)#003EndSub代码解析：bTestRound过程分别调用VBA内置的Round函数和工作表Round函数在消息框中显示两者运算结果，如图5-2所示。图5-2工作表函数运算结果从运算结果中可以发现，使用工作表Round函数后Round(4.5)已正确运算为5而不是4。技巧6使用字符串函数使用VBA的字符串函数可以对字符串进行各种操作，如下面的代码所示。#001SubStrFunctions()#002DimStrAsString#003Str=AbcDEFGhijkLmn#004MsgBox原始字符串为：&Str&Chr(13)_8#005&字符串长度为：&Len(Str)&Chr(13)_#006&左边8个字符为：&Left(Str,8)&Chr(13)_#007&右边6个字符为：&Right(Str,6)&Chr(13)_#008&从左边第2个开始取5个字符为：&Mid(Str,2,5)&Chr(13)_#009&转换为大写：&UCase(Str)&Chr(13)_#010&转换为小写：&LCase(Str)&Chr(13)#011EndSub代码解析：StrFunctions过程使用字符串函数对字符串进行各种操作，如计算字符数、取得一定数量的字符、大小写转换等。第5行代码使用Len函数返回字符串内字符的数目，Len函数语法如下：Len(string|varname)参数string为任何有效的字符串表达式。参数varname为任何有效的变量名称。两个可能的参数必须有一个，而且只能有一个参数。第6行代码使用Left函数从字符串左边起返回8个字符。第7行代码使用Right函数从字符串右边起返回6个字符Left函数语法如下：Left(string,length)Right函数语法如下：Right(string,length)参数string是必需的，字符串表达式。参数length是必需的，数值表达式，将返回的字符数量。如果为0，返回零长度字符串()；如果大于或等于参数string的字符数，则返回整个字符串。第8行代码使用Mid函数从字符串第2位起返回5个字符。Mid函数语法如下：Mid(string,start[,length])参数string是必需的，字符串表达式。参数start是必需的，string中被取出部分的字符位置。如果超过