Hi，大家好，我是编程小6，很荣幸遇见你，我把这些年在开发过程中遇到的问题或想法写出来，今天说一说Worksheet对象之应用进阶,希望能够帮助你!!!。

[应用28] Worksheets集合与Sheets集合

对 于不熟悉Excel的人来说，可能会混淆Sheets集合和Worksheets集合之间的不同。Worksheets集合包含典型的Excel工作表 （即通常我们说的工作表），即包含有行、列、单元格和公式等的工作表，而Sheets集合不仅仅是工作表的集合，而且也包含其它类型的工作表，例如图表工 作表、Excel 4.0宏工作表（也称作XLM文件）和Excel 5.0对话框工作表（允许创建自定义对话框）。图表工作表是占用了整个工作表的图表，而不是插入作为工作表一部分的图表。Excel 4工作表和Excel 5工作表用于保持Excel向后兼容，并且也很容易转换为新的工作表类型。进一步说，图表工作表也组成了Charts集合。

Worksheets集合的Count属性返回工作簿中工作表的数量，而Sheets集合的Count属性则返回工作簿中所有工作表的数量，包含图表工作表和工作表。例如，使用下面的语句添加指定数量的工作表到工作簿中：

Do While Worksheets.Count < 5

ThisWorkbook.Sheets.Add

Loop

添加的工作表可以是图表工作表或者是工作表，分别包含于Charts集合或Worksheets集合，或者是Sheets集合的成员。下面的代码修改工作簿中最后一个工作表的名称：

Dim wrkSheetName As String

wrkSheetName = "Sample Chart"

Sheets(Sheets.Count).Name = wrkSheetName

注意，因为使用了Sheets集合，工作簿中最后一个工作表可能是工作表也可能是图表工作表。

因为Sheets集合包含有更多类型的工作表，所以其包含的方法比Worksheets集合更多。然而，两个集合都有添加、删除、复制和移动工作表的方法。

[应用29]组合工作表

在 Excel中，我们可以通过在按下Shift键或Ctrl键的同时，单击其它工作表标签来手工组合工作簿中的工作表。在VBA中，可以通过使用 Worksheets集合的Select方法并结合Array函数来组合工作表。例如，下面的代码组合工作簿中的第1、第3和第5个工作表，并使第3个工 作表成为活动工作表：

Worksheets(Array(1, 3, 5)).Select

Worksheets(3).Activate

也可以使用Worksheet对象的Select方法创建工作表组。首先按正常的方式选择第1个工作表，然后通过使用Select方法并将其参数Replace设置为False，从而将其他工作表添加到组中。

Sub GroupWorksheets()

Dim arrstrNames(1 To 3) As String

Dim i As Integer

arrstrNames(1) = "Sample1"

arrstrNames(2) = "Sample2"

arrstrNames(3) = "Sample3"

Worksheets(arrstrNames(1)).Select

For i = 2 To 3

Worksheets(arrstrNames(i)).Select Replace:=False

Next i

End Sub

然而，在VBA中组合工作表后，对工作表的更改将只影响活动工作表，如果需要更改其他工作表，则需要使用循环语句遍历工作表并作相应的更改。

Sub FormatWorksheetsGroup()

Dim shts As Sheets

Dim wks As Worksheet

Set shts = Worksheets(Array(1, 3, 5))

For Each wks In shts

wks.Range("A1").Value = 100

wks.Range("A1").Font.Bold = True

Next wks

End Sub

[应用30]Activate方法与Select方法的区别

当需要激活或者是选择某 个工作表时，使用Sheets(1).Activate和Sheets(1).Select的作用表面上看起来是相同的。但是，如果将需要激活或者是选择 的工作表隐藏后，使用Sheets(1).Select将会出现错误，而使用Sheets(1).Activate则会正常运行，例如下面的代码：

'- - - 下面的代码运行正常 - - - -

Sub test1()

Sheets(1).Visible = xlHidden

Sheets(1).Activate

End Sub

'---下面的代码运行错误，作用于对象的方法无效---

Sub test2()

Sheets(1).Visible = xlHidden

Sheets(1).Select

End Sub

