VBA基础

关于对象

第七篇、Excel VBA对象类型之：WorkSheet(工作表)对象

  接上一篇：如何操控单元格对象？①找到符合要求的单元格、②改变它的状态、③改变它的特性，让它服从我们的需求。就是通篇我们围绕的三个重点：

  • ①单元格对象的引用
  • ②单元格对象的方法
  • ③单元格对象的属性

  沿着Excel对象的层级结构图往上，Range单元格的父对象是：Worksheet(单个工作表)，Worksheet属于Worksheets (所有工作表的集合)。

  本篇幅我们了解Worksheet工作表 和 Worksheets 工作表集合，来看这个对象的引用和操作，最后在结合Range单元格对象，看Worksheet + Range能给我们的日常工作带来多大的便利！

对象层级结构

  通过Excel对象的层级结构图，看到Excel对象模型是一个树状结构，从顶层的 Application 开始，逐级向下延伸：

复制成功!
1

Application
│
├─ Workbooks (所有打开的工作簿集合)
│   │
│   └─ Workbook (单个工作簿)
│       │
│       ├─ Worksheets (所有工作表集合) 'Worksheet父对象
│       │   │
│       │   └─ Worksheet (单个工作表) 'Range的父对象
│       │       │
│       │       ├─ Range (单元格及区域)'Worksheet子对象
│       │       │
│       │       ├─ ChartObjects (嵌入的图表集合)
│       │       │   └─ ChartObject → Chart (嵌入的图表)
│       │       │
│       │       └─ Shapes (形状/图形集合，如文本框、图片等)
│       │           └─ Shape (单个形状)
│       │
│       └─ Charts (独立的图表工作表集合)
│           └─ Chart (独立的图表工作表)
│
└─ Windows (所有窗口集合)

  上一篇了解🔗Range(单元格)对象，我们往上一级了解，这篇来看它的父对象Worksheet是如何工作的。

