【原】Excel【VBA教程】Worksheet對象工作表對象的基本用法

冷茶視界 2023-11-15 發(fā)布于江蘇

展開全文

快速瀏覽

往期合集：【2023年3月】【2023年4月】【2023年5月】【2023年6月】【2023年7月】【2023年8月】【2023年9月】

實用案例

|日期控件 ||簡單的收發(fā)存||收費管理系(Access改進(jìn)版)|

|電子發(fā)票管理助手||電子發(fā)票登記系統(tǒng)(Access版)|

|文件合并 ||表格拆分 ||審計憑證抽查底稿|

|中醫(yī)診所收費系統(tǒng)(Excel版)||中醫(yī)診所收費系統(tǒng)(Access版)|

收費使用項目

|財務(wù)管理系統(tǒng)||工資薪金和年終獎個稅籌劃|

內(nèi)容提要

Worksheet對象的用法

大家好，我是冷水泡茶，前面我們講過WorkBook對象【WorkBook工作簿對象基本用法】，今天我們一起來了解一下Worksheet對象。

一、什么是Worksheet對象？

Worksheet，即“工作表”，就是我們最熟悉的Excel工作表，是WorkBook對象的一個子對象。

二、常用的屬性、方法

1、定義一個Worksheet對象

Dim ws as Worksheet

2、引用工作表

（1）通過工作表的名稱（Name）引用工作表

Set ws = ThisWorkbook.Sheets("明細(xì)表")Set ws = ThisWorkbook.Worksheets("明細(xì)表")

這種方法的好處是比較直觀，我們可以清楚地看到引用的工作表的名稱；

不好（也不應(yīng)該說是不好吧？）的地方就是如果工作表的名稱被改了，那么代碼就得修改，否則肯定會出錯；

當(dāng)然，我們在寫代碼之前，應(yīng)該把一些基礎(chǔ)表格確定下來，定好名稱，不再輕易修改；

worksheets，就是單純的工作表集合，sheets的范圍更廣，不僅包括worksheets，還包括Charts等，我們右擊工作表標(biāo)簽，插入工作表，可以看到這些類型：

（2）通過工作表的代碼名稱（CodeName）引用工作表

Set ws = Sheet1

這里Sheet1就是CodeName，它只能在代碼窗口修改。好處是，即便工作表名稱被修改，代碼仍可以正常運行。當(dāng)然，還是那句話，我們在編寫代碼前，對一些基礎(chǔ)表格設(shè)置好名稱后，就不要輕易再去修改：

（3）使用工作表的索引號

Set ws = Worksheets(2)Set ws = Worksheets.Item(1)Set ws = Sheets.Item(2)

使用索引號，就是指向第幾個工作表。

（4）使用ActiveSheet方法，引用當(dāng)前活動工作表

Set ws = ActiveSheet

在我們插入工作表時，新插入的工作表即為當(dāng)前活動工作表，我們可以對它進(jìn)行操作：

Worksheets.AddSet ws = ActiveSheetws.Name = "new1"

3、新建工作表：Worksheets.Add

'在活動工作表之前插入一張工作表（不帶任何參數(shù)）Set ws = Worksheets.Add'在最前面插入一張工作表Set ws = Worksheets.Add(before:=Worksheets(1))'在最后一張表之前插入一張工作表Set ws = Worksheets.Add(before:=Worksheets(Worksheets.Count))'在第一張工作表之后插入一張工作表Set ws = Worksheets.Add(after:=Worksheets(1))'在最后面插入一張工作表Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))

4、Activate：激活工作表，使其成為活動工作表，一個工作簿只能有一個活動工作表。

ws.Activate

5、Name：獲取或設(shè)置工作表的名稱；

Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))ws.Name = "最后"MsgBox ws.Name

6、Copy：復(fù)制工作表

'復(fù)制到新建工作簿ws.CopyActiveSheet.Name = "New"'復(fù)制到當(dāng)前工作簿，源工作表之前，自動改名ws.Copy before:=wsActiveSheet.Name = "LastSheet"

7、Move：移動工作表

'把最后一個工作表移動到新的工作簿Set ws = Worksheets(Worksheets.Count)ws.Move'把中間的一個工作表移動到最前面Dim i As Integeri = Worksheets.Count / 2Set ws = Worksheets(i)ws.Move before:=Worksheets(1)

8、Visible：隱藏和顯示工作表

Set ws = Sheets("明細(xì)表")ws.Activate'隱藏工作表，在工作表右鍵可取消隱藏，亦可取值0或FALSEws.Visible = xlSheetHidden'深度隱藏工作表，在工作表右鍵不可取消隱藏，亦可取值2ws.Visible = xlSheetVeryHidden'顯示工作表，亦可取值-1或true ws.Visible = xlSheetVisible

9、Count：獲取工作表的數(shù)量，前面代碼中出現(xiàn)過

MsgBox Worksheets.Count  MsgBox Sheets.Count

10、AutoFilterMode：自動篩選狀態(tài)，返回TRUE或者FALSE，可以設(shè)為False以取消自動篩選，但不可設(shè)為TRUE。

MsgBox ws.AutoFilterMode'取消工作表的自動篩選ws.AutoFilterMode = False

11、AutoFilter：設(shè)置自動篩選