Activate方法是用来激活对象的方法，而Select方法是用来选取对象的方法，能使用Select方法一次选取多个工作表，但不能使用Activate方法一次激活多个工作表，一次只能激活一个工作表。详见下面的代码示例：

'---下面的代码运行正常 ---

Sub Test3()

ActiveWorkbook.Sheets(Array(1, 2, 3)).Select

End Sub

'---下面的代码运行错误，对象不支持该属性和方法---

Sub Test4()

ActiveWorkbook.Sheets(Array(1, 2, 3)).Activate

End Sub

当然，上述内容同样适用于Worksheets集合。

[应用31]工作表名称的使用

可以在代码中采用下面的三种方式引用工作表：

（1）该工作表在工作簿中的位置(索引号)。索引号自工作表标签最左边向右依次计数，最左边的是第1个工作表，依次为第2个、3个……等等。

（2）该工作表的名称，即在工作表左下角中看到的工作表标签中的名称。

（3）该工作表的对象名称，即在创建工作表时自动分配给该工作表的名称(在VBE编辑器中的工程窗口中可以看到)。

通常，在代码中引用工作表时，我们所使用的是工作表对象的Index属性和Name属性，例如 Worksheets(1).Select或者 Worksheets(“Sheet1″).Select。

但 是，如果工作表的名称被改变或者工作表被重新排序或者删除其中的一些工作表后，则不能使用工作表对象的Name属性或Index属性引用所需要的工作表， 这可能使已经编写好的代码出现错误。因此，我们应该考虑虽然工作簿中的工作表改变但不影响工作表引用的办法，可以使用工作表对象的名称避免这种情况，即上 面所讲的第3种方式，无论是在工作簿中增加或删除其它工作表，还是对工作表排序，或者是重命名需要引用的该工作表，其对象名都不变（除非您删除该工作表， 或者是在VBE窗口中重命名该对象）。工作表对象的名称可以在VBE编程器中看到。例如，Sheet1(Sheet1)，左边是工作表对象的名称，右边的 括号中是工作表名，括号中的工作表名可以通过在工作簿界面中改变相应的工作表标签名来改变，如果在工作表中重命名Sheet1工作表为“数据工作表”，则 工程属性窗口中的名称为：Sheet1(数据工作表)。如果工作表Sheet3的对象名称是“主工作表”，而在Excel中，如果将工作表Sheet3的 名称修改为“数据工作表”，在VBE编程器的工程窗口中，“Sheet3”将变成“数据工作表”，但是该工作表的对象名称仍为“主工作表”。

改变 工作表对象名称的方法是，通过改变属性窗口中的(名称)或者在代码中使用Properties(“_CodeName”)。下面的代码将会添加一个工作表 并将该工作表的对象名称命名为“ws_main”，这样，在后面的代码中就可以使用该对象名称来引用这个工作表，而不必担心工作表名称改变或工作表顺序改 变。

Sub ChageWksObjectName()

Dim ws As Worksheet

Dim sPrevCodeName As String

Dim sNewCodeName As String

'设置新对象的名称

sNewCodeName = "ws_main"

'增加新工作表

Set ws = Worksheets.Add

'获取新增工作表的对象名称

sPrevCodeName = ws.CodeName

'变化新增工作表的对象名称

ThisWorkbook.VBProject.VBComponents(sPrevCodeName). _

Properties("_CodeName") = sNewCodeName

End Sub

Sub Test()

ws_main.Range("A1").Value = "This is it!"

End Sub

注意，虽然使用工作表代码名称有很多优点，例如不受用户更改工作表名称以及工作表顺序的影响、容易处理复制粘贴操作等，但是不可以跨工作簿使用工作表代码名称，即不能在一个工作簿中使用另一个工作簿中的工作表代码名称。

[应用32]引用工作表的方法

下面的示例简单的介绍了工作表的引用方法。在示例中，使用了工作表Sheet1。

（1）指定工作表的位置激活工作表。下面的代码激活工作簿中的第1个工作表，即工作表标签最左边的工作表。（如果增加或删除了其中某工作表，或者是对工作表进行排序后，可能引用的不是您想引用的工作表）

Sub ActivateFirstsheetInBook()

Sheets(1).Activate

End Sub

或者：

Sub ReferenceShtByIndexNumber()

Sheets(1).[A1:D4].Copy Sheets(2).[A1]

