Worksheet(s)對象應(yīng)用進(jìn)階
[應(yīng)用28] Worksheets集合與Sheets集合
對于不熟悉Excel的人來說，可能會混淆Sheets集合和Worksheets集合之間的不同。Worksheets集合包含典型的Excel工作表（即通常我們說的工作表），即包含有行、列、單元格和公式等的工作表，而Sheets集合不僅僅是工作表的集合，而且也包含其它類型的工作表，例如圖表工作表、Excel 4.0宏工作表（也稱作XLM文件）和Excel 5.0對話框工作表（允許創(chuàng)建自定義對話框）。圖表工作表是占用了整個工作表的圖表，而不是插入作為工作表一部分的圖表。Excel 4工作表和Excel 5工作表用于保持Excel向后兼容，并且也很容易轉(zhuǎn)換為新的工作表類型。進(jìn)一步說，圖表工作表也組成了Charts集合。
Worksheets集合的Count屬性返回工作簿中工作表的數(shù)量，而Sheets集合的Count屬性則返回工作簿中所有工作表的數(shù)量，包含圖表工作表和工作表。例如，使用下面的語句添加指定數(shù)量的工作表到工作簿中：
Do While Worksheets.Count < 5 ThisWorkbook.Sheets.AddLoop |
添加的工作表可以是圖表工作表或者是工作表，分別包含于Charts集合或Worksheets集合，或者是Sheets集合的成員。下面的代碼修改工作簿中最后一個工作表的名稱：
Dim wrkSheetName As StringwrkSheetName = "Sample Chart"Sheets(Sheets.Count).Name = wrkSheetName |
注意，因?yàn)槭褂昧薙heets集合，工作簿中最后一個工作表可能是工作表也可能是圖表工作表。
因?yàn)镾heets集合包含有更多類型的工作表，所以其包含的方法比Worksheets集合更多。然而，兩個集合都有添加、刪除、復(fù)制和移動工作表的方法。
[應(yīng)用29]組合工作表
在Excel中，我們可以通過在按下Shift鍵或Ctrl鍵的同時，單擊其它工作表標(biāo)簽來手工組合工作簿中的工作表。在VBA中，可以通過使用Worksheets集合的Select方法并結(jié)合Array函數(shù)來組合工作表。例如，下面的代碼組合工作簿中的第1、第3和第5個工作表，并使第3個工作表成為活動工作表：
Worksheets(Array(1, 3, 5)).SelectWorksheets(3).Activate |
也可以使用Worksheet對象的Select方法創(chuàng)建工作表組。首先按正常的方式選擇第1個工作表，然后通過使用Select方法并將其參數(shù)Replace設(shè)置為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 iEnd Sub |
然而，在VBA中組合工作表后，對工作表的更改將只影響活動工作表，如果需要更改其他工作表，則需要使用循環(huán)語句遍歷工作表并作相應(yīng)的更改。
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 wksEnd Sub |
[應(yīng)用30]Activate方法與Select方法的區(qū)別
當(dāng)需要激活或者是選擇某個工作表時，使用Sheets(1).Activate和Sheets(1).Select的作用表面上看起來是相同的。但是，如果將需要激活或者是選擇的工作表隱藏后，使用Sheets(1).Select將會出現(xiàn)錯誤，而使用Sheets(1).Activate則會正常運(yùn)行，例如下面的代碼：
'- - - 下面的代碼運(yùn)行正常 - - - -Sub test1() Sheets(1).Visible = xlHidden Sheets(1).ActivateEnd Sub'- - - 下面的代碼運(yùn)行錯誤，作用于對象的方法無效 - - - -Sub test2() Sheets(1).Visible = xlHidden Sheets(1).SelectEnd Sub |
Activate方法是用來激活對象的方法，而Select方法是用來選取對象的方法，能使用Select方法一次選取多個工作表，但不能使用Activate方法一次激活多個工作表，一次只能激活一個工作表。詳見下面的代碼示例：
'- - - 下面的代碼運(yùn)行正常 - - - -Sub Test3() ActiveWorkbook.Sheets(Array(1, 2, 3)).SelectEnd Sub'- - - 下面的代碼運(yùn)行錯誤，對象不支持該屬性和方法 - - - -Sub Test4() ActiveWorkbook.Sheets(Array(1, 2, 3)).ActivateEnd Sub |
當(dāng)然，上述內(nèi)容同樣適用于Worksheets集合。
[應(yīng)用31]工作表名稱的使用
可以在代碼中采用下面的三種方式引用工作表：
（1）該工作表在工作簿中的位置(索引號)。索引號自工作表標(biāo)簽最左邊向右依次計數(shù)，最左邊的是第1個工作表，依次為第2個、3個……等等。
（2）該工作表的名稱，即在工作表左下角中看到的工作表標(biāo)簽中的名稱。
（3）該工作表的對象名稱，即在創(chuàng)建工作表時自動分配給該工作表的名稱(在VBE編輯器中的工程窗口中可以看到)。
通常，在代碼中引用工作表時，我們所使用的是工作表對象的Index屬性和Name屬性，例如 Worksheets(1).Select或者
Worksheets(“Sheet1″).Select。
但是，如果工作表的名稱被改變或者工作表被重新排序或者刪除其中的一些工作表后，則不能使用工作表對象的Name屬性或Index屬性引用所需要的工作表，這可能使已經(jīng)編寫好的代碼出現(xiàn)錯誤。因此，我們應(yīng)該考慮雖然工作簿中的工作表改變但不影響工作表引用的辦法，可以使用工作表對象的名稱避免這種情況，即上面所講的第3種方式，無論是在工作簿中增加或刪除其它工作表，還是對工作表排序，或者是重命名需要引用的該工作表，其對象名都不變（除非您刪除該工作表，或者是在VBE窗口中重命名該對象）。工作表對象的名稱可以在VBE編程器中看到。例如，Sheet1(Sheet1)，左邊是工作表對象的名稱，右邊的括號中是工作表名，括號中的工作表名可以通過在工作簿界面中改變相應(yīng)的工作表標(biāo)簽名來改變，如果在工作表中重命名Sheet1工作表為“數(shù)據(jù)工作表”，則工程屬性窗口中的名稱為：Sheet1(數(shù)據(jù)工作表)。如果工作表Sheet3的對象名稱是“主工作表”，而在Excel中，如果將工作表Sheet3的名稱修改為“數(shù)據(jù)工作表”，在VBE編程器的工程窗口中，“Sheet3”將變成“數(shù)據(jù)工作表”，但是該工作表的對象名稱仍為“主工作表”。
改變工作表對象名稱的方法是，通過改變屬性窗口中的(名稱)或者在代碼中使用Properties(“_CodeName”)。下面的代碼將會添加一個工作表并將該工作表的對象名稱命名為“ws_main”，這樣，在后面的代碼中就可以使用該對象名稱來引用這個工作表，而不必?fù)?dān)心工作表名稱改變或工作表順序改變。
Sub ChageWksObjectName() Dim ws As Worksheet Dim sPrevCodeName As String Dim sNewCodeName As String '設(shè)置新對象的名稱 sNewCodeName = "ws_main" '增加新工作表 Set ws = Worksheets.Add '獲取新增工作表的對象名稱 sPrevCodeName = ws.CodeName '變化新增工作表的對象名稱 ThisWorkbook.VBProject.VBComponents(sPrevCodeName). _ Properties("_CodeName") = sNewCodeNameEnd SubSub Test() ws_main.Range("A1").Value = "This is it!"End Sub |
注意，雖然使用工作表代碼名稱有很多優(yōu)點(diǎn)，例如不受用戶更改工作表名稱以及工作表順序的影響、容易處理復(fù)制粘貼操作等，但是不可以跨工作簿使用工作表代碼名稱，即不能在一個工作簿中使用另一個工作簿中的工作表代碼名稱。
[應(yīng)用32]引用工作表的方法
下面的示例簡單的介紹了工作表的引用方法。在示例中，使用了工作表Sheet1。
（1）指定工作表的位置激活工作表。下面的代碼激活工作簿中的第1個工作表，即工作表標(biāo)簽最左邊的工作表。（如果增加或刪除了其中某工作表，或者是對工作表進(jìn)行排序后，可能引用的不是您想引用的工作表）
Sub ActivateFirstsheetInBook() Sheets(1).ActivateEnd Sub |
或者：
Sub ReferenceShtByIndexNumber() Sheets(1).[A1:D4].Copy Sheets(2).[A1]End Sub |
（2）通過工作表的名稱激活工作表，而不管工作表處于工作簿中的什么位置以及工作表對象的代碼名稱。（如果該工作表被重命名后，運(yùn)行代碼會出錯）
Sub ActivateSheet1_1() Sheets("Sheet1").ActivateEnd Sub |
或者：
Sub ReferenceShtByGivenName() [Sheet1!A1:D4].Copy [Sheet2!A1]End Sub |
（3）通過工作表對象的名稱激活工作表，而不管該工作表處于工作簿中的什么位置以及該工作表的名稱
Sub ActivateSheet1_2() Sheet1.ActivateEnd Sub |
或者：
Sub ReferenceShtByCodeName() Sheet1.[A1:D4].Copy Sheet2.[A1]End Sub |
[應(yīng)用33]判斷工作簿中是否存在指定名稱的工作表
[代碼1]下面的函數(shù)判斷是否存在指定工作表名稱的工作表：
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 FunctionworksheetExistsErr: WorksheetExists = FalseEnd Function |
如果指定名稱的工作表存在，WorksheetExists函數(shù)返回True，否則返回False，表示該工作表不存在。
[代碼2]下面的函數(shù)判斷是否存在指定工作表代碼名稱的工作表：
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 = NothingEnd Function |
[代碼3]下面的函數(shù)判斷指定名稱的工作表是否存在
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 IfNoSuchSheet:End Function |
[代碼4]下面的函數(shù)判斷指定名稱的工作表是否存在
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 IfEnd Function |
[代碼5]下面的函數(shù)判斷指定名稱的工作表是否存在
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]下面的函數(shù)判斷指定名稱的工作表是否存在
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 = FalseEnd Function |
[代碼7]下面的函數(shù)判斷工作表是否存在工作簿中
Function SheetExists(SName As String, Optional wb As Workbook) As Boolean Dim ws As Worksheet '默認(rèn)使用活動工作表 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 0End Function |
[應(yīng)用34]工作表行和列的操作
[示例1] 隱藏行
Sub HideRow() Dim iRow As Long MsgBox "隱藏當(dāng)前單元格所在的行" iRow = ActiveCell.Row ActiveSheet.Rows(iRow).Hidden = True MsgBox "取消隱藏" ActiveSheet.Rows(iRow).Hidden = FalseEnd Sub |
[示例2] 隱藏列
Sub HideColumn() Dim iColumn As Long MsgBox "隱藏當(dāng)前單元格所在列" iColumn = ActiveCell.Column ActiveSheet.Columns(iColumn).Hidden = True MsgBox "取消隱藏" ActiveSheet.Columns(iColumn).Hidden = FalseEnd Sub |
[示例3] 插入行
Sub InsertRow() Dim rRow As Long MsgBox "在當(dāng)前單元格上方插入一行" rRow = Selection.Row ActiveSheet.Rows(rRow).InsertEnd Sub |
[示例4] 插入列
Sub InsertColumn() Dim cColumn As Long MsgBox "在當(dāng)前單元格所在行的左邊插入一行" cColumn = Selection.Column ActiveSheet.Columns(cColumn).InsertEnd Sub |
[示例5] 插入多行
Sub InsertManyRow() MsgBox "在當(dāng)前單元格所在行上方插入三行" Dim rRow As Long, i As Long For i = 1 To 3 rRow = Selection.Row ActiveSheet.Rows(rRow).Insert Next iEnd Sub |
[示例6] 設(shè)置行高
Sub SetRowHeight() MsgBox "將當(dāng)前單元格所在的行高設(shè)置為25" Dim rRow As Long, iRow As Long rRow = ActiveCell.Row iRow = ActiveSheet.Rows(rRow).RowHeight ActiveSheet.Rows(rRow).RowHeight = 25 MsgBox "恢復(fù)到原來的行高" ActiveSheet.Rows(rRow).RowHeight = iRowEnd Sub |
[示例7] 設(shè)置列寬
Sub SetColumnWidth() MsgBox "將當(dāng)前單元格所在列的列寬設(shè)置為20" Dim cColumn As Long, iColumn As Long cColumn = ActiveCell.Column iColumn = ActiveSheet.Columns(cColumn).ColumnWidth ActiveSheet.Columns(cColumn).ColumnWidth = 20 MsgBox "恢復(fù)至原來的列寬" ActiveSheet.Columns(cColumn).ColumnWidth = iColumnEnd Sub |
[示例8] 恢復(fù)行高列寬至標(biāo)準(zhǔn)值
Sub ReSetRowHeightAndColumnWidth() MsgBox "將當(dāng)前單元格所在的行高和列寬恢復(fù)為標(biāo)準(zhǔn)值" Selection.UseStandardHeight = True Selection.UseStandardWidth = TrueEnd Sub |
[應(yīng)用35]工作表標(biāo)簽操作
[示例1] 設(shè)置工作表標(biāo)簽的顏色
Sub SetSheetTabColor() MsgBox "設(shè)置當(dāng)前工作表標(biāo)簽的顏色" ActiveSheet.Tab.ColorIndex = 7End Sub |
[示例2] 恢復(fù)工作表標(biāo)簽顏色
Sub SetSheetTabColorDefault() MsgBox "將當(dāng)前工作表標(biāo)簽顏色設(shè)置為默認(rèn)值" ActiveSheet.Tab.ColorIndex = -4142End Sub |
[示例3] 交替隱藏或顯示工作表標(biāo)簽
Sub HideOrShowSheetTab() MsgBox "隱藏/顯示工作表標(biāo)簽" ActiveWindow.DisplayWorkbookTabs = Not ActiveWindow.DisplayWorkbookTabsEnd Sub |
[應(yīng)用36]確定打印的頁數(shù)(HPageBreaks屬性與VPageBreaks屬性)
Sub PageCount() Dim i As Long i = (ActiveSheet.HPageBreaks.Count + 1) * (ActiveSheet.VPageBreaks.Count + 1) MsgBox "當(dāng)前工作表共" & i & "頁."End Sub |
[應(yīng)用37]排序工作表
方法1：下面是《Writing Excel Macros with VBA》中的一個示例，摘錄于此。
首先驗(yàn)證用戶是否想排序工作表，如果是則調(diào)用SortAllSheets過程來完成排序。
Sub SortSheets() If MsgBox("想排序工作表嗎?", vbOKCancel + vbQuestion, "排序工作表") = vbOK Then SortAllSheets End IfEnd Sub |
下面的代碼首先將工作表的名稱放置在數(shù)組中，然后添加新工作表，將代表工作表名稱的數(shù)組元素放置在新工作表的第1列，接著對該列排序，將排序好的元素放回數(shù)組，并刪除添加的工作表，最后使用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 '獲取數(shù)組的實(shí)際大小 cSheets = wb.Sheets.Count ReDim sSheets(1 To cSheets) '使用工作表名稱填充數(shù)組 For i = 1 To cSheets sSheets(i) = wb.Sheets(i).Name Next '創(chuàng)建新的工作表并在其第一列放置名稱 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 '重新填充數(shù)組 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) NextEnd 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 LoopEnd Sub |
方法3：
Sub SortWorksheets2() '根據(jù)字母對工作表排序 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 iEnd 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 iEnd Sub |
若想排序所有工作表，將代碼中的Worksheets替換為Sheets。
[應(yīng)用38]刪除當(dāng)前工作簿中的空工作表
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 NextEnd Sub |
[應(yīng)用39]同步工作表
這是John Walkenbach的一個示例，在工作簿的所有工作表中，選擇與活動工作表所選單元格區(qū)域相同的區(qū)域，并使左上角單元格為活動單元格。
Sub SynchSheets() '選擇工作簿其他工作表中與活動工作表所選單元格區(qū)域相同的區(qū)域 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 '記住當(dāng)前工作表 Set UserSheet = ActiveSheet '保存當(dāng)前工作表的信息 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 '恢復(fù)原始的位置 UserSheet.Activate Application.ScreenUpdating = TrueEnd Sub |
[應(yīng)用40]經(jīng)驗(yàn)提示
1、除非萬不得已，使用VBA對工作表進(jìn)行操作時，無須先激活該工作表。最好先聲明一個代表工作表的變量，然后使用該變量來引用要操作的工作表。這樣，也會使得代碼的運(yùn)行速度加快。
2、為使代碼更健壯，應(yīng)該采取預(yù)防性策略，以確定代碼所操作的工作表存在，否則會因?yàn)橹孛ぷ鞅砘蛘邉h除工作表而引起運(yùn)行時錯誤。
聲明：本文由完美Excel網(wǎng)站整理，完美Excel保留本文的所有權(quán)利，未經(jīng)許可，任何組織或個人不得以任何方式將本文用于商業(yè)作途。其他網(wǎng)站或博客引用本文，請注明原文鏈接和版權(quán)聲明。
