Excel 单元格操作.docx

1、Excel 单元格操作第 3 章 单元格的基本操作在本章中，将详细讲解如何操作 Excel 中的单元格对象。在 Excel 中，单元格对象是 用户经常需要处理的对象。同时单元格对象也是用户操作 Excel 的最小单元。在本章中， 将对涉及到单元格的常见操作进行介绍。3.1 获取单元格的引用在 Excel VBA 中，经常要使用到对单元格的引用，然后使用属性和方法对区域进行操 作。根据单元格区域的使用属性不同，主要包括以下几种情况： 单个单元格； 多个连续或者不连续单元格组成的区域； 整行或整列。用户如果希望使用 VBA 代码对单元格区域进行操作，就必须用将单元格区域赋值给 某个变量，也就是首先

2、获得某个单元格区域的引用。案例 17 使用 A1 样式引用单元格1功能说明在使用 Excel 中，A1 样式是用户最熟悉的一种引用样式。A1 样式其实就是列名和行 名的组合，确定对应的单元格。在 Excel VBA 中，当用户需要对单元格进行操作的时候， 需要首先使用代码引用单元格。2语法说明在 VBA 中，通过 Range 对象的 Range 属性返回 Range 对象。Range 属性返回一个单 元格或单元格区域，对区域的引用如果使用 A1 样式，需将引用字符串包含在引号中。另 外还可以使用以下方式引用单元格： A3：引用单元格“A3”； ActiveCell：当前单元格。 在本例中，因为

3、引用的单元格为对象，因此需要创建对象变量。创建对象变量通常分两个步骤：（1）声明对象变量。与声明普通变量类似，用户可以使用 Dim 语句或其他的声明语 句之一来声明对象变量。引用对象的变量必须是 Variant、Object，或是一个对象的指定类 型。下面的声明在 Excel 中都是有效的：（2）赋值对象变量给对象。在 Excel VBA 中，需要使用 Set 语句赋值对象给对象变量，可以赋值对象表达式或是 Nothing。下面的赋值语句在 Excel VBA 中是有效的：设置对象变量为 Nothing，会中断此对象变量与其他对象的关联，可预防因意外改变变量而更改对象。在关闭关联对象后，对象变

4、量总是设置为 Nothing，所以可以检测对象 变量是否指到有效的对象。3案例说明本例中，用户需要首先引用单元格 D3，然后在对应的单元格中添加字符串“I love Excel VBA”。4编写代码实现 A1 样式引用的代码如下：Sub ForCell()Dim RngCell As Range Set RngCell = Range(D3)With RngCell.Value = I love Excel VBA.Font.Name = Tahoma.Font.Italic = True End WithSet RngCell = Nothing End Sub5运行结果运行程序代码，得到的

5、结果如图 3.1 所示。图 3.1 A1 样式引用结果6程序分析在上面的代码中，通过下面的代码段：Dim RngCell As Range Set RngCell = Range(D3)定义了 Range 变量，然后通过 Range(D3)获取对单元格 D3 的引用。案例 18 使用 R1C1 样式引用单元格1功能说明在 Excel 中，R1C1 的引用格式在开发过程中也是经常用到的。在很多情况下，使用单 元格的行列序号来引用单元格，会给程序开发带来更大的便利。例如，当用户需要在单元 格区域内进行循环设置的时候，使用 R1C1 样式就便利很多。因为，对于循环而言，数字 会更加便利，而 A1 样

6、式中的列名字母则不适合循环。2语法说明Excel 的工作表由行和列构成。通过使用行列索引号，可用 Cells 属性引用单个单元格。 该属性返回代表单个单元格的 Range 对象。Cells(3,2)返回对工作表中单元格 B3 的引用。 同时，R1C1 样式可以使用多种引用方式：绝对引用、相对引用和混合引用，得到的结果 都是一样的。因此，用户在实际的编程中，可以根据情况选择合适的方法。使用 Cells 属性引用单元格时，用户可以使用变量替代行列索引号，所以 Cells 属性适 合在单元格区域中循环。另外，使用 Cells 属性还可按以下方式引用单元格区域： Cells(2, B)：表示第 2 行

