各位朋友,你們好,今天要和你們分享的是一個關於Excel和VBA的基礎內容。文章最後有自動生成工作表目錄的代碼,代碼中用的內容,就是本文所講述的。
在VBA中,我們怎麼引用Excel的工作表,這些引用方式各有什麼特點?
先看下圖:
從圖片中我們可以看到,對工作表的引用有三種常規方式:
1、CodeName屬性引用。
2、Name屬性引用。
3、Index屬性引用。
接下來我們來看看是如何引用的,並且各種引用方式的優缺點。
一、前面的基礎
這裡有兩個Name,很多人可能要混淆,先給你們看張圖片:
上面部分是工作表對象,下面是工作表對象的一些屬性,在這個屬性中,有兩個名字項,這兩個名字屬性,分別表示什麼意思呢?
(名稱)所對應的Sheet3,是括號外的Sheet3(Sheet3);Name所對應的Sheet3,是括號中的Sheet3(Sheet3)。我們經常乾的修改工作表名稱這個事情,其實修改的就是括號內的部分。我們以動圖來演示效果:
大家一定要記住這兩個名稱,因為對這兩個不同的名稱,用的是不同的引用方式,效果也不一樣。
二、CodeName屬性引用
利用CodeName屬性,對工作表進行引用,代碼書寫方式為(比如引用CodeName表中的A1單元格):
<code>CodeName.Range(“A1”)/<code>
我們還是用動圖來演示(這裡,我用立即窗口,來進行代碼的演示。所謂立即窗口,可以理解為輸入一行代碼,它可以立即執行出結果,常用於檢查代碼):
這種引用方式,是我比較常用的。主要原因有以下兩點:
1、CodeName一般人是不會去修改的,所以一旦寫好了,就可以不用管;
2、用這種引用方式,不受工作表名稱更改的影響(這點很重要,尤其是在你做的文件工作表名稱未確定,或者不確定別人以後是否會修改工作表名稱時。這種引用,即使今後修改了工作表名稱,代碼依然可以正常運行)
但是,這個引用方式也有缺點:
如果做一個比較大的工程,幾十張工作表,用這種方式就很容混淆,在代碼維護和檢查上,沒有用Name屬性引用那麼直觀、那麼好檢查。所以,要根據實際情況來選擇。
三、INDEX屬性引用
這裡的INDEX屬性,就好比我們軍訓的時候排隊列時的報數,都是1、2、3……,但是每天排在第1、2、3……位置上的人可能會不同,這就會造成很多的不確定性。我們還是以動圖的形式來演示:
同樣的代碼,在調整了工作表的順序後,得到的結果卻不一樣,這就是這種引用方式的缺點。
但是這種引用方式有它的優點:可以比較方便的對某幾張表格進行遍歷循環。
如果我們要對工作所有表進行遍歷循環,用一句【For Each Sheet In ThisWorkbook.Sheets】就可以實現。
如果說要對某幾張表格進行遍歷循環呢?CodeName或者Name屬性引用都比較難實現,但是用Index屬性引用就非常方便。看下面的動圖(動圖中的代碼,是按排列順序,依次在立即窗口中顯示相應工作表的名稱):
所以這種引用方式的優點就是:方便遍歷循環部分工作表;
缺點就是:隨著工作表順序的改變或者工作表數量的增加、減少,代碼都會收到影響,代碼維護難度很大。
四、Name屬性引用
這種引用方式,也是比較常見的,因為這裡的Name就是工作表名稱,通過這個名稱,我們可以非常直觀的看出我們折斷代碼的數據來自哪個工作表,不像前面兩種引用方式,有的看不到,有的還會因為人為操作而變化。
所以這種引用方式的優點:代碼比較直觀看到數據來源,檢查代碼,維護代碼都比較方便;
缺點是:工作表名稱改變,會影響代碼的執行。比如將表A改為了表B,原來引用表A的代碼,就無法執行了,需要在代碼中同步修改。
五、給自己做一個工作表目錄
今天學習了在VBA中對工作表的引用方式,我們可以利用所學的內容,用代碼給自己的工作表做一個帶鏈接的目錄(看著別人操作很高大上,當明白原理後,其實就很簡單)。我們來看看吧:
這裡我們需要用到的對象是【Hyperlinks】需要用到的方法是【ADD】
寫法是: Hyperlinks.Add(Anchor,Address,SubAddress,ScreenTip,TextToDisplay)
Anchor:Object 類型,必需。超鏈接的位置。可為 Range 對象或 Shape 對象。
Address: String 類型,必需。超鏈接的地址。
SubAddress:Variant 類型,可選。超鏈接的子地址。
ScreenTip:Variant 類型,可選。當鼠標指針停留在超鏈接上時所顯示的屏幕提示。
TextToDisplay:Variant 類型,可選。要顯示的超鏈接的文本
在操作之前,我們要在工作表最前面插入一張新工作表,然後A1錄入序號,B1錄入工作表名稱。然後從第二個工作表開始遍歷,依次提取工作表名稱到B列,然後設置超鏈接。我們用的代碼如下:
Sub 生成目錄()
Dim a As Integer, b As Integer
For a = 1 To Sheets.Count
If Sheets(a).Name = "目錄" Then
Exit For
End If
Next
If a > Worksheets.Count Then Sheets.Add.Name = "目錄"
Sheets("目錄").Move Before:=Sheets(1)
With Sheets("目錄")
.Range("A1:B" & .Cells(Rows.Count, 1).End(xlUp).Row + 1).Clear
.Range("A1") = "序號"
.Range("B1") = "工作表名稱"
For b = 2 To Sheets.Count
.Range("A" & b).Value = b - 1
.Range("B" & b).Value = Sheets(b).Name
.Range("B" & b).Hyperlinks.Add Anchor:=.Cells(b, 2), Address:="", SubAddress:=Sheets(b).Name & "!A1", TextToDisplay:=Sheets(b).Name
Next
.Range("A1:B" & b - 1).HorizontalAlignment = xlCenter
.Range("A1:B" & b - 1).Borders.LineStyle = xlContinuous
End With
End Sub
好了,今天就到這裡,感謝各位朋友的關注和支持。
如果你喜歡我分享的內容,請點個贊支持下;
如果你覺得我分享的內容對你有幫助,可以關注我;
如果要看我以前分享過的好玩的內容,大家可以去我的主頁查看歷史文章。
閱讀更多 上班下班 的文章