Excel 门外汉学VBA系列教程 教学考试成绩统计实例

阳朔游游

二、单元格的引用
1、引用单个单元格
（1）、Range 对象引用
录制宏就是以这种方式引用单元格。其语法为：Range(Address)。其中，Address 为单元格的名称或地址，以文本的形式表示。
比如，需要引用地址为“E7”的单元格，可以如此表示：

1. Sub Range对象引用单个单元格()
   
2.         Range("E7").Activate        '激活“E7”单元格
   
3. End Sub

复制代码

图 4-3
如图4-3所示，如果我们为地址为“E7”的单元格定义了名称“自定义”，还可以如此表示：

1. Sub Range对象引用单个单元格1()
   
2.     ' Range("自定义"). Select    '激活单元格名称为“自定义”的单元格
   
3. End Sub

复制代码

（2）、Cells对象引用
使用Cells对象只能引用一个单元格，不能引用多个单元格。其语法为：Cells(行号,列号)。参数“行号”和“列号”为大于0的整数，其表示单元格所在的绝对行数和列数。
比如，C1单元格的绝对行数为1及绝对列数为3（即第1行第3列）可表示为：

1. Sub Cells对象引用单个单元格()
   
2.     Cells(1, 3).Activate  '激活第1行与第3列的交叉单元格“C5”
   
3. End Sub

复制代码

因工作表的列数是用字母表示，某些情形下，我们不想去判断其属于哪一列时，“列号”的参数位置可以使用该列的名称。所以C1单元格也可以如此表示：

1. Sub Cells对象引用单个单元格1()
   
2.     Cells(1, "C").Activate  '激活第1行与C列的交叉单元格“C1”
   
3. End Sub

复制代码

Cells可以只使用一个参数，即Cells (索引号)，索引号告诉VBA，现在引用的是当前工作表中的第几个单元格。单元格按从左往右,从上到下的顺序编号，即A1为第1个单元格，B1为第2个单元格，C1为第3个单元格……在Excel 2003中，A2为第257个单元格……
所以C1单元格还可以如此表示：

1. Sub Cells对象引用单个单元格2()
   
2.     Cells(3).Activate  '激活第3个单元格即“C1”
   
3. End Sub

复制代码

（3）、Evaluate方法的简写模式
在VBA中可以将A1引用样式或命名区域名称使用方括号（[]）括起来，作为Range属性的快捷方式，即Evaluate方法的简写模式，这样就不必键入单词“Range”或使用引号。
C1单元格表示为：

1. Sub 快捷方式引用单元格()
   
2. '    [E7].Activate      '激活"E7"单元格
   
3.     [自定义].Activate   ''激活单元格名称为“自定义”的单元格即"E7"
   
4. End Sub

复制代码

【VBA秘笈】如何引用单元格更方便
从简洁及习惯来看，Evaluate方法简写模式采用我们习惯熟悉的A1样式表示单元格，对于我们来说最简洁易懂，其次Range，Cells最难理解。
从运行速度上来看，Cells引用最快，其次Range，最慢Evaluate方法。
从使用方便来看，Evaluate 方法的简写模式中无法使用各种运算，也无法使用变量表示。Cells和Range可以使用变量，Cells的行列均可使用数字变量，使用起来更方便，经常应用于在单元格区域中循环。
聪明的你对如何引用单元格必有所感悟。

阳朔游游

【VBA秘笈】Offset——单元格的偏移
“愿做桂林人，不愿做神仙”，桂林阳朔是旅游胜地，曾有一游客问我肯德基在西街多少门牌号？这个门牌号我确实没用心去记过，不过我能描述从这里怎么走到那，你沿着这条路麻直麻直往前走，莫拐弯，看到天桥后往右拐50米就到。操作单元格也有类似困惑，目标单元格地址不清楚，但我知道他从A1单元格开始向右偏移4列，再向下偏移5行，此时可以用单元格的偏移（Offset）来表示，其语法为
Range(起始单元格的地址).Offset(偏移的行数, 偏移的列数)
其中偏移的行数和偏移的列数均为整数。如激活E6单元格的代码：

