囉哩囉嗦地講透VBA中引用單元格區域的18個有用方法--Range屬性

fanjy 2022-01-07 19:03:57 阅读数:365

vba 引用 有用 方法 range

標簽:VBA,Range對象

引言:本文學習整理自powerspreadsheets.com,講解得很細致,一些知識點反複强調,對於熟悉VBA的朋友來說,感覺有點囉嗦,但是對於VBA初學者來說,對快速掌握Range對象的引用,卻很有好處。

使用Excel時,我們花時間最多的就是處理單元格。因此,如果想使用VBA提高Excel工作效率,必須學習如何使用VBA處理單元格。

本文講解如何使用VBA處理單元格,解釋使用Range對象所需了解的所有基本細節。Range對象是VBA中用於引用和處理單元格的對象,是ExcelVBA中最常用的對象之一。VBA允許用戶以多種不同的方式引用單元格區域。

什麼是Range對象

Range對象代錶工作錶中的單元格區域,這意味著可以使用Range對象來引用:

  • 單個單元格
  • 一行或一列單元格。
  • 選擇的單元格,無論它們是否連續
  • 三維單元格區域

從上面可以看出,Range對象的大小可能會有很大差异,你可以只引用單個單元格,也可以引用Excel工作錶中的所有單元格。

盡管在引用特定Excel工作錶中的單元格時具有這種靈活性,但Range對象還是有一些限制。最相關的是,一次只能使用它引用單個Excel工作錶,要引用不同工作錶中的單元格區域,必須對每個工作錶使用單獨的引用。

如何引用Range對象

要掌握Range對象,首先要學習的是如何引用它,隨後的各節解釋了需要了解的編寫合適引用的最相關的規則。

前幾節介紹引用Range對象的最基本方式:Range屬性。這也是宏錄制器通常引用Range對象的方式。

接著,介紹創建對象引用的更多方法,例如使用Cells或Offset屬性。

然而,這些並不是引用Range對象的唯一方法,還有一些更高級的方法,例如使用Application.Union方法,這裏沒有介紹。

基於上下文和特定的需要,確定引用Range對象的最佳方法。

引用Range對象以及對象限定符介紹

為了能够正確使用範圍對象,必須了解如何使用Range對象引用的兩個主要部分:

1.對象限定符。這是創建對象引用的一般規則。

2.用於返回Range對象的相關屬性或方法。這是適用於引用Range對象的特定規則,這也是本文所要講解的主要內容。

完全限定的VBA對象引用介紹

對象能够作為其他對象的容器。

在基本層級上,當引用特定對象時,可以通過引用其所有父對象來告訴Excel該對象是什麼。換句話說,將遍曆Excel的VBA對象層次結構。

可以使用點(.)運算符通過Excel的對象層次結構連接每個不同層級的對象。

這些類型的特定引用稱為完全限定引用。那麼,Range對象的完全限定引用如何錶示?

Excel VBA對象層次結構頂部的對象是Application。Application本身包含其他對象。

Range對象包含在Worksheet對象中。更准確地說:Worksheet對象有一個Range屬性;Worksheet.Range屬性返回Range對象。

Worksheets的父對象是Workbook對象,Workbook對象本身包含在Application對象中。

這些不同對象之間的層次關系如下:

因此,用於引用Range對象的基本結構如下:

Application.Workbooks.Worksheets.Range

注意到,上述基本結構中的一些內容是模糊的。特別是,這裏並沒有指定所引用的特定Excel工作簿或工作錶。為此,必須理解下面的內容。

從集合中引用對象

在VBA中,對象集合是一組相關的對象。

用於創建對Range對象的完全限定引用的Workbooks和Worksheets都是集合的示例。有兩種基本方法可以引用集合中的特定對象:

1.使用對象名稱。這種情形的語法是:集合名稱(“對象名稱”)。

2.使用索引。其語法是:集合名稱(索引號)

注意,第一種方法必須在括號內使用雙引號(””),而第二種方法不必在索引編號周圍加引號。

假設想要處理工作簿“Book1.xlsm”中名為“Sheet1”的工作錶,兩種方法的引用如下:

1.使用對象名稱:

Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).Range

2.使用索引號:

Application.Workbooks(1).Worksheets(1).Range

簡化完全限定對象引用

Excel的VBA對象模型包含一些默認對象。依賴這些默認的對象,可以簡化完全限定的對象引用。

有兩種主要類型的默認對象可用於簡化完全限定對象引用:

