VBA学习重点笔记专业资料.doc

EXCEL之VBA学习笔记 姓名：刘磊 时间：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 = a1End Sub注意：一种单元格可以省略value，多单元格区域不能省略Sub 多区域赋值()Range(e1:e4) = Range(d1:d4).ValueEnd Sub单元格地址与引用Sub 地址与引用()Set rng = b2:f2a9 = rng.Address(1，1) 绝对引用b9