VBA常用技巧第1部分.docx
《VBA常用技巧第1部分.docx》由会员分享，可在线阅读，更多相关《VBA常用技巧第1部分.docx（67页珍藏版）》请在冰豆网上搜索。
VBA常用技巧第1部分
VBA常用技巧
目录
第1章Range（单元格）对象3
技巧1单元格的引用方法3
1-1使用Range属性3
1-2使用Cells属性4
1-3使用快捷记号4
1-4使用Offset属性5
1-5使用Resize属性6
1-6使用Union方法7
1-7使用UsedRange属性7
1-8使用CurrentRegion属性8
技巧2选定单元格区域的方法8
2-1使用Select方法8
2-2使用Activate方法9
2-3使用Goto方法10
技巧3获得指定行、列中的最后一个非空单元格10
技巧4定位单元格13
技巧5查找单元格14
5-1使用Find方法14
5-2使用Like运算符18
技巧6替换单元格内字符串19
技巧7复制单元格区域20
技巧8仅复制数值到另一区域23
8-1使用选择性粘贴23
8-2直接赋值的方法24
技巧9单元格自动进入编辑状态25
技巧10禁用单元格拖放功能25
技巧11单元格格式操作26
11-1单元格字体格式设置26
11-2设置单元格内部格式28
11-3为单元格区域添加边框29
11-4灵活设置单元格的行高列宽31
技巧12单元格中的数据有效性32
12-1在单元格中建立数据有效性32
12-2判断单元格是否存在数据有效性33
12-3动态的数据有效性34
12-4自动展开数据有效性下拉列表36
技巧13单元格中的公式37
13-1在单元格中写入公式37
13-2检查单元格是否含有公式38
13-3判断单元格公式是否存在错误39
13-4取得单元格中公式的引用单元格40
13-5将单元格中的公式转换为数值41
技巧14单元格中的批注42
14-1判断单元格是否存在批注42
14-2为单元格添加批注43
14-3删除单元格中的批注44
技巧15合并单元格操作45
15-1判断单元格区域是否存在合并单元格45
15-2合并单元格时连接每个单元格的文本46
15-3合并内容相同的连续单元格47
15-4取消合并单元格时在每个单元格中保留内容49
技巧16高亮显示单元格区域50
技巧17双击被保护单元格时不显示提示消息框51
技巧18重新计算工作表指定区域53
技巧19录入数据后单元格自动保护53
技巧20工作表事件Target参数的使用方法55
20-1使用单元格的Address属性55
20-2使用Column属性和Row属性56
20-3使用Intersect方法56
第1章Range（单元格）对象
Range对象是Excel应用程序中最常用的对象，一个Range对象代表一个单元格、一行、一列、包含一个或者更多单元格区域（可以是连续的单元格，也可以是不连续的单元格）中选定的单元格，甚至是多个工作表上的一组单元格，在操作Excel内的任何区域之前都需要将其表示为一个Range对象，然后使用该Range对象的方法和属性。
技巧1单元格的引用方法
在VBA中经常需要引用单元格或单元格区域区域，主要有以下几种方法。
1-1使用Range属性
VBA中可以使用Range属性返回单元格或单元格区域，如下面的代码所示。
#001SubRngSelect（）
#002Sheet1.Range（"A3:
F6,B1:
C5"）.Select
#003EndSub
代码解析：
RngSelect过程使用Select方法选中A3:
F6，B1:
C5单元格区域。
Range属性返回一个Range对象，该对象代表一个单元格或单元格区域，语法如下：
Range（Cell1,Cell2）
参数Cell1是必需的，必须为A1样式引用的宏语言，可包括区域操作符（冒号）、相交区域操作符（空格）或合并区域操作符（逗号）。
也可包括美元符号（即绝对地址，如“$A$1”）。
可在区域中任一部分使用局部定义名称，如Range（"B2:
LastCell"），其中LastCell为已定义的单元格区域名称。
参数Cell2是可选的，区域左上角和右下角的单元格。
运行SubRngSelect过程，选中A3:
F6,B1:
C5单元格区域，如图11所示。
图11使用Range属性引用单元格区域
注意如果没有使用对象识别符，Range属性返回活动表的一个区域，如果活动表不是工作表，则该属性无效。
1-2使用Cells属性
使用Cells属性返回一个Range对象，如下面的代码所示。
#001SubCell（）
#002DimicellAsInteger
#003Foricell=1To100
#004Sheet2.Cells（icell,1）.Value=icell
#005Next
#006EndSub
代码解析：
Cell过程使用For...Next语句为工作表中的A1:
A100单元格区域填入序号。
Cells属性指定单元格区域中的单元格，语法如下：
Cells（RowIndex,ColumnIndex）
参数RowIndex是可选的，表示引用区域中的行序号。
参数ColumnIndex是可选的，表示引用区域中的列序号。
如果缺省参数，Cells属性返回引用对象的所有单元格。
Cells属性的参数可以使用变量，因此经常应用于在单元格区域中循环。
1-3使用快捷记号
在VBA中可以将A1引用样式或命名区域名称使用方括号括起来，作为Range属性的快捷方式，这样就不必键入单词“Range”或使用引号，如下面的代码所示。
#001SubFastmark（）
#002[A1：
A5]=2
#003[Fast]=4
#004EndSub
代码解析：
Fastmark过程使用快捷记号为单元格区域赋值。
第2行代码使用快捷记号将活动工作表中的A1：
A5单元格赋值为2。
第3行代码将工作簿中已命名为“Fast”的单元格区域赋值为4。
注意使用快捷记号引用单元格区域时只能使用固定字符串而不能使用变量。
1-4使用Offset属性
可以使用Range对象的Offset属性返回一个基于引用的Range对象的单元格区域，如下面的代码所示。
#001SubOffset（）
#002Sheet3.Range（"A1:
C3"）.Offset（3,3）.Select
#003EndSub
代码解析：
Offset过程使用Range对象的Offset属性选中A1：
A3单元格偏移三行三列后的区域。
应用于Range对象的Offset属性的语法如下：
expression.Offset（RowOffset,ColumnOffset）
参数expression是必需的，该表达式返回一个Range对象。
参数RowOffset是可选的，区域偏移的行数（正值、负值或0（零））。
正值表示向下偏移，负值表示向上偏移，默认值为0。
参数ColumnOffset是可选的，区域偏移的列数（正值、负值或0（零））。
正值表示向右偏移，负值表示向左偏移，默认值为0。
运行Offset过程，选中A1：
A3单元格偏称三行三列后的区域，如图12所示。
图12使用Range对象的Offset属性
1-5使用Resize属性
使用Range对象的Resize属性调整指定区域的大小，并返回调整大小后的单元格区域，如下面的代码所示。
#001SubResize（）
#002Sheet4.Range（"A1"）.Resize（3,3）.Select
#003EndSub
代码解析：
Resize过程使用Range对象的Resize属性选中A1单元格扩展为三行三列后的区域。
Resize属性的语法如下：
expression.Resize（RowSize,ColumnSize）
参数expression是必需的，返回要调整大小的Range对象
参数RowSize是可选的，新区域中的行数。
如果省略该参数，则该区域中的行数保持不变。
参数ColumnSize是可选的，新区域中的列数。
如果省略该参数。
则该区域中的列数保持不变。
运行Resize过程，选中A1单元格扩展为三行三列后的区域，如图13所示。
图13使用Resize属性调整区域大小
1-6使用Union方法
使用Union方法可以将多个非连续区域连接起来成为一个区域，从而可以实现对多个非连续区域一起进行操作，如下面的代码所示。
#001SubUnSelect（）
#002Union（Sheet5.Range（"A1:
D4"）,Sheet5.Range（"E5:
H8"））.Select
#003EndSub
代码解析：
UnSelect过程选择单元格A1:
D4和E5:
H8所组成的区域。
Union方法返回两个或多个区域的合并区域，语法如下：
expression.Union（Arg1,Arg2,...）
其中参数expression是可选的，返回一个Application对象。
参数Arg1,Arg2,...是必需的，至少指定两个Range对象。
运行UnSelect过程，选中单元格A1:
D4和E5:
H8所组成的区域，如图14所示。
图14使用Union方法将多个非连续区域连接成一个区域
1-7使用UsedRange属性
使用UsedRange属性返回指定工作表上已使用单元格组成的区域，如下面的代码所示。
#001SubUseSelect（）
#002Sheet6.UsedRange.Select
#003EndSu
代码解析：
UseSelect过程使用UsedRange属性选择工作表上已使用单元格组成的区域，包括空单元格。
如工作表中已使用A1单元格和D8单元格，运行UseSelect过程将选择A1到D8单元格区域，如图15所示。
图15使用UsedRange属性选择已使用区域
1-8使用CurrentRegion属性
使用CurrentRegion属性返回指定工作表上当前的区域，如下面的代码所示。
#001SubCurrentSelect（）
#002Sheet7.Range（"A5"）.CurrentRegion.Select
#003EndSub
代码解析：
CurrentSelect过程使用CurrentRegion属性选择工作表上A5单元格当前的区域，当前区域是一个边缘是任意空行和空列组合成的范围。
运行CurrentSelect过程将选择A5到B6单元格区域，如图16所示。
图16CurrentRegion属性选择当前的区域
技巧2选定单元格区域的方法
2-1使用Select方法
在VBA中一般使用Select方法选定单元格或单元格区域，如下面的代码所示。
#001SubRngSelect（）
#002Sheet3.Activate
#003Sheet3.Range（"A1：
B10"）.Select
#004EndSub
代码解析：
RngSelect过程使用Select方法选定Sheet3中的A1：
B10单元格区域，Select方法应用于Range对象时语法如下：
expression.Select（Replace）
参数expression是必需的，一个有效的对象。
参数Replace是可选的，要替换的对象。
使用Select方法选定单元格时，单元格所在的工作表必需为活动工作表，所以在第2行代码中先使用Activate方法使Sheet3成为活动工作表，否则Select方法有可能出错，显示如图21所示的错误提示。
图21Select方法无效提示
2-2使用Activate方法
还可以使用Activate方法选定单元格或单元格区域，如下面的代码所示。
SubRngActivate（）
Sheet3.Activate
Sheet3.Range（"A1:
B10"）.Activate
EndSub
代码解析：
RngActivate过程使用Activate方法选定Sheet3中的A1：
B10单元格区域，Activate方法应用于Range对象时语法如下：
expression.Activate
使用Activate方法选定单元格时，单元格所在的工作表也必需为活动工作表，否则Activate方法有可能出错，显示如图22所示的错误提示。
图22Activate方法无效提示
2-3使用Goto方法
使用Goto方法无需使单元格所在的工作表成为活动工作表，如下面的代码所示。
#001SubRngGoto（）
#002Application.GotoReference:
=Sheet3.Range（"A1：
B10"）,scroll:
=True
#003EndSub
代码解析：
RngGoto过程使用Goto方法选定Sheet3中的A1：
B10单元格区域，并滚动工作表以显示该单元格。
Goto方法选定任意工作簿中的任意区域或任意VisualBasic过程，并且如果该工作簿未处于活动状态，就激活该工作簿，语法如下：
expression.Goto（Reference,Scroll）
参数expression是必需的，返回一个Application对象。
参数Reference是可选的，Variant类型，指定目标。
可以是Range对象、包含R1C1-样式记号的单元格引用的字符串或包含VisualBasic过程名的字符串。
如果省略本参数，目标将是最近一次用Goto方法选定的区域。
参数Scroll是可选的，Variant类型，如果该值为True，则滚动窗口直至目标区域的左上角单元格出现在窗口的左上角。
如果该值为False，则不滚动窗口。
默认值为False。
技巧3获得指定行、列中的最后一个非空单元格
使用VBA对工作表进行操作时，经常需要定位到指定行或列中最后一个非空单元格，此时可以使用Range对象的End属性，在取得单元格对象后便能获得该单元格的相关属性，如单元格地址、行列号、数值等，如下面的代码所示。
#001SubLastRow（）
#002DimrngAsRange
#003Setrng=Sheet1.Range（"A65536"）.End（xlUp）
#004MsgBox"A列中最后一个非空单元格是"&rng.Address（0,0）_
#005&",行号"&rng.Row&",数值"&rng.Value
#006Setrng=Nothing
#007EndSub
代码解析：
LastRow过程使用消息框显示工作表中A列最后非空单元格的地址、行号和数值。
End属性返回一个Range对象，该对象代表包含源区域的区域尾端的单元格。
等同于按键、、或，语法如下：
expression.End（Direction）
参数expression是必需的，一个有效的对象。
参数Direction是可选的，所要移动的方向，可以为表格31所示的XlDirection常量之一。
常量
值
描述
xlDown
-4121
向下
xlToRight
-4161
向右
xlToLeft
-4159
向左
xlUp
-4162
向上
表格31XlDirection常量
Range对象的End属性返回的是一个Range对象，因此可以直接使用该对象的属性和方法。
运行LastRow过程结果如图31所示。
图31获得A列最后一个非空单元格
通过修改相应的参数，能够获得指定行中最后一个非空单元格，如下面的代码所示。
#001SubLastColumn（）
#002DimrngAsRange
#003Setrng=Sheet1.Range（"IV1"）.End（xlToLeft）
#004MsgBox"第一行中最后一个非空单元格是"&rng.Address（0,0）_
#005&",列号"&rng.Column&",数值"&rng.Value
#006Setrng=Nothing
#007EndSub
代码解析：
LastColumn过程使用消息框显示工作表中第一行最后一个非空单元格的地址、列号和数值，如图32所示。
图32获得第一行最后一个非空单元格
技巧4定位单元格
在Excel中使用定位对话框可以选中工作表中特定的单元格区域，而在VBA中则使用SpecialCells方法，如下面的代码所示。
#001SubSpecialAddress（）
#002DimrngAsRange
#003Setrng=Sheet1.UsedRange.SpecialCells（xlCellTypeFormulas）
#004rng.Select
#005MsgBox"工作表中有公式的单元格为：
"&rng.Address
#006Setrng=Nothing
#007EndSub
代码解析：
SpecialAddress过程使用SpecialCells方法选中工作表中有公式的单元格，并用消息框显示其地址。
SpecialCells方法返回一个Range对象，该对象代表与指定类型及值相匹配的所有单元格，语法如下：
expression.SpecialCells（Type,Value）
参数expression是必需的，返回一个有效的对象。
参数Type是必需的，要包含的单元格，可为表格41所列的XlCellType常量之一。
常量
值
描述
xlCellTypeAllFormatConditions
-4172
任意格式单元格
xlCellTypeAllValidation
-4174
含有验证条件的单元格
xlCellTypeBlanks
4
空单元格
xlCellTypeComments
-4144
含有注释的单元格
xlCellTypeConstants
2
含有常量的单元格
xlCellTypeFormulas
-4123
含有公式的单元格
xlCellTypeLastCell
11
使用区域中最后的单元格
xlCellTypeSameFormatConditions
-4173
含有相同格式的单元格
xlCellTypeSameValidation
-4175
含有相同验证条件的单元格
xlCellTypeVisible
12
所有可见单元格
表格41XlCellType常量
第3行代码将SpecialCells方法的Type参数设置为xlCellTypeFormulas，返回的是含有公式的单元格，通过修改相应的参数可以返回不同的单元格。
参数Value是可选的，如果Type参数为xlCellTypeConstants或xlCellTypeFormulas，此参数可用于确定结果中应包含哪几类单元格。
将某几个值相加可使此方法返回多种类型的单元格。
如果省略将选定所有常量或公式，可为表格42所列的XlSpecialCellsValue常量之一。
常量
值
描述
xlErrors
16
错误
xlLogical
4
逻辑值
xlNumbers
1
数字
xlTextValues
2
文本
表格42XlSpecialCellsValue常量
第5行代码使用消息框显示工作表中含有公式单元格的地址。
SpecialCells方法返回的是Range对象，因此可以直接使用该对象的属性和方法。
运行SpecialAddress过程结果如图41所示。
图41SpecialCells方法
技巧5查找单元格
5-1使用Find方法
在Excel中使用查找对话框可以查找工作表中特定内容的单元格，而在VBA中则使用Find方法，如下面的代码所示。
#001SubRngFind（）
#002DimStrFindAsString
#003DimRngAsRange
#004StrFind=InputBox（"请输入要查找的值:
"）
#005IfTrim（StrFind）<>""Then
#006WithSheet1.Range（"A:
A"）
#007SetRng=.Find（What:
=StrFind,_
#008After:
=.Cells（.Cells.Count）,_
#009LookIn:
=xlValues,_
#010LookAt:
=xlWhole,_
#011SearchOrder:
=xlByRows,_
#012SearchDirection:
=xlNext,_
#013MatchCase:
=False）
#014IfNotRngIsNothingThen
#015Application.GotoRng,True
#016Else
#017MsgBox"没有找到该单元格!
"
#018EndIf
#019EndWith
#020EndIf
#021EndSub
代码解析：
RngFind过程使用Find方法在工作表Sheet1的A列中查找InputBox函数对话框中所输入的值，并查找该值所在的第一个单元格。
第6到第13行代码在工作表Sheet1的A列中查找InputBox函数对话框中所输入的值。
应用于Range对象的Find方法在区域中查找特定信息，并返回Range对象，该对象代表用于查找信息的第一个单元格。
如果未发现匹配单元格，就返回Nothing，语法如下：
expression.Find（What,After,LookIn,LookAt,SearchOrder,SearchDirection,MatchCase,MatchByte,SerchFormat）
参数expression是必需的，该表达式返回一个Range对象。
参数What是必需的，要搜索的数据，可为字符串或任意数据类型。
参数After是可选的，表示搜索过程将从其之后开始进行的单元格，必须是区域中的单个单元格。
查找时是从该单元格之后开始的，直到本方法绕回到指定的单元格时，才对其进行搜索。
如果未指定本参数，搜索将从区域的左上角单元格之后开始。
在本例中将After参数设置为A列的最后一个单元格，所以查找时从A1单元格开始搜索。
参数LookIn是可选的，信息类型。
参数LookAt是可选的，可为XlLookAt常量的xlWhole或xlPart之一。
参数SearchOrder是可选的，可为XlSearchOrder常量的xlByRows或xlByColumns之一。
参数SearchDirection是可选的，搜索的方向，可
升级会员 