1. Sub 单元格偏移1()
   
2.     Range("A1").Offset(5, 4).Activate
   
3. End Sub

复制代码

其计算过程是以 A1 单元格为起点，向右偏移4列到E1单元格，再向下偏移5行到 E6 单元格，如图4-4所示。

图 4-4
参数“偏移的行数”或“偏移的列数”可以为0，表示没有偏移。如分别激活E1及A6单元格代码。

1. Sub 单元格偏移2()
   
2.     Range("A1").Offset(0, 4).Activate '激活E1单元格
   
3.     Range("A1").Offset(5, 0).Activate '激活A6单元格
   
4. End Sub

复制代码

把0省略也是可以的，下面代码也是分别激活E1及A6单元格。

1. Sub 单元格偏移3()
   
2.     Range("A1").Offset(, 4).Activate  '激活E1单元格   
   
3.     Range("A1").Offset(5).Activate   '激活A6单元格
   
4. End Sub

复制代码

参数“偏移的行数”或“偏移的列数”还可以为负数，当为负数时，表示向上或向左偏移。如激活A1单元格代码：

1. Sub 单元格偏移4()
   
2.     Range("E6").Offset(-5, -4).Activate
   
3. End Sub

复制代码

thzss

好帖，对我们这些新手有很大帮助

阳朔游游

2、引用单元格区域
（1）、矩形单元格区域
A、Range对象引用单元格
往回看看我们录制得到的宏代码。

1. Sub Macro2()
   
2.     Range("D5:G11").Select
   
3. End Sub

复制代码

在VBA中，使用Range对象可以表示单元格区域，其用法与单个的单元格相同，参数“单元格地址”改成矩形区域的两个对角单元格地址并运用冒号（:）运算符连接起来。上述宏代码便是选定矩形单元格区域“D5:G11”，D5和G11就是该矩形区域的两对角单元格地址如图4-5所示。

图 4-5
我们还可以把冒号改为逗号，下面代码也是选定矩形单元格区域“D5:G11”。

1. Sub 选取矩形单元格区域1()
   
2.     Range("D5", "G11").Select
   
3. End Sub

复制代码

代码中的D5和G11也是该矩形区域的两对角单元格地址。
逗号方式表示单元格区域时，也可以用单元格对象（Range 对象或者 Cell 对象）替代其地址，下面代码均是选定矩形单元格区域“D5:G11”。

1. Sub 选取矩形单元格区域2()
   
2.     Range(Range("D5"), Range("G11")).Select
   
3. '    Range(Range("D5"), Cells(11, 7)).Select
   
4. '    Range(Cells(5, 4), Range("G11")).Select
   
5. '    Range(Cells(5, 4), Cells(11, 7)).Select
   
6. End Sub

复制代码

从代码Range(Cells(5, 4), Cells(11, 7))中，可以看到行列均为数字，方便我们使用变量表示，循环时常用此法引用单元格区域。
读者或许喜欢刨根问底，如上述代码中的对角单元格D5和G11位置能否互换？能否用G5和D11这两个对角单元格来表示？你把代码修改下试试不就知道了吗？笔者在此不再赘述。
B、Resize属性引用单元格
使用Range对象的Resize属性调整指定区域的大小。返回 Range 对象，该对象代表调整后的区域。
如下面代码使用Range对象的Resize属性选中A1单元格扩展为2行3列后的区域，如图4-6所示。

1. Sub Resize属性1()
   
2.     Range("A1").Resize(2, 3).Select
   
3. End Sub

复制代码

图 4-6
把光标定位于Resize中，按快捷键【F1】获取帮助，可得Resize属性的语法为：
expression.Resize(RowSize, ColumnSize)
expression      必需。该表达式返回要调整大小的 Range 对象。
RowSize      Variant 类型，可选。新区域中的行数。如果省略该参数，则该区域中的行数保持不变。
ColumnSize      Variant类型，可选。新区域中的列数。如果省略该参数。则该区域中的列数保持不变。
根据语法里的提示，我们可以用省略参数得到只扩展列，行数保持不变的代码（扩展为1行3列）。

