VBA常用代码(60页).docx

《VBA常用代码(60页).docx》由会员分享，可在线阅读，更多相关《VBA常用代码(60页).docx（60页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、-VBA常用代码-第 60 页excel常用宏宏代码大全本示例为设置密码窗口 (1)If Application.InputBox(请输入密码：) = 1234 ThenA1 = 1 密码正确时执行Else: MsgBox 密码错误，即将退出！ 此行与第2行共同设置密码End If本示例为设置密码窗口 (1)X = MsgBox(是否真的要结帐？, vbYesNo)If X = vbYes ThenClose本示例为设置工作表密码ActiveSheet.Protect Password:=641112 保护工作表并设置密码ActiveSheet.Unprotect Password:=6411

2、12 撤消工作表保护并取消密码本示例关闭除正在运行本示例的工作簿以外的其他所有工作簿，并保存其更改内容。For Each w In WorkbooksIf w.Name ThisWorkbook.Name Thenw.Close SaveChanges:=TrueEnd IfNext w每次打开工作簿时，本示例都最大化 Microsoft Excel 窗口。Application.WindowState = xlMaximized本示例显示活动工作表的名称。MsgBox The name of the active sheet is & ActiveSheet.Name本示例保存当前活动工作簿

3、的副本。ActiveWorkbook.SaveCopyAs C:TEMPXXXX.XLS下述过程激活工作簿中的第四张工作表。Sheets(4).Activate下述过程激活工作簿中的第1张工作表。Worksheets(1).Activate本示例通过将 Saved 属性设为 True 来关闭包含本段代码的工作簿，并放弃对该工作簿的任何更改。ThisWorkbook.Saved = TrueThisWorkbook.Close本示例对自动重新计算功能进行设置，使 Microsoft Excel 不对第一张工作表自动进行重新计算。Worksheets(1).EnableCalculation =

4、False下述过程打开 C 盘上名为 MyFolder 的文件夹中的 MyBook.xls 工作簿。Workbooks.Open (C:MyFolderMyBook.xls)本示例显示活动工作簿中工作表 sheet1 上单元格 A1 中的值。MsgBox Worksheets(Sheet1).Range(A1).Value本示例显示活动工作簿中每个工作表的名称For Each ws In WorksheetsMsgBox ws.NameNext ws本示例向活动工作簿添加新工作表 , 并设置该工作表的名称?Set NewSheet = Worksheets.AddNewSheet.Name =

5、 current Budget本示例将新建的工作表移到工作簿的末尾Private Sub Workbook_NewSheet(ByVal Sh As Object)Sh.Move After:=Sheets(Sheets.Count)End Sub本示例将新建工作表移到工作簿的末尾Private Sub App_WorkbookNewSheet(ByVal Wb As Workbook, _ByVal Sh As Object)Sh.Move After:=Wb.Sheets(Wb.Sheets.Count)End Sub本示例新建一张工作表，然后在第一列中列出活动工作簿中的所有工作表的名称。

6、Set NewSheet = Sheets.Add(Type:=xlWorksheet)For i = 1 To Sheets.CountNewSheet.Cells(i, 1).Value = Sheets(i).NameNext i本示例将第十行移到窗口的最上面?Worksheets(Sheet1).ActivateActiveWindow.ScrollRow = 10当计算工作簿中的任何工作表时，本示例对第一张工作表的 A1:A100 区域进行排序。Private Sub Workbook_SheetCalculate(ByVal Sh As Object)With Worksheets

7、(1).Range(a1:a100).Sort Key1:=.Range(a1)End WithEnd Sub本示例显示工作表 Sheet1 的打印预览。Worksheets(Sheet1).PrintPreview本示例保存当前活动工作簿?ActiveWorkbook.Save本示例保存所有打开的工作簿，然后关闭 Microsoft Excel。For Each w In Application.Workbooksw.SaveNext wApplication.Quit下例在活动工作簿的第一张工作表前面添加两张新的工作表?Worksheets.Add Count:=2, Before:=Sh

