excel vba笔记-14.10.07

Tips：chr(10) 换行符 1. 常用指令 Option Explicit    '强制对模块内所有变量进行声明 On Error GoTo Line    '当错误发生时，会立刻转移到 line 行去 On Error Resume Next    '当错误发生时，会立刻转移到发生错误的下一行去 On Erro GoTo 0    '当错误发生时，会立刻停止过程中任何错误处理过程 Application.DisplayAlerts = False    禁止显示提示和警告消息 Application.DisplayAlerts = True    显示提示和警告消息 Application.ScreenUpdating = False    '关闭屏幕更新 Application.ScreenUpdating = True    '打开屏幕更新 Application.EnableEvents = False    禁止事件发生 Application.EnableEvents = True    允许事件发生 Option Base 1    '指定数组的第一个下标为1 Option Compare Text    '字符串不区分大小写 Application.Enable.CancelKey = xlDisabled    '禁用Ctrl+Break中止宏运行的功能 Option Private Module    '标记模块为私有，仅对同一 工程 路基工程安全技术交底工程项目施工成本控制工程量增项单年度零星工程技术标正投影法基本原理 中其它模块有用，在宏对话框中不显示 MsgBox ExecuteExcel4Macro("GET.DOCUMENT(50)")    '查看打印所需页数 2. 单元格 单元格 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 示 单一单元格表示 Range ("A1")    A1单元格 [A1]    A1单元格 Cells(2, 5)    第2行第5列单元格 Cells(5, "A")    A5单元格 多个单元格表示 Range("A1,F5")    A1和F5单元格 Range("A1", "F5")    A1到F5区域单元格 Range("A1:F5")    A1到F5区域单元格 Range("A1:A3", "C2:C4")    A1到C4区域单元格 Range(Cells(1, 1), Cells(4, 3))    A1到C4区域单元格 [D2:F5]    D2到F5区域单元格 [D2,F5]    D2和F5单元格 Union(Range("A1:D4"), Range("E5:H8"))    A1到D4与E5到H8区域单元格 Range("A:A")    A列单元格 Range("1:1")    第1行 Rows(2)    第2行 Rows("2:4")    第2行到第4行 Columns(2)    B列 Columns("B:D")    B列到D列 Range("B3").EntireColumn    B列 Range("B3").EntireRow    第3行 带参数单元格表示（设参数为a和b，a=5，b=2） Range("A" & a)    Aa（A5）单元格 Range("A2:A" & a)    A2到Aa（A5）单元格 Range("A" & b, "A" & a)    Ab（A2）到Aa（A5）单元格 Cells(5, a)    E5单元格 单元格行数和列数 Range("B5").Row    B5所在的行数（=5） Range("B5").Column    B5所在的列数（=2） 单元格偏移 语法：expression.Offset(RowOffset, ColumnOffset) Range("A1").Offset(rowoffset:=2, columnoffset:=3)    A1偏移2行3列（D3） Range("A1").Offset(2, 3)    A1偏移2行3列（D3） 单元格调整大小 语法：expression.Resize(RowSize, ColumnSize) Range("A1").Resize(rowsize:=2, columnsize:=3)    A1起始增至2行3列（A1到C3单元格） Range("A1").Resize(2, 3)    A1起始增至2行3列（A1到C3单元格） 单元格地址表示 With Range("B2:I6") [C8] = .Address     '结果为$B$2:$I$6 [C9] = .Address(0, 0)     '结果为B2:I6 [C10] = .Address(1, 0)     '结果为B$2:I$6 [C11] = .Address(0, 1)     '结果为$B2:$I6 [C12] = .Address(1, 1)     '结果为$B$2:$I$6 End With 单元格上级信息 With Range("B1") [B3] = .Parent.Name    'B3返回B1单元格所在工作表名称 [B4] = .Parent.Parent.Name    'B4返回B1单元格所在工作簿名称 End With 单元格删除 语法：expression.Delete(Shift) Worksheets("Sheet1").Range("A1:D10").Delete Shift:= xlShiftToLeft '删除 Sheet1 的 A1:D10 单元格区域，并将其右侧单元格向左移位 XlDeleteShiftDirection常量(Shift) 值 描述 xlShiftToLeft -4159 右侧单元格左移 xlShiftUp -4162 下方单元格上移       特殊单元格 2.1.1.1 最后一个单元格 Cells(Rows.Count, 1).End(xlUp).Select     '选定A列最后一个单元格 Range("B65536").End(xlUp).Select     '选定B列最后一个单元格 Cells(1, Columns.Count).End(xlToLeft).Select '选定第1行最后一个单元格 2.1.1.2 特定单元格 语法：expression.SpecialCells(Type, Value) Sheet1.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues).Select '选中Sheet1 中的已用区域的文本常量 XlCellType常量(Type) 值 描述 xlCellTypeAllFormatConditions -4172 任意格式单元格 xlCellTypeAllValidation -4174 含有验证条件的单元格 xlCellTypeBlanks 4 空单元格 xlCellTypeComments -4144 含有注释的单元格 xlCellTypeConstants 2 含有常量的单元格 xlCellTypeFormulas -4123 含有公式的单元格 xlCellTypeLastCell 11 使用区域中最后的单元格 xlCellTypeSameFormatConditions -4173 含有相同格式的单元格 xlCellTypeSameValidation -4175 含有相同验证条件的单元格 xlCellTypeVisible 12 所有可见单元格       XlSpecialCellsValue常量(Value) 值 描述 xlErrors 16 错误 xlLogical 4 逻辑值 xlNumbers 1 数字 xlTextValues 2 文本       单元格值的判断 空值的判断 If Range("A1") = "" Then    '判断A1是否为空单元格 If Len([A1]) = 0 Then    '判断A1是否为空单元格 If VBA.IsEmpty([A1]) Then    '判断A1是否为空单元格 数字的判断 If VBA.IsNumeric([A2]) And [A2] <> "" Then        '判断A2是否为数字 If Application.WorksheetFunction.IsNumber([A2]) Then    '判断A2是否为数字 文本的判断 If Application.WorksheetFunction.IsText([A3]) Then    '判断A3是否为文本 If VBA.TypeName([A3].Value) = "String" Then        '判断A3是否为文本 汉字的判断 If [A4] > "z" Then    '判断A4是否为汉字 错误值的判断 If VBA.IsError([A5]) Then        '判断A5是否为错误值 If Application.WorksheetFunction.IsError([A5]) Then    '判断A5是否为错误值 日期的判断 If VBA.IsDate([A6]) Then    '判断A6是否为日期 合并单元格的判断 Range("E2") = IsNull(Range("A1:D7").MergeCells) Then    '判断A1:D7是否存在合并单元格 单元格格式设置 字体格式 With Range("B2").Font    '设置B2单元格的字体格式 .Name = "华文彩云"     '设置字体为华文彩云字体 .FontStyle = "Bold Italic"     '设置字体为粗体和斜体 .Size = 18     '设置字体大小为18 .Color = RGB(0, 255, 0)     '设置字体为RGB中的(0, 255, 0) .ColorIndex = 3     '设置字体颜色为调色板中的3号 .Underline = 2     '设置字体带单下划线 .Strikethrough = True    '设置字体带删除线 .Superscript = True     '设置字体为上标字符 .Subscript = False    '设置字体不为下标字符 End With Range("A1").Font.Bold = True     '设置A1字体为粗体 Range("A1").Font.Italic = True     '设置A1字体为斜体 调色板中颜色的编号 Underline常量 值 描述 xlUnderlineStyleNone -4142 无（默认值） xlUnderlineStyleSingle 2 单下划线 xlUnderlineStyleDouble 3或-4119 双下划线 xlUnderlineStyleSingleAccounting 4 会计用单下划线 xlUnderlineStyleDoubleAccounting 5 会计用双下划线       Strikethrough常量 描述：是否删除线 True 是 False 否（默认值）     Superscript常量 描述：是否为上标字符 True 是 False 否（默认值）     Subscript常量 描述：是否为下标字符 True 是 False 否（默认值）     对齐格式 2.1.1.3 水平对齐 语法： expression.HorizontalAlignment          HorizontalAlignment常量 描述：水平对齐 xlGeneral 常规（默认值） xlLeft 靠左（缩进） xlCenter 居中 xlRight 靠右（缩进） xlFill 填充 xlJustify 两端对齐 xlCenterAcrossSelection 跨列居中 xlDistributed 分散对齐（缩进）     Range("D2").HorizontalAlignment = xlCenter    'D2设置水平居中 2.1.1.4 垂直对齐 语法： expression.VerticalAlignment VerticalAlignment常量 描述：垂直对齐 xlTop 靠上 xlCenter 居中 xlBottom 靠下（默认值） xlJustify 两端对齐 xlDistributed 分散对齐     Range("D2").VerticalAlignment = xlCenter    'D2设置垂直居中 2.1.1.5 缩进 语法：expression.IndentLevel（可为 0 到15之间的整数值-输入小数会取整） With Range("D2") .HorizontalAlignment = xlLeft    '设置D2为左对齐 .IndentLevel = 2    '设置D2缩进量为2 End With 2.1.1.6 文字方向 语法：expression.Orientation（可为 –90 到 90 度之间的整数值） Range("D2").Orientation = 30     '设置D2的文字方向为30度 2.1.1.7 阅读次序 语法：expression .ReadingOrder ReadingOrder常量 描述：返回或设置指定对象的阅读次序 xlContext 根据内容（默认值） xlLTR 从左到右 xlRTL 从右到左     2.1.1.8 单元格缩小字体填充 Range("B2").ShrinkToFit = True    'B2开启缩小字体填充 2.1.1.9 单元格自动换行 Range("B2").WrapText = True    'B2开启自动换行 2.1.1.10 单元格合并 Range("B2:C5").MergeCells = True    'B2:C5区域合并单元格 Range("B2:C5").Merge    'B2:C5区域合并单元格 Range("e1") = Range("b2").MergeArea.Address    '返回单元格所在的合并单元格区域 内部格式 With Range("B2").Interior    '设置B2单元格的内部格式 .ColorIndex = 8     '设置边框内部颜色为调色板中的8号 .Pattern = xlPatternCrissCross     '设置单元格设置内部图案为十字图案 .PatternColorIndex = 6     '设置边框内部图案的颜色为调色板中的6号 End With XlColorIndex常量(ColorIndex) 描述 xlColorIndexAutomatic 自动填充（默认值） xlColorIndexNone 无内部填充     设置ColorIndex和PatternColorIndex也可使用XlColorIndex常量 Pattern常量 描述 xlSolid 实心（默认值） xlGray75 75% 灰色 xlGray50 50% 灰色 xlGray25 25% 灰色 xlGray16 12.5% 灰色 xlGray8 6.25% 灰色 xlHorizontal 水平 条纹 xlVertical 垂直 条纹 xlDown 逆对角线 条纹 xlUp 对角线 条纹 xlChecker 对角线 剖面线 xlSemiGray75 粗 对角线 剖面线 xlLightHorizontal 细 水平 条纹 xlLightVertical 细 垂直 条纹 xlLightDown 细 逆对角线 条纹 xlLightUp 细 对角线 条纹 xlGrid 细 水平 剖面线 xlCrissCross或xlPatternCrissCross 细 对角线 剖面线     边框格式 语法：BorderAround(LineStyle, Weight, ColorIndex, Color) With Range("B2:D4").Borders     '设置B2:D4区域的边框 .LineStyle = xlDot     '设置边框的线条样式为 .Weight = xlMedium     '设置边框的粗细为中等 .ColorIndex = 5     '设置 边框的颜色为调色板中的5号 End With Range("B2:D4").BorderAround xlContinuous, xlMedium, 5 '设置B2:D4区域的外围边框为连续线，中等粗细，颜色为调色板中的5号 XlBordersIndex常量 值 描述 xlDiagonalDown 5 斜下边框 xlDiagonalUp 6 斜上边框 xlEdgeBottom 9 底部边框 xlEdgeLeft 7 左边框 xlEdgeRight 10 右边框 xlEdgeTop 8 顶部边框 xlInsideHorizontal 12 内部水平 xlInsideVertical 11 内部垂直       XlLineStyle常量(LineStyle) 值 描述 xlContinuous 1 实线（默认值） xlDash -4115 虚线 xlDashDot 4 点划相间线. xlDashDotDot 5 划线后跟两个点 xlDot -4118 点式线 xlDouble -4119 双线 xlLineStyleNone -4142 无线条 xlSlantDashDot 13 倾斜的划线.       Weight常量 值 描述 xlHairline 1 虚线（最细） xlMedium -4138 中等 xlThick 4 粗 xlThin 2 细（默认值）       单元格选择 语法：expression.Goto(Reference, Scroll) Range("A1").Select    '选中A1单元格 Range("A1:B5").Activate     '选定A1:B5区域 Application.Goto Reference:=Range("A5"), scroll:=True '选定单元格 A5，并滚动工作表以显示该单元格 Scroll常量 描述：是否滚动窗口单元格使目标区域出现在窗口的左上角 True 是 False 否（默认值）     单元格清除 Range("A1").Clear    '清除A1单元格（包括所有格式） Range("A1").ClearContents    '清除A1单元格的数值 Range("A1").ClearComments    '清除A1单元格的批注 Range("A1").ClearNotes    '清除A1单元格的批注 Range("A1").Comment.Delete    '清除A1单元格的批注 单元格输入 单元格复制 语法：expression.Copy(Destination) Range("A1").Copy    '复制A1单元格 Range("A1").Copy Destination:=Range("A2")    'A1复制到A2（包括所有格式） Range("A1").Copy Range("A2")    'A1复制到A2（带所有格式） 单元格粘贴 语法：expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose) Range("A1").Copy    '复制A1单元格 Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationNone, Skipblanks:=True, Transpose:=True    'A1的数值复制到A4中，其他属性为默认值 Range("A4").PasteSpecial xlPasteValues    'A1的值复制到A4中 Range("A4").PasteSpecial(xlPasteValues)    'A1的值复制到A4中 Paste常量 值 描述 xlPasteAll -4104 全部（默认值） xlPasteAllExceptBorders 7 边框除外 xlPasteColumnWidths 8 列宽 xlPasteComments -4144 批注 xlPasteFormats -4122 格式 xlPasteFormulas -4123 公式 xlPasteFormulasAndNumberFormats 11 公式和数字格式 xlPasteValidation 6 有效性验证 xlPasteValues -4163 数值 xlPasteValuesAndNumberFormats 12 值和数字格式       Operation常量 值 描述 xlPasteSpecialOperationNone -4142 无（默认值） xlPasteSpecialOperationAdd 2 加 xlPasteSpecialOperationSubtract 3 减 xlPasteSpecialOperationMultiply 4 乘 xlPasteSpecialOperationDivide 5 除       SkipBlanks常量 描述：是否跳过空单元格 True 是 False 否（默认值）     Transpose常量 描述：是否进行转置行列 True 是 False 否（默认值）     单元格的输入 Range("A1").Value = Range("A4").Value    'A4的数值赋给A1 单元格查找 Find、FindNext、FindPrevious 语法：expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SerchFormat) expression.FindNext(After) expression.FindPrevious(After) LookIn常量 描述：查找范围 xlFormulas 公式（默认值） xlValues 值 xlComments 批注     XlLookAt常量(LookAt) 值 描述 xlWhole 1 单元格全字匹配 xlPart 2 部分匹配（默认值）       XlSearchOrder常量(SearchOrder) 值 描述 xlByRows 1 按行查找（默认值） xlByColumns 2 按列查找       XlSearchDirection常量(SearchDirection) 值 描述 xlNext 1 查找下一个 xlPrevious 2 查找上一个       MatchCase常量 描述：是否区分大小写 True 是 False 否（默认值）     MatchByte常量 描述：双字节语言支持时使用 True 双字节字符仅匹配双字节字符 False 双字节字符可匹配其等价的单字节字符     每次使用Find方法后，参数LookIn、LookAt、SearchOrder 和MatchByte的设置将保存。如果下次调用Find方法时不指定这些参数的值，就使用保存的值。因此每次使用该方法时请明确设置这些参数。 可以使用 FindNext 和 FindPrevious 方法重复搜索。 当搜索到指定的搜索区域的末尾时，本方法将绕回到区域的开始继续搜索。发生绕转后，若要停止搜索，请保存第一个找到的单元格地址，然后依据该保存地址测试每个后续查找到的单元格地址。 For Each...Next 语句和 Like 运算符 语法：result = string Like pattern Dim c As Range    '设置c的数据类型为单元格 For Each c In [A1:C5]    '查找范围为A1:C5 If c.Font.Name Like "Co*" Then    '查找字体名称开头为Co的单元格 c.Font.Name = "Times New Roman"    '替换该单元格字体为Times New Roman End If Next Pattern 中的字符 符合 string 中的 ? 任何单一字符。 * 零个或多个字符。 # 任何一个数字 (0–9)。 [charlist] charlist.中的任何单一字符。 [!charlist] 不在 charlist 中的任何单一字符。     单元格排序 语法：expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3) Sheet1.UsedRange.Sort _        '对sheet1的已用区域进行排序 key1:=Cells(1, 1), _        '单元格A1作为第一排序关键字 order1:=xlDescending, _        'Key1按降序排序 key2:=Cells(1, 2), _        '单元格B1作为第一排序关键字 order2:=xlAscending, _        'Key2按升序排序 Header:=xlYes, _        '第一行为标题行 MatchCase:=False        '不区分大小写 XlSortOrder常量(order) 值 描述：指定字段或区域的排序顺序 xlAscending 1 按升序排序（默认值） xlDescending 2 按降序排序       XlYesNoGuess常量(Header) 值 描述：指定第一行是否包含标题 xlYes 1 不应对整个区域进行排序 xlGuess 0 由 Microsoft Excel 确定是否有标题，如果有，确定标题位于何处。 xlNo 2 应对整个区域进行排序（默认值）       MatchCase常量 描述：是否区分大小写 True 是 False 否（默认值）     XlSortOrientation常量(Orientation) 值 描述：排序方向 xlSortRows 1 按行排序（默认值） xlSortColumns 2 按列排序       XlSortMethod常量(SortMethod) 值 描述：排序类型 xlPinYin 1 按字符的汉语拼音顺序排序（默认值） xlStroke 2 按每个字符的笔划数量排序       XlSortDataOption常量(DataOption) 值 描述：指定如何对 key中的文本进行排序 xlSortNormal 1 分别对数字和文本数据进行排序（默认值） xlSortTextAsNumbers 2 将文本作为数字型数据排序       字符串 Left,Right,Mid,Len函数 语法：Left(string, length) Right(string, length)  Mid(string, start[, length]) Len(string | varname) Dim sr sr = "Excel精英 培训 焊锡培训资料ppt免费下载焊接培训教程 ppt 下载特设培训下载班长管理培训下载培训时间表下载 网" Debug.Print Left(sr, 5)     '截取sr中左起5个字符 Debug.Print Right(sr, 5)     '截取sr中右起5个字符 Debug.Print Mid(sr, 3, 5)     '截取sr中中间第3个字符起5个字符 Debug.Print Left(sr, Len(sr) - 1)     '截取sr中除左边最后一个外的字符 Split,Join函数 语法：Split(expression[, delimiter[, limit[, compare]]]) Join(sourcearray[, delimiter]) Dim sr$, arr, i% sr = "Excel的精的英的培训网" arr = Split(sr, "的") Cells(1, "A") = UBound(arr) For i = 0 To UBound(arr) Cells(i + 2, "A") = arr(i) Next i Cells(UBound(arr) + 3, "A") = Join(arr, "+") Val函数（返回包含于字符串内的数字） 语法：Val(string) Debug.Print Val("    1615 198th Street N.E.")     '返回1615198 3. 工作簿与工作表 工作簿 新建工作簿 Workbooks.Add    '新建工作簿 保存工作簿 语法：expression.Save expression.SaveAs(FileName,FileFormat,Password,WriteResPassword,ReadOnlyRecommended,CreateBackup,AccessMode,ConflictResolution,AddToMru,TextCodepage,TextVisualLayout,Local) ThisWorkbook.Save    '保存工作簿 ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\123.xls" '当前工作簿保存为当前目录下的xls文件，文件名为123 ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\123.xls" '当前工作簿副本另存为当前目录下的xls文件，文件名为123 关闭工作簿 语法：expression.Close(SaveChanges, Filename, RouteWorkbook) ThisWorkbook.Close SaveChanges:=False    '不保存关闭工作簿 工作表 删除工作表 Application.DisplayAlerts = False    Worksheets("Sheet1").Delete Application.DisplayAlerts = True '删除活动工作簿的 Sheet3 工作表，并且不显示确认对话框 4. 导入外部数据 With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\IM.txt", Destination:=Range( _ "A1")) .Name = "IM" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 936 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With 5. 调用对象 字典 说明 字典（dictionary）是一个储存数据的小仓库。共有两列。第一列叫key , 不允许有重复的元素。第二列是item,每一个key对应一个item，本列允许为重复。字典只有两列，如果要处理多列的数据，还需要通过字符串的组合和拆分来实现。字典调用会耗费一定时间，如果是数据量不大，字典的优势就无法体现出来。 如何调用 直接创建法 Dim dic As Object Set dic = CreateObject("Scripting.Dictionary") 引用Microsoft Scripting Runtime Dim D As New Dictionary 属性 正则表达式 说明 正则表达式是处理字符串的外部工具，它可以根据设置的字符串对比 规则 编码规则下载淘宝规则下载天猫规则下载麻将竞赛规则pdf麻将竞赛规则pdf ，进行字符串的对比、替换等操作。正则表达式的作用：1、完成复杂的字符串判断；2、在字符串判断时，可以最大限度的避开循环，从而达到提高运行效率的目的。 如何调用 直接创建法 Dim regex As Object Set regex = CreateObject("VBScript.RegExp") 引用Microsoft VBScript Regular Expressions 5.5 Dim reg As New RegExp 属性 Global属性 Global常量 描述 True 搜索全部字符 False 搜索到第1个即停止（默认值）     Pattern属性：一个字符串，用来定义正则表达式。缺省值为空文本。 IgnoreCase 属性 IgnoreCase常量 描述：区分大小写 True 不区分大小写 False 区分大小写（默认值）     Multiline 属性：字符串是不是使用了多行,如果是多行,$适用于每一行的最后一个 Replace属性：替换搜索到的Pattern字符串 Dim reg As New RegExp    '创建新的RegExp对象 Dim sr sr = "AEA" & Chr(10) & "ABCA"    '设定字符串 With reg .Global = True    '搜索全部内容 .MultiLine = True    '搜索多行 .Pattern = "A$" '.Pattern = "^A" Debug.Print .Replace(sr, "")    '替换搜索到的字符为空 End With