1.Application對象

2.活動的工作簿和工作錶對象

VBA始終假定處理的是Excel本身。因此,可以通過省略Application簡化完全限定對象引用。例如,在上述示例的情况下,簡化引用如下:

Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).Range

Workbooks(1).Worksheets(1).Range

此外,VBA假定正在處理當前活動工作簿和活動工作錶。這種簡化比前一種更複雜,因為它依賴於正確識別活動工作簿和工作錶,這比識別Excel應用程序本身稍微困難一些。

然而,也可以使用這兩個默認對象創建更簡單的VBA對象引用。繼續上面相同的例子,可寫為:

Range

使用Range屬性引用Range對象

在上面的示例中,我們使用了Range代錶要引用的單元格區域,在實際代碼中,需要指定想要處理的特定單元格區域。

引用Range對象的最基本方法可能是使用Range屬性。應用此屬性時,將返回一個錶示一個或多個單元格的Range對象。

Range屬性有兩個版本:Worksheet.Range屬性和Range.Range屬性,兩者背後的邏輯基本相同,主要區別在於它們應用於哪個對象:

1.使用Worksheet.Range屬性時,Range屬性應用於工作錶。

2.使用Range.Range屬性時,Range應用於單元格區域。

換句話說,Range屬性應用於2個不同類型的對象:Worksheet對象和Range對象。

在上文的介紹中,Range對象的父對象是Worksheet對象,換句話說,Range屬性應用於Worksheet對象。然而,也可以將Range屬性應用於Range對象,如果這樣,由Range屬性返回的對象會改變。

正如Microsoft所解釋的那樣,這是因為Range.Range屬性與應用該屬性的對象相關。因此,如果應用Range.Range屬性,該屬性相對於Range對象而不是Worksheet對象起作用。

這意味著可以應用Range.Range屬性,用於引用與另一個區域相關的區域。下面將舉例說明這種引用是如何工作的。

Range屬性的基本語法

引用Range對象的基本語法是:

錶達式.Range(“單元格區域”)

注意到,該語法遵循上面為其他VBA對象(如Workbooks和Worksheets)解釋的一般規則。特別是,有4個基本元素:

元素1:關鍵字“Range”。

元素2:關鍵字後面的括號。

元素3:相關單元格區域。下面會解釋了定義單元格區域的不同方法。

元素4:引號。所引用的單元格區域通常在引號(“”)內。

在這種特殊情况下,“錶達式”只是一個錶示Worksheet對象(對於Worksheet.Range屬性)或Range對象(對於Range.Range對象)的變量。

從廣義上講,通常可以使用與編寫常規Excel公式時使用的類似的方法來引用單元格區域,這意味著使用A1樣式引用。

對於初學者來說,一開始可以會感覺有點混亂,不要擔心,展示一些引用示例後,一切都會變得清晰。

可以使用兩種不同的語法來定義想要處理的單元格區域。

語法1:(“Cell1”)

這是定義相關單元格區域時必須包含的最小值。作為一般規則,使用此語法時,參數(Cell1)必須為以下任一項:

1.錶示單元格區域地址的字符串。

2.命名單元格區域的名稱

命名區域時,可以使用以下3個運算符之一:

1.冒號(:):這是用於設置數組的運算符。在引用單元格區域的上下文中,可以用來引用整列或整行、連續單元格區域或非連續單元格區域。

2.空格():這是交叉運算符。如下文圖所示,可以使用交叉操作符來引用兩個單獨區域共用的單元格。

3.逗號(,):這是聯合運算符,可用於組合多個區域。如下面的示例所示,在處理非連續單元格區域時,可以使用此運算符。

語法2:”(Cell1, Cell2)”

如果選擇使用此語法,則基本上是通過在其兩個角的命名單元格來指定相關區域:

1.“Cell1”是單元格區域左上角的單元格。

2.“Cell2”是單元格區域右下角的單元格。

然而,這種語法並不像看起來那麼嚴格。在這種情况下,參數可以包括:

1.Range對象;

2.單元格區域地址;

3.命名單元格區域名稱;

4.上述組合。

使用Worksheet.Range屬性引用單個單元格

如果要引用的Range對象是單個單元格,則語法為簡單的:

Range(“Cell”)

例如,如果要引用單個單元格A1,輸入

Range(“A1”)

假設繼續使用Book1.xlsm的Sheet1,則進一步對這個單元格創建完全限定引用:

Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).Range(“A1”)

