Excel 单元格操作.docx
《Excel 单元格操作.docx》由会员分享,可在线阅读,更多相关《Excel 单元格操作.docx(100页珍藏版)》请在冰豆网上搜索。
Excel单元格操作
第3章单元格的基本操作
在本章中,将详细讲解如何操作Excel中的单元格对象。
在Excel中,单元格对象是用户经常需要处理的对象。
同时单元格对象也是用户操作Excel的最小单元。
在本章中,将对涉及到单元格的常见操作进行介绍。
3.1获取单元格的引用
在ExcelVBA中,经常要使用到对单元格的引用,然后使用属性和方法对区域进行操作。
根据单元格区域的使用属性不同,主要包括以下几种情况:
•单个单元格;
•多个连续或者不连续单元格组成的区域;
•整行或整列。
用户如果希望使用VBA代码对单元格区域进行操作,就必须用将单元格区域赋值给某个变量,也就是首先获得某个单元格区域的引用。
案例17使用A1样式引用单元格
1.功能说明
在使用Excel中,A1样式是用户最熟悉的一种引用样式。
A1样式其实就是列名和行名的组合,确定对应的单元格。
在ExcelVBA中,当用户需要对单元格进行操作的时候,需要首先使用代码引用单元格。
2.语法说明
在VBA中,通过Range对象的Range属性返回Range对象。
Range属性返回一个单元格或单元格区域,对区域的引用如果使用A1样式,需将引用字符串包含在引号中。
另外还可以使用以下方式引用单元格:
•[A3]:
引用单元格“A3”;
•ActiveCell:
当前单元格。
在本例中,因为引用的单元格为对象,因此需要创建对象变量。
创建对象变量通常分
两个步骤:
(1)声明对象变量。
与声明普通变量类似,用户可以使用Dim语句或其他的声明语句之一来声明对象变量。
引用对象的变量必须是Variant、Object,或是一个对象的指定类型。
下面的声明在Excel中都是有效的:
(2)赋值对象变量给对象。
在ExcelVBA中,需要使用Set语句赋值对象给对象变
量,可以赋值对象表达式或是Nothing。
下面的赋值语句在ExcelVBA中是有效的:
设置对象变量为Nothing,会中断此对象变量与其他对象的关联,可预防因意外改变
变量而更改对象。
在关闭关联对象后,对象变量总是设置为Nothing,所以可以检测对象变量是否指到有效的对象。
3.案例说明
本例中,用户需要首先引用单元格D3,然后在对应的单元格中添加字符串“IloveExcelVBA”。
4.编写代码
实现A1样式引用的代码如下:
SubForCell()
DimRngCellAsRangeSetRngCell=Range("D3")
WithRngCell
.Value="IloveExcelVBA"
.Font.Name="Tahoma"
.Font.Italic=TrueEndWith
SetRngCell=NothingEndSub
5.运行结果
运行程序代码,得到的结果如图3.1所示。
图3.1A1样式引用结果
6.程序分析
在上面的代码中,通过下面的代码段:
DimRngCellAsRangeSetRngCell=Range("D3")
定义了Range变量,然后通过Range("D3")获取对单元格D3的引用。
案例18使用R1C1样式引用单元格
1.功能说明
在Excel中,R1C1的引用格式在开发过程中也是经常用到的。
在很多情况下,使用单元格的行列序号来引用单元格,会给程序开发带来更大的便利。
例如,当用户需要在单元格区域内进行循环设置的时候,使用R1C1样式就便利很多。
因为,对于循环而言,数字会更加便利,而A1样式中的列名字母则不适合循环。
2.语法说明
Excel的工作表由行和列构成。
通过使用行列索引号,可用Cells属性引用单个单元格。
该属性返回代表单个单元格的Range对象。
Cells(3,2)返回对工作表中单元格B3的引用。
同时,R1C1样式可以使用多种引用方式:
绝对引用、相对引用和混合引用,得到的结果都是一样的。
因此,用户在实际的编程中,可以根据情况选择合适的方法。
使用Cells属性引用单元格时,用户可以使用变量替代行列索引号,所以Cells属性适合在单元格区域中循环。
另外,使用Cells属性还可按以下方式引用单元格区域:
Cells(2,"B"):
表示第2行B列
如果使用Cells属性时,不指定行列索引号,程序将返回工作表上所有单元格的Range
对象。
3.案例说明
运行本例的效果如图4-2所示,单击“使用索引号引用单元格”按钮,将在单元格区域“A1:
E10”中按顺序填充数字。
4.编写代码
R1C1样式的代码如下:
SubR1C1_cells()DimiAsIntegerDimjAsInteger
Fori=1To4
Forj=1To4
Cells(i,j).Value=(i-1)*4+jNext
Next
EndSub
5.运行结果
运行程序代码,得到的结果如图3.2所示。
图3.2R1C1样式引用单元格
6.程序分析
从上面的例子中可以看出,当用户使用R1C1的样式引用单元格时,可以很方便的使用循环结构,进行各种复杂的运算。
案例19引用多个单元格区域
1.功能说明
在使用Excel分析数据或者处理问题时,有时需要同时选择多个单元格,然后再进行处理。
同时,这些单元格区域在范围上可能并不连续,因此需要使用特殊方法引用单元格区域。
当单元格区域范围比较多,同时比较杂乱,没有规律的时候,单个单元格区域依次选择,会很耽误效率。
因此,需要使用特定的语法对多个单元格区域进行引用。
2.语法说明
在ExcelVBA中,可以使用两种法选择单元格区域。
如果是连续的单元格区域,可以使用左上角和右下角的单元格来确定区域。
例如,使用下面的代码:
Range("B2:
D3")
表示引用的是单元格B2:
D3的区域。
如果是不连续的单元格区域,可以选择使用Range
属性。
其中,使用Range属性的方法是,用逗号将不同的单元格区域隔开,例如:
Range("B2:
D3,E4:
F8")
在上面的代码中,逗号必须在引号的内部。
3.案例说明
在本例中,用户需要通过程序代码同时选中单元格中多个不连续的单元格区域,然后设置这些单元格区域的填充红色。
4.编写代码
本例代码如下:
Subselect_range()DimrngAsRange
Setrng=Range("A1:
B4,D1:
G6,B8:
C9")
rng.SelectSelection.Interior.ColorIndex=3
Setrng=NothingEndSub
5.运行结果
运行程序代码,得到的结果如图3.3所示。
图3.3选择多个单元格区域
6.程序分析
当用户使用代码Range("A1:
B3","D4:
E5")时,程序选择的单元格区域是A1:
E5,即以第一个区域左上角单元格为起点,第二个区域右下角单元格为终点,连接成一个新的连续区域。
案例20合并单元格区域
1.功能说明
前面的案例说明了如何使用Range属性来引用多个单元格区域。
在ExcelVBA中,用户还可以使用Union方法来合并不同的单元格区域。
在本小节中,将详细讲解如何使用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,Arg25,Arg26,Arg27,Arg28,Arg29,Arg30)
用户在使用Union方法时,至少需要两个单元格区域,最多可合并30个单元格区域。
例如:
Union(Range("A1:
C3"),Range("E1:
F5"))
3.案例说明
在本例中,用户需要引用多个单元格区域,然后在引用区域的单元格中添加随机数。
4.编写代码
合并单元格区域的具体代码如下:
Subunion_range()
Dimrng1AsRangeDimrng2AsRangeDimrng3AsRange
Setrng1=Range("A1:
B3")Setrng2=Range("D4:
F6")Setrng3=Union(rng1,rng2)
rng3.Formula="=int(100*RAND()+20)"EndSub
5.运行结果
运行程序代码,得到的结果如图3.4所示。
图3.4合并单元格区域的结果
6.程序分析
在本例的代码中,首先使用两个对象变量获取单元格区域,然后使用Union方法将两个区域合并为一个区域。
案例21引用合并区域的子区域
1.功能说明
当用户同时选中多个单元格区域时,某些操作不能在选定区域内同时执行,必须在选定区域内的单个子区域上循环,对每个单独的子区域分别执行该操作。
因此,这个时候有必要引用合并区域的子区域。
2.语法说明
在ExcelVBA中,用户选定多个区域后,将生成Areas集合。
Areas集合的每个成员是Range对象。
选定区域内每个离散的连续单元格区域都有Range对象。
如果选定区域内只有一个子区域,则Areas集合包含一个与该选定区域对应的Range对象。
使用Areas(index)可从集合中返回单个Range对象。
该索引号对应选定这些区域的顺序。
下例中,如果当前选定区域包含多个子区域,就清除该选定区域中的第一个子区域。
3.案例说明
选择工作表中的多个单元格式区域,将其单元格区域进行合并,然后依次遍历合并后的单元格区域的各个子区域。
4.编写代码
引用合并区域的子区域的具体代码如下:
SubGet_Part_rubrange()Dimrng1AsRangeDimrng2AsRange
DimiAsIntegerDimstrAsString
Setrng1=Range("A1:
C2,B3:
D4,E4:
F6")
rng1.Select
Fori=1Torng1.Areas.CountSetrng2=rng1.Areas(i)
str="子区域"&i&"的地址是:
"&vbCr&vbCr
str=str&rng2.AddressMsgBoxstr
Next
Setrng1=NothingSetrng2=Nothing
EndSub
5.运行结果
运行程序代码,得到的结果如图3.5所示。
图3.5遍历第一个子区域的结果
单击对话框中的“确定”按钮,然后依次运行程序代码,得到的最后一个子区域的结果如图3.6所示。
图3.6遍历最后一个子区域的结果
6.程序分析
在本例的代码中,Areas集合包括三个子区域,通过循环分别显示每个子区域的地址,然后显示在对话框中。
案例22当前单元格的前一单元格
1.功能说明
Excel表格是一个二维结构,所有的单元格组成了一个工作表。
这些单元格在物理位置上是相互关联的:
前、后、左和右等。
这些都是相邻单元格的区域关系,在Excel中,用户通过VBA代码还可以处理非相邻的单元格物理关系。
在本小节中,将首先讲解如何引用当前单元格的前一单元格。
2.语法说明
使用Range对象的Previous属性,可获取对指定单元格的前一个单元格的引用。
其语法格式如下:
表达式.Previous
如果表达式为一个对象为区域,则Previous属性会模拟Shift+Tab键,但此属性只是返回上一单元格,并不选定它。
3.案例说明
某公司统计了部分员工上、下两半年销量,同时提供了员工所处的地区。
本例中,用户在选中某单元格后,需要查看该单元格的前面一个单元格数据。
原始数据如图3.7所示。
图3.7原始数据
4.编写代码
获取当前单元格前一单元格的引用的代码如下:
Subpre_cells()
DimrngAsRange
Setrng=ActiveCell.Previous
MsgBox"当前单元格的前一单元格的值为:
"&rng1.Value
Setrng=NothingEndSub
5.运行结果
选择单元格D6,运行程序代码,得到的结果如图3.8所示。
图3.8运行结果
6.程序分析
在上面的程序代码中,通过Previous属性获取了前一单元格的引用,然后显示该单元格的数值。
案例23当前单元格的后一单元格
1.功能说明
前面小节的例子中,已经详细介绍了如何获取当前单元格的前一单元格的引用。
类似的,Excel中同样可以引用当前单元格的后一单元格。
在本节中,将详细介绍如何获取当前单元格的后一单元格。
2.语法说明
使用Range对象的Next属性,可获取对指定单元格的后一个单元格的引用。
其语法格式如下:
表达式.Next
如果表达式为一个对象为区域,则Next属性会模Tab键,但此属性只是返回下一单元格,并不选定它。
3.案例说明
某公司统计了部分员工上、下两半年销量,同时提供了员工所处的地区。
本例中,用户在选中某单元格后,需要查看该单元格的后面一个单元格数据。
原始数据如图3.9所示。
图3.9原始数据
4.编写代码
取当前单元格的后一单元格的引用的代码具体如下:
Subnext_cells()
DimrngAsRange
Setrng=ActiveCell.Next
MsgBox"当前单元格的后一单元格的值为:
"&rng.ValueSetrng=Nothing
EndSub
5.运行结果
选择单元格C7,运行程序代码,得到的结果如图3.10所示。
图3.10运行结果
6.程序分析
在上面的程序代码中,通过Next属性获取了后一单元格的引用,然后显示该单元格的数值。
案例24引用整行或者整列单元格
1.功能说明
在使用ExcelVBA进行数据处理的时候,经常会需要对某列或某行单元格进行设置的情况。
这个使用,用户就需要首先选择整行或者整列单元格区域。
2.语法说明
在ExcelVBA中,可以使用多种方法选择整行或者整列。
首先,可用Rows属性或Columns属性来处理整行或整列。
这两个属性返回代表单元格区域的Range对象。
在下面的代码中,将返回工作表的第二行,然后将区域字体加粗。
Worksheets("Sheet1").Rows
(2).Font.Bold=True
下面列出了使用Rows和Columns属性的一些行和列的引用。
同时,用户可以直接使用Range属性来返回整行或者整列单元格,如下:
3.案例说明
某公司统计了部分员工上、下两半年销量,同时提供了员工所处的地区。
本例中,需要设置工作表的最后一行的格式,原始数据如图3.11所示。
图3.11原始数据
4.编写代码
获取整行单元格引用的代码具体如下:
SubAll_RowCol()
Range("11:
11").SelectWithSelection.Font
.Bold=True
.Italic=TrueEndWith
EndSub
5.运行结果
运行程序代码,得到的结果如图3.12所示。
图3.12运行结果
6.程序分析
在上面的程序代码中,通过代码“Range("11:
11").Select”引用了第11行的所有数据。
案例25引用相对其他单元格的单元格
1.功能说明
前面小节已经讲解了多种不同的引用方法。
使用不同的引用样式,用户可以应用不同的单元格区域。
其中,以某单元格为基准单元格,然后根据偏移数引用其他单元格应用十分广泛。
理论上分析,使用这种方法可以引用任何相对位置。
2.语法说明
在ExcelVBA中,用户可以使用Range对象的Offset属性返回Range对象,代表位于指定单元格区域的一定的偏移量位置上的区域。
其使用格式为:
Offset(RowOffset,ColumnOffset)
两个参数的含如下:
•RowOffset为行偏移量,区域偏移的行数可为正数、负数或0(零)。
正数表示向下偏移,负数表示向上偏移。
默认值是0;
•ColumnOffset为列偏移量,区域偏移的列数可为正数、负数或0(零)。
正数表示向右偏移,负数表示向左偏移。
默认值是0。
例如下面的程序代码:
3.案例说明
某公司统计了部分员工上、下两半年销量,同时提供了员工所处的地区。
根据用户选择的单元格,设置该单元格右下方单元格的背景。
原始数据如图3.13所示。
图3.13原始数据
4.编写代码
引用相对其他单元格的单元格的具体代码如下:
SubOffset_Cells()DimrngAsRange
Setrng=ActiveCell.Offset(1,1)rng.SelectSelection.Interior.ColorIndex=3Setrng=Nothing
EndSub
5.运行结果
选择工作表的单元格C5,如图3.14所示。
图3.14选中单元格
运行程序代码后,得到的程序结果如图3.15所示。
图3.15运行的程序代码结果
6.程序分析
在上面的程序代码中,是以当前单元格为基准,所以每执行一次上面的子过程,当前单元格就向下移动三行、向右移动三列。
案例26引用当前区域
1.功能说明
当前区域是Excel工作表中一个十分特殊的单元格区域,是指以空行与空列的组合为边界的区域。
当前区域这个概念在数据处理领域使用的十分普遍,特别是当用户需要添加、编辑或者修改当前数据的时候。
在工作表中,选择“开始”|“编辑”|“查找和选择”|“定位条件”命令,在“定位条件”对话框中,选中“当前区域”选项按钮,也能获取关于当前区域的信息,如图3.16所示。
图3.16选择“当前区域”选项
2.语法说明
在ExcelVBA中,使用Range对象的CurrentRegion属性,可返回表示当前区域的Range
对象。
其语法格式如下:
表达式.CurrentRegion
该属性对于许多自动展开选择区域,以包括整个当前区域的操作很有用。
3.案例说明
某公司统计了部分员工上、下两半年销量,同时提供了员工所处的地区。
现在需要通过VBA代码获取当前区域的信息。
原始数据如图3.17所示。
图3.17原始数据
4.编写代码
引用当前区域的具体代码如下:
SubCurrent_Region()DimrngAsRange
Setrng=Range("A1")
Setrng=rng.CurrentRegionrng.Select
Setrng=Nothing
EndSub
5.运行结果
运行程序代码,得到的结果如图3.18所示。
图3.18引用当前区域
6.程序分析
在当前区域范围内,无论活动单元格是哪一个单元格,所在的当前区域均为同一区域。
如上例中的A1:
D11区域,活动单元格B2的当前区域为A1:
D11,当活动单元格为D2时,其当前区域仍为A1:
D11。
案例27引用已使用区域
1.功能说明
已使用区域在Excel中是一个十分常用的概念,其主要功能是显示当前使用的单元格的范围区域。
和当前区域不同,已使用区域表示的是所有工作表中所使用单元格的区域。
2.语法说明
在ExcelVBA中,UsedRange属性返回指定工作表中已使用区域的Range对象,即返回工作表中已使用的单元格区域。
因此,该属性也可以用于选取单元格区域。
UsedRange属性与上例中的CurrentRegion属性的区别:
•UsedRange属性是Worksheet对象的一个属性,返回指定工作表中所有已使用单元格区域,无论各单元格之间是否有空行或空列隔开。
•CurrentRegion属性是Range对象的一个属性,返回的是一个由空行空列围起来的区域,空行空列之外的单元格不被包含在内。
3.案例说明
某公司统计了部分员工上、下两半年销量,同时提供了员工所处的地区。
现在需要通过VBA代码获取已使用区域的信息。
原始数据如图3.19所示。
图3.19原始数据
4.编写代码
选择已使用区域的程序代码如下:
Subused_range()
DimrngAsRange
Setrng=ActiveSheet.UsedRangerng.Select
Setrng=Nothing
EndSub
5.运行结果
运行程序代码,得到的结果如图3.20所示。
图3.20选择已使用区域
6.程序分析
当用户使用UsedRange属性来获取已使用的区域时,可以通过Address属性返回该区域的地址。
例如,在上面的程序代码中添加下面的代码:
MsgBoxrng.Address
运行程序代码后,会显示对应的地址,如图3.21所示。
图3.21显示区域的地址
案例28调整单元格区域的大小
1.功能说明
在前面小节中,用户已经了解了如何使用VBA来引用单元格区域的方法。
在实际应用中,用户可能在程序代码中需要调整引用的单元格区域。
这个时候,用户就需要首先调整单元格区域的大小。
在本小节中,将详细讲解如何调整单元格区域的大小。
2.语法说明
在ExcelVBA中,使用Range对象的Resize属性,可以调整指定单元格区域的大小,并返回一个Range对象,该对象代表调整后的区域。
其语法格式如下:
表达式.Resize(RowSize,ColumnSize)
两个参数的含义如下:
•RowSize为新区域中的行数。
如果省略该参数,则该区域中的行数保