神奇的Excel VBA系列之：製作工作表目錄

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

其實如果您會使用Excel VBA表格編程。您也可以給自己定製一個更適合自己的工作表目錄。本篇文章將分享VBA代碼製作工作表目錄的示範代碼。

示例功能要求：在當前工作簿中新建工作表，名稱命名為“My 目錄”。 然後將工作簿中的所有其它工作表名稱放入“My 目錄”工作表單元格區域中，並建立超鏈接，這樣我們可以點擊鏈接快速跳轉至其它工作表中。

根據要求，我們將該功能分解為3個部。

第1步：創建變量

<code>Sub 製作工作表目錄()
'創建變量
Dim ws As Worksheet
Dim sh As Worksheet
Dim n As Integer
... ... ...
End Sub/<code>

第2步：新建“My目錄”工作表，並設置表頭。

<code>Sub 製作工作表目錄()
'創建變量
Dim ws As Worksheet 

Dim sh As Worksheet
Dim n As Integer

'新建“My目錄”工作表，並設置表頭
On Error Resume Next                                '如遇錯誤繼續運行
Application.DisplayAlerts = False               '禁用顯示警告提示
Worksheets("My目錄").Delete                     '刪除原目錄工作表
Set ws = Worksheets.Add(before:=Sheets(1))      '新建工作表
Application.DisplayAlerts = True                '恢復顯示警告提示
ws.Name = "My目錄"                              '新建工作表命名為目錄
ws.Cells(1, "A") = "工作表目錄"                 '設置目錄表頭名稱
... ... ...
End Sub/<code>

該步驟需要注意的是，如果當前工作簿中已經存在了名稱為“My目錄”工作表，那麼新建同名工作表將不被允許，如果不存在該工作表，那麼引用該工作做也會產生運行時錯誤。 因此我們需要先判斷是否存在再決定是直接新建還是先刪後建。本文中我們使用了On Error Resume Next 語句， 通過該錯誤處理語句巧妙地忽略運行時錯誤，快速新建“My目錄”工作表。 除此方法外，我們還可以通過循環遍歷或者字典的應用等等來判斷當前工作簿中是否存在指定名稱的工作表。如果您不理解 On Error Resume Next語句可以安裝《神奇的VBA》插件瞭解學習。

第3步：通過循環遍歷，將其它工作表名稱放入“My 目錄”工作表的A列區域中。並加入超鏈接。

<code>Sub 製作工作表目錄()
'步驟1：創建變量
Dim ws As Worksheet
Dim sh As Worksheet
Dim n As Integer

'步驟2：新建“My目錄”工作表，並設置表頭
On Error Resume Next                                '如遇錯誤繼續運行
Application.DisplayAlerts = False               '禁用顯示警告提示
Worksheets("My目錄").Delete                     '刪除原目錄工作表
Set ws = Worksheets.Add(before:=Sheets(1))      '新建工作表
Application.DisplayAlerts = True                '恢復顯示警告提示 

ws.Name = "My目錄"                                  '新建工作表命名為目錄
ws.Cells(1, "A") = "工作表目錄"                   '設置目錄表名稱

'步驟3：通過循環遍歷，將其它工作表名稱放入“My 目錄”工作表的A列區域中。並加入超鏈接。
n = 2
For Each sh In Sheets                           '遍歷所有工作表
    If sh.Visible <> 0 And sh.Name <> "My目錄" Then '判斷工作表是否隱藏或者深度隱藏
        'My目錄工作表中建立跳轉目錄
        ws.Hyperlinks.Add Anchor:=ws.Cells(n, "A"), Address:="", _
                          SubAddress:="'" & sh.Name & "'" & "!A1", _
                          TextToDisplay:=sh.Name
        n = n + 1
    End If
Next
Set ws =Nothing
End Sub/<code>

該步驟通過循環遍歷將工作表名稱寫入“My 目錄”工作表單元格區域中，並在單元格找中錨定加入跳轉鏈接。這裡使用了Worksheet.Hyperlinks.Add方法。

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

參數說明：

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

好了，我們運行下看看。

實際運行非常成功！但是我們發現了一個新需求， 跳轉後，如何快速地再跳轉回“My 目錄”工作表呢？其實很簡單，在原有循環遍歷語句塊中，再增加一個超鏈接返回跳轉即可。見下面代碼：

<code>For Each sh In Sheets                                                   '遍歷所有工作表
    If sh.Visible <> 0 And sh.Name <> "My目錄" Then '判斷工作表是否隱藏或者深度隱藏
        'My目錄工作表中建立跳轉目錄
        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 If
Next/<code>

暫時就簡單介紹到這裡！上面源碼複製下來，自己試一下吧。

有關Excel VBA編程知識（超多職場牛人的必備秘技），可安裝使用一款優秀職場人必備的工具

閱讀更多 神奇的VBA 的文章

關鍵字: Excel 遍歷 跳轉