深入掌握 Excel Worksheet 对象的编程操作
获取工作表的父级对象
在 VBA 中，每个 Worksheet 对象都隶属于一个 Workbook。通过 Parent 属性可以访问其所属的工作簿对象。
Sub ShowParentWorkbook()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
' 输出父对象（即工作簿）名称
Debug.Print "当前工作表所属工作簿: " & ws.Parent.Name
Set ws = Nothing
End Sub
区分工作表的显示名称与代码名称
Excel 工作表有两个关键名称：一个是用户可见的标签名（Name），另一个是在 VBE 编辑器中设置的代码名称（CodeName）。后者在运行时不可更改，适合用于稳定引用。
Dim wsConfig As Worksheet
Sub PrintNames()
On Error Resume Next
Debug.Print "标签名称为: " & wsConfig.Name
Debug.Print "代码名称为: " & wsConfig.CodeName
End Sub
安全检查工作表是否存在
在操作工作表前应先验证其存在性，避免因名称错误导致运行时异常。
Function SheetExists(wb As Workbook, sheetName As String) As Boolean
Dim tempSheet As Object
On Error GoTo NotExist
Set tempSheet = wb.Sheets(sheetName)
SheetExists = True
Exit Function
NotExist:
SheetExists = False
End Function
若需根据代码名称判断工作表是否存在，可遍历所有工作表进行比对：
Function SheetByCodeExists(wb As Workbook, codeName As String) As Boolean
Dim ws As Worksheet
For Each ws In wb.Worksheets
If StrComp(ws.CodeName, codeName, vbTextCompare) = 0 Then
SheetByCodeExists = True
Exit For
End If
Next ws
Set ws = Nothing
End Function
控制工作表的可见状态
可通过设置 Visible 属性实现隐藏或彻底隐藏（仅代码可访问）工作表。
Sub HideSheet(sName As String, isDeepHide As Boolean)
If SheetExists(ThisWorkbook, sName) Then
With ThisWorkbook.Sheets(sName)
.Visible = IIf(isDeepHide, xlSheetVeryHidden, xlSheetHidden)
End With
End If
End Sub
Sub UnhideSheet(sName As String)
If SheetExists(ThisWorkbook, sName) Then
ThisWorkbook.Sheets(sName).Visible = xlSheetVisible
End If
End Sub
批量解除所有工作表的隐藏状态：
Sub RevealAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
Set ws = Nothing
End Sub
保护与解除工作表内容
使用 Protect 方法防止用户修改单元格内容，提升数据安全性。
Function LockSheet(targetSheet As Worksheet, pwd As String) As Boolean
On Error GoTo Fail
If Not targetSheet.ProtectContents Then
targetSheet.Protect Password:=pwd, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
End If
LockSheet = True
Exit Function
Fail:
LockSheet = False
End Function
对应地，使用 Unprotect 方法移除保护：
Function UnlockSheet(targetSheet As Worksheet, pwd As String) As Boolean
On Error GoTo Err
If targetSheet.ProtectContents Then
targetSheet.Unprotect Password:=pwd
End If
UnlockSheet = True
Exit Function
Err:
UnlockSheet = False
End Function
管理工作表的增删操作
动态添加新工作表，并指定插入位置和数量：
' 添加两个新工作表，位于第二张表之前
ThisWorkbook.Worksheets.Add Before:=ThisWorkbook.Worksheets(2), Count:=2
删除工作表时需确保至少保留一张可见工作表，并可临时关闭提示警告：
Function RemoveSheet(sheetToDelete As Worksheet, silentMode As Boolean) As Boolean
Dim alertState As Boolean
Dim success As Boolean
success = False
If CountVisibleInBook(sheetToDelete.Parent) <= 1 Then
RemoveSheet = False
Exit Function
End If
On Error GoTo CleanUp
alertState = Application.DisplayAlerts
If silentMode Then Application.DisplayAlerts = False
success = sheetToDelete.Delete
CleanUp:
Application.DisplayAlerts = True ' 恢复警告
RemoveSheet = success
End Function
Function CountVisibleInBook(book As Workbook) As Integer
Dim i As Integer, count As Integer
For i = 1 To book.Sheets.Count
If book.Sheets(i).Visible = xlSheetVisible Then
count = count + 1
End If
Next i
CountVisibleInBook = count
End Function
移动与复制工作表
支持将工作表移动或复制到指定位置，或生成新的工作簿副本。
Sub ManageSheetLayout()
' 复制第三个工作表至新工作簿
ThisWorkbook.Worksheets(3).Copy
' 将其复制到第二个工作表之前
ThisWorkbook.Worksheets(3).Copy Before:=ThisWorkbook.Worksheets(2)
' 移动第二张表至末尾
ThisWorkbook.Worksheets(2).Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
End Sub
按名称字母顺序对工作表排序：
Sub SortSheetsAlphabetically(workbook As Workbook)
Dim sorted As Boolean
Dim passCount As Integer
Dim total As Integer
total = workbook.Worksheets.Count
Do While Not sorted
sorted = True
passCount = passCount + 1
Dim i As Integer
For i = 1 To total - passCount
If StrComp(workbook.Worksheets(i).Name, workbook.Worksheets(i + 1).Name, vbTextCompare) > 0 Then
workbook.Worksheets(i + 1).Move Before:=workbook.Worksheets(i)
sorted = False
End If
Next i
Loop
End Sub
响应工作表事件
利用内置事件过程监控用户操作。例如，当 B1 或 B2 单元格值改变时，自动调整行列尺寸。
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$B$1"
AdjustColumnWidth Target.Value
Case "$B$2"
AdjustRowHeight Target.Value
End Select
End Sub
Sub AdjustColumnWidth(widthInput As Variant)
If IsNumeric(widthInput) Then
With Me.Columns
If widthInput > 0 And widthInput < 100 Then
.ColumnWidth = widthInput
ElseIf widthInput = 0 Then
.ColumnWidth = Me.StandardWidth
End If
End With
End If
End Sub
Sub AdjustRowHeight(heightInput As Variant)
If IsNumeric(heightInput) Then
With Me.Rows
If heightInput > 0 And heightInput < 100 Then
.RowHeight = heightInput
ElseIf heightInput = 0 Then
.RowHeight = Me.StandardHeight
End If
End With
End If
End Sub
注意：Me 关键字在此上下文中代表触发事件的当前工作表对象。
