Excel VBA入门（五）Excel对象操作

2023-07-28 17:54:22

本章是本系列教程的重点。但我觉得应该不是难点。从第零章开始到学完本章，应该可以把VBA用于实战中了。

Excel对象主要有4个：

工作薄 Workbook
工作表 Worksheet
单元格区域 Range
单元格 Cell

这里我只讲后面3个，不讲工作薄。原因有2点：

第零章里面讲过，工作薄其实就是一个Excel文件。我不建议直接操作Excel文件。因为文件属性被更改的机率高。比如修改了文件名，或者文件被移动到其它地方去了，这样的话，写死的VBA代码就不管用了。这是主要原因

学完本章，或者后面的章节，应该可以自己寻找到如何操作Excel工作薄的方法

在多数情况下，如果需要操作多个工作薄中的数据，建议把这些工作薄里面的表复制到一个工作薄中进行操作。这样会方便很多。

1. 操作工作表

其实对工作表的操作，更多是对其引用。当一个工作薄中有多个工作表而需要用到不同表中的数据时，就需要分别引用不同的工作表。

引用工作表，有两种方式：通过表名引用、通过表顺序引用

1.1 按表名引用

顾名思义，表名引用即通过工作表的名字来引用相应的工作表。除了可以直接在Excel中看到工作表的名字外，也可以在VBE中左侧的工程视图里看到当前工作薄中包含有哪些工作表。

如上图红框所示，括号里面的即为表名。在VBA中可如下分别引用这3个表：

Sub test()

    Dim sht_slea As Worksheet

    Dim sht_result As Worksheet

    Dim sht_para As Worksheet

    Set sht_slea = Worksheets("SLEA")

    Set sht_result = Worksheets("Check_Result")

    Set sht_para = Worksheets("Parameter")

End Sub

如上，用Dim 变量名 As Worksheet的格式来定义一个工作表对象。用Set 变量名＝ Worksheets("表名")的格式来把工作表对象赋值给指定的变量。然后就可以用这个变量来引用或操作对应工作表中的对象和数据了。

1.2 按表顺序引用

顺序引用，即按工作表出现在工作薄中的顺序从左到右，依次用1、2、3.……来引用。格式和以表名引用一样：

Sub test()

    Dim sht_slea As Worksheet

    Dim sht_result As Worksheet

    Dim sht_para As Worksheet

    Set sht_slea = Worksheets(2)

    Set sht_result = Worksheets(1)

    Set sht_para = Worksheets(3)

End Sub

这里要提出的是，在VBE工程窗口中看到的自上而下的表顺序并不是在VBA中引用的顺序。这个顺序是以工作表在工作薄中从左到右的顺序为准。因此上例代码是基于如下顺序的：

这也意味着，如果被人为地有意或无意地拖动这些表而改变了它们的顺序，那么以这种方式引用工作表将得不到预想中的结果

2. 操作单元格区域

单元格区域，即Range对象。应该是在Excel VBA中用得最多的对象。Range对象是Worksheet对象的一个子集。所以通常通过worksheet_object.Range()的方式来引用。

单元格区域，可以是单个单元格，也可以是多个连续的单元格和多个不连续的单元格。在使用单元格区域对象前，应该先进行变量定义。把变量定义为Range对象即可：

Dim rng As Range

本节使用下图数据为例进行代码演示：

2.1 单个单元格区域的引用

在Excel中，每个单元格都是有其相应的地址的，或者叫做“名字”也可以。最常用到的，就是平时说的A1、B4、D10等。在VBA中，可以通过单元格的地址来引用单个单元格。

Sub test()

    Dim sht_slea As Worksheet

    Dim rng As Range

    Set sht_slea = Worksheets("SLEA")

    Set rng = sht_slea.Range("D2")

    Debug.Print rng

End Sub

输出：92257598

即D2单元格中的数据。这里可能会引起误会，特说明一下。仅在Range对象引用的是单个单元格时，才可以用Debug.Print或者MsgBox来输出Range对象中的内容。如果将接下来介绍的引用了多个单元格的Range对象使用Debug.Print或者MsgBox来输出，将会报错。

2.2 多个连续单元格区域的引用

这种引用则类似于用鼠标在工作表中选中特定区域（然后我们可以给这个区域加上边框，或者加上底色等操作），或者是在Excel函数中引用某个区域。如选中A1到D4，或者对D2到D4中的数值进行求和－SUM(D2:D4)。在VBA中也可以这样来引用。

Sub test()

    Dim sht_slea As Worksheet

    Dim rng As Range

    Set sht_slea = Worksheets("SLEA")

    Set rng = sht_slea.Range("A1:D4")

    rng.Interior.ColorIndex = 16

End Sub

如上代码中，先引用SLEA表，然后把这个表中A1到D4区赋值给rng对象。最后一行把这个区域标上灰底色。结果如下：

对于这样多行多列的单元格区域，通常只是用于设置其格式，很少会直接对其中每个单元格的数据进行操作的。更多的是对单行或单列中的数据进行操作。比如把上例中D1到D5的数据依次输出：