WorkSheet工作表对象

  我们需要把WorkSheeets和WorkSheet放到一起来了解。WorkSheeets是WorkSheet的集合，把它当做WorkSheet的父辈就完事了，一个父亲可能有一个孩子WorkSheets(1)，也可能有多个孩子WorkSheets(1)、WorkSheets(2)、WorkSheets(3).....。

  Worksheets集合，常常用于批量操作多个工作表，你可以遍历它、添加表、删除表、复制表、隐藏表等等。

  Worksheet是其中的一个成员(一个孩子），常常用于操作工作表的具体内容，你可以读写表格里的单元格数据、修改工作表的名称等等。

  它俩一个是处理多个工作表的批量操作，一个是处理单个工作表的具体内容。

1-工作表对象的引用:

  一样的：如果我们要操控工作表，首先也是要先选定(引用)它。同样作为的Excel VBA对象，工作表对象的引用比单元格对象简单太多了！单元格对象引用方法多的需要列出表来了解，直接引用、扩展引用、相对定位.....，工作表对象就两个：使用工作表名称或使用工作表的索引号。(可能还有一个:引用活动工作表-ActiveSheet)。

本节视频：
通过工作表名称引用

  就是直接使用工作表的名称(字符串)，我们每一个工作薄里都有多个(至少一个)工作表，每添加一个工作表，默认的名称是Sheet + 累计增加的数量，这个就是默认的工作表名称，我们引用它时，直接用：WorkSheets("Sheet1")即可，就表示引用了这个工作表。当我们自定义工作表名后，引用自定义的工作表名，也表示引用了这个工作表：WorkSheets("新名字")，就这么简单。

示例：
  • 通过名称引用：
复制成功!
1

Sub 工作表引用_通过名称引用()

   Worksheets("Sheet1").Range("B1").Value = "Hello,工作表"
   Worksheets("表2").Range("B1").Value = "Hello,工作表"

End Sub
通过索引(位置)引用

  就是通过工作表在集合(当前工作薄中的所有Worksheet)中的位置(索引)引用，索引是从1开始的。引用方式：Worksheets(1)。

  这种引用方式有个缺点，索引程序会随着移动工作表而改变，不够稳定，比如原来名称为Sheet3工作表在第三个位置，索引值是3：Worksheets(3)可以引用它，移动到第四个位置后，索引值变成4。这时Worksheets(3)不能在引用这个工作表，而是Worksheets(4)。

示例
  • 通过索引引用：
复制成功!
1

Sub 工作表引用_通过索引引用()

   Worksheets(1).Range("C1").Value = "Hello,工作表索引"
   ’尝试改变第二个工作表的位置，在引用并写入内容试试
   Worksheets(2).Range("C1").Value = "Hello,工作表索引"

End Sub
还有一种ActiveSheet引用，表示选中当前用户选中的工作表

   ActiveSheet.Range("A1").value = 1，即表示当前的工作薄中那个工作表处于活动状态，就在这个工作表的A1单元格写入1。如果切换了活动工作表，代码可能会操作错误的对象。

2-工作表对象的操作（方法和属性）:

  单元格对象有自己的方法和属性，工作表对象也是！方法也是改变对象的状态，属性用来反映对象的特性。

  和介绍Range单元格对象不同，既然已经了解了对象方法和属性用来做什么，在介绍新的对象时，我们先把该对象的一些方法和属性列出来，在通过示例来了解他们是如何工作的！

工作表对象的方法
常用方法列表：
点击放大的图片
工作表对象的属性

  介绍Range单元格对象的属性时，提到过有些属性共有的(有相同的属性名可能含义和用法不同)，也列举了一些共有、独有的属性名和用法。

  这里我们仍把属于工作表对象常用的属性依次介绍下，不在说明它的共有的还是独有的，只知道它能用在工作表对象上表现它的特征就可以了。

常用属性列表：
点击放大的图片

  关于Range、Cells、Usedrange等，是不是觉得很熟悉？，它们其实也就是上一节提到的“单元格的引用”，只不过我们是引用时一直用的是“本表”，也就是当前的、活动的WorkSheet工作表，可以用Range("A1")、Cells(1,1)这样直接引用。当它具体指向某个工作表时，具体写法就会是：WorkSheets("表1").Range("A1")、WorkSheets("表1").Cells(1,1)，指定工作表.属性。

本节视频：
  示例：
  • 工作表对象的方法和属性：
复制成功!
1

Sub 工作对象的方法和属性()

    'Add方法添加新工作表，Name设置并获取工作表名
    Worksheets.Add After:=Worksheets(1)                      '添加新工作表
    Worksheets(2).Range("A1").Value = Worksheets(2).Name    '新工作表的A1单元格获取工作表名(默认)
    Worksheets(2).Name = "新工作表1"                        '设置新的工作表名
    Worksheets("新工作表1").Range("B1").Value = Worksheets("新工作表1").Name '新工作表的B1单元格获取新工作表名

    Dim new_sht As Worksheet
    Set new_sht = Worksheets.Add(After:=Worksheets(1))   'set 将添加的新工作表赋值给一个变量，然后重命名
    new_sht.Name = "新工作表2"
    new_sht.Range("A1").Value = new_sht.Name

    '删除工作表
    Worksheets(2).Delete
    Worksheets("新工作表1").Delete

    '复制工作表
    Worksheets(1).Copy After:=Worksheets(3)
    Worksheets("Sheet1").Copy Before:=Worksheets(3)

    '移动工作表位置
    Worksheets(1).Move After:=Worksheets(3)
    Worksheets("Sheet1").Move Before:=Worksheets(3)

    '激活工作表
    Worksheets(1).Activate
    Worksheets("Sheet1").Activate

    'Count属性，工作表的数量
    MsgBox "当前工作薄中有： " & Worksheets.Count & "个工作表"

    'Visible属性 工作表可见性
    Debug.Print Worksheets("Sheet1").Visible  '立即窗口输出Sheet1工作表的可见性
    Worksheets("Sheet1").Visible = False      '设置Sheet1工作表不可见(隐藏)
    Debug.Print Worksheets("Sheet1").Visible
    Worksheets("Sheet1").Visible = True

End Sub

3-了解几个常用的工作表事件

  首先要确定下工作表事件的基本概念，从模块中存放代码说起”！

  迄今为止，我们的代码一直被放置在“模块”中“代码窗口”的，因为我们默认代码就是用来“操控Excel各种对象，按照我们的设想完成自动化处理工作。”，代码不与任何的一个工作表特定关联，它是可以用来处理所有的工作表而不是某个特定的工作表。所以我们把它放在到“模块”中，“模块”是一个独立的代码文件。

  工作表WorkSheet也可以用来存放代码，它同样也有“代码窗口”，只不过代码仅仅适用于存放的工作表”🔗代码的作用域和生命周期，它需要和工作表里的其他元素紧密集成来完成某个任务。

  工作表事件，就是存放在工作表中的代码，它仅仅适用于存放的工作表，还有一个特性是：它伴随着工作表内的指定操作而自动触发程序。比如修改单元格内容时、激活当前工作表时或双击单元格时，就会自动触发代码(代码内容由开发者自己决定)。这对于增强工作表交互性和自动化功能特别有帮助。

本节视频：
示例1：
  • 比如我们选定某一个单元格，希望整行和整列都高亮显示，用一个工作表事件就再也合适不过。只有在某个工作表里，选中单元格时才会出现这种效果！
复制成功!
1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Cells.Interior.Color = RGB(255, 255, 255)             '底色为白色
    Target.EntireColumn.Interior.Color = RGB(255, 255, 0) '选中单元格的整列设置底色
    Target.EntireRow.Interior.Color = RGB(255, 255, 0)    '选中单元格的整行设置底色


End Sub
  • 以上代码就是一个工作表的SelectionChange事件，当选择的单元格发生变化时触发执行的代码，代码段是“选中的单元格整行和整列显示黄色 RGB(255, 255, 0)”。

  工作表事件有很多，打开进入Visual Basic编辑器，双击工程资源管理器中的一个工作表对象，在代码窗口顶端选择“WorkSheet"，就进入了工作表事件，有很多种工作表事件，我们结合示例简单介绍几个常用的，知道在哪里？有什么作用？很重要！，至于如何用？，逢山开路、遇水架桥、查找资料！！

工作表事件选择：
点击放大的图片

  几个常用的工作表事件：Change事件、SelectionChange事件、Activate事件：

  • SelectionChange事件，当选择的单元格发生变化时触发执行的代码，我们已经通过示例介绍过，举了“动态提示、高亮显示”的例子。
  • Change事件：单元格内容修改时触发代码，
  • Activate事件：工作表被激活时触发代码。

  假如，Sheets("表2")工作表的B3:H8区域存放着重要信息,每次修改都需要在A1单元格记录最后一次修改时间，A2单元格记录最后一次的工作表被激活时间(查看该工作表的时间)，如何利用工作表事件？

示例2：
复制成功!
1

Private Sub Worksheet_Change(ByVal Target As Range)
    '记录单元格B3:H8区域更内容的时间
    If Not Intersect(Target, Range("B3:H8")) Is Nothing Then
        Application.EnableAnimations = False '防止递归触发
        Range("A1").Value = "最后修改时间: " & Now
        Application.EnableAnimations = True
    End If
End Sub

Private Sub Worksheet_Activate()
    '当前工作表被激活时，在A2单元格写入激活时间
    Range("A2").Value = "最后查看时间: " & Now
End Sub
  • Intersect两个单元格区域的交叉区域；
  • Intersect(Target, Range("B3:H8")) Is Nothing两个单元格区域的相交区域为空；
  • 整行的释义是：如果选定操作的单元格范围和Rnage("B3:H8")单元格区域有相交(不为空)。

4-工作表和数据处理(Worksheet + Range对象)

  截止到现在我们已经了解过Excel VBA对象类型里的工作表对象、和单元格对象，这是最基础的也是尤为重要的两个，它们两个组合在一起，起码能解决我们办公自动化需求里的66%的问题！

  可能您对这些个还没概念，觉得平平无奇，看了内容看了也就看了，但是它能用来做什么？怎么就能解决我的问题了？除非是你日常的工作还不需要用大量的时间去处理固定的表格，或是还不能按照设想把代码堆积起来解决问题！

  这里通过我的经历、和最开始遇到的一个需要VB解决的问题作为示例，通过这个就能解释为什么“VB是编程零基础也能快速掌握，了解小部分知识点就能对工作带来很多便利， 拯救天选打工人的神器！。”

我的经历：合并工作表
  • 2006年—2010年毕业最初的四年在一个鞋类的工厂做“生管”，就是生产计划和跟单，需要对从外贸接单到出货的整个过程制定详细的计划表，大致就是从物料采购、样本开发制作、确认、试产、排产、包装到交付，制定一个详细的计划表并跟进。这个期间逐渐熟悉了Excel表格，比如函数运用、数据透视、常规图表制作。以为电子表格的功能也就这些了，没有解决不了的问题，后来才发现不是没有问题，而是没有遇到更好的问题。
  • 2011年跳槽至一家代工转自创品牌的服装企业做商品管理，工作内容包括：1、指导和服务加盟商做订销指引，2、负责集团直营店的商品流转(订货、铺货、促销、换季调整等全流程的货品管理)。第一次秋冬产品订货会，认知被彻底打破了。因为团队每天晚上需要复盘订货目标的完成进度，从客户的维度需要细分到每个加盟店、每个区域的完成量，从产品维度需要汇总每个品类、款式的订货量和尺码分布。这关联着前端的面料采购，面料采购单的早晚意味着成本和交期，毕竟同时段的可不止一个成衣公司在面对着面料供应商，这就需要每晚汇总所有的订货信息来做透视分析。
  • 信息采集方式还算好，一个终端客户一个手持终端采集PDI设备。数据质量和完整度不用担心了，问题恰恰就出现在汇总上。当时也是第一次通过这种方式来采集信息（相比其他发展的早的品牌就太落伍了），PDI设备和后端的分析功能在当时是拆开来卖钱的这个谁又能懂？，公司就只用了设备和统计，就是记录和汇出，每晚结束后，需要要导出一个包含张三、李四、王五、赵六...等等多个工作表的工作薄，一个表就代表一个手持终端(加盟店)，要知道先要汇总才能分析。这个汇总工作可太麻烦了！当时的新团队，也没人知道如何快速把80+张表的工作表汇总到一起。我就用了最原始的方式：建一个名为“汇总”的表、手动复制表、粘贴到汇总表里、第一列填充表名(加盟店名)、80+次重复，这个过程是真累、时间是真紧张。结束后自己复盘工作，唯独觉得这个过程不美丽，这么重复的占用大量时间的工作不能说毫无意义，是十分枯燥！
  • 网上查找解决办法，印象最深的排名第一的解决办法是别人写的一个工具《逸凡工作簿合并助手》，惊为天人！界面友好、功能强大、使用了窗体和控件供多种方式的合并，但是收费，用自己的钱解决公司的问题不是我上班的原则，且当时只能解决这一个使用频次很低的问题！
  • 既然了解了就想去尝试，如果也能解决日常工作中的高频问题就好了。“VBA代码合并工作表”就是手搓出的第一个满足自己个性化需求的小段代码，过程很痛苦。一本厚厚的《Visual Basic编程基础》从头看到尾，似懂非懂，知识点太多！转念想：“我就想解决现实的这一个小问题，先解决它就可以了，其他的管它呢，又不准备做是专业开发者和老师。”，这就又从头开始，看到Worksheet和Range对象就有了点眉目了，就觉得似乎找到办法了：就是操作工作表和单元格嘛！

  背景介绍就到这，从此就开始进入了遇到问题找解决方法的过程！

本节视频：
  当时遇到的问题如图所示：如何把这80个拥有相同格式、标题行的数据汇总起来，还要知道它来自哪个表？ 点击放大的图片   思路：
  • 1、在第一个工作表之前，建一个名为“汇总”的工作表；
  • 2、复制Sheet1，在“汇总”表的B列单元格开始粘贴；
  • 3、在“汇总”表的第一列A列，从首个单元格开始到最后一行，写上Sheet1(来源表的名称)
  • 4、复制Sheet2工作表的内容，接着"汇总"表的最后一行的下一行开始粘贴，然后同样写上Shee2的名称
  • 5、重复 2 -3步骤，直到汇总完成。

  开始堆代码，第一步手动新建“汇总”工作表，刚开始代码宁可少写，就担心出错！

复制成功!
1

Sub VBA代码合并工作表()
    Dim sht As Worksheet
    Dim end_row As Long, step_row As Long

    '遍历这个工作薄的每一个工作表
    For Each sht In Worksheets
        '先判断“汇总”工作表的已使用区域的最后一行是哪里，增加一行用来存放下复制的工作表
        end_row = Sheets("汇总").UsedRange.Rows.Count + 1
        '复制工作表，粘贴到“汇总”里的B列，位置就是已使用区域的下一行
        sht.UsedRange.Copy Destination:=Sheets("汇总").Range("B" & end_row)
        '计算出复制的工作表的行数
        step_row = sht.UsedRange.Rows.Count
        '在“汇总表”的A列，从最后一行开始，到复制的行数为止，写入复制的工作表名
        Sheets("汇总").Range("A" & end_row & ":A" & end_row + step_row).Value = sht.Name

    Next

End Sub

  以上不包括定义和类型注释语句，只有6行，一个循环、一个计算、一个复制粘贴就搞定了~~。结果如下图(至于为什么首列还有多出2行，这个就不重要了)，运行不出错还能返回正确的结果，就是有用的代码，剩余的用表格知识:筛选+剔除就OK了。这就是我的第一个解决实际问题的程序，简单吧?~

点击放大的图片   不复杂的工作经历告诉我，工作表对象+单元格对象组合在一起，起码能解决办公自动化需求里的66%的问题！

下一篇：了解Excel VBA的对象之：Workbook(工作簿)对象

   通过不同用方式的组合运用，找到单元格并选定他们，通过方法(操作)和属性(设置)，让单元根据我们的要求做出相应的改变。这就是操控单元格对象！

   通过工作表名称或索引(位置)引用工作表，用它的方法和属性，让工作表根据我们的要求做出相应的改变，这就是操控工作表对象！

   工作表对象+单元格对象组合已然能解决很多的、在一个工作薄内的问题，如果跨工作薄呢？延续本篇的案例：如果每个小组是一个工作簿，有10个小组管理这80+门店，就意味着有10个工作簿内、合计包含80个工作表，如何汇总呢？

   沿着“对象的层级结构”在往上一级了解，就是WorkBook(工作薄)对象！

请开发者喝杯咖啡 请开发者喝杯咖啡！