1. Sub Resize属性2()
   
2.     Range("A1").Resize(, 3).Select   '省略参数
   
3. '    Range("A1").Resize(1, 3).Select  '把省略的参数改为1也是一样的
   
4. End Sub

复制代码

笔者经过试验把省略的参数改为1，效果也是一样的。同理我们还可以得到只扩展行而保持列数不变的代码（扩展为2行1列）。

1. Sub Resize属性3()
   
2.     Range("A1").Resize(2).Select   '省略参数
   
3. '    Range("A1").Resize(2, 1).Select  '把省略的参数改为1也是一样的
   
4. End Sub

复制代码

我们还可以把一个大区域缩小为一个小区域，如下代码是将A2:E7单元格区域缩小为A2:C3。

1. Sub Resize属性4()
   
2.     Range("A2:E7").Resize(2, 3).Select   '将A2:E7单元格区域缩小为A2:C3
   
3. End Sub

复制代码

阳朔游游

（2）、不规则单元格区域
A、Range对象引用单元格
回顾我们录制的同时选定多个区域的宏代码。

1. Sub Macro3()
   
2.     Range("D14:F17,H11:J15,H19:H21").Select
   
3. End Sub

复制代码

任何不规则单元格区域可以表示为若干个矩形单元格区域或单元格的并集。在VBA中，使用Range对象表示此类单元格区域对象时，其用法与单个的单元格或单元格区域相同，参数“单元格地址”把多个区域的单元格地址运用逗号（,）连接起来。
下面的代码同时选定A2单元格及“D14:F17”单元格区域。

1. Sub 不规则单元格区域1()
   
2.     Range("A2,D14:F17").Select
   
3. End Sub

复制代码

B、Union方法引用单元格
除了使用单元格地址通过Range对象引用不规则单元格区域外，还可以使用 Union 方法对多个区域进行并集运算。
上述代码可改写成：

1. Sub 不规则单元格区域2()
   
2.     Union(Range("D14:F17"), Range("H11:J15"), Range("H19:H21")).Select
   
3. End Sub

复制代码

1. Sub 不规则单元格区域3()
   
2.     Union(Range("A2"),Range("D14:F17")).Select
   
3. End Sub

复制代码

把光标定位于Union中，按快捷键【F1】获取帮助，可得Union方法的语法为：
expression.Union(Arg1, Arg2, ...)
expression      可选。该表达式返回一个 Application 对象。
Arg1, Arg2, ...   Range 类型，必需。必须至少指定两个 Range 对象。
在一个Union方法中，最少求并集的区域为2个，最多为30个。
【VBA提高】 巧用Union方法及循环引用多个单元格区域
当需要同时引用多个单元格区域时，此方法可以避免同时记录太多单元格地址而造成程序难以阅读。如下代码用Union方法通过循环方式获取第一列的1-50行的空单元格。

1. Sub 获取空单元格并集()
   
2.     '定义变量
   
3.     Dim Rng As Range
   
4.     Dim x As Long
   
5.    
   
6.     '1-50行间循环获取空单元格
   
7.     For x = 1 To 50
   
8.         If Cells(x, 1) = "" Then '空单元格
   
9.             If Rng Is Nothing Then
   
10.                 '若Rng未赋值，则设置Rng为当前单元格
    
11.                 Set Rng = Cells(x, 1)
    
12.             Else
    
13.                 '若Rng已赋值，则设置Rng为当前单元格与原Rng的合并区域
    
14.                 Set Rng = Union(Rng, Cells(x, 1))
    
15.             End If
    
16.         End If
    
17.     Next x
    
18.    
    
19.     '显示空单元格的地址
    
20.     MsgBox ("第一列的空单元格：" & Rng.Address(False, False))
    
21. End Sub

复制代码