End Sub

（2）通过工作表的名称激活工作表，而不管工作表处于工作簿中的什么位置以及工作表对象的代码名称。（如果该工作表被重命名后，运行代码会出错）

Sub ActivateSheet1_1()

Sheets("Sheet1").Activate

End Sub

或者：

Sub ReferenceShtByGivenName()

[Sheet1!A1:D4].Copy [Sheet2!A1]

End Sub

（3）通过工作表对象的名称激活工作表，而不管该工作表处于工作簿中的什么位置以及该工作表的名称

Sub ActivateSheet1_2()

Sheet1.Activate

End Sub

或者：

Sub ReferenceShtByCodeName()

Sheet1.[A1:D4].Copy Sheet2.[A1]

End Sub

[应用33]判断工作簿中是否存在指定名称的工作表

[代码1]下面的函数判断是否存在指定工作表名称的工作表：

Function WorksheetExists(wb As Workbook, strName As String) As Boolean

Dim str As String

On Error GoTo worksheetExistsErr

str = wb.Worksheets(strName).Name

WorksheetExists = True

Exit Function

worksheetExistsErr:

WorksheetExists = False

End Function

如果指定名称的工作表存在，WorksheetExists函数返回True，否则返回False，表示该工作表不存在。

[代码2]下面的函数判断是否存在指定工作表代码名称的工作表：

Function WorksheetCodeNameExists(wb As Workbook, sCodeName As String) As Boolean

Dim str As String

Dim ws As Worksheet

WorksheetCodeNameExists = False

For Each ws In wb.Worksheets

If StrComp(ws.CodeName, sCodeName, vbTextCompare) = 0 Then

WorksheetCodeNameExists = True

Exit For

End If

Next

Set ws = Nothing

End Function

[代码3]下面的函数判断指定名称的工作表是否存在

Function SheetExists(SheetName As String) As Boolean

SheetExists = False

On Error GoTo NoSuchSheet

If Len(Sheets(SheetName).Name) > 0 Then

SheetExists = True

Exit Function

End If

NoSuchSheet:

End Function

[代码4]下面的函数判断指定名称的工作表是否存在

Function DoesWksExist1(sWksName As String) As Boolean

Dim i As Long

For i = Worksheets.Count To 1 Step -1

If Sheets(i).Name = sWksName Then

Exit For

End If

Next

If i = 0 Then

DoesWksExist1 = False

Else

DoesWksExist1 = True

End If

End Function

[代码5]下面的函数判断指定名称的工作表是否存在

Function DoesWksExist2(sWksName As String) As Boolean

Dim wkb As Worksheet

On Error Resume Next

Set wkb = Sheets(sWksName)

On Error GoTo 0

DoesWksExist2 = IIf(Not wkb Is Nothing, True, False)

End Function

[代码6]下面的函数判断指定名称的工作表是否存在

Function SheetExists(sname) As Boolean

'如果活动工作簿中存在该工作表则返回True

Dim x As Object

On Error Resume Next

Set x = ActiveWorkbook.Sheets(sname)

If Err = 0 Then SheetExists = True Else SheetExists = False

End Function

[代码7]下面的函数判断工作表是否存在工作簿中

Function SheetExists(SName As String, Optional wb As Workbook) As Boolean

Dim ws As Worksheet

'默认使用活动工作表

If wb Is Nothing Then

Set wb = ActiveWorkbook

End If

On Error Resume Next

SheetExists = CBool(Not wb.Sheets(SName) Is Nothing)

On Error GoTo 0

End Function

[应用34]工作表行和列的操作

[示例1] 隐藏行

Sub HideRow()

Dim iRow As Long

MsgBox "隐藏当前单元格所在的行"

iRow = ActiveCell.Row

ActiveSheet.Rows(iRow).Hidden = True

MsgBox "取消隐藏"

ActiveSheet.Rows(iRow).Hidden = False

End Sub

[示例2] 隐藏列

Sub HideColumn()

Dim iColumn As Long

MsgBox "隐藏当前单元格所在列"

iColumn = ActiveCell.Column

ActiveSheet.Columns(iColumn).Hidden = True

MsgBox "取消隐藏"

ActiveSheet.Columns(iColumn).Hidden = False

