ExcleVBA常用技巧Worksheet对象.doc
《ExcleVBA常用技巧Worksheet对象.doc》由会员分享,可在线阅读,更多相关《ExcleVBA常用技巧Worksheet对象.doc(35页珍藏版)》请在咨信网上搜索。
1、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工作表的深度隐藏17技巧9防止更改工作表的名称19技巧10工作表中一次插入多行20技巧11删除工作表中的空行21技巧12删除工作
2、表的重复行23技巧13定位删除特定内容所在的行25技巧14判断是否选中整行26技巧15限制工作表的滚动区域27技巧16复制自动筛选后的数据区域28技巧17使用高级筛选获得不重复记录30技巧18工作表的保护与解除保护31技巧19奇偶页打印34第2章 Worksheet(工作表)对象技巧1 引用工作表的方式VBA中,在不同的工作表之间转换或者对不同工作表中的单元格区域进行操作时,需要指定引用的工作表,通常有下面几种方法:1-1 使用工作表的名称工作表名称是指显示在工作表标签中的文本,工作表名称可以使用WorkSheets集合和Sheets集合两种引用方式,如下面的代码所示。#001 Sub ShA
3、ctivate()#002 Worksheets(索引号).Activate#003 Sheets(索引号).Activate#004 End Sub第3、4行代码都激活工作簿中名称为“索引号”的工作表,激活后“索引号”工作表将成为活动工作表。WorkSheets集合包含所有的工作表,而Sheets集合不仅包含工作表集合WorkSheets,还包含图表集合Charts、宏表集合Excel4MacroSheets与MS Excel 5.0对话框集合DialogSheets等。任何时刻工作簿中只有一个工作表是活动工作表。1-2 使用工作表的索引号工作表索引号是指工作表在工作簿中的位置,Excel根
4、据工作表在工作表标签中的位置以1开始从左向右进行编号。下面的代码选中并激活当前工作簿中第1个工作表:#001 Sub ShIndex()#002 Worksheets(1).Select#003 End Sub单个WorkSheet对象的Select方法与Activate方法的主要区别在于Select方法要求工作表可视。注意 当工作簿包括工作表、宏表、图表等时,使用索引号引用工作表如Sheets(1)与WorkSheets(1)引用的可能不是同一个表。使用Worksheet对象的Index属性可以返回工作表的索引号,如下面的代码所示。#001 Sub ShInde()#002 MsgBox W
5、orksheets(索引号).Index#003 End Sub1-3 使用工作表的代码名称使用Worksheet对象的CodeName属性可以返回工作表的代码名称,如下面的代码所示。#001 Sub ShCodeName()#002 MsgBox Sheets(1).CodeName#003 End Sub工作表的代码名称显示在VBE工程资源管理器窗口中,在属性窗口中能够修改工作表代码名称,如图 211所示。在VBA中能够直接使用工作表的代码名称引用工作表,即使工作表的名称被修改,代码仍然能够正常运行。图 11工作表的代码名称1-4 使用ActiveSheet属性引用活动工作表使用Activ
6、eSheet属性可以返回活动工作表,如下面的代码所示。#001 Sub ShActive()#002 MsgBox ActiveSheet.Name#003 End SubActiveSheet属性应用于AppActivate对象、Window对象和Workbook对象时,如果未给出对象识别符,返回活动工作簿中的活动工作表。技巧2 选择工作表的方法在VBA中需要激活或者选择某个工作表时使用Select方法或Activate方法,如下面的代码所示。#001 Sub SelectSh()#002 Worksheets(Sheet2).Select#003 End Sub#004 Sub Activ
7、ateSh()#005 Worksheets(Sheet2).Activate#006 End Sub代码解析:SelectSh过程使用Select方法选择“Sheet2”工作表,而ActivateSh过程则使用Activate方法选择“Sheet2”工作表,从表面看两者的作用是相同的,但是如果“Sheet2”工作表是隐藏的,Activate方法可以正常运行,而Select方法将会出现错误,如图 221所示。图 21Select方法无效提示如果需要同时选中工作簿中的所有工作表,则只能使用Select方法而不能使用Activate方法,如下面的代码所示。#001 Sub SelectShs()#
8、002 Dim Shs As Worksheet#003 For Each Shs In Worksheets#004 Shs.Select False#005 Next#006 End Sub#007 Sub SelectSheets()#008 Worksheets.Select#009 End Sub#010 Sub ArraySheets()#011 Worksheets(Array(1, 2, 3).Select#012 End Sub代码解析: SelectShs过程遍历工作表并使用带参数的Select方法选中所有工作表。应用于Worksheet对象的Select方法的语法如下:S
9、elect(Replace)参数Replace是可选的。如果该值为True,则用指定对象替代当前选定对象。如果该值为False,则延伸当前选定对象以包括任何以前选定的对象。SelectSheets过程使用Worksheets集合的Select方法选中集合中所有的对象。ArraySheets过程使用Array 函数返回工作簿中的前三张工作表并使用Worksheets集合的Select方法选中前三张工作表。技巧3 遍历工作表的方法在Excel应用中经常需要遍历工作簿中所有的工作表,有以下两种方法可以实现。3-1 使用For.Next 语句使用For.Next 语句遍历工作簿中所有的工作表,如下面的
10、代码所示。#001 Sub ShCount1()#002 Dim c As Integer#003 Dim i As Integer#004 Dim s As String#005 c = Worksheets.Count#006 For i = 1 To c#007 s = s & Worksheets(i).Name & Chr(13)#008 Next#009 MsgBox 工作簿中含有以下工作表: & Chr(13) & s#010 End Sub代码解析:ShCount1过程使用For.Next 语句遍历工作簿中所有的工作表,并用消息框显示所有的工作表名称。 第5行代码根据Works
11、heet对象的Count属性返回工作簿中工作表的数量赋给变量c。应用于Worksheet对象的Count属性返回Worksheets集合中工作表的数量,语法如下:expression.Count第6行代码开始For.Next 语句循环。For.Next 语句以指定次数来重复执行一组语句,语法如下:For counter = start To end Step step statements Exit For statementsNext counter参数counter是必需的,用做循环计数器的数值变量。参数start是必需的,循环计数器的初值。 参数end是必需的,循环计数器的终值。 参数s
12、tep是可选的,环计数器的步长,缺省值为 1。 参数statements是可选的,放在For和Next之间的一条或多条语句,它们将被执行指定的次数。 第7行代码在For.Next循环中根据工作表的索引号取得所有工作表的名称赋给字符串变量s。运行ShCount过程结果如图 231所示。图 31取得所有工作表名称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 W
13、orksheets#005 s = s & Sh.Name & Chr(13)#006 Next#007 MsgBox 工作簿中含有以下工作表: & Chr(13) & s#008 End Sub代码解析:ShCount2过程使用For Each.Next语句遍历工作簿中所有的工作表,并用消息框显示所有工作表名称。第4行代码使用For Each.Next语句遍历Worksheets集合中所有元素。For Each.Next语句针对一个数组或集合中的每个元素,重复执行一组语句,语法如下:For Each element In Group statements Exit For statement
14、sNext element参数element是必需的,用来遍历集合或数组中所有元素的变量。参数group是必需的,对象集合或数组的名称。参数statements是可选的,针对对象集合或数组中的每一项执行的一条或多条语句。第5行代码将返回的工作表的名称赋给字符串变量s。运行ShCount2过程结果如图 31所示。技巧4 在工作表中上下翻页如果需要在工作簿的工作表中进行上下翻页,可以使用下面的代码。#001 Sub DownSheet()#002 Dim i As Integer#003 i = Worksheets.Count#004 If ActiveSheet.Index 1 Then#01
15、4 Worksheets(ActiveSheet.Index - 1).Activate#015 Else#016 Worksheets(i).Activate#017 End If#018 End Sub代码解析:DownSheet过程向下翻页,第3、12行代码使用Worksheets对象的Count属性取得工作表的数目,第4行到第7行代码根据Index属性判断活动工作表是否是工作簿中的最后一张工作表。如果活动工作表不是最后一张工作表则激活活动工作表的下一张工作表,否则激活第一张工作表。UpSheet过程向上翻页,第13行到第16行代码根据Index属性判断活动工作表是否是工作簿中的第一张工
16、作表。如果活动工作表不是第一张工作表则激活活动工作表的上一张工作表,否则激活最后一张工作表。技巧5 工作表的添加与删除在工作簿中添加工作表使用Add方法,如下面的代码所示。#001 Sub Addsh()#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方法在工作
17、簿的最后新建“数据”工作表。Add 方法应用于Sheets和Worksheets对象时新建工作表、图表或宏表,语法如下:expression.Add(Before, After, Count, Type)参数Before是可选的,指定工作表对象,新建的工作表将置于此工作表之前。参数After是可选的,指定工作表对象,新建的工作表将置于此工作表之后。如果Before和 After两者均省略,则新建的工作表将插入到活动工作表之前。参数Count可选,要新建的工作表的数目。默认值为 1。参数Type可选,指定新建的工作表类型。第5行代码将添加的工作表重命名为“数据”。如果需要在工作簿中批量添加工作表
18、,可以使用下面的代码。#001 Sub Addsh_2()#002 Dim i As Integer#003 Dim sh As Worksheet#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张工作表并将添加的工作表依次重命名。在使用以上代码往工作簿中添加工作表时,如果工作簿中已存在相同名称的工作表,运行时会发生错误,代码中断,如图 251
19、所示。图 51运行错误提示为了避免此错误的发生,可以在添加前先删除所有的工作表,如下面的代码所示。#001 Sub Delsh()#002 Dim sh As Worksheet#003 For Each sh In ThisWorkbook.Sheets#004 If sh.Name 工作表的添加与删除 Then#005 Application.DisplayAlerts = False#006 sh.Delete#007 Application.DisplayAlerts = True#008 End If#009 Next#010 End Sub代码解析:Delsh过程使用Delete方
20、法删除工作簿中除了“工作表的添加与删除”工作表以外所有的工作表。第3行代码使用For Each.Next语句遍历代码所在工作簿中所有的工作表。第4行到第7行代码判断工作表名称是否为“工作表的添加与删除”,如果不是则使用Delete方法删除。其中第5行代码将Application对象的DisplayAlerts属性设置为False,使删除时不显示如图 252所示系统警告对话框。图 52系统警告对话框第6行代码使用Delete方法删除工作表,应用于工作表对象的Delete方法删除指定的对象,语法如下:expression.Delete参数expression是必需的,该表达式返回“应用于”列表中的
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ExcleVBA 常用 技巧 Worksheet 对象
1、咨信平台为文档C2C交易模式,即用户上传的文档直接被用户下载,收益归上传人(含作者)所有;本站仅是提供信息存储空间和展示预览,仅对用户上传内容的表现方式做保护处理,对上载内容不做任何修改或编辑。所展示的作品文档包括内容和图片全部来源于网络用户和作者上传投稿,我们不确定上传用户享有完全著作权,根据《信息网络传播权保护条例》,如果侵犯了您的版权、权益或隐私,请联系我们,核实后会尽快下架及时删除,并可随时和客服了解处理情况,尊重保护知识产权我们共同努力。
2、文档的总页数、文档格式和文档大小以系统显示为准(内容中显示的页数不一定正确),网站客服只以系统显示的页数、文件格式、文档大小作为仲裁依据,平台无法对文档的真实性、完整性、权威性、准确性、专业性及其观点立场做任何保证或承诺,下载前须认真查看,确认无误后再购买,务必慎重购买;若有违法违纪将进行移交司法处理,若涉侵权平台将进行基本处罚并下架。
3、本站所有内容均由用户上传,付费前请自行鉴别,如您付费,意味着您已接受本站规则且自行承担风险,本站不进行额外附加服务,虚拟产品一经售出概不退款(未进行购买下载可退充值款),文档一经付费(服务费)、不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
4、如你看到网页展示的文档有www.zixin.com.cn水印,是因预览和防盗链等技术需要对页面进行转换压缩成图而已,我们并不对上传的文档进行任何编辑或修改,文档下载后都不会有水印标识(原文档上传前个别存留的除外),下载后原文更清晰;试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓;PPT和DOC文档可被视为“模板”,允许上传人保留章节、目录结构的情况下删减部份的内容;PDF文档不管是原文档转换或图片扫描而得,本站不作要求视为允许,下载前自行私信或留言给上传者【w****g】。
5、本文档所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用;网站提供的党政主题相关内容(国旗、国徽、党徽--等)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
6、文档遇到问题,请及时私信或留言给本站上传会员【w****g】,需本站解决可联系【 微信客服】、【 QQ客服】,若有其他问题请点击或扫码反馈【 服务填表】;文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“【 版权申诉】”(推荐),意见反馈和侵权处理邮箱:1219186828@qq.com;也可以拔打客服电话:4008-655-100;投诉/维权电话:4009-655-100。