ExcleVBA常用技巧Worksheet对象.doc

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删除工作表的重复行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 ShActivate()#002 Worksheets(索引号).Activate#003 Sheets(索引号).Activate#004 End Sub第3、4行代码都激活工作簿中名称为“索引号”的工作表，激活后“索引号”工作表将成为活动工作表。WorkSheets集合包含所有的工作表，而Sheets集合不仅包含工作表集合WorkSheets，还包含图表集合Charts、宏表集合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方法要求工作表可视。注意 当工作簿包括工作表、宏表、图表等时，使用索引号引用工作表如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 End Sub工作表的代码名称显示在VBE工程资源管理器窗口中，在属性窗口中能够修改工作表代码名称，如图 211所示。在VBA中能够直接使用工作表的代码名称引用工作表，即使工作表的名称被修改，代码仍然能够正常运行。图 11工作表的代码名称1-4 使用ActiveSheet属性引用活动工作表使用ActiveSheet属性可以返回活动工作表，如下面的代码所示。#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 ActivateSh()#005 Worksheets(Sheet2).Activate#006 End Sub代码解析：SelectSh过程使用Select方法选择“Sheet2”工作表，而ActivateSh过程则使用Activate方法选择“Sheet2”工作表，从表面看两者的作用是相同的，但是如果“Sheet2”工作表是隐藏的，Activate方法可以正常运行，而Select方法将会出现错误，如图 221所示。图 21Select方法无效提示如果需要同时选中工作簿中的所有工作表，则只能使用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()#008 Worksheets.Select#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方法选中集合中所有的对象。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 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行代码根据Worksheet对象的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是必需的，循环计数器的终值。 参数step是可选的，环计数器的步长，缺省值为 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 Worksheets#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 statementsNext 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#014 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属性判断活动工作表是否是工作簿中的第一张工作表。如果活动工作表不是第一张工作表则激活活动工作表的上一张工作表，否则激活最后一张工作表。技巧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方法在工作簿的最后新建“数据”工作表。Add 方法应用于Sheets和Worksheets对象时新建工作表、图表或宏表，语法如下：expression.Add(Before, After, Count, Type)参数Before是可选的，指定工作表对象，新建的工作表将置于此工作表之前。参数After是可选的，指定工作表对象，新建的工作表将置于此工作表之后。如果Before和 After两者均省略，则新建的工作表将插入到活动工作表之前。参数Count可选，要新建的工作表的数目。默认值为 1。参数Type可选，指定新建的工作表类型。第5行代码将添加的工作表重命名为“数据”。如果需要在工作簿中批量添加工作表，可以使用下面的代码。#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所示。图 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方法删除工作簿中除了“工作表的添加与删除”工作表以外所有的工作表。第3行代码使用For Each.Next语句遍历代码所在工作簿中所有的工作表。第4行到第7行代码判断工作表名称是否为“工作表的添加与删除”,如果不是则使用Delete方法删除。其中第5行代码将Application对象的DisplayAlerts属性设置为False，使删除时不显示如图 252所示系统警告对话框。图 52系统警告对话框第6行代码使用Delete方法删除工作表，应用于工作表对象的Delete方法删除指定的对象，语法如下：expression.Delete参数expression是必需的，该表达式返回“应用于”列表中的对象之一。在运行添加工作表代码前先删除工作簿中的工作表虽然可以避免同名错误,但也可能误删除有用的工作表,因此更为严谨的方法是在添加前先判断工作簿中是否存在相同名称的工作表,然后再进行下一步的操作。对于单张工作表可以使用下面的代码。#001 Sub Addsh_3()#002 Dim Sh As Worksheet#003 For Each Sh In Worksheets#004 If Sh.Name = 数据 Then#005 MsgBox 工作簿中已有数据工作表,不能重复添加!#006 Exit Sub#007 End If#008 Next#009 With Worksheets#010 Set Sh = .Add(after:=Worksheets(.Count)#011 Sh.Name = 数据#012 End With#013 End Sub代码解析：Addsh_3过程在使用Add方法在工作簿中新建“数据”工作表时首先判断工作簿中是否存在“数据”工作表，如果已存在“数据”工作表则不运行添加工作表的代码而只显示一个消息框进行提示，如图 253所示。图 53工作表同名提示还可以使用错误处理语句来绕过错误，如下面的代码所示。#001 Sub Addsh_4()#002 Dim sh As Worksheet#003 On Error GoTo line#004 With Worksheets#005 Set sh = .Add(after:=Worksheets(.Count)#006 sh.Name = 数据#007 End With#008 Exit Sub#009 line:#010 MsgBox 工作簿中已有数据工作表,不能重复添加!#011 Application.DisplayAlerts = False#012 Worksheets(Worksheets.Count).Delete#013 Application.DisplayAlerts = True#014 End Sub代码解析：Addsh_4过程是先使用Add方法在工作簿中新建“数据”工作表，如果工作簿中已存在同名的工作表则使用GoTo语句转移到指定的line行处进行提示并删除已添加还没有重命名的工作表，也就是工作簿中最后一张工作表。如果是批量添加工作表，使用上述方法时，添加工作表和已有工作表重名时，后面即使没有重名的工作表也不能添加，所以应先使用错误处理语句忽略错误，待全部添加好以后再删除多余的工作表，如下面的代码所示。#001 Sub Addsh_5()#002 Dim i As Integer, arr#003 Dim sh As Worksheet#004 On Error Resume Next#005 arr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)#006 For i = 0 To UBound(arr)#007 With Worksheets#008 Set sh = .Add(after:=Sheets(.Count)#009 sh.Name = arr(i)#010 End With#011 Next#012 Application.DisplayAlerts = False#013 For Each sh In Worksheets#014 If sh.Name Like Sheet* Then sh.Delete#015 Next#016 Application.DisplayAlerts = True#017 End Sub代码解析：Addsh_5过程使用Add方法在工作簿中添加10张工作表并重新命名为1到10，如果工作簿中已有相同名称的工作表则不添加。第4行代码错误处理语句，当发生重名错误时忽略错误，继续添加工作表。第5行到第11行代码在工作簿中添加10张工作表并重新命名为1到10，如果工作簿中已有相同名称的工作表则忽略错误重命名时发生的错误，此时工作簿中添加的工作表会以系统赋与的名称命名，如“Sheet1”。第12行到第15行代码使用For Each.Next语句遍历工作簿中所有的工作表，将工作簿中凡是以“Sheet”开头的工作表删除。技巧6 禁止删除指定工作表在工作表事件中是没有工作表删除事件的，为了防止用户误删除重要的工作表，除了使用保护工作簿方法外，还可以使用下面的代码。#001 Public Ctl As CommandBarControl#002 Sub DelSht()#003 Set Ctl = Application.CommandBars.FindControl(ID:=847)#004 Ctl.OnAction = MyDelSht#005 End Sub#006 Sub ResSht()#007 Set Ctl = Application.CommandBars.FindControl(ID:=847)#008 Ctl.OnAction = #009 End Sub#010 Sub MyDelSht()#011 If VBA.UCase$(ActiveSheet.CodeName) = SHEET2 Then#012 MsgBox 禁止删除 & ActiveSheet.Name & 工作表!#013 Else#014 ActiveSheet.Delete#015 End If#016 End Sub代码解析：DelSht过程将工作表标签右键菜单中的“删除工作表”菜单的OnAction属性设置为“MyDelSht”。第3行代码使用Set语句将工作表标签右键菜单中的“删除工作表”菜单赋给变量Ctl，并将其OnAction属性设置为MyDelSht过程，该菜单被单击时将运行“MyDelSht”过程而不是系统默认的设置。OnAction属性返回或设置一个VBA的过程名，该过程在用户单击或更改某命令栏控件的值时运行。ResSht过程将工作表标签右键菜单中的“删除工作表”菜单的OnAction属性恢复为默认设置。MyDelSht过程判断所要删除的工作表的代码名称是否是“SHEET2”，如果是则禁止删除该表而只显示一个提示消息框。为了不影响其他工作簿的使用，在VBE中双击ThisWorkbook写入下面的代码。#001 Private Sub Workbook_Activate()#002 Call DelSht#003 End Sub#004 Private Sub Workbook_Deactivate()#005 Call ResSht#006 End Sub代码解析：工作簿的Activate事件和Deactivate事件代码，在工作簿激活时运行DelSht过程，在关闭或打开其他工作簿时运行ResSht过程，这样只禁止删除本工作簿中“SHEET2”工作表，并不影响其他工作簿。当删除本工作簿中的“SHEET2”工作表时，并不会显示如图 261所示的消息框，而只会显示如图 262所示的禁止删除工作表的消息框。图 61删除工作表提示图 62禁止删除工作表技巧7 自动建立工作表目录如果在工作簿中有许多工作表，使用时往往会建立一张目录表并插入超链接以方便选择工作表。但是如果工作簿中的工作表经常添加和删除，使用手工建立目录很不方便，此时可以使用工作表的Activate事件自动建立工作表的目录，如下面的代码所示。#001 Private Sub Worksheet_Activate()#002 Dim sh As Worksheet#003 Dim a As Integer#004 Dim R As Integer#005 R = Sheet1.A65536.End(xlUp).Row#006 a = 2#007 If Sheet1.Cells(2, 1) Then#008 Sheet1.Range(A2:A & R).ClearContents#009 End If#010 For Each sh In Worksheets#011 If sh.CodeName Sheet1 Then#012 Sheet1.Cells(a, 1).Value = sh.Name#013 a = a + 1#014 End If#015 Next#016 End Sub代码解析：工作表的Activate事件，在“目录”工作表激活时自动建立工作簿中除“目录”工作表外所有工作表的目录。第2、3、4行代码声明变量类型。第5行代码取得A列最后非空单元格的行号。第6行代码设置变量a的初始值为2，从A2单元格开始建立工作表目录。第7行到第9行代码判断是否存在工作表目录，如果存在先清空原来的目录，以便更新目录。第10行到第15代码遍历工作簿的所有工作表，将除“目录”工作表外所有工作表的名称写入到A列单元格中。为了建立到各工作表的链接，使用工作表的SelectionChange事件，如下面的代码所示。#001 Private Sub Worksheet_SelectionChange(ByVal Target As Range)#002 Dim R As Integer#003 R = Sheet1.A65500.End(xlUp).Row#004 On Error Resume Next#005 If Target.Count = 1 Then#006 If Target.Column = 1 Then#007 If Target.Row 1 And Target.Row = R Then#008 Sheets(Target.Value).Select#009 End If#010 End If#011 End If#012 End Sub代码解析：工作表的SelectionChange事件，当选择A列工作表目录中工作表名称时自动选择该单元格所对应的工作表。第5、6、7行代码限制该事件触发的条件。第8行代码选择单元格所对应的工作表。“目录”工作表激活后自动在A列建立工作簿中除“目录”工作表以外所有表的目录，如图 271所示。图 71自动建立工作表目录技巧8 工作表的深度隐藏在使用VBA开发的工作簿文件完成交与用户使用后，我们往往希望用户在打开工作簿时启用宏，此时除了使用“禁用宏则关闭工作簿”的功能外，还可以隐藏所有有数据的工作表，如果用户在打开工作簿时禁用宏则只显示一张空白的工作表，达到强制启用宏的效果，代码如下：#001 Dim sh As Worksheet#002 Private Sub Workbook_BeforeClose(Cancel As Boolean)#003 Sheet1.Visible = True#004 For Each sh In ThisWorkbook.Sheets#005 If sh.Name 空白 Then#006 sh.Visible = xlSheetVeryHidden#007 End If#008 Next#009 ActiveWorkbook.Save#010 End Sub#011 Private Sub Workbook_Open()#012 For Each sh In ThisWorkbook.Sheets#013 If sh.Name 空白 Then#014 sh.Visible = xlSheetVisible#015 End If#016 Next#017 Sheet1.Visible = xlSheetVeryHidden#018 End Sub代码解析：第2行到第10行代码是工作簿的BeforeClose事件过程，在工作簿关闭前隐藏除“空白”表以外的所有的工作表。第3行代码将“空白”表的Visible属性设置为True，使其可见。应用于Charts和Worksheets对象的Visible属性决定对象是否可见，语法如下：expression.Visible参数expression是必需的，该表达式返回上面的对象之一。Visible属性可以设置为表格 281所示的XlSheetVisibility常量之一。常量值描述xlSheetHidden0隐藏对象，可以通过“格式”“工作表”“取消隐藏”菜单使对象重新可见，等同于设置为False。xlSheetVisible-1使对象重新可见，等同于设置为True。xlSheetVeryHidden2隐藏对象，使该对象重新可见的唯一方法是将此属性设置为True或xlSheetVisible。表格 81XlSheetVisibility常量第4行到第8行代码使用For Each.Next语句遍历工作簿中所有的工作表，将除“空白”表以外的所有工作表的Visible属性设置为xlSheetVeryHidden，使之隐藏。Visible属性设置为xlSheetVeryHidden后工作表不能通过“格式”“工作表”“取消隐藏”菜单来显示隐藏的工作表。第9行代码使用Save方法保存代码所在工作簿的更改，在关闭工作簿时不显示如图 281所示的消息框。图 81工作簿保存提示第10行到第18行代码是工作簿的Open事件过程，在打开工作簿时将除“空白”表以外的所有工作表的Visible属性设置为xlSheetVisible，取消隐藏。如果打开工作簿时禁用宏，则工作簿中除了“空白”表以外，其他的工作表还处于深度隐藏的状态，如图 282所示，这样就达到强制用户启用宏的效果，当然这还需要VBA工程保护的配合。图 82工作表深度隐藏技巧9 防止更改工作表的名称工作表的名称显示在工作表标签上，除了在相应的功能菜单中可以对其进行重命名操作外，在工作表标签上双击鼠标也能修改工作表名称。一旦修改了工作表名称，可能就会产生一连串的问题，例如在其他工作簿中对该工作表的引用将会失效，通过工作表名称引用工作表的代码也将出错。Excel没有提供修改工作表名称的相关事件，要禁止用户修改工作表名称，需采取其他一些技巧。比如在工作表BeforeClose事件中检验工作表名称，如果工作表名称不是指定的字符串，则将其修改为指定字符串，即保持工作表名称不变，代码如下。#001 Private Sub Workbook_BeforeClose(Cancel As Boolean)#002 If Sheet1.Name Excel Home Then Sheet1.Name = Excel Home#003 ThisWorkbook.Save#004 End Sub代码解析：工作簿的BeforeClose事件过程，在关闭当前工作簿时判断Sheet1工作表名称，如果不是指定的字符串“Excel Home”，则将其恢复为“Excel Home”后保存工作簿，从而避免更改Sheet1工作表名称。技巧10 工作表中一次插入多行在工作表的中插入多行空行，需要使用Insert方法，如下面的代码所示。#001 Sub InSertRows_1()#002 Dim i As Integer#003 For i = 1 To 3#004 Sheet1.Rows(3).Insert#005 Next#006 End Sub代码解析：InSertRows_1过程使用Insert方法在如图 301所示的数据区域的第2行和第3行之间插入三行空行。图 101数据区域Insert方法应用于Range对象时在工作表或宏表中插入一个单元格或单元格区域，其他单元格作相应移位以腾出空间，语法如下：expression.Insert(Shift, CopyOrigin)参数expression是必需的，该表达式返回一个Range对象。参数Shift是可选的，指定单元格的移动方向。可为以