End Sub

[示例3] 插入行

Sub InsertRow()

Dim rRow As Long

MsgBox "在当前单元格上方插入一行"

rRow = Selection.Row

ActiveSheet.Rows(rRow).Insert

End Sub

[示例4] 插入列

Sub InsertColumn()

Dim cColumn As Long

MsgBox "在当前单元格所在行的左边插入一行"

cColumn = Selection.Column

ActiveSheet.Columns(cColumn).Insert

End Sub

[示例5] 插入多行

Sub InsertManyRow()

MsgBox "在当前单元格所在行上方插入三行"

Dim rRow As Long, i As Long

For i = 1 To 3

rRow = Selection.Row

ActiveSheet.Rows(rRow).Insert

Next i

End Sub

[示例6] 设置行高

Sub SetRowHeight()

MsgBox "将当前单元格所在的行高设置为25"

Dim rRow As Long, iRow As Long

rRow = ActiveCell.Row

iRow = ActiveSheet.Rows(rRow).RowHeight

ActiveSheet.Rows(rRow).RowHeight = 25

MsgBox "恢复到原来的行高"

ActiveSheet.Rows(rRow).RowHeight = iRow

End Sub

[示例7] 设置列宽

Sub SetColumnWidth()

MsgBox "将当前单元格所在列的列宽设置为20"

Dim cColumn As Long, iColumn As Long

cColumn = ActiveCell.Column

iColumn = ActiveSheet.Columns(cColumn).ColumnWidth

ActiveSheet.Columns(cColumn).ColumnWidth = 20

MsgBox "恢复至原来的列宽"

ActiveSheet.Columns(cColumn).ColumnWidth = iColumn

End Sub

[示例8] 恢复行高列宽至标准值

Sub ReSetRowHeightAndColumnWidth()

MsgBox "将当前单元格所在的行高和列宽恢复为标准值"

Selection.UseStandardHeight = True

Selection.UseStandardWidth = True

End Sub

[应用35]工作表标签操作

[示例1] 设置工作表标签的颜色

Sub SetSheetTabColor()

MsgBox "设置当前工作表标签的颜色"

ActiveSheet.Tab.ColorIndex = 7

End Sub

[示例2] 恢复工作表标签颜色

Sub SetSheetTabColorDefault()

MsgBox "将当前工作表标签颜色设置为默认值"

ActiveSheet.Tab.ColorIndex = -4142

End Sub

[示例3] 交替隐藏或显示工作表标签

Sub HideOrShowSheetTab()

MsgBox "隐藏/显示工作表标签"

ActiveWindow.DisplayWorkbookTabs = Not ActiveWindow.DisplayWorkbookTabs

End Sub

[应用36]确定打印的页数(HPageBreaks属性与VPageBreaks属性)

Sub PageCount()

Dim i As Long

i = (ActiveSheet.HPageBreaks.Count + 1) * (ActiveSheet.VPageBreaks.Count + 1)

MsgBox "当前工作表共" & i & "页."

End Sub

[应用37]排序工作表

方法1：下面是《Writing Excel Macros with VBA》中的一个示例，摘录于此。

首先验证用户是否想排序工作表，如果是则调用SortAllSheets过程来完成排序。

Sub SortSheets()

If MsgBox("想排序工作表吗?", vbOKCancel + vbQuestion, "排序工作表") = vbOK Then

SortAllSheets

End If

End Sub

下面的代码首先将工作表的名称放置在数组中，然后添加新工作表，将代表工作表名称的数组元素放置在新工作表的第1列，接着对该列排序，将排序好的元素放回数组，并删除添加的工作表，最后使用Move方法重新排列工作表，从而完成工作表排序。

Sub SortAllSheets()

'排序工作表

Dim wb As Workbook

Dim ws As Worksheet

Dim rng As Range, i As Integer

Dim cSheets As Integer

Dim sSheets() As String

Set wb = ActiveWorkbook

'获取数组的实际大小

cSheets = wb.Sheets.Count

ReDim sSheets(1 To cSheets)

'使用工作表名称填充数组

For i = 1 To cSheets

sSheets(i) = wb.Sheets(i).Name

Next

'创建新的工作表并在其第一列放置名称