8、eets(1)本示例设置 15 秒后运行 my_Procedure 过程，从现在开始计时。Application.OnTime Now + TimeValue(00:00:15), my_Procedure本示例设置 my_Procedure 在下午 5 点开始运行。Application.OnTime TimeValue(17:00:00), my_Procedure本示例撤消前一个示例对 OnTime 的设置。Application.OnTime EarliestTime:=TimeValue(17:00:00), _Procedure:=my_Procedure, Schedule:=F

9、alse每当工作表重新计算时，本示例就调整 A 列到 F 列的宽度。Private Sub Worksheet_Calculate()Columns(A:F).AutoFitEnd Sub本示例使活动工作簿中的计算仅使用显示的数字精度。ActiveWorkbook.PrecisionAsDisplayed = True本示例将工作表 Sheet1 上的 A1:G37 区域剪下，并放入剪贴板。Worksheets(Sheet1).Range(A1:G37).CutCalculate 方法计算所有打开的工作簿、工作簿中的一张特定的工作表或者工作表中指定区域的单元格，如下表所示：要计算 依照本示例所

10、有打开的工作簿 Application.Calculate （或只是 Calculate）指定工作表 计算指定工作表Sheet1 Worksheets(Sheet1).Calculate指定区域 Worksheets(1).Rows(2).Calculate本示例对自动重新计算功能进行设置，使 Microsoft Excel 不对第一张工作表自动进行重新计算。Worksheets(1).EnableCalculation = False本示例计算 Sheet1 已用区域中 A 列、B 列和 C 列的公式。Worksheets(Sheet1).UsedRange.Columns(A:C).Cal

11、culate本示例更新当前活动工作簿中的所有链接?ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources本示例设置第一张工作表的滚动区域?Worksheets(1).ScrollArea = a1:f10本示例新建一个工作簿，提示用户输入文件名，然后保存该工作簿。Set NewBook = Workbooks.AddDofName = Application.GetSaveAsFilenameLoop Until fName FalseNewBook.SaveAs Filename:=fName本示例打开 Analysis.xls

12、工作簿，然后运行 Auto_Open 宏。Workbooks.Open ANALYSIS.XLSActiveWorkbook.RunAutoMacros xlAutoOpen本示例对活动工作簿运行 Auto_Close 宏，然后关闭该工作簿。With ActiveWorkbook.RunAutoMacros xlAutoClose.CloseEnd With在本示例中，Microsoft Excel 向用户显示活动工作簿的路径和文件名称。Sub UseCanonical()Display the full path to user.MsgBox ActiveWorkbook.FullNameU

13、RLEncodedEnd Sub本示例显示当前工作簿的路径及文件名（假定尚未保存此工作簿）。MsgBox ActiveWorkbook.FullName本示例关闭 Book1.xls，并放弃所有对此工作簿的更改。Workbooks(BOOK1.XLS).Close SaveChanges:=False本示例关闭所有打开的工作簿。如果某个打开的工作簿有改变，Microsoft Excel 将显示询问是否保存更改的对话框和相应提示。Workbooks.Close本示例在打印之前对当前活动工作簿的所有工作表重新计算?Private Sub Workbook_BeforePrint(Cancel As

14、 Boolean)For Each wk In Worksheetswk.CalculateNextEnd Sub本示例对查询表一中的第一列数据进行汇总，并在数据区域下方显示第一列数据的总和。Set c1 = Sheets(sheet1).QueryTables(1).ResultRange.Columns(1)c1.Name = Column1c1.End(xlDown).Offset(2, 0).Formula = =sum(Column1)本示例取消活动工作簿中的所有更改?ActiveWorkbook.RejectAllChanges本示例在商业问题中使用规划求解函数，以使总利润达到最大

