啰哩啰嗦地讲透VBA中引用单元格区域的18个有用方法--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]

未完待续