Excel_VBA_函数使用.doc

函数的使用技巧1 调用工作表函数求和在对工作表的单元格区域进行求和计算时，使用工作表Sum函数比使用VBA代码遍历单元格进行累加求和效率要高得多，代码如下所示。#001 Sub rngSum()#002 Dim rng As Range#003 Dim d As Double#004 Set rng = Range(A1:F7)#005 d = Application.WorksheetFunction.Sum(rng)#006 MsgBox rng.Address(0, 0) & 单元格的和为 & d#007 End Sub代码解析：rngSum过程调用工作表Sum函数对工作表的单元格区域进行求和计算。在VBA中调用工作表函数需要在工作表函数前加上WorksheetFunction属性。应用于Application对象的WorksheetFunction属性返回WorksheetFunction对象，作为VBA中调用工作表函数的容器，在实际应用中可省略Application对象识别符。技巧2 查找最大、最小值在VBA中没有内置的函数可以进行最大、最小值的查找，借助工作表Max、Min函数可以快速地在工作表区域中查找最大、最小值，如下面的代码所示。#001 Sub seeks()#002 Dim rng As Range#003 Dim myRng As Range#004 Dim k1 As Integer, k2 As Integer#005 Dim max As Double, min As Double#006 Set myRng = Sheet1.Range(A1:F30)#007 For Each rng In myRng#008 If rng.Value = WorksheetFunction.max(myRng) Then#009 rng.Interior.ColorIndex = 3#010 k1 = k1 + 1#011 max = rng.Value#012 ElseIf rng.Value = WorksheetFunction.min(myRng) Then#013 rng.Interior.ColorIndex = 5#014 k2 = k2 + 1#015 min = rng.Value#016 Else#017 rng.Interior.ColorIndex = 0#018 End If#019 Next#020 MsgBox 最大值是: & max & 共有 & k1 & 个 _#021 & Chr(13) & 最小值是: & min & 共有 & k2 & 个#022 End Sub代码解析：seeks过程在工作表单元格区域中查找最大、最小值，并将其所在的单元格底色分别设置为红色和蓝色。第2行到第5行代码声明变量类型。第6行代码使用关键字Set将单元格引用赋给变量myRng。第7行到第19行代码遍历单元格区域，使用工作表Max、Min函数判断单元格数值是否是所在区域的最大、最小值，如果是，将其所在的单元格底色设置为红色或蓝色，并保存其数值和数量。第20、21行代码使用消息框显示最大、最小值数值和数量。运行seeks过程后将工作表区域最大、最小值所在的单元格的底色设置为红色或蓝色并用消息框显示其数值和数量，如图 1541所示。图 21查找最大、最小值技巧3 不重复值的录入在工作表中录入数据时，有时希望能限制重复值的录入，比如在示例的A列单元格只能录入唯一的人员编号，此时可以利用工作表的Change事件结合工作表的CountIf 函数来判断所录入的人员编号是否重复，示例代码如下。#001 Private Sub Worksheet_Change(ByVal Target As Range)#002 With Target#003 If .Column 1 Or .Count 1 Then Exit Sub#004 If Application.CountIf(Range(A:A), .Value) 1 Then#005 .Select#006 MsgBox 不能输入重复的人员编号!, 64#007 Application.EnableEvents = False#008 .Value = #009 Application.EnableEvents = True#010 End If#011 End With#012 End Sub代码解析：工作表的Change事件过程，使A列单元格只能录入唯一的人员编号。第4行代码使用工作表的CountIf 函数来判断在A列单元格输入的人员编号是否重复。工作表的CountIf 函数计算区域中满足给定条件的单元格的个数，语法如下：COUNTIF(range, criteria)参数range为需要计算其中满足条件的单元格数目的单元格区域。参数criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。在示例中以所录入的人员编号与A列单元格区域进行比较，如果CountIf 函数的返回值大于1，说明录入的是重复编号。第5行代码，重新选择该单元格便于下一步清空后重新录入。第7、8、9行代码，清除录入的重复编号，在清除前将Application对象的EnableEvents属性设置为False，禁用事件。因为如果不禁用事件，那么在清除重复值的过程中会不断地触发工作表的Change事件，从而造成代码运行的死循环。经过以上的设置，在工作表的A列中只能录入唯一的人员编号，如果录入重复值会进行提示，如图 1551所示，点击确定后自动清除录入的重复编号。图 31限制重复值的录入技巧4 获得当月的最后一天在实际工作中经常需要根据给定的日期计算其所属月份的最后一天，此时可以使用DateSerial函数完成计算，如下面的代码所示。#001 Sub Serial()#002 Dim DateStr As Byte#003 DateStr = Day(DateSerial(Year(Date), Month(Date) + 1, 0)#004 MsgBox 本月的最后一天是 & Month(Date) & 月 & DateStr & 号#005 End Sub代码解析：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所示。图 41获得当月的最后一天技巧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函数，如下面的代码所示。#001 Sub aTestRound()#002 MsgBox Round(4.5)= & Round(4.5) & Chr(13) & Round(4.5)= & Round(4.5 + 0.0000001)#003 End Sub代码解析：aTestRound过程分别调用VBA内置的Round函数和加上极小值再调用VBA内置的Round函数在洗染店框中显示两者运算结果，如图 1571所示。图 51加上极小值进行运算结果从运算结果中可以发现，加上极小值后Round(4.5)已正确运算为5而不是4。5-2 调用工作表函数法还可以使用工作表函数Round代替VBA内置的Round函数。工作表函数Round和VBA内置的Round函数的用法相同，但它采用算术舍入而不是Bankre舍入，所以不会有“偶舍奇入”的问题，如下面的代码所示。#001 Sub bTestRound()#002 MsgBox Round(4.5)= & Round(4.5) & Chr(13) & Round(4.5)= & Application.Round(4.5, 0)#003 End Sub代码解析：bTestRound过程分别调用VBA内置的Round函数和工作表Round函数在消息框中显示两者运算结果，如图 1572所示。图 52工作表函数运算结果从运算结果中可以发现，使用工作表Round函数后Round(4.5)已正确运算为5而不是4。技巧6 使用字符串函数使用VBA的字符串函数可以对字符串进行各种操作，如下面的代码所示。#001 Sub StrFunctions()#002 Dim Str As String#003 Str = AbcD EFG hijk Lmn#004 MsgBox 原始字符串为： & 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)#011 End Sub代码解析：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所示。图 61使用字符串函数技巧7 使用日期函数使用VBA的日期函数可以对日期进行各种计算，如下面的代码所示。#001 Sub DatFunctions()#002 Dim Str As String#003 Dim Week As String#004 Str = InputBox(请输入日期：)#005 If Len(Str) 0 Then#006 If IsDate(Str) Then#007 Select Case Weekday(Str, vbMonday)#008 Case 1#009 Week = 一#010 Case 2#011 Week = 二#012 Case 3#013 Week = 三#014 Case 4#015 Week = 四#016 Case 5#017 Week = 五#018 Case 6#019 Week = 六#020 Case 7#021 Week = 日#022 End Select#023 MsgBox 你输入的日期是 & 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)#028 Else#029 MsgBox 请输入正确格式的日期!#030 End If#031 End If#032 End Sub代码解析：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所示。常数值描述vbUseSystem0使用 NLS API 设置VbSunday1星期日（缺省值）vbMonday2星期一vbTuesday3星期二vbWednesday4星期三vbThursday5星期四vbFriday6星期五vbSaturday7星期六表格 71firstdayofweek参数值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函数也能识别December 30, 1991 和Dec 30, 1991。如果date参数中略去了年这一部分，DateValue函数就会使用由计算机系统日期设置的当前年份。第24行代码判断输入的日期的季度。DatePart函数返回一个包含已知日期的指定时间部分的值，语法如下：DatePart(interval, date,firstdayofweek, firstweekofyear)其中参数interval是必需的，字符串表达式，是要返回的时间间隔，设定值如表格 1592所示。设置说明yyyy年q季m日y一年的日数d日w一周的日数ww周h时n分钟s秒表格 72interval参数设定值第26行代码计算所输入的日期距当天的天数。DateDiff函数返回两个指定日期间的时间间隔数目，语法如下：DateDiff(interval, date1, date2, firstdayofweek, firstweekofyear)其中参数interval是必需的，字符串表达式，表示用来计算date1和date2的时间差的时间间隔，设定值如表格 72所示。参数date1和date2是必需的，计算中要用到的两个日期。因为如果输入的日期是当前日期以前的日期，DateDiff函数会返回负值，所以使用Abs函数返回绝对值将其转换为正值。第27行代码计算所输入的日期距当天的天数，DateAdd返回加上了一段时间间隔的一个日期，语法如下：DateAdd(interval, number, date)参数interval是必需的，字符串表达式，是所要加上去的时间间隔，设定值如表格 72所示。参数number是必需的，是要加上的时间间隔的数目。其数值可以为正数（得到未来的日期），也可以为负数（得到过去的日期）。参数date是必需的，需要加上时间间隔的字符串表达式。运行DatFunctions过程，在显示的对话框中输入一个日期，结果如图 1591所示。图 71使用日期函数技巧8 判断是否为数值使用IsNumeric函数可以判断表达式的运算结果是否为数值，如下面的代码所示。#001 Sub Numeric()#002 Dim i As Integer#003 Dim n As String#004 Dim s As String#005 With Sheet1#006 For i = 1 To .Range(A65536).End(xlUp).Row#007 If IsNumeric(.Cells(i, 1) Then#008 n = n & .Cells(i, 1).Address(0, 0) & Chr(9) & .Cells(i, 1) & Chr(13)#009 Else#010 s = s & .Cells(i, 1).Address(0, 0) & Chr(9) & .Cells(i, 1) & Chr(13)#011 End If#012 Next#013 End With#014 MsgBox A列中数值单元格： & Chr(13) & n & Chr(13) _#015 & A列中非数值单元格： & Chr(13) & s#016 End Sub代码解析：Numeric过程使用IsNumeric函数判断工作表的A列单元格是否为数值，并使用消息框显示。第7行代码判断工作表的A列单元格是否为数值。IsNumeric函数返回Boolean值，指出表达式的运算结果是否为数，语法如下：IsNumeric(expression)参数expression是必需的，Variant类型，包含数值表达式或字符串表达式。如果参数expression的运算结果为数字，则IsNumeric返回True，否则返回False。第8行代码将数值单元格的地址和数值保存在变量 e中。第10行代码将非数值单元格的地址和内容保存在变量 s中。在保存时插入制表符对数据列进行分隔，使之排列整齐，请参阅技巧Error! Reference source not found.。运行Numeric过程结果如图 1601所示。图 81判断是否为数值技巧9 格式化数值、日期和时间Format函数是VBA中的常用函数，可以实现数值、日期和时间格式的转变，示例代码如下：#001 Sub FromatCurrent()#002 MsgBox Format(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, Long Date) & Chr(13) _#010 & Format(Now, hh:mm:ss) & Chr(13) _#011 & Format(Now, hh:mm:ss AMPM)#012 End Sub代码解析：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过程结果如图 1611所示。图 91格式化数值、日期和时间技巧10 个人所得税自定义函数在财务工作中经常需要计算个人所得税，而在Excel中没有计算个人所得税的函数，此时可以使用自定义函数来计算，如下面的代码所示。#001 Public Function PITax(Income, Optional Threshold) As Single#002 Dim Rate As Single#003 Dim Debit As Single#004 Dim Taxliability As Single#005 If IsMissing(Threshold) Then Threshold = 2000#006 Taxliability = Income - Threshold#007 Select Case Taxliability#008 Case 0 To 500#009 Rate = 0.05#010 Debit = 0#011 Case 500.01 To 2000#012 Rate = 0.1#013 Debit = 25#014 Case 2000.01 To 5000#015 Rate = 0.15#016 Debit = 125#017 Case 5000.01 To 20000#018 Rate = 0.2#019 Debit = 375#020 Case 20000.01 To 40000#021 Rate = 0.25#022 Debit = 1375#023 Case 40000.01 To 60000#024 Rate = 0.3#025 Debit = 3375#026 Case 60000.01 To 80000#027 Rate = 0.35#028 Debit = 6375#029 Case 80000.01 To 10000#030 Rate = 0.4#031 Debit = 10375#032 Case Else#033 Rate = 0.45#034 Debit = 15375#035 End Select#036 If Taxliability 0 Then#005 If Not ExistSh(Sh) Then#006 MsgBox 对不起, & Sh & 表不存在!#007 Else#008 Sheets(Sh).Select#009 End If#010 End If#011 End Sub代码解析：NotSht过程使用自定义的ExistSh函数判断工作簿中是否存在指定名称的工作表，如果不存在则使用消息框进行提示，如图 1661所示。图 141查找指定工作表技巧15 查找指定工作簿是否打开如果需要判断指定名称的工作簿是否已经打开，除了使用Error! Reference source not found.的方法外，还可以使用与技巧14 类似的自定义函数，如下面的代码所示。#001 Function ExistWorkbook(WbName As String