常用功能加载宏——工作表目录

在一个工作簿中，如果工作表太多了，要选中需要操作的Sheet还挺麻烦的。对于Excel用的不大熟练的人，只会按左下角的方向箭头，然后用眼睛去查找，有的人为了让Excel显示多一些工作表名称，还会把右下角的滚动条拉的只剩一点点，造成操作Sheet都不方便。

其实Excel里还有一个方便一点的操作：

在图上那个位置，点击鼠标右键，就能弹出一个新的窗口来选择并激活Sheet，这个操作应该已经很方便了。

当然也可以使用VBA来快速创建一个所有Sheet的目录：

首先在customUI.xml中增加代码：

代码语言：javascript复制

    <group id="GroupShtWk" label="工作表、工作簿">
     <menu id="rbmenuDir" label="目录&#13;" size="large" imageMso="ViewOutlineView">
      <button id="rbbtnShtDir" label="工作表目录&#13;" onAction="rbbtnShtDir" imageMso="AccessFormDatasheet" />

     </menu>
    </group>

插入一个模块，命名MShtWk。

回调函数：

代码语言：javascript复制

Sub rbbtnShtDir(control As IRibbonControl)
    Call MShtWk.ShtDir
End Sub

函数实现：

代码语言：javascript复制

Sub ShtDir()
    Dim i As Long
    Dim result() As Variant
    
    '定义结果数组
    ReDim result(Worksheets.Count, 2)
    
    result(0, 0) = "序号"
    result(0, 1) = "工作表名称"
    result(0, 2) = "跳转"
    
    For i = 1 To Worksheets.Count
        result(i, 0) = i
        result(i, 1) = Worksheets(i).Name
        '使用HYPERLINK创建超链接，这里也可以使用VBA直接创建
        result(i, 2) = "=HYPERLINK(""#'""&RC[-1]&""'!A1"")"
    Next
    
    Dim rngout As Range
    On Error Resume Next
    Set rngout = Application.InputBox("请选择输出单元格", Default:=ActiveCell.Address, Type:=8)
    On Error GoTo 0
    
    If Not rngout Is Nothing Then
        rngout.Resize(Worksheets.Count   1, 3).Value = result
    End If
    
    Set rngout = Nothing
    Erase result
End Sub

这里的跳转使用的是HYPERLINK函数创建的超链接：

代码语言：javascript复制

"=HYPERLINK(""#'""&RC[-1]&""'!A1"")"

这个公式使用的是FormulaR1C1样式，要得到这个公式的文本，可以先在一个单元格中设置好公式，然后在立即窗口输入：

代码语言：javascript复制

?Activecell.FormulaR1C1

其实这里使用VBA直接在对应的单元格创建超链接更好，使用HYPERLINK是为了解VBA配合Excel函数的使用。

vba

0 人点赞