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

EXCEL之VBA
学习笔记

姓名：刘磊
时间：9

目录
第一章 VBA基本知识 3
第二章 工作簿以及工作表操作 9
第三章：单元格区域操作 14
第四章：事件程序: 36
第五章：VBA数组 42

第一章 VBA基本知识
1：代码协助： F1
2：代码换行： 下划线+空格+回车
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 亿 ( 2^31)个字符。 定长字符串可包括 1 到大概 64K ( 2^16 ) 个字符。)
Currency 货币型 @ 8Byte
6:if条件语句

1.单行形式1（If...Then）
If 条件判断 Then 条件成立成果
注意 在单行形式中，按照 If...Then 判断成果也可以执行多条语句。
所有语句必要在同一行上并且以冒号分开?
例子:
Sub test()
If 1 > 10 Then a = a + 1：b = 1 + a：c = 1 + b
End Sub
2. 单行形式1（If 条件判断 Then 条件成立 Else 条件不成立）
例子:
Sub test2()
If 1 > 1 Then MsgBox "yes" Else MsgBox "no"
End Sub

3.块形式（If...Then…End）
If 条件判断 Then
条件成立成果
End If
例子:
Sub test3()
If 11 > 10 Then
a = 1 + a
b = 1 + a
c = 1 + b
End If
End 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 Sub
7：select语句用于判断选取
Select case
Case 1
Case 2
…..
Case else
End select
8：循环语句
（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}表达式]
---------------------------------------------------------------------------------
用Do…Loop循环要注意几点:
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 = 3
Next
End Sub

Sub 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.Name
Next
End Sub

9：exit语句与end语句
（1）： exit是退出当前语句
1.Exit Do
2.Exit For
3.Exit Function
4.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 example
Sub gotoreturn()
Dim i!
For i = 2 To 10
If Sheet1.Range("a" & i) > 1 / 3 Then GoSub 100
Next i
Exit Sub
100： (作为gosub跳转标示符号)
Sheet1.Range("b" & i) = "迟到"
Return （return语句返回到跳转地方）
End Sub
11：对错误语句解决
办法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 World"
Range("a1").Font.Size = 20
Range("a1").Font.Bold = True
End Sub
Sub with嵌套2()
With Range("a1")
.Value = "Who am i ?"
.Parent.Name = "Hello World"
With .Font
.Size = 20
.Bold = True
End With
End With
End Sub
13:VBA 与公式
For example
Sub 普通公式()
Sheet1.Cells(1，3) = "=a1+b1"
End Sub

Sub 批量计算()
Dim i As Integer
For i = 1 To 10
Sheet1.Cells(i，4) = "=a" & i & "+b" & i
Next i
End Sub

Sub 数组公式()
Range("e1:e10").FormulaArray = "=a1:a10+b1:b10" (FormulaArray为数组公式)
End Sub

Sub 公式带引号计算()
Cells(12，1) = "=COUNTIF(A1:A10,"">9"")" （如果公式当中具有引号，则需要添加双重引号，才可以使公式输入格式对的）
Cells(12，2) = "=sum(INDIRECT(""a1:a10""))"
End Sub
14:运算符
运算符是代表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 Workbook
Set 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.txt"
End Sub
4：工作薄应用实例
（1） 判断文献与否存在
Sub 文献与否存在()
a = Dir("c:\123.xls") （Dir函数用来取出途径下目录文献）
If a = "" Then
MsgBox "不存在"
Else
MsgBox "存在"
End If
End Sub
（2） 打开指定目录下文献
Sub 打开指定目录下文献()
Dim a$，n!，wbs As Workbook
a = Dir("c:\*.txt")
Workbooks.Open "c:\" & a
Do
a = Dir
If a <> "" Then
Workbooks.Open "c:\" & a
Else
Exit Sub
End If
Loop
End Sub
5：工作簿表达办法
在workbook对象中，有一种SHEETS集合,其成员是worksheet对象或chart对象。
worksheets仅指是工作表，而sheets包括图表，工作表，宏表等等
VBA中，经常在工作表之间转换或者对不同工作表中单元格区域进行操作.
普通有下面几种办法:
（1）：Sub 直接使用工作表名称法()
MsgBox Worksheets("我工作表").Name
MsgBox Sheets("我图表").Name
End Sub
（2）Sub 索引号表达法()
MsgBox Worksheets(1).Name
End Sub
（3）Sub 工作表代码索引号表达法()
MsgBox Sheets(1).Name
End Sub
（4）Sub 直接取工作代码法()
MsgBox Sheet1.Name
End Sub
（5）Sub 活动工作表()
MsgBox ActiveSheet.Name
End Sub
注意：当工作簿涉及工作表、宏表、图表等时，
使用索引号引用工作表如Sheets(1)与
WorkSheets(1)引用也许不是同一种表。
Sub worksheetss()
MsgBox Worksheets(1).Name
MsgBox Sheets(1).Name
End Sub
Sub sheetss()
For i = 1 To Sheets.Count
MsgBox Sheets(i).Name
Next
End Sub
6：工作表集合应用
（1）Sub 遍历sheets下所有对象()
For Each shs In Sheets
k = k + 1
Cells(k，1) = shs.Name
Next
End Sub

