ExcleVBA常用技巧Worksheet对象.docx
《ExcleVBA常用技巧Worksheet对象.docx》由会员分享，可在线阅读，更多相关《ExcleVBA常用技巧Worksheet对象.docx（113页珍藏版）》请在冰豆网上搜索。
ExcleVBA常用技巧Worksheet对象
VBA常用技巧
VBA常用技巧...................................................................................................................................
1
第2章
Worksheet（工作表）对象..................................................................................
2
技巧1
引用工作表的方式
...........................................................................................
2
1-1
使用工作表的名称.........................................................................................
2
1-2
使用工作表的索引号.....................................................................................
2
1-3
使用工作表的代码名称.................................................................................
3
1-4
使用ActiveSheet属性引用活动工作表.......................................................
3
技巧2
选择工作表的方法
...........................................................................................
4
技巧3
遍历工作表的方法
...........................................................................................
5
3-1
使用For...Next
语句.....................................................................................
5
3-2
使用ForEach...
Next语句............................................................................
7
技巧4
在工作表中上下翻页.......................................................................................
8
技巧5
工作表的增添与删除.......................................................................................
9
技巧6
严禁删除指定工作表.....................................................................................
13
技巧7
自动建立工作表目录.....................................................................................
15
技巧8
工作表的深度隐蔽
.........................................................................................
17
技巧9
防范改正工作表的名称.................................................................................
19
技巧10
工作表中一次插入多行...............................................................................
20
技巧11
删除工作表中的空行...................................................................................
21
技巧12
删除工作表的重复行...................................................................................
23
技巧13
定位删除特定内容所在的行.......................................................................
25
技巧14
判断能否选中整行.......................................................................................
26
技巧15
限制工作表的转动地域...............................................................................
27
技巧16
复制自动挑选后的数据地域.......................................................................
28
技巧17
使用高级挑选获取不重复记录...................................................................
30
技巧18
工作表的保护与排除保护...........................................................................
31
技巧19
奇偶页打印...................................................................................................
34
第2章Worksheet（工作表）对象
技巧1引用工作表的方式
VBA中，在不一样的工作表之间变换也许对不一样工作表中的单元格地域进行操作时，需要指定引用的工作表，平时有下边几种方法：
1-1使用工作表的名称
工作表名称是指显示在工作表标签中的文本，工作表名称可以使用WorkSheets会集和
Sheets会集两种引用方式，以下边的代码所示。
#001SubShActivate（）
#002Worksheets（"索引号"）.Activate
#003'Sheets（"索引号"）.Activate
#004EndSub
第3、4行代码都激活工作簿中名称为“索引号”的工作表，激活后“索引号”工作表将成为活动工作表。
WorkSheets会集包含所有的工作表，而Sheets会集不但包含工作表会集WorkSheets，还包含图表会集Charts、宏表会集Excel4MacroSheets与MSExcel5.0对话框会集
DialogSheets等。
任何时辰工作簿中只有一个工作表是活动工作表。
1-2使用工作表的索引号
工作表索引号是指工作表在工作簿中的地点，Excel依据工作表在工作表标签中的地点
以1开始从左向右进行编号。
下边的代码选中并激活当前工作簿中第1个工作表：
#001SubShIndex（）
#002Worksheets
（1）.Select
#003EndSub
单个WorkSheet对象的Select方法与Activate方法的主要差别在于Select方法要求工
作表可视。
注意当工作簿包含工作表、宏表、图表等时，使用索引号引用工作表如Sheets
（1）与
WorkSheets
（1）引用的可能不是同一个表。
使用Worksheet对象的Index属性可以返回工作表的索引号，以下边的代码所示。
#001SubShInde（）
#002MsgBoxWorksheets（"索引号"）.Index
#003EndSub
1-3使用工作表的代码名称
使用Worksheet对象的CodeName属性可以返回工作表的代码名称，以下边的代码所
示。
#001SubShCodeName（）
#002MsgBoxSheets
（1）.CodeName
#003EndSub
工作表的代码名称显示在VBE工程资源管理器窗口中，在属性窗口中可以更正工作表
代码名称，如图1-1所示。
在VBA中可以直接使用工作表的代码名称引用工作表，即便工
作表的名称被更正，代码依旧可以正常运转。
图1-1工作表的代码名称
1-4使用ActiveSheet属性引用活动工作表
使用ActiveSheet属性可以返回活动工作表，以下边的代码所示。
#001SubShActive（）
#002
#003EndSub
ActiveSheet属性应用于AppActivate对象、Window对象和Workbook对象时，假如未给出对象鉴别符，返回活动工作簿中的活动工作表。
技巧2选择工作表的方法
在VBA中需要激活也许选择某个工作表时使用Select方法或Activate方法，以下边的
代码所示。
#001SubSelectSh（）
#002Worksheets（"Sheet2"）.Select
#003EndSub
#004SubActivateSh（）
#005Worksheets（"Sheet2"）.Activate
#006
EndSub
代码分析：
SelectSh过程使用Select方法选择“Sheet2”工作表，而ActivateSh过程则使用Activate
方法选择“Sheet2”工作表，从表面看二者的作用是相同的，但是假如“Sheet2”工作表
是隐蔽的，Activate方法可以正常运转，而Select方法将会出现错误，如图2-1所示。
图2-1Select方法无效提示
假如需要同时选中工作簿中的所有工作表，则只好使用Select方法而不可以使用Activate方法，以下边的代码所示。
#001SubSelectShs（）
#002DimShsAsWorksheet
#003ForEachShsInWorksheets
#004Shs.SelectFalse
#005Next
#006EndSub
#007SubSelectSheets（）
#008
#009EndSub
#010SubArraySheets（）
#011Worksheets（Array（1,2,3））.Select
#012EndSub
代码分析：
SelectShs过程遍历工作表并使用带参数的Select方法选中所有工作表。
应用于
Worksheet对象的Select方法的语法以下：
Select（Replace）
参数Replace是可选的。
假如该值为True，则用指定对象代替当前选定对象。
假如该
值为False，则延伸当前选定对象以包含任何从前选定的对象。
SelectSheets过程使用Worksheets会集的Select方法选中会集中所有的对象。
ArraySheets过程使用Array函数返回工作簿中的前三张工作表并使用Worksheets集
合的Select方法选中前三张工作表。
技巧3遍历工作表的方法
在Excel应用中常常需要遍历工作簿中所有的工作表，有以下两种方法可以实现。
3-1使用For...Next语句
使用For...Next语句遍历工作簿中所有的工作表，以下边的代码所示。
#001SubShCount1（）
#002DimcAsInteger
#003DimiAsInteger
#004DimsAsString
#005
#006Fori=1Toc
#007s=s&Worksheets（i）.Name&Chr（13）
#008Next
#009
MsgBox"
工作簿中含有以下工作表
:
"&Chr（13）&s
#010
EndSub
代码分析：
ShCount1
过程使用
For...Next
语句遍历工作簿中所有的工作表，
并用信息框显示所有
的工作表名称。
第5行代码依据Worksheet
应用于Worksheet对象的Count
对象的Count属性返回工作簿中工作表的数目赋给变量
属性返回Worksheets会集中工作表的数目，语法以下：
c。
第6行代码开始For...Next语句循环。
For...Next语句以指定次数来重复履行一组语句，语法以下：
Forcounter=startToend[Stepstep]
[statements]
[ExitFor]
[statements]
Next[counter]
参数
counter
是必要的，用做循环计数器的数值变量。
参数start是必要的，循环计数器的初值。
参数end是必要的，循环计数器的终值。
参数step是可选的，环计数器的步长，缺省值为1。
参数statements是可选的，放在For和Next之间的一条或多条语句，它们将被履行指
定的次数。
第7行代码在For...Next循环中依据工作表的索引号获得所有工作表的名称赋给字符串变量s。
运转ShCount过程结果如图3-1所示。
图3-1获得所有工作表名称
3-2使用ForEach...Next语句
使用ForEach...Next语句遍历工作簿中所有的工作表，以下边的代码所示。
#001SubShCount2（）
#002DimShAsWorksheet
#003DimsAsString
#004ForEachShInWorksheets
#005s=s&Sh.Name&Chr（13）
#006
Next
#007
MsgBox"
工作簿中含有以下工作表:
"&Chr（13）&s
#008EndSub
代码分析：
ShCount2过程使用ForEach...Next语句遍历工作簿中所有的工作表，并用信息框显示所有工作表名称。
第4行代码使用ForEach...Next语句遍历Worksheets会集中所有元素。
ForEach...Next语句针对一个数组或会集中的每个元素，重复履行一组语句，语法以下：
ForEachelementInGroup
[statements]
[ExitFor]
[statements]
Next[element]
参数element是必要的，用来遍历会集或数组中所有元素的变量。
参数group是必要的，对象会集或数组的名称。
参数statements是可选的，针对对象会集或数组中的每一项履行的一条或多条语句。
第5行代码将返回的工作表的名称赋给字符串变量s。
运转ShCount2过程结果如图3-1所示。
技巧4在工作表中上下翻页
假如需要在工作簿的工作表中进行上下翻页，可以使用下边的代码。
#001SubDownSheet（）
#002DimiAsInteger
#003
#004
IfActiveSheet.Index#005
Worksheets（ActiveSheet.Index+1）.Activate
#006
Else
#007
Worksheets
（1）.Activate
#008
EndIf
#012
#013
IfActiveSheet.Index>1Then
#014
Worksheets（ActiveSheet.Index-1）.Activate
#015
Else
#016
Worksheets（i）.Activate
#017
EndIf
#018EndSub
代码分析：
DownSheet过程向下翻页，第3、12行代码使用Worksheets对象的Count属性获得
工作表的数目，第4行到第7行代码依据Index属性判断活动工作表是不是工作簿中的最后一张工作表。
假如活动工作表不是最后一张工作表则激活活动工作表的下一张工作表，激活第一张工作表。
不然
UpSheet过程向上翻页，第13行到第16行代码依据Index属性判断活动工作表是不是工作簿中的第一张工作表。
假如活动工作表不是第一张工作表则激活活动工作表的上一张
工作表，不然激活最后一张工作表。
技巧5工作表的增添与删除
在工作簿中增添工作表使用
Add
方法，以下边的代码所示。
#001SubAddsh（）
#002DimShAsWorksheet
#003WithWorksheets
#004SetSh=.Add（after:
=Worksheets（.Count））
#005Sh.Name="
#006EndWith
数据"
#007EndSub
代码分析：
Addsh过程使用Add方法在工作簿中新建“数据”工作表。
第2行代码申明变量Sh为工作表对象。
第4行行代码使用Add方法在工作簿的最后新建“数据”工作表。
Add方法应用于Sheets和Worksheets对象时新建工作表、图表或宏表，语法以下：
expression.Add（Before,After,Count,Type）
参数Before是可选的，指定工作表对象，新建的工作表将置于此工作表从前。
参数After是可选的，指定工作表对象，新建的工作表将置于此工作表以后。
假如Before和After二者均省略，则新建的工作表将插入到活动工作表从前。
参数Count可选，要新建的工作表的数目。
默认值为1。
参数Type可选，指定新建的工作表种类。
第5行代码将增添的工作表重命名为“数据”。
假如需要在工作簿中批量增添工作表，可以使用下边的代码。
#001SubAddsh_2（）
#002DimiAsInteger
#003DimshAsWorksheet
#004Fori=1To10
#005Setsh=Sheets.Add（after:
=Sheets（Sheets.Count））
#006sh.Name=i
#007Next
#008EndSub
代码分析：
Addsh_2过程使用For...Next语句和Add方法在工作簿中增添10张工作表并将增添的工作表挨次重命名。
在使用以上代码往工作簿中增添工作表时，假如工作簿中已存在相同名称的工作表，运
行时会发生错误，代码中断，如图5-1所示。
图5-1运转错误提示
为了防范此错误的发生，可以在增添前先删除所有的工作表，以下边的代码所示。
#001SubDelsh（）
#002DimshAsWorksheet
#003
#004
Ifsh.Name<>"
工作表的增添与删除"Then
#005
Application.DisplayAlerts=False
#006
#007
Application.DisplayAlerts=True
#008
EndIf
#009
Next
#010EndSub
代码分析：
Delsh过程使用Delete方法删除工作簿中除了“工作表的增添与删除”工作表以外所有的工作表。
第3行代码使用ForEach...Next语句遍历代码所在工作簿中所有的工作表。
第
Delete
4行到第7行代码判断工作表名称能否为“工作表的增添与删除”
方法删除。
此中第5行代码将Application对象的DisplayAlerts
假如不是则使用
属性设置为False，
使删除时不显示如图
5-2
所示系统警告对话框。
图5-2系统警告对话框
第6行代码使用Delete方法删除工作表，应用于工作表对象的Delete方法删除指定的
对象，语法以下：
参数
expression
是必要的，该表达式返回
“应用于”列表中的对象之一。
在运转增添工作表代码前先删除工作簿中的工作表固然可以防范同名错误,但也可能误删除实用的工作表,所以更为慎重的方法是在增添前先判断工作簿中能否存在相同名称的工作表,
升级会员 