【原】神奇的Excel VBA系列之：制作工作表目錄

神奇的ExcelVBA 2020-02-06

展開(kāi)全文

在前一篇文章“Excel插件--Power Click功能介紹08：表格目錄”中，我們介紹了《Power Click》插件的一鍵生成工作表目錄的功能。

其實(shí)如果您會(huì)使用Excel VBA表格編程。您也可以給自己定制一個(gè)更適合自己的工作表目錄。本篇文章將分享使用VBA代碼制作工作表目錄。

示例功能要求：在當(dāng)前工作簿中新建工作表，名稱(chēng)命名為“My 目錄”。然后將工作簿中的所有其它工作表名稱(chēng)放入“My 目錄”工作表單元格區(qū)域中，并建立超鏈接，這樣我們可以點(diǎn)擊鏈接快速跳轉(zhuǎn)至其它工作表中。

根據(jù)要求，我們將該功能分解為3個(gè)部。

第1步：創(chuàng)建變量

Sub 制作工作表目錄()'創(chuàng)建變量Dim ws As WorksheetDim sh As WorksheetDim n As Integer... ... ...End Sub

第2步：新建“My目錄”工作表，并設(shè)置表頭。

Sub 制作工作表目錄()'創(chuàng)建變量Dim ws As WorksheetDim sh As WorksheetDim n As Integer

'新建“My目錄”工作表，并設(shè)置表頭On Error Resume Next '如遇錯(cuò)誤繼續(xù)運(yùn)行Application.DisplayAlerts = False '禁用顯示警告提示Worksheets("My目錄").Delete '刪除原目錄工作表Set ws = Worksheets.Add(before:=Sheets(1)) '新建工作表Application.DisplayAlerts = True '恢復(fù)顯示警告提示ws.Name = "My目錄" '新建工作表命名為目錄ws.Cells(1, "A") = "工作表目錄" '設(shè)置目錄表頭名稱(chēng)

... ... ...End Sub

該步驟需要注意的是，如果當(dāng)前工作簿中已經(jīng)存在了名稱(chēng)為“My目錄”工作表，那么新建同名工作表將不被允許，如果不存在該工作表，那么引用該工作做也會(huì)產(chǎn)生運(yùn)行時(shí)錯(cuò)誤。因此我們需要先判斷是否存在再?zèng)Q定是直接新建還是先刪后建。本文中我們使用了On Error Resume Next 語(yǔ)句，通過(guò)該錯(cuò)誤處理語(yǔ)句巧妙地忽略運(yùn)行時(shí)錯(cuò)誤，快速新建“My目錄”工作表。除此方法外，我們還可以通過(guò)循環(huán)遍歷或者字典的應(yīng)用等等來(lái)判斷當(dāng)前工作簿中是否存在指定名稱(chēng)的工作表。如果您不理解 On Error Resume Next語(yǔ)句可以安裝《神奇的VBA》插件了解學(xué)習(xí)。

第3步：通過(guò)循環(huán)遍歷，將其它工作表名稱(chēng)放入“My 目錄”工作表的A列區(qū)域中。并加入超鏈接。

Sub 制作工作表目錄()'步驟1：創(chuàng)建變量Dim ws As WorksheetDim sh As WorksheetDim n As Integer

'步驟2：新建“My目錄”工作表，并設(shè)置表頭On Error Resume Next '如遇錯(cuò)誤繼續(xù)運(yùn)行Application.DisplayAlerts = False '禁用顯示警告提示Worksheets("My目錄").Delete '刪除原目錄工作表Set ws = Worksheets.Add(before:=Sheets(1)) '新建工作表Application.DisplayAlerts = True '恢復(fù)顯示警告提示ws.Name = "My目錄" '新建工作表命名為目錄ws.Cells(1, "A") = "工作表目錄" '設(shè)置目錄表名稱(chēng)

'步驟3：通過(guò)循環(huán)遍歷，將其它工作表名稱(chēng)放入“My 目錄”工作表的A列區(qū)域中。并加入超鏈接。

n = 2For Each sh In Sheets '遍歷所有工作表 If sh.Visible <> 0 And sh.Name <> "My目錄" Then '判斷工作表是否隱藏或者深度隱藏 'My目錄工作表中建立跳轉(zhuǎn)目錄 ws.Hyperlinks.Add Anchor:=ws.Cells(n, "A"), Address:="", _ SubAddress:="'" & sh.Name & "'" & "!A1", _ TextToDisplay:=sh.Name n = n + 1 End IfNextSet ws =NothingEnd Sub

該步驟通過(guò)循環(huán)遍歷將工作表名稱(chēng)寫(xiě)入“My 目錄”工作表單元格區(qū)域中，并在單元格找中錨定加入跳轉(zhuǎn)鏈接。這里使用了Worksheet.Hyperlinks.Add方法。

Worksheet.Hyperlinks.Add (object Anchor, string Address, [object SubAddress], [object ScreenTip], [object TextToDisplay]);

參數(shù)說(shuō)明：

Anchor：必選，表示超鏈接定位標(biāo)記。可以為Range或Shape對(duì)象。
Address：必選，表示超鏈接的地址，一般指的是外部鏈接，如網(wǎng)址，郵件地址等等。
SubAddress：可選。表示超鏈接的子地址。
ScreenTipObject：可選。表示當(dāng)鼠標(biāo)指針停留在超鏈接上時(shí)所顯示的屏幕提示。
TextToDisplayObject：可選。表示要顯示的超鏈接的文本。

好了，我們運(yùn)行下看看。

實(shí)際運(yùn)行非常成功！但是我們發(fā)現(xiàn)了一個(gè)新需求，跳轉(zhuǎn)后，如何快速地再跳轉(zhuǎn)回“My 目錄”工作表呢？其實(shí)很簡(jiǎn)單，在原有循環(huán)遍歷語(yǔ)句塊中，再增加一個(gè)超鏈接返回跳轉(zhuǎn)即可。見(jiàn)下面代碼：

For Each sh In Sheets '遍歷所有工作表 If sh.Visible <> 0 And sh.Name <> "My目錄" Then '判斷工作表是否隱藏或者深度隱藏 'My目錄工作表中建立跳轉(zhuǎn)目錄 ws.Hyperlinks.Add Anchor:=ws.Cells(n, "A"), Address:="", _ SubAddress:="'" & sh.Name & "'" & "!A1", _ TextToDisplay:=sh.Name '【其它工作表中建立返回目錄】 sh.Hyperlinks.Add Anchor:=sh.Range("A1"), Address:="", _ SubAddress:="My目錄!A1", _ TextToDisplay:="返回目錄" n = n + 1 End IfNext

暫時(shí)就簡(jiǎn)單介紹到這里！上面源碼復(fù)制下來(lái)，自己試一下吧。

有關(guān)Excel VBA編程知識(shí)（超多職場(chǎng)牛人的必備秘技），可安裝使用一款優(yōu)秀職場(chǎng)人必備的工具《神奇的VBA》插件，一款嵌入進(jìn)Excel Ribbon界面，打開(kāi)任意Excel工作簿就能隨時(shí)查閱學(xué)習(xí)VBA編程的Excel 插件。