excel vba统计数量（ExcelVBA与数据统计第四章）

第四章 对象第一节 什么是对象Excel VBA中的对象，下面我们就来聊聊关于excel vba统计数量?接下来我们就一起去了解一下吧!

excel vba统计数量

第四章 对象

第一节 什么是对象Excel VBA中的对象

4.1.1 对象和类

所谓对象就是自然界存在是一切实体，所谓类就是这些实体在人脑中的一个“划分”。例如，“人类”是一个类，而一个具体的人，例如张王李赵这样的个体，就是“人类”这个类中的一个对象。“电脑”也是一个类，具体到“这台电脑”就是电脑这个类的一个对象了。

在VBA中，“对象”一词来源于英语“Object”，指Excel中可以操作的具体对象。在VBA中，除了类和对象之外，还有父类和子类、父对象和自对象的概念。

父类和子类，在类别划分的领域，人们总是习惯地把一个类规划为另一个类中的一部分。例如，“人类”和“猿类”同属于“灵长类“，”圆珠笔“和”笔记本“同属于”办公用品“。在这种一个类中包含另一个类的划分结构中把包含其他类型的类称为“父类“，而被包含是类称为“子类”。在上面的两个例子中，“灵长类“是”人类“和”猿类“的”父类“，而”笔记本“和”圆珠笔“是”办公用品“的”子类“

父对象和子对象，与父类和子类的定义相同，一个”父对象“中包含了许多可以操作的”子对象“，就好比一个具体的人，是一个具体的对象。而这个人的四肢、眼睛、鼻子、耳朵就是这个人这个父对象的”子对象“。显然，无论是父对象还是子对象，都是可以直接操作的对象，也就是具体的事物，但是这些事务之间又有着”包含“的关系。

4.1.2 Excel VBA中的对象

在Excel VBA中，有着4大对象，即Application对象、WorkBook对象、WorkSheet对象和Range对象。这四大对象之间是上级和下级，父对象和子对象的关系。

Application对象：Excel VBA中最高级别的对象，这个对象就是整个Excel VBA应用程序，在这个对象中，可以实现对应用程序级别的任何调用。

WorkBook对象 Excel VBA中的工作簿对象，这个对象是在Excel VBA中的参与操作的工作簿。

WorkSheet对象 Excel VBA中的工作表对象，这个对象是在一个工作簿中的一个工作表。在Excel 工作簿中，可以添加和删除工作表。这些工作表的关系就是这个工作簿的”子集“

Range 对象 Excel VBA中的单元格对象，这个对象是一个工作表中的一个单元格或者一个单元格区域，这一部分是Excel VBA操作的重点，也是本书重点阐述的内容。

第二节 对象的属性和方法

4.2.2 对象的属性

操作对象在某一个方面的特征，称为这个对象的”属性“例如，对于一个具体的人，这个人的姓名就是他的一个属性，性别是另一个属性，显然，对于一个具体的对象，其属性也是一个具体的数值。

对于确定的对象，可以读取这个对象的某一个属性，例如，对于一台电脑，可以获知这台电脑的厂家、品牌、内存大小、处理器情况等。

对象的属性，有些属性可以修改，修改对象的属性并不会影响对这个对象的其他操作，例如在World中修改正文的字体和字号，其实是修改了这个字的”字体“属性和”字号“属性，并没有改变正文的本身内容。

而对象的另外一些属性只能读取不能修改，例如，对于一部手机，可以获知这部手机的型号和厂家，但是却不能修改。想获得另外一个厂家和型号的手机，只有另外再买一部。

4.2.3 对象的方法

对象的操作方法称为对象的方法，即对于一个具体的对象，可以采用什么样的操作方法。例如，对于一本书，我们可以通过”阅读“的方法阅读这本书中的内容。对于一壶冷水，我们可以通过”加热“的方法使得水的温度上升。

4.2.4 对象的默认属性

对象的默认属性，是对象呈现在外界时的属性，即外界通过这一个属性可以直接识别对象本身。例如，对于一个人来说，他的默认属性是姓名，其他的人总是通过姓名在直接识别这个人，而不是通过这个人的身高、体重、肤色等其他属性。对于一本书，总是以书名为默认属性向外界呈现。

4.2.5 对象的默认方法

既然对象有默认的属性，那么对于一个确定的对象，也有一个默认的方法来操作该对象。在VBA中，对象的默认方法为显示方法，即这个对象总是通过图形、数字等形式显示在屏幕上。在VBA中无论调用一个对象的属性或方法或它的子对象，均是在这个对象后面加上一个点，即英文状态在的句号 ”.” 来实现。用上面的例子，人的姓名在VBA中的“表示方法“就是：人.姓名。

4.2.6 声明一个对象变量

声明对象变量的方法是：Dim 变量名 As 对象。

4.2.7 Active对象

在VBA中，Active对象成为活动对象，是指程序当前处理或者选择的具体对象。例如ActiveSheet就是当前正在编辑的工作表对象，ActiveCell就是当天正在编辑的单元格或者区域对象。

4.2.8事件

