ExcelVBA函数使用DOC.docx
《ExcelVBA函数使用DOC.docx》由会员分享，可在线阅读，更多相关《ExcelVBA函数使用DOC.docx（39页珍藏版）》请在冰豆网上搜索。
ExcelVBA函数使用DOC
函数的使用
技巧1调用工作表函数求和
在对工作表的单元格区域进行求和计算时，使用工作表Sum函数比使用VBA代码遍历
单元格进行累加求和效率要高得多，代码如下所示。
#001SubrngSum（）
#002
DimrngAsRange
#003
DimdAsDouble
#004
Setrng=Range（"A1:
F7"）
#005
d=Application.WorksheetFunction.Sum（rng）
#006
MsgBoxrng.Address（0,0）&"单元格的和为"&d
#007EndSub
代码解析：
rngSum过程调用工作表Sum函数对工作表的单元格区域进行求和计算。
在VBA中调用工作表函数需要在工作表函数前加上WorksheetFunction属性。
应用于
Application对象的WorksheetFunction属性返回WorksheetFunction对象，作为VBA中调用工作表函数的容器，在实际应用中可省略Application对象识别符。
技巧2查找最大、最小值
在VBA中没有内置的函数可以进行最大、最小值的查找，借助工作表Max、Min函数
可以快速地在工作表区域中查找最大、最小值，如下面的代码所示。
#001Subseeks（）
#002
DimrngAsRange
#003
DimmyRngAsRange
#004
Dimk1AsInteger,k2AsInteger
#005
DimmaxAsDouble,minAsDouble
#006
SetmyRng=Sheet1.Range（"A1:
F30"）
#007
ForEachrngInmyRng
#008
Ifrng.Value=WorksheetFunction.max（myRng）Then
#009
rng.Interior.Colorlndex=3
#010
k1=k1+1
#011
max=rng.Value
#012
ElseIfrng.Value=WorksheetFunction.min（myRng）Then
#013
rng.Interior.Colorlndex=5
#014
k2=k2+1
#015
min=rng.Value
#016
Else
#017
rng.Interior.ColorIndex=0
#018
EndIf
#019
Next
#020
MsgBox"最大值是:
"&max&"
共有"&k1&"个"_
#021
&Chr（13）&"最小值是
:
"&min&"共有"&k2&"
个"
#022
EndSub
代码解析：
seeks过程在工作表单元格区域中查找最大、最小值，并将其所在的单元格底色分别设
置为红色和蓝色。
第2行到第5行代码声明变量类型。
第6行代码使用关键字Set将单元格引用赋给变量myRng。
第7行到第19行代码遍历单元格区域，使用工作表Max、Min函数判断单元格数值是
否是所在区域的最大、最小值，如果是，将其所在的单元格底色设置为红色或蓝色，并保存其数值和数量。
第20、21行代码使用消息框显示最大、最小值数值和数量。
运行seeks过程后将工作表区域最大、最小值所在的单元格的底色设置为红色或蓝色并用消息框显示其数值和数量，如图2-1所示。
图2-1查找最大、最小值
技巧3不重复值的录入
在工作表中录入数据时，有时希望能限制重复值的录入，比如在示例的A列单元格只
能录入唯一的人员编号，此时可以利用工作表的Change事件结合工作表的Countlf函数来判断所录入的人员编号是否重复，示例代码如下。
#001PrivateSubWorksheet_Change（ByValTargetAsRange）
#002
WithTarget
#003
If.Column<>1Or.Count>1ThenExitSub
#004
IfApplication.Countlf（Range（"A:
A"）,.Value）>1Then
#005
.Select
#006
MsgBox"不能输入重复的人员编号!
",64
#007
Application.EnableEvents=False
#008
.Value=""
#009
Application.EnableEvents=True
#010EndIf
#011EndWith
#012EndSub
代码解析：
工作表的Change事件过程，使A列单元格只能录入唯一的人员编号。
第4行代码使用工作表的Countlf函数来判断在A列单元格输入的人员编号是否重复。
工作表的Countlf函数计算区域中满足给定条件的单元格的个数，语法如下：
COUNTIF（range,criteria）
参数range为需要计算其中满足条件的单元格数目的单元格区域。
参数criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。
在示例中以所录入的人员编号与A列单元格区域进行比较，如果Countlf函数的返回
值大于1，说明录入的是重复编号。
第5行代码，重新选择该单元格便于下一步清空后重新录入。
第7、8、9行代码，清除录入的重复编号，在清除前将Application对象的EnableEvents属性设置为False，禁用事件。
因为如果不禁用事件，那么在清除重复值的过程中会不断地触发工作表的Change事件，从而造成代码运行的死循环。
经过以上的设置，在工作表的A列中只能录入唯一的人员编号，如果录入重复值会进
行提示，如图3-1所示，点击确定后自动清除录入的重复编号。
图3-1限制重复值的录入
技巧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获得当月的最后一天
技巧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（）
#002MsgBox"Round（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加上极小值进行运算结果
从运算结果中可以发现，加上极小值后Round（4.5）已正确运算为5而不是4。
5-2调用工作表函数法
还可以使用工作表函数Round代替VBA内置的Round函数。
工作表函数Round和
VBA内置的Round函数的用法相同，但它采用算术舍入而不是Bankre舍入，所以不会有
“偶舍奇入”的问题，如下面的代码所示。
#001SubbTestRound（）
#002MsgBox"Round（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的字符串函数可以对字符串进行各种操作，如下面的代码所示。
升级会员 