在使用Union方法时需注意，由于Rng初始值为Nothing，若参数也为Nothing，将产生运行时错误，如图4-9所示。为避免该错误的发生，应对 Rng 对象进行判断，若其为 Nothing，就应直接赋值，而不使用 Union 方法。

图 4-9

阳朔游游

3、整行或整列的引用
（1）、Columns 对象和 Rows 对象
Columns 对象和 Rows 对象分别表示整列单元格和整行单元格，是一种特殊的单元格区域对象。
A、单行或单列的引用
我们通过录制宏来获取代码，如下：

1. Sub 选定单列1()
   
2.     Columns("B:B").Select   '选定B列
   
3. End Sub

复制代码

1. Sub 选定单行1()
   
2.     Rows("2:2").Select '选定第2行
   
3. End Sub

复制代码

特别注意，Columns("B:B")括号里的参数"B:B"，只能使用用英文字母表示的列标，不能使用数字，不方便用变量表示。
当然括号里的参数也可以用数字，即列号和行号，必须为大于0的整数，上面代码可更改为：

1. Sub 选定单列2()
   
2.     Columns(2).Select   '选定B列
   
3. End Sub

复制代码

1. Sub 选定单行2()
   
2.     Rows(2).Select '选定第2行
   
3. End Sub

复制代码

B、连续多行多列的引用
我们还是通过录制宏获取代码，如下：

1. Sub 选定多列1()
   
2.     Columns("B:D").Select   '同时选定B、C、D三列，"B:D"为表示列地址的字符串，不能用数字
   
3. End Sub

复制代码

1. Sub 选定多行1()
   
2.     Rows("2:3").Select '同时选定第2和3行，"2:3"为表示行地址的字符串
   
3. End Sub

复制代码

C、不连续多行多列的引用
我们可以举一仿三，得到如下代码：

1. Sub 不连续多行1()
   
2.     Rows ("14:14,16:17,19:19").Select  '同时选定第14行、16:17行、19行
   
3. End Sub

复制代码

1. Sub 不连续多列1()
   
2.     Columns ("F:F,D:D,A:B").Select   '同时选定第F列、第D列、A:B列
   
3. End Sub

复制代码

运行代码却出现“类型不匹配”的运行错误，如图4-13所示。

图 4-13
我们通过录制宏看看应该怎样引用不连续的多行多列：

1. Sub 不连续多行2()
   
2.     Range("14:14,16:17,19:19").Select  '同时选定第14行、16:17行、19行
   
3. End Sub

复制代码

1. Sub 不连续多列2()
   
2.     Range("F:F,D:D,A:B").Select   '同时选定第F列、第D列、A:B列
   
3. End Sub

复制代码

可以看到这是使用Range对象引用多个矩形单元格区域的方法。

阳朔游游

【VBA秘笈】使用Columns 对象和 Rows 对象引用矩形单元格区域
通常，可以有几种方法表示某个矩形区域，忘记了请往前翻翻。还可以采用Columns 结合 Rows 的方法引用矩形区域。
以下两句代码均是选定区域“B3:D3”：

1. Sub RC1()
   
2. '    Columns("B:D").Rows(3).Select
   
3.     Rows(3).Columns("B:D").Select
   
4. End Sub

复制代码

用 Columns("B:D")表示B:D列，用Rows(3)表示第3行，Columns("B:D").Rows(3)或Rows(3).Columns("B:D")则表示B:D列与第3行的重叠部分，即交集。
好吧，我们来举一仿三，选定区域“B2:B3”代码如下：

1. Sub RC2()
   
2.     Columns("B:B").Rows("2:3").Select
   
3. '    Rows("2:3").Columns("B:B").Select
   
4. '    Rows("2:3").Columns(2).Select
   
5. '    Columns(2).Rows("2:3").Select
   
6. End Sub

复制代码

选定区域“B2:D3”代码如下：

1. Sub RC3()
   
2.     Columns("B:D").Rows("2:3").Select
   
3. '    Rows("2:3").Columns("B:D").Select
   
4. End Sub

复制代码

阳朔游游