可能已經注意到了一些非常重要的事情:沒有Cell對象這樣的東西。單元格本身不是一個對象。單元格包含在Range對象中。

也許更准確地說,單元格是一個屬性。實際上,可以使用此屬性(單元格)來引用單元格區域。上面的示例將Range屬性應用於Worksheet對象。換句話說,它是Worksheet.Range屬性的一個示例。

使用Range.Range屬性相對於另一個單元格區域引用單個單元格

假設簡單地按如下方式使用Selection對象,而不是如上所述指定完全限定引用:

Selection.Range(“A1”)

此外,假設當前選擇區域是活動工作錶的C3和D5(單元格C3、C4、C5、D3、D4和D5)之間的單元格區域。該選擇區域是一個Range對象。

由於Selection對象錶示文檔中當前選定的區域,因此上面的引用返回單元格C3。它不會返回單元格A1。

上述兩個示例引用行為不同的原因是,Range屬性的行為是相對於其應用的對象。換句話說,將Range屬性應用於Range對象時,其行為相對於該區域(更准確地說,是其左上角)。將其應用於Worksheet對象時,其行為相對於工作錶。

通過將Range屬性應用於Range對象來創建引用不是很直觀。然而,相對於其他區域引用單元格的能力有幾個優點,允許在事先不知道其地址的情况下引用單元格。

幸運的是,為了引用與區域相關的特定單元格,還有其他方法,主要是Range.Offset屬性,這將在下面介紹。

使用Worksheet.Range屬性引用整列或整行

Range對象可以由完整的行或列組成。可以按如下方式引用整行或整列:

行:Range(“行號:行號”)

列:Range(“列字母:列字母”)

例如,如果要引用特定工作錶的第一行,其語法為:

Range(“1:1”)

如果要引用第一列(列A),則鍵入:

Range(“A:A”)

假設處理Book1.xlsm中的Sheet1,則完整的限定引用如下:

Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).Range(“1:1”)

Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).Range(“A:A”)

使用Worksheet.Range屬性引用連續單元格區域

如果要引用單元格A1和B5之間的單元格區域(A1、A2、A3、A4、A5、B1、B2、B3、B4和B5),一個合適的語法是:

Range(“A1:B5”)

如果繼續使用Book1.xlsm中的Sheet1,則完全限定引用如下:

Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).Range(“A1:B5”)

然而,如果選擇應用第二種語法,描述了相關的區域,那麼合適的語法是:

Range(“A1”, “B5”)

在本例中,完全限定引用如下所示:

Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).Range(“A1”,“B5”)

使用Worksheet.Range屬性引用非連續單元格區域

Excel中用於引用一系列非連續單元格的語法與用於引用一系列連續單元格的語法非常相似,只需使用逗號(,)分隔不同的區域。因此,其基本語法是:

Range(“單元格區域1, 單元格區域#, …”)

假設想引用下列非連續單元格區域:

單元格A1至B5(A1,A2,A3,A4,A5,B1,B2,B3,B4和B5)和單元格D1至D5(D1,D2,D3,D4和D5)

輸入:

Range(“A1:B5,D1:D5”)

完全限定引用如下:

Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).Range(“A1:B5,D1:D5”)

然而,在處理一系列非連續單元時,可能需要分別處理每個不同的區域。原因是某些方法/屬性在處理此類非連續單元格區域時存在問題。

使用Worksheet.Range屬性引用兩個單元格區域的交叉區域

假設想要引用下面兩個單元格區域的交叉區域:

單元格B1至B10(B1,B2,B3,B4,B5,B6,B7,B8,B9和B10)和單元格A5至C5(A5,B5和C5)

這種情况下,合適的語法為:

Range(“B1:B10A5:C5”)

假設處理Book1.xlsm的Sheet1,則完全限定引用的構造如下:

Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).Range(“B1:B10A5:C5”)

這樣的引用返回兩個區域共用的單元格。在本例中的兩個區域共用的唯一單元格是B5。

使用Worksheet.Range屬性引用命名區域

如果引用的區域具有名稱,則其語法與引用單個單元格非常相似,只需將用於引用區域的地址替換為適當的名稱。

例如,如果想創建對名為“Excel_Example”的單元格區域的引用,其合適的語法為:

Range(“Excel_Example”)

完全限定的引用為:

Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).Range(“Excel_Example”)

記住在單元格區域名稱周圍使用引號(“”)。如果不使用引號,VBA會將其解釋為變量。

