ExcelVBA函数使用.docx

ExcelVBA函数使用.docx

《ExcelVBA函数使用.docx》由会员分享，可在线阅读，更多相关《ExcelVBA函数使用.docx（36页珍藏版）》请在冰豆网上搜索。

ExcelVBA函数使用

函数的使用

技巧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（）

#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过程后将工作表区域最大、最小值所在的单元格的底色设置为红色或蓝色并用消息框显示其数值和数量，如图1541所示。

图1541查找最大、最小值

技巧3不重复值的录入

在工作表中录入数据时，有时希望能限制重复值的录入，比如在示例的A列单元格只能录入唯一的人员编号，此时可以利用工作表的Change事件结合工作表的CountIf函数来判断所录入的人员编号是否重复，示例代码如下。

#001PrivateSubWorksheet_Change（ByValTargetAsRange）

#002WithTarget

#003If.Column<>1Or.Count>1ThenExitSub

#004IfApplication.CountIf（Range（"A:

A"）,.Value）>1Then

#005.Select

#006MsgBox"不能输入重复的人员编号!

",64

#007Application.EnableEvents=False

#008.Value=""

#009Application.EnableEvents=True

#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列中只能录入唯一的人员编号，如果录入重复值会进行提示，如图1551所示，点击确定后自动清除录入的重复编号。

图1551限制重复值的录入

技巧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过程结果如图1561所示。

图1561获得当月的最后一天

技巧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函数在洗染店框中显示两者运算结果，如图1571所示。

图1571加上极小值进行运算结果

从运算结果中可以发现，加上极小值后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函数在消息框中显示两者运算结果，如图1572所示。

图1572工作表函数运算结果

从运算结果中可以发现，使用工作表Round函数后Round（4.5）已正确运算为5而不是4。

技巧6使用字符串函数

使用VBA的字符串函数可以对字符串进行各种操作，如下面的代码所示。

#001SubStrFunctions（）

#002DimStrAsString

#003Str="AbcDEFGhijkLmn"

#004MsgBox"原始字符串为：

"&Str&Chr（13）_

#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中被取出部分的字符位置。

如果超过string的字符数，将返回零长度字符串（""）。

参数length是可选的，要返回的字符数。

如果省略或超过string的字符数，将返回字符串中所有字符。

第9行代码使用UCase函数将字符串转换成大写的字符串。

第10行代码使用LCase函数将字符串转换成小写的字符串。

UCase函数的语法如下：

UCase（string）

LCase函数的语法如下：

LCase（string）

参数string是必需的，任何有效的字符串表达式。

运行StrFunctions过程结果如图1581所示。

图1581使用字符串函数

技巧7使用日期函数

使用VBA的日期函数可以对日期进行各种计算，如下面的代码所示。

#001SubDatFunctions（）

#002DimStrAsString

#003DimWeekAsString

#004Str=InputBox（"请输入日期：

"）

#005IfLen（Str）>0Then

#006IfIsDate（Str）Then

#007SelectCaseWeekday（Str,vbMonday）

#008Case1

#009Week="一"

#010Case2

#011Week="二"

#012Case3

#013Week="三"

#014Case4

#015Week="四"

#016Case5

#017Week="五"

#018Case6

#019Week="六"

#020Case7

#021Week="日"

#022EndSelect

#023MsgBox"你输入的日期是"&DateValue（Str）&Chr（13）_

#024&"是"&Year（Str）&"年的第"&DatePart（"q",Str）&"季度"&Chr（13）_

#025&"是星期"&Week&Chr（13）_

#026&"距离今天有"&Abs（DateDiff（"d",Date,Str））&"天"&Chr（13）_

#027&"60天后的日期是"&DateAdd（"d",60,Str）

#028Else

#029MsgBox"请输入正确格式的日期!

"

#030EndIf

#031EndIf

#032EndSub

代码解析：

DatFunctions过程在对话框中输入日期后使用各种日期函数对其进行计算并用消息框显示。

第4、5行代码使用InputBox函数显示一个对话框，供用户在对话框中输入一个日期。

第6行代码使用IsDate函数判断输入的日期是否正确。

IsDate函数返回Boolean值，指出一个表达式是否可以转换成日期，语法如下：

IsDate（expression）

参数expression是必需的，日期表达式或字符串表达式，如果表达式是一个日期，或者可以作为有效日期识别，则IsDate函数返回True，否则返回False。

第7行到第22行代码使用Weekday函数判断所输入的日期是星期几。

Weekday函数返回一个整数，代表某个日期是星期几，语法如下：

Weekday（date,[firstdayofweek]）

参数date是必需的，能够表示日期的Variant、数值表达式、字符串表达式或它们的组合。

参数firstdayofweek是可选的，指定一星期第一天的常数，如表格1591所示。

常数

值

描述

vbUseSystem

0

使用NLSAPI设置