15、值。SolverSave 函数将当前问题保存到活动工作表上的某一区域。Worksheets(Sheet1).ActivateSolverResetSolverOptions Precision:=0.001SolverOK SetCell:=Range(TotalProfit), _MaxMinVal:=1, _ByChange:=Range(C4:E6)SolverAdd CellRef:=Range(F4:F6), _Relation:=1, _FormulaText:=100SolverAdd CellRef:=Range(C4:E6), _Relation:=3, _FormulaTe

16、xt:=0SolverAdd CellRef:=Range(C4:E6), _Relation:=4SolverSolve UserFinish:=FalseSolverSave SaveArea:=Range(A33)本示例隐藏 Chart1、Chart3 和 Chart5。Charts(Array(Chart1, Chart3, Chart5).Visible = False当激活工作表时，本示例对 A1:A10 区域进行排序。Private Sub Worksheet_Activate()Range(a1:a10).Sort Key1:=Range(a1), Order:=xlAscen

17、dingEnd Sub本示例更改 Microsoft Excel 链接。ActiveWorkbook.ChangeLink c:excelbook1.xls, _c:excelbook2.xls, xlExcelLinks本示例启用受保护的工作表上的自动筛选箭头?ActiveSheet.EnableAutoFilter = TrueActiveSheet.Protect contents:=True, userInterfaceOnly:=True本示例将活动工作簿设为只读?ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly本示例使共享工作簿每三分

18、钟自动更新一次?ActiveWorkbook.AutoUpdateFrequency = 3下述 Sub 过程清除活动工作簿中 Sheet1 上的所有单元格的内容。Sub ClearSheet()Worksheets(Sheet1).Cells.ClearContentsEnd Sub本示例对所有工作簿都关闭滚动条?Application.DisplayScrollBars = False如果具有密码保护的工作簿的文件属性没有加密，则本示例设置指定工作簿的密码加密选项。Sub SetPasswordOptions()With ActiveWorkbookIf .PasswordEncrypti

19、onProvider Microsoft RSA SChannel Cryptographic Provider Then.SetPasswordEncryptionOptions _PasswordEncryptionProvider:=Microsoft RSA SChannel Cryptographic Provider, _PasswordEncryptionAlgorithm:=RC4, _PasswordEncryptionKeyLength:=56, _PasswordEncryptionFileProperties:=TrueEnd IfEnd WithEnd Sub在本示例

20、中，如果活动工作簿不能进行写保护，那么 Microsoft Excel 设置字符串密码以作为活动工作簿的写密码。Sub UseWritePassword()Dim strPassword As StringstrPassword = secret Set password to a string if allowed.If ActiveWorkbook.WriteReserved = False ThenActiveWorkbook.WritePassword = strPasswordEnd IfEnd Sub在本示例中，Microsoft Excel 打开名为 Password.xls 的

21、工作簿，设置它的密码，然后关闭该工作簿。本示例假定名为 Password.xls 的文件位于 C: 驱动器上。Sub UsePassword()Dim wkbOne As WorkbookSet wkbOne = Application.Workbooks.Open(C:Password.xls)wkbOne.Password = secretwkbOne.Close注意 Password 属性可读并返回 “*”。End Sub本示例将 Book1.xls 的当前窗口更改为显示公式。Workbooks(BOOK1.XLS).Worksheets(Sheet1).ActivateActiveWi

22、ndow.DisplayFormulas = True本示例接受活动工作簿中的所有更改?ActiveWorkbook.AcceptAllChanges本示例显示活动工作簿的路径和名称Sub UseCanonical()MsgBox 消息框b7 = ActiveWorkbook.FullName 当前工作簿b8 = ActiveWorkbook.FullNameURLEncoded 活动工作簿End Sub本示例显示 Microsoft Excel 启动文件夹的完整路径。MsgBox Application.StartupPath本示例显示活动工作簿中每个工作表的名称。For Each ws I

23、n WorksheetsMsgBox ws.NameNext ws本示例关闭除正在运行本示例的工作簿以外的其他所有工作簿，并保存其更改内容。For Each w In WorkbooksIf w.Name ThisWorkbook.Name Thenw.Close savechanges:=TrueEnd IfNext wActivate 事件激活一个工作簿、工作表、图表或嵌入图表时产生此事件。当激活工作表时，本示例对 A1:A10 区域进行排序。Private Sub Worksheet_Activate()Range(a1:a10).Sort Key1:=Range(a1), Order:

24、=xlAscendingEnd SubCalculate 事件对于 Worksheet 对象，在对工作表进行重新计算之后产生此事件每当工作表重新计算时，本示例就调整 A 列到 F 列的宽度。Private Sub Worksheet_Calculate()Columns(A:F).AutoFitEnd SubBeforeDoubleClick 事件应用于 Worksheet 对象的 Activate 方法。当双击某工作表时产生此事件，此事件先于默认的双击操作。Private Sub expression_BeforeDoubleClick(ByVal Target As Range, Canc

25、el As Boolean)expression 引用在类模块中带有事件声明的 Worksheet 类型对象的变量。Target 必需。双击发生时最靠近鼠标指针的单元格。Cancel 可选。当事件发生时为 False。如果事件过程将该参数设为 True，则该过程执行完之后将不进行默认的双击操作。BeforeRightClick 事件应用于 Worksheet 对象的 Activate 方法。当用鼠标右键单击某工作表时产生此事件，此事件先于默认的右键单击操作。Private Sub expression_BeforeRightClick(ByVal Target As Range, Cancel

26、 As Boolean)expression 引用在类模块中带有事件声明的 Worksheet 类型对象的变量。Target 必需。右键单击发生时最靠近鼠标指针的单元格。Cancel 可选。当事件发生时为 False。如果该事件过程将本参数设为 True，则该过程执行结束之后不进行默认的右键单击操作。Change 事件当用户更改工作表中的单元格，或外部链接引起单元格的更改时产生此事件。Private Sub Worksheet_Change(ByVal Target As Range)Target 更改的区域。可以是多个单元格。说明重新计算引起的单元格更改不触发本事件。可使用 Calculat

27、e 事件俘获工作表重新计算操作。本示例将更改的单元格的颜色设为蓝色。Private Sub Worksheet_Change(ByVal Target as Range)Target.Font.ColorIndex = 5End SubDeactivate 事件图表、工作表或工作簿从活动状态转为非活动状态时产生此事件。Private Sub object_Deactivate()object Chart、Workbook 或者 Worksheet。有关对 Chart 对象使用事件的详细信息，请参阅 Chart 对象事件的用法。本示例当工作簿转为非活动状态时，对所有打开的窗口进行排列。Priva

28、te Sub Workbook_Deactivate()Application.Windows.Arrange xlArrangeStyleTiledEnd SubFollowHyperlink 事件当单击工作表上的任意超链接时，发生此事件。对于应用程序级或工作簿级的事件，请参阅 SheetFollowHyperlink 事件。Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)Target Hyperlink 类型，必需。一个代表超链接目标位置的 Hyperlink 对象。本示例对在当前活动工作簿中访问过的所有链接保

