VBA学习笔记培训资料.doc

精品文档 EXCEL之VBA学习笔记 姓名：刘磊 时间：2015年9目录第一章 VBA基础知识3第二章 工作簿以及工作表的操作9第三章：单元格区域操作14第四章：事件程序:36第五章：VBA数组42第一章 VBA基础知识1：代码帮助： F12：代码换行： 下划线+空格+回车3：.常用代码操作excel中的对象 （1）、工作簿（Workbooks） Workbooks（N）第N个工作簿 Workbooks (工作簿名) ActiveWorkbook 活动工作簿 ThisWorkBook 代码所在工作簿 （2）、工作表（Worksheets） Sheets(N) 第N个工作表 Sheets(工作表名) SheetN 第N个工作表 ActiveSheet 活动工作表 worksheets 与 Sheets的区别 （3）、 单元格（cells） Range (单元格地址) Cells(行号,列号) A1单元格简写 Activecell 活动单元格 Selection 当前被选取的区域4：常量与变量 （1.）常量：常量是定义了之后就不做变化了。 常量定义格式：Const 常量名= 常量表达式 （2）.变量:在定义之后还能再次赋值 变量定义格式：Dim 变量 As 变量类型5：数据类型 （1.）VBA中的常见数据类型： 类型 注释 简写 占用内存 Integer 整型 % 2Byte Single 单精度 ! 4Byte Double 双精度 # 8Byte Long 长整型 & 4Byte String 字符型 $ 定长或变长( 变长字符串最多可包含大约 20 亿 ( 231)个字符。 定长字符串可包含 1 到大约 64K ( 216 ) 个字符。) Currency 货币型 8Byte6:if条件语句 1.单行形式1（If.Then） If 条件判断 Then 条件成立结果 注意 在单行形式中，按照 If.Then 判断的结果也可以执行多条语句。 所有语句必须在同一行上并且以冒号分开?例子:Sub test()If 1 10 Then a = a + 1: b = 1 + a: c = 1 + bEnd Sub 2. 单行形式1（If 条件判断 Then 条件成立 Else 条件不成立）例子:Sub test2()If 1 1 Then MsgBox yes Else MsgBox noEnd Sub 3.块形式（If.ThenEnd） If 条件判断 Then 条件成立结果 End If例子:Sub test3()If 11 10 Thena = 1 + ab = 1 + ac = 1 + bEnd IfEnd Sub 4.块形式的If嵌套 If 条件判断 Then 成立时的结果 ElseIf 条件判断 Then 成立时的结果 Else 不成立时的结果 End If例子: Sub 等级判断() If Sheet1.Range(b1) = 90 Then Sheet1.Range(b2) = 优 ElseIf Sheet1.Range(b1) = 80 Then Sheet1.Range(b2) = 良 ElseIf Sheet1.Range(b1) = 70 Then Sheet1.Range(b2) = 中 Else Sheet1.Range(b2) = 差 End If End Sub7：select语句用于判断选择 Select case Case 1 Case 2 . Case else End select8：循环语句 （1）：do loop语句 Do . Loop(2):do while loop语句 Do while (条件成立时候循环) Loop（3）do until loop 语句 Do until (直到条件成立) Loop注：while与until不但可以放在DO后面，也可以放在LOOP后面事实上有时在循环的最后一行进行判断，更具有意义。Do While | Until 表达式执行的一条或多条语句Exit Do执行的一条或多条语句Loop-while：当这个条件为True时就 循环until：直到这个条件为True时就 跳出循环-或者可以使用下面这种语法:Do执行的一条或多条语句Exit Do执行的一条或多条语句Loop While | Until表达式-用DoLoop循环要注意的几点:1. While与Until是放在Do后面还是Loop后面，取决于是先判断再循环，还是先循环再判断。前者则在Do后面，后者则在Loop后面。2. 可以在Do.Loop中的任何位置放置任意个数的 Exit Do 语句，随时跳出 Do.Loop 循环。3. Do.Loop + If.Then + Exit Do 通常结合使用.4. 如果 Exit Do 使用在嵌套的 Do.Loop 语句中，则 Exit Do 会将控制权转移到 Exit Do 所在位置的外层循环。（4）：for each next 语句 Eg: Sub foreachnext循环1()Dim rng As Range, n! (range为单元格对象)For Each rng In Sheet1.Range(a2:a10) 取a2:a10中的每个单元格 If rng = A1 Then rng.Interior.ColorIndex = 3NextEnd SubSub foreachnext循环2()Dim wsh As Worksheet, n As Byte, m As String （worksheet为工作表变量）For Each wsh In Worksheets 取当前工作表集合中的每个成员 n = n + 1 Sheet1.Cells(n, 3) = wsh.NameNextEnd Sub9：exit语句与end语句 （1）： exit是退出当前语句1.Exit Do2.Exit For 3.Exit Function4.Exit Sub （2）：结束一个过程或块 End End Function End If End Select End Sub 注：end 有时候在某些地方的功能和exit for的作用相同。10：跳转语句GoTo line无条件地转移到过程中指定的行。 Gosub return 跳转到某行，而且能够返回。注意太多的 GoTo 语句，会使程序代码不容易阅读及调试。尽可能使用结构化控制语句(Do.Loop、For.Next、If.Then.Else、Select Case)。 For exampleSub gotoreturn()Dim i!For i = 2 To 10 If Sheet1.Range(a & i) 1 / 3 Then GoSub 100Next iExit Sub100: (作为gosub的跳转标示符号) Sheet1.Range(b & i) = 迟到 Return （return语句返回到跳转的地方）End Sub11：对错误语句的处理方法1：On Error Resume Next 当错误的时候继续执行下去方法2：On Error goto 当错误时候去哪儿。12：with语句当对某个对象执行一系列的语句时，不用重复指出对象的名称。For example Sub with嵌套1()Range(a1).Value = Who am i ?Range(a1).Parent.Name = Hello WorldRange(a1).Font.Size = 20Range(a1).Font.Bold = TrueEnd Sub Sub with嵌套2()With Range(a1) .Value = Who am i ? .Parent.Name = Hello World With .Font .Size = 20 .Bold = True End WithEnd WithEnd Sub13:VBA 与公式For example Sub 普通公式()Sheet1.Cells(1, 3) = =a1+b1End SubSub 批量计算()Dim i As IntegerFor i = 1 To 10 Sheet1.Cells(i, 4) = =a & i & +b & iNext iEnd SubSub 数组公式()Range(e1:e10).FormulaArray = =a1:a10+b1:b10 (FormulaArray为数组公式)End SubSub 公式带引号的计算()Cells(12, 1) = =COUNTIF(A1:A10,9) （如果公式当中含有引号，则需要添加双重引号，才能够使公式的输入格式正确）Cells(12, 2) = =sum(INDIRECT(a1:a10)End Sub14:运算符 运算符是代表VBA某种运算功能的符号。 1）赋值运算符 :=2）数学运算符: &(字符连接符)、+(加)、-（减）、Mod（取余）、（整除）、*（乘）、/（除）、-（负号）、（指数） 3）逻辑运算符:Not（非）、And（与）、Or(或)、Xor(异或 相同为0 ，不同为1)、Eqv(相等，相同为1，不同为0)、Imp(隐含)4）关系运算符: = （相同）、（不等）、（大于）、=（不小于）、=（不大于）、Like（判断两个字符串是否相同） ?：代表任何单一字符*：代表零个或多个字符。 charlist ：代表charlist.中的任何单一字符? !charlist ：代表不在 charlist 中的任何单一字符。 第二章 工作簿以及工作表的操作1：VBA 中工作表与工作簿的表示方法1： workbooks(“工作表的文件名”) Workbooks(“工作表的文件名”).parent 返回工作簿对象的父对象2：工作簿引索号表示法workbooks(数字).name 返回工作表的名称3：窗口表示方法 Windows.count 返回当前excel工作簿打开的个数 Windows(N).parent.Name 返回第N个工作簿的名称注：工作簿索引号的表示法与窗口表示法表示的工作簿的顺序相反。2：当前工作簿与活动工作簿当前工作簿：thisworkbook 代码所在工作簿活动工作簿：activeworkbook 已经激活的工作簿 注：当前工作簿可能是已经激活的工作簿，也可能不是已经激活的工作簿。3：工作簿的基本操作workbooks由当前所有在内存中打开的workbook对象组成的集合（1）：.新建工作簿Sub 新建工作簿()Dim wkb As Workbook 声明wkb为工作簿Set wkb = Workbooks.Add 新建工作秒簿wkb.SaveAs c:123.xls 保存为工作簿End Sub（2）.打开工作簿Sub 打开工作簿()Dim wkb As WorkbookSet wkb = Workbooks.Open(c:123.xls)End Sub（3）.关闭工作簿Sub 关闭()Workbooks(123).Close True (默认为自动保存，不提示)End Sub（4）.文件复制与删除Sub 文件复制与删除()FileCopy c:123.txt, c:321.txt （对所有文件类型都起作用）Kill c:321.txtEnd Sub 4：工作薄的应用实例（1） 判断文件是否存在Sub 文件是否存在() a = Dir(c:123.xls) （Dir函数用来取出路径下的目录文件） If a = Then MsgBox 不存在 Else MsgBox 存在 End IfEnd Sub（2） 打开指定目录下的文件 Sub 打开指定目录下的文件()Dim a$, n!, wbs As Workbooka = Dir(c:*.txt)Workbooks.Open c: & aDo a = Dir If a Then Workbooks.Open c: & a Else Exit Sub End IfLoopEnd Sub5：工作簿的表示方法在workbook对象中，有一个SHEETS集合,其成员是worksheet对象或chart对象。worksheets仅指的是工作表，而sheets包含图表，工作表，宏表等等VBA中，经常在工作表之间转换或者对不同工作表中的单元格区域进行操作.通常有下面几种方法:（1）：Sub 直接使用工作表名称法()MsgBox Worksheets(我的工作表).NameMsgBox Sheets(我的图表).NameEnd Sub（2）Sub 索引号表示法()MsgBox Worksheets(1).NameEnd Sub（3）Sub 工作表代码索引号表示法()MsgBox Sheets(1).NameEnd Sub（4）Sub 直接取工作代码法()MsgBox Sheet1.NameEnd Sub（5）Sub 活动工作表()MsgBox ActiveSheet.NameEnd Sub注意：当工作簿包括工作表、宏表、图表等时， 使用索引号引用工作表如Sheets(1)与 WorkSheets(1)引用的可能不是同一个表。Sub worksheetss()MsgBox Worksheets(1).NameMsgBox Sheets(1).NameEnd SubSub sheetss()For i = 1 To Sheets.CountMsgBox Sheets(i).NameNextEnd Sub6：工作表集合的应用（1）Sub 遍历sheets下的所有对象()For Each shs In Sheets k = k + 1 Cells(k, 1) = shs.NameNextEnd Sub（2）Sub 遍历worksheets下的所能对象()For Each shs In Worksheets k = k + 1 Cells(k, 2) = shs.NameNextEnd Sub（3）Sub 工作表存在与否()Dim sn$For Each sht In Sheets sn = sht.Name If sn = 我的工作表 Then MsgBox 存在 Exit Sub End IfNext MsgBox 不存在End Sub（4）Sub 工作表存在与否1()Dim sn$For i = 1 To Sheets.Count （Sheets.Count指sheet里面的数量） a = Sheets(i).Name If Sheets(i).Name = 我的工作表 Then MsgBox 存在 Exit Sub End IfNext MsgBox 不存在End Sub7：工作表的增加与删除 Sheets.Add 方法 表达式.Add(Before, After, Count, Type) XlSheetType 常量之一: xlWorksheet 工作表 xlChart 图表 xlExcel4MacroSheet 宏表 xlExcel4IntlMacroSheet 对话框 默认值为 xlWorksheet?Sub 新建sheets()Sheets.Add （默认在活动工作表之前添加一个工作表）Sheets.Add Sheets(abc) （在工作表名为ABC的工作表之前添加一个工作表）Sheets.Add , Sheets(abc) （在工作表名为ABC的工作表之后添加一个工作表）Sheets.Add after:=Sheets(abc) （与上式等价）Sheets.Add Count:=2 （在活动工作表前添加两个工作表）Sheets.Add , , 2 （与上式等价）Sheets.Add , , , xlChart （添加图表）End SubSub 删除工作表()Sheet10.Delete End Sub8：工作表的删除与添加 如果想批量新建工作表，可以结果循环来制作Sub 新建1到12月份的工作表()Dim j%For j = 12 To 1 Step -1 Sheets.Add.Name = j & 月NextEnd Sub删除工作表Sub 删除sheet()On Error Resume Next （当出现错误时候忽略错误）Application.DisplayAlerts = False （当屏幕有警告提示时候忽略开启）Dim i%For i = 1 To 12 Sheets(i & 月).DeleteNextApplication.DisplayAlerts = True （当屏幕有警告提示时候忽略关闭，否则，下次运行代码时候依旧是忽略关闭状态）End Sub9：工作表的移动与复制（1） 工作表的复制表达式.copy(Before, After)Sub 复制()Sheet1.Copy Sheets(Sheets.Count)End Sub（2） 工作表的移动表达式.Move(Before, After)Sub 移动()Sheet1.Move , Sheet3End Sub10：工作表的选择与激活 Worksheet.Select 方法 不支持隐藏选取Worksheet.Activate 方法 支持隐藏选取 （1）： Sub 快速选择所有工作表()Worksheets.Select （只选择工作表）Sheets.Select （工作表，图表等全部选择）End Sub（2）：Sub 自定义选择()Worksheets(Array(1, 3, 5).SelectEnd Sub11：拆分工作簿实例Sub 拆分到工作簿()Dim wk As Workbook, ss$, k% 声明wk为一个工作簿类型变量Application.DisplayAlerts = FalseFor Each sht In Workbooks(2-11.工作簿综合运用(拆分工作簿).Sheets Set wk = Workbooks.Add wk为一个对象，对象的方法为添加工作表 k = k + 1 Workbooks(1).Sheets(k).Copy Workbooks(2).Sheets(1) ss = ThisWorkbook.Path & & sht.Name & .xlsx wk.SaveAs ss wk.CloseNextApplication.DisplayAlerts = TrueMsgBox 拆分工作簿完成！End Sub 第三章：单元格区域操作1：range对象单元格对象在VBA中一个非常基础，同时也很重要的。它的表达方式也是非常的多样化。Range 对象代表某一单元格、某一行、某一列、某一选定区域（该区域可包含一个或若干连续单元格区域），或者某一三维区域。Range (文本型装单元格地址)range的常见写法Sub rng()Range(a1).Select 单元格Range(a:a).Select 列Range(1:3).Select 行Range(a1:b10).Select 相邻区域Range(a1:d7,c4:e8).Select 不相个邻区域Range(a1:d7 c4:e8).Select 相交的区域End Sub2：range的其他写法Range(a1:b10).Select 一般写法Range(a1, b10).Select 变化写法1Range(Range(a1), Range(b10).Select 变化写法2 （方便以后可以使用变量替换）Range(a1) = 123 （给单元格赋值）注意：1.如果在range前没有指定工作表，则默认为活动工作表2.如果对象不是活动工作表（如活动图表），则会出现错误Sub 单元格对象例子()Debug.Print Range(a:a).Count 计数工作表最大的行数（Debug.Print意思是在活动窗口中显示出来）Debug.Print Range(1:1).Count 计算工作表最大的列数Debug.Print Application.CountA(Range(a:a) 计算工作表已使用的行数Debug.Print Application.CountA(Range(1:1) 计算工作表已使用的列数End Sub3：range变量与引用（1）：range的变化写法1):range(地址区域).range(地址区域)Sub 序号表示法()Range(b2:d4).Range(b2).Select 相对引用的写法参照前一个range的左上单元格End Sub2): 2.range地址区域中支持变量Sub range的变量支持()Dim a%a = 3Range(a & a).SelectRange(c3:e5)(2).SelectEnd Sub3):动态引用实例Sub 实例1动态选单元格或区域()Dim i%i = Application.CountA(Range(c:c) 找到c列中已使用的最后一个单元格位置Range(c & i).Select 选择C列最后一格Range(a1, c & i).Select 选择A1到C列的最后一格（方法一）Range(a1:c & i).Select 选择A1到C列的最后一格（方法二）小结：动态单元格区域的定位，可以应用到单据的保存等实际工作中End Sub4：Range引用与索引range区域中的每个单元格，我们也可以用索引号表示出来写法：range(单元格区域)(行号，列号)Sub 索引号取出range的单元格()Range(a1:c4)(4).Select 引用顺序是：从左向右，从上到下选取Range(b2:c4)(3).Select 以前一个单元格区域为照Range(a1:c4)(4.5).Select 当有小数时，则取整注意：如果索引号出现小数，则按照“四舍六入五单双”的“银行家舍入法”End SubSub 行列号定位()Range(a1:c4)(3, 2).Select 利用行号与列号定位Range(a1:c4)(1.5, 2.5).Select 行列号也可以使用小数5：cells单元格的引用cells单元格引用法写法：cells(行号,列号)Sub cells基本写法()Cells(3, 4).Select 行列号均为数字Cells(2, c).Select 行为数字，列为列标字母Cells.Select 全选End Subcells可以像range一样可以参照前面的单元格位置Sub 参照写法()Range(b3:f11).Cells(2, 2).SelectRange(b3:f11).Cells(6).Select 从左到右，从上到下Range(b3:f11)(6).Select 与上一句相等End Sub注意：1.cells中的数字一样支持正数，负数，0值，小数(四舍六入五单双)2.cells不能像range一样可以引用一个区域，只能引用一个单元格6：单元格简写 除了前面讲的rangecells单元格区域的表示方法还，还是一种简单的写法写法: 单元格地址 注意：中括号中的单元格地址并不需要双引号()Sub 单元格简写()a3.Select 单元格引用b2:c6.Select 单元格区域引用a3,b2:c6,b8:d12.Select 多区域引用a:a.Select 整列引用1:1.Select 整行引用End Sub单元格简写的也支持引用子集Sub 子集引用()b2:c6.Item(3).SelectRange(b2:c6)(3).Selectb2:c6.Cells(4).SelectEnd SubSub 动态区域的引用()a = Application.CountA(a:a)b = Application.CountA(1:1)Range(Range(a1), Range(Chr(64 + b) & a).Select 利用chr函数，让字母形式的列号也支持变量End SubSub chr函数字符循环()For i = 1 To 65535Cells(i, 1) = iCells(i, 2) = Chr(i)NextEnd Sub7：三种单元格引用小结 功能RangeCells单元格地址引用对象单元格，区域，行，列单元格单元格，区域，行，列变量支持支持支持不支持书写难易难难易Range(a1:c & i).Select 引用单元格是区域且有变量Cells(i, c).Select 引用的是单个单元格且有变量a1:19.Select 引用的是区域或单元格且无变量8：行列的引用行列引用Sub 列引用()Columns(1).SelectColumns(b).Select （b列）Columns(c:e).Select （c到e列）End SubSub 行引用()Rows(1).SelectRows(2).SelectRows(3:4).Select （3到4行）End SubSub range行列表式法()Range(1:1).Select （第一行）Range(2:4).Select （2到4行）Range(a:a).Select （a列）Range(b:d).Select （B到D列）End SubSub 简写法()a:a.Selectb:d.Select1:1.Select2:4.SelectEnd SubSub 全选()Rows.Select 选择所有行Columns.Select 选择所有列Cells.Select 选择所单元格i = Rows.Countj = Columns.Countk = Cells.CountEnd SubSub 动态引用使用区域()a = Application.CountA(Columns(1) （返回第一列当中使用的（非空）单元格数目）b = Application.CountA(Rows(1) （返回第一行中使用的（非空）单元格数目）Range(a1, Cells(a, b).Select （动态引用单元格）End Sub9: row与column属性Range.Row 属性返回区域中第一个子区域的第一行的行号Range.Column 属性返回指定区域中第一块中的第一列的列号Sub test()i = Range(a3:b9).Range(a5).Row （返回A3到B9区域的第一行第五列所在单元格位置的真实行号）j = Range(a3:b9).Rowi = Range(b3:d9).Range(a5).Columnj = Range(b3:d9).ColumnEnd Sub 实例： Sub row应用()For Each rw In Rows(1:13) If rw.Row Mod 2 = 0 Then rw.RowHeight = 5 （将偶数行的行高设置为5，其中mod为求余函数） End If Next rwEnd Sub10：单元格的地址与值单元格的值表示方法Sub 单元格值表示()a = a1.Value 实际是什么，就是什么b = a1.Text 看到是什么，就是什么c = a1