如何一键生成带超链接的工作表目录?

在VBA系列教程第2卷,用了17章的篇幅给大家集中介绍了如何使用VBA进行数据分析和处理,内容涵盖了单元格常用操作、数组遍历读写数据、字典聚合与查询数据等。这章开始给大家介绍工作表对象的常用操作,比如新建、删除、重命名、排序、解密、汇总和拆分等。


1 丨

什么是工作表



先来给大家简单介绍一下工作表。这货是单元格的爸爸、工作簿的儿子。众所周知,工作表的单元格多的数不过来,所以这家伙儿女众多,兄弟也不少,是个生活十分艰难的中年老男人……


在VBA基础篇「什么是对象」里咱们讲过,一个工作表是单个对象,用Worksheet表示,一组工作表就是工作表对象的集合,用复数Worksheets表示。因此,如果我们要声明一个工作表变量,可以使用语句▼



Dim sht As Worksheet


但不能使用语句



Dim sht As Worksheets


毕竟一个和一群是两个截然不同的概念,就好比单挑和群殴的区别。


如何一键生成带超链接的工作表目录? Excel教程 第1张


……


工作簿不允许它的儿子们有重名,所以每一张工作表都有自己独一无二的标签名,比如,选中工作表名称为"看见星光"的工作表。


如何一键生成带超链接的工作表目录? Excel教程 第2张


示例代码如下▼





Sub SelectSht()    Worksheets("看见星光").SelectEnd Sub


工作表对象是一个集合,因此除了标签名以外,每张工作表也都有自己的索引号,比如"看见星光"坐在前排数第2个位置上,因此也可以使用以下代码选中他。






Sub SelectShtByIndex()    Worksheets(2).Select    MsgBox Worksheets(2).NameEnd Sub


但需要注意的是,索引的位置并非固定不变,比如我们将"看见星光"升值不加薪调到第1个位置上。


如何一键生成带超链接的工作表目录? Excel教程 第3张


此时Worksheets(2)就变成了"目录表",而不是原来指向的"看见星光"。


……


除了这两种方式外,每张工作表还拥有独一无二的代码名称;代码名称由系统按序号默认生成的,可以在VBE编辑器工作表对象的属性中进行修改。


如何一键生成带超链接的工作表目录? Excel教程 第4张


如上图所示,工作表的标签名称是"看见星光",代码名称是"Sheet1"。使用代码名称也可以引用工作表,而且不论该工作表的标签名修改为"看见月光"还是"看见阳光",代码名称都不会发生改变。

示例代码如下▼







Sub SelectShtByCodeName()    Sheet1.Select    MsgBox Sheet1.Name '返回工作表标签名    MsgBox Worksheets("看见星光").CodeName '返回工作表代码名称End Sub


……

既然三种方式都能表达引用工作表,那么各有什么优劣之处呢?

打个响指,做个总结如下。

索引法是根据工作表位置确定的,常用于表格排序。代码名称法不会因工作表名称的改变而改变,看起来好像是个很可靠的同志,但如果工作表删除后又新建,系统自建的代码名称也可能会紊乱,所以他只是看起来比较可靠罢了,实际应用也很少——不过它有一个很大的优点,输入工作表代码名称后再输入点号,系统会自动显示对象的方法和属性。作为VBA初学者,可以先使用代码名称编写代码,再将工作表的引用修改为标签名称。

最常使用的还是工作表标签引用法,通过工作表标签名称引用相关工作表;但这个方法也有个问题,工作表的标签名很有可能会被用户更改,比如"看见星光"被更改为了"看见月光",此时再使用Worksheets("看见星光")会被提示"下标越界",意思就是当前工作簿的工作表集合中查无此表。

因此在使用标签法引用工作表前,往往需要判断当前工作簿中是否存在相关工作表……▼



2 丨

工作表遍历



判断工作簿中是否存在指定名称的工作表,有两种常用方式,一种是试错法,一种是遍历法。

