ExcleVBA常用技巧Worksheet对象.docx

1、ExcleVBA常用技巧Worksheet对象VBA常用技巧VBA常用技巧 .1第 2 章Worksheet（工作表）对象 .2技巧 1引用工作表的方式.21-1使用工作表的名称 .21-2使用工作表的索引号 .21-3使用工作表的代码名称 .31-4使用 ActiveSheet 属性引用活动工作表 .3技巧 2选择工作表的方法.4技巧 3遍历工作表的方法.53-1使用 For.Next语句 .53-2使用 For Each.Next 语句 .7技巧 4在工作表中上下翻页 .8技巧 5工作表的增添与删除 .9技巧 6严禁删除指定工作表 .13技巧 7自动建立工作表目录 .15技巧 8工作表的深

2、度隐蔽.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 中，在不一样的工作表之间变换也许对不一样工作表中的单元格地域进行操作时，需要指定引用的工作表，平时

3、有下边几种方法：1-1 使用工作表的名称工作表名称是指显示在工作表标签中的文本， 工作表名称可以使用 WorkSheets 会集和Sheets 会集两种引用方式，以下边的代码所示。#001 Sub ShActivate()#002 Worksheets( 索引号 ).Activate#003 Sheets( 索引号 ).Activate#004 End Sub第 3、 4 行代码都激活工作簿中名称为“索引号”的工作表，激活后“索引号”工作表将成为活动工作表。WorkSheets 会集包含所有的工作表， 而 Sheets 会集不但包含工作表会集 WorkSheets ，还包含图表会集 Chart

4、s 、宏表会集 Excel4MacroSheets 与 MS Excel 5.0 对话框会集DialogSheets 等。任何时辰工作簿中只有一个工作表是活动工作表。1-2 使用工作表的索引号工作表索引号是指工作表在工作簿中的地点， Excel 依据工作表在工作表标签中的地点以 1 开始从左向右进行编号。下边的代码选中并激活当前工作簿中第 1 个工作表：#001 Sub ShIndex()#002 Worksheets(1).Select#003 End Sub单个 WorkSheet 对象的 Select 方法与 Activate 方法的主要差别在于 Select 方法要求工作表可视。注意

5、当工作簿包含工作表、宏表、图表等时，使用索引号引用工作表如 Sheets(1) 与WorkSheets(1) 引用的可能不是同一个表。使用 Worksheet 对象的 Index 属性可以返回工作表的索引号，以下边的代码所示。#001 Sub ShInde()#002 MsgBox Worksheets( 索引号 ).Index#003 End Sub1-3 使用工作表的代码名称使用 Worksheet 对象的 CodeName 属性可以返回工作表的代码名称， 以下边的代码所示。#001 Sub ShCodeName()#002 MsgBox Sheets(1).CodeName#003 En

6、d Sub工作表的代码名称显示在 VBE 工程资源管理器窗口中，在属性窗口中可以更正工作表代码名称，如图 1-1 所示。在 VBA 中可以直接使用工作表的代码名称引用工作表，即便工作表的名称被更正，代码依旧可以正常运转。图 1-1 工作表的代码名称1-4 使用 ActiveSheet 属性引用活动工作表使用 ActiveSheet 属性可以返回活动工作表，以下边的代码所示。#001 Sub ShActive()#002 #003 End SubActiveSheet 属性应用于 AppActivate 对象、 Window 对象和 Workbook 对象时，假如未给出对象鉴别符，返回活动工作簿

7、中的活动工作表。技巧 2 选择工作表的方法在 VBA 中需要激活也许选择某个工作表时使用 Select 方法或 Activate 方法，以下边的代码所示。#001 Sub SelectSh()#002 Worksheets(Sheet2).Select#003 End Sub#004 Sub ActivateSh()#005 Worksheets(Sheet2).Activate#006End Sub代码分析：SelectSh 过程使用 Select 方法选择“ Sheet2 ”工作表，而 ActivateSh 过程则使用 Activate方法选择“ Sheet2 ”工作表，从表面看二者的作用