（2）Sub 遍历worksheets下所能对象()
For Each shs In Worksheets
k = k + 1
Cells(k，2) = shs.Name
Next
End Sub
（3）Sub 工作表存在与否()
Dim sn$
For Each sht In Sheets
sn = sht.Name
If sn = "我工作表" Then
MsgBox "存在"
Exit Sub
End If
Next
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 If
Next
MsgBox "不存在"
End Sub
7：工作表增长与删除

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 Sub

Sub 删除工作表()
Sheet10.Delete
End Sub
8：工作表删除与添加
如果想批量新建工作表，可以成果循环来制作
Sub 新建1到12月份工作表()
Dim j%
For j = 12 To 1 Step -1
Sheets.Add.Name = j & "月"
Next
End Sub

'删除工作表
Sub 删除sheet()
On Error Resume Next （当浮现错误时候忽视错误）
Application.DisplayAlerts = False （当屏幕有警告提示时候忽视启动）
Dim i%
For i = 1 To 12
Sheets(i & "月").Delete
Next
Application.DisplayAlerts = True （当屏幕有警告提示时候忽视关闭，否则，下次运营代码时候仍旧是忽视关闭状态）
End Sub
9：工作表移动与复制
（1） 工作表复制
表达式.copy(Before，After)
Sub 复制()
Sheet1.Copy Sheets(Sheets.Count)
End Sub
（2） 工作表移动
'表达式.Move(Before，After)
Sub 移动()
Sheet1.Move ，Sheet3
End Sub
10：工作表选取与激活
Worksheet.Select 办法 不支持隐藏选用
Worksheet.Activate 办法 支持隐藏选用
（1）： Sub 迅速选取所有工作表()
Worksheets.Select （只选取工作表）
Sheets.Select （工作表，图表等所有选取）
End Sub
（2）：Sub 自定义选取()
Worksheets(Array(1，3，5)).Select
End Sub
11：拆分工作簿实例
Sub 拆分到工作簿()
Dim wk As Workbook，ss$，k% 声明wk为一种工作簿类型变量
Application.DisplayAlerts = False
For 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.Close
Next
Application.DisplayAlerts = True
MsgBox "拆分工作簿完毕！"
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 Sub
2：range其她写法
Range("a1:b10").Select '普通写法
Range("a1"，"b10").Select '变化写法1
Range(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 Sub
3：range变量与引用
（1）：range变化写法
1):range("地址区域").range("地址区域")
Sub 序号表达法()
Range("b2:d4").Range("b2").Select '相对引用写法
'参照前一种range左上单元格
End Sub
2)：2.range地址区域中支持变量
Sub range变量支持()
Dim a%
a = 3
Range("a" & a).Select
Range("c3:e5")(2).Select
End Sub
3):动态引用实例
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 Sub
4：Range引用与索引
range区域中每个单元格，咱们也可以用索引号表达出来
写法：range("单元格区域")(行号，列号)
Sub 索引号取出range单元格()
'Range("a1:c4")(4).Select '引用顺序是：从左向右，从上到下选用
'Range("b2:c4")(3).Select '此前一种单元格区域为照
Range("a1:c4")(4.5).Select '当有小数时，则取整
'注意：如果索引号浮现小数，则按照“四舍六入五单双”“银行家舍入法”
End Sub
Sub 行列号定位()
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 Sub

