1.引用工作表的方式
VBA中，在不同的工作表之间转换或者对不同工作表中的单元格区域进行操作时，需要指定引用的工作表，通常有下面几种方法
使用工作表的名称
工作表名称是指显示在工作表标签中的文本，工作表名称可以使用Worksheets集合和Sheets集合两种引用方式。
两种方法的主要区别在于：Worksheets集合只包含工作表对象，Sheets集合包含所有表格对象，包括工作表和图表表。
通过Worksheets集合：
Worksheets("Sheet1").Range("A1").Value = "Hello"
通过Sheets集合：
Sheets("Sheet1").Range("A1").Value = "Hello"任何时刻工作簿中只有一个工作表是活动工作表。
使用工作表的索引号
工作表索引号是指工作表在工作簿中的位置，Excel根据工作表在工作表标签中的位置，从1开始从左向右进行编号，
Worksheets(1).Range("A1").Value = "Hello"
Sheets(1).Range("A1").Value = "Hello"通过索引号，可以使用变量来动态引用不同的工作表
示例：遍历所有工作表：
Sub LoopThroughSheets()
Dim i As Integer
For i = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Worksheets(i).Range("A1").Value = "Sheet " & i
Next i
End Sub需要注意的是：当工作簿包括工作表、宏表、图表等时，使用Worksheets(1)集合与Sheets(1)集合，引用的可能不是同一个表。
Worksheet对象的index属性可以返回工作表的索引号
MsgBox Worksheets(“工作表名称”).Index
使用工作表的代码
使用Worksheet对象的CodeName属性可以返回工作表的代码名称：
MsgBox Worksheets(“Sheet1”).CodeName，就是该工作表对象在编辑器中，属性里的名称字段
使用ActiveSheet属性引用活动工作表
使用ActiveSheet属性可以返回活动工作表：
MsgBox ActiveSheet.Name
2.选择工作表的方法
VBA中需要激活或者选择某个工作表时，使用Select方法或Active方法，（不能激活 或 选择隐藏的工作表）
从表面看，两者的作用是相同的，
Worksheets(“工作表名称”).Select
Worksheets(“工作表名称”).Active
如果需要同时选中工作簿中能够所有的工作表，则只能使用Select方法，不能使用Active。
可以通过数组的方式同时选中多张工作表：
Worksheets(Array(1, 2, 3)).Select
3.遍历工作表的方法
使用For…Next语句
Dim i As Integer
For i = 1 To ThisWorkbook.Worksheets.Count
Debug.Print ThisWorkbook.Worksheets(i).Name
Next i使用For Each…Next语句
For Each…Next的基本语法
For Each 元素 In 集合
'对每个元素执行的代码
Next 元素使用For Each…Next语句来遍历所有工作表
Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
'对每个工作表执行的操作
Debug.Print "正在处理工作表：" & ws.Name
Next ws
MsgBox "所有工作表处理完毕"
End Sub在工作簿中翻页
Option Explicit
' 这个子程序用于切换到下一个工作表
Sub DownSheet()
Dim i As Integer ' 声明一个整型变量i用于存储工作表总数
i = Worksheets.Count ' 获取工作簿中的工作表总数
' 判断当前活动工作表的索引是否小于工作表总数
If ActiveSheet.Index < i Then
' 如果是,则激活下一个工作表
Worksheets(ActiveSheet.Index + 1).Activate
'MsgBox ActiveSheet.Name
Else
' 如果当前已是最后一个工作表,则循环到第一个工作表
Worksheets(1).Activate
End If
End Sub
' 这个子程序用于切换到上一个工作表
Sub UpSheet()
Dim i As Integer ' 声明一个整型变量i用于存储工作表总数
i = Worksheets.Count ' 获取工作簿中的工作表总数
' 判断当前活动工作表的索引是否大于1
If ActiveSheet.Index > 1 Then
' 如果是,则激活上一个工作表
Worksheets(ActiveSheet.Index - 1).Activate
Else
' 如果当前已是第一个工作表,则循环到最后一个工作表
Worksheets(i).Activate
End If
End Sub
4.工作表的添加与删除
在工作簿中添加工作表使用Add方法
Sub 添加工作表()
' 在当前工作簿的末尾添加一个新工作表
Worksheets.Add
' 输出提示信息
MsgBox "新工作表已添加!", vbInformation
End SubSub 添加工作表高级()
' 声明工作表变量
Dim newSheet As Worksheet
' 在第一个工作表之前添加新工作表
Set newSheet = Worksheets.Add(Before:=Worksheets(1))
' 设置新工作表的名称
newSheet.Name = "新工作表_" & Format(Now, "yyyymmdd")
' 设置工作表的标签颜色
newSheet.Tab.Color = RGB(255, 0, 0) ' 红色
' 在A1单元格写入文本
newSheet.Range("A1").Value = "这是新添加的工作表"
' 输出提示信息
MsgBox "新工作表 '" & newSheet.Name & "' 已添加并设置!", vbInformation
End Sub删除工作表需要通过Delete方法
Sub 删除工作表()
Dim ws As Worksheet
Dim wsName As String
' 获取要删除的工作表名称
wsName = InputBox("请输入要删除的工作表名称:", "删除工作表")
' 检查工作表是否存在
On Error Resume Next
Set ws = Worksheets(wsName)
On Error GoTo 0
If Not ws Is Nothing Then
' 询问用户是否确定删除
If MsgBox("确定要删除工作表 '" & wsName & "' 吗?", vbYesNo + vbQuestion) = vbYes Then
Application.DisplayAlerts = False ' 禁用警告消息
ws.Delete
Application.DisplayAlerts = True ' 重新启用警告消息
MsgBox "工作表 '" & wsName & "' 已删除", vbInformation
End If
Else
MsgBox "找不到名为 '" & wsName & "' 的工作表", vbExclamation
End If
End Sub5.自动建立工作表目录
如果在工作簿中有许多工作表，使用时往往会建立一张目录并插入超链接 以方便选择使用工作表
Sub 创建工作表目录()
Dim ws As Worksheet
Dim titleRow As Integer
Dim currentRow As Integer
Dim directorySheet As Worksheet
' 检查是否已存在名为"目录"的工作表
On Error Resume Next
Set directorySheet = ThisWorkbook.Worksheets("目录")
On Error GoTo 0
' 如果"目录"工作表不存在，则创建它
If directorySheet Is Nothing Then
Set directorySheet = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
directorySheet.Name = "目录"
Else
' 如果已存在，清除其内容
directorySheet.Cells.Clear
End If
' 设置标题
With directorySheet.Cells(1, 1)
.Value = "工作表目录"
.Font.Bold = True
.Font.Size = 14
End With
' 设置列标题
titleRow = 3
With directorySheet
.Cells(titleRow, 1).Value = "序号"
.Cells(titleRow, 2).Value = "工作表名称"
.Cells(titleRow, 3).Value = "描述"
.Range(.Cells(titleRow, 1), .Cells(titleRow, 3)).Font.Bold = True
End With
' 从第4行开始添加工作表信息
currentRow = titleRow + 1
' 遍历所有工作表
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> directorySheet.Name Then ' 跳过目录工作表自身
With directorySheet
' 添加序号
.Cells(currentRow, 1).Value = currentRow - titleRow
' 添加工作表名称并创建超链接
.Hyperlinks.Add Anchor:=.Cells(currentRow, 2), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
' 为描述留出空白单元格
.Cells(currentRow, 3).Value = ""
End With
currentRow = currentRow + 1
End If
Next ws
' 调整列宽
directorySheet.Columns("A:C").AutoFit
' 为整个表格添加边框
With directorySheet.Range(directorySheet.Cells(titleRow, 1), directorySheet.Cells(currentRow - 1, 3))
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
End With
' 激活目录工作表
directorySheet.Activate
MsgBox "工作表目录已创建完成！", vbInformation
End Sub6.删除工作表中的空行
Sub DelBlankRow()
Dim rRow As Long
Dim lRow As Long
Dim i As Long
' 获取工作表中已使用范围的最后一行
rRow = Sheet1.UsedRange.Row
' 计算工作表中已使用范围的总行数
lRow = rRow + Sheet1.UsedRange.Rows.Count - 1
' 从最后一行开始，向上遍历每一行
For i = lRow To rRow Step -1
' 检查当前行是否为空行（即该行所有单元格都为空）
If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then
' 如果是空行，则删除该行
Rows(i).Delete
End If
Next
End Sub7.删除工作表中的重复行
Sub DeleteRow()
' 声明整型变量R和i
Dim R As Integer
Dim i As Integer
' 使用With语句来简化对Sheet1的引用
With Sheet1
' 获取工作表中最后一个非空单元格所在的行号
R = .[a65536].End(xlUp).Row
' 从最后一行开始向上遍历每一行
For i = R To 1 Step -1
' 检查当前行的第1列(A列)是否有超过1个非空单元格
If WorksheetFunction.CountIf(.Columns(1), .Cells(i, 1)) > 1 Then
' 如果有重复值,则删除该行
.Rows(i).Delete
End If
Next
End With
End SubSub DeleteRow(): 定义了一个名为DeleteRow的子程序,用于删除特定条件的行。
Dim R As Integer: 声明一个整型变量R,用于存储最后一个非空行的行号。
Dim i As Integer: 声明一个整型变量i,用作循环计数器。
With Sheet1: 开始一个With语句块,简化对Sheet1的引用。
R = .[a65536].End(xlUp).Row: 这行代码找到A列最后一个非空单元格的行号。
[a65536]是A列的最后一个单元格(在旧版Excel中)
End(xlUp)从底部向上移动到第一个非空单元格
.Row获取该单元格的行号
For i = R To 1 Step -1: 开始一个从底部向上的循环,步长为-1(即逆向遍历)。
If WorksheetFunction.CountIf(.Columns(1), .Cells(i, 1)) > 1 Then:
检查A列中是否有与当前单元格值相同的其他单元格。如果CountIf的结果大于1,说明存在重复值。
.Rows(i).Delete: 如果条件满足(即存在重复值),则删除该行。
End If: 结束If语句。
Next: 移动到下一次循环。
End With: 结束With语句块。
End Sub: 结束子程序。
这段代码的主要功能是从下往上遍历工作表的所有行,检查A列中是否存在重复值。如果在A列中发现某个单元格的值在该列中出现多次,则删除该行。这种从下往上的遍历方式可以确保在删除行时不会影响到还未检查的行的索引。
这个程序可能用于清理数据,删除基于某个关键列(这里是A列)的重复记录。如果你需要保留重复记录中的第一个出现,而不是最后一个,你可能需要调整循环的方向(从上到下)。
如果出现重复，会优先删除下面的行（保留第一行）
8.奇偶页打印
Sub PrintOddPage()
' 声明一个整型变量来存储总页数
Dim TotalPg As Integer
' 使用ExecuteExcel4Macro函数获取文档的总页数
TotalPg = ExecuteExcel4Macro("GET.DOCUMENT(50)")
' 使用For循环遍历奇数页
For i = 1 To TotalPg Step 2
' 打印当前活动工作表的指定页
ActiveSheet.PrintOut From:=i, To:=i
Next
End SubSub PrintOddPage(): 定义了一个名为PrintOddPage的子程序。
Dim TotalPg As Integer: 声明一个整型变量TotalPg，用于存储文档的总页数。
TotalPg = ExecuteExcel4Macro(“GET.DOCUMENT(50)”):
这行代码使用了一个旧的Excel 4.0宏函数GET.DOCUMENT(50)来获取文档的总页数。
ExecuteExcel4Macro允许在VBA中执行这些旧的宏函数。
50是GET.DOCUMENT函数的参数，用于指定获取总页数。
For i = 1 To TotalPg Step 2:
这是一个For循环，从1开始，到总页数结束。
Step 2表示每次增加2，也就是只处理奇数页（1, 3, 5, …）。
ActiveSheet.PrintOut From:=i, To:=i:
这行代码打印当前活动工作表的指定页。
From:=i 和 To:=i 都设置为当前循环的i值，意味着每次只打印一页。
Next: 结束For循环，移动到下一个奇数页。
End Sub: 结束子程序。
总结：这个宏的功能是打印当前活动工作表的所有奇数页。它首先获取文档的总页数，然后使用循环遍历所有奇数页码，每次打印一页。这种方法适用于需要只打印奇数页的场景，例如双面打印时只需打印正面。
需要注意的是，ExecuteExcel4Macro函数使用了旧的Excel 4.0宏语法，在某些新版本的Excel中可能不被支持或不推荐使用。在实际应用中，可能需要考虑使用更现代的方法来获取总页数。
1 thought on “2.Worksheet对象——《VBA常用技巧代码解析》”