所谓的“事件”通俗来说就是发生了的一个事情，例如，“刚刚下雨了”就是一个事件，“火车启动了”，也是一个事件。通常来说，事件往往伴随着一系列的“后果“，例如”刚刚下雨了“，会导致”出门要带雨伞“的后果。事件的发生在VBA中称为”触发“。

第三节 Workbook对象的属性和方法

由于Application对象作用于整个Excel VBA工程，所以我们这里从它的子对象WorkBook开始讲解。由于每一个对象的属性和方法十分复杂，正常运用也不会涉及这么多，所以这里只挑选几个比较重要的讲解，需要进一步了解其他属性和方法的读者可以去CSDN查询相关的内容

第三节 Workbook对象

4.3.1 Workbook 和ThisWorkbook对象的属性

ThisWorkbook 这个对象指当前打开的VBE所在的工作薄，ThisWorkbook以及Workbook对象中常用的属性有：

Name属性，返回当前工作簿的名称，例如，在桌面上的新建文件夹中一个名字叫做”工作簿3“的Excel表，打开后，在VBE环境下尝试下列程序：

Sub W()

MsgBox(ThisWorkbook.Name)

End Sub

运行结果，返回该Excel工作薄（文件）的名称：工作簿3.xls。

Path属性，返回当前工作簿所在的保存路径，还是上面的例子，假如程序改成：

Sub W()

MsgBox(ThisWorkbook.Path)

End Sub

运行结果，返回工作簿所在的保存路径，即C:\Users\Administrator\Desktop\新建文件夹。

利用ThisWorkbook的Path属性获得工作簿所在目录下的所有Excel工作簿名称（所有xls格式工作簿名称）

Sub W()

Dim Path As String, File As String

Path = ThisWorkbook.Path & "\"

File = Dir(Path & "*.xls")

MsgBox (File)

While File <> ""

File = Dir

If File = "" Then Exit Sub

MsgBox (File)

Wend

End Sub

上面这个例子中，利用 Thisworkbook.Path返回当前工作簿所在的路径，并且使用Dir函数一一读取这个路径下所有的xls格式文件的文件名。对于Dir函数，其使用规则如下：Dir(Path,[,attr]),当第二个参数缺失时，Dir函数返回路径下没有设置属性的文件的名称。第一次使用Dir函数时，返回路径下的第一个文件名称，再次使用这个函数，并且不加参数，返回第二个文件名称……当路径下没有文件或者所有文件名称均被返回，则返回一个空字符。Dir函数的第二个参数可以是下列参数之一：vbNormal（没有属性的文件）、vbRedOnly(只读文件)、vbHidden(隐藏文件)、vbSystem(系统文件)、vbVolume(卷标文件)、vbDirectory(文件夹的名称和文件名称)

与ThisWorkbook一样，任何Workbook对象都有Name和Path属性，用法与ThisWorkbook中的用法一致，例如，可以定义一个WorkBook对象变量，并获得该对象的名称和路径。

4.3.2 Workbook对象常用到的事件

在VBA中，打开工作簿，关闭工作簿，激活工作簿都是一个“事件“。

打开工作簿事件：打开工作簿时，触发”打开工作簿“事件，在VBE界面窗口中，点击资源管理器中的“ThisWorkbook”就可以进入ThisWorkbook的工作簿事件。

从这里可以看出，Workbook的事件编辑窗口与一般的之前我们的模块编辑窗口没什么不同，只是多了一行选择菜单。这里，在“通用”下拉菜单中找到“Workbook”，就可以在后面“声明”下拉菜单中找到Workbook的可利用事件。这里我们只讲解其中4个事件。

Open事件：打开Workbook时触发Open事件，例如，我们如果想每一次打开这个工作簿就显示欢迎界面，可以在ThisWorkbook的事件编码器里面找到Workbook的Open事件，并加上一行代码： MsgBox(“你好！”)，整个程序段如下：

Private Sub Workbook_Open()

MsgBox ("你好！")

End Sub

图4.2 在Workbook的Open事件中添加欢迎界面“你好！”

保存一下，以后我们每一次打开这个工作簿的时候，都会弹出这个欢迎的对话框“你好！”

图4.3 欢迎界面

BeforClose事件：在关闭工作簿时，触发BeforeClose事件，例如，我们想让每一次关闭工作簿的时候，弹出一对话框“Good Bye！”，可以在ThisWorkbook的事件编码器里面找到Workbook的BeforeClose事件，并且添加一行 MsgBox(”Good Bye！“)完整代码如下：

Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox ("Good Bye!")

End Sub

保存后，每次关闭工作簿时就会弹出这个对话框了。

图4.4 关闭时显示“Good Bye!”

BeforeSave事件 保存工作表前，触发BeforeSave事件，例如，我们想自己添加一个是否保存的对话框，并且点击”是“时进行保存，”否“不保存，可以在ThisWorkbook的事件编码器里面找到Workbook的BeforeSave事件，并且加上命令，完整代码如下。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

MsgBox ("保存是个好习惯")

Cancel = Not (MsgBox("保存？", vbYesNo))

End Sub