使用Worksheet.Range屬性引用合並的單元格區域

通常,處理合並單元格並不是那麼簡單,下面是使用包含合並單元格的區域時可能面臨的一些(潜在)挑戰:

1.宏的行為不同於你所期望的。

2.排序問題

引用合並單元格時要考慮的第一件事是,可以用以下2種方式之一引用它們:

1.通過引用整個合並單元格區域。

2.僅引用合並單元格區域的左上角單元格。

假設正在處理的Excel電子錶格中合並了A1到C5的單元格區域,這包括單元格A1、A2、A3、A4、A5、B1、B2、B3、B4、B5、C1、C2、C3、C4和C5。在這種情况下,適當的語法如下所示:

1.如果引用整個合並區域:Range(“A1:C5”)。在本例中,完全限定的引用是:Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).Range(“A1:C5”)。

2.如果僅引用合並區域左上角單元格:Range(“A1”)。完全限定的引用是:Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).Range(“A1”)。

兩種情形,結果相同。

在嘗試將值賦給合並的單元格時,應特別小心。通常,只能通過將值賦給區域的左上角單元格(上例中的單元格A1)來執行此操作。否則,ExcelVBA(通常)不會:進行賦值;和返回錯誤。

使用Range屬性的快捷方式引用Range對象

可以使用方括號([])縮短使用Range屬性對Range對象的引用。可以像下面這樣使用這種快捷方式:

1.不使用關鍵字Range。

2.用方括號([])包圍相關屬性參數,而不是使用括號和雙引號(“”)。

快捷方式1:引用單個單元格

代替輸入:Range(“Cell”),而是輸入:[Cell]。

例如,如果想要引用單元格A1,使用:[A1]。對於Book1.xlsm中工作錶Sheet1的單元格A1,完整限定的引用是:

Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).[A1]

快捷方式2:引用整行或整列

應用方括號的快捷方式是:

行:[行號:行號]

列:[列字母:列字母]

例如,如果引用工作錶第1行或第1列,其語法為:

行:[1:1]

列:[A:A]

假設在Book1.xlsm中工作錶Sheet1,則完全限定引用為:

Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).[1:1]

Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).[A:A]

快捷方式3:引用連續單元格區域

通過使用方括號縮短對一系列連續單元格的引用,如下所示:

[左上角單元格:右下角單元格]

例如,為了引用單元格A1和B5(A1、A2、A3、A4、A5、B1、B2、B3、B4和B5)之間的單元格區域,可以鍵入:[A1:B5]。或者,如果對Book1.xlsm的Sheet1使用完全限定的引用,語法如下:

Application.Workbooks("Book1.xlsm").Worksheets("Sheet1").[A1:B5]

快捷方式4:引用非連續單元格區域

使用方括號簡化引用如下:[單元格區域1,單元格區域#,…]。

如果要引用以下非連續單元格區域:

單元格A1至B5(A1、A2、A3、A4、A5、B1、B2、B3、B4和B5)和單元格D1至D5(D1、D2、D3、D4和D5)。

使用方括號的語法為:

[A1:B5,D1:D5]

完全限定的引用語法為:

Application.Workbooks("Book1.xlsm").Worksheets("Sheet1").[A1:B5,D1:D5]

快捷方式5:引用兩個區域的交叉區域

使用方括號時,引用兩個區域的交叉區域:

[單元格區域1 單元格區域2]

假設希望引用以下兩個區域的交叉區域:

單元格B1至B10(B1、B2、B3、B4、B5、B6、B7、B8、B9和B10)和單元格A5至C5(A5、B5和C5)。

可以創建使用方括號的引用如下:

[B1:B10 A5:C5]

對Book1.xlsm的Sheet1使用完全限定的引用如下:

Application.Workbooks("Book1.xlsm").Worksheets("Sheet1").[B1:B10A5:C5]

返回兩個區域的共用單元格:B5。

快捷方式6:引用命名區域

使用方括號時,引用命名區域如下:

[區域名稱]

例如,如果引用的區域名稱為“Excel_Example”,則引用構造可以為:

[Excel_Example]

對Book1.xlsm的Sheet1使用完全限定的引用如下:

Application.Workbooks("Book1.xlsm").Worksheets("Sheet1").[Excel_Example]

未完待續

版权声明:本文为[fanjy]所创,转载请带上原文链接,感谢。 https://gsmany.com/2022/01/202201071903566916.html