应用｜让Excel的目录超级自动化

Excel是各种数据统计维护使用的工具。

不管是做网络规划，还是做财务报表，或者是工程统计，都会将数据分门别类地定义在各种工作表里。

然后在一连串的工作表中来回跳转会异常头痛，所以必然想做一个目录索引以方便跳转，就如同Word里的目录索引一样。

那么你有没有为了生成Excel的目录而痛苦？在一次次右键选择超链，再选择一个工作表，最后修改下显示文本，循环往复直到所有的工作表都定义到目录中。

然后一旦有工作表的名字被修改，超链接也必须修改后才可以使用，否则就是一个查无此表；再或者新加入的工作表后，再次需要右键选择编辑等等操作后才能更新到目录中。

那么有没有一个好办法能让这一切都自动化起来呢？

答案当然是：有。

比如像这样，目录在工作表切换后自动生成。

比如当工作表的位置发生变动后，目录也自动跟随调整位置。

比如添加新的工作表后，目录在对应的位置就自动添加了超链索引。

比如工作表的名字更改后，目录里的名字和超链也自动修改。

比如工作表被删除后，目录也自动删除其对应的索引。

这样的自动化目录是不是看起来就丝滑了不少，富裕的时间至少可以去喝一杯82年的咖啡。

那么是如何实现的呢？

其实就是在目录工作表中加载了几行VBA代码，具体的代码如下（左右滑动看完整代码）：

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False

Range('A1') = 'Table of Content'
If Range('A5000').End(xlUp).Row >= 2 Then
    Range('A2', 'A' & Range('A5000').End(xlUp).Row).ClearContents
End If
For i = 3 To Sheets.Count
    j = i - 1
    Range('A' & j) = Sheets(i).Name
    name_cell = Range('A' & j)
    Range('A' & j).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:='', SubAddress:= _
        ''' & name_cell & ''' & '!A1', TextToDisplay:=name_cell
    Next
    Application.ScreenUpdating = True
End Sub


其中有两处可以适配修改的地方：

1. 在目录工作表的第一个单元格A1中定义表头：Table of Content

Range('A1') = 'Table of Content'

2. for循环里i和j的含义：在目录工作表中A列的第j个单元格中生成第i个工作表的索引链接，所以要很好的把握它们俩的关系。

• 比如如下代码是因为我习惯定义第一个工作表作为此Excel的描述和修订版本的追踪，第二个工作表是目录，所以需要在目录里生成第三个工作表和之后的工作表的索引：

For i = 3 To Sheets.Count
    j = i - 1


• 如果你的习惯是第一个工作表是目录，然后要生成第二个和之后的工作表的索引，那么代码需要修改成如下即可：

For i = 2 To Sheets.Count
    j = i

哪里添加代码和让其永久生效呢？

选择Developer中的Visual Basic，然后点击工作表后添加代码保存即可。

然后需要将Excel存储为支持宏(Macro-Enabled)的格式即可永久生效，比如office2007开始的xlsm：

可能你会说excel里没有Developer选项，这就需要在选项设置中勾选Developer使其显示：

可能会遇到的问题。

一个是目录工作表后移导致目录混乱，所以要严格控制For循环中i和j的关系，并且保证目录工作表的位置不变：

第二个可能遇到的问题是再次打开Excel后VBA不工作的问题，主要原因是宏被禁止了。

方案一是打开消息提示窗口，然后在每次打开excel的时候就会有安全问题的提示，直接允许即可：

方案二就是直接允许运行VBA的宏，一劳永逸，但是会有安全方面的风险，比如我司就直接不允许修改宏配置：

最后还有一个小bug，就是工作表的名字不能是数字，否则会提示bug，这个时候点击End然后修改工作表名字即可：

到这里，Excel使用VBA生成自动化的目录索引就告一段落了，或者你有更好的方案也欢迎留言私信分享！