这一程序的运行结果，每一次保存工作簿前都会给出提示“保存是个好习惯“，然后提示是否保存，注意到，这个程序中的Cancel是”取消保存“，所以，前面用了一个否定运算符的Not，后面MsgBox，一是给用户显示”是否保存“，二是显示两个按钮，即”是“和”否“。用户按下”是“则返回逻辑值”Ture“，否则返回”False“。

AfterSave事件 保存工作簿后，触发AfterSave事件，不同的是，AfterSave时间中没有”取消“选项了，我们也可以在AfterSave中添加代码，用于提示保存成功。

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

MsgBox ("保存成功！")

End Sub

给工作簿添加打开密码：通过Workbook的Open事件，可以给工作簿添加打开密码，完整代码如下：

Private Sub Workbook_Open()

Dim PassWord As String

PassWord = InputBox("请输入密码")

If PassWord = "123456" Then

Exit Sub

Else

MsgBox ("密码错误！")

ThisWorkbook.Close

End If

End Sub

这一段代码作用是，每一次打开工作簿的时候要求用户输入密码，如果输入的密码不是123456，则显示“密码错误“，并且关闭工作簿。

图4.5 给Excel工作簿添加打开密码

第四节 workSheet对象

4.4.1 worksheet对象的引用

对于一个worksheet对象，可以采用的引用方法有

（1）直接引用Sheet 数字的形式，Sheet1即这个Excel工作簿中的第一个工作表，Sheet2为Excel工作簿中的第二个工作表……

（2）采用worksheets(n)的形式，表示工作簿中第n个工作表（按照从左向右的顺序）。

（3）采用worksheets(“字符串”)的形成，表示名子为字符串的工作表。

（4）采用worksheets(“Sheet” 数字)的形式，表示第n个工作表。

4.4.2 worksheet对象的属性

Name属性：与Workbook中的Name属性一样，worksheet也有Name属性，我们可以访问这些名称。例如，获取工作簿中的工作表名称，可以写成下列程序

Sub GetWSN()

Dim wsh As Worksheet

For Each wsh In Worksheets

MsgBox wsh.Name

Next wsh

End Sub

程序中，Worksheets即整个Excel工作簿中饭的所有工作表，这个例子中，运用For Each……Next对工作簿中的工作表一一遍历，然后输出每个工作表的名称。

也通过修改工作簿中的工作表的Name属性修改工作表名称，例如。

Sub ChWHN()

dim wsh As WorkSheet, i as Integer

i = 1

For Each wsh In WorkSheets

wsh.Name=”表” & i

i =i 1

Next wsh

End Sub

运行结果，用一个循环变量i，给Excel工作簿中所有的工作表编号。

Visible属性：工作表的Visible属性决定这个工作表是否可见，如果我们想影藏工作表1，只需要 Sheet1.Visible= False, 或者Worksheets(1).Visible=False即可。

Sub Hiden()

Worksheets(1).Visible = False

End Sub

显示所有的工作表：与上过程相反，显示所有的工作表可以用下列语句

Sub ShowAll()

Dim wsh as Worksheet

For Each wsh in WorkSheets

wsh.Visible=True

Next wsh

End Sub

语句也是通过一个循环变量，将所有工作表的Visible属性修改为True。

Index属性：返回工作表的序号，例如，在Excel工作簿中有一个名字为“My Work Sheet”的工作表，可以用过Worksheets(“My Work Sheet”).Index得到这个工作表在工作簿中的序号。

4.4.3 worksheet对象的方法

Copy方法，Copy方法可以复制粘贴整个工作表。例如，将整个工作表1复制一份，命令是 Sheet1.Copy。此时，Excel会在最后自动建立一个新工作表，然后工作表中的内容和表1中完全一致。

Activate方法，Activate方法可以使工作表处于激活状态，例如，在工作表1中进行工作时，想跳转到工作表2，用VBA代码的方法就是：Sheet2.Activate。

Delete方法，Delete方法用于删除一个工作表。例如，Sheet2.Delete，就删除了工作表2.

Move方法，Move方法可以改变工作表在工作簿中的位置，例如：Sheet2.Move After:=Sheet3 ,就将Sheet2工作表移动到了工作表Sheet3的后面，当然也可以利用例如：Sheet4.Move Before:= Sheet2 的方式把表Sheet4移动到Sheet2的前面。

第五节 Range对象

Range对象即单元格对象，是应用最为广泛，使用最为灵活的一个Excel对象，是本章节的重中之重。

4.5.1 Range对象的引用方法

（1）单一引用

直接用Range或者Cells或者[]方式引用，Excel VBA中，可以直接用代码引用Excel表格中的单元格或者区域。对于这些单元格或者区域，可以采用单一引用的方式，也可以采用嵌套引用的方式 。

Range(“A1”)方式引用单元格：Excel VBA中，可以用Range(“A1”)的方式引用Excel中的单元格，Range(“A1”)即表示Excel表格中的”A1”单元格，即第一行第一列的单元格。例如，可以用以下命令将A1单元格中的数字修改为2019.

Sub S()

Range(“A1”)=2019

End Sub