8、是相同的，但是假如“ Sheet2 ”工作表是隐蔽的， Activate 方法可以正常运转，而 Select 方法将会出现错误，如图 2-1 所示。图 2-1 Select 方法无效提示假如需要同时选中工作簿中的所有工作表， 则只好使用 Select 方法而不可以使用 Activate 方法，以下边的代码所示。#001 Sub SelectShs()#002 Dim Shs As Worksheet#003 For Each Shs In Worksheets#004 Shs.Select False#005 Next#006 End Sub#007 Sub SelectSheets()#00

9、8 #009 End Sub#010 Sub ArraySheets()#011 Worksheets(Array(1, 2, 3).Select#012 End Sub代码分析：SelectShs 过程遍历工作表并使用带参数的 Select 方法选中所有工作表。应用于Worksheet 对象的 Select 方法的语法以下：Select(Replace)参数 Replace 是可选的。假如该值为 True ，则用指定对象代替当前选定对象。假如该值为 False ，则延伸当前选定对象以包含任何从前选定的对象。SelectSheets 过程使用 Worksheets 会集的 Select 方法选

10、中会集中所有的对象。ArraySheets 过程使用 Array 函数返回工作簿中的前三张工作表并使用 Worksheets 集合的 Select 方法选中前三张工作表。技巧 3 遍历工作表的方法在 Excel 应用中常常需要遍历工作簿中所有的工作表，有以下两种方法可以实现。3-1 使用 For.Next 语句使用 For.Next 语句遍历工作簿中所有的工作表，以下边的代码所示。#001 Sub ShCount1()#002 Dim c As Integer#003 Dim i As Integer#004 Dim s As String#005 #006 For i = 1 To c#00

11、7 s = s & Worksheets(i).Name & Chr(13)#008 Next#009MsgBox 工作簿中含有以下工作表: & Chr(13) & s#010End Sub代码分析：ShCount1过程使用For.Next语句遍历工作簿中所有的工作表，并用信息框显示所有的工作表名称。第 5 行代码依据 Worksheet应用于 Worksheet 对象的 Count对象的 Count 属性返回工作簿中工作表的数目赋给变量属性返回 Worksheets 会集中工作表的数目，语法以下：c。第 6 行代码开始 For.Next 语句循环。 For.Next 语句以指定次数来重复履行

12、一组语句，语法以下：For counter = start To end Step stepstatementsExit ForstatementsNext counter参数counter是必要的，用做循环计数器的数值变量。参数 start 是必要的，循环计数器的初值。参数 end 是必要的，循环计数器的终值。参数 step 是可选的，环计数器的步长，缺省值为 1。参数 statements 是可选的， 放在 For 和 Next 之间的一条或多条语句， 它们将被履行指定的次数。第 7 行代码在 For.Next 循环中依据工作表的索引号获得所有工作表的名称赋给字符串变量 s。运转 ShCo

13、unt 过程结果如图 3-1 所示。图 3-1 获得所有工作表名称3-2 使用 For Each.Next 语句使用 For Each.Next 语句遍历工作簿中所有的工作表，以下边的代码所示。#001 Sub ShCount2()#002 Dim Sh As Worksheet#003 Dim s As String#004 For Each Sh In Worksheets#005 s = s & Sh.Name & Chr(13)#006Next#007MsgBox 工作簿中含有以下工作表 : & Chr(13) & s#008 End Sub代码分析：ShCount2 过程使用 For

14、 Each.Next 语句遍历工作簿中所有的工作表，并用信息框显示所有工作表名称。第 4 行代码使用 For Each.Next 语句遍历 Worksheets 会集中所有元素。 For Each.Next 语句针对一个数组或会集中的每个元素，重复履行一组语句，语法以下：For Each element In GroupstatementsExit ForstatementsNext element参数 element 是必要的，用来遍历会集或数组中所有元素的变量。参数 group 是必要的，对象会集或数组的名称。参数 statements 是可选的，针对对象会集或数组中的每一项履行的一条或多

