VBA常用技巧代码解析
VBA常用技巧
目录
VBA常用技巧 ................................................................................................................................... 1
第9章 函数的使用 ........................................................................................................... 2
技巧1 调用工作表函数求和 ....................................................................................... 2 技巧2 查找最大、最小值 ........................................................................................... 2 技巧3 不重复值的录入 ............................................................................................... 4 技巧4 获得当月的最后一天 ....................................................................................... 6 技巧5 四舍五入运算 ................................................................................................... 7
5-1 极小值修正法 ................................................................................................ 7 5-2 调用工作表函数法......................................................................................... 8 技巧6 使用字符串函数 ............................................................................................... 8 技巧7 使用日期函数 ................................................................................................. 10 技巧8 判断是否为数值 ............................................................................................. 14 技巧9 格式化数值、日期和时间 ............................................................................. 15 技巧10 个人所得税自定义函数 ............................................................................... 17 技巧11 人民币大写函数 ........................................................................................... 19 技巧12 列号转换为列标 ........................................................................................... 21 技巧13 判断工作表是否为空表 ............................................................................... 22 技巧14 查找指定工作表 ........................................................................................... 23 技巧15 查找指定工作簿是否打开 ........................................................................... 25 技巧16 取得应用程序的安装路径 ........................................................................... 25 技巧17 数组的使用 ................................................................................................... 27
17-1 代码运行时创建数组................................................................................. 27 17-2 文本转换为数组 ........................................................................................ 28 17-3 使用动态数组去除重复值 ......................................................................... 30
1
VBA常用技巧代码解析
第9章 函数的使用
技巧1 调用工作表函数求和
在对工作表的单元格区域进行求和计算时，使用工作表Sum函数比使用VBA代码遍历单元格进行累加求和效率要高得多，代码如下所示。
#001 Sub rngSum()
#002 Dim rng As Range #003 Dim d As Double
#004 Set rng = Range(\
#005 d = Application.WorksheetFunction.Sum(rng) #006 MsgBox rng.Address(0, 0) & \单元格的和为\#007 End Sub
代码解析：
rngSum过程调用工作表Sum函数对工作表的单元格区域进行求和计算。
在VBA中调用工作表函数需要在工作表函数前加上WorksheetFunction属性。应用于Application对象的WorksheetFunction属性返回WorksheetFunction对象，作为VBA中调用工作表函数的容器，在实际应用中可省略Application对象识别符。
技巧2 查找最大、最小值
在VBA中没有内置的函数可以进行最大、最小值的查找，借助工作表Max、Min函数可以快速地在工作表区域中查找最大、最小值，如下面的代码所示。
#001 Sub seeks()
2
VBA常用技巧代码解析
#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(\#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 \最大值是:\共有 \个\#021 & Chr(13) & \最小值是:\共有 \个\#022 End Sub
代码解析：
seeks过程在工作表单元格区域中查找最大、最小值，并将其所在的单元格底色分别设置为红色和蓝色。
第2行到第5行代码声明变量类型。
第6行代码使用关键字Set将单元格引用赋给变量myRng。
第7行到第19行代码遍历单元格区域，使用工作表Max、Min函数判断单元格数值是否是所在区域的最大、最小值，如果是，将其所在的单元格底色设置为红色或蓝色，并保存其数值和数量。
第20、21行代码使用消息框显示最大、最小值数值和数量。
运行seeks过程后将工作表区域最大、最小值所在的单元格的底色设置为红色或蓝色并用消息框显示其数值和数量，如图 2-1所示。
3
VBA常用技巧代码解析
图 2-1 查找最大、最小值
技巧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(\#005 .Select
#006 MsgBox \不能输入重复的人员编号!\#007 Application.EnableEvents = False #008 .Value = \
#009 Application.EnableEvents = True
4
VBA常用技巧代码解析
#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列中只能录入唯一的人员编号，如果录入重复值会进行提示，如图 3-1所示，点击确定后自动清除录入的重复编号。
图 3-1 限制重复值的录入
5