1、vba精典源程序源代码(工作表操作)（VBA classic source program source code (work table operation)）VBA classic source program source code (work table operation).Txt51 is an inexhaustible source of self-confidence, self-confidence is surging waves, confidence is the jet stream ahead of the channel, self-confidence is
2、the real mother of success. example 04-01 add worksheet (Add method)Sub AddWorksheet ()MsgBox adds a worksheet to the current workbook“Worksheets.AddMsgBox adds a worksheet before the worksheet SHEET2 in the current workbook“Worksheets.Add before:=Worksheets (“SHEET2“)MsgBox adds a worksheet after t
3、he worksheet SHEET2 in the current workbook“Worksheets.Add after:=Worksheets (“SHEET2“)MsgBox adds 3 worksheets in the current workbook“Worksheets.Add Count:=3End SubExamples: a Add method with an optional 4 parameter, the parameter Before and parameter After specifies increased sheet position, but
4、can only choose one or two parameters; Count parameter is used to specify the number of work table increased.-example 04-02 replication worksheet (Copy method)Sub CopyWorksheet ()MsgBox “copy worksheet Sheet1 in the current workbook and place the replicated worksheet before the worksheet SHEET2.“Wor
5、ksheets (“Sheet1“).Copy Before:=Worksheets (“SHEET2“)MsgBox “copy worksheet SHEET2 in the current workbook and place the copied worksheet after the worksheet sheet3“Worksheets (“SHEET2“).Copy After:=Worksheets (“sheet3“)End SubThe example shows that the Copy method has 2 optional parameters, that is
6、, the parameter Before and the parameter After, and only two parameters are used when using.-example 04-03 mobile worksheet (Move method)Sub MoveWorksheet ()“MsgBox“ moves worksheet sheet3 to the worksheet SHEET2 in the current workbook“Worksheets (“sheet3“).Move Before:=Worksheets (“SHEET2“)MsgBox
7、moves the worksheet Sheet1 to the last in the current workbook“Worksheets (“Sheet1“).Move After:=Worksheets (Worksheets.Count)End SubThe example shows that the Move method has the same parameters as the Copy method, and the function is the same.-example 04-04 hides and displays worksheet (Visible pr
8、operty)example 04-04-01Sub testHide ()MsgBox “the first hidden worksheet sheet1“Worksheets (“Sheet1“).Visible = FalseMsgBox display worksheet sheet1“Worksheets (“Sheet1“).Visible = TrueMsgBox “second hidden worksheet Sheet1“Worksheets (“Sheet1“).Visible = xlSheetHiddenMsgBox display worksheet sheet1
9、“Worksheets (“Sheet1“).Visible = TrueMsgBox “third hidden worksheet Sheet1“Worksheets (“Sheet1“).Visible = xlSheetHiddenMsgBox display worksheet sheet1“Worksheets (“Sheet1“).Visible = xlSheetVisibleMsgBox “fourth hidden worksheet Sheet1“Worksheets (“Sheet1“).Visible = xlSheetVeryHiddenMsgBox display
10、 worksheet sheet1“Worksheets (“Sheet1“).Visible = TrueMsgBox “fifth hidden worksheet Sheet1“Worksheets (“Sheet1“).Visible = xlSheetVeryHidden本显示工作表 Sheet1”工作表（“Sheet1” ） 。可见= xlsheetvisible端子示例说明：本示例演示了隐藏和显示工作表的各种情形。其中，使用 xlsheetveryhidden常量来隐藏工作表，将不能通过选择工作表菜单栏中的”格式”-“工作表”-“取消隐藏”命令来取消隐藏。-示例 04-04-02
11、 子 showallsheets()本使当前工作簿中的所有工作表都显示（即将隐藏的工作表也显示） ”作为工作表的 WS对于每一个 WSWS =可见=真下一个 WS端子-示例 04-05 获取工作表数（计数属性）示例 04-05-01 子 worksheetnum()暗淡我一样长i =工作表。计数本当前工作簿的工作表数为：“& chr（10）和我端子-示例 04-05-02 子 worksheetnum()暗淡我一样长我=床单。本当前工作簿的工作表数为：“& chr（10）和我端子示例说明：在一个包含图表工作表的工作簿中运行上述两段代码，将会得出不同的结果，原因是对于表集合来讲，工作表包含图表工
12、作表。应注意表集合与表集合的区别，下同。-示例 04-06 获取或设置工作表名称（名字属性）示例 04-06-01 子 nameworksheet()昏暗的名为字符串，字符串 schangenameSNAME =工作表名称（2） 。本当前工作簿中第 2个工作表的名字为：“与国schangename =“我的工作表”本将当前工作簿中的第 3个工作表名改为：“schangename表（3） 。名称= schangename端子示例说明：使用名字属性可以获取指定工作表的名称，也可以设置工作表的名称。-示例 04-06-02 重命名工作表子 renamesheet()昏暗的晶体管作为字符串重试:呃。清
13、晰超强= InputBox（“请输入工作表的新名称：“_， “重命名工作表” ，在当前工作表的名称） 。如果晶体管=“Exit Sub下次继续出错在当前工作表的名称=极强如果错误，数字0，然后MsgBox犯错。数”错误。描述呃。清晰去重试最后如果关于错误 0“.端子工作表（表）基本操作应用示例 22007-06-10 06:29 示例 04-07 激活/选择工作表（激活方法和选择方法）示例 04-07-01 子 selectworksheet()本激活当前工作簿中的工作表篇”工作表（“Sheet2”激活） 。MsgBox activates the worksheet sheet3 in th
14、e current workbook“Worksheets (“sheet3“).SelectMsgBox “select worksheet SHEET2 and sheet3 in workbook at the same time“Worksheets (Array (“SHEET2“, “sheet3“).SelectEnd SubThe example shows that the Activate method can only activate a worksheet, and the Select method can select multiple worksheets si
15、multaneously.-example 04-07-02Sub SelectManySheet ()MsgBox selects the first and third worksheets“Worksheets (1).SelectWorksheets (3).Select FalseEnd Sub-example 04-08 gets the index number (Index attribute) of the current worksheetSub GetSheetIndex ()Dim I As LongI = ActiveSheet.IndexMsgBox “the wo
16、rksheet you are using is called“ & I “End Sub-sample 04-09 selects the previous worksheet (Previous property)Sub PreviousSheet ()If ActiveSheet.Index 1 ThenMsgBox selects the previous worksheet of the current worksheet in the current workbook“ActiveSheet.Previous.ActivateElse“MsgBox“ has reached the
17、 first worksheet“End IfEnd SubThe example shows that if the current worksheet is the first worksheet, the use of the Previous property will go wrong.-sample 04-10 selects the next worksheet (Next property)Sub NextSheet ()If ActiveSheet.Index Worksheets.Count ThenMsgBox selects the next worksheet of
18、the current worksheet in the current workbook“ActiveSheet.Next.ActivateElse“MsgBox“ has reached the last worksheet“End IfEnd SubThe example shows that if the current worksheet is the last worksheet, the use of the Next property will go wrong.-example 04-11 worksheet row and column operationsexample
19、04-11-01 hidden rowsSub HideRow ()Dim iRow As LongMsgBox “hides the rows in which the current cell is located“IRow = ActiveCell.RowActiveSheet.Rows (iRow).Hidden = TrueMsgBox “cancel hidden“ActiveSheet.Rows (iRow).Hidden = FalseEnd Sub-example 04-11-02 hidden columnsSub HideColumn ()Dim iColumn As L
20、ongMsgBox “hide the column of the current cell.“IColumn = ActiveCell.ColumnActiveSheet.Columns (iColumn).Hidden = TrueMsgBox “cancel hidden“ActiveSheet.Columns (iColumn).Hidden = FalseEnd Sub-example 04-11-03 insert rowsSub InsertRow ()Dim rRow As LongMsgBox inserts a row above the current cell“RRow
21、 = Selection.RowActiveSheet.Rows (rRow).InsertEnd Sub-示例 04-11-04 插入列子 insertcolumn()暗柱长本在当前单元格所在行的左边插入一行”柱柱=选择。在当前工作表的列中插入（柱） 。端子-示例 04-11-05 插入多行子 insertmanyrow()本在当前单元格所在行上方插入三行”朦胧的未来一样长，我只要对于我= 1 比 3行行为选择。在当前工作表的列（行） ，插入下一个我端子-示例 04-11-06 设置行高子 setrowheight()本将当前单元格所在的行高设置为 25”朦胧的未来一样长，IROW 长行行=
22、当前活动。iRow =在当前工作表的行（行） 。设定指定行的高度在当前工作表的列（行） 。RowHeight = 25本恢复到原来的行高”在当前工作表的列（行） 。设定指定行的高度= IROW端子-示例 04-11-07 设置列宽子 setcolumnwidth()本将当前单元格所在列的列宽设置为 20”暗柱一样长，icolumn 长柱柱=当前活动。icolumn =在当前工作表的列（柱） 。ColumnWidth在当前工作表的列（柱） 。ColumnWidth = 20本恢复至原来的列宽”在当前工作表的列（柱） 。ColumnWidth = icolumn端子-示例 04-11-08 恢复行
23、高列宽至标准值子 resetrowheightandcolumnwidth()本将当前单元格所在的行高和列宽恢复为标准值”selection.usestandardheight =真Selection.UseStandardWidth =真端子-示例 04-12 工作表标签示例 04-12-01 设置工作表标签的颜色子 setsheettabcolor()本设置当前工作表标签的颜色”activesheet.tab.colorindex = 7端子-示例 04-12-01 恢复工作表标签颜色子 setsheettabcolordefault()本将当前工作表标签颜色设置为默认值”activesh
24、eet.tab.colorindex = - 4142端子-示例 04-12-03 交替隐藏或显示工作表标签子 hideorshowsheettab()本隐藏/显示工作表标签”activewindow.displayworkbooktabs =不activewindow.displayworkbooktabs端子example 04-13 determines the number of pages printed (the HPageBreaks attribute and the VPageBreaks attribute)Sub PageCount ()Dim I As LongI =
25、(ActiveSheet.HPageBreaks.Count + 1) * (ActiveSheet.VPageBreaks.Count + 1)MsgBox “current worksheet together“ & I & “page“End Sub-example 04-14 protection / undo protection worksheetexample 04-14-01Sub ProtectSheet ()MsgBox “protect the current worksheet and set the password.“ActiveSheet.Protect Pass
26、word:= “fanjy“End SubThe example shows that after the code is run, the current worksheet is not allowed to edit unless the worksheet is protected.-example 04-14-02Sub UnprotectSheet ()MsgBox “undo the current worksheet protection.“ActiveSheet.UnprotectEnd SubExample: when you run the code, if the or
27、iginal worksheet is set with a password, then you need to enter the password.-The example 04-14-03 protects all worksheets in the current workbookSub ProtectAllWorkSheets ()On Error Resume NextDim WS As WorksheetDim myPassword As StringMyPassword = InputBox (“please input your password“ & vbCrLf & _
28、“(dont show no input password)“ & vbCrLf & vbCrLf & _“Make sure you dont forget your password.“! “,“ enter the password “For Each WS In ThisWorkbook.WorksheetsWs.Protect (myPassword)Next WSEnd Sub-example 04-14-04 undo the protection of all worksheets in the current workbookSub UnprotectAllWorkSheet
29、s ()On Error Resume NextDim WS As WorksheetDim myPassword As StringMyPassword = InputBox (“please input your password“ & vbCrLf & _“(no input means no password“, “enter password“)For Each WS In ThisWorkbook.WorksheetsWs.Unprotect (myPassword)Next WSEnd Sub-example 04-14-05 can only edit cells that a
30、re not lockedSub OnlyEditUnlockedCells ()Sheets (“Sheet1“).EnableSelection = xlUnlockedCellsActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=TrueEnd SubThe example shows that after running this code, you can edit only the cells that are not locked in the current worksheet, and ot
31、her cells cannot be edited. The unlocked cell refers to the cell or cell area in the “protect“ tab that appears in the pop-up dialog box after the menu “format cell command“ is selected.-example 04-15 delete worksheet (Delete method)Sub DeleteWorksheet ()MsgBox deletes the worksheet SHEET2 in the cu
32、rrent workbook“Application.DisplayAlerts = FalseWorksheets (“SHEET2“).DeleteApplication.Displayalerts = trueEnd Sub示例说明: 本示例代码使用 application.displayalerts = false来屏蔽弹出的警告框.-一些编程方法和技巧 “) thenMsgbox “ 不存在!“ElseSheets (“).ActivateEnd ifEnd Sub- - - - - - - - - - - - - - - - - - -Function sheetexists (s
33、heetname the string).Sheetexists = falseOn error goto nosuchsheetIf len (sheets (sheetname). Name) 0 thenSheetexists = trueExit functionEnd ifNosuchsheet:End function示例说明: 在代码中, 用实际工作表名代替 .-示例 04 - 16 - 03Testingfunction (sub)如果工作表存在则返回 true, 否则为 false测试 doeswksexist1函数Debug. Print doeswksexist1 (“S
34、heet1“)Debug. Print doeswksexist1 (“sheet100“)Debug. Print “-“测试 doeswksexist2函数Debug. Print doeswksexist2 (“Sheet1“)Debug. Print doeswksexist2 (“sheet100“)End Sub- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Function doeswksexist1 (swksname the string).Dim I as longFor I = worksheets
35、. Count to 1 step 1If sheets (I). Name = swksname thenExit isEnd ifNextIf I = 0 thenDoeswksexist1 = falseElseDoeswksexist1 = trueEnd ifEnd function- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Function doeswksexist2 (swksname the string).The worksheet dim WKBOn error summarized next集 WKB =表（swksname）关于错误 0doeswksexist2 = IIF（不是 WKB是什么，真的，假的）端功能-示例 04-17 排序工作表示例 04-17-01 子 sortworksheets1()昏暗的 bsorted布尔昏暗的 nsortedsheets长昏暗的 nsheets长暗淡 Nnsheets =工作。计数nsortedsheets = 0do-while（nsortedsheets 0工作表（n + 1） 。bsorted = false最后如果接下来的 N