Cells(1,1)方式引用单元格：在Excel VBA中，还可以用Cells(1,1)的方式引用单元格，Cells(1,1)也表示Excel表格中第一行第一列的单元格，即“A1”单元格，例如，可以用下面命令给B1单元格中的数字修改为4

Sub S1()

Cells(1,2)=4

End Sub

[A1]方式引用单元格: 在Excel VBA中，还可以用[A1]方式引用单元格，[A1]方式引用单元格也是引用Excel中的A1单元格。可以用下面程序修改C1单元格中的数字。

Sub S2()

[C1]=25

End Sub

引用的引用：假如我们在Excel的单元格A1中写入了一个单元格的名称，例如”B2“，那么，用Range([A1])的方式同样可以引用到B2单元格。例如，下面程序中，利用A1单元格中的内容给相应的单元格赋值为15.

Sub S3()

Range([A1])=15

End Sub

(2) 嵌套引用 利用Range不仅可以引用单个单元格，而且可以引用一个单元格区域，Range应用单元格区域的方法和意义如下。

Range(“A1:B5”) 表示引用Excel表格中整个A1到B5区域。这种引用方法，引号中的部分可以用一个字符串变量代替。

Range(“A1”,”B5”)也是表示引用Excel表格中整个A1到B5区域，这种引用的方法中，逗号分隔的两个部分可以使用用两个字符串变量代替。

Range(“A:A”) 表示引用Excel表中整个A列所有单元格。

Range(“A:C”) 表示引用Excel表中A列到C列的所有单元格。

Range(“2:2”) 表示引用Excel表中整个第2行的所有单元格。

Range(“2:5”) 表示引用Excel表中第2行到第5行所有单元格。

整列的引用，除了可以用Range，也可以用Columns(“A”)的方式或者Columns(“A:B”)的方式。Columns(“A:B”)表示引用A列和B列。

整行的引用，可以采用Rows()的方式，与Columns方式引用整列的用法相似，Rows(2)表示引用整个第2行。

单元格对象的嵌套引用，可以用Range引用一个区域，再用Range引用其中的一个单元格或者区域，例如Range(“B2:D4”).Range(“B2”) 表示单元格区域B2到D4中的“B2”格，即第二行第二列的单元格，即C3单元格。

图4.6 单元格的嵌套引用

也可以用Range选择一个单元格区域，再用Cells引用单元格区域中的某一个单元格，例如Range(“C2:F9”).Cells(2,4) 表示单元格区域C2到F9中，第2行第4列的单元格。即F3单元格。

4.5.2 Range对象的属性和方法

Address属性：Address属性返回一个Range对象在工作表中的绝对位置，下面命令在A1单元格中返活动单元格的位置，当Range对象为一个单元格区域时，返回这个单元格区域的第一行第一列所在的位置。

Sub Address()

Range(“A1”)=ActiveCell.Address

End Sub

Column属性：Column属性返回单元格所在的列的列数，例如，将与活动单元格同一列的第4行的单元格的数修改为29.

Sub Col()

Cells(4, ActiveCell.Column) = 29

End Sub

Columns属性

Columns属性的Clear方法 用于清除单元格区域对象中的某一列或者所有列的数据。例如，想清除Excel 单元格区域A1：C3中第2列的数据，可以用以下程序。

Sub Col2()

Range(“A1:C3”).Columns(2).Clear

End Sub

如果想运用于单元格区域中的所有列，则Columns后面不加(2)。

Columns属性的Copy方法 用于复制单元格区域中某一列或者所有列的数据，例如，将Excel单元格区域A1：C3中第2列的数据复制到F列，可以使用下列程序：

Sub Col2()

Range("A1:C3").Columns(2).Copy Range("F:F")

End Sub

Copy方法前面是复制的“原始数据“的位置，后面是复制的”目标位置“。这里，直接用原始位置.Copy 目标位置，就可以完成这个复制操作。

Columns属性的ClearFormats方法，ClearFormats方法用于清除单元格区域中选定列的单元格格式。例如，加上单元格区域A1：C1格式设定为货币型，则这个单元格区域中所有的数字前多出一个货币符号”￥”,用Columns.ClearFormats就可以清除这种格式设置。

Columns 属性的Delete方法, Delete方法用于删除列，例如，想删除Excel中A列中的内容，并且让原来的B列自动成为A列则可以用下列程序。

Sub Del()

Range(“A:A”).Columns.Delete

End Sub

这里需要注意，删除列的操作可能会引起单元格中公式引用错误，例如，原先在D4单元格中输入公式“=A4 B4”，在删除A列后，这个公式就会发生引用错误的情况。这是因为，在进行删除列的操作后，公式所在的列也发生了变化（原来在D4格，删除一列后在C4格）

Columns 属性的Count方法，Columns属性的Count方法返回区域的列数，例如，Range(“A2:D5”).Columns.Count 等于4（这个区域有4列）。Columns属性的Count方法通常用于用户输入或者选择区域的地方，例如：

Sub ColumnCount()

Dim Rng As Range

Set Rng = Application.InputBox("选择区域", "计算区域列数", , , , , , 8)

MsgBox (Rng.Columns.Count)

