各位朋友,你们好,今天要和你们分享的是一个关于Excel和VBA的基础内容。文章最后有自动生成工作表目录的代码,代码中用的内容,就是本文所讲述的。
在VBA中,我们怎么引用Excel的工作表,这些引用方式各有什么特点?
先看下图:
从图片中我们可以看到,对工作表的引用有三种常规方式:
1、CodeName属性引用。
2、Name属性引用。
3、Index属性引用。
接下来我们来看看是如何引用的,并且各种引用方式的优缺点。
一、前面的基础
这里有两个Name,很多人可能要混淆,先给你们看张图片:
上面部分是工作表对象,下面是工作表对象的一些属性,在这个属性中,有两个名字项,这两个名字属性,分别表示什么意思呢?
(名称)所对应的Sheet3,是括号外的Sheet3(Sheet3);Name所对应的Sheet3,是括号中的Sheet3(Sheet3)。我们经常干的修改工作表名称这个事情,其实修改的就是括号内的部分。我们以动图来演示效果:
大家一定要记住这两个名称,因为对这两个不同的名称,用的是不同的引用方式,效果也不一样。
二、CodeName属性引用
利用CodeName属性,对工作表进行引用,代码书写方式为(比如引用CodeName表中的A1单元格):
CodeName.Range(“A1”)
我们还是用动图来演示(这里,我用立即窗口,来进行代码的演示。所谓立即窗口,可以理解为输入一行代码,它可以立即执行出结果,常用于检查代码):
这种引用方式,是我比较常用的。主要原因有以下两点:
1、CodeName一般人是不会去修改的,所以一旦写好了,就可以不用管;
2、用这种引用方式,不受工作表名称更改的影响(这点很重要,尤其是在你做的文件工作表名称未确定,或者不确定别人以后是否会修改工作表名称时。这种引用,即使今后修改了工作表名称,代码依然可以正常运行)
但是,这个引用方式也有缺点:
如果做一个比较大的工程,几十张工作表,用这种方式就很容混淆,在代码维护和检查上,没有用Name属性引用那么直观、那么好检查。所以,要根据实际情况来选择。
三、INDEX属性引用
这里的INDEX属性,就好比我们军训的时候排队列时的报数,都是1、2、3……,但是每天排在第1、2、3……位置上的人可能会不同,这就会造成很多的不确定性。我们还是以动图的形式来演示:
同样的代码,在调整了工作表的顺序后,得到的结果却不一样,这就是这种引用方式的缺点。
但是这种引用方式有它的优点:可以比较方便的对某几张表格进行遍历循环。
如果我们要对工作所有表进行遍历循环,用一句【For Each Sheet In ThisWorkbook.Sheets】就可以实现。
如果说要对某几张表格进行遍历循环呢?CodeName或者Name属性引用都比较难实现,但是用Index属性引用就非常方便。看下面的动图(动图中的代码,是按排列顺序,依次在立即窗口中显示相应工作表的名称):
所以这种引用方式的优点就是:方便遍历循环部分工作表;
缺点就是:随着工作表顺序的改变或者工作表数量的增加、减少,代码都会收到影响,代码维护难度很大。
四、Name属性引用
这种引用方式,也是比较常见的,因为这里的Name就是工作表名称,通过这个名称,我们可以非常直观的看出我们折断代码的数据来自哪个工作表,不像前面两种引用方式,有的看不到,有的还会因为人为操作而变化。
所以这种引用方式的优点:代码比较直观看到数据来源,检查代码,维护代码都比较方便;
缺点是:工作表名称改变,会影响代码的执行。比如将表A改为了表B,原来引用表A的代码,就无法执行了,需要在代码中同步修改。
五、给自己做一个工作表目录
今天学习了在VBA中对工作表的引用方式,我们可以利用所学的内容,用代码给自己的工作表做一个带链接的目录(看着别人操作很高大上,当明白原理后,其实就很简单)。我们来看看吧:
这里我们需要用到的对象是【Hyperlinks】需要用到的方法是【ADD】
写法是: Hyperlinks.Add(Anchor,Address,SubAddress,ScreenTip,TextToDisplay)
Anchor:Object 类型,必需。超链接的位置。可为 Range 对象或 Shape 对象。
Address: String 类型,必需。超链接的地址。
SubAddress:Variant 类型,可选。超链接的子地址。
ScreenTip:Variant 类型,可选。当鼠标指针停留在超链接上时所显示的屏幕提示。
TextToDisplay:Variant 类型,可选。要显示的超链接的文本
在操作之前,我们要在工作表最前面插入一张新工作表,然后A1录入序号,B1录入工作表名称。然后从第二个工作表开始遍历,依次提取工作表名称到B列,然后设置超链接。我们用的代码如下:
Sub 生成目录()
Dim a As Integer, b As Integer
For a = 1 To Sheets.Count
If Sheets(a).Name = "目录" Then
Exit For
End If
Next
If a > Worksheets.Count Then Sheets.Add.Name = "目录"
Sheets("目录").Move Before:=Sheets(1)
With Sheets("目录")
.Range("A1:B" & .Cells(Rows.Count, 1).End(xlUp).Row + 1).Clear
.Range("A1") = "序号"
.Range("B1") = "工作表名称"
For b = 2 To Sheets.Count
.Range("A" & b).Value = b - 1
.Range("B" & b).Value = Sheets(b).Name
.Range("B" & b).Hyperlinks.Add Anchor:=.Cells(b, 2), Address:="", SubAddress:=Sheets(b).Name & "!A1", TextToDisplay:=Sheets(b).Name
Next
.Range("A1:B" & b - 1).HorizontalAlignment = xlCenter
.Range("A1:B" & b - 1).Borders.LineStyle = xlContinuous
End With
End Sub
好了,今天就到这里,感谢各位朋友的关注和支持。
如果你喜欢我分享的内容,请点个赞支持下;
如果你觉得我分享的内容对你有帮助,可以关注我;
如果要看我以前分享过的好玩的内容,大家可以去我的主页查看历史文章。