Excel VBA入門（五）Excel對象操作

刮骨劍 2018-12-26

展開全文

本章是本系列教程的重點。但我覺得應該不是難點。從第零章開始到學完本章，應該可以把VBA用于實戰(zhàn)中了。

Excel對象主要有4個：

工作薄 Workbook
工作表 Worksheet
單元格區(qū)域 Range
單元格 Cell

這里我只講后面3個，不講工作薄。原因有2點：

第零章里面講過，工作薄其實就是一個Excel文件。我不建議直接操作Excel文件。因為文件屬性被更改的機率高。比如修改了文件名，或者文件被移動到其它地方去了，這樣的話，寫死的VBA代碼就不管用了。這是主要原因
學完本章，或者后面的章節(jié)，應該可以自己尋找到如何操作Excel工作薄的方法
在多數(shù)情況下，如果需要操作多個工作薄中的數(shù)據(jù)，建議把這些工作薄里面的表復制到一個工作薄中進行操作。這樣會方便很多。

1. 操作工作表

其實對工作表的操作，更多是對其引用。當一個工作薄中有多個工作表而需要用到不同表中的數(shù)據(jù)時，就需要分別引用不同的工作表。

引用工作表，有兩種方式：通過表名引用、通過表順序引用

1.1 按表名引用

顧名思義，表名引用即通過工作表的名字來引用相應的工作表。除了可以直接在Excel中看到工作表的名字外，也可以在VBE中左側(cè)的工程視圖里看到當前工作薄中包含有哪些工作表。

如上圖紅框所示，括號里面的即為表名。在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("表名")的格式來把工作表對象賦值給指定的變量。然后就可以用這個變量來引用或操作對應工作表中的對象和數(shù)據(jù)了。

1.2 按表順序引用

順序引用，即按工作表出現(xiàn)在工作薄中的順序從左到右，依次用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中引用的順序。這個順序是以工作表在工作薄中從左到右的順序為準。因此上例代碼是基于如下順序的：

這也意味著，如果被人為地有意或無意地拖動這些表而改變了它們的順序，那么以這種方式引用工作表將得不到預想中的結(jié)果

2. 操作單元格區(qū)域

單元格區(qū)域，即Range對象。應該是在Excel VBA中用得最多的對象。Range對象是Worksheet對象的一個子集。所以通常通過worksheet_object.Range()的方式來引用。

單元格區(qū)域，可以是單個單元格，也可以是多個連續(xù)的單元格和多個不連續(xù)的單元格。在使用單元格區(qū)域?qū)ο笄?，應該先進行變量定義。把變量定義為Range對象即可：

Dim rng As Range

本節(jié)使用下圖數(shù)據(jù)為例進行代碼演示：

2.1 單個單元格區(qū)域的引用

在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單元格中的數(shù)據(jù)。這里可能會引起誤會，特說明一下。僅在Range對象引用的是單個單元格時，才可以用Debug.Print或者MsgBox來輸出Range對象中的內(nèi)容。如果將接下來介紹的引用了多個單元格的Range對象使用Debug.Print或者MsgBox來輸出，將會報錯。

2.2 多個連續(xù)單元格區(qū)域的引用

這種引用則類似于用鼠標在工作表中選中特定區(qū)域（然后我們可以給這個區(qū)域加上邊框，或者加上底色等操作），或者是在Excel函數(shù)中引用某個區(qū)域。如選中A1到D4，或者對D2到D4中的數(shù)值進行求和－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區(qū)賦值給rng對象。最后一行把這個區(qū)域標上灰底色。結(jié)果如下：

對于這樣多行多列的單元格區(qū)域，通常只是用于設置其格式，很少會直接對其中每個單元格的數(shù)據(jù)進行操作的。更多的是對單行或單列中的數(shù)據(jù)進行操作。比如把上例中D1到D5的數(shù)據(jù)依次輸出：

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

執(zhí)行結(jié)果如下：

2.3 多個不連續(xù)單元格區(qū)域的引用

這種引用方式應該應用場景不多，我本人目前為止還沒有在工作中使用過。

它的引用只需要在Range()函數(shù)中的參數(shù)里，在雙引號中輸入多個區(qū)域地址中間用逗號隔開即可。如以下代碼可將B2到B5，D2到D5區(qū)域標上紅色。

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

結(jié)果如下：

3. 操作單元格對象

單元格，即Cell。不過在VBA里面，這個Cell得加上個s，即Cells，然后在連帶著的括號里面輸入用數(shù)字表示的行號和列號，即可引用到單個單元格對象。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列）的內(nèi)容：Subsector

所以Cells()的第1個參數(shù)是行號，第2個參數(shù)是列號。都用數(shù)字表示。在上例中，使用Cells和使用Range好像沒什么區(qū)別，但是在進行數(shù)據(jù)處理時，我們經(jīng)常需要動態(tài)地把數(shù)據(jù)讀或?qū)懭胍粋€單元格中，這時候，用數(shù)字表示位置的Cells對象，再結(jié)合For循環(huán)，操作起來就很方便了。

如以下代碼可以把A1到D5中所有單元格的內(nèi)容分別輸出：

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對象便于把單元格區(qū)域作為一個整體來引用或操作，而Cells對象則方便于對每一個單元格分別進行操作。

番外篇

1. 理解Range("B2:B4, D2:D4")和Range("B2:B4", "D2:D4")的區(qū)別

先看清楚，上面兩種格式

一個是把兩個區(qū)域放在一個雙引號里面，用逗號隔開
另一個是把兩個區(qū)域分別放在雙引號里面，用逗號隔開

前者是分別引用B2:B4和D2:D4這兩個區(qū)域，而后者則表示引用的是從B2:B4開始到D2:D4結(jié)束為止的這一整個連續(xù)的區(qū)域。所以后者其實是等價于Range("B2:D4")。

所以雖然使用后者的方式來使用Range也不會報錯，但其實通常并不會這么使用

2. 結(jié)合Cells對象的Range

因為Cells對象接受數(shù)字來表示行和列，而在Excel中，如果有兩個行列對，就可以表示一個單元格區(qū)域了。例如Range("B2:D4")也可以用Range(Cells(2, 2), Cells(4, 4))來表示。這種方式有時候很有用，如需要根據(jù)條件來判斷區(qū)域的開始和結(jié)束位置時，它就派上用場了。

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中的代碼涉及到的對象都位于一個工作表中，而這個工作表當前是激活狀態(tài)，則這些父對象是可以省略的。默認就是當前（激活的）工作表。所以當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對象也不要省略

具體哪里省略哪里不省略，就得看個人習慣和應用場景了。并沒有什么固定的規(guī)律可循。

本系列教程其它文章

Excel VBA 入門（零）
Excel VBA入門（一）數(shù)據(jù)類型
 Excel VBA入門（二）數(shù)組和字典
 Excel VBA入門（三）流程控制1－條件選擇
 Excel VBA入門（四）流程控制2－循環(huán)控制
Excel VBA入門（五）Excel對象操作
Excel VBA入門（六）過程和函數(shù)
Excel VBA入門（七）注釋、宏按鈕及錯誤處理
 Excel VBA入門（八）單元格邊框
 Excel VBA入門（九）操作工作薄