End Sub

这一段程序中，需要解释的是Application.InputBox的用法，Application.InputBox是InputBox的”升级版本“其可以允许用户输入数字、文本（字符串）、公式、逻辑值、单元格或者单元格区域，其用法是Application.InputBox(Prompt,[Title],[Default],[Left],[Top],[HelpFile]

,[HelpIndex],[Type]),Prompt是对话框的内容，Title为对话框的标题，Default为默认输入值，Left和Top分别为对话框所在的屏幕位置（不设定情况下可以自由移动），HelpFile、HelpIndex为帮助文件，Type指定用户输入的类型，分别为0：公式，1：数字，2：文本，4：逻辑值，8：Range对象，16：错误值，64：数值数组。

Columns属性Active方法：用于激活Range对象的某一列。

Row 属性：Range对象的Raw属性，返回这个单元格所在的行数，如果是一个单元格区域，则返回该区域第一个单元格所在的行数。例如，将与活动单元格同行的第4列数字改为26.

Sub R()

Cells(ActiveCell.Row,4)=26

End Sub

Rows属性

Rows属性的Clear方法：与Columns属性的Clear方法相似，Rows属性的Clear方法用于清除一个Range对象中的某一行或者某几行数据。

Rows属性的Copy方法：用于复制一个Range对象的某一列数据。用法与Columns属性的Copy方法相似。

Rows属性同时也具有Columns属性的其他方法。

Offset属性：Range对象的Offset属性，返回Range对象移动若干个单元格后的单元格区域，Offset属性的用法是：Offset(行偏移量,列偏移量)，若行偏移量>0,则表示单元格区域向下移动，列偏移量>0表示区域向右移动，否则表示区域向上、向左移动。例如：Range(”A2:C6“).Offset(1,2) 表示单元格区域A2:C6向下移动1行，再向右移动2列的单元格区域，即C3:E7区域。

图4.7 单元格区域的Offset属性

利用Range对象的Offset属性，我们可以把一个Excel表特定的区域中的数字复制到其相邻的区域。例如：

Sub Rep（）

Dim Rng As Range

Set Rng=Range(“A1:C4”)

Rng.Copy Rng.Offset(0,Rng.Columns.Count)

End Sub

这一段的作用是，将A1:C4单元格区域中的内容复制到它右侧相邻的区域，即D1:F4.这里，先使用Columns的Count方法返回之前单元格区域Rng的列数Rng.Columns.Count，并使用Offset属性，让之前的区域向右移动这么多列，任意一个单元格区域，向右移动其列数个单元格后，总会得到与其相邻的右侧区域。最后，再用Copy方法将之前的内容复制过去。读者可以通过这个例子思考一下如何把单元格区域复制到其下侧相邻的区域。请读者记住这一个程序，我们会在下一节中对这个程序详加讨论，并把这个程序的思想“发扬光大”。

Interior属性

Interior属性的Color属性：用于修改单元格的背景颜色，可以采用vbRed(红色)、vbGreen(绿色)、vbBlue(蓝色)的方法修改单元格背景色，也可以用RGB()的方式修改单元格背景色。例如

Sub Col1()

Range(“A1”).Interior.Color=vbRed ‘将A1单元格的背景色改为红色

Range(“B2:D4”).Interior.Color=vbBlue ’将单元格区域B2:D4背景色改为绿色

Range(“E:E”).Interior.Color=RGB(0,0,255) ‘将E列单元格背景改为绿色

Range(“F2”,”G5”).Interior.Color=RGB(255,255,0) ‘将F2:G5区域背景色改为黄色。

End Sub

Interior属性的ColorIndex属性：也用于修改背景色的颜色，不同的是，ColorIndex通过一个数字修改背景色颜色，具体哪一个数字对应哪种种颜色，读者可以自己实现以下下列从程序。

Sub Col2()

Dim i As Integer, Rng1 As Range, Rng2 As Range

Set Rng1 = Range("A:A")

Set Rng2 = Range("B:B")

For i = 1 To 50

Rng1(i) = i

Rng2(i).Interior.ColorIndex = i

Next i

End Sub

程序输出了前50种颜色和其Index的对应关系。

Interior属性的Pattern属性

使用Pattern属性可以清除单元格或区域中的背景颜色，只需要将单元格的Interior.Pattern属性的值改为xlNone即可。

Font属性 Font属性用于修改单元格中的字体，可用属性有：Color，修改字体颜色；Bold，字体是否加粗；Italic，是否采用斜体，Size，设置字体大小，FontStyle，用于设置字体样式；Underline,是否使用下划线等。例如，将E2单元格字体设置为：红色、加粗、斜体、加下划线，字号为15的程序如下：

Sub Col2()

Range("E2").Font.Color = vbRed

Range("E2").Font.Bold = True

Range("E2").Font.Italic = True

Range("E2").Font.Size = 15

Range("E2").Font.Underline = True

End Sub

Formula属性: Range对象的的Formula属性的作用是在Range对象中运用Excel公式进行计算，相当于在Excel文本中的“插入公式”操作，例如，在单元格A6中，计算A1单元格到A4单元格中的和，程序为：