29、留一个列表或历史记录。Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)With UserForm1.ListBox1.AddItem Target.Address.ShowEnd WithEnd SubPivotTableUpdate 事件发生在工作簿中的数据透视表更新之后。Private Sub expression_PivotTableUpdate(ByVal Target As PivotTable)expression 引用在类模块中带有事件声明的 Worksheet 类型对象的变量。Target 必需。选

30、定的数据透视表。本示例显示一则消息，说明数据透视表已经更新。本示例假定您已在类模块中声明了带有事件的 Worksheet 类型的对象。Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)MsgBox The PivotTable connection has been updated.End SubSelectionChange 事件当工作表上的选定区域发生改变时，将产生本事件。Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)Tar

31、get 新选定的区域。本示例滚动工作簿窗口，直至选定区域位于窗口的左上角。Private Sub Worksheet_SelectionChange(ByVal Target As Range)With ActiveWindow.ScrollRow = Target.Row.ScrollColumn = Target.ColumnEnd WithEnd Sub本示例显示活动工作簿中工作表 sheet1 上单元格 A1 中的值。MsgBox Worksheets(Sheet1).Range(A1).Value本示例显示活动工作簿中每个工作表的名称。For Each ws In Worksheet

32、sMsgBox ws.NameNext ws本示例向活动工作簿添加新工作表，并设置该工作表的名称。Set newSheet = Worksheets.AddnewSheet.Name = current Budget本示例关闭工作簿 Book1.xls，但不提示用户保存所作更改。Book1.xls 中的所有更改都不会保存。Application.DisplayAlerts = FalseWorkbooks(BOOK1.XLS).CloseApplication.DisplayAlerts = True本示例设置保存文件时显示提示，要求用户输入汇总信息。Application.PromptFor

