ExcelVBA参考大全ver0[1].0读书笔记.doc

目 录第一章：Excel VBA基础41录制宏：42用户自定义函数43 Excel对象模型4集合5属性5方法5事件54 VBA语言5基本的输入输出5调用函数和子过程5括号和参数列表6变量声明6变量的作用域和生存期7变量类型7对象变量7做出判断7循环9数组9运行时错误处理10第二章：Application对象101全局：102 Active属性：103 显示警告：104 屏幕刷新：105 Application的Evaluate方法:106 InputBox:117 状态栏：118 SendKeys：119 OnTime：1210 OnKey：1211 工作表函数：1212 Caller：12第三章：工作薄和工作表121 Workbooks集合：12添加Workbook对象13从路径中获取文件名13在相同目录中的文件13覆盖现有的工作薄13保存改变142 Sheets集合:14工作表（Worksheet）14工作表的复制和移动14组合工作表143 Windows对象：15第四章：使用单元格区域151 Range对象的Activate方法和Select方法 ：152 Range属性：15活动工作表中的单元格区域16非活动工作表中的单元格区域16Range对象的Range属性16Cells属性17Range对象的Item属性17在Range中使用Cells17关于Range对象的Cells属性18单个参数的单元格区域引用183 Offset 属性：184 Resize属性：185 SpecialCells方法：18确定最后一个单元格19删除数字196 CurrentRegion属性197 End属性：198 单元格区域求和：199 Columns属性和Rows属性：1910 Count属性1911 Areas属性2012 Areas集合2113 Union方法和Intersect方法：2114 空单元格：21第五章：使用名称211 Name对象212 Names属性21Application.Names 属性22Workbook.Names属性22Worksheet.Names属性223 Name对象的Name属性：234 命名单元格区域23创建全局名称23创建局部名称：235 在名称中存储值236 存储数组247 隐藏名字：248 处理命名的单元格区域：249 搜索名称：24第六章：数据列表251 结构化数据：252 排序单元格区域253 创建表254 排序表255 自动筛选25第七章：数据透视表25第八章：图表25第九章：事件过程25VBA是一种面向对象的程序设计语言，与Visual Basic程序设计语言在结构化和处理对象方面相同。VBA（Visual Basic for Application）。对象模型是可以在应用程序中找到的所有对象的层次结构。例如，Excel对象模型部分，有一个Application对象，Application对象包含Workbook对象，而Workbook对象包含Worksheet对象，Worksheet对象又包含Range对象。模块事实上只是一个带有一些特定特性的字处理文档，可用于帮助编写和测试代码。第一章：Excel VBA基础1录制宏：通过宏录制器可以生成子过程。在VBA中，宏被称为过程。有两种类型的过程：子过程和函数过程。2用户自定义函数Function Fahrenheit( Centigrade )Fahrenheit = Centigrade * 9 / 5 + 32End Funtion3 Excel对象模型集合Excel自身是一个对象，称为Application对象。在Excel Application对象里，有一个Workbooks集合，包含所有当前打开的Workbook对象。每个Workbook对象有一个Worksheets集合，包含在该工作薄里的Worksheet对象。属性属性是对象的自然特征，可以被度量或量化。例如：每个人都有一个身高属性 一个年龄属性、一个性别属性、一个名字属性等。方法方法是对象或者在对象中可以执行的动作。事件对象可以响应事件。如果希望某个对象响应事件，需要在该对象相应的事件过程里输入VBA代码。4 VBA语言 基本的输入输出MsgBox输出：MsgBox( prompt , buttons , title , helpfile, context )通过位置指定参数： MsgBox “打印吗？”, , “警告！”通过名称指定参数： MsgBox Title:=”警告！”, Prompt:=”打印吗？”, Buttons:=36指定参数时使用常量：MsgBox Prompt:=”打印吗？”, Title:=”警告！”, Buttons:=vbYesNo + vbQuestion返回值：Answer = MsgBox(Prompt:=”打印吗？”, Buttons:=vbYesNo + vbQuestion)注意：如果希望获取函数的返回值，需要在括号里放置所有的参数。如果无需使用返回值，则不应该使用括号。此规则也应用于对象的方法。InputBox输入：UserName = InputBox( Prompt:= “请输入您的名字：” ) 调用函数和子过程Option Explicit'Start 函数、子过程调用Sub Master() Dim SalesData As String SalesData = GetInput(Message:="输入销售数据") If SalesData = False Then Exit Sub PostInput InputData:=SalesData, Target:="B3"End SubFunction GetInput(Message As String) As String Dim Data As String Data = InputBox(Message) If Data = "" Then GetInput = False Else GetInput = DataEnd FunctionSub PostInput(InputData As String, Target As String) Range(Target).Value = InputDataEnd Sub'End 函数、子过程调用 括号和参数列表不使用Call语句：A 仅当调用函数过程并使用该函数过程的返回值时，将参数放置在括号内；B 当调用函数过程但不使用该函数过程的返回值时，不需要在参数周围放置括号C 当调用子过程时，不在参数周围放置括号关于括号，重要而细微的区别A MsgBox (“插入磁盘”) MsgBox与（“插入磁盘”）之间有一个空格Response = MsgBox(“插入磁盘”) MsgBox与（“插入磁盘”）之间无空格注意：在MsgBox与（“插入磁盘”）之间插入一个空格额外的空格表明括号里面是参数而非参数列表。使用Call语句：A如果使用Call语句，则必须在传递给被调用过程的参数两边放置括号 变量声明注意：Option Explicit仅应用于其出现处的模块。需要强制变量声明的每个模块必须在其声明部分重复该语句 变量的作用域和生存期变量的作用域定义了哪些过程可以使用该变量。（在VBA中，宏被称为过程。有两种类型的过程：子过程和函数过程。）变量的生存期定义了变量保存所赋的值多长时间。 变量类型声明变量类型Dim SalesData As Double, Index As Integer, StartDate As Date声明函数和参数类型Function IsHoliday(WhichDay As Date) As BooleanSub Marine(CrewSize As Integer, FuelCapacity As Double)常量Const Version As String = “Release 3.9a” 对象变量创建对象变量引用对象A Set语句用于将一个对象引用赋值给一个对象变量。'Start对象变量Sub ObjectVariable() Dim rng As Range Set rng = ThisWorkbook.Worksheets("Sheet1").Range("C10") rng.Value = InputBox("输入一月的销售量") rng.Offset(-1, 0).Value = "一月销售量"End Sub'End对象变量 做出判断If语句If语句提供三种形式：IIf函数、单行的If语句以及If结构。AIIf函数Function dTax(dProfitBeforeTax As Double) As Double dTax = IIf(dProfitBefore > 0, 0.3 * dProfitBeforeTax, 0)End FunctionB单行的If语句Function dTax2(dProfitBeforTax As Double) As Double If dProfitBeforTax > 0 Then dTax2 = 0.3 * dProfitBeforeTax Else dTax2 = 0End FunctionCIf结构Function dTax3(dProfitBeforTax As Double) As Double If dProfitBeforTax > 0 Then dTax3 = 0.3 * dProfitBeforeTax Else dTax3 = 0 End IfEnd FunctionSelect Case 语句Function vPrice1(sProduct As stirng) As Variant Select Case sProduct Case "苹果" vPrice1 = 12.5 Case "桔子" vPrice1 = 15 Case "梨子" vPrice1 = 18 Case Else vPrice1 = CVErr(xlErrNA) End SelectEnd FunctionFunction vPrice2(sProduct As stirng) As Variant Select Case sProduct Case "苹果": vPrice2 = 12.5 Case "桔子": vPrice2 = 15 Case "梨子": vPrice2 = 18 Case Else: vPrice2 = CVErr(xlErrNA) End Select Function vFare(iAge As Integer) As Variant Select Case iAge Case 0 To 3, Is > 65 vFare = 0 Case 4 To 15 vFare = 10 Case 16 To 65 vFare = 20 Case Else vFare = CVErr(xlErrNA) End SelectEnd Function 循环DoLoop例子省略ForNext例子省略数组数组A 数组是可以包含一个以上数据项的VBA变量，通过在名称后面包含括号声明数组。在括号里放置整数，定义数组中元素的个数。B 数组的定义Dim avData(2) As Integer 一共有avData(0),avData(1),avData(2)三个元素Dim avData(1 To 2) As Integer 一共有avData(1),avData(2)两个元素C 数组元素赋值avData(0) = 1avData(1)=10avData(2)=100Dim avData As VariantavData = Array("North", "South", "East", "West")D 数组函数LBound，UBoundSub Array1() Dim aiData(10) As Integer Dim sMessage As String, i As Integer For i = LBound(aiData) To UBound(aiData) aiData(i) = i Next i sMessage = "ÏÂÏÞ=" & LBound(aiData) & vbCr sMessage = sMessage & "ÉÏÏÞ=" & UBound(aiData) & vbCr sMessage = sMessage & "ÔªËØÊý=" & WorksheetFunction.Count(aiData) & vbCr sMessage = sMessage & "ÔªËغÍ=" & WorksheetFunction.Sum(aiData) MsgBox sMessageEnd Sub多维数组A 多维数组定义Dim avData(10, 20) As VariantDim avData(1 To 10, 1 To 20)B 多维数组函数Dim avData As VariantLBound(aiData,1) To UBound(aiData,2)动态数组A 通过忽略数组维数声明一个动态数组Dim asData() As StringB 可以在运行时使用ReDim语句声明所需大小，因而可以使用变量定义索引值的范围：ReDim avData(iRows, iColumns)ReDim avData(iminRow To iMaxRow, iminCol To imaxCol)运行时错误处理捕获错误On Error Goto LineLabel获取错误信息Err对象可以获取到错误信息。Err对象的Number属性返回错误号，Description属性返回相关的报错信息忽略错误Resume语句表现为三种形式：A Resume执行导致错误的语句B Resume Next返回导致错误语句的下一条语句并执行该语句，从而跳过了有问题的语句；C Resume LineLabel跳转到代码中指定的行标签处，选择从何处恢复执行恢复错误处理On Error GoTo 0第二章：Application对象1全局：Application对象是全局对象，它的许多属性和方法也是全局的成员。2 Active属性：下面的Application属性是全局的属性，允许引用活动的对象：ActiveCell、ActiveChart、ActivePrinter、ActiveSheet、ActiveWindow、ActiveWorkbook、Selection。3 显示警告：设置DisplayAlerts属性为False，可以屏蔽掉大多数警告。当屏蔽一个警告对话框时，自动执行该对话框中默认的按钮相关联的操作。例如：Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True4 屏幕刷新：Application.ScreenUpdating = False5 Application的Evaluate方法: 将一个 Microsoft Excel 名称转换为一个对象或者一个值。语法表达式.Evaluate(Name)表达式   一个代表 Application 对象的变量。参数名称必选/可选数据类型描述Name必选Variant使用 Microsoft Excel 命名约定的对象名称。返回值Variant说明该方法可使用下列 Microsoft Excel 名称类型：· A1 格式引用。可以通过 A1 格式表示法引用单个单元格。所有引用均视为绝对引用。 · 区域。在引用中可以使用区域、交集和联合运算符（分别为冒号、空格和逗号）。 · 定义的名称。可用宏语言指定任何名称。 · 外部引用。可以使用 ! 运算符引用另一工作簿中的单元格或已定义的名称，例如，Evaluate("BOOK1.XLSSheet1!A1")。 · 图表对象。可以指定任何图表对象名称（如“Legend”、“Plot Area”或“Series 1”），以访问该对象的属性和方法。例如，Charts("Chart1").Evaluate("Legend").Font.Name 返回图例中所用字体的名称。6 InputBox:Set rng = Application.InputBox(prompt:="请输入单元格区域", Type:=8)7 状态栏：允许为StatusBar属性赋一个文本字符串，并将该字符串显示在Excel屏幕底部状态栏的左侧。Sub ShowMessage() Dim lCounter As Long For lCounter = 0 To 100000000 If lCounter Mod 1000000 = 0 Then Application.StatusBar = "Processing Record " & lCounter End If Next lCounter Application.StatusBar = FalseEnd Sub注意：在过程的结尾，必须将StatusBar属性值设置为False，返回状态栏默认操作。否则，最后的消息将会一直停留在状态栏上。8 SendKeys：SendKeys允许发送按键到当前活动窗口，用来控制不支持任何其他交互形式的应用程序，例如DDE（Dynamic Data Exchange）或 OLE。9 OnTime：使用OnTime方法安排在将来某个时刻运行宏，需要指定该宏运行的日期和时间以及宏的名称。如果使用Application对象的Wait方法暂停某宏，所有的Excel行为，包括手工交互操作，都将挂起。使用OnTime的优势在于，当等待运行预设的宏时，允许返回正常的Excel交互操作，包括运行其他的宏。10 OnKey：使用OnKey方法将一个宏过程赋给单个按键或任意组合键。也可以使用该方法禁用组合键。Sub AssignDown() Application.OnKey "Down", "DownTen"End SubSub DownTen() ActiveCell.Offset(10, 0).SelectEnd Sub11 工作表函数：在Excel中可以使用两种内置函数，一组函数是VBA语言的组成部分，另一组函数是Excel工作表函数的子集（WorksheetFunction 对象用作可从 Visual Basic 中调用的 Microsoft Excel 工作表函数的容器。）。一般情况下，如果一个VBA函数与一个Excel函数有着相同的用途，那么该Excel函数就不能直接用于VBA宏（但可以使用Evaluate方法访问任何Excel函数）。12 Caller：Application对象的Caller属性返回调用或执行宏过程的对象的引用，Select Case TypeName(Application.Caller) Case "Range" v = Application.Caller.Address Case "String" v = Application.Caller Case "Error" v = "Error" Case Else v = "unknown"End SelectMsgBox "caller = " & v第三章：工作薄和工作表1 Workbooks集合：Workbooks集合由当前所有在内存里打开的Workbook对象组成。添加Workbook对象向Workbooks集合中添加对象的方式有很多种，可以基于Workbook对象的默认属性创建新的空工作薄，或基于模板文件创建新工作薄，还可以打开一个现有的工作薄文件。A 基于默认的工作薄创建新的空工作薄，使用Workbooks集合的Add方法B Add方法允许为新工作薄指定模板C 用Open方法可以向Workbooks集合中添加现有的工作薄文件。从路径中获取文件名当在VBA中处理工作薄时，经常需要指定目录路径和文件名称。某些任务只需要知道路径，例如已设置了默认的目录。某些任务只需要知道文件名称，例如希望激活某个已打开的工作薄。而另一些任务中，既需要路径也需要文件名，例如希望打开已存在但不在活动目录中的工作薄文件。Set wkb = Workbooks.Open(FileName:=” D:ProjectVBAStudyCapture3test.xlsx”)MsgBox wkb.Name 返回test.xlsxMsgBox wkb.Path 返回 D:ProjectVBAStudyCapture3MsgBox wkb.FullName 返回 D:ProjectVBAStudyCapture3test.xlsx在相同目录中的文件ThisWorkbook是对包含该代码的工作薄的引用。无论该工作薄位于哪儿，ThisWorkbook的Path属性都将提供必需的路径以定位相关的文件。覆盖现有的工作薄Function bFileIsExists(sFile As String) As Boolean If Dir(sFile) <> "" Then bFileIsExists = TrueEnd FunctionSub CreateNextFileName() Dim wkb As Workbook Dim i As Integer Dim sFName As String Set wkb = Workbooks.Add(Template:="D:ProjectVBAStudyCapture3Test.xlsx") i = 0 Do i = i + 1 sFName = "D:ProjectVBAStudyCapture3Test" & i & ".xlsx" Loop While bFileIsExists(sFName) Application.DisplayAlerts = False wkb.SaveAs Filename:=sFName Application.DisplayAlerts = TrueEnd Sub保存改变Sub CloseWorkbook() Dim wkb As Workbook Set wkb = Workbooks.Open(Filename:="D:ProjectVBAStudyCapture3Test.xlsx") Range("A1").Value = Format(Date, "ddd mmm dd, yyyy") Range("A1").EntireColumn.AutoFit wkb.Close SaveChanges:=TrueEnd Sub2 Sheets集合:在Workbook对象里，有一个Sheets集合，其成员是Worksheet对象或Chart对象。Worksheet对象与Chart对象也分别属于他们自己的集合Worksheets集合 和Charts集合。Charts集合仅包括图表工作表。嵌入在工作表中的图表不是Charts集合的成员，而是包含在ChartObject对象中，是工作表的ChartObjects集合中的成员。工作表（Worksheet）可通过在Sheets集合和Worksheets集合里的名称或索引值引用工作表。如果已知要处理的工作表的名称，可以通过名称在Worksheets集合里指定工作表。注意：Worksheet对象的Index属性返回的是Sheets集合中的索引值，不是Worksheets集合中的索引值。（Sheets集合中的索引值和Worksheets集合中的索引值是不同的。因为，Sheets集合中也包括Chart对象即图表工作表）工作表的复制和移动Worksheet对象的Copy方法和Move方法允许每次复制或移动一个或者多个工作表。他们都提供了两个可选参数，允许指定该操作的目的位置，即某指定的工作表之前或之后。如果不使用任何参数，那么将复制或者移动工作表到一个新工作薄中。注意：Copy方法和Move方法不返回任何值或引用组合工作表在VBA中，可以使用Worksheets集合的Select方法并联合Array函数来组合工作表。此外，也可以使用Worksheet对象的Select方法创建工作表组。按正常的方式选择第1个工作表，通过使用Select方法并将其Replace参数设置为False将其他工作表添加到组中。3 Windows对象：Window 对象代表窗口。 说明许多工作表特征（如滚动条和标尺）实际上是窗口的属性。Window 对象是 Windows 集合的成员。Application 对象的 Windows 集合包含应用程序中的所有窗口，而 Workbook 对象的 Windows 集合只包含指定工作簿中的窗口。示例使用 Windows(index)（其中 index 是窗口名称或索引号）可返回一个 Window 对象。下例最大化活动窗口。Visual Basic for ApplicationsWindows(1).WindowState = xlMaximized注意，活动窗口总是 Windows(1)。第四章：使用单元格区域Range对象可以是单个单元格、一个矩形的单元格区域或者许多矩形单元格区域（不相邻的区域）的联合。Range对象包含在Worksheet对象里。Excel对象模型不支持跨多个工作表的三维Range对象，在单个Range对象里每个单元格必须位于同一个工作表中。1 Range对象的Activate方法和Select方法 ：使用Activate方法激活一个单元格（尽管使用Activate方法时允许指定多个单元格，但只能激活一个单元格），使用Select方法选择单元格区域。2 Range属性： 返回一个 Range 对象，它代表一个单元格或单元格区域。 语法表达式.Range(Cell1, Cell2)表达式  一个代表 Application 对象的变量 、 一个代表 Worksheet 对象的变量 或 一个代表 Range 对象的变量。参数名称必选/可选数据类型描述Cell1必选Variant区域名称。必须为采用宏语言的 A1 样式引用。可包括区域操作符（冒号）、相交区域操作符（空格）或合并区域操作符（逗号）。也可包括货币符号，但它们会被忽略掉。您可以在区域中任一部分使用局部定义名称。如果使用名称，则假定该名称使用的是宏语言。Cell2可选Variant区域左上角和右下角的单元格。可以是一个包含单个单元格、整列或整行的 Range 对象，或者也可以是一个用宏语言为单个单元格命名的字符串。说明如果在没有对象识别符时使用，则该属性是 ActiveSheet.Range 的快捷方式（它返回活动表的一个区域，如果活动表不是一张工作表，则该属性无效）。当应用于 Range 对象时，该属性与 Range 对象相关。例如，如果选中单元格 C3，那么 Selection.Range("B1") 返回单元格 D3，因为它同 Selection 属性返回的 Range 对象相关。此外，代码 ActiveSheet.Range("B1") 总是返回单元格 B1。活动工作表中的单元格区域可以使用Application对象的Range属性引用活动工作表中的Range对象。因为Application对象的Range属性是全局的成员，所以可以省略Application对象的引用。Application.Range(“B2”)Range(“B2”)非活动工作表中的单元格区域如果希望引用非活动工作表上的单元格区域，可简单地使用所需Worksheet对象的Range属性。Worksheets(“Sheet2”).Range(“C10”)如果包含该工作表和单元格区域的工作薄处于非活动状态，则需要进一步指定对Range对象的引用：Workbooks(“test.xlsx”).Worksheets(“Sheet1”).Range(“C10”)注意：当把Range属性作为另一个Range属性的参数时，还需要使用完整的限定属性：Sub test2() Sheets("Sheet1").Range( _ Sheets("Sheet1").Range("A1"), _ Sheets("Sheet1").Range("A10") _ ).Interior.ColorIndex = 3End SubRange对象的Range属性Range(“C3”).Range(“B1”) 该代码引用单元格D3Cells属性使用Application对象、Worksheet对象或Range对象的Cells属性，将引用一个包含Worksheet对象或Range对象里所有单元格的Range对象。Range对象的Item属性Range.Item 属性返回一个 Range 对象，它代表对指定区域某一偏移量处的区域。 语法表达式.Item(RowIndex, ColumnIndex)表达式   一个代表 Range 对象的变量。参数名称必选/可选数据类型描述RowIndex必选Variant要访问的单元格的索引号，顺序为从左到右，然后往下。 Range.Item(1)返回区域左上角单元格； Range.Item(2)返回紧靠左上角单元格右侧的单元格。ColumnIndex可选Variant指明要访问的单元格所在列的列号的数字或字符串，1 或 “A”表示区域中的第一列。说明语法 1 使用行号和列号或列标作为索引参数。关于此语法的详细信息，请参阅 Range 对象。RowIndex 和 ColumnIndex 参数为相对偏移量。换句话说，如果 RowIndex 指定为 1，则返回区域内第一行中的单元格，而非工作表的第一行。例如，如果选定区域为单元格 C3，则 Selection.Cells(2, 2) 返回单元格 D4（使用 Item 属性可在原始区域之外进行索引）。注意：因为Item属性是Range对象的默认属性，所以下面的代码是等效的：Cells.Item(2,2) Cells.Item(2,”B”) Cells(2,2) Cells(2,”B”)在Range中使用CellsRange(Cells(1,1), Cells(10,5) 该代码引用活动工作表的A1:E10With Sheets(“Sheet1”).Range( .Cells(1,1), . Cells(10,5) ).Font.Bold = TrueEnd With 注意确保完整的限定Cells属性关于Range对象的Cells属性Range对象的Cells属性提供了一种比较好的方法，根据某单元格相对于起始单元格的位置或在某单元格块中的相对行列位置来实现引用。Range(“D10:G20”).Cells(2,3) 该代码引用单元格F11事实上，不需要限制所引用的单元格在Range对象的范围内，也可以引用原单元格区域之外的单元格，这就意味着实际上只需要使用该Range对象左上角的单元格作为起始点。Range(“D10”).Cells(2,3) 该代码引用单元格F11注意：Range(“D10”).Cells(2,3) Range(“D10”).Item(2,3) Range(“D10”)(2,3) 等价单个参数的单元格区域引用引用单元格的简写方法既可接受两个参数（ 如：Range(“D10”).Item(2,3) ），也可以接受单个参数。在多于一行的单元格区域里，如果索引值超过了单元格区域的总列数，引用将在单元格区域指定的列内，自动向下换行到合适的位置。Range(“D10:E11”)(2) 该代码引用单元格E10Range(“D10:E11”)(3) 该代码引用单元格D11Range(“D10:E11”)(4) 该代码引用单元格E11