Sub test()

    Dim sht_slea As Worksheet

    Dim rng As Range

    Set sht_slea = Worksheets("SLEA")

    Set rng = sht_slea.Range("D2:D5")

    For Each Item In rng

        Debug.Print Item

    Next Item

End Sub

执行结果如下：

2.3 多个不连续单元格区域的引用

这种引用方式应该应用场景不多，我本人目前为止还没有在工作中使用过。

它的引用只需要在Range()函数中的参数里，在双引号中输入多个区域地址中间用逗号隔开即可。如以下代码可将B2到B5，D2到D5区域标上红色。

Sub test()

    Dim sht_slea As Worksheet

    Dim rng As Range

    Set sht_slea = Worksheets("SLEA")

    Set rng = sht_slea.Range("D2:D5, B2:B5")

    rng.Interior.ColorIndex = 3

End Sub

结果如下：

3. 操作单元格对象

单元格，即Cell。不过在VBA里面，这个Cell得加上个s，即Cells，然后在连带着的括号里面输入用数字表示的行号和列号，即可引用到单个单元格对象。Cells对象也是Worksheet对象的一个子集。通常通过worksheet_object.Cells()的方式来引用。

Sub test2()

    Dim sht_slea As Worksheet

    Set sht_slea = Worksheets("SLEA")

    Debug.Print sht_slea.Cells(1, 2)

End Sub

输出B1单元格（第1行，第2列）的内容：Subsector

所以Cells()的第1个参数是行号，第2个参数是列号。都用数字表示。在上例中，使用Cells和使用Range好像没什么区别，但是在进行数据处理时，我们经常需要动态地把数据读或写入一个单元格中，这时候，用数字表示位置的Cells对象，再结合For循环，操作起来就很方便了。

如以下代码可以把A1到D5中所有单元格的内容分别输出：

Sub test2()

    Dim sht_slea As Worksheet

    Set sht_slea = Worksheets("SLEA")

    For r = 1 To 5

        For c = 1 To 4

            Debug.Print sht_slea.Cells(r, c)

        Next

    Next

End Sub

简单来说，Range对象便于把单元格区域作为一个整体来引用或操作，而Cells对象则方便于对每一个单元格分别进行操作。

番外篇

1. 理解Range("B2:B4, D2:D4")和Range("B2:B4", "D2:D4")的区别

先看清楚，上面两种格式

一个是把两个区域放在一个双引号里面，用逗号隔开
另一个是把两个区域分别放在双引号里面，用逗号隔开

前者是分别引用B2:B4和D2:D4这两个区域，而后者则表示引用的是从B2:B4开始到D2:D4结束为止的这一整个连续的区域。所以后者其实是等价于Range("B2:D4")。

所以虽然使用后者的方式来使用Range也不会报错，但其实通常并不会这么使用

2. 结合Cells对象的Range

因为Cells对象接受数字来表示行和列，而在Excel中，如果有两个行列对，就可以表示一个单元格区域了。例如Range("B2:D4")也可以用Range(Cells(2, 2), Cells(4, 4))来表示。这种方式有时候很有用，如需要根据条件来判断区域的开始和结束位置时，它就派上用场了。

3. 父对象的省略

其实前面提到过的Worksheet对象，它是有父对象的。其父对象为Workbook对象，而Workbook对象的父对象是*对象Application。Range的父对象是Worksheet对象，Cells对象的父对象也是Worksheet对象。所以在给这些对象赋值时，标准的写法应当要把父对象给写上，如：

Sub test3()

    Dim sht_slea As Worksheet

    Dim titl_rng As Range

    Dim data_rng As Range

    Set sht_slea = Application.ThisWorkbook.Worksheets("SLEA")

    Set title_rng = sht_slea.Range("A1:D1")

    Set data_rng = sht_slea.Range(sht_slea.Cells(2, 1), sht_slea.Cells(4, 4))

End Sub

但是如果VBA中的代码涉及到的对象都位于一个工作表中，而这个工作表当前是激活状态，则这些父对象是可以省略的。默认就是当前（激活的）工作表。所以当SLEA工作表激活时，上述代码和下面的是等价的：

Sub test4()

    Dim sht_slea As Worksheet

    Dim titl_rng As Range

    Dim data_rng As Range

    Set sht_slea = Worksheets("SLEA")

    Set title_rng = sht_slea.Range("A1:D1")

    Set data_rng = sht_slea.Range(Cells(2, 1), Cells(4, 4))

End Sub

我个人建议（或者说是我个人习惯）在引用Range对象时，Worksheet对象不要省略

在单独引用Cells对象时，Worksheet对象也不要省略

具体哪里省略哪里不省略，就得看个人习惯和应用场景了。并没有什么固定的规律可循。

本系列教程其它文章

Excel VBA 入门（零）

Excel VBA 入门（一）数据类型

 Excel VBA 入门（二）数组和字典

 Excel VBA 入门（三）流程控制1－条件选择

 Excel VBA 入门（四）流程控制2－循环控制

Excel VBA 入门（五）Excel对象操作

Excel VBA 入门（六）过程和函数

 Excel VBA 入门（七）注释、宏按钮及错误处理

 Excel VBA 入门（八）单元格边框

 Excel VBA 入门（九）操作工作薄

 Excel VBA 入门（十）用户窗体开发

码农公寓