33、SummaryInfo = True本示例显示 Microsoft Excel 的完整路径。Private Sub aa()MsgBox The path is & Application.PathEnd Sub示例显示每一个可用加载宏的路径及文件名。For Each a In AddInsMsgBox a.FullNameNext aChDir 语句改变当前的目录或文件夹。ChDir path在 Power Macintosh 中，默认驱动器总是改为在 path 语句中指定的驱动器。完整路径指定由卷标名开始，相对路径由冒号 (:) 开始. ChDir 可以辨认路径中指定的别名:ChDir M

34、acDrive:Tmp 在 Macintosh 中本示例显示当前路径分隔符。MsgBox The path separator character is & _Application.PathSeparatorMove 方法将一个指定的文件或文件夹从一个地方移动到另一个地方。语法object.Move destinationMove 方法语法有如下几部分：部分 描述object 必需的。始终是一个 File 或 Folder 对象的名字。destination 必需的。文件或文件夹要移动到的目标。不允许有通配符。CreateFolder 方法创建一个文件夹。语法object.CreateFol

35、der(foldername)reateFolder 方法有如下几部分：部分 描述object 必需的。始终是一个 FileSystemObject 的名字。foldername 必需的。字符串表达式，它标识创建的文件夹。本示例使用 MkDir 语句来创建目录或文件夹。如果没有指定驱动器，新目录或文件夹将会建在当前驱动器中。MkDir MYDIR 建立新的目录或文件夹。Name 语句示例本示例使用 Name 语句来更改文件的名称。示例中假设所有使用到的目录或文件夹都已存在。 在 Macintosh 中，默认驱动器名称是 “HD” 并且路径部分由冒号取代反斜线隔开。Dim OldName, Ne

36、wNameOldName = OLDFILE: NewName = NEWFILE 定义文件名。Name OldName As NewName 更改文件名。OldName = C:MYDIROLDFILE: NewName = C:YOURDIRNEWFILEName OldName As NewName 更改文件名，并移动文件。本示例显示当前默认文件路径。MsgBox The current default file path is & _Application.DefaultFilePath本示例设置替换启动文件夹。Application.AltStartupPath = C:EXCELM

37、ACROSFolderExists 方法如果指定的文件夹存在返回 True，不存在返回 False。语法object.FolderExists(folderspec)本示例在单元格中启用编辑。Application.EditDirectlyInCell = TrueVBA 入门课程Advanced Office 2000 Password Recovery 破解VBA的程序我学VBA时的两本书！excle2000vba开发实例指南晶辰工作室excle2002函数应用秘笈中国铁路出版社程序说明：几种用VBA在单元格输入数据的方法：Public Sub Writes()1- 2 方法，最简单在 中

