excel vba语法辞典-04工作表操作

4章工作表操面章节中，已经详细介绍了单元格对象的操作和内容。在ExcelVBA中，Worksheet对象表示Excel工作表，是Worksheets集合的成员。Worksheets集合表示某个工作簿中所有的Worksheet对象。用户可以通过在VBA代码中使用Worksheets集合对象删除工作表等常见操作。Worksheet对象的事件控制工作表的行为。在本章中，将详细讲解如何在VBA中使用工作表对象工作表是用户十分熟悉的对象，许多关于Excel的操作都是基于工作表的。例如，插入、删除、选择和保护工作表等。在ExcelVBA中，用户可以通过代码对工作表进行各种案例 工作在Excel中，用户经常需要添加新的工作表，来添加新的数据。默认情况下，工作薄中包含三个工作表。当用户需要新的工作表时，则需要工作表。在ExcelVBA中，用户可以使用Add方法来工作表。ExcelVBA中，使用WorksheetsAdd方法，可向指定工作簿中增加工表达式.Add(Before,After,Count, 该方法共有4个参数，这些参数都可省略。各参数的含义如BeforeAfterCount：要添加的工作表数。默认值为1Type：指定工作表类型。可以为xlWorksheet（xlChart（图xlExcel4MacroSheet或xlExcel4IntlMacroSheet。默认值为xlWorksheet提提示：如果省略参数Before和After，则新工作 到活动工作表之前。新建的工作表将成为活动作表原始数据的基础上添加新工作表，原始数据如4.1所示。4.1原始数SubSubInsertNewSheets()DimStrAsStringOnErrorResumeStr=Application.InputBox(prompt:="选Worksheets.Addbefore:=Worksheets(Str)EndSub的工作表：",Title:="确位置",工作表的VBA代码如运行程序代码，输入工作表的位置，如图4.2所示图4.2选择工作表的位

单击框中的“确定”按钮，查看添加的结果，如图4.3所示4.3查看添加的工作在上面的程序代码中，提示用户输入工作表名称，再使用Add方法的efore参数指定将新增加的工作表放在该工作表之前。在程序中使用了nrror错误捕获语句，用来捕获用户输入的工作表名称不存在时的错误提示。用户可以直接增加工作表，代码很简单，使用无参数的Add方法即可，具体代码如下： 案例 工作表工作表同样是十分常见的操作，当各个工作表的结构或者数据运算结构十分相似的候，工作表节省大的工作在xcelVBA中，用户同样可以代码实现表的功能。表达式.Copy(Before, 方法的两个参数都可省略，各参数的Before：将要在其之前放置所工作表的工作表。如果指定了After，则不能指定After：将要在其之后放置所工作表的工作表。如果指定了Before，则不能指定某公司统计了各员工的上下半年销量，现在需要对该数据进行处理，因此，用户需要根据数，不同数目的工作表，原始数据如图4.4所示。4.4原始数DimiAsIntegerDimjAsIntegerDimStrAsStringOnErrorResumej=Application.InputBox(prompt:="输的个数：",Title:="输 的个数",Ifj>0Fori=1ToWorksheets("Sheet1").Copybefore:=Worksheets("Sheet1")Str=" "&i&"的销售表"ActiveSheet.Name=EndIfEnd工作表的VBA代码如运行程序代码，输入的数目，如图4.5所示图4.5输入的个单击框中的“确定”按钮，查看的工作表结果，如图4.6所示图4.6查看工作表的结在本案例中，用户使用InputBox方法要求用户输入的数量，再使用循环结构依案例 统计工作表的数在默认情况下，工作薄中包含3个工作表。但是，用户可以根据需要添加工作表。因VBA代码统计工作表的数目。ExcelVBA中，WorksheetsWorksheet对象组成，该集合的Count属性返回集合中Worksheet对象的数量，代表工作簿中工作表的数量。使用Sheets集合对象的Count属性，可返回指定的或活动工作簿中所有工作表的集合。这Chart（图表工作表）Worksheet对象。应注意Worksheets集合与Sheets集合的区别。某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。因此，用户需要统计工作薄中的工作表总数，原始数据如图4.7所示。4.7原始数SubDimiAsi=SubDimiAsi=EndSub运行程序代码，查看程序代码的结果，如图4.84.8统计工作表的数为了检测程序代码的结果，添加新的工作表，如图4.9所示。再次运行程果如图4.10图4.9添加新的工作 图4.10查看程序结从上面的结果中可以看出，当用户添加新工作表后，程序会统计新工作薄中包含的工作表数目。案例 选择多个工作在用户需要进行跨表操作时，同时选择多个工作表将是经常需要进行的操作。在中，用户可以使用Select方法同时选择多个工作表表达式 参数Replace可省略。该参数如果为True，则用指定的对象替换当前所选内容。如果为时，将取消前一个工作表的选取状态。如以下代码最后只有第四个工作表处于选中状态：而以下代码将同时选中第一个和第四个工作表使用以下代码也可同时选中多个工作表： Worksheets(Array(Worksheets(1).Name, 某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。用户需要同时选择多个工作表，原始数据如4.11所示。4.11SubMsgBox"选取第一、二和第四个工作表Worksheets(2).SelectSubMsgBox"选取第一、二和第四个工作表Worksheets(2).SelectFalseWorksheets(4).SelectEndEnd运行程序代码，查看程序代码的结果，如图4.124.12显示选中的信息结单击框中的“确定”按钮，查看运行的结果，如图4.13所示4.13查看运行的结从该例的结果可以看出，当程序运行后，Excel显示的是选中“工作组”。用户可以选案例 选择前一个工作工作表之前在物理上是相互连续的，用户需要对相邻的工作表进行操作。当用户需要跨表进行操作的时候，首先需要选择对应的工作表。在ExcelVBA中，用户可以使用代码来选择相邻的工作表。ExcelVBA中，在工作簿中按顺序移动工作表时，可PreviousNext属Previous属性：返回代表下一个工作表的Worksheet对象Next属性：返回代表下一个工作表的Worksheet注注意：如果当前工作表是第一个工作表，则使用Previous属性会出错。如果当前工作表是最作表，则使用Next属性会出错（或下一个）单元格，并不选中了工作Sheet2，现在需要选择工作表Sheet1。SubIfActiveSheet.Index<>1ThenEndIfEndSubIfActiveSheet.Index<>1ThenEndIfEnd运行程序代码，查看程序代码的结果，如图4.134.13运行程序代在上面的代码中，ActiveSheet代表活动工作簿中或指定的窗口或工作簿中的活动工作表。通过ActiveSheet.Index属性判断当前工作表是否为第一个工作表，对用户进行相应的提示。同理，选择下一工VBA代码如下：SubSub下一工作表IfActiveSheet.Index<>Worksheets.CountThenEndIfEnd案例 移动工作在对工作表的操作中，移动工作表是十分常见的操作。在默认情况下，用户添加的工作表是在对应工作表之前。为了体现工作表的次序，用户需要经常移动工作表。在ExcelVBA中，用户可以使用ove方法来移动工作表。ExcelVBA中，使Worksheet对象的Move方法，可将工作表移到工作簿中的其表达式.Move(Before, Move方法的参数与Copy方法注注意：如果既不指定Before也不指定After，Excel将新建一个工作簿，其中包含所移动的工作表某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。用户需要移动工作表的位置，原始数据如4.14所示。4.14原始数SubDimwsAsWorksheetSetws=ws.MoveSubDimwsAsWorksheetSetws=ws.MoveEnd首先激活工作表Sheet2，如图4.15所示。运行程序代码，查看移动工作表的结果，如图4.16所示。图4.15激活工作 图4.16移动工作

在上面的代码中，首先将当前工作表（ActiveSheet）赋值给一个对象变量，再使用该对象变量完成工作表的移动操作。案例 重命名工作当用户在新的工作表时，其名称是默认是按照工作表名称次序命名。为了让工作表具有更加明确的含义，用户经常需要对工作表进行重新命名。在VBA中，用户只需要重新设置工作表的Name属性。在本例中，将会涉及到InputBox函数。从多个例子中可以看出，该函数有很广泛的使用。其功能是用话框来中显示提示，等待用户输入正文或按下按钮，并返回包含文本框内容的InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile, 参数prompt表示框消息出现的字符串表达式。参数Title是可选的。显示框titleDefault参数Xpos、Ypos是可选的。分别指定框的左边与屏幕左边以及话框的上边与屏幕上边的距离。参数Helpfile也是可选的。表示字符串表达式，识别帮助文件，用该文件为框提供上下文相关的帮助。参数Context也是可选的。数值表达式，由帮助文件的中，NameWorkbook和WorksheetNamesnames(index)可返回一个Name对象。索引号表示名称在集合中的位置，名称按字母顺序从a到z放置。某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。首先用户需要重新设置工作表的名称，原始数据如图4.17所示。4.17原始数SubDimstrAsstr=prompt:="输入SubDimstrAsstr=prompt:="输入工作表的名称：",_Title:="重命名工作表的名称",Type:=2)Ifstr=""Orstr="False"ThenExitSubActiveSheet.Name=strEnd运行程序代码，在框中输入名称，如图4.18所示4.18输入重命名的名单击框中的“确定”按钮，查看重命名的结果，如图4.19所示4.19重命名工作用户没有输入任何的名称，则直接调Exit函数，退出整个程序。案例 判断工作表是否存当用户在处理大量数据，特别是有大量工作表时，经常需要判断某个名称的工作表是否存在。在本小节中，将结合具体的例子，来说明如何判断工作表是否存在。OnError语句捕获这种错误信息，以判断指定的工作表是否存在。在ExcelVBA中，OnError语句启动错误处理程序，并指定该程序在过的位置；OnErrorGoTolineline参数中指定的line开始错误处理程序。指定的line必须在一个过，这个过程与OnError语句相同；否则句之后的语句并在此继续运行。对象时要使用这种形式而不使用OnErrorGoTo。OnErrorGoTo0：当前过任何已启动的错误处理程序某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。首先用户需要判断工作表是否存在，原始数据如图4.20所示。4.20原始数SubDedfineSheetExist()DimwSheetAsWorksheetDimwsAsStringws=Application.InputBox(Prompt:="输入查找的工作表：",_Title:="输入工作表的名称",Default:="Sheet1",Type:=2)OnSubDedfineSheetExist()DimwSheetAsWorksheetDimwsAsStringws=Application.InputBox(Prompt:="输入查找的工作表：",_Title:="输入工作表的名称",Default:="Sheet1",Type:=2)OnErrorResumeSetwSheet=IfwSheetIsNothingThenSetwSheet=NothingOnErrorGoTo0SetwSheet=NothingOnErrorGoTo0EndEnd运行程序代码，在框中输入需要查看的工作表名称，如图4.21所示。查看程序运行的结果4.22所示。图4.21输入查看的工作表名 图4.22查看程序运行的结再次运行程序代码，输入查看工作表的名称，如图4.23所示。查看运行的图4.23输入查看的工作表名 图4.24查看运行的结当用户查看到工作表后，程序会激活到工作表，如图4.25所示4.25查看激活的工作从本例的代码可以看出，判断是否存在该工作表，也是直接在程序代码中选择该工作表，如果得到的结果是Nothing，则直接判断没有这个工作表。案例 判断工作表是否为当用户使用Excel处理大量数据的时候，会产生很多类型的工作表。但是，在处理过，可能由于程序控制或者用户自行操作，将工作表的内容清空。在本小节中，将需要通过VBA代码判断工作表是否为空。ExcelVBA用户可以通过IsEmpty函数和Shapes对象统计Excel工作表的内容。IsEmptyShapes对象判断工作表是否有图形对象。IsEmpty函数的功能是返回Boolean类型的数值，判断变量是否存在。其表达式如下： 参数expression是数值或字符串表达式。同时，由于删除工作表是需要用户再次确认的操作，需要用到Application.DisplayAlerts。这个属性表示宏运行时，Excel会显示特定的表达式 表达式是代表Application对象的变量。该属性的默认值为True。如果将该属性设置为False，则在代码运行结束后，Excel将该属性设置为True。某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。首先用户需要判断工作薄中是否存在空工作表，原始数据如4.26所示。4.26原始数SubCheckBlankSheets()DimIntnumsAsIntegerDimiAsIntegerFori=1ToIntnumsIfIsEmpty(Sheets(i).UsedRangeAndSubCheckBlankSheets()DimIntnumsAsIntegerDimiAsIntegerFori=1ToIntnumsIfIsEmpty(Sheets(i).UsedRangeAndSheets(i).Shapes.Count0ThenMsgBox"删除"&"工作表"&Sheets(i).Name&"?"EndIfApplication.DisplayAlerts=TrueNextiEnd运行程序代码，查看程序运行的结果，如图4.274.27查看程序运行的结查看系统运行的结果，如图4.284.28查看提示信用户还可以用其他方法来判断工作表是否为空，感的用户可以查看相应的书籍案例 设置工作表颜在ExcelVBA中，工作表是一个特殊的对象主要用来标识工作表的类型和内容。在在ExcelVBA中，Tab对象表示图表或工作表中的。使用Chart对象或Worksheet对象的Tab属性可返Tab对象。用户可以Tab对象的ColorIndex属性来设置图表或某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。首先用户需要设置工作表的颜色，原始数据如图4.29所示。4.29原始数SubActiveSheet.Tab.ColorIndex=4EndSub设置工作表颜色的VBA代SubActiveSheet.Tab.ColorIndex=4EndSub运行程序代码，查看程序运行的结果，如图4.30图4.30设置工作表的颜RGB(red,green, 函数包含三个不能省略的参数，各在数的含义如下：red：表示颜色的红色成份，数值范围 0~255，值越大表示该种颜色成份越多green：表示颜色的绿色成份，数值范围 0~255，值越大表示该种颜色成份越多blue：表示颜色的兰色成份，数值范围 0~255，值越大表示该种颜色成份越多案例 工作表排数值排序是用户十分熟悉的一种操作，在Excel中，用户还可以对工作表按照名称进行排序。工作表的名称标识工作表中的内容，因此，对工作表排序，就相当于对工作表的内容进行排序。本小节将演示一种排序法对工作表进行排序。本例使用直接选择排序算法进行排序，直接选择排序基本算法是：对于n个数据，使用直接选择排序算法，可经过n-1次直接选择排序得到有序结果：1次从无序的数1个数2~n个数据进行比较，如果找到更小的数据，就进行交换。通过第一次循环，可使用第1个数据为最小的数据。第2次选择2个数3~n个数据进2个数据为整个2小的数据。第i次选i个数据，将它i+1~n个数据进行比较i个数据为整个数域中in-1次上面的过程，n个数据经过直接选择排序得到有序结果。某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。首先用户需要设置工作表的颜色，原始数据如图4.31所示。4.31原始数DimiAsLongDimjAsLongDimmsgAsLongmsg=DimiAsLongDimjAsLongDimmsgAsLongmsg=MsgBox("按升序排列，请选'是[Y]'."&vbCrLf&vbCrLf&"按降序排列，请选'否[N]'",vbYesNoCancel,"工作表 Ifmsg=vbCancelThenExitIntnums=Sheets.CountIfmsg=vbYesThenFori=1ToForForj=iToSheets(j).MoveBefore:=Sheets(i)NextjNextFori=1ToForj=iToSheets(j).MoveBefore:=Sheets(i)NextjEndIfEnd运行程序代码，查看程序运行的结果，选择排序的标准，如图4.32所示。单击框中的“是”按钮，查看排序的结果，如图4.33所示。图4.32选择排序标 图4.33查看排序的结再次运行程序代码，选择降序排列，如图4.34所示。单击框中的“否”按钮，查看排序的结果，如4.35所示。 图4.34查看排序的标 图4.35查看排序的结用户可以自行尝试其他方法，对工作表的内容进行排序。然后比较各种算法之间的优劣点。案例 确定工作表的可视范置工作表的操作范围。ExcelVBA中，用户可以使用VisibleRange属性返回显示在窗口或窗格中的单元格区域。为了表示该区域的地址，还需要使用到Range.AddressLocal属性。该属性返回对指定区域的区域。其语法表达式如下：表达式.AddressLocal(RowAbsolute,ColumnAbsolute,ReferenceStyle,External, 其中表达式表示Range对象的变量。参数的具体说明RowAbsolute：以绝对的形式返回的行部分。默认值 TrueColumnAbsolute：以绝对的形式返回的列部分。默认值 TrueReferenceStyle：指定类型，取值是XlReferenceStyle的常量之一External返回外部。如果为False，则返回本地。默认值为FalseRelativeToRowAbsoluteColumnAbsolute都设置False设置为xlR1C1，则必须包含相对的起始点某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。首先用需要确定工作表的可视范围，原始数据如图4.36所示4.36原始数SubDimrngAdrAsrngAdr=SubDimrngAdrAsrngAdr=MsgBox"工作表的可见单元格范围是："&rngAdrEndSub运行程序代码，查看程序运行的结果，如图4.374.37查看运行的结案例 保护工作表达式表达式.Protect(Password,DrawingObjects,Contents,Scenarios,UserInterfaceOnly,AllowFormattingCells,AllowFormattingColumns,AllowFormattingRows,AllowInsertingColumns,AllowInsertingRows,AllowInsertingHyperlinks,AllowDeletingColumns,AllowDeletingRows,lloortin,llowilterin,lonitbs) 该方法的参数都可以省略，一般只需要设置Password即可。各参数的含义如下DrawingObjects：该参数若为True，则保护工作表中的形状。默认值是TrueContents：该参数若为rue，则保护的内容。对于图表，这样会保护整个图表。对于工作表，这样会保护锁定的单元格。默认值是rue。Scenarios：该参数若为True，则保护方案。此参数仅对工作表有效。默认值是TrueUserInterfaceOnly：该参数若为True，则保护用户界面，但不保护宏。如果省略此参AllowFormattingCells：该参数若为True，则允许用户为受保护的工作表上的任意单元格设置格式。默认值是False。AllowFormattingColumns：该参数若为True，则允许用户为受保护的工作表上的任意False。AllowFormattingRows：该参数若True，则允许用户为受保护的工作表上的任意行设置格式。默认值是False。AllowInsertingColumns：该参数若为True，则允许用户在受保护的工作表上列。False。False。AllowInsertingHyperlinks：该参数若 True，则允许用户在受保护的工作表中。默认值是FalseAllowDeletingColumns：该参数若为rue，则允许用户在受保护的工作表上删除列，要删除的列中的每个单元格都被解除锁定。默认值是False。lloeletingos：该参数若为ue，则允许用户在受保护的工作表上删除行，要删除ale。AllowSorting：该参数若为rue，则允许用户在受保护的工作表上进行排序。排序区域中的每个单元格必须是解除锁定的或取消保护的。默认值是False。AllowFiltering：该参数若为rue，则允许用户在受保护的工作表上设置筛选。用户可能上设置筛选。默认值是Fale。AllowUsingPivotTables：该参数若为True，则允许用户在受保护的工作表上使用数据表。默认值是False。某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。首先用户需要对工作表进行保护，原始数据如图4.示。4.38原始数SubOnErrorResumeNextDimWsAsSubOnErrorResumeNextDimWsAsWorksheetDimStrAsStringStr=Application.InputBox(prompt:="输入工作表的 ",Type:=2)ForEachWsInWs.ProtectWs.ProtectEndSub运行程序代码，在框中输入，如图4.39所示。单击“确定”按钮，查看工作表保护的结果，如4.40所示。图4.39输入图4.40查看保护的结当用户将工作表保护后，查看操作的结果，如图4.41所示4.41保护工作表的结当用户对工作表进行修改操作后，系统会显示系统提示，如图4.42所示4.42系统提案例 取消工作表的保当用户对工作表进行保护后，同样可以使用VBA代码撤销对工作表的保护。该代码的主要功能类似Excel中的基本操如果工作表或工作簿不是受保护的，则此方法不起作用。Unprotect 方法的语法格式如下：表达式 参数Password表示用于解除工作表保护的。如果工作表不设保护，则省略此参数。在撤消工作表保护时，如果输入的错误应给用户提示。本例使用OnError捕获OnErrorGoTo 以上语句启动错误处理程序，且该例程从必要的line参数中指定的line开始。line参数可以是任何行或行号。如果发生一个运行时错误，则程序会跳到line，激活错误处理程序。某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。首先用户需要取消工作表的保护，原始数据如图4.43所示。4.43原始数SubOnErrorGoToBackDimwsAsWorksheetDimStrAsStringStr=Application.InputBox(prompt:="输入 SubOnErrorGoToBackDimwsAsWorksheetDimStrAsStringStr=Application.InputBox(prompt:="输入 ",Type:=2)ForEachwsInWorksheetsws.UnprotectPassword:=StrExitSubMsgBox" EndSub运行程序代码，在框中输入，如图4.44所示。单击“确定”按钮，查看取消工作表保护的结果4.45所示。图4.44输入图4.45出现错误的情再次运行程序代码，在框中输入，如图4.46所示。单击“确定”按钮，查看取消工作表保护的结果，如图4.47所示。图4.46输入新的图4.47查看取消保护的结上面程序代码的功能和Excel工作表中的撤销工作表保护的操作类似，用户可以自行案例 拆分工作ExcelVBA中，Collection对象是项目所组成的有序集合，可以把集合作为单元来imXsewoletn 在ExcelCollection对象提供简便方法，可以将所有相关的项目视为单个对象进行。集合中的成员都属于这个集合，但是不一定都是同一种数据类型的。单供用户创建建立集合之后，就可以用Add方法添加成员，用Remove方法删除成员。在用ForEach...Next语句重复整个集合时，可以Item方法从集合返回特定某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。首先用户需要根据部门的名称，拆分工作表，原始数据如图4.48所示。4.48原始数DimirowAsLongDimiAsIntegerDimHAsNewCollectionDimshtAsWorksheetDimADimirowAsLongDimiAsIntegerDimHAsNewCollectionDimshtAsWorksheetDimAAsRangeDimIntColAsSetA=ActiveCellApplication.ScreenUpdating=FalseApplication.DisplayAlerts=FalseForEachshtInIfIfsht.Name<>"销售表"ThenApplication.DisplayAlerts=IntCol=Application.InputBox("选择划分的数据列：（B列，输入2）","选择数据列：","2",Type:=1)OnErrorResumeNextWithSheets("销售表Fori=2ToirowH.Add.Cells(i,IntCol),CStr(.Cells(i,IntCol))Fori=1To.Cells.AutoFilterfield:=IntCol,Criteria1:=H(i).[a1].CurrentRegion.CopyDebug.PrintEndWithEnd运行程序代码，选择筛选的条件数据列，如图4.49所示4.49选择筛选的条件数据单击框中的“确定”按钮，查看拆分的结果，如图4.50所示4.50拆分的结在上面的代码中，用户使用了Application.ScreenUpdating属性，将其设置为Fasle。表表达式 其中表达式表Application对象的变量。关闭屏幕更新可加快宏的执行速度。这样将看不到宏的执行过程，但宏的执行速度加快了。由于本例中使用了Collection对象，同时该对象中包含了多个成员，屏幕更新，会极大的加快运行速度。案例 删除工作当某个工作表中的数据无效的时候，用户可以直接删除工作表。同时，用户VBA代码来实现删除工在ExcelVBA中，用户使用Worksheets集合对象的Delete方法，可删除集合对象中指某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。首先用户需要根据需要删除某工作表，原始数据如图4.51所示。4.51原始数据SubDeleteWorkSheets()DimStrAsStringStr=Application.InputBox(Prompt:="输入删除的工SubDeleteWorkSheets()DimStrAsStringStr=Application.InputBox(Prompt:="输入删除的工作表：",_Title:="删除工作表",Default:="Sheet1",Type:=2)OnErrorGoToSetWs=Worksheets(Str)Application.DisplayAlerts=FalseApplication.DisplayAlerts=TrueExitSubMsgBox"工作表不存在！"EndSub运行程序代码，选择删除的工作表名称，如图4.52所示。查看删除的结果，如图图4.52选择删除的工作表名 图4.53删除的结再次运行程序代码，选择删除的工作表名称，如图4.54所示。查看删除的图4.54选择删除的工作 图4.55查看删除的结以上代码首先通过InputBox方法获取用户希望删除的工作表名称，再通过DisplayAlerts属性关闭警告提示，就调用Delete方法删除工作表。另外，程序中使用错误捕案例 隐藏工作在用户进行实际开发时，很多工作表中的数据是的，主要用来程序代码所的数据。为了保证整个程序的有效性，这些数据需要隐藏。这个时候，用户需要隐藏工作表xlSheetHiddenxlSheeteryHidden：隐藏工作表。通过该值隐藏的工作表，用户不能通过菜单取消隐藏的工作表，只能通过VBA代码修改工作表的可见状态。xlSheetVisible：显示工作设置Visible属性为True或False，也可显示或隐藏工作表某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。首先用户需要将主要工作表之外的工作表隐藏，原始数据如4.56所示。4.56原始数DimiAsIntegerFori=2ToThisWorkbook.Sheets.CountSheets(i).Visible=xlSheetVeryHiddenEnd隐DimiAsIntegerFori=2ToThisWorkbook.Sheets.CountSheets(i).Visible=xlSheetVeryHiddenEnd运行程序代码，查看隐藏的工作表结果，如图4.57所示4.57隐藏工作表的结显示工作表的VBA代码如Sheets(i).Visible= 案例 打印工作当Excel中的数据是结果的时候，有时需要将结果打印出来。这个时候，用户需要设和垂直分页符。HPageBreaks属性返回HPageBreaks集合，它代表工作表上的水平分页符。每HPageBreak对象代表。如果添加的分页符不和打印区域交叠，则HPageBreakHPageBreaks集合中。如果重新调整HPageBreaks属性类似，VPageBreaksVPageBreakVPageBreak对象某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。首先用户需要确定工作表的打印页数，原始数据如图4.58所示。4.58原始数SubCountSheetNums()DimrAsLongDimcAsLongDimwsAsWorksheetSetSubCountSheetNums()DimrAsLongDimcAsLongDimwsAsWorksheetSetws=c=ws.HPageBreaks.Count+1r=ws.VPageBreaks.Count+page=r*MsgBox该工作表共可以打印有&page&页"EndSub运行程序代码，查看隐藏的工作表结果，如图4.59所示4.59查看打印的页在上面的代码中，首先获HPageBreaks集合VPageBreaks集合中包含对象的数量，案例 选择单元格xcel的主要功能是编辑单元格的数据，但是有时xcel工作表的数据是结果。为了防止用户错误操作，有时用户需要选择工作表的单元格。用户只能查看结果，而不能选择和编辑单元格。ExcelVBA中，，可以使Worksheet.EnableSelection属性限制用户选中工作表中的表达式 其中表达式表示Worksheet对象的变量。仅当工作表处于保护状态时，本属性才能起作xlNoSelectionxlUnlockedCells：仅允许Locked属性为FalsexlNoRestrictions某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。首先用户需要保护工作表，用户选择工作表的单元格，原始数据如图4.60所示。4.60原始数SubContorlWith.EnableSelection=.ProtectContents:=True,UserInterfaceOnly:=TrueSubContorlWith.EnableSelection=.ProtectContents:=True,UserInterfaceOnly:=TrueEndWithEnd运行程序代码，查看单元格的结果，如图4.61所示图4.61选择单元从上面的结果中可以看出，限制单元格被选择比保护工作表更加深入。保护工作表的只是保护工作表不被编辑，则限制单元格的时候，用户则不能选择对应的单元格。案例 导出工作表达式表达式.SaveAs(FileName,FileFormat,Password,CreateBackup,AddToMru,TextCodepage,TextVisualLayout,SaveAs方法中，FileName参数是必需的，其他参数都可省略。各参数的含Filename：表示要保存的文件名。可包含完整路径。如果不指定路径，Excel将文件保存FileForat：保存文件时使用的文件格式。对于现有文件，默认采用上一次指定的文件格式；对于新文件，默认采用当前所用Excel版本的格式。文件格式如表4.1所示。Password：用于指定文件的保护。要区分大小写，最长不超过15个字符iteRePasword：一个表示文件写保护的字符串。如果文件保存时带有，但打开文件时不输入，则该文件以只读方式打开。 mended：如果为True，则在打开文件时显示一条消息，提示该文件以CreateBackup：如果为TrueAddoMru如果为True。默认值是FalseTextCodepage和TextVisualLayout：不在英语版的Excel中使用Local：如果为True，则以Excel的语言保存文件。如果为False（默认值），则的语言保存文件4.1保存文件时使用的文件格名值描OfficeExcel加载项Excel2007加载6MacintoshMSDOSWindows-789ExcelExcelExcel2Excel4工作HTML国际加载国际打开XML加载打开XML模打开启用的XML模板打开XML工作打开启用的XML工作簿2模模板MacintoshMSDOS文WindowsUnicode54工作默认工作-常规工作Works2XML电子表某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。用户需将该工作表的数据导出到文本文档，原始数据如图4.624.62原始数SubDimwsAsWorksheetDimstrAsStringApplication.ScreenUpdating=FalseApplication.DisplayAlerts=FalsestrApplication.InputBox(Prompt:="设置文本文件名称：",_Title:="SubDimwsAsWorksheetDimstrAsStringApplication.ScreenUpdating=FalseApplication.DisplayAlerts=FalsestrApplication.InputBox(Prompt:="设置文本文件名称：",_Title:="导出文件",Type:=2)Ifstr=""ThenExitSubSetws=ActiveSheetws.SaveAsFilename:=str&".txt",FileFormat:=xlUnicodeTextApplication.DisplayAlerts=TrueApplication.ScreenUpdating=TrueEnd运行程序代码，查看单元格的结果，如图4.63所示4.63导出文单击“确定”按钮，查看保存的销售表，如图4.64所示4.64导出的文在Excel工作表中，行和列是比较特殊的对象。行和列对象隶属于工作表对象，而高于单元格对象。用户可以把工作表看作是由多个行或者多个列组成的对象。在Excel的操作中，行和列的操作是十分常见的操作，用户可以用VBA代码进行行和列的操作。案例 单行Excel中的数据按照行和列规律排列，当用户需要调整这些排列时，可以执行数在ExcelVBA中，用户可以通过Range对象的Insert方法单元格或单元格区域该方法的语法格式表达式.Insert(Shift, Insert方法的两个参数都可以省略，参数含义如下：Shift：指定单元格的调整方式，设置其他单元格相应移位以腾出空间。可以为常量xlShiftoRight（向右移动单元格）或xlShiftDon（向下移动单元格。如果省略此参数，xcel将根据区域的形状确定调整方式。CopyOrigin：的起点某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。用户需要将在工作表的数据行中新的数据行，原始数据如图4.65所示。4.65原始数SubDimintRowAsSubDimintRowAsintRow=ActiveCell.RowEnd选择单元格A4，然后运行程序代码，查看单行的结果，如图4.66所示图4.66当行数据在上面代码中，为了简化案例，省略了Insert方法的参数，行后当前单元格所在案例78多在Excel中，用户可以单行数据，同时也可以多行数据。用户可以使用代码完成多行的任务，否则用户需要多次运行单行的代码个空行，要多个空行则需要循环执行多次Insert方法。本例首先通过Rows.Count属性获取选择区域的行数，再根据该行数决定循环的次数，最后可完成多个空行的操作。某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。用户需要将在工作表的数据行中多行数据行，原始数据如图4.67所示。4.67原始数SubInsertMoreRows()DimiAsLongDimSubInsertMoreRows()DimiAsLongDimintRowsAsintRows=Selection.Rows.CountFori=1TointRowsintRow=Selection.RowEnd选择5～6行的单元格，如图4.68所示。运行程序代码，查看多行的结果，如图4.68选择单元格区 图4.69查看的结上面代码实现的功能和用户直接选中多行单元格然后选择的结果一样0所示。图4.70选择多案例 数据列面小节中，已经演示了如何通过VBA代码多行数据。用户同样可以通过代码数据列在ExcelVBA中，用户可以通过Range对象的Insert方法单元格或单元格区域。表达式.Insert(Shift, Insert方法的两个参数都可以省略，参数含义如下：Shift：指定单元格的调整方式，设置其他单元格相应移位以腾出空间。可以为常量xlShiftoRight（向右移动单元格）或xlShiftDon（向下移动单元格。如果省略此参数，xcel将根据区域的形状确定调整方式。CopyOrigin：的起点某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。用户需要将在工作表的数据行中数据列，原始数据如图4.71所示。4.71原始数SubInsertMoreCols()DimintColAsLongDimiAsLongDimintColsAsSubInsertMoreCols()DimintColAsLongDimiAsLongDimintColsAsintCols=Selection.Columns.CountFori=1TointColsintCol=Selection.ColumnEnd选择工作表的单个数据列，如图4.72所示。运行程序代码，得到的结果如图4.73示图4.72选择单个数据 图4.73的结选择工作表中的多个数据列，如图4.74所示。运行程序代码，得到的结果如图图4.74选择多个数据 图4.75的结案例 隐藏数据在ExcelVBA中，用户可以通过Range对象的Hidden属性设置是否隐藏Hidden属性设置为True以隐藏行或列。指定的区域必须占据整个行或整个列。某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。用户需要将在对数据行进行隐藏，原始数据如4.76所示。4.76原始数DimintRowAsIntegerSubHideCellRow()DimWsAsWorksheetSetWs=DimintRowAsIntegerSubHideCellRow()DimWsAsWorksheetSetWs=IfintRow<>0IfWs.Rows(intRow).HiddenThenWs.Rows(intRow).Hidden=FalseintRow=0EndintRow=Ws.Rows(intRow).Hidden=TrueWs.Rows(intRow).Hidden=TrueEndIfEnd选择工作表的单元格，运行程序代码，查看运行的结果如图4.77所示。再代码，得到的结果如图4.78所示图4.77隐藏单元 图4.78显示单元在上面的代码中，首先定义模块变量intRow，通过该变量的值是否为0，判断是进行案例 隐藏数据和前面小节的例子类似，用户可以通过VBA代码来隐藏工作表中的在ExcelVBA中，用户可以通过Range对象的Hidden属性设置是否隐藏Hidden属性设置为True以隐藏行或列。指定的区域必须占据整个行或整个列。某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。用户需要将在对数据列进行隐藏，原始数据如4.79所示。4.79原始数DimintColsAsLongSubHideCellColumns()DimwsAsWorksheetSetws=IfDimintColsAsLongSubHideCellColumns()DimwsAsWorksheetSetws=IfintCols<>0Ifws.Columns(intCols).HiddenThenws.Columns(intCols).Hidden=FalseintCols=0EndintCols=ActiveCell.Columnws.Columns(intCols).Hidden=TrueEndEnd选择工作表的单元格，运行程序代码，查看运行的结果如图4.80所示。再代码，得到的结果如图4.81所示 图4.80选择数据 图4.81隐藏数据再次运行程序代码，查看运行的结果，如图4.824.82显示数据上面代码的原理和前面小节例子的原理类似，这里就不重复解释。案例 设置单元格的行在Excel工作表中，用户可以设置单元格的行高或者列宽，来设置数据的显示效果。用户同样可以通过VBA代码实现该功能。在ExcelVBA中，用户要设置工作表中行的高度，需使用Range对象的RowHeight高不等，则返null。如果要返回多行的RowHeight属性，可得到每一行的行高（如果所有的行等高），或得到null（如果它们不等高。如果要返回几行的Height属性，将得到所有行高的总和。某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。用户需要将设置单元格的行高，原始数据如4.83所示。4.83原始数SubDimintHeightAsLongDimrAsLongDimiSubDimintHeightAsLongDimrAsLongDimiAsDimintRowsAsIntegerDimWsAsintHeight=Application.InputBox(prompt:="输入单元格的行高：",_Title:="输入行高",Type:=1)SetWs=intRows=Selection.Rows.Countr=ActiveCell.RowFori=1ToWs.Rows(rWs.Rows(r+i-1).RowHeight=EndSub本例的主要功能是设置所选单元格区域的行高。其主要的操作如下：选择单元格区域。选择单元格A4：D7区域，如图4.84运行程序代码，在框中输入设置的数值，在本例中输入45，如图4.85所示图4.84选择单元格区 图4.85设置单元格的行单击“确定”按钮，可以查看设置完成的结果如图4.864.86设置行高的结用户可以使用AutoFit方法，可更改区域中的列宽或行高以达到最佳匹配，在本例中，就不详细介绍。案例 设置单元格的列和前面例子类似，用户可以使用VBA代码设置单元格的本例使用的方法与上例类似，只是将Range对象的RowHeight属性改为属性某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。用户需要将设置单元格的列宽，原始数据如4.87所示。4.87原始数SubDimintWidthAsLongDimcAsLongDimiAsDimSubDimintWidthAsLongDimcAsLongDimiAsDimintColsAsIntegerDimwsAsintWidthApplication.InputBox(prompt:="输入单元格的列宽：",_Title:="输入列度",Type:=1)Ifw=0ThenExitSetSetws=c=ActiveCell.ColumnFori=1Tows.Columns(c+i-1).ColumnWidth=EndSub本例的主要功能是设置所选单元格区域的行高。其主要的操作如下：设置的数值，在本例中输入150，如图4.88所示。单击“确定”按钮，可以查看设置完成的结果如图4.89图4.88设置列 图4.89查看设置的结Sub自动调整列宽()End同理，用户可以设置单元格的自Sub自动调整列宽()End为了让用户更加便利的开发 给用户提供了工作表的常见事件。灵活使用这事件，用户可以编写出符合操作的各种程序。本小节中，将选择几种常见的工作表事件，来说明如何使用工作表事件。案例 记录修改的单元当用户对Excel工作表的数据进行修改的时候，如果用户需要处理或者分析修改的工作，可以在Worksheet对象的Chang事件中，编写相应的代码。PrivateSubWorksheetChange(ByValTargetAsEnd在ExcelVBA中，oksheet对象的Chang事件发生在用户更改工作表中的单元格，或外部引起单元格的更改PrivateSubWorksheetChange(ByValTargetAsEnd其中的参数Target表示被修改的数据区域，是Range对象的变量。通过该参数可获取数据被修改的单元格区域。及时表明文档的修改信息，对所有修改的单元格都设置其底纹，原始数据如图4.90所示。4.90原始数PrivateSubWorksheetChange(ByValTargetAsTarget.Interior.ColorIndexPrivateSubWorksheetChange(ByValTargetAsTarget.Interior.ColorIndex=End 选择修改单元格D5中的数值，查看修改后的结果，如图4.914.91查看修改的结在Excel中，当单元格在重新计算过更改时，将不会发生Worksheet_Change事件。如果需要对计算过程进行编写，则需要使用Calculate事件。案例 激活时自动排当用户需要Excel工作表进行初始化设置的时候，可以在工作表的激活事件中编写对在ExcelVBA中，当用户激活工作簿、工作表、图表工作表或嵌入式图表时发生Worksheet对象的Activate事件。本例在工作表的Activate事件过编写代码，也就是将单元格区域按照A列的数据进行排序，而排序则需要使用Sort方法。表达式.Sort(Key1Order1Key2TypeOrder2Key3Order3HeaderOrderCustomMatchCase,Orientation,SortMethod,DataOption1,DataOption2,DataOption3)在ExcelVBA中表达式.Sort(Key1Order1Key2TypeOrder2Key3Order3HeaderOrderCustomMatchCase,Orientation,SortMethod,DataOption1,DataOption2,DataOption3)其中表达式表示代表Range对象的变量。参数的Key1：指定第一排序字段，作为区称（字符串）或Range对象；确定要排序的Order1：确定Key1中指定的值的排序次序。其取值是XlSortOrder中的数值Key2：第二排序字段；对数据表进行排序时不能使用Type其中，XlSortOrder取值主要有下面两个：xlAscendingxlDescending某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。默认情况下，数据表中的数据是乱序的。现在需要在用户激活该数据工作表后，对数据按照排序，原始数据如图4.92所示。4.92原始数PrivateSubRange("A2:A10").SortKey1:=Range("A1"),Order1:=xlAscendingEndSub激活时自动排序的VBAPrivateSubRange("A2:A10").SortKey1:=Range("A1"),Order1:=xlAscendingEndSub首先选择其他工作表，然后激活工作表Sheet1，查看结果，如图4.93所示4.93查看排序结上面的程序代码使用的简单的Sort方法，如果用户需要进行复杂的排序，则需要详细设案例 记录区域内的修改信xcelVBA提供了工作表的change的何记录单元格范围内的修改信息。ExcelVBA中，Range对象没有对应Change事件，因此，在处理这类问题的时候，只能通过调用Worksheet_Change事件。当该事件处理程序过程的时候，将接受一个Range对象作为它的参数值。这个Range对象代表内容发生改变的一个或者多个单元格。同时，通过设定区域的单元格范围，然后将系统的Range对象和该单元格范围交及时表明文档的修改信息，对有数据区域的修改单元格设置底纹，原始数据如图4.94所示。4.94原始数PrivateSubWorksheetChange(ByValTargetAsRange)DimSourceRangeAsRangeName:="Source",RefersToR1C1:="=Sheet1!R1C1:Sheet1!R10C4"PrivateSubWorksheetChange(ByValTargetAsRange)DimSourceRangeAsRangeName:="Source",RefersToR1C1:="=Sheet1!R1C1:Sheet1!R10C4"SetSourceRange=Range("Source")IfNot(Intersect(Target,SourceRange)IsNothing)ThenTarget.Interior.ColorIndex=7MsgBox"在数据区域内有修改！"EndIfEnd在已经数据区域之外添加新的数据，查看程序的结果，如图4.95所示4.95查看修改的结在数据区域范围内修改单元格的数值，结果如图4.96所示4.96修改区域范围内的数在本例中，如果Interect函数返回othing，表明这两个单元格区域没有共同的单元格。这里使用了Not就会返回TRUE。因此，如果内容有变化的单无格区域与已知单元格区域有共同的单元格，就会显示一个消息框。否则，退出本过程。案例 突显选择的单元当需要对用户在Excel工作表中所作选择进行记录的时候，用户可以使用PrivateSubWorksheetSelectionChange(ByValTargetAsPrivateSubWorksheetSelectionChange(ByValTargetAsEnd参数Target为新选及时表明文档的修改信息，对有数据区域的修改单元格设置底纹，原始数据如图4.97所示。4.97原始数PrivateSubWorksheetSelectionChange(ByValTargetAsRange)Cells.Interior.ColorIndex=xlNoneActiveCell.EntireColumn.Interior.ColorIndex=4ActiveCell.EntireRow.Interior.ColorIndexPrivateSubWorksheetSelectionChange(ByValTargetAsRange)Cells.Interior.ColorIndex=xlNoneActiveCell.EntireColumn.Interior.ColorIndex=4ActiveCell.EntireRow.Interior.ColorIndex=End选择单元格，查看程序运行的结果，如图4.984.98查看程序运行的结 事件的主要功能是当用户的光标发生变化时，就会触发该案例 添加快捷菜在ExcelVBA中，对用户的左键和右键操作当作不同的处理。当用户需要处理鼠标的右键操作时，将触发BeforeRightClick事件。要增加工作表的快捷菜单项，需要捕获鼠标的右击操作。使用Worksheet对象的 事件可捕获工作表中的右击操作。右击工作表时发生BeforeRightClick事件，此事件先于默认的右击操作。该事件过程的PrivatePrivateSubWorksheetBeforeRightClick(ByValTargetAsRange,CancelAsEnd参数含义如下：Target表示一个Range对象，为双击发生时最靠近鼠标指针的单元格某公司统计了各员工的上下半年销量，现在需要对该数据进行处理。现在需要设置数据分析功能，当用户在单元格范围C1～D10内单击鼠标右键时，添加新的自定义菜单选项，原始数据如图4.99所示。4.99原始数添加快捷菜单选项的VBA代码如PrivatePrivateSubWorksheetBeforeRightClick(ByValTargetAsRange,CancelAsBoolean)DimicbcAsObjectForEachicbcIn Ificbc.Tag="brccm"Thenicbc.DeleteNextIfNotApplica