Excel VBA：定制表格目录

在前一篇文章“ ”中，我们介绍了《Power Click》插件的一键生成工作表目录的功能。

其实如果您会使用Excel VBA表格编程。您也可以给自己定制一个更适合自己的工作表目录。本篇文章将分享VBA代码制作工作表目录的示范代码。

示例功能要求：在当前工作簿中新建工作表，名称命名为“My 目录”。 然后将工作簿中的所有其它工作表名称放入“My 目录”工作表单元格区域中，并建立超链接，这样我们可以点击链接快速跳转至其它工作表中。

根据要求，我们将该功能分解为3个部。

<strong>第1步：创建变量

<code>Sub 制作工作表目录()'创建变量Dim ws As WorksheetDim sh As WorksheetDim n As Integer... ... ...End Sub/<code>

<strong>第2步：新建“My目录”工作表，并设置表头。

<code>Sub 制作工作表目录()'创建变量Dim ws As WorksheetDim sh As WorksheetDim 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》插件了解学习。

<strong>第3步：通过循环遍历，将其它工作表名称放入“My 目录”工作表的A列区域中。并加入超链接。

<code>Sub 制作工作表目录()'步骤1：创建变量Dim ws As WorksheetDim sh As WorksheetDim 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 = 2For 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 IfNextSet ws =NothingEnd 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：可选。 表示要显示的超链接的文本。

<strong>好了，我们运行下看看。

实际运行非常成功！但是我们发现了一个新需求， 跳转后，如何快速地再跳转回“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 IfNext/<code>

暂时就简单介绍到这里！上面源码复制下来，自己试一下吧。

有关Excel VBA编程知识（超多职场牛人的必备秘技），可安装使用一款优秀职场人必备的工具《神奇的VBA》插件，一款嵌入进Excel Ribbon界面，打开任意Excel工作簿就能随时查阅学习VBA编程的Excel 插件。

閱讀更多 神奇的VBA 的文章

關鍵字: Excel 跳转 超链接