38、输入单元格名称。1 A1 = 100 在 A1 单元格输入100。2 A2:A4 = 10 在 A2:A4 单元格输入10。3- 4 方法，采用 Range( )， 中输入单元格名称。3 Range(B1) = 200 在 B1 单元格输入200。4 Range(C1:C3) = 300 在 C1:C3 单元格输入300。5- 6 方法，采用 Cells(Row,Column)，Row是单元格行数，Column是单元格栏数。5 Cells(1, 4) = 400 在 D1 单元格输入400。6 Range(Cells(1, 5), Cells(5, 5) = 50 在 E1:E 5单元格输入5

39、0。End Sub你点选任何单元格，按 Selection 按钮，則则所点选的单元格均会被输入文字 Test。Public Sub Selection1()Selection.Value = Test 在任何你点选的单元格输入文字 Test。End SubVBALesson2 程序说明：几种如何把别的工作表 Sheet4 数据，读到这个工作表的方法：在被读取的单元格前加上工作表名称 Sheet4。Public Sub Writes()1- 2 方法，最简单在被读取的 前加上被读取的工作表名称 Sheet4。1 A1 = Sheet4.A1 把Sheet4 A1 单元格的数据，读到 A1单元格。

40、2 A2:A4 = Sheet4.B1 把 Shee4 工作表单元格 B1 数据，读到 A2:A4 单元格。3- 4 方法，在被读取的工作表 Range( )的 Range 前加上被读取的工作表名称Sheet4。3 Range(B1) = Sheet4.Range(B1) 把 Shee4工作表单元格 B1 数据，读到 B1 单元格。4 Range(C1:C3) = Sheet4.Range(C1) 把 Shee4 工作表单元格 C1 数据，读到 C1:C3 单元格。5- 6 方法，在被读取的工作表 Cells(Row,Column)，Cells 前加上被读取工作表名称 Sheet4。5 Cel

41、ls(1, 4) = Sheet4.Cells(1, 4) 把 Shee4 工作表单元格 D1 数据，读到 D1 单元格。6 Range(Cells(1, 5), Cells(5, 5) = Sheet4.Cells(1, 5) 把 Shee4 工作表单元格 E1 数据，读到 E1:E 5单元格。End Sub你点选任何单元格，按 Selection 按钮，则所点选的单元格均会被输入 Shee4 工作表单元格 F1 数据。Public Sub Selection1()Selection.Value = Sheet4.F1 把 Shee4 工作表单元格 F1 数据，读到任何你点选的单元格。End

42、 SubVBALesson3 程序说明：如何利用 Worksheet_SelectionChange 输入数据的方法。Private Sub Worksheet_SelectionChange(ByVal Target As Range)Target = 100End SubTarget 指的是你鼠标所选的单元格，Worksheet_SelectionChange() 事件的参数。可以是一个也可以是好几个单元格。Range 是 Excel 特有的变量形态，叫范围。Target As Rang 是把 Target 这个参数设定为 Range 变量形态。Target = 100 是把你点选的单元格

43、输入数字100。VBALesson4 程序说明：如何利用 Worksheet_SelectionChange 在限定的单元格输入数据的方法。Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Target.Row = 2 And Target.Column = 2 ThenTarget = 100End IfEnd SubIf . Then . End If 这是我们学的这一个逻辑判断语句。Target.Row = 2，指的是鼠标选定的单元格的行大于或等于 2。Target.Column = 2 ，指的是鼠标选定的单元格

44、的栏等于 2。If Target.Row = 2 And Target.Column = 2 Then 指的是只有在Target.Row = 2及Target.Column = 2二个条件成立时。就是 (Target.Row = 2) 为True及(Target.Column = 2)为True时，才执行下面的程序 Target=100，也就是 B 栏第二行及以下行用鼠标被点选时，才会被输入100，其它单元格则不被输入数据。VBALesson5 程序说明：比较 Worksheet_SelectionChange() 与用按钮 CommandButton1_Click() 来执行程序二者的方法与写法有何不同。Worksheet_Selecti