1、程序错误继续执行On Error Resume Next屏幕不更新Application.ScreenUpdating = FalseApplication.ScreenUpdating = True警示为假Application.DisplayAlerts = False关掉文件不保存Windows(oFile.Name).ActivateActiveWorkbook.Close savechanges:=False定义选中区域的坐标dim x,y x = Selection.Row() 行数 y = Selection.Column() 列数单元格所在的行数ActiveCell.Row 活
2、动单元格所在的行数 ActiveCell.Column 活动单元格所在的列数通过使用行列编号，可用 Cells 属性来引用单个单元格。该属性返回代表单个单元格的 Range 对象。下例中，Cells(6,1) 返回 Sheet1 上的单元格 A6，然后将 Value 属性设置为 10。 Sub EnterValue() Worksheets(“Sheet1“).Cells(6, 1).Value = 10 End Sub 因为可用变量替代编号，所以 Cells 属性非常适合于在单元格区域中循环，如下例所示。 Sub CycleThrough() Dim Counter As Integer F
3、or Counter = 1 To 20 Worksheets(“Sheet1“).Cells(Counter, 3).Value = Counter Next Counter End Sub在命名区域中的单元格上循环 下例用 For Each.Next 循环语句在命名区域中的每一个单元格上循环。如果该区域中的任一单元格的值超过 limit 的值，就将该单元格的颜色更改为黄色。 Sub ApplyColor() Const Limit As Integer = 25 For Each c In Range(“MyRange“) If c.Value Limit Then c.Interior.
4、ColorIndex = 27 End If Next c End Sub增加一个 workbooks, name CarrierWorkbooks.AddActiveWorkbook.SaveAs Filename:=“D:BOM Producecarrier.xls“, FileFormat:= _xlNormal, Password:=“, WriteResPassword:=“, ReadOnlyRecommended:=False _, CreateBackup:=False增加一个表单，获取 nameSheets.Addx = ActiveSheet.NameSheets(x).S
5、elect插入一列Range(“E5“).SelectSelection.EntireRow.Insert插入一栏Range(“F6“).SelectSelection.EntireColumn.Insert向右移动一格ActiveCell.Offset(0, -1).Select当前单元格当前单元格的值ActiveCell.FormulaR1C1 = “UseRow”复制表单Windows(“spacebom.xls“).ActivateCells.SelectSelection.CopyWindows(“Bomsetup.xls“).ActivateSheets(“Sheet2“).Sel
6、ectCells.SelectActiveSheet.PasteRange(“A1“).Select复制单元格Windows(“Akiko Resource Budget Plan.xls“).ActivateRange(“BK71“).SelectApplication.CutCopyMode = FalseSelection.CopyWindows(“Book1.xls“).ActivateSheets(“Sheet2“).SelectActiveSheet.Paste当前单元格整栏选择ActiveCell.EntireColumn.Select、整栏复制与粘贴Columns(“C:C“)
7、.SelectSelection.CopySelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False两栏进行交换Columns(“L:L“).SelectSelection.CutColumns(“N:N“).SelectSelection.Insert Shift:=xlToRightDelete:Rows(“2:2“).SelectSelection.Delete Shift:=xlUpRange(“B4“).SelectSelection.Ent
8、ireRow.Delete每列从第 k 栏开始每 5 个一列进行排列：Windows(“bomsetup.xls“).ActivateDim Counter As IntegerFor Counter = 2 To 1000Cells(Counter, 11).SelectIf ActiveCell.Value = “ ThenActiveCell.Offset(1, 0).SelectElseActiveCell.Offset(1, -5).SelectSelection.EntireRow.InsertActiveCell.Offset(-1, 5).SelectRange(Selecti
9、on, Selection.End(xlToRight).SelectSelection.CutActiveCell.Offset(1, -5).SelectActiveSheet.PasteEnd IfNext Counter字体变色Range(“C3“).SelectSelection.Font.ColorIndex = 3单元格变背景色Selection.Interior.ColorIndex=3字体变粗Range(“D4“).SelectSelection.Font.Bold = True在 B 栏中查找是否有 0000 后Columns(“B:B“).SelectSet findxx
10、 = Selection.Find(“0000“)If findxx Is Nothing Then在 B 栏中查找 0000 后，向左移动一格Columns(“B:B“).SelectSelection.Find(What:=“0000“, After:=ActiveCell, LookIn:=xlFormulas, LookAt _:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _False, MatchByte:=False, SearchFormat:=False).ActivateActive
11、Cell.Offset(0, -1).Select在 c 栏中找到 N/a 后用* 替代Columns(“C:C“).SelectSelection.Replace What:=“n/a“, Replacement:=“*“, LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ReplaceFormat:=False排序Cells.SelectSelection.Sort Key1:=Range(“A2“), Order1:=xlAscending, Key2:=Range(“C2“)
12、 _, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _, Orientation:=xlTopToBottom, SortMethod:=xlStroke, DataOption1:= _xlSortNormal, DataOption2:=xlSortNormal自动塞选Cells.SelectSelection.AutoFilterSelection.AutoFilter Field:=10 取消赛选 第 10 栏Selection.AutoFilter Field:=10, Criteria1:
13、=“ ActiveCell.Interior.ColorIndex Then Cells(i, AC).EntireRow.deleteEnd If Next End If End Sub依次打开选定数据夹中的 xls 文件Sub aa()Dim myDialog As FileDialog, oFile As Object, strName As String, n As IntegerDim FSO As Object, myFolder As Object, myFiles As ObjectDim ySet myDialog = Application.FileDialog(msoFi
14、leDialogFolderPicker)n = 1With myDialogIf .Show “ 开始循环。 跳过当前的目录及上层目录。If myname “ Thendnum = dnum + 1End Ifmyname = Dir 查找下一个目录。Loop显示 C: 目录下的名称。MyPath = “c:“ 指定路径。MyName = Dir(MyPath, vbDirectory) 找寻第一项。Do While MyName “.“ And MyName 0 Then ActiveSheet.Tab.ColorIndex = 3Z = Z + Cells(1, 4)NextMsgBox
15、 Zselectioon.CopyFor ccccc = 1 To iSheets(1).DeleteNextSheets(1).Cells.Cleari = 0Z = 0NextEnd Sub添加图表xxx = ActiveSheet.Shapes.AddChart.NameActiveSheet.ChartObjects(xxx).SelectActiveChart.SetSourceData Source:=Range(“A3:F16“)COPY一栏到多栏Rows(1).Copy Destination:=.Rows(“ & SPfileexistcount + 1 & “:“ & SP
16、fileexistcount + Bomrtqty & “)For i = 1 To ActiveSheet.ChartObjects.CountMsgBox ActiveSheet.ChartObjects(i).NameNextActiveSheet.ChartObjects(1).ActivateActiveSheet.ChartObjects(“Chart 1“).Activate=定制模块行为(1) Option Explicit 强制对模块内所有变量进行声明Option Private Module 标记模块为私有，仅对同一工程中其它模块有用，在宏对话框中不显示Option Com
17、pare Text 字符串不区分大小写Option Base 1 指定数组的第一个下标为1(2) On Error Resume Next 忽略错误继续执行VBA 代码,避免出现错误消息(3) On Error GoTo ErrorHandler 当错误发生时跳转到过程中的某个位置(4) On Error GoTo 0 恢复正常的错误提示(5) Application.DisplayAlerts=False 在程序执行过程中使出现的警告框不显示(6) Application.ScreenUpdating=False 关闭屏幕刷新Application.ScreenUpdating=True 打
18、开屏幕刷新(7) Application.Enable.CancelKey=xlDisabled 禁用Ctrl+Break中止宏运行的功能工作簿(8) Workbooks.Add() 创建一个新的工作簿(9) Workbooks(“book1.xls”).Activate 激活名为book1的工作簿(10) ThisWorkbook.Save 保存工作簿(11) ThisWorkbook.close 关闭当前工作簿(12) ActiveWorkbook.Sheets.Count 获取活动工作薄中工作表数(13) ActiveWorkbook.name 返回活动工作薄的名称(14) ThisWo
19、rkbook.Name 返回当前工作簿名称ThisWorkbook.FullName 返回当前工作簿路径和名称(15) ActiveWindow.EnableResize=False 禁止调整活动工作簿的大小(16) Application.Window.Arrange xlArrangeStyleTiled 将工作簿以平铺方式排列(17) ActiveWorkbook.WindowState=xlMaximized 将当前工作簿最大化Dim Found, MyObject, MyCollectionFound = False 设置变量初始值。For Each MyObject In MyCollection 对每个成员作一次迭代。If MyObject.Text = “Hello“ Then 如果 Text 属性值等于“Hello”。Found = True 将变量 Found 的值设成 True。Exit For 退出循环。End IfNext