7、 B 列 如果使用 Cells 属性时，不指定行列索引号，程序将返回工作表上所有单元格的 Range对象。3案例说明运行本例的效果如图 4-2 所示，单击“使用索引号引用单元格”按钮，将在单元格区 域“A1:E10”中按顺序填充数字。4编写代码R1C1 样式的代码如下：Sub R1C1_cells() Dim i As Integer Dim j As IntegerFor i = 1 To 4For j = 1 To 4Cells(i, j).Value = (i - 1) * 4 + j NextNext End Sub 5运行结果运行程序代码，得到的结果如图 3.2 所示。图 3.2 R

8、1C1 样式引用单元格6程序分析从上面的例子中可以看出，当用户使用 R1C1 的样式引用单元格时，可以很方便的使 用循环结构，进行各种复杂的运算。案例 19 引用多个单元格区域1功能说明在使用 Excel 分析数据或者处理问题时，有时需要同时选择多个单元格，然后再进行 处理。同时，这些单元格区域在范围上可能并不连续，因此需要使用特殊方法引用单元格 区域。当单元格区域范围比较多，同时比较杂乱，没有规律的时候，单个单元格区域依次 选择，会很耽误效率。因此，需要使用特定的语法对多个单元格区域进行引用。2语法说明在 Excel VBA 中，可以使用两种法选择单元格区域。如果是连续的单元格区域，可以 使

9、用左上角和右下角的单元格来确定区域。例如，使用下面的代码： Range(B2:D3) 表示引用的是单元格 B2：D3 的区域。如果是不连续的单元格区域，可以选择使用 Range属性。其中，使用 Range 属性的方法是，用逗号将不同的单元格区域隔开，例如： Range(B2:D3, E4:F8) 在上面的代码中，逗号必须在引号的内部。3案例说明在本例中，用户需要通过程序代码同时选中单元格中多个不连续的单元格区域，然后 设置这些单元格区域的填充红色。4编写代码本例代码如下：Sub select_range() Dim rng As RangeSet rng = Range(A1:B4, D1:G

10、6, B8:C9)rng.Select Selection.Interior.ColorIndex = 3Set rng = Nothing End Sub5运行结果运行程序代码，得到的结果如图 3.3 所示。图 3.3 选择多个单元格区域6程序分析当用户使用代码 Range(A1:B3,D4:E5)时，程序选择的单元格区域是 A1:E5，即以 第一个区域左上角单元格为起点，第二个区域右下角单元格为终点，连接成一个新的连续 区域。案例 20 合并单元格区域1功能说明前面的案例说明了如何使用 Range 属性来引用多个单元格区域。在 Excel VBA 中，用 户还可以使用 Union 方法来合