（2）、单元格对象的 EntireColumn 属性和 EntireRow 属性
以上代码可更改为：

1. Sub 选定单列3()
   
2.     Range("B1").EntireColumn.Activate   '选定B列，Range("B1")为B列中任一单元格
   
3. End Sub

复制代码

1. Sub 选定单行3()
   
2.     Range("A2").EntireRow.Activate  '选定第2行，Range("A2")为第2行任一单元格
   
3. End Sub

复制代码

'

1. 以下两句代码均是同时选定B、C、D三列
   
2. Sub 选定多列2()
   
3.    Range("B1:D1").EntireColumn.Activate
   
4. '   Range(Cells(1, 2), Cells(1, 4)).EntireColumn.Activate
   
5. End Sub

复制代码

1. '以下两句代码均是同时选定第2和3行
   
2. Sub 选定多行2()
   
3.    Range("A2:A3").EntireRow.Activate
   
4. '   Range(Cells(2, 1), Cells(3, 1)).EntireRow.Activate
   
5. End Sub

复制代码

利用 Columns 对象表示多列时，不能使用数字，而通过 Range 对象的 EntireColumn 属性则使得程序更灵活，可以方便对其使用数字进行循环，从而使得批量操作成为可能。
4、全部单元格的引用
英语单词有复数形式，表示多的意思，VBA代码里也有这样的复数形式，如Cell表示一个单元格，其复数Cells则表示工作表的全部单元格；Row表示一行，其复数形式Rows则表示工作表的全部行（即全部单元格）；Column表示一列，其复数形式Columns则表示工作表的全部列（即全部单元格）。以下三句代码均是选定当前工作表的全部单元格。

1. Sub 选定全部单元格()
   
2.     Cells.Activate
   
3.     Rows.Activate
   
4.     Columns.Activate
   
5. End Sub

复制代码

【VBA提高】 集合
Cells、Rows及Columns等复数形式也是对象，是对多个同种类型的对象的统称，我们称之为集合。
工作簿里的一张工作表，所有的单元格（行或列），无论单元格（行或列）里储存了什么数据，它们都属于单元格（行或列）集合，即：Cells、Rows及Columns。
若不加任何限定，如上所述，我们直接用复数形式Cells、Rows及Columns对象表示工作表里的所有单元格、行及列。
VBA中怎样取到集合里的一个对象呢？可以使用单元格（行或列）在其集合中特定的位置（即索引号）来指定某个单元格（行或列）及单元格（行或列）区域。也就是用其复数形式后面括号里的参数表示引用所有单元格（行或列）的某单元格（行或列）或区域。如Cells(2,3)表示引用工作表所有单元格中的第2行与第3列交叉的单元格；Cells(2)表示引用工作表所有单元格中的第2个单元格。
【VBA秘笈】  多少？Count一下就知道
如果我们记不住工作表一共有多少个单元格？多少行？多少列？Count一下就知道。

1. Sub Count统计()
   
2.     MsgBox ("《" & ActiveSheet.Name & "》工作表共有" & Cells.Count & "个单元格、" & Rows.Count & "行、" & Columns.Count & "列")
   
3. End Sub

复制代码

在不同版本的Excel中，工作表的总行数、总列数，当然包括总单元格数目是不同的，在Excel 2003及之前的版本中，工作表的总行数为65536（即2的16次方）、总列数为256列，而自Excel 2007开始的版本，总行数扩大到了1048576（即2的20次方）。为了让程序更通用，一般用“Rows.Count“表示工作表的总行数、Columns.Count表示工作表的总列数、Cells.Count表示工作表的总单元格数目。

阳朔游游

5、动态单元格区域的引用

在VBA中，经常需要对原始数据进行统计。然而在大多数情况下，数据所占用的单元格区域并不是固定不变的，因而会给编写通用性较高的代码造成一些麻烦。那应该如何引用动态单元格区域，以使程序更具通用性呢？

（1）、单元格对象的 End 属性

