该博客主要记录了Excel常用属性、函数和方法。涵盖交互函数、单元格调整移动、字符串处理、数学运算、日期时间处理、查找定位替换、排序筛选填充、文件目录操作等内容，还介绍了调用Excel内置函数的方法，并给出了各函数和方法的语法、参数及示例。
第3部分 常用属性、函数和方法
- 属性、函数和方法的区别：
- 属性：描述对象的特征或状态，必须与对象相关联（表达方式：对象.属性 ），通常用于获取或设置对象的状态或属性值
- 函数：通常以独立的方式调用（表达方式：函数名（参数1，参数2）），有明确的输入和输出
- 方法：本身就是对象的一部分，必须与对象相关联（表达方式：对象.方法 ），并用于改变对象的状态或执行操作，例如：保存、新增工作簿，以及在工作表对象中查找一个值
3.1 交互函数
3.1.1 弹窗 MsgBox 函数
- 功能：显示一个包含消息和按钮的对话框，用于与用户进行交互。
- 语法：MsgBox(prompt, [buttons], [title], [helpfile], [context])
- 参数：
- prompt：必选参数，为要显示的消息文本，字符串或者用变量代表的字符串
- buttons：可选参数，不同的参数对话框显示不同的按钮，可以多选，参数之间用+连接
- vbYesNo：对话框显示“是”和“否”按钮
- vbInformation：信息对话框，通常用于显示一般性的信息或提示
- vbQuestion：询问对话框，用于询问用户关于某个操作或选择的意见
- title：可选参数，为对话框左上角的标题
- 返回值：表示用户在对话框中点击了不同按钮，可以用来判断用户的选择从而控制代码的运行（不在MsgBox 函数的语法中，可以用变量记录返回值或者直接调用该函数，通常和If 语句一起使用）
- vbYes：用户点击了是按钮
- vbNo：用户点击了否按钮
- 其他参数和返回值：MsgBox 常量 | Microsoft Learn
- 示例：
' 显示一个简单的信息框 MsgBox "Hello, welcome to VBA programming!" ' 显示一个包含是、否按钮的询问框 If MsgBox("Do you want to save changes?", vbQuestion + vbYesNo, "Save Changes") = vbYes Then ' 用户点击了是按钮 ' 执行保存操作 Else ' 用户点击了否按钮或关闭了对话框 ' 不执行保存操作 End If
3.1.2 输入框 InputBox 函数
- 功能：以对话框的形式从用户那里获取输入
- 语法：InputBox(Prompt, [Title], [Default], [XPos], [YPos])
- 参数：
- prompt：是一个字符串表达式，用于向用户显示提示消息，指示他们应该输入什么
- title：可选参数，是一个字符串表达式，为输入框的标题
- default：可选参数，是一个字符串表达式，为输入框中的默认文本
- XPos和 YPos：可选参数，是数字表达式，为指定输入框相对于屏幕或父窗口的位置，默认为屏幕的水平居中、垂直从上往下大约三分之一的位置。XPos和 YPos同时为0的在屏幕的左上角位置。
- 示例：弹出一个输入框，要求用户输入姓名
Sub GetUserInput() Dim userInput As String ' 提示用户输入姓名，并将输入保存到变量中 userInput = InputBox("请输入您的姓名:", "姓名输入") ' 检查用户是否输入了内容 If userInput <> "" Then MsgBox "您输入的姓名是：" & userInput Else MsgBox "您没有输入姓名。" End If End Sub
3.2 单元格调整和移动
3.2.1 地址 Address 属性
- 功能：表示指定单元格范围的地址
- 语法：Range.Address([RowAbsolute], [ColumnAbsolute], [ReferenceStyle], [External], [RelativeTo])
- 参数：
- RowAbsolute：可选参数，指定行是否为绝对引用（有美元符号，例如$A$1)。默认值为 True，表示行是绝对引用
- ColumnAbsolute：可选参数，指定列是否为绝对引用。默认值为 True，表示列是绝对引用
- 其他参数：Range.Address 属性 (Excel) | Microsoft Learn
- 示例：获取A1:B2 的单元格的绝对地址和相对地址
Sub AddressExample() Dim rng As Range Set rng = Range("A1:B2") ' 获取范围绝对地址并输出 MsgBox "范围地址：" & rng.Address ' 获取范围相对地址并输出 MsgBox "范围地址：" & rng.Address(0, 0) End Sub
3.2.2 位移 Offset 函数
- 功能：在 VBA 中进行单元格的动态定位和操作，常用于获取偏移后的单元格
- 语法：Offset(rowoffset, columnoffset)
- 参数：
- rowoffset：要偏移的行数，可以是正数（向下偏移）或负数（向上偏移）
- columnoffset：要偏移的列数，可以是正数（向右偏移）或负数（向左偏移）
- 注意点：行偏移和列偏移是相对于当前单元格位置的，也就是说计算移动时偏移的行和列数，是不包括本单元格在内的
- 示例：将A1单元格移动至B3，并写入"Offset Example"字符串
Sub OffsetExample() Dim rngStart As Range Dim rngOffset As Range ' 定义起始单元格 Set rngStart = Range("A1") ' 向下偏移2行，向右偏移1列至B3 Set rngOffset = rngStart.Offset(2, 1) ' 在偏移后的单元格中写入值 rngOffset.value = "Offset Example" End Sub
3.2.3 调整选中范围 Resize 函数
- 功能：动态调整指定范围的大小
- 语法：Resize(rowsize, columnsize)
- 参数：
- rowsize：要调整的行数，可以是正数（向下增加行数）或负数（向上减少行数）
- columnsize：要调整的列数，可以是正数（向右增加列数）或负数（向左减少列数）
- 注意点：行数和列数是相对于原始范围的，计算调整的行数和列数要包括原始范围在内
- 示例：先选中A1:C3 单元格，再将原范围增加1行2列，并写入值"Resize Example"
Sub ResizeExample() Dim rngOriginal As Range Dim rngResized As Range ' 定义原始范围，假设为 A1:C3 Set rngOriginal = Range("A1:C3") ' 调整范围大小，增加1行，增加2列 Set rngResized = rngOriginal.Resize(rngOriginal.Rows.Count + 1, rngOriginal.Columns.Count + 2) ' 在调整后的范围中写入值 rngResized.Value = "Resize Example" End Sub
3.2.4 合并范围 Union 方法
- 功能：合并多个单元格范围
- 语法：Union(range1, range2, ...)
- 参数：
- range1, range2, ...：要合并的多个单元格范围，可以是单个单元格或多个单元格组成的范围
- 示例：把三个单元格范围rng1、rng2、rng3合并成一个新的范围 rngUnion
Sub UnionExample() Dim rng1 As Range, rng2 As Range, rng3 As Range, rngUnion As Range ' 定义要合并的单元格范围 Set rng1 = Range("A1:B2") Set rng2 = Range("C1:D2") Set rng3 = Range("E1:F2") ' 合并单元格范围 Set rngUnion = Union(rng1, rng2, rng3) ' 在合并后的范围中写入值 rngUnion.Value = "Union Example" End Sub
3.2.5 交集范围 Intersect 方法
- 功能：获取两个或多个单元格范围的交集，如果没有交集，则返回Nothing
- 语法：Intersect(range1, range2, ...)
- 参数：
- range1, range2, ...：要获取交集的多个单元格范围，可以是单个单元格或多个单元格组成的范围
- 示例：判断两个单元格范围 rng1 和 rng2是否有交集，并输出交集范围
Sub IntersectExample() Dim rng1 As Range, rng2 As Range, rngIntersect As Range ' 定义要获取交集的单元格范围 Set rng1 = Range("A1:B2") Set rng2 = Range("B1:C3") ' 获取交集范围 Set rngIntersect = Intersect(rng1, rng2) ' 判断是否有交集并输出交集范围地址 If Not rngIntersect Is Nothing Then MsgBox "交集范围地址：" & rngIntersect.Address Else MsgBox "没有交集范围" End If End Sub
3.3 字符串处理
3.3.1 字符串长度 Len 函数
- 功能：计算字符串的长度
- 语法：Len(string)
- 示例：Len("Hello") 返回 5，因为字符串 "Hello" 包含 5 个字符
3.3.2 查找字符位置 InStr 函数
- 功能：从左到右，查找一个字符串在另一个字符串中的位置
- 语法：InStr([start], string1, string2, [compare])
- 参数：
- string：字符串，在 string1 中查找 string2 的位置
- start：可选参数，从start位置开始搜索，默认为字符串的第一个字符
- compare：可选参数，默认为vbBinaryCompare
解释：二进制比较是基于字符的 ASCII 值进行比较的，因为大小写字母ASCII 值不同，所以使用二进制比较会区分大小写，如果不想区分大小写，则使用文本比较方式。常量 值 说明 vbBinaryCompare 0 使用二进制比较，区分字母大小写 vbTextCompare 1 使用文本比较，不区分字母大小写 vbDatabaseCompare 2 使用数据库排序规则比较，通常与数据库相关
vbUseCompareOption -1 使用 Option Compare 语句设置比较方式
- 示例： 使用不同的比较方式查找字符串的位置
Sub InStrExample() ' 使用二进制比较方式查找子字符串位置 Position1 = InStr(1, "ABC abc", "b", vbBinaryCompare) MsgBox Position1 ' 使用文本比较方式查找子字符串位置 Position2 = InStr(1, "ABC abc", "b", vbTextCompare) MsgBox Position2 End Sub
3.3.3 反方向查找字符位置 InStrRev 函数
- 功能：和InStr 函数顺序相反，从右到左查找一个字符串在另一个字符串中的位置
- 语法：InStrRev(string1, string2[, start[, compare]])
- 参数：
- string：字符串，在 string1 中查找 string2 的位置
- start：可选参数，从右向左开始，默认为字符串的最后一个字符
- compare：可选参数，同InStr 函数
- 注意点：查找的顺序是相反的，从右到左开始找，但返回位置的值还是在字符串中从左往右数的顺序
- 示例：InStrRev("Hello World", "o") 返回 8，因为字符串 "o" 在字符串 "Hello World" 中最后一次出现的位置是第 8 个字符处
3.3.4 从中提取字符串 Mid 函数
- 功能：从字符串中提取指定位置和长度的子字符串
- 语法：Mid(string, start[, length])
- 参数：
- start：指定的起始位置
- length：提取的字符串长度
- 示例：`Mid("Hello World", 7, 5)` 返回值为 "World"，从字符串的第 7 个字符开始提取长度为 5 的子字符串
3.3.5 从左/右提取字符串 LeftB 和 RightB 函数
- 功能：从字符串的左侧或右侧提取指定长度的字符
- 语法：Left(string, length) 或 Right(string, length)
- 参数：
- length: 要截取的字符数
- 示例：
Sub LeftRightExample() Dim originalString As String Dim leftPart As String originalString = "Hello, world!" ' 从左侧截取5个字符输出 "Hello" leftPart = Left(originalString, 5) MsgBox leftPart ' 从右侧截取6个字符输出 "world!" rightPart = Right(originalString, 6) MsgBox rightPart End Sub
3.3.6 移除空格 Trim 函数
- 功能：用于移除字符串两端的空格（包括空格、制表符和换行符等不可见字符）
- 语法：Trim(string)
- 示例：`Trim(" Hello World ")` 返回 "Hello World"，移除了两端的空格
3.3.7 比较 like 运算符
- 功能：比较两个字符串是否相似，通常用来检查一个字符串是否符合指定的模式，正确返回true，可以连接if语句一起使用
- 语法：string Like pattern
- string：要测试的字符串
- pattern：包含通配符和字符列表的模式字符串，用于指定匹配规则
- 注意：比较时区分大小写
- 通配符：
- *：匹配零个或多个字符
- ?：匹配一个任意字符
- #：匹配一个数字（0-9）
- [charlist]：匹配字符列表中的任意一个字符
- [!charlist]：匹配不在字符列表中的任意一个字符
- 示例：
- "apple" Like "a*p*e"：返回 True，因为模式中的 * 可以匹配任意字符
- "a" Like "[a-d]"：返回 True，因为字符 a 在字符范围 [a-d] 中
3.4 数学运算
3.4.1 绝对值 Abs 函数
- 功能：返回一个数的绝对值，即该数与零的差的绝对值
- 语法：Abs(number)
- 示例：数值-10的Abs 函数返回值为10
Dim num% num = -10 Msgbox Abs(num) ' 输出 10
3.4.2 保留小数 Round 函数
- 功能：返回一个数的四舍五入值，保留小数的位数根据 num_digits 参数设置
- 语法：Round(number, num_digits)
- 示例：
Dim value As Double value = 3.14159 Msgbox Round(value, 2) ' 输出 3.14
3.4.3 取整 Int 函数
- 功能：取整到最接近的整数
- 语法：Int(number)
- 示例：
Dim num As Double num = 3.9 MsgBox Int(num) ' 输出 3
3.4.4 随机数 Rnd 函数
- 功能：返回一个伪随机数，该数在 0 到 1 之间（不包括 1）
- 语法：Rnd([number])
- number (可选): 一个正数，用于生成随机数序列的种子值
- 解释：
- 计算机生成随机数是用算法模拟出来的，并不具有真正的随机性，所以称之为伪随机数
- 种子值决定了随机数序列的起点，指定种子值可以使得每次生成的随机数序列都是相同的，方便复现特定的测试场景
- 如果不指定种子值，VBA 默认使用系统时间作为种子值，这样每次执行程序时生成的随机数序列都会不同
- 示例：
Dim randomNum As Double Randomize ' 初始化随机数种子 randomNum = Rnd() Msgbox randomNum ' 输出 0 到 1 之间的随机数
3.4.5 生成随机数 Rnd 函数扩展
- 功能：通过结合数学运算，用Rnd 函数生成两个数之间的随机数
- 语法：Int((maxValue - minValue + 1) * Rnd + minValue)
- maxValue：随机数中的最大值
- minValue：随机数中的最小值
- 示例：生成 4 到 10 之间的随机数
Sub GenerateRandomNum() Dim randomNum As Integer ' 生成 4 到 10 之间的随机数 randomNum = Int((10 - 4 + 1) * Rnd + 4) MsgBox randomNum End Sub
3.4.6 平方根 SQR 函数
- 功能：返回一个数的平方根
- 语法：Sqr(number)
- 示例：取25的平方根
Dim num% num = 25 Msgbox Sqr(num) ' 输出 5
3.5 日期时间处理
3.5.1 转化格式 Format 函数
- 功能：将表达式格式化为指定的格式
- 语法：Format(expression, format)
- 参数：
- expression：要进行格式化的表达式，可以是日期、时间、数字等
- format：指定字符串的显示格式，以下为常用格式
- "yyyy-mm-dd"：以年-月-日的格式显示日期。
- "yyyy/mm/dd"：以年/月/日的格式显示日期。
- "dd-mmm-yyyy"：以日-月缩写-年的格式显示日期（如 01-Jan-2023）。
- "hh:mm:ss"：以时:分:秒的格式显示时间
- "0.00"：保留两位小数的数字格式
- "#,##0"：带千位分隔符的整数格式
- 示例：将日期转化为"yyyy-mm-dd" 的格式
Dim myDate As Date myDate = #4/15/2023# Dim formattedDate As String formattedDate = Format(myDate, "yyyy-mm-dd") Msgbox "Formatted date: " & formattedDate
3.5.2 当前时间 Now 函数
- 功能：返回包含当前日期和时间的Date 值，可以结合Format 函数将当前日期和时间格式化为特定的字符串形式
- 语法：
- Now() 或者Now
- 结合Format 函数：Format(Now, format)
- 示例：
Sub NowExample() Dim formattedDate As String ' 使用 Format 函数将日期和时间格式化为 "yyyy-mm-dd hh:mm:ss" 的形式 formattedDate = Format(Now, "yyyy-mm-dd hh:mm:ss") ' 输出格式化后的日期和时间 Debug.Print "当前日期和时间：" & formattedDate End Sub
3.5.3 计算间隔时间 DateDiff 函数
- 功能：返回一个整数，表示两个日期之间的时间间隔
- 语法：DateDiff(interval, date1, date2, [firstdayofweek], [firstweekofyear])
- 参数：
- interval：指定计算的时间间隔
如 "d" 表示天数，"m" 表示月数，"yyyy" 表示年数等参数 说明 yyyy 年 q 季度 m 月 y 日，和d一样 d 天 w 周，以7天为单位，表示两个日期之间间隔了多少个7天 ww 周，表示两个日期之间隔了多少周，指定一周的开始日期由 [firstdayofweek] 参数决定，如果date1和date2等于[firstdayofweek] ，计算间隔周包括date2，不包括date1 h 小时 n 分钟 s 秒 - date1 和 date2：要进行计算的两个日期，计算公式为date2减date1
- firstdayofweek：可选参数，指定一周的第一天是星期几，默认为星期天vbSunday
- firstweekofyear：可选参数，指定一年的第一周的起始方式，默认为第一个星期包含 1月1日的周
常数
值
说明
vbUseSystem
0
使用 NLS API 设置（国家语言支持应用程序接口），根据系统的地区设置或者用户自定义的日期格式来确定一年的第一周
vbFirstJan1
1
从包含1月1日的周开始
vbFirstFourDays
2
从新年至少有四天的第一周开始
vbFirstFullWeek
3
从一年中的第一个整周开始
- 其他参数：DateDiff 函数 (Visual Basic for Applications) | Microsoft Learn
- interval：指定计算的时间间隔
- 示例：计算2023-3-4和2023-3-24之间间隔的周数
Sub DateDiffExample() Dim weeks1%, weeks2% ' 将周的开始日期设为星期六，起始日期2023-3-4就是星期六，不计入间隔周数 weeks1 = DateDiff("ww", #3/4/2023#, #3/24/2023#, vbSaturday) Debug.Print "Weeks between dates: " & weeks1 ' 将周的开始日期设为星期天，起始日期2023-3-4为星期六，星期天算入下周，导致周数差异增加1 weeks2 = DateDiff("ww", #3/4/2023#, #3/24/2023#, vbSunday) Debug.Print "Weeks between dates: " & weeks2 End Sub
3.5.4 添加间隔时间 DateAdd 函数
- 功能：向指定日期添加一定的时间间隔，返回一个新的日期
- 语法：DateAdd(interval, number, date)
- 参数：
- interval：指定要添加的时间间隔，如 "d" 表示天数，"m" 表示月数，"yyyy" 表示年数等，参考DateDiff 函数中的interval
- number：要添加的间隔数，可以是正数表示加法，负数表示减法
- date：指定日期
- 示例：向2023/1/1添加3个月
Dim newDate As Date newDate = DateAdd("m", 3, #1/1/2023#) Debug.Print "New date: " & newDate
3.6 查找、定位和替换
3.6.1 查找字符串 Find 方法
- 功能：用于在字符串中查找指定的子字符串，返回值为其在字符串中的位置，如果找不到，则返回Nothing
- 语法：Find(What, [After], [LookIn], [LookAt], [SearchOrder], [MatchCase], [MatchByte], [SearchFormat])
- 参数：
- What：要查找的子字符串，" * "表示查找所有有内容的单元格
- After：可选参数，指定在哪个单元格之后开始查找，默认为从左上角单元格开始
- LookIn：可选参数，指定要查找的内容类型，可以是：
- xlFormulas 公式、xlValues 数值、xlComments 批注，默认为 xlValues
- LookAt：可选参数，指定匹配方式，可以是：
- xlWhole（1）：精确查找，默认为 xlWhole
- xlPart（2）：模糊查找
- SearchOrder：可选参数，指定查找顺序，可以是：
- xlByRows（1）：按行查找，默认为 xlByRows
- xlByColumns（2）：按列查找
- SearchDirection：可选参数，指定查找方向
- xlNext（1）：向下查找，默认为 xlNext
- xlPrevious（2）：向上查找
- MatchCase：可选参数，指定是否区分大小写，为 True 时区分大小写，为 False 时不区分大小写，默认为 False
- MatchByte：可选参数，指定是否区分全/半角字符，True 表示区分，False 表示不区分。默认为 False（全角字符通常为汉字，半角字符通常为英文、数字、符号）
- SearchFormat：可选参数，指定是否考虑单元格格式，True 表示区分，False 表示不区分。默认为 False
- 备注：考虑查找单元格格式，需要先设置搜索的格式，详见Application.FindFormat属性
- 示例：精确查找字符串apple，在Sheet1先后输入空字符、apple和apples进行测试
Sub FindValue() Dim ws As Worksheet Dim cell As Range Dim searchTerm As String Dim resultCell As Range ' 设置要查找的工作表 Set ws = ThisWorkbook.Sheets("Sheet1") ' 设置要查找的值 searchTerm = "apple" ' 在工作表中查找包含特定值的单元格 Set resultCell = ws.Cells.Find(What:=searchTerm, LookIn:=xlValues, LookAt:=xlWhole) ' 检查是否找到了匹配的单元格 If Not resultCell Is Nothing Then ' 找到匹配的单元格后，可以在这里处理结果，例如输出地址或者单元格内容 Debug.Print "找到匹配的单元格地址：" & resultCell.Address Debug.Print "匹配的单元格内容：" & resultCell.value Else ' 如果未找到匹配的单元格，输出未找到的信息 Debug.Print "未找到匹配的单元格" End If End Sub
3.6.2 设置查找格式 Application.FindFormat 属性
- 功能：用于设置搜索单元格的格式
- 语法：Application.FindFormat.PropertyName
- PropertyName: 要设置或返回的属性名称，可以是任何搜索格式的属性，如字体样式、文本颜色、背景颜色等
- 常用属性：
- Font：
- Color或者ColorIndex：设置字体颜色
- 解释：Color使用 RGB（红，绿，蓝） 格式表示颜色，红绿蓝的取值范围为 0 到 255；ColorIndex 表示 Excel 颜色板中的颜色索引，一共有56种
- ColorIndex颜色表：Excel VBA Interior.ColorIndex 色彩列表 - Alice Sun - 博客园 (cnblogs.com)
- Color颜色表：RGB 颜色表与十六进制颜色值码 - Codeeeee 在线小工具
- Name：设置字体名称
- Size：设置字体大小
- FontStyle：设置字体样式，如加粗、斜体为"Bold Italic"，或者用Font.Bold = True和Font.Italic = True代替
- Interior：
- Pattern：设置单元格的填充图案
- Color或者ColorIndex：设置单元格的背景颜色
- NumberFormat：设置单元格的数字格式
- Color或者ColorIndex：设置字体颜色
- Font：
- 示例：
Application.FindFormat.Font.Name = "Arial"' 设置搜索时Arial字体为匹配条件 Application.FindFormat.Font.Bold = True' 设置搜索时加粗字体为匹配条件 Application.FindFormat.Interior.Color = RGB(255, 0, 0) ' 设置搜索时红色背景为匹配条件 Application.FindFormat.NumberFormat = "#,##0.00"' 设置搜索数字为千位分隔、保留两位小数的格式
3.6.3 查找下一个 FindNext 方法
- 功能：通常与 Find 方法一起使用，查找第二个符合条件的匹配项
- 语法：expression.FindNext(After:=After)
- expression：必选参数，通常表示可以查找的单元格范围
- After：可选参数，一个 Range 对象，表示在此单元格之后开始查找
- 示例：在A1:A10区域先后输入空字符、apple和两个apple进行测试
Sub FindNextExample() Dim firstMatch As Range Dim nextMatch As Range Set firstMatch = Range("A1:A10").Find(What:="apple") ' 如果找到了第一个匹配项 If Not firstMatch Is Nothing Then ' 使用FindNext方法查找下一个匹配项，并将其赋值给nextMatch变量 Set nextMatch = Range("A1:A10").FindNext(After:=firstMatch) ' 如果找到了下一个匹配项 If Not nextMatch Is Nothing Then MsgBox "Next match found at " & nextMatch.Address(0, 0) Else MsgBox "No more matches found" End If Else MsgBox "No matches found" End If End Sub
3.6.4 选择特定单元格 Range.SpecialCells 方法
- 功能：相当于Ctrl+G的快捷键，用于选择特定类型单元格
- 语法：expression.SpecialCells(Type, Value)
- 参数：
- expression：表示单元格区域，即要进行特殊单元格选择的范围
- Type：指定要选择的特殊单元格类型，比如xlCellTypeComments为含有注释的单元格
- Value：可选的 XlSpecialCellsValue 常量，用于进一步过滤选择的单元格类型，比如当 Type 参数为 xlCellTypeConstants，Value可以是以下值
名称 值 含义 xlError 16 包含错误（公式错误和常量错误是不一样的，要看前面Type选的哪个） xlLogical 4 包含逻辑值（TRUE 或 FALSE） xlNumbers 1 包含数值 xlTextValues 2 包含文本
- 示例：选择工作表 "Sheet1" 中包含文本常量的所有单元格
Sub SelectTextCells() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") Dim textCells As Range Set textCells = ws.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues) ' 在 Immediate 窗口中输出选择的单元格地址 Debug.Print textCells.Address End Sub
3.6.5 边界定位 End
- End：在指定方向上查找数据的边界
- End 常见参数：
- xlToLeft：向左查找数据的边界
- xlToRight：向右查找数据的边界
- xlUp：向上查找数据的边界
- xlDown：向下查找数据的边界
- 使用注意点：end取边界的时候如果中间有空白格可能会少选，最好是通过空白部分往里取
- 示例1：找到工作表中的最后一行
Sub FindLastRow() Dim lastRow As Long ' 假设数据从A1开始，可以根据实际情况修改范围 lastRow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Row MsgBox "工作簿中最后一行的行数为: " & lastRow End Sub
3.6.6 选中使用区域 UsedRange 方法
- UsedRange：也是一个常用确定工作表中的实际数据范围的方法，可以避免End方法中因为某列只有一个值而导致范围少选的误判，确保获取到整个工作表中的数据范围
- 示例2：
Sub FindLastRowWithUsedRange() Dim lastRow As Long ' 使用 UsedRange 获取工作表的数据范围 lastRow = ActiveSheet.UsedRange.Rows.Count MsgBox "工作簿中最后一行的行数为: " & lastRow End Sub
3.6.6 字符串替换 Replace 函数
- 功能：用于替换字符串中的指定部分
- 语法：Replace(expression, find, replace, [start, [count, [compare]]])
- 参数：
- expression：要进行替换操作的字符串或字符串变量
- find：被替换的子字符串
- replace：是要用来替换 find 的字符串
- start：可选参数，表示从哪个字符位置开始查找。如果省略，则默认从第一个字符开始
- count：可选参数，表示要替换的次数。如果省略，则默认替换所有匹配项
- ompare：可选参数，表示比较模式。可以使用以下常量之一
- vbBinaryCompare：二进制比较模式，区分大小写
- vbTextCompare：文本比较模式，不区分大小写
- 示例：把原始字符串中的"World" 替换为 "Universe"
Sub ReplaceExample() Dim originalString As String Dim replacedString As String originalString = "Hello World" replacedString = Replace(originalString, "World", "Universe") MsgBox replacedString ' 输出：Hello Universe End Sub
3.6.7 单元格内容替换 Range.Replace 方法
- 功能：在指定单元格范围内执行批量替换操作，替换内容除了字符串还可以包括单元格格式
- 语法：expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)
- 参数：
- expression：必选，表示要应用此方法的对象，通常为单元格区域
- What：必选，被替换的子字符串或单元格格式
- Replacement：必选，表示替换 What 参数的新内容
- LookAt：可选参数，指定匹配方式，可以是：
- xlWhole（1）：精确查找，默认为 xlWhole
- xlPart（2）：模糊查找
- SearchOrder：可选参数，指定查找顺序，可以是：
- xlByRows（1）：按行查找，默认为 xlByRows
- xlByColumns（2）：按列查找
- MatchCase：可选参数，指定是否区分大小写，True 表示区分大小写，False 表示不区分大小写。默认为 False
- MatchByte：可选，指定是否区分全/半角字符。True 表示区分，False 表示不区分。默认为 False
- SearchFormat：可选，指定被替换的单元格格式。可以是单元格格式对象或字符串，表示要搜索的格式。默认为 Nothing
- ReplaceFormat：可选，指定要用于替换的单元格格式。可以是单元格格式对象或字符串，表示要应用的格式。默认为 Nothing
- 注意：SearchFormat和ReplaceFormat在替换单元格格式时使用，通常需要分别提前指定格式，可以是某个单元格的格式Range("A1").Format，也可以是表达式
- 示例：把A1:A10区域中单元格填充色为红色的"apple"替换成黄色填充色的"orange"
Sub ReplaceWithFormat() ' 声明一个工作表变量并设置为名为 "Sheet1" 的工作表 Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ' 设置查找单元格填充色为红色（颜色索引为 3） Application.FindFormat.Interior.ColorIndex = 3 ' 设置替换单元格填充色为橙色（颜色索引为 45） Application.replaceFormat.Interior.ColorIndex = 45 ' 在指定范围（A1:A10）查找内容为 "apple"，替换内容为 "orange"，单元格填充色从红色替换为橙色 ws.Range("A1:A10").Replace What:="apple", Replacement:="orange", searchFormat:=True, replaceFormat:=True End Sub
3.7 排序、筛选和填充
3.7.1 单元格排序 Range.Sort 方法
- 功能：按照单个或多个列对单元格内容进行排序（升序或降序）
- 语法：expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)
- 参数：
- expression：必选，表示要应用此方法的对象，通常为单元格区域
- Key1, Key2, Key3：指定要排序的列或者列的首行单元格，至少用一个key，最多三个
- Order1, Order2, Order3：指定排序的顺序，xlAscending（1）升序或 xlDescending（2）降序
- Type：指定排序的类型，例如文本、数字等
- Header：指定第一行是否为表头，xlYes（1）有， xlNo（2）无，xlGuess（0）系统判断，默认为xlGuess；有表头则表头不参与排序
- OrderCustom：指定自定义排序顺序的范围，自定义范围：选中单元格区域——"公式" 选项卡——"定义名称" 组中，点击 "定义名称"——输入名称，然后就可以引用了
- MatchCase：指定是否区分大小写，True 表示区分大小写，False 表示不区分大小写。默认为 False
- Orientation：指定排序的方向，xlSortColumns（1）按列排序或 xlSortRows（2）按行排序，默认为 xlSortRows
- SortMethod：指定排序方法，例如拼音排序
- DataOption1, DataOption2, DataOption3：指定排序选项，例如忽略空值
- 注意：在使用多列排序时，第一列数据的排序顺序会影响到之后列数据的排序结果，因为排序会依次按照指定的排序键对数据进行排序
- 示例：对于Sheet1中的A1:D10区域进行排序，第一列升序，第二列降序，有表头
Sub ExampleSortRange() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet4") ' 设置排序范围为A1:D10 Dim sortRange As Range Set sortRange = ws.Range("A1:D10") ' 执行排序操作，第一列升序，第二列降序，有表头 sortRange.Sort Key1:=sortRange.Columns(1), Order1:=xlAscending, _ Key2:=sortRange.Columns(2), Order2:=xlDescending, Header:=xlYes End Sub
3.7.2 单元格筛选 Range.AutoFilter 方法
- 功能：根据指定的条件筛选出符合条件的数据
- 语法：expression.AutoFilter Field, Criteria1, Operator, Criteria2, VisibleDropDown
- 参数：
- expression：必需。一个表示 Range 对象的表达式
- Field：必需。一个整数，指定要筛选的列的索引号（从左到右，从1开始计数）
- Criteria1：可选。筛选条件1，可以是数值、文本、逻辑表达式等
- Operator：可选。比较运算符，用于指定筛选条件的比较方式，默认为 xlAnd
- Criteria2：可选。筛选条件2，当需要设置复杂的筛选条件时，结合Criterial1和Operator使用，比如筛选大于等于25小于30的数值
- VisibleDropDown：可选。一个布尔值，指定是否显示筛选下拉箭头，默认为 True
- 示例：筛选大于等于25小于30的数，Sheet1数据如右图
Sub ExampleMultipleCriteriaFilterWithOperator() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ' 开启自动筛选 ws.Range("A1:C8").AutoFilter ' 第一列（姓名）不做筛选，从第二列开始设置筛选条件 With ws.Range("A1:C8") .AutoFilter Field:=2, Criteria1:=">=25", Operator:=xlAnd, Criteria2:="<30" ' 筛选大于等于25小于30的数 End With End Sub
3.7.3 单元格填充 Range.AutoFill 方法
- 功能：根据选定的规则自动填充单元格区域的数据序列
- 语法：expression.AutoFill(Destination, Type)
- 参数：
- expression：表示填充的参照单元格
- Destination：表示填充的目标区域，填充区域必须包括参照单元格
- Type：可选参数，指定填充的类型。可以是xlFillCopy复制、xlFillSeries填充数据序列
- 示例：
Sub ExampleAutoFill() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet4") ' 填充数字序列 ws.Range("A1").value = 1 ws.Range("A1").AutoFill Destination:=ws.Range("A1:A5"), Type:=xlFillSeries ' 填充日期序列 ws.Range("B1").value = Date ws.Range("B1").AutoFill Destination:=ws.Range("B1:B5"), Type:=xlFillSeries ' 基于已有数据的序列填充 ws.Range("C1").value = "Monday" ws.Range("C1").AutoFill Destination:=ws.Range("C1:C5"), Type:=xlFillSeries End Sub
3.8 文件和目录操作
3.8.1 遍历 Dir 函数
- 功能：用于遍历文件夹中的文件或文件夹列表
- 语法：Dir([path], [attributes])
- 参数：
- path：可选参数，表示要搜索的目录路径。如果省略该参数，则使用上一次调用 Dir 函数时指定的路径。如果是首次调用，则默认搜索当前目录
- attributes：可选参数，指定要匹配的文件或文件夹的属性。可以是以下常量之一：
常量 值 说明 vbNormal 0 （默认）普通文件 vbReadOnly 1 只读文件 vbHidden 2 隐藏文件 vbSystem 4 系统文件 vbVolume 8 匹配卷标（Volume）类型的文件或文件夹；卷标是指代磁盘驱动器或存储设备的标签，例如 "C:" 或 "D:" vbDirectory 16 文件夹
- 用法：通常和Do循环结合使用，每次调用它都会返回指定目录中的下一个文件或文件夹，如果没有下一个则返回空字符串
- 搜索模式：结合通配符查找特定文件或者文件夹
- *：匹配零个或多个字符
- ?：匹配一个字符
- 举例：
- *.txt：所有txt文件
- ??.docx：查找两个字符的docx文档
- *日语*.*：文件名中包含日语的任意文件
- 示例：搜索的目录路径为 C:\Users\Username\Documents\TestFolder\，循环遍历输出所有文件或文件夹的名称
Sub ExampleDir() Dim filePath As String Dim fileName As String ' 指定目录路径 filePath = "C:\Users\Username\Documents\TestFolder\" ' 获取第一个文件或文件夹名称 fileName = Dir(filePath) ' 遍历目录中的文件或文件夹 Do While fileName <> "" Debug.Print fileName fileName = Dir ' 获取下一个文件或文件夹名称 Loop End Sub
3.8.2 创建文件夹 MkDir 函数
- 功能：创建新文件夹
- 语法：MkDir path
- path：要创建的文件夹的完整路径
- 示例：在 C:\Users\Username\Documents 目录下创建一个名为 NewFolder 的文件夹
MkDir "C:\Users\Username\Documents\NewFolder" -
注意点：
-
如果指定的路径已经存在同名文件夹，则会产生运行时错误
-
指定的路径包含不存在的文件夹，MkDir 会自动创建缺失的文件夹，例如 MkDir "C:\Users\Username\Documents\NewFolder\SubFolder" 会同时创建 NewFolder 和 SubFolder
-
在某些操作系统或环境中，可能需要管理员权限才能创建文件夹，否则会导致权限错误
-
3.8.3 创建超链接 Hyperlinks.Add 方法
- 功能：在 Excel 中的单元格中添加超链接
- 语法：expression.Hyperlinks.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)
- 参数：
- expression：需要添加超链接的文件
- Anchor：添加超链接到单元格的哪个位置
- Address：超链接文件的地址，可以是网页、文件路径或电子邮件地址
- SubAddress：可选，子地址，指定链接目标的具体位置，比如工作簿里某张工作表
- ScreenTip：可选，鼠标悬停在超链接上时显示的提示文本
- TextToDisplay：可选，指定在单元格中显示的超链接文本。如果省略此参数，则默认显示链接地址
- 示例：在单元格 A1 中创建一个超链接，并显示文本为 "Visit Example"
Sub AddHyperlink() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") Dim cell As Range Set cell = ws.Range("A1") ' 添加超链接到网站 cell.Hyperlinks.Add Anchor:=cell, _ Address:="https://mp.csdn.net/mp_blog/creation/editor/138241452?not_checkout=1", _ TextToDisplay:="Visit Example" End Sub
3.8.4 重命名 Name 语句
- 功能：用于重命名文件或文件夹
- 语法：Name oldPath As newPath
- oldPath：是要重命名的文件或文件夹的原路径
- newPath：重命名后的文件或文件夹的新路径
- 示例：将原始文件 OldFile.txt 重命名为 NewFile.txt
Sub RenameFile() Dim oldPath As String Dim newPath As String ' 定义原始路径和新路径 oldPath = "C:\Users\Username\Documents\OldFile.txt" newPath = "C:\Users\Username\Documents\NewFile.txt" ' 使用 Name 语句重命名文件 Name oldPath As newPath MsgBox "文件已成功重命名为 NewFile.txt" End Sub
3.9 调用 Excel 的内置函数
- 语法：result = Application.WorksheetFunction.FunctionName(argument1, argument2, ...)
- 简写：可以省略 Application 或 WorksheetFunction 中的一个来调用 Excel 内置函数，例如，可以将 Application.WorksheetFunction.Sum 缩写为 WorksheetFunction.Sum或者 Application.Sum
- 参数：
- FunctionName：是 Excel 内置函数的名称
- argument1、argument2：是函数的参数
- 示例：使用 SUM 函数求和
Dim sumResult As Double sumResult = Application.WorksheetFunction.Sum(Range("A1:A10")) MsgBox "Sum result: " & sumResult -
其他常用内置函数：以下都省略了Application.WorksheetFunction
函数语法 用法 CountA([a:a]) 统计a列非空单元格个数 Find ("apple", "A1:B10", 1) 在单元格区域中找第一次出现的"apple" CountIf([b:b], ">50") 多条件查找，在b列中找满足条件的数
扫一扫
1710
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
