聪明文档网

    聪明文档网

    最新最全的文档下载
    当前位置： 首页> Excel_VBA_函数使用

    Excel_VBA_函数使用

    时间：    下载该word文档
    .
    函数的使用
    技巧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(myRngThen#009rng.Interior.ColorIndex=3#010k1=k1+1#011max=rng.Value
    #012ElseIfrng.Value=WorksheetFunction.min(myRngThen#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过程后将工作表区域最大、最小值所在的单元格的底色设置为红色或蓝色并用消息框显示其数值和数量，如图154-1所示。
    页脚

    .

    图154-1查找最大、最小值
    技巧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列中只能录入唯一的人员编号，如果录入重复值会进行提示，如图155-1所示，点击确定后自动清除录入的重复编号。

    图155-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(DateMonth(DateDay(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过程结果如图156-1所示。

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

    图157-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函数在消息框中显示两者运算结果，如图157-2所示。

    图157-2工作表函数运算结果
    从运算结果中可以发现，使用工作表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过程结果如图158-1所示。

    图158-1使用字符串函数
    技巧7使用日期函数
    使用VBA的日期函数可以对日期进行各种计算，如下面的代码所示。
    #001SubDatFunctions(#002DimStrAsString#003DimWeekAsString
    #004Str=InputBox("请输入日期："#005IfLen(Str>0Then#006IfIsDate(StrThen
    #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是可选的，指定一星期第一天的常数，如表格159-1所示。
    常数vbUseSystemVbSundayvbMondayvbTuesdayvbWednesdayvbThursdayvbFridayvbSaturday
    值01234567
    描述
    使用NLSAPI设置星期日（缺省值）星期一星期二星期三星期四星期五星期六
    表格159-1firstdayofweek参数值
    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是必需的，字符串表达式，是要返回的时间间隔，设定值如表格159-2所示。
    设置
    页脚
    说明

    .
    yyyyqmydwwwhns
    年季日
    一年的日数日
    一周的日数周时分钟秒
    表格159-2interval参数设定值
    第26行代码计算所输入的日期距当天的天数。DateDiff函数返回两个指定日期间的时间间隔数目，语法如下：
    DateDiff(interval,date1,date2[,firstdayofweek[,firstweekofyear]]
    其中参数interval是必需的，字符串表达式，表示用来计算date1和date2的时间差的时间间隔，设定值如表格159-2所示。
    参数date1和date2是必需的，计算中要用到的两个日期。
    因为如果输入的日期是当前日期以前的日期，DateDiff函数会返回负值，所以使用Abs函数返回绝对值将其转换为正值。
    第27行代码计算所输入的日期距当天的天数，DateAdd返回加上了一段时间间隔的一个日期，语法如下：
    DateAdd(interval,number,date
    参数interval是必需的，字符串表达式，是所要加上去的时间间隔，设定值如表格159-2所示。
    参数number是必需的，是要加上的时间间隔的数目。其数值可以为正数（得到未来的日期），也可以为负数（得到过去的日期）。
    参数date是必需的，需要加上时间间隔的字符串表达式。
    运行DatFunctions过程，在显示的对话框中输入一个日期，结果如图159-1所示。
    页脚

    .

    图159-1使用日期函数
    技巧8判断是否为数值
    使用IsNumeric函数可以判断表达式的运算结果是否为数值，如下面的代码所示。
    #001SubNumeric(#002DimiAsInteger#003DimnAsString#004DimsAsString#005WithSheet1
    #006Fori=1To.Range("A65536".End(xlUp.Row#007IfIsNumeric(.Cells(i,1Then
    #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过程结果如图160-1所示。

    图160-1判断是否为数值
    技巧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;($#,##0.00"&Chr(13_#006&Format(-123456.789,"￥#,##0.00;(￥#,##0.00"&Chr(13_#007&Format(Date,"yyyy-mm-dd"&Chr(13_#008&Format(Date,"yyyymmdd"&Chr(13_#009&Format(Date,"LongDate"&Chr(13_#010&Format(Now,"hh:mm:ss"&Chr(13_#011&Format(Now,"hh:mm:ssAMPM"#012EndSub
    代码解析：
    FromatCurrent过程使用消息框显示格式化后的数值、日期和时间。
    Format函数根据格式表达式中的指令来格式化的数值、日期和时间，语法如下：
    Format(expression[,format[,firstdayofweek[,firstweekofyear]]]
    其中参数expression是必需的，任何有效的表达式。
    参数format是可选的，有效的命名表达式或用户自定义格式表达式。第2行代码将数值格式化为两位小数格式显示。第3行代码将数值格式化为两位小数的百分比格式显示。第4行代码将数值格式化为千位分隔符显示。
    第5行代码将数值格式化为以美元符号显示的两位小数，以千位分隔符分隔，如果是负值则以小括号显示。
    第6行代码将数值格式化为以人民币符号显示的两位小数，以千位分隔符分隔，如果是负值则以小括号显示。
    第7行代码将系统日期格式化为“yyyy-mm-dd”格式显示。第8行代码将系统日期格式化为“yyyymmdd”格式显示。第9行代码将系统日期格式化为长日期格式显示。
    第10行代码将系统时间格式化为24小时、分钟和秒的格式显示。第11行代码将系统时间格式化为分12小时、分钟和秒的格式显示。
    页脚

    .
    运行FromatCurrent过程结果如图161-1所示。

    图161-1格式化数值、日期和时间
    技巧10个人所得税自定义函数
    在财务工作中经常需要计算个人所得税，而在Excel中没有计算个人所得税的函数，此时可以使用自定义函数来计算，如下面的代码所示。
    #001PublicFunctionPITax(Income,OptionalThresholdAsSingle#002DimRateAsSingle#003DimDebitAsSingle#004DimTaxliabilityAsSingle
    #005IfIsMissing(ThresholdThenThreshold=2000#006Taxliability=Income-Threshold#007SelectCaseTaxliability#008Case0To500#009Rate=0.05#010Debit=0#011Case500.01To2000#012Rate=0.1
    页脚

    .
    #013Debit=25#014Case2000.01To5000#015Rate=0.15#016Debit=125#017Case5000.01To20000#018Rate=0.2#019Debit=375#020Case20000.01To40000#021Rate=0.25#022Debit=1375#023Case40000.01To60000#024Rate=0.3#025Debit=3375#026Case60000.01To80000#027Rate=0.35#028Debit=6375#029Case80000.01To10000#030Rate=0.4#031Debit=10375#032CaseElse#033Rate=0.45#034Debit=15375#035EndSelect
    #036IfTaxliability<=0Then#037PITax=0#038Else
    #039PITax=Application.Round(Taxliability*Rate-Debit,2#040EndIf#041EndFunction
    代码解析：
    自定义PITax函数根据应纳税额计算应纳的个人所得税额。
    第5行代码设置个人所得税的起征点为2000元，如果以后需要调整起征点，可把2000
    页脚

    .
    元改为调整后的起征点。
    第6行代码设置全月应纳税所得额等于应纳税收入减去起征点。
    第7行到第35行代码根据全月应纳税所得额取得税率和速算扣除数。税率和速算扣除数根据如表格162-1所示的工资、薪金所得适用个人所得税九级超额累进税率表计算。
    级数一二三四五六七八九
    全月应纳税所得额（含税所得额）不超过500元超过500元至2000元超过2000元至5000元超过5000元至20000元超过20000元至40000元超过40000元至60000元超过60000元至80000元超过80000元至100000元超过100000元
    税率%51015202530354045
    速算扣除数（元）0251253751375337563751037515375
    表格162-1个人所得税九级超额累进税率表
    第36行到第40行代码根据应纳税所得额、税率和速算扣除数计算应纳的个人所得税额。其中第39行代码中使用工作表函数Round对计算结果进行四舍五入运算，请参阅技巧157-2。
    在工作表中使用自定义PITax函数结果如图162-1所示。

    图162-1工作表中使用自定义PITax函数
    技巧11人民币大写函数
    在VBA中没有置的函数进行人民币大写转换，此时可以编写自定义函数进行人民币大
    页脚

    .
    写转换，如下面的代码所示。
    #001PublicFunctionRMBDX(M
    #002RMBDX=Replace(Application.Text(Round(M+0.00000001,2,"[DBnum2]",".","元"#003RMBDX=IIf(Left(Right(RMBDX,3,1="元",Left(RMBDX,Len(RMBDX-1&"角"&Right(RMBDX,1&"分",IIf(Left(Right(RMBDX,2,1="元",RMBDX&"角整",IIf(RMBDX="零","",RMBDX&"元整"
    #004RMBDX=Replace(Replace(Replace(Replace(RMBDX,"零元零角","","零元","","零角","零","-","负"
    #005EndFunction
    代码解析：
    第2行代码首先使用Round函数对小写数字加上极小值后进行四舍五入运算，关于Round函数请参阅技巧157-1。其次使用工作表Text函数将数值转换成人民币大写格式表示的文本。Text函数将数值转换为按指定数字格式表示的文本，语法如下：
    TEXT(value,format_text
    Value参数为数值、计算结果为数值的公式，或对包含数值的单元格的引用。Format_text参数为“单元格格式“对话框中”数字“选项卡上”分类框中的文本形式的数字格式。
    最后使用Replace函数将人民币大写格式表示的文本中的小数点替换成“元”。Replace函数返回一个字符串，该字符串中指定的子字符串已被替换成另一子字符串，并且替换发生的次数也是指定的，语法如下：
    Replace(expression,find,replace[,start[,count[,compare]]]
    其中参数expression是必需的，包含要替换的子字符串。参数find是必需的，要搜索到的子字符串。参数replace是必需的，用来替换的子字符串。
    参数start是可选的，在表达式中子字符串搜索的开始位置。
    第3行代码使用了IIF函数、Left函数、Right函数根据第2行代码返回的人民币大写格式表示的文本中的“元”的位置在文本中插入正确的“元”、“角”、“分”字符，使之符合人民币大写习惯。
    IIf函数根据表达式的值，来返回两部分中的其中一个，语法如下：
    IIf(expr,truepart,falsepart
    参数expr是必需的，用来判断真伪的表达式。
    参数truepart是必需的，如果expr为True，则返回这部分的值或表达式。参数falsepart是必需的，如果expr为False，则返回这部分的值或表达式。
    页脚

    .
    Left、Right函数请参阅技巧158。
    第4行代码使用Replace函数将人民币大写格式表示的文本中可能出现的“零元零角”、“零元”替换成空白字符；可能出现的“零角”替换成“零”。如果输入负数的话，将“-”替换成“负”。
    在工作表中使用自定义RMBDX函数转换人民币大写的效果如图163-1所示。

    图163-1人民币大写转换
    技巧12列号转换为列标
    使用VBA获取单元格的列号时，只能返回一个数值。如果需要获取以字符表示的列标，可以使用下面的自定义GetColumn函数过程。
    #001FunctionGetColumn(CAsIntegerAsString#002GetColumn=Split(Cells(1,C.Address,"$"(1#003EndFunction
    代码解析：
    GetColumn函数过程代码中，将参数iCol作为列号传递给Cells属性，并获取其绝对地址字串符，然后以“$”字符为分隔符，通过Split函数返回一个一维数组。
    Split函数返回一个下标从零开始的一维数组，它包含指定数目的子字符串，语法如下：
    Split(expression[,delimiter[,limit[,compare]]]
    其中参数expression是必需的，包含子字符串和分隔符的字符串表达式。如果expression是一个长度为零的字符串(""，则返回一个空数组，即没有元素和数据的数组。
    参数delimiter是可选的，用于标识子字符串边界的字符串字符。如果忽略，则使用空格字符(""作为分隔符。
    返回一维数组后获取该数组的第2个元素（下标为1），即该列号的字符列标。
    页脚

    .
    下面的代码使用GetColumn函数过程获得所选单元格的字符列标。
    #001PrivateSubWorksheet_SelectionChange(ByValTargetAsRange#002MsgBoxGetColumn(Selection.Column#003EndSub
    在工作表中选择单元格后结果如图164-1所示。

    图164-1返回列标字符串
    技巧13判断工作表是否为空表
    VBA中没有专门的属性或函数可以判断工作表是否为空白工作表，可以使用自定义函数返回指定工作表是否为空工作表，如下面的代码所示。
    #001FunctionIsBlankSht(ShAsVariantAsBoolean
    #002IfTypeName(Sh="String"ThenSetSh=Worksheets(Sh#003IfApplication.CountA(Sh.UsedRange.Cells=0Then#004IsBlankSht=True#005EndIf#006EndFunction
    代码解析：
    自定义IsBlankSht函数包含一个Variant变量类型的参数，代表工作表名称或者对象名称。如果指定的工作表为空工作表，则该函数返回True。
    页脚

    .
    第2行代码使用TypeName函数判断参数Sh是否为字符串类型（“String”），如果是字符串，则将以该字符串作为名称的工作表赋值给变量Sh。
    第3行代码通过工作表函数CountA统计工作表已使用区域的非空单元格个数，如果统计结果为0，则表示该工作表为空工作表。
    现在就可以像使用VBA函数一样使用自定义的IsBlankSht函数，如下面的代码所示。
    #001SubDelBlankSht(#002DimShAsWorksheet
    #003Application.DisplayAlerts=False#004ForEachShInThisWorkbook.Sheets#005IfIsBlankSht(ShThenSh.Delete#006Next
    #007Application.DisplayAlerts=True#008EndSub
    代码解析：
    使用自定义的IsBlankSht函数删除工作簿中所有空工作表。
    第3行代码将Application对象的DisplayAlerts属性设置为False，使删除时不显示系统警告对话框。
    第4行到第6行代码，使用ForEach...Next语句遍历所有工作表，使用自定义的IsBlankSht函数判断是否为空表，如果为空表则使用Delete方法删除。
    注意自定义IsBlankSht函数仅仅判断工作表单元格区域容是否为空，如果工作表中存在其它对象（如图形对象、数据有效性、单元格批注等），还需要再进一步判断。
    技巧14查找指定工作表
    判断工作簿中是否存在指定名称的工作表，除了使用遍历工作簿中所有工作表的方法外，还可以使用自定义函数，如下面的代码所示。
    #001FunctionExistSh(ShAsStringAsBoolean#002DimShtAsObject#003OnErrorResumeNext页脚

    .
    #004SetSht=Sheets(Sh
    #005IfErr.Number=0ThenExistSh=True#006SetSht=Nothing#007EndFunction
    代码解析：
    自定义ExistSh函数包含一个String类型的参数，代表需要判断的工作表名称。如果该工作表存在，则返回True。
    第5行代码判断前面的代码是否出错，如果前面的代码存在错误，则表示不存在指定名称的表。
    使用自定义ExistSheet函数判断工作簿中是否存在指定名称的工作表，如下面的代码所示。
    #001SubNotSht(#002DimShAsString
    #003Sh=InputBox("请输入工作表名称："#004IfLen(Sh>0Then#005IfNotExistSh(ShThen
    #006MsgBox"对不起,"&Sh&"表不存在!"#007Else
    #008Sheets(Sh.Select#009EndIf#010EndIf#011EndSub
    代码解析：
    NotSht过程使用自定义的ExistSh函数判断工作簿中是否存在指定名称的工作表，如果不存在则使用消息框进行提示，如图166-1所示。

    图166-1查找指定工作表
    页脚

    .
    技巧15查找指定工作簿是否打开
    如果需要判断指定名称的工作簿是否已经打开，除了使用技巧43的方法外，还可以使用与技巧166类似的自定义函数，如下面的代码所示。
    #001FunctionExistWorkbook(WbNameAsStringAsBoolean#002DimwbAsWorkbook#003OnErrorResumeNext
    #004Setwb=Workbooks(WbName
    #005IfErr.Number=0ThenExistWorkbook=True#006Setwb=Nothing#007EndFunction
    代码解析：
    自定义ExistWorkbook函数判断指定名称的工作簿是否已经打开。
    第5行代码判断前面的赋值语句是否存在错误。如果没有指定名称的工作簿，则第4行代码会产生错误，自定义ExistWorkbook函数返回False。
    下面使用自定义ExistWorkbook函数判断名称为“ExcelHome”的工作簿是否已经打开，如果没有打开则使用消息框进行提示，如图167-1所示。
    #001SubNotWorkbook(
    #002IfNot(ExistWorkbook("ExcelHome"ThenMsgBox"对不起,ExcelHome工作簿没有打开!"
    #003EndSub

    图167-1消息框提示
    技巧16取得应用程序的安装路径
    页脚

    .
    使用自定义函数取得应用程序的安装路径，如下面的代码所示。
    #001FunctionGetSetupPath(AppNameAsString#002DimWSHAsObject
    #003SetWSH=CreateObject("Wscript.Shell"
    #004GetSetupPath=WSH.RegRead("HKEY_LOCAL_MACHINE\Software"_#005&"\Microsoft\Windows\CurrentVersion\AppPaths\"_#006&AppName&"\Path"#007SetWSH=Nothing#008EndFunction#009SubWinRARPath(
    #010MsgBoxGetSetupPath("WinRAR.exe"#011EndSub
    代码解析：
    自定义GetSetupPath函数取得应用程序的安装路径，其中参数AppName代表指定的应用程序的名称。
    第3行代码使用CreateObject函数将Wscript.Shell对象的引用赋给变量WSH。CreateObject函数创建并返回一个对ActiveX对象的引用，语法如下：
    CreateObject(class,[servername]
    参数class是必需的，Variant(String，要创建的应用程序名称和类。
    参数servername是可选的，Variant(String，要在其上创建对象的网络服务器名称。如果servername是一个空字符串(""，即使用本地机器。
    第4行代码取得AppName参数指定的应用程序在注册表中的路径。
    WinRARPath过程使用消息框显示由自定义的GetSetupPath函数取得的应用程序“WinRAR”的安装路径。
    运行WinRARPath过程结果如图168-1所示。

    图168-1应用程序安装路径
    页脚

    .
    技巧17数组的使用
    17-1代码运行时创建数组
    使用Array函数可以在代码运行时创建数组并把一系列数据保存在数组中，示例代码如下：
    #001OptionBase1#002Subarr(
    #003DimarrAsVariant#004DimiAsInteger
    #005arr=Array("王晓明","吴胜玉","周志国","武伟","新发","卓雪梅","煜婷","丁林平"#006Fori=LBound(arrToUBound(arr#007Cells(i,1=arr(i#008Next#009EndSub
    代码解析：
    Arr过程使用Array函数创建一个数组用来保存数据并将其写入到工作表的单元格区域。
    第1行代码使用OptionBase语句声明数组下标的缺省下界为1，数组下标的缺省下界默认为0。
    第5行代码使用Array函数创建数组用来保存数据。Array函数返回一个包含数组的Variant，语法如下：
    Array(arglist
    Arglist参数是一个用逗号隔开的值表，这些值用于给Variant所包含的数组的各元素赋值。如果不提供Arglist参数，则创建一个长度为0的数组。
    第6行代码使用LBound函数和UBound函数取得数组的最小和最大下标。
    LBound函数返回一个Long型数据，其值为指定数组维可用的最小下标，语法如下：
    LBound(arrayname[,dimension]
    UBound函数返回一个Long型数据，其值为指定数组维可用的最大下标，语法如下：
    UBound(arrayname[,dimension]
    参数arrayname是必需的，数组变量的名称。
    参数dimension是可选的，指定返回哪一维的下界，1表示第一维，2表示第二维，如此类推。默认为1。
    页脚

    .
    UBound函数与LBound函数一起使用，可以用来确定数组的大小。
    第7行代码确定数组的大小后使用For...Next语句遍历数组元素并将数组元素依次写入到工作表的A列单元格中，如图169-1所示。

    图169-1将数组元素写入工作表
    17-2文本转换为数组
    在处理字符串时可以使用Split函数将字符串按指定的分隔符分开并以数组返回，代码如下：
    #001SubSplitarr(
    #002DimArrAsVariant
    #003Arr=Split(Sheet2.Cells(1,1,","
    #004Sheet1.Cells(1,1.Resize(UBound(Arr+1,1=Application.Transpose(Arr#005EndSub
    代码解析：
    Splitarr过程使用Split函数将工作表Sheet2中A1单元格的分别写入到工作表Sheet1中的A列单元格。
    Split函数返回一个下标从零开始的一维数组，包含指定数目的子字符串，语法如下：
    Split(expression[,delimiter[,limit[,compare]]]
    参数expression是必需的，包含子字符串和分隔符的字符串表达式。
    参数delimiter是必需的，用来标识子字符串边界的字符串字符。如果忽略，则使用空格字符(""作为分隔符。
    第4行代码，首先使用UBound函数取得返回数组的最大下标后调整单元格区域，因为数组下标的缺省下界默认为0，所以在使用Resize属性调整单元格区域时参数RowSize需要在返回数组的最大下标上加一。
    然后使用工作表Transpose函数将返回数组转置后写入到工作表调整后的单元格区域
    页脚

    .
    中。
    工作表Transpose函数返回转置单元格区域，即将一行单元格区域转置成一列单元格区域，反之亦然，语法如下：
    TRANSPOSE(array
    参数array为需要进行转置的数组或工作表中的单元格区域。
    Splitarr过程将如图169-2所示的工作表单元格中的字符串以逗号分隔后依次写入到工作表的A列单元格中，如图169-3所示。

    图169-2工作表单元格中的字符串
    页脚

    .

    图169-3文本转换为数组写入单元格
    17-3使用动态数组去除重复值
    在技巧169-2中使用数组函数将单元格中的文本进行分隔后写入到工作表Sheet1中的A列单元格，但是如果文本中含有大量的重复值，在写入时也会将重复值写入到工作表中，此时可以使用动态数组去除文本中的重复值，如下面的代码所示。
    #001SubSplitarr(
    #002DimSplarr(AsString#003DimArr(AsString#004DimTemp(AsString#005DimrAsInteger
    页脚

    .
    #006DimiAsInteger#007OnErrorResumeNext
    #008Splarr=Split(Sheet2.Range("a1",","#009Fori=0ToUBound(Splarr#010Temp=Filter(Arr,Splarr(i#011IfUBound(Temp<0Then#012r=r+1
    #013ReDimPreserveArr(1Tor#014Arr(r=Splarr(i#015EndIf#016Next
    #017Sheet1.Range("a1".Resize(r,1=Application.Transpose(Arr#018EndSub
    代码解析：
    Splitarr过程将工作表Sheet2中A1单元格的文本去除重复值后写入到工作表Sheet1中的A列单元格。
    第2行代码声明数组Splarr用来保存Sheet2中A1单元格的文本。第3行代码声明数组Arr用来保存去除重复值后的文本。第4行代码声明数组Temp用来判断文本是否重复。第5行代码声明变量r用来保存去除重复值后的文本数量。
    第7行代码启动错误处理程序来忽略错误，因为在程序运行到第11行代码会发生下标越界错误。
    第8行代码使用Split函数以Sheet2中A1单元格的文本创建一个下标从零开始的一维数组。关于Split函数请参阅技巧169-2。
    第9行代码使用For...Next语句遍历数组Splarr的所有元素。
    第10行代码使用Filter函数创建一个数组Temp用来保存以当前Splarr数组的值在Arr数组中的搜索结果。Filter函数返回一个下标从零开始的数组，该数组包含基于指定筛选条件的一个字符串数组的子集，语法如下：
    Filter(sourcesrray,match[,include[,compare]]
    参数sourcesrray是必需的，要执行搜索的一维字符串数组。参数match是必需的，要搜索的字符串。
    参数include是可选的，Boolean值，表示返回子串是否包含match字符串。如果参数include是True，Filter函数返回的是包含match参数子字符串的数组子集。如果参数include
    页脚

    .
    是False，Filter函数返回的是不包含match参数子字符串的数组子集。
    参数compare是可选的，所使用的字符串比较类型。
    第11行代码根据返回的数组Temp的最大下标来判断当前Splarr数组的值是否重复。在使用使用Filter函数时如果没有相匹配的值，将返回一个空数组，最大下标小于0。
    第12行代码如果当前Splarr数组的值不重复则将变量r的值加1。
    第13行代码重新定义动态数组大小。ReDim语句，在过程级别中使用，用于为动态数组变量重新分配存储空间，语法如下：
    ReDim[Preserve]varname(subscripts[Astype][,varname(subscripts[Astype]]
    参数Preserve是可选的，关键字，当改变原有数组最末维的大小时，使用此关键字可以保持数组中原来的数据。
    参数varname是必需的，变量的名称。
    参数subscripts是必需的，数组变量的维数，最多可以定义60维的多维数组，使用下面的语法；
    [lowerTo]upper[,[lowerTo]upper]
    第14行代码将不重复值添加到数组Arr中。
    第15行代码使用工作表Transpose函数将去除重复值的的文本转置后写入到工作表的A列单元格中。
    如果需要将去除重复值的的文本写入到第一行单元格中，可以将第15行代码改成下面的代码：
    Sheet1.Range("a1".Resize(1,r=Arr
    如果需要将去除重复值的的文本还是以逗号作为分隔符写入到A1单元格中，可以将第15行代码改成下面的代码：
    Sheet1.Range("a1"=Join(Arr,","
    Join函数返回一个字符串，该字符串是通过连接某个数组中的多个子字符串而创建的，语法如下：
    Join(sourcearray[,delimiter]
    参数sourcearray是必需的，包含被连接子字符串的一维数组。
    参数delimiter是可选的，在返回字符串中用于分隔子字符串的字符，如果忽略则使用空格(""来分隔子字符串。
    页脚

    免费下载 Word文档免费下载： Excel_VBA_函数使用

    TOP热门搜索

    相关推荐：

    建国70周年辉煌事件

    • ￥29.8

      ￥45 每天只需1.0元
      1个月 推荐

    • ￥9.9

      ￥15
      1天

    • ￥59.8

      ￥90
      3个月

    选择支付方式

    • 微信付款
    会员须知 联系在线客服 登录账号
    郑重提醒：支付后，系统自动为您完成注册

    请使用微信扫码支付(元)

    订单号：
    支付后，系统自动为您完成注册
    遇到问题请联系 在线客服

    常用手机号：
    用于找回密码
    图片验证码：
    看不清？点击更换
    短信验证码：
    新密码：
     
    绑定后可用手机号登录
    请不要关闭本页面，支付完成后请点击【支付完成】按钮
    遇到问题请联系 在线客服

    侵权投诉  © 2013-2019 http://www.tcm999.cn   站点地图 |  手机版

    本站文档均来自互联网及网友上传分享，本站只负责收集和整理，有任何问题可通过上访投诉通道进行反馈