'cells可以像range同样可以参照前面单元格位置
Sub 参照写法()
Range("b3:f11").Cells(2，2).Select
Range("b3:f11").Cells(6).Select '从左到右，从上到下
Range("b3:f11")(6).Select '与上一句相等
End Sub
'注意：
'1.cells中数字同样支持正数，负数，0值，小数(四舍六入五单双)
'2.cells不能像range同样可以引用一种区域，只能引用一种单元格
6：单元格简写
除了前面讲range\cells单元格区域表达办法还，还是一种简朴写法
'写法：[单元格地址] '注意：中括号中单元格地址并不需要双引号("")
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).Select
Range("b2:c6")(3).Select
[b2:c6].Cells(4).Select
End Sub

Sub 动态区域引用()
a = Application.CountA([a:a])
b = Application.CountA([1:1])
Range(Range("a1")，Range(Chr(64 + b) & a)).Select '运用chr函数，让字母形式列号也支持变量
End Sub

Sub chr函数字符循环()
For i = 1 To 65535
Cells(i，1) = i
Cells(i，2) = Chr(i)
Next
End Sub
7：三种单元格引用小结

功能
Range
Cells
[单元格地址]
引用对象
单元格，区域，行，列
单元格
单元格，区域，行，列
变量支持
支持
支持
不支持
书写难易
难
难
易
Range("a1:c" & i).Select '引用单元格是区域且有变量
Cells(i，"c").Select '引用是单个单元格且有变量
[a1:19].Select '引用是区域或单元格且无变量
8：行列引用
'行列引用
Sub 列引用()
Columns(1).Select
Columns("b").Select （b列）
Columns("c:e").Select （c到e列）
End Sub

Sub 行引用()
Rows(1).Select
Rows("2").Select
Rows("3:4").Select （3到4行）
End Sub

Sub range行列表式法()
Range("1:1").Select （第一行）
Range("2:4").Select （2到4行）
Range("a:a").Select （a列）
Range("b:d").Select （B到D列）
End Sub
Sub 简写法()
[a:a].Select
[b:d].Select
[1:1].Select
[2:4].Select
End Sub

Sub 全选()
Rows.Select '选取所有行
Columns.Select '选取所有列
Cells.Select '选取所单元格
i = Rows.Count
j = Columns.Count
k = Cells.Count
End Sub
Sub 动态引用使用区域()
a = Application.CountA(Columns(1)) （返回第一列当中使用（非空）单元格数目）
b = Application.CountA(Rows(1)) （返回第一行中使用（非空）单元格数目）
Range("a1"，Cells(a，b)).Select （动态引用单元格）
End Sub
9：row与column属性
Range.Row 属性
'返回区域中第一种子区域第一行行号
'Range.Column 属性
'返回指定区域中第一块中第一列列号
Sub test()
i = Range("a3:b9").Range("a5").Row （返回A3到B9区域第一行第五列所在单元格位置真实行号）
j = Range("a3:b9").Row
i = Range("b3:d9").Range("a5").Column
j = Range("b3:d9").Column
End 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 rw
End Sub
10：单元格地址与值
单元格值表达办法
Sub 单元格值表达()
a = [a1].Value '实际是什么，就是什么
b = [a1].Text '看到是什么，就是什么
c = [a1]
End Sub

'注意：一种单元格可以省略value，多单元格区域不能省略
Sub 多区域赋值()
Range("e1:e4") = Range("d1:d4").Value
End Sub

'单元格地址与引用
Sub 地址与引用()
Set rng = [b2:f2]
[a9] = rng.Address(1，1) '绝对引用
[b9]