返回一个 Range 对象，该对象代表包含源区域的区域尾端的单元格。相当于寻找以某个单元格为起点按组合键【Ctrl+方向键】所定位到的单元格。当起始单元格值不为空时，将找到该方向上最后一个非空单元格，当起始单元格值为空时，将找到第一个非空单元格。

按【F1】帮助得到End的语法：

expression.End(Direction)

expression      必需。该表达式返回“应用于”列表中的对象之一。

Direction      XlDirection 类型，必需。所要移动的方向。

XlDirection 可为 XlDirection 常量之一。

常量                值                   描述

xlDown           -4121                 向下

xlToRight           -4161                 向右

xlToLeft          -4159                 向左

xlUp               -4162                 向上

以下为通过End属性查找某行或某列最后一个有数据（即非空）的单元格代码：

1. Sub End属性()
   
2.     Cells(Rows.Count, "B").End(xlUp).Activate '激活B列最后一个非空单元格
   
3.     Cells(2, Columns.Count).End(xlToLeft).Activate '激活第2行最后一个非空单元格
   
4. End Sub

复制代码

1. 
   

复制代码

特别注意，End 属性返回的只能是可见单元格，一旦最后一行数据被隐藏了，则 End 属性将不会得到正确的结果，因而在使用 End 属性时需要格外小心。

阳朔游游

(2)、CurrentRegion属性引用单元格
使用CurrentRegion属性返回 Range对象，该对象代表当前的区域。当前区域是一个边缘是任意空行和空列组合成的范围。只读。
如下面代码将选定工作表上B2单元格当前的区域，即“B2:D4”单元格区域，相当于我们在Excel窗口激活B2单元格再按下组合键【Ctrl】+【A】所选定的区域，如图4-7所示。

1. Sub CurrentRegion属性()
   
2.     Range("B2").CurrentRegion.Activate
   
3. End Sub

复制代码

图 4-7
【VBA秘笈】 Intersect 方法与Offset方法的妙用
可以使用 Intersect 方法获取两个或两个以上单元格区域的重叠部分，即交集。如图4-10所示，选定单元格区域“B2:D8”与“C5:F10”的交集区域即“C5:D8”的代码如下：

1. Sub 获取交集1()
   
2.     Intersect(Range("b2:d8"), Range("C5:F10")).Activate
   
3. End Sub

复制代码

图 4-10
同 Union 方法一样，在一个 Intersect 方法中，最少求交集的区域为2个，最多为30个。Intersect 方法将会返回一个单元格区域的对象，可以将其赋值给 Range 对象变量或者直接作为对象进行使用。
需要注意的是，当两个或多个区域不存在交集时，如图4-11所示，该方法将返回 Nothing，此时将不代表任何单元格或单元格区域，无法对其进行对象操作。

图 4-11
我们还可巧用Intersect 方法与Offset方法选定除标题行和标题列外的区域，Range("A1").CurrentRegion表示A1单元格的所在的当前区域“A1:E7”，Range("A1").CurrentRegion.Offset(1, 0)表示当前区域整体向下偏移一行的区域“A2:E8”，Range("A1").CurrentRegion.Offset(0, 1) 表示当前区域整体向右偏移一列的区域“B1:F7”，使用Intersect方法获取偏移后的所得区域的交集“B2:E7”，如图4-12所示，代码如下：

1. Sub 获取交集2()
   
2.     Intersect(Range("A1").CurrentRegion.Offset(1, 0), Range("A1").CurrentRegion.Offset(0, 1)).Activate
   
3. End Sub

复制代码

图 4-12
【VBA提高】 （3）、UsedRange属性引用单元格
使用UsedRange属性返回代表指定工作表上已使用区域的 Range 对象。只读。
如下面代码将选定当前工作表上已使用单元格组成的区域，包括空单元格。即单元格区域“B2:E6”，如图4-8所示。

1. Sub UsedRange属性()
   
2.     ActiveSheet.UsedRange.Select
   
3. End Sub

复制代码

图 4-8
特别注意：只要是用户设置过格式或者写入任何内容的都将断定其为使用过的单元格。