Sub F1()

Range("A6").Formula = "=Sum(A1:A5)"

End Sub

而要在A列到E列中，在第6行计算前5行单元格中的和可以用下列方式：

Sub F2()

Range(“A1:E6”).Rows(6).Formula=”=Sum(A1:A5)”

End Sub

通过Formula属性添加的公式，不会遇到之前提到的删除行或者列造成公式引用错误的情况。这里公式的书写方法和在Excel单元格中的书写方法是一致的，即加入$符号表示绝对引用。值得注意的是，这里在引号内外都有“=”号，引号外侧的“=”的用处是将这个公式作为一个“字符串”赋值给Range变量的Formula属性，而引号内部的“=”的作用是，告诉Excel这是一个公式，而不是一个字符串。

FormulaR1C1属性： FormulaRC1与Formula属性的作用是一致的，即向Excel单元格中添加公式，而不同之处在于，FormulaR1C1的方式是采用相对位置的方式引用单元格，还是以上述的程序为例子，如果采用FormulaR1C1形式，则书写方式为：

Sub F3()

Range(“A1:E6”).Rows(6).FormulaR1C1=”=Sum(R[-5]C[0]:R[-1]C[0])”

End Sub

解释一下这个R和C中括号中的数字的意思，R和C中括号里面的数字表示相对偏移量。在这个例子中，相对于A6来说，A1位于同一列前面的第5行，相对于A6来说，A1的行偏移量为-5，列偏移量为0，所以A1这里表示为R[-5]C[0]。同样的道理，A5表示为R[-1]C[0]。 在VBA中，如果偏移量为0也可以省略不写，上面两个可以直接写出R[-5]C和R[-1]C。中括号中的负数表示引用的单元格在当前单元格的上方或者左方,正数则表示引用的单元格位于目前的下侧或者右侧。

前面已经提到了Range对象的几种属性和方法，例如Copy、Clear、ClearFormats等，Range对象本身也有这些方法，例如前面的程序，直接使用了Range对象的Copy方法，而不是其Columns属性或者Rows属性的Copy方法，Range对象的Copy方法是复制整个Range对象，而Columns或者Rows属性的Copy方法只是复制Range对象中的某列或者某行。Range对象除了上述的属性和方法以外，还有一些属性和方法

Sort方法：Sort方法用于对Range对象按照列排序，Sort的用法如下：Sort key1:=Range对象1, order1:=方式1, key2:=Range对象2, order2=方式2……Header=……例如，对象区域A1:B5，按照A1列降序排序（没有标题）的程序为：

Sub S()

Range("A1:B5").Sort key1:=Range("A1"), order1:=xlDescending

End Sub

key表示以哪一列为排序对象，order为xlDescending是为降序排序，为xlAscending时为升序排序。

Merge方法：Merge方法可以合并Excel中的单元格，利用Range().Merge属性即可以将单元格区域中的单元格合并，合并后的单元格的地址，自动等于参与合并的单元格首个单元格的地址。例如，将A1:B5区域合并单元格，可以写成：

Sub M1()

Range(“A1:B5”).Merge

End Sub

MergeCells属性：如果单元格是合并以后的单元格，则单元格的Merge属性为Ture，否则为False，利用这个属性可以把已经合并的单元格进行拆分。例如，上面的命令将A1到B5单元格区域合并为一个单元格，现在需要拆分，只需要下列命令即可：

Sub M2()

Range(“A1”).MergeCells=False

End Sub

Resize属性：Resize属性返回一个改变大小的Range区域，Resize属性的用法是，Resize(行数,列数),表示以原来区域第一个单元格为标准，将原来Range区域改变为行数和列数的新区域，例如Range(“A1:B5”).Resize(3,5)，表示以原先单元格区域A1:B5第一个单元格A1为基准，将区域大小修改为3行和5列，修改后的区域为A1:E3。

图4.8 Range对象的Resize属性

利用Resize属性，求任意一个选定单元的每一行数字和。我们可以利用Range对象的Resize属性以及FormulaR1C1属性编写程序实现对选定单元每一行求和运算。

Sub AllSum()

Dim Rng As Range, Rng1 As Range

Dim N As Integer

Set Rng = Application.InputBox("选择区域", "进行求和", , , , , , 8)

N = Rng.Columns.Count

Set Rng1 = Rng.Resize(Rng.Rows.Count, Rng.Columns.Count 1)

Rng1.Columns(N 1).FormulaR1C1 = "=Sum(RC[-" & N & "]:RC[-1])"

End Sub