11、并不同的单元格区域。在本小节中，将详细讲解如何使用 Union 方法来合并单元格的区域。2语法说明使用 Application 对象的 Union 方法，可将多个单元格区域组合到 Range 对象中。Application.Union 方法的语法格式如下：表达式.Union(Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13,Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg2

12、5, Arg26, Arg27, Arg28, Arg29, Arg30)用户在使用 Union 方法时，至少需要两个单元格区域，最多可合并 30 个单元格区域。例如： Union(Range(A1:C3), Range(E1:F5) 3案例说明在本例中，用户需要引用多个单元格区域，然后在引用区域的单元格中添加随机数。4编写代码合并单元格区域的具体代码如下：Sub union_range()Dim rng1 As Range Dim rng2 As Range Dim rng3 As RangeSet rng1 = Range(A1:B3) Set rng2 = Range(D4:F6) Se

13、t rng3 = Union(rng1, rng2)rng3.Formula = =int(100*RAND()+20) End Sub5运行结果运行程序代码，得到的结果如图 3.4 所示。图 3.4 合并单元格区域的结果6程序分析在本例的代码中，首先使用两个对象变量获取单元格区域，然后使用 Union 方法将两 个区域合并为一个区域。案例 21 引用合并区域的子区域1.功能说明当用户同时选中多个单元格区域时，某些操作不能在选定区域内同时执行，必须在选 定区域内的单个子区域上循环，对每个单独的子区域分别执行该操作。因此，这个时候有 必要引用合并区域的子区域。2语法说明在 Excel VBA 中

14、，用户选定多个区域后，将生成 Areas 集合。Areas 集合的每个成员 是 Range 对象。选定区域内每个离散的连续单元格区域都有 Range 对象。如果选定区域内 只有一个子区域，则 Areas 集合包含一个与该选定区域对应的 Range 对象。使用 Areas(index)可从集合中返回单个 Range 对象。该索引号对应选定这些区域的顺 序。下例中，如果当前选定区域包含多个子区域，就清除该选定区域中的第一个子区域。 3案例说明选择工作表中的多个单元格式区域，将其单元格区域进行合并，然后依次遍历合并后 的单元格区域的各个子区域。4编写代码引用合并区域的子区域的具体代码如下：Sub G

15、et_Part_rubrange() Dim rng1 As Range Dim rng2 As RangeDim i As Integer Dim str As StringSet rng1 = Range(A1:C2, B3:D4, E4:F6)rng1.SelectFor i = 1 To rng1.Areas.Count Set rng2 = rng1.Areas(i)str = 子区域 & i & 的地址是： & vbCr & vbCrstr = str & rng2.Address MsgBox strNextSet rng1 = Nothing Set rng2 = Nothin

16、gEnd Sub5运行结果运行程序代码，得到的结果如图 3.5 所示。图 3.5 遍历第一个子区域的结果单击对话框中的“确定”按钮，然后依次运行程序代码，得到的最后一个子区域的结 果如图 3.6 所示。图 3.6 遍历最后一个子区域的结果6程序分析在本例的代码中，Areas 集合包括三个子区域，通过循环分别显示每个子区域的地址， 然后显示在对话框中。案例 22 当前单元格的前一单元格1功能说明Excel 表格是一个二维结构，所有的单元格组成了一个工作表。这些单元格在物理位 置上是相互关联的：前、后、左和右等。这些都是相邻单元格的区域关系，在 Excel 中， 用户通过 VBA 代码还可以处理非

17、相邻的单元格物理关系。在本小节中，将首先讲解如何 引用当前单元格的前一单元格。2语法说明使用 Range 对象的 Previous 属性，可获取对指定单元格的前一个单元格的引用。其语 法格式如下：表达式. Previous 如果表达式为一个对象为区域，则 Previous 属性会模拟 Shift+Tab 键，但此属性只是 返回上一单元格，并不选定它。3案例说明某公司统计了部分员工上、下两半年销量，同时提供了员工所处的地区。本例中，用 户在选中某单元格后，需要查看该单元格的前面一个单元格数据。原始数据如图 3.7 所示。图 3.7 原始数据4编写代码获取当前单元格前一单元格的引用的代码如下：Su

18、b pre_cells()Dim rng As RangeSet rng = ActiveCell.PreviousMsgBox 当前单元格的前一单元格的值为： & rng1.ValueSet rng = Nothing End Sub5运行结果选择单元格 D6，运行程序代码，得到的结果如图 3.8 所示。图 3.8 运行结果6程序分析在上面的程序代码中，通过 Previous 属性获取了前一单元格的引用，然后显示该单元 格的数值。案例 23 当前单元格的后一单元格1功能说明前面小节的例子中，已经详细介绍了如何获取当前单元格的前一单元格的引用。类似 的，Excel 中同样可以引用当前单元格的后

19、一单元格。在本节中，将详细介绍如何获取当 前单元格的后一单元格。2语法说明使用 Range 对象的 Next 属性，可获取对指定单元格的后一个单元格的引用。其语法 格式如下：表达式. Next 如果表达式为一个对象为区域，则 Next 属性会模 Tab 键，但此属性只是返回下一单 元格，并不选定它。3案例说明某公司统计了部分员工上、下两半年销量，同时提供了员工所处的地区。本例中，用 户在选中某单元格后，需要查看该单元格的后面一个单元格数据。原始数据如图 3.9 所示。图 3.9 原始数据4编写代码取当前单元格的后一单元格的引用的代码具体如下：Sub next_cells()Dim rng As

20、 RangeSet rng = ActiveCell.NextMsgBox 当前单元格的后一单元格的值为： & rng.Value Set rng = NothingEnd Sub5运行结果选择单元格 C7，运行程序代码，得到的结果如图 3.10 所示。图 3.10 运行结果6程序分析在上面的程序代码中，通过 Next 属性获取了后一单元格的引用，然后显示该单元格 的数值。案例 24 引用整行或者整列单元格1功能说明在使用 Excel VBA 进行数据处理的时候，经常会需要对某列或某行单元格进行设置的 情况。这个使用，用户就需要首先选择整行或者整列单元格区域。2语法说明在 Excel VBA

21、中，可以使用多种方法选择整行或者整列。首先，可用 Rows 属性或 Columns 属性来处理整行或整列。这两个属性返回代表单元格区域的 Range 对象。在下面 的代码中，将返回工作表的第二行，然后将区域字体加粗。 Worksheets(Sheet1).Rows(2).Font.Bold = True 下面列出了使用 Rows 和 Columns 属性的一些行和列的引用。同时，用户可以直接使用 Range 属性来返回整行或者整列单元格，如下：3案例说明某公司统计了部分员工上、下两半年销量，同时提供了员工所处的地区。本例中，需 要设置工作表的最后一行的格式，原始数据如图 3.11 所示。图 3

22、.11 原始数据4编写代码获取整行单元格引用的代码具体如下：Sub All_RowCol()Range(11:11).Select With Selection.Font.Bold = True.Italic = True End WithEnd Sub5运行结果运行程序代码，得到的结果如图 3.12 所示。图 3.12 运行结果6程序分析在上面的程序代码中，通过代码“Range(11:11).Select”引用了第 11 行的所有数据。案例 25 引用相对其他单元格的单元格1功能说明前面小节已经讲解了多种不同的引用方法。使用不同的引用样式，用户可以应用不同 的单元格区域。其中，以某单元格为基

23、准单元格，然后根据偏移数引用其他单元格应用十 分广泛。理论上分析，使用这种方法可以引用任何相对位置。2语法说明在 Excel VBA 中，用户可以使用 Range 对象的 Offset 属性返回 Range 对象，代表位于 指定单元格区域的一定的偏移量位置上的区域。其使用格式为： Offset(RowOffset, ColumnOffset) 两个参数的含如下： RowOffset 为行偏移量，区域偏移的行数可为正数、负数或 0（零）。正数表示向下偏 移，负数表示向上偏移。默认值是 0； ColumnOffset 为列偏移量，区域偏移的列数可为正数、负数或 0（零）。正数表示向右 偏移，负数表

24、示向左偏移。默认值是 0。例如下面的程序代码：3案例说明某公司统计了部分员工上、下两半年销量，同时提供了员工所处的地区。根据用户选 择的单元格，设置该单元格右下方单元格的背景。原始数据如图 3.13 所示。图 3.13 原始数据4编写代码引用相对其他单元格的单元格的具体代码如下：Sub Offset_Cells() Dim rng As RangeSet rng = ActiveCell.Offset(1, 1) rng.Select Selection.Interior.ColorIndex = 3 Set rng = NothingEnd Sub5运行结果选择工作表的单元格 C5，如图 3

25、.14 所示。图 3.14 选中单元格运行程序代码后，得到的程序结果如图 3.15 所示。图 3.15 运行的程序代码结果6程序分析在上面的程序代码中，是以当前单元格为基准，所以每执行一次上面的子过程，当前 单元格就向下移动三行、向右移动三列。案例 26 引用当前区域1功能说明当前区域是 Excel 工作表中一个十分特殊的单元格区域，是指以空行与空列的组合为 边界的区域。当前区域这个概念在数据处理领域使用的十分普遍，特别是当用户需要添加、 编辑或者修改当前数据的时候。在工作表中，选择“开始”|“编辑”|“查找和选择”|“定位条件”命令，在“定位 条件”对话框中，选中“当前区域”选项按钮，也能获

26、取关于当前区域的信息，如图 3.16 所示。图 3.16 选择“当前区域”选项2语法说明在 Excel VBA 中，使用 Range 对象的 CurrentRegion 属性，可返回表示当前区域的 Range对象。其语法格式如下：表达式.CurrentRegion 该属性对于许多自动展开选择区域，以包括整个当前区域的操作很有用。3案例说明某公司统计了部分员工上、下两半年销量，同时提供了员工所处的地区。现在需要通 过 VBA 代码获取当前区域的信息。原始数据如图 3.17 所示。图 3.17 原始数据4编写代码引用当前区域的具体代码如下：Sub Current_Region() Dim rng

27、As RangeSet rng = Range(A1)Set rng = rng.CurrentRegion rng.SelectSet rng = NothingEnd Sub5运行结果运行程序代码，得到的结果如图 3.18 所示。图 3.18 引用当前区域6程序分析在当前区域范围内，无论活动单元格是哪一个单元格，所在的当前区域均为同一区域。 如上例中的 A1:D11 区域，活动单元格 B2 的当前区域为 A1:D11，当活动单元格为 D2 时， 其当前区域仍为 A1:D11。案例 27 引用已使用区域1功能说明已使用区域在 Excel 中是一个十分常用的概念，其主要功能是显示当前使用的单元

28、格 的范围区域。和当前区域不同，已使用区域表示的是所有工作表中所使用单元格的区域。 2语法说明在 Excel VBA 中，UsedRange 属性返回指定工作表中已使用区域的 Range 对象，即返 回工作表中已使用的单元格区域。因此，该属性也可以用于选取单元格区域。UsedRange 属性与上例中的 CurrentRegion 属性的区别： UsedRange 属性是 Worksheet 对象的一个属性，返回指定工作表中所有已使用单元格 区域，无论各单元格之间是否有空行或空列隔开。 CurrentRegion 属性是 Range 对象的一个属性，返回的是一个由空行空列围起来的区域， 空行空列

29、之外的单元格不被包含在内。3案例说明某公司统计了部分员工上、下两半年销量，同时提供了员工所处的地区。现在需要通 过 VBA 代码获取已使用区域的信息。原始数据如图 3.19 所示。图 3.19 原始数据4编写代码选择已使用区域的程序代码如下：Sub used_range()Dim rng As RangeSet rng = ActiveSheet.UsedRange rng.SelectSet rng = NothingEnd Sub5运行结果运行程序代码，得到的结果如图 3.20 所示。图 3.20 选择已使用区域6程序分析当用户使用 UsedRange 属性来获取已使用的区域时，可以通过

30、Address 属性返回该区 域的地址。例如，在上面的程序代码中添加下面的代码： MsgBox rng.Address 运行程序代码后，会显示对应的地址，如图 3.21 所示。图 3.21 显示区域的地址案例 28 调整单元格区域的大小1功能说明在前面小节中，用户已经了解了如何使用 VBA 来引用单元格区域的方法。在实际应 用中，用户可能在程序代码中需要调整引用的单元格区域。这个时候，用户就需要首先调 整单元格区域的大小。在本小节中，将详细讲解如何调整单元格区域的大小。2语法说明在 Excel VBA 中，使用 Range 对象的 Resize 属性，可以调整指定单元格区域的大小， 并返回一个 Range 对象，该对象代表调整后的区域。其语法格式如下：表达式.Resize(RowSize, ColumnSize) 两个参数的含义如下： RowSize 为新区域中的行数。如果省略该参数，则该区域中的行数保