第18课 工作表与图形对象引用

《来吧！带你玩转 Excel VBA》 http://product.dangdang.com/product.aspx?product_id=23278575 第18课 工作表与图形对象引用 工作表对象的类别名称是Worksheet，图形对象的类型名称是Shape，单元格对象的类别名称是Range。 本课时讲解工作表对象与图形对象的引用方式。 18.1 表对象集合与工作表对象集合 Excel VBA采用Sheets表示表对象集合。表对象包括工作表、图表、4.0宏表和5.0对话框。在工作表标签处单击右键，选择菜单【插入】可看到下图内容，在图中显示了表对象的4类子对象： 图1 插入表的对话框 工作表对象集合Worksheets代表一个工作簿中的所有工作表，自动忽略图表、4.0宏表和5.0对话框。 下以代码可以展示表对象与工作表对象的差异： Sub 表对象与工作表对象数量() MsgBox "表对象数量:" & Sheets.Count & Chr(13) & "工作表对象数量:" & Worksheets.Count End Sub 图2 区分表对象和工作表对象 上图中有2个工作表，属于Worksheets对象集合的成员，有5个表，属于Sheets对象集合的成员。不过5个表都统称为Worksheet对象，类似于不管是单个单元格、区域、行、列还是所有Cells集合都统为Range对象一样。 在日常工作中，利用VBA操作的对象通常都是工作表，4.0宏表和5.0对话框已淘汰多年(目前只有宏病毒开发者才会使用4.0宏表，而对于图表则基本没人使用了，通常采用嵌入式图表，而非单独生成一个Chart对象)，因此工作中常用Worksheets代替Sheets使用。 附注：既然已打算学习Excel VBA，说明你已经算Excel高手了，对Excel的基础功能应该都了如指掌，退一步说，你不是高手，至少有成为Excel高手的决心，否则不必要专攻VBA了。基于此，请日常工作中称呼Excel的各种对象时尽可能准确。例如工作表、工作簿(工作簿包含工作表)、Excel软件等等,不能将各种概念混淆。例如下图是工作表，也可以称之为文件，请不要称之为表、工作表或者Excel软件。 图3 工作簿 下图是在移动工作表位置，不能称之为“移动工作簿”，也尽量不要说成“移动表”，虽然在VBA的概念里面，Sheet1就是一个表，但是鉴于日常工作中很多人将“生产表.xls”也说成是表，容易产生混淆，因此尽量说全称“移动工作表”。 图4 移动工作表 工作表->单元格的父对象，由单元格、行号、列标组成 工作簿->由多个工作表组成，它是一个单独的文件。百度百科关于工作簿的解释是错误的，它说工作簿中最多可建立255个工作表，其实不然，是一次性创建工作表不能超过255个，但是可以多次创建。一个工作簿能存放的工作表数量是没有限制的，不过电脑的内存资源有限，因此可以利用代码无限地创建新工作表，直到你的电脑承受不起为止。 以下代码一次性新建300个工作表，因此会失败： Sub 新建工作表() Worksheets.Add , Count:=300 End Sub 以下代码分4次创建工作表，每次创建255个，因此可以创建成功(印证百度百科的错误)： Sub 新建工作表() Worksheets.Add , Count:=255 Worksheets.Add , Count:=255 Worksheets.Add , Count:=255 Worksheets.Add , Count:=255 End Sub Excel软件->软件与工作表、工作簿无关，工作表和工作簿是由用户创建的，例如你、我，而Excel软件是微软公司开发的，它们之间是有很大区别的。因此： 1. 卸载Excel软件不会影响你的工作簿、工作表，OFFICE不在了，你的文件照样存在。 2. “我有40个表格，请帮我合并一下”，要么说成40个工作簿，要么说成40个工作表，请不要说成表格，很难明白你笔下的表格是什么。 3. 将“生产表.xls”文件传过来请不要说成“将你的Excel传过来”，工作簿与Excel软件是完全不同的概念。 4. “用代码如何可以关闭工作表？”这种说法也是错的，工作表没有关闭这种说法，只能隐藏、显示或者删除、新建，只有工作簿可以关闭，Excel软件也可以关闭。 18.2 单个工作表对象的引用方式 Worksheets代表工作表对象集合，工作簿中有多少个工作表，Worksheets对象就包含多少个工作表。 引用工作表对象集合的子对象有两种方式：按名称引用和按位置引用。 1.按名称引用 按名称引用的方式是在工作表名称前后加上半角引号作为Worksheets的参数。 假设工作表名为“生产表”，那么可以用以下代码引用此工作表： Worksheets ("生产表") 注意：工作表名称的前后必须有双引号，而且是半角的双引号。 如果工作表名称在A1单元格中，那么引用A1的值所代表的工作表可用Worksheets(Range("a1").Value)。下图中展示了删除A1的值所代表的工作表的方法： 图5 删除A1指定的工作表 如果用变量代替工作表名称，那么对变量赋值后，将变量作为Worksheets的参数即可。例如以下代码表示删除变量指定的工作表： Sub 删除工作表() '功能：删除变量指定的工作表 Dim ShtName As String '声明一个String型的变量 ShtName = "生产表" '对变量赋值 Worksheets(ShtName).Delete '删除工作表 End Sub 注意：变量前后不能添加引号，否则不再具备变量的功能。例如上面的代码Worksheets(ShtName)改写成Worksheets("ShtName")的话，不再是引用“生成表”，而是引用名为“ShtName”的工作表。 另外，不能写作Worksheet(ShtName)，因为Worksheets是复数形式，代表所有工作表的集合，只有复数的对象才拥有子对象。Worksheet只是工作表的类别名称，它没有子对象。 2.按位置引用 按位置引用是指使用工作表出现在工作表标签的顺序序号作为Worksheets的参数的一种引用方式。 Worksheets (1)——引用第1个工作表，最左边的那个。 图6 引用第一个工作表 上图中Worksheets (1)代表Sheet3，Worksheets (2)代表Sheet1，Worksheets (3)代表生产表。 如果对上图中的工作簿引用第4个工作表，那么必定会出现“下标越界”错误。因为工作簿中只有3个工作表，有效范围是1到3，4超出了有效范围，因此“下标越界”就好理解了吧？ 图7 展示下标越界 Worksheets (Worksheets.count)——引用最后一个工作表，因为Worksheets.count)代表工作表的总数量，当Worksheets.count的值为3时，代码就引用第3个工作表。 由于按位置引用时参数只能用数值，因此当使用变量作参数时，变量的类型就显得格外重要。 Sub test() '功能：获取第2个工作表的名称 Dim ShtIndex As Byte '声明一个Byte型的变量 ShtIndex = 2 '对变量赋值为2 MsgBox Worksheets(ShtIndex).Name '引用变量所指定的工作表 End Sub 以上代码中变量ShtIndex的值为2，数据类型是byte，编写方式正确，因此可以正常执行代码。 Sub test2() '功能：获取第2个工作表的名称 Dim ShtIndex As String '声明一个变量 ShtIndex = 2 '对变量赋值为2 MsgBox Worksheets(ShtIndex).Name '引用变量所指定的工作表 End Sub 以上代码中变量ShtIndex声明为文本型，不再是数值，那么Worksheets(ShtIndex)就不再是引用序号为2的工作表，而中名字是2的工作表，相当于代码“Worksheets("2 ")”，而非等同于代码“Worksheets(2)”。因此执行以上代码同样会产生“下标越界”错误，因为有效的工作表名称中不包含“2”(此处的2代表工作表名称，而不是顺序)。 再如将A1单元格所指定的工作表移到最前面去，见下图： 图8 移动A1所指定工作表 假设采用以下代码，必定产生“下标越界”错误，因为2008是数值，VBA将它当成顺序，而工作簿中不存在第2008个工作表，因此出错。 Sub test() '功能：将A1指定工作表移到最前面 Worksheets(Range("a1")).Move before:=Worksheets(1) End Sub 将代码按以下方式修改即可，通过“&""”转换成文本： Sub test2() '功能：将A1指定工作表移到最前面 Worksheets(Range("a1") & "").Move before:=Worksheets(1) End Sub 也可以采用Cstr函数将2008转换成String型： Sub test3() '功能：将A1指定工作表移到最前面 Worksheets(CStr(Range("a1"))).Move before:=Worksheets(1) End Sub 18.3 引用活动工作表 活动工作表用ActiveSheet表示，每个工作簿都拥有一个活动工作表。 引用活动工作表中的单元格时可以忽略活动工作表，例如： ActiveSheet.Range ("a1")—>表示活动工作表的A1单元格，可以简写为Range ("a1") 但是其它引用工作表的单元格时不能忽略父对象工作表的引用。 18.4 在循环语句中引用工作表 当工作表较多时，批量操作工作表必须配合循环语句使用，循环语句将在22课详解，本课题提及一下即可，展示多工作表批量操作的基本思路。 案例一：通过对象变量引用工作表 Sub 工作表批量命名() Worksheets.Add , Count:=4 '新建4个工作表 Dim sht As Worksheet '声明一个工作表对象变量 For Each sht In Worksheets '遍历所有工作表 sht.Name = Format(sht.Index, "AAAA") '逐一以中文的星期命名 Next sht End Sub 图9 新建工作表并批量重命名 代码中sht.Index表示工作表的序号，例如第一个工作表的Index等于1，而Format(sht.Index, "AAAA") 相当于计算1900年1月1日是星期几(星期日)。 案例二：通过序号引用工作表 Sub 工作表批量命名() Dim Item As Byte '声明一个Byte型变量 For Item = 1 To 7 '从1到7 '对第item个工作表命名，名字来源为A列第Item个单元格 Worksheets(Item).Name = Cells(Item, 1).Value Next sht End Sub 图10 用指定区域的值批量重命名工作表 案例三：通过名称引用工作表 要求：将4个年级的工作表数据合并到新工作表中。 图11 待合并的工作表 Sub 合并工作簿() Dim Item As Byte, sht As Worksheet '声明两个变量 '新建一个工作表，放在所有工作表之后，且将它赋值给变量Sht Set sht = Worksheets.Add(after:=Worksheets(Worksheets.Count)) For Item = 1 To 4 '从1到4，遍成4个工作表 '将第Item个工作表的已用区域复制到Sht表中的已用区域的下一行中 Worksheets(Item).UsedRange.Copy sht.UsedRange.Offset(sht.UsedRange.Rows.Count).Cells(1) Next sht.Rows(1).Delete '删除第一行 End Sub 以上代码可以将4个工作表的数据合并到第5个工作表中，第5个工作表是用代码创建的，放在最末端。不过由于每个工作表都有一个标题行，因此合并后存在多个标题行，为了解决这个问题，需要用到第17课的知识——Offset和Intersect. Sub 合并工作簿2() '改进版，只保留第一个标题行 Dim Item As Byte, sht As Worksheet, 目标单元格 As Range '声明两个变量 '新建一个工作表，放在所有工作表之后，且将它赋值给变量Sht Set sht = Worksheets.Add(after:=Worksheets(Worksheets.Count)) For Item = 1 To 4 '从1到4，遍成4个工作表 ' 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 粘贴数据时的目标单元格，使用变量代替，由于后面需要两次调用，所以用变量代替目标区域可以方便后面的调用 '能简代码，也方便理解 Set 目标单元格 = sht.UsedRange.Offset(sht.UsedRange.Rows.Count).Cells(1) If Item = 1 Then '如果是复制第一个工作表的内容 Worksheets(Item).UsedRange.Copy 目标单元格 '复制已用区域(包含标题) Else '否则 '只复制正文，排除了标题行Offset(1,0)表示向下偏移一行，排除了标题 Intersect(Worksheets(Item).UsedRange, Worksheets(Item).UsedRange.Offset(1, 0)).Copy 目标单元格 End If Next sht.Rows(1).Delete '删除第一行 End Sub 改进后的代码显得更人性化，大家应该也可以从中学到工作表引用的一些技巧。 18.5 跨工作簿引用工作表 前面所讲的都是引用活动工作簿中的工作表，因此可以忽略工作簿，直接引用工作表。假设当前打开的工作簿数量不只一个，那么引用非活动工作簿中的工作表则应注意代码的层级关系，需要将工作表的父对象一并书写完整。 假设当前打开了“生产部.xls”和“财务部.xls”，活动工作簿是“生产部.xls”，两个工作簿中都存在“6月”工作表。那么引用“生产部.xls”中的6月工作表应按以下方式编写代码： Worksheets("6月") 如果要引用“财务部.xls”中的6月工作表则应采用以下方式的代码： Workbooks("财务部").Worksheets ("6月") 18.6 不同方式引用工作表时的属性与方法列表问题 引用工作表时，可能产生属性与方法提示，也可能不产生，取决于你的引用方式。 属性与方法列表对于编程工作极度重要，应尽量采用能产生提示的方式引用工作表。 下图的方式录入代码，不会产生提示： 图12 无提示1 图13 无提示2 声明一个Worksheet型的变量，然后将工作表赋值给此变量，且在书写变量的名称后加一个小圆点就会产生工作表的属性与方法提示，见下图： 图14 工作表的属性与方法提示 18.7 工作表的几个案例应用 1.隐藏“配置表” 工作表对象的Visible属性表代表工作表的显示状态，包含三种可选项： xlSheetHidden：用户可以通过右键菜单取消隐藏。 xlSheetVeryHidden：深度隐藏工作表，不可以通过右键菜单取消隐藏 xlSheetVisible：显示工作表。 当工作簿中有一个名为“配置表”的工作表需要深度隐藏时，可以用以下代码： Sub 隐藏() Worksheets("配置表").Visible = xlSheetVeryHidden End Sub 此方式隐藏工作表后，在工作表标签处单击右键，无法取消隐藏，见下图： 图15 无法取消隐藏 可以用以下代码显示“配置表”： Sub 显示() Worksheets("配置表").Visible = xlSheetVisible End Sub 延伸应用：假设不确定工作簿中有多个工作表处于隐藏状态，需要将所有工作表都显示出来(宏病毒往往会创建深度隐藏的工作表)，可以配合循环语句使用，代码如下： Sub 显示所有工作表() Dim Item As Integer '声明一个Integer型变量 '遍历所有表(此时不能用工作表，因此它会跳过宏表和图表，病毒通常采用宏表) For Item = 1 To Sheets.Count Sheets(Item).Visible = xlSheetVisible '逐个显示工作表 Next Item End Sub 2.创建打开工作表的超级链接 单元格中可以创建链接到其它工作表的超级链接，当工作表太多时，可以很方面地通过链接在指定工作表中跳转。不过如果使用VBA，不需要逐个添加超级链接，直接用Worksheet.Activate方法激活指定名称的工作表即可。 下图中的工作簿有100个工作表，假设要打开名为“朱贵”的工作表，由于不知道“朱贵”工作表排在哪个位置，工作表名称又不支持查找，怎么办呢？ 图16 工作表目录 编写以下代码： Sub 打开指定工作表() On Error Resume Next '当程序出错时，不弹出错误提示 Worksheets(ActiveCell.Value).Activate '激活活动单元格所指定的工作表 End Sub 代码的含义是激活活动单元格所指定的工作表，所以在任意单元格中录入朱贵，然后运行代码，然后自动进入名为朱贵的工作表中。也可以将朱贵改为100个工作表的任意工作表名称，然后执行代码跳转到该表。 延伸应用：在单元格中创建下拉列表，再配合工作表的Worksheet_Change事件即可实现选择工作表名称进入对应的工作表界面。 设计过程如下： 1)将所工作表名称输入到A列中(当然可以用VBA的循环语句完成)。 2)在B1单元格创建数据有效性序列，从而使用B1单元格产生下拉列表，列表的内容是A列的所有工作表名称。 图17 设置数据有效性 3)按【Alt+F11】组合键进入VBE窗口，再双击工程资源管理器的“工作表目录”，然后在右边的工作表事件代码窗口中录入以下代码： '工作表事件，修改单元格的值时触发事件 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then '如果被修改的单元格的址是B1 Worksheets(Target.Value).Activate '激活B1的值指向的工作表 End If End Sub 4)返回工作表界面在“工作表目录”的B1单元格中单击下拉列表，随意选择一个工作表名称，程序将会进入同名工作表中。 3.删除工作表 删除工作表直接用Worksheet.Delete方法即可，例如删除名为“生产表”的工作表： Worksheets("生产表").Delete 删除第3个工作表(注意，代码中的3不能添加引号)： Worksheets(3).Delete 删除最后一个工作表： Worksheets(Worksheets.Count).Delete 这里涉及一个新问题——删除工作表时会产生一个提示框，它会造成程序中断，而且批量删除工作表时会影响程序的执行效率，因此需要想办法关闭提示。 图18 删除工作表时产生的提示 VBA提供了一个关闭提示的办法——将DisplayAlerts赋值为False，待操作完毕后再恢复提示即可。因此以下代码删除“生产表”不会提示上图的提示信息。 Sub 删除工作表() Application.DisplayAlerts = False '关闭提示 Worksheets("生产表").Delete '删除工作表 Application.DisplayAlerts = True '恢复提示 End Sub 延伸应用： 合并非空单元格时也会产生提示，见下图： 图19 合并单元格的提示 为了解决此问题，也应该在合并单元格的代码前将DisplayAlerts赋值为False，合并完后再将它的值恢复为True。代码如下： Sub 合并单元格() Application.DisplayAlerts = False '关闭提示 Range("a1:B2").Merge '合并单元格 Application.DisplayAlerts = True '恢复提示 End Sub 18.8 图形对象集合 图形对象的类别名称是Shape，任何图形对象都可以统通为Shape对象。 图形对象包括图片、自选图形、艺术字、文本框、SmartArt、嵌入式图表、表单控件、ActiveX控件等等。 图形对象集合采用Shapes表示，代表工作表中的所有图形对象。 例如计算活动工作表的图形对象数量可用以下代码： MsgBox ActiveSheet.Shapes.Count 如果要选择活动工作表中的所有图形对象，可用以下代码： ActiveSheet.Shapes.SelectAll 注意：Shapes对象不支持Select方法，只支持SelectAll。 图形对象集合还有另一个称呼——DrawingObjects，录制定位对象的宏时就会产生这个对象名称。 因此选择活动工作表中的所有图形对象还可以使用以下代码： ActiveSheet.DrawingObjects.Select DrawingObjects对象是支持Select方法的。 一个比较特殊的问题点需要补充：Shapes代表所有图形对象，但是它不支持Delete方法，因此期望通过“ActiveSheet.Shapes.Delete”删除所有图形对象势必会令你失望，改用“ActiveSheet. DrawingObjects.Delete”才可删除活动工作表中所有图形对象。 18.9 引用单个图形对象 和引用工作表对象一样，引用单个图形对象也包含按名称引用和按钮位置引用。例如： Shapes ("矩形 2")——表示引用名为“矩形 2”的图形对象 Shapes (3)——表示引用工作表中第3个图形对象 Shapes (Shapes.count)——表示引用最后一个图形对象。 引用图形对象和引用工作表有形式上没有分别，所以只要掌握了前面的知识点，图形对象的引用方式就自然学会了，因此此处不再详述。 但是还是有一点需要详述——图形对象的Type属性，可以在帮助中搜索关键字“MsoShapeType”，得到下表结果： 名称 值 说明 msoShapeTypeMixed -2 混和形状类型 msoAutoShape 1 自选图形 msoCallout 2 标注 msoChart 3 图 msoComment 4 批注 msoFreeform 5 任意多边形 msoGroup 6 组合 msoEmbeddedOLEObject 7 嵌入的 OLE 对象 msoFormControl 8 窗体控件 msoLine 9 线条 msoLinkedOLEObject 10 链接 OLE 对象 msoLinkedPicture 11 链接图片 msoOLEControlObject 12 OLE 控件对象 msoPicture 13 图片 msoPlaceholder 14 占位符 msoTextEffect 15 文本效果 msoMedia 16 媒体 msoTextBox 17 文本框 msoScriptAnchor 18 脚本定位标记 msoTable 19 表 msoCanvas 20 画布 msoDiagram 21 图表 msoInk 22 墨迹 msoInkComment 23 墨迹批注 msoIgxGraphic 24 SmartArt 图形 也就是说图形对象统称为Shape，但是子类别却有很多，可以通过这些类别属性区分各种图形对象。例如工作表中有若干个图形对象，包括图片、矩形、艺术字、图表等等，要求只删除其中的图片，那么可用以下代码： Sub 只删除图片() Dim shp As Shape '声明一个Shape型的图形对象 For Each shp In ActiveSheet.Shapes '遍历所有图形对象 '如果是图片，那么删除它 If shp.Type = msoPicture Then shp.Delete Next shp End Sub 同学们可以修改代码中的msoPicture，从而实现只删除矩形、只删除图表或者只删除艺术字…… Excel百宝箱就有这个功能，刚好用到了Shape.Type属性，见下图： 图20 Exel百宝箱的功能之一 课后思考题目： 1. 假设工作簿中有工作表、图表、宏表，请用代码计算图表与宏表的数量之和。 2. 假设工作簿中有一个“汇总表”，请用代码判断该表是处于显示状态还是隐藏状态。 3. 用代码新建一个工作表，取名为“汇总表”，且将此表放在所有工作表之前。 4. 请用代码创建一个矩形，矩形刚好覆盖B2:F5区域。 http://www.exceltip.net/thread-48416-1-1.html 讲师：罗刚君 2013-7-10 《来吧！带你玩转 Excel VBA》(双色版)附带近500个VBA思考练习题 http://product.dangdang.com/product.aspx?product_id=23278575 第 14页 总 14页