15、条语句。第 5 行代码将返回的工作表的名称赋给字符串变量 s。运转 ShCount2 过程结果如图 3-1 所示。技巧 4 在工作表中上下翻页假如需要在工作簿的工作表中进行上下翻页，可以使用下边的代码。#001 Sub DownSheet()#002 Dim i As Integer#003#004If ActiveSheet.Index 1 Then#014Worksheets(ActiveSheet.Index - 1).Activate#015Else#016Worksheets(i).Activate#017End If#018 End Sub代码分析：DownSheet 过程向下翻页

16、，第 3、 12 行代码使用 Worksheets 对象的 Count 属性获得工作表的数目， 第 4 行到第 7 行代码依据 Index 属性判断活动工作表是不是工作簿中的最后一张工作表。 假如活动工作表不是最后一张工作表则激活活动工作表的下一张工作表，激活第一张工作表。不然UpSheet 过程向上翻页，第 13 行到第 16 行代码依据 Index 属性判断活动工作表是不是工作簿中的第一张工作表。 假如活动工作表不是第一张工作表则激活活动工作表的上一张工作表，不然激活最后一张工作表。技巧 5 工作表的增添与删除在工作簿中增添工作表使用Add方法，以下边的代码所示。#001 Sub Adds

17、h()#002 Dim Sh As Worksheet#003 With Worksheets#004 Set Sh = .Add(after:=Worksheets(.Count)#005 Sh.Name = #006 End With数据 #007 End Sub代码分析：Addsh 过程使用 Add 方法在工作簿中新建“数据”工作表。第 2 行代码申明变量 Sh 为工作表对象。第 4 行行代码使用 Add 方法在工作簿的最后新建“数据”工作表。Add 方法应用于 Sheets 和 Worksheets 对象时新建工作表、图表或宏表，语法以下：expression.Add(Before,

18、After, Count, Type)参数 Before 是可选的，指定工作表对象，新建的工作表将置于此工作表从前。参数 After 是可选的，指定工作表对象，新建的工作表将置于此工作表以后。假如 Before 和 After 二者均省略，则新建的工作表将插入到活动工作表从前。参数 Count 可选，要新建的工作表的数目。默认值为 1。参数 Type 可选，指定新建的工作表种类。第 5 行代码将增添的工作表重命名为“数据” 。假如需要在工作簿中批量增添工作表，可以使用下边的代码。#001 Sub Addsh_2()#002 Dim i As Integer#003 Dim sh As Work

19、sheet#004 For i = 1 To 10#005 Set sh = Sheets.Add(after:=Sheets(Sheets.Count)#006 sh.Name = i#007 Next#008 End Sub代码分析：Addsh_2 过程使用 For.Next 语句和 Add 方法在工作簿中增添 10 张工作表并将增添的工作表挨次重命名。在使用以上代码往工作簿中增添工作表时， 假如工作簿中已存在相同名称的工作表， 运行时会发生错误，代码中断，如图 5-1 所示。图 5-1 运转错误提示为了防范此错误的发生，可以在增添前先删除所有的工作表，以下边的代码所示。#001 Sub

20、Delsh()#002 Dim sh As Worksheet#003 #004If sh.Name 工作表的增添与删除 Then#005Application.DisplayAlerts = False#006#007Application.DisplayAlerts = True#008End If#009Next#010 End Sub代码分析：Delsh 过程使用 Delete 方法删除工作簿中除了“工作表的增添与删除”工作表以外所有的工作表。第 3 行代码使用 For Each.Next 语句遍历代码所在工作簿中所有的工作表。第Delete4 行到第 7 行代码判断工作表名称能否为“工作表的增添与删除”方法删除。此中第 5 行代码将 Application 对象的 DisplayAlerts, 假如不是则使用属性设置为 False ，使删除时不显示如图5-2所示系统警告对话框。图 5-2 系统警告对话框第 6 行代码使用 Delete 方法删除工作表， 应用于工作表对象的 Delete 方法删除指定的对象，语法以下：参数expression是必要的，该表达式返回“应用于 ”列表中的对象之一。在运转增添工作表代码前先删除工作簿中的工作表固然可以防范同名错误 ,但也可能误删除实用的工作表 ,所以更为慎重的方法是在增添前先判断工作簿中能否存在相同名称的工作表 ,