VbSunday

1

星期日（缺省值）

vbMonday

2

星期一

vbTuesday

3

星期二

vbWednesday

4

星期三

vbThursday

5

星期四

vbFriday

6

星期五

vbSaturday

7

星期六

表格1591firstdayofweek参数值

Weekday函数返回一个1到7之间的整数，当firstdayofweek参数设置为vbMonday

（2）时，返回1时说明是星期一，以此类推。

第23行代码根据系统中指定的短日期格式来显示所输入的日期。

DateValue函数的语法如下：

DateValue（date）

参数date是必需的，任何表达式，表示从100年1月1日到9999年12月31日之间的一个日期。

如果是一个字符串，且其内容只有数字以及分隔数字的日期分隔符，则DateValue函数就会根据系统中指定的短日期格式来识别月、日、年的顺序。

DateValue函数也识别明确的英文月份名称，全名或缩写均可。

例如，除了12/30/1991和12/30/91之外，DateValue函数也能识别December30,1991和Dec30,1991。

如果date参数中略去了年这一部分，DateValue函数就会使用由计算机系统日期设置的当前年份。

第24行代码判断输入的日期的季度。

DatePart函数返回一个包含已知日期的指定时间部分的值，语法如下：

DatePart（interval,date[,firstdayofweek[,firstweekofyear]]）

其中参数interval是必需的，字符串表达式，是要返回的时间间隔，设定值如表格1592所示。

设置

说明

yyyy

年

q

季

m

日

y

一年的日数

d

日

w

一周的日数

ww

周

h

时

n

分钟

s

秒

表格1592interval参数设定值

第26行代码计算所输入的日期距当天的天数。

DateDiff函数返回两个指定日期间的时间间隔数目，语法如下：

DateDiff（interval,date1,date2[,firstdayofweek[,firstweekofyear]]）

其中参数interval是必需的，字符串表达式，表示用来计算date1和date2的时间差的时间间隔，设定值如表格1592所示。

参数date1和date2是必需的，计算中要用到的两个日期。

因为如果输入的日期是当前日期以前的日期，DateDiff函数会返回负值，所以使用Abs函数返回绝对值将其转换为正值。

第27行代码计算所输入的日期距当天的天数，DateAdd返回加上了一段时间间隔的一个日期，语法如下：

DateAdd（interval,number,date）

参数interval是必需的，字符串表达式，是所要加上去的时间间隔，设定值如表格1592所示。

参数number是必需的，是要加上的时间间隔的数目。

其数值可以为正数（得到未来的日期），也可以为负数（得到过去的日期）。

参数date是必需的，需要加上时间间隔的字符串表达式。

运行DatFunctions过程，在显示的对话框中输入一个日期，结果如图1591所示。

图1591使用日期函数

技巧8判断是否为数值

使用IsNumeric函数可以判断表达式的运算结果是否为数值，如下面的代码所示。

#001SubNumeric（）

#002DimiAsInteger

#003DimnAsString

#004DimsAsString

#005WithSheet1

#006Fori=1To.Range（"A65536"）.End（xlUp）.Row

#007IfIsNumeric（.Cells（i,1））Then

#008n=n&.Cells（i,1）.Address（0,0）&Chr（9）&.Cells（i,1）&Chr（13）

#009Else

#010s=s&.Cells（i,1）.Address（0,0）&Chr（9）&.Cells（i,1）&Chr（13）

#011EndIf

#012Next

#013EndWith

#014MsgBox"A列中数值单元格：

"&Chr（13）&n&Chr（13）_

#015&"A列中非数值单元格：

"&Chr（13）&s

#016EndSub

代码解析：

Numeric过程使用IsNumeric函数判断工作表的A列单元格是否为数值，并使用消息框显示。

第7行代码判断工作表的A列单元格是否为数值。

IsNumeric函数返回Boolean值，指出表达式的运算结果是否为数，语法如下：

IsNumeric（expression）

参数expression是必需的，Variant类型，包含数值表达式或字符串表达式。

如果参数expression的运算结果为数字，则IsNumeric返回True，否则返回False。

第8行代码将数值单元格的地址和数值保存在变量e中。

第10行代码将非数值单元格的地址和内容保存在变量s中。

在保存时插入制表符对数据列进行分隔，使之排列整齐，请参阅技巧73-5。

运行Numeric过程结果如图1601所示。

图1601判断是否为数值

技巧9格式化数值、日期和时间

Format函数是VBA中的常用函数，可以实现数值、日期和时间格式的转变，示例代码如下：

#001SubFromatCurrent（）

#002MsgBoxFormat（123456.789,"0.00"）&Chr（13）_

#003&Format（123456.789,"0.00%"）&Chr（13）_

#004&Format（123456.789,"##,##0.00"）&Chr（13）_

#005&Format（-123456.789,"$#,##0.00