VBA学习笔记.docx

1、VBA学习笔记 EXCEL之VBA学习笔记 姓名：刘磊 时间：2015年9第一章 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）、

2、 单元格（cells） Range (单元格地址) Cells(行号,列号) A1单元格简写 Activecell 活动单元格 Selection 当前被选取的区域4：常量与变量 （1.）常量：常量是定义了之后就不做变化了。 常量定义格式：Const 常量名= 常量表达式 （2）.变量:在定义之后还能再次赋值 变量定义格式：Dim 变量 As 变量类型5：数据类型 （1.）VBA中的常见数据类型： 类型 注释 简写 占用内存 Integer 整型 % 2Byte Single 单精度 ! 4Byte Double 双精度 # 8Byte Long 长整型 & 4Byte String 字符型

3、$ 定长或变长( 变长字符串最多可包含大约 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

4、 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)

5、 = 优 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

6、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

7、后面，取决于是先判断再循环，还是先循环再判断。前者则在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

8、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.Ex

9、it 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(

10、)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()Rang

11、e(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)

12、 = =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(

13、a1:a10)End Sub14:运算符 运算符是代表VBA某种运算功能的符号。 1）赋值运算符 :=2）数学运算符: &(字符连接符)、+(加)、-（减）、Mod（取余）、（整除）、*（乘）、/（除）、-（负号）、（指数） 3）逻辑运算符:Not（非）、And（与）、Or(或)、Xor(异或 相同为0 ，不同为1)、Eqv(相等，相同为1，不同为0)、Imp(隐含)4）关系运算符: = （相同）、（不等）、（大于）、=（不小于）、=（不大于）、Like（判断两个字符串是否相同） ?：代表任何单一字符*：代表零个或多个字符。 charlist ：代表charlist.中的任何单一字符? !ch

14、arlist ：代表不在 charlist 中的任何单一字符。 第二章 工作簿以及工作表的操作1：VBA 中工作表与工作簿的表示方法1： workbooks(“工作表的文件名”) Workbooks(“工作表的文件名”).parent 返回工作簿对象的父对象2：工作簿引索号表示法workbooks(数字).name 返回工作表的名称3：窗口表示方法 Windows.count 返回当前excel工作簿打开的个数 Windows(N).parent.Name 返回第N个工作簿的名称注：工作簿索引号的表示法与窗口表示法表示的工作簿的顺序相反。2：当前工作簿与活动工作簿当前工作簿：thisworkb

15、ook 代码所在工作簿活动工作簿：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:

16、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） 打开指定

17、目录下的文件 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中，经常在工作表之间转换或者对不同工作表中的单元格区域进行操作.通常有下面几种

18、方法:（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注意：当工作簿包括工作表、宏表、图表等时， 使用索引号引用工作表如She

19、ets(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下的所能对象()F

20、or 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(

21、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 Shee

22、ts(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月份的工作表()D

23、im 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：工作表的移动与复制（

24、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）：

25、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

26、(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

27、 单元格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前没有指定工作

28、表，则默认为活动工作表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(