程序中，先使用Application.InputBox输入一个单元格区域，并把这个单元格区域赋值给Range变量Rng，然后再定义一个Range变量Rng1，让Rng1等于Rng列数增加1，行数不变的新区域。然后再利用FormulaRC1属性计算每一行的和，而对于FormulaRC1来说，因为用户输入的区域行数和列数都是不确定的，所以这里，第一个单元格的相对列偏移量应该等于之前Rng变量的列数的相反数，这里用一个变量N来对Rng变量的列数进制保存，然后利用字符串的& 符号将常量“RC[-“与列偏移的变量N相连。而最后一个单元格列偏移量一定等于-1。读者可以自己试着写一个求每一列和的程序。

Delete方法：Delete方法可以用于删除Excel表的某一行或者某一列，在运用这个方法的时候，有一个小小的陷阱，即在行删除之后，后面的行数会自动减去1，即删除前的第2行，在删除之后就变成了第1行。例如，下面程序不是删除Excel的1到20行，而是删除了第1、3、5、7、9……39行：

Sub Del()

Dim i As Integer

For i =1 to 20

Rows(i).Delete

Next i

End Sub

那么如何才能删除前20行呢？第一个方法，删除第一行，删除20遍。第二个方法，采用逆向删除，先删除1第20行，然后删除第19行……。

删除前20行方法1。

Sub Del20()

Dim i As Integer

For i =1 to 20

Rows(1).Delete

Next i

End Sub

删除前20行方法2

Sub Del20()

Dim i As Integer

For i =20 to 1 Step -1

Rows(i).Delete

Next i

End Sub

逆向删除时，由于删除操作不会对前面的行产生影响，所以可以用Rows(i).Delete

Seletion方法: Selection方法完成对Range对象的“选定”操作，这种操作可以允许使用一次性操作不同区域的Range对象。例如可以用Section方法计算选中单元格中所有数字的和。

Sub S()

Dim i , S1 As Double

For Each i In Selextion

S1=S1 i

Msgbox(S1)

最后讲解一下Range对象的Value属性，Range对象的Value属性即Range对象的值属性，即单元格或者单元格区域中的内容。Range对象的Value属性是Range对象的默认属性，以上对Range对象的赋值过程或者访问过程其实都是利用了Range对象的这个属性，只不过由于Value属性是默认属性，所以我们这里并没有见到例如Range(“A1”).Value=5这样的表达形式。

第六节 在Excel表中相隔的区域输入序号

这个问题出自于对电话号码的打印和保存，假设我们现在用一个Excel表格保存电话号码，当然你可以每一行只保存一个人的电话号码，这样做将非常浪费Excel表格的行，阅读起来也相对困难。现在我们在每一个电话号码的前面加上一个“序号“，把Excel表格按照列分成若干的区域，每一个区域只保存20个电话号码。即A列到C列保存前20个人的电话号码，A列编号从1到20，然后D列到F列保存21号到40号的电话号码，D列编号从21到40……如果我们现在有100个电话号码，呢么请问如何快速地在A列、D列、G列、J列、M列输入这100个数字呢。

4.6.1 思路分析

现在我们设一个Range变量，这个变量为3列，20行，然后让这个Range变量从A列开始向右侧移动5次，每移动一次，就向变量的第一列写入数字。

这个思路听上去很棒，实际上也确实是一个非常不错的思路，那么，我们如何来实现这个思路呢？实现这个思路，我们先来看一看需要哪些“材料“，

首先，我们得有一个Range变量，这个Range变量可以通过命令Dim Rng As Range来实现，这个不难。

其次，我们需要一个循环变量，来计数一下这个Rng共循环了多少次，我们用i这个变量对Rng循环的次数进行保存，并且当i的数值达到6时，不再循环。

随后，我们需要一个变量表示1到100这100个数字，因为对于每一个Rng以及Rng循环的每一次，都需要输入这个数字，并且保证每输入一个数，这个变量自动增加1。现在，还剩下一个问题：如何向每一个Rng中的第一列输入连续的数？

所以我们还需要一个变量，表示Rng变量第一列的每一行。可以用RowI这样的变量来表示。

最后让我们把这个思路给串联起来。

（1）定义Range变量，用于整个大循环，定义变量i用于保存循环进行了多少次。

（2）定义RowI变量，用于给每个大循环中的第一列进行循环。

（3）定义整数N，用于表示现在需要输入的数字。

将这些思想串联起来之后，可以写出以下程序。

4.6.2 动手写程序

Sub Insert()

Dim Rng As Range, i As Integer, RowI as Integer, N As Integer

Set Rng=Range(“A1:C20”)

i=1

N=1

For i=1 to 5

For RowI=1 to 20

Rng.Cells(RowI,1)=N

N=N 1

Next RowI

Set Rng=Rng.Offset(0,Rng.Columns.Count)

Next i

End Sub

这个程序看起来稍微比之前长了一些，但基本框架还是之前的那些，并且，其基础的命令也没有发生什么变化，这里，读者只需要知道这个For……Next是一种循环结构，循环结构将在第6章的部分详细讲解。这里看到，利用Offset属性将范围重新选定的方式确实发挥着超级高的效率。

4.6.3 另一个例子

在A列输入细胞培养的培养天数：在工作中，我们希望用一个Excel保存若干个摇瓶的细胞培养数据，由于整个Excel表格需要放不止一个摇瓶的细胞培养数据，所以我们想按照列的输入方式，在A列填入培养天数，剩下的按照摇瓶编号或者实验的先后顺序编号将数据输入Excel表格。这样A列的数据就需要反复输入，采用同上面思路一致的思路，现在假设我们培养天数需要连续重复输入20遍，每一遍的数字已经确认，为0到14，并且在每两遍之间空一行，那么，同样可以编写程序如下.

Sub Insert()

Dim i As Integer, j AS Integer,Rng As Range

Set Rng=Range(“A1:A15”)

For i =1 to 20

For j=1 to 15

Rng(j)=j-1

Next j

Set Rng=Rng.Offset(Rng.Rows.Count 1,0)

Next i

End Sub

这里可以少一个变量的原因是，我们每一次的输入都是从0重新开始，对于每一个Rng，第一个数字总等于0，第二个数字总等于1…… 所以，Rng的第j个数总等于j-1。

第七节 UsedRange与CurrentRegion对象

4.7.1 UsedRange对象

让我们来看一下这样一个问题，问题：将Excel工作表Sheet1中已有数据的列复制到与其相邻的区域。

聪明的读者已经认识到了，题目中并没有告诉我这个Excel表格里面那些列被写入了数据，我怎么定义这个变化的单元格区域对象呢？

在VBA中，UsedRange对象为当前表格使用的区域，这个区域的定义是这样的，不管区域中有多少空格，以左上和右下两个顶端有数据的单元格为界，都是UsedRange区域。

图4.9 UsedRange

也就是说，在上面这个图中，UsedRange起始的列为不是空列的第一列，即B列，结束的列为非空列的最后一列，即G列，开始的行和结束的行也是非空的第一行和最后一行。加入在J10的位置再写上数据，那么这个UsedRange就变成B2:J10。

有了UsedRange对象，我们就可以完成上述的任务了。

Sub U()

Dim Rng As Range

Set Rng=Sheet1.UsedRange.Offset(0,Sheet1.UsedRange.Columns.Count)

Sheet1.UsedRange.Copy Rng

End Sub

这里要注意的是，使用UsedRange时，一定要指明是在哪个工作表中，即使只有一个工作表也需要这样写！

4.7.2 CurrentRegion对象

与UsedRange对象不同的是，CurrentRegion对象返回Range对象中连续的列和连续的行组成的连续的“区域”。例如Range(“A1”).CurrentRegion的意思是，以A1单元格为基准的，具有数据的连续列和连续的行组成的区域。

图4.10 A1单元格的CurrentRegion

可以利用CurrentRegion选择有连续数据的单元格（即相邻的行或者列中单元格有数据），例如，求A1单元格有连续数据的单元格中的数字之和（在上图中是求方块中的数字，不计算方框外面的这个E4单元格。

Sub C()

Dim S As Integer, i

For Each i In Range(“A1”).CurrentRegion

S=S i

Next i

MsgBox(S)

End Sub

第八节 Range对象的 Union、Intersect与End

4.8.1 Range对象的合集Union

Union的作用是将不连续的Range区域选择作为一个“整体”，将不连续的单元格区域作为整体操作的方式也可以用Range的方法来实现，例如：Range(“A1:B5”,”D4:E6”)，为A1:B5和D4:E6两个不连续的单元格区域，用Union方法表示就是Application.Union(Range(“A1:B5”),Range(“D4:E6”))。下面将不连续的两个区域背景色改为红色。

Sub U2()

Application.Union(Range(“A1:B5”),Range(“D4:E6”)).Interior.Color=vbRed

End Sub

4.8.2 Range对象的交集Intersect

Intersect的作用是求两个Range变量的交集。例如，Intersect(Range(“A1:F5”),Range(“E3:G7”))，结果是A1:F5区域与E3:G7区域的 “交集”即E3:F5区域。

图4.11 两个单元格区域的“交集”

利用Instersect求两个区域的交集，可以完成一些“看似不可能”完成的任务。例如，求用户选择的区域与Excel表中前两列交叉范围内的数字之和。由于不能确定用户只选取了前两列的数据，而计算中，两列后的数据对于计算是没有作用的。所以，这里要用Intersect。

Sub Calc()

Dim Rng1 As Range, Rng2 As Range, s As Double, i

Set Rng1 = Range("A:B")

Set Rng2 = Application.InputBox("选择区域", "", , , , , , 8)

Set Rng2 = Intersect(Rng1, Rng2)

For Each i In Rng2

s = s i

Next i

MsgBox (s)

End Sub

4.8.3 End属性

Range对象的的End属性返回该对象所在行和列的“边缘”，其中可以有4个选项，即xlDown、xlUp、xlToLeft、xlToRight分别表示上、下、左、右边缘。读者可以任意用一组数据（记住B3单元格周围有数据实验一下效果）

Sub E()

Range("B3").End(xlDown).Interior.Color = vbRed

Range("B3").End(xlUp).Interior.Color = vbRed

Range("B3").End(xlToLeft).Interior.Color = vbRed

Range("B3").End(xlToRight).Interior.Color = vbRed

End Sub

将B3单元格不为空且上下左右“边缘”背景改为红色。这里可以看到，产生的效果是，以B3单元格所在的单元格为基准，向上、向下、向左、向右找到连续有数字的“最后一个”单元格。

还有一点，如果选择了一个空的单元格，那么寻找结果为第一个不为空的单元格。

,