VBA学习笔记
笔记摘抄自EXCEL精英培训-蓝色幻想
目录
CH1 VBA基础知识
CH2 VBA函数与公式
CH3 VBE编辑器
CH4 分支与END语句
CH5 文件操作
<br />
<br />
CH1 VBA基础知识
一、VBA对象：
VBA中的对象其实就是我们操作的具有方法、属性的excel中支持的对象
Excel中的几个常用对象表示方法
1、工作簿
Workbooks 代表工作簿集合，所有的工作簿,Workbooks(N)，表示已打开的第N个工作簿
Workbooks ("工作簿名称")
ActiveWorkbook 正在操作的工作簿
ThisWorkBook 代码所在的工作簿
2、工作表
Sheets("工作表名称")
Sheet1 表示第一个插入的工作表,Sheet2表示第二个插入的工作表....
Sheets(n) 表示按排列顺序，第n个工作表
ActiveSheet 表示活动工作表，光标所在工作表
worksheet 也表示工作表，但不包括图表工作表、宏工作表等。
3、单元格
cells 所有单元格
Range ("单元格地址")
Cells(行数,列数)
Activecell 正在选中或编辑的单元格
Selection 正被选中或选取的单元格或单元格区域
二、VBA方法和属性
1.VBA属性：VBA属性就是VBA对象所具有的特点。表示某个对象的属性的方法是。
对象.属性=属性值
Sub ttt()
Range("a1").Value = 100
End Sub
2.VBA方法
VBA方法是作用于VBA对象上的动作
表示用某个方法作用于VBA的对象上，可以用下面的格式：
Sub ttt4()
牛排.做 熟的程度:=七成熟
Range("A1").Copy Range("A2")
End Sub
三、VBA语句
1.宏程序语句：运行后可以完成一个功能
Sub test() 开始语句
Range("a1") = 100
End Sub 结束语句
2.函数程序语句：运行后可以返回一个值
Function shcount()
shcount = Sheets.Count
End Function
3.在程序中应用的语句
Sub test2()
Call test
End Sub
Sub test3()
For x = 1 To 100 for next 循环语句
Cells(x, 1) = x
Next x
End Sub
4.判断语句
（1）IF判断语句
Sub 判断() 单条件判断
If Range("a1").Value > 0 Then
Range("b1") = "正数"
Else
Range("b1") = "负数或0"
End If
End Sub
Sub 判断2() 多条件判断
If Range("a1").Value > 0 Then
Range("b1") = "正数"
ElseIf Range("a1") = 0 Then
Range("b1") = "等于0"
ElseIf Range("B1") <= 0 Then
Range("b1") = "负数"
End If
End Sub
Sub 多条件判断2()
If Range("a1") <> "" And Range("a2") <> "" Then
Range("a3") = Range("a1") * Range("a2")
End If
End Sub
（2）IFF函数判断
Sub 判断4()
Range("a3") = IIf(Range("a1") <= 0, "负数或零", "负数")
End Sub
（3）select判断
Sub 判断1() 单条件判断
Select Case Range("a1").Value
Case Is > 0
Range("b1") = "正数"
Case Else
Range("b1") = "负数或0"
End Select
End Sub
Sub 判断2() 多条件判断
Select Case Range("a1").Value
Case Is > 0
Range("b1") = "正数"
Case Is = 0
Range("b1") = "0"
Case Else
Range("b1") = "负数"
End Select
End Sub
Sub 判断3()
If Range("a3") < "G" Then
MsgBox "A-G"
End If
End Sub
（4）判断范围
Sub if 区间判断()
If Range("a2") <= 1000 Then
Range("b2") = 0.01
ElseIf Range("a2") <= 3000 Then
Range("b2") = 0.03
ElseIf Range("a2") > 3000 Then
Range("b2") = 0.05
End If
End Sub
Sub select区间判断()
Select Case Range("a2").Value
Case 0 To 1000
Range("b2") = 0.01
Case 1001 To 3000
Range("b2") = 0.03
Case Is > 3000
Range("b2") = 0.05
End Select
End Sub
5.循环语句
（1）
Sub t1()
Range("d2") = Range("b2") * Range("c2")
Range("d3") = Range("b3") * Range("c3")
Range("d4") = Range("b4") * Range("c4")
Range("d5") = Range("b5") * Range("c5")
Range("d6") = Range("b6") * Range("c6")
End Sub
（2）FOR <循环变量>=<初值>to<终值> [step 步长]
<循环体>
[EXIT FOR]
<循环体>
NEXT [循环变量]
Sub t2()
Dim x As Integer
For x = 10000 To 2 Step -3
Range("d" & x) = Range("b" & x) * Range("c" & x)
Next x
End Sub
（3）
Sub t3()
Dim rg As Range
For Each rg In Range("d2:d18")
rg = rg.Offset(0, -1) * rg.Offset(0, -2)
Next rg
End Sub
（4） do[ while 逻辑表达式]
<循环体>
[EXIT DO]
<循环体>
loop [while 逻辑表达式]
Sub t4()
Dim x As Integer
x = 1
Do
x = x + 1
Cells(x, 4) = Cells(x, 2) * Cells(x, 3)
Loop Until x = 18
End Sub
（5）
Sub t5()
x = 1
Do While x < 18
x = x + 1
Cells(x, 4) = Cells(x, 2) * Cells(x, 3)
Loop
End Sub
6.变量
（1）什么是变量？
所谓变量，就是可变的量。就好象在内存中临时存放的一个小盒子，这个小盒子放的什么物体不固定。
Sub t1()
Dim X As Integer x就是一个变量
For X = 1 To 10
Cells(X, 1) = X
Next X
End Sub
（2）变量的类型和声明
A.变量的类型
byte 字符型（0-255）
integer 整数型（-32768-32767）
long 长整数型
single 单精度浮点型
double 双精度浮点型
currency 货币型
decimal 小数型
string 字符串型 （数字 文本）
date 日期型
boolean 布尔型 （逻辑判断）
variant 万能型
B.声明变量
dim 变量名 as 数据类型
dim str as string
C.如给文本、数值、日期等数据型变量赋值
let 变量名称 =数据
如给对象变量（object型，如单元格）赋值
set 变量名称=对象
e.g
set rng=worksheets("sheet1").range("a1")
rng.value="欢迎"
使用常量
const 变量名称 as 数据类型-数值
const p as single =3.14
（3）变量的存活周期
1 过程级变量:过程结束，变量值释放
如t1
2 模块级变量:变量的值只在本模块中保持，工作簿关闭时随时释放
例5
Sub t6()
m = 1
End Sub
Sub t5()
MsgBox m
m = 7
End Sub
3 全局级变量: 在所有的模块中都可以调用，值会保存到EXCEL关闭时才会被释放。
public 变量
Sub t7()
MsgBox qq
End Sub
（4）变量的释放
一般情况下，过程级变量在过程运行结束后就会自动从内存中释放，而只有一些从外部借用的对象变量才需要使用set 变量=nothing进行释放。
<br />
<br />
CH2 VBA函数与公式
一、在单元格中输入公式
1、用VBA在单元格中输入普通公式
Sub t1()
Range("d2") = "=b2*c2"
End Sub
Sub t2()
Dim x As Integer
For x = 2 To 6
Cells(x, 4) = "=b" & x & "*c" & x
Next x
End Sub
2、用VBA在单元格输入带引号的公式
Sub t3()
Range("c16") = "=SUMIF(A2:A6,""b"",B2:B6)" 遇到单引号就把单引号加倍
End Sub
3、用VBA在单元格中输入数组公式
Sub t4()
Range("c9").FormulaArray = "=SUM(B2:B6*C2:C6)"
End Sub
二、利用单元格公式返回值
Sub t5()
Range("d16") = Evaluate("=SUMIF(A2:A6,""b"",B2:B6)")
Range("d9") = Evaluate("=SUM(B2:B6*C2:C6)")
End Sub
三、借用工作表函数
Sub t6()
Range("d8") = Application.WorksheeFunction.CountIf(Range("A1:A10"), "B")
End Sub
四、利用VBA函数
Sub t7()
Range("C20") = VBA.InStr(Range("a20"), "E")
End Sub
五、编写自定义函数
Function wn()
wn = Application.Caller.Parent.Name
End Function
<br />
<br />
CH3 VBE编辑器
- 1.注释文字 ：文字前加单引号
- 2.强制转行 空格+下划线
- 3.调试工具栏： 逐语句运行或按F8
- 4.设置断点：STOP
- 5.立即窗口： debug.print "第“&X&”运行结果“ 显示某步程序运行结果
- 6.本地窗口：显示运行中断时对象信息
<br />
<br />
CH4 分支与END语句
一、END语句
作用：强制退出所有正在运行的程序。
二、Exit语句
退出指定的语句
1、Exit Sub
Sub e1()
Dim x As Integer
For x = 1 To 100
Cells(1, 1) = x
If x = 5 Then
Exit Sub
End If
Next x
Range("b1") = 100
End Sub
2、Exit function
Function ff()
Dim x As Integer
For x = 1 To 100
If x = 5 Then
Exit Function
End If
Next x
ff = 100
End Function
3、Exit for
Sub e2()
Dim x As Integer
For x = 1 To 100
Cells(1, 1) = x
If x = 5 Then
Exit For
End If
Next x
Range("b1") = 100
End Sub
4、Exit do
Sub e3()
Dim x As Integer
Do
x = x + 1
Cells(1, 1) = x
If x = 5 Then
Exit Do
End If
Loop Until x = 100
Range("b1") = 100
End Sub
三、分支语句
1.Goto语句,跳转到指定的地方
Sub t1()
Dim x As Integer
Dim sr
100:
sr = Application.InputBox("请输入数字", "输入提示")
If Len(sr) = 0 Or Len(sr) = 5 Then GoTo 100
End Sub
2.gosub..return ,跳过去,再跳回来
Sub t2()
Dim x As Integer
For x = 1 To 10
If Cells(x, 1) Mod 2 = 0 Then GoSub 100
Next x
Exit Sub
100:
Cells(x, 1) = "偶数"
Return
End Sub
3.on error resume next 遇到错误,跳过继续执行下一句
Sub t3()
On Error Resume Next
Dim x As Integer
For x = 1 To 10
Cells(x, 3) = Cells(x, 2) * Cells(x, 1)
Next x
End Sub
4.on error goto 出错时跳到指定的行数
Sub t4()
On Error GoTo 100
Dim x As Integer
For x = 1 To 10
Cells(x, 3) = Cells(x, 2) * Cells(x, 1)
Next x
Exit Sub
100:
MsgBox "在第" & x & "行出错了"
End Sub
5.on error goto 0 取消错误跳转
Sub t5()
On Error Resume Next
Dim x As Integer
For x = 1 To 10
If x > 5 Then On Error GoTo 0
Cells(x, 3) = Cells(x, 2) * Cells(x, 1)
Next x
Exit Sub
<br />
<br />
CH5 文件操作
一、常用概念
1.excel文件和工作簿
excel文件就是excel工作簿，excel文件打开需要excel程的支持
（1） Workbooks 工作簿集合，泛指excel文件或工作簿
Workbooks("A.xls")，名称为A的excel工作簿
Sub t1()
Workbooks("A.xls").Sheets(1).Range("a1") = 100
End Sub
workbooks(2)，按打开顺序，第二个打开的工作簿。
Sub t2()
Workbooks(2).Sheets(2).Range("a1") = 200
End Sub
（2）ActiveWorkbook ，当打开多个excel工作簿时，你正在操作的那个就是ActiveWorkbook（活动工作簿）
（3）Thisworkbook，VBA程序所在的工作簿，无论你打开多少个工作簿，无论当前是哪个工作簿是活动的,thisworkbook就是指它所在的工作簿。
2.工作簿窗口
Windows("A.xls"),A工作簿的窗口，使用windows可以设置工作簿窗口的状态，如是否隐藏等。
Sub t3()
Windows("A.xls").Visible = False
End Sub
Sub t4()
Windows(2).Visible = True
End Sub
3.excel工作表的分类
excel工作表有两大类，一类是我们平常用的工作表(worksheet)，另一类是图表、宏表等。这两类的统称是sheets
sheets 工作表集合，泛指excel各种工作表
Sheets("A")，名称为A的excel工作表
Sub t1()
Sheets("A").Range("a1") = 100
End Sub
workbooks(2)，按打开顺序，第二个打开的工作簿。
Sub t2()
Sheets(2).Range("a1") = 200
End Sub
ActiveSheet ，当打开多个excel工作簿时，你正在操作的那个就是ActiveSheet
二、 EXCEL文件操作
1 判断A.Xls文件是否存在
Sub W1()
If Len(Dir("d:/A.xls")) = 0 Then
MsgBox "A文件不存在"
Else
MsgBox "A文件存在"
End If
End Sub
2 判断A.Xls文件是否打开
Sub W2()
Dim X As Integer
For X = 1 To Windows.Count
If Windows(X).Caption = "A.XLS" Then
MsgBox "A文件打开了"
Exit Sub
End If
Next
End Sub
3 excel文件新建和保存
Sub W3()
Dim wb As Workbook
Set wb = Workbooks.Add
wb.Sheets("sheet1").Range("a1") = "abcd"
wb.SaveAs "D:/B.xls"
End Sub
4 excel文件打开和关闭
Sub w4()
Dim wb As Workbook
Set wb = Workbooks.Open("D:/B.xls")
MsgBox wb.Sheets("sheet1").Range("a1").Value
wb.Close False
End Sub
5 excel文件保存和备份
Sub w5()
Dim wb As Workbook
Set wb = ThisWorkbook
wb.Save
wb.SaveCopyAs "D:/ABC.xls"
End Sub
6 excel文件复制和删除
Sub W6()
FileCopy "D:/ABC.XLS", "E:/ABCd.XLS"
Kill "D:/ABC.XLS"
End Sub
7.判断A工作表文件是否存在
Sub s1()
Dim X As Integer
For X = 1 To Sheets.Count
If Sheets(X).Name = "A" Then
MsgBox "A工作表存在"
Exit Sub
End If
Next
MsgBox "A工作表不存在"
End Sub
-
excel工作表的插入
Sub s2() Dim sh As Worksheet Set sh = Sheets.Add sh.Name = "模板" sh.Range("a1") = 100 End Sub
9.excel工作表隐藏和取消隐藏
Sub s3()
Sheets(2).Visible = True
End Sub
10.excel工作表的移动
Sub s4()
Sheets("Sheet2").Move before:=Sheets("sheet1") ‘sheet2移动到sheet1前面
Sheets("Sheet1").Move after:=Sheets(Sheets.Count) ’ sheet1移动到所有工作表的最后面
End Sub
11.excel工作表的复制
Sub s5() 在本工作簿中
Dim sh As Worksheet
Sheets("模板").Copy before:=Sheets(1)
Set sh = ActiveSheet
sh.Name = "1日"
sh.Range("a1") = "测试"
End Sub
Sub s6() 另存为新工作簿
Dim wb As Workbook
Sheets("模板").Copy
Set wb = ActiveWorkbook
wb.SaveAs ThisWorkbook.Path & "/1日.xls"
wb.Sheets(1).Range("b1") = "测试"
wb.Close True
End Sub
12.保护工作表
Sub s7()
Sheets("sheet2").Protect "123"
End Sub
Sub s8() 判断工作表是否添加了保护密码
If Sheets("sheet2").ProtectContents = True Then
MsgBox "工作簿保护了"
Else
MsgBox "工作簿没有添加保护"
End If
End Sub
13.工作表删除
Sub s9()
Application.DisplayAlerts = False
Sheets("模板").Delete
Application.DisplayAlerts = True
End Sub
14.工作表的选取
Sub s10()
Sheets("sheet2").Select
End Sub