Excel VBA(九)表格对象操作

本章是本系列教程的重点。但我觉得应该不是难点。从第零章开始到学完本章,应该可以把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对象,它是有父对象的。其父对象为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.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对象也不要省略

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

发表评论