试错法示例代码如下▼

代码看不全可以左右拖动..▼











Sub HaveSheet()    Dim sht As Worksheet    On Error Resume Next '忽略程序错误,使之继续运行    Set sht = Worksheets("看见星光") '将指定工作表赋值变量sht    If Err.Number Then '如果不存在指定工作表,程序出现错误,err.number会返回非0数值        MsgBox "工作簿中不存在名为看见星光的工作表。"        Exit Sub '退出程序    Else        sht.Select    End IfEnd Sub

代码解析:


第3行代码的作用是忽略代码运行中产生的错误,使程序继续运行。


第4行代码将指定工作表赋值变量sht。如果当前工作簿不存在指定工作表,这句代码会返回错误"下标越界",但由于第3行代码的作用,程序不会弹窗警告错误信息,而是继续运行。


第5行代码判断错误号。如果当前代码运行未出现错误,错误号会返回0,否则返回非0数值。在逻辑判断中,非0的数值为True,0为False。以此即可判断当前工作簿中是否存在指定工作表。


……


遍历法示例代码如下▼

















Sub HaveSheetByTra()    Dim sht As Worksheet, bln As Boolean    bln = False '逻辑值初始化    For Each sht In Worksheets '遍历工作表        If sht.Name = "看见星光" Then            bln = True '布尔标记改为true            Exit For '退出循环        End If    Next    If bln Then '如果存在指定工作表        sht.Select    Else        MsgBox "工作簿中不存在名为看见星光的工作表。"    End IfEnd Sub


代码解析:

Worksheets是一个集合,因此第4至第9行代码使用For each语句遍历每一个工作表的名字,如果有指定工作表的名字,则将布尔标记bln修改为True。第10至第14行代码根据布尔变量bln,返回相应的结果。

……


3 丨

生成工作表目录



本小节给大家分享一个常用的案例,为工作簿一键生成带超链接的工作表目录。

示例代码如下▼

代码看不全可以左右拖动..▼














Sub BuildSheetList()    Dim sht As Worksheet, i As Long, strName As String    With Columns(1)        .Clear '清空A列数据        .NumberFormat = "@" '设置文本格式    End With    Range("a1") = "目录"    For i = 1 To Sheets.Count '索引法遍历工作表集合        strName = Sheets(i).Name '表名        Cells(i + 1, 1).Value = strName        ActiveSheet.Hyperlinks.Add anchor:=Cells(i + 1, 1), Address:="", _                SubAddress:="'" & strName & "'!a1", TextToDisplay:=strName    NextEnd Sub

代码解析▼

第8至第13行代码采用索引法遍历当前工作簿表的集合。Sheets.Count语句返回当前工作簿工作表的数量。

第11行代码利用工作表的Hyperlinks属性创建超链接,语法格式如下▼

expression.Add (Anchor, Address, SubAddress, ScreenTip, TextToDisplay)


各参数说明如下图所示。

如何一键生成带超链接的工作表目录? Excel教程 第5张



4 丨

Worksheets和Sheets的区别



如果你是像星光一样细心的朋友,可能会发现上述一系列代码,对工作表的表述,我们有时候使用了Worksheets,有时候又使用了Sheets,那么两者有什么去区别呢?

如何一键生成带超链接的工作表目录? Excel教程 第6张

如上图所示,从理论上来说,工作簿并非只有普通的工作表,还有图表、宏表等形式的工作表;Worksheets只是普通的工作表,sheets不但包含了普通工作表,也包含了其它类型的工作表。
不过如果从实际出发……,图表、宏表等形式的工作表都是历史遗留的产物了,极其稀罕,因此绝大部分时候Wroksheets和sheets是相等的……看我锐利迷人的小眼睛,但是严谨期间,咱们还是建议使用Worksheets。
本站均来自网络,如有侵权,请联系站长删除!
海印素材 » 如何一键生成带超链接的工作表目录?

发表评论

欢迎 访客 发表评论