Set ws = wb.Worksheets.Add

For i = 1 To cSheets

ws.Cells(i, 1).Value = sSheets(i)

Next

'排序列

ws.Columns(1).Sort Key1:=ws.Columns(1), Order1:=xlAscending

'重新填充数组

For i = 1 To cSheets

sSheets(i) = ws.Cells(i, 1).Value

Next

'删除临时工作表

Application.DisplayAlerts = False

ws.Delete

Application.DisplayAlerts = True

'通过移动每个工作表到最后来重新排列工作表

For i = 1 To cSheets

wb.Sheets(sSheets(i)).Move After:=wb.Sheets(cSheets)

Next

End Sub

方法2：下面是《Mastering Excel 2003 Programming with VBA》一书中的示例，使用冒泡法排序，摘录于此。

Sub AlphabetizeWorksheets(wb As Workbook)

Dim bSorted As Boolean

Dim nSheetsSorted As Integer

Dim nSheets As Integer

Dim n As Integer

nSheets = wb.Worksheets.Count

nSheetsSorted = 0

Do While (nSheetsSorted < nSheets) And Not bSorted

bSorted = True

nSheetsSorted = nSheetsSorted + 1

For n = 1 To nSheets - nSheetsSorted

If StrComp(wb.Worksheets(n).Name, wb.Worksheets(n + 1).Name, vbTextCompare) > 0 Then

wb.Worksheets(n + 1).Move Before:=wb.Worksheets(n)

bSorted = False

End If

Next

Loop

End Sub

方法3：

Sub SortWorksheets2()

'根据字母对工作表排序

Dim i As Long, j As Long

For i = 1 To Sheets.Count

For j = 1 To Sheets.Count - 1

If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then

Sheets(j).Move After:=Sheets(j + 1)

End If

Next j

Next i

End Sub

方法4：

Sub SortWorksheets3()

'以升序排列工作表

Dim sCount As Integer, i As Integer, j As Integer

Application.ScreenUpdating = False

sCount = Worksheets.Count

If sCount = 1 Then Exit Sub

For i = 1 To sCount - 1

For j = i + 1 To sCount

If Worksheets(j).Name < Worksheets(i).Name Then

Worksheets(j).Move Before:=Worksheets(i)

End If

Next j

Next i

End Sub

若想排序所有工作表，将代码中的Worksheets替换为Sheets。

[应用38]删除当前工作簿中的空工作表

Sub Delete_EmptySheets()

Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets

If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then

Application.DisplayAlerts = False

sh.Delete

Application.DisplayAlerts = True

End If

Next

End Sub

[应用39]同步工作表

这是John Walkenbach的一个示例，在工作簿的所有工作表中，选择与活动工作表所选单元格区域相同的区域，并使左上角单元格为活动单元格。

Sub SynchSheets()

'选择工作簿其他工作表中与活动工作表所选单元格区域相同的区域

If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

Dim UserSheet As Worksheet, sht As Worksheet

Dim TopRow As Long, LeftCol As Integer

Dim UserSel As String

Application.ScreenUpdating = False

'记住当前工作表

Set UserSheet = ActiveSheet

'保存当前工作表的信息

TopRow = ActiveWindow.ScrollRow

LeftCol = ActiveWindow.ScrollColumn

UserSel = ActiveWindow.RangeSelection.Address

'遍历工作表

For Each sht In ActiveWorkbook.Worksheets

If sht.Visible Then '跳过隐藏的工作表

sht.Activate

Range(UserSel).Select

ActiveWindow.ScrollRow = TopRow

ActiveWindow.ScrollColumn = LeftCol

End If

Next sht

'恢复原始的位置

UserSheet.Activate

Application.ScreenUpdating = True

End Sub

[应用40]经验提示

1、除非万不得已，使用VBA对工作表进行操作时，无须先激活该工作表。最好先声明一个代表工作表的变量，然后使用该变量来引用要操作的工作表。这样，也会使得代码的运行速度加快。

2、为使代码更健壮，应该采取预防性策略，以确定代码所操作的工作表存在，否则会因为重命名工作表或者删除工作表而引起运行时错误。

今天的分享到此就结束了，感谢您的阅读，如果确实帮到您，您可以动动手指转发给其他人。