ws.Range("A1:b10").AutoFilter Field:=1, Criteria1:="李四"

12、Cells：工作表的所有單元格，這個內(nèi)容非常多，待以后再詳細(xì)了解。我們看一個常用操作，清空工作表所有單元格的內(nèi)容或格式：

'清除內(nèi)容和格式ws.Cells.Clear'清除內(nèi)容ws.Cells.ClearContents'清除格式ws.Cells.ClearFormats

不過，執(zhí)行這個代碼要謹(jǐn)慎，要防止誤操作把別的表的數(shù)據(jù)給清除了。通常我們在寫入一個工作表之前，執(zhí)行清除操作。

13、Delete:：刪除工作表，謹(jǐn)慎操作，視需要屏蔽警告信息

'復(fù)制"明細(xì)表"，清空復(fù)制的表，再把它刪除'好象什么都沒有發(fā)生，設(shè)置中斷可以查看過程Set ws = Sheets("明細(xì)表")ws.Copy before:=wsSet ws = ActiveSheetws.Cells.ClearApplication.DisplayAlerts = Falsews.DeleteApplication.DisplayAlerts = True

14、Index：返回工作表的索引號，工作表的排列位置，包括所有類型的表。

Debug.Print ws.Index

15、Next：返回指定工作表（非最后一張）的下一張工作表

Set ws = Worksheets(Worksheets.Count - 1)Set ws = ws.NextDebug.Print ws.Name

16、PageSetup：頁面設(shè)置，PageSetup是Worksheet的屬性，它也是一個對象

Dim ps As PageSetupSet ps = ws.PageSetupWith ps    '設(shè)置打印區(qū)域    .PrintArea = ws.Range("A1:B10").Address    '設(shè)置左右上下邊距    .LeftMargin = Application.InchesToPoints(0.5)    .RightMargin = Application.InchesToPoints(0.75)    .TopMargin = Application.InchesToPoints(1.5)    .BottomMargin = Application.InchesToPoints(1)    '設(shè)置頁眉頁大小    .HeaderMargin = Application.InchesToPoints(0.5)    .FooterMargin = Application.InchesToPoints(0.5)    '設(shè)置為一頁高一頁寬    .Zoom = False    .FitToPagesTall = 1    .FitToPagesWide = 1    '水平居中    .CenterHorizontally = True    '垂直居中    .CenterVertically = True    '橫向與縱向來回變化    If .Orientation = xlLandscape Then        .Orientation = xlPortrait  '縱向    Else        .Orientation = xlLandscape '橫向    End IfEnd With

PageSetup對象的屬性非常多，想全面了解的可以錄制一個宏來看一下。

17、Paste：粘貼

Set ws = Sheets("明細(xì)表")ws.Range("A1:B10").CopySet ws = Sheets("Sheet1")ws.Paste Destination:=ws.Range("b10")

18、PrintOut：打印工作表

ws.PrintOut copies:=1

19、Protect：保護(hù)工作表

'保護(hù)工作表，密碼123,僅在用戶界面有效，用代碼可以操作工作表 ws.Protect Password:=123, userinterfaceonly:=True

20、Sort：工作表排序

With ws.Sort    .SortFields.Clear    .SortFields.Add Key:=Range("b2:b10"), _        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal    .SetRange Range("A1:B10")    .Header = xlYes    .MatchCase = False    .Orientation = xlTopToBottom    .SortMethod = xlPinYin    .ApplyEnd With

21、Tab：工作表標(biāo)簽屬性，我們可以設(shè)置標(biāo)簽顏色

ws.Tab.Color = vbRed

三、事件

工作表的事件不算多，在Worksheet對象中。

我們常用到的工作表事件有：

1、Activate：工作表激活事件。

Private Sub Worksheet_Activate()    ......End Sub

2、BeforeDoubleClick：鼠標(biāo)雙擊之前事件。正常我們雙擊單元格會進(jìn)入編輯狀態(tài)，有了這個雙擊之前事件，就執(zhí)行這個過程內(nèi)的代碼，比如，雙擊A1單元格，啟動用戶窗體，可以用來選擇輸入：

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    '雙擊A1單元格，啟動用戶窗體    If Target.Address = "$A$1" Then        UserForm1.Show    End IfEnd Sub

3、BeforeRightClick：鼠標(biāo)右擊之前事件。正常我們右擊單元格會啟動右鍵菜單，有了這個右擊之前事件，就執(zhí)行這個過程內(nèi)的代碼，比如，禁用右鍵菜單：

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)    '禁用右鍵菜單    Cancel = TrueEnd Sub

4、Change：工作表發(fā)生改變事件，在單元格發(fā)生改變時發(fā)生。比如，可以用來判斷單元格的值是否符合設(shè)定的條件。

Private Sub Worksheet_Change(ByVal Target As Range)    '輸入數(shù)字不大于10    If Target.Value > 10 Then        Target.Value = 10    End IfEnd Sub

5、SelectionChange：工作表選擇目標(biāo)發(fā)生改變時的事件，比如我們點擊B1單元格，啟動用戶窗體，跟雙擊類似。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    '點擊B1單元格，啟動用戶窗體    If Target.Address = "$B$1" Then        UserForm1.Show    End IfEnd Sub