01-01、Excel VBA简介
1.VBA是什么,能干什么
VBA可以说是一种编程语言,是VB的分支
VBA的作用,可以将重复的Excel工作简单化
2.VBA具备的基础知识
2.1基础操作,能发现Excel软件的局限性
2.2有一定的函数功底
2.3会VB语言(会VB语言更好,不一定一定需要)
3.保存
Excel启用宏工作簿,可以防止代码丢失
01-02、宏在工作中的运用
1.宏的定义
宏:macro 形容词:巨大的,大量的,宏观的 名词:【电脑】巨(宏)指令
定义:椅子可以自动执行的代码(VBA)
录制宏相当于摄像机的功能
2.录制宏
在开发工具中的录制宏中进行宏的录制
重复执行相同的操作。可以考虑用宏
总结:
优点:重复执行相同操作,提高工作效率
确定:不够智能化,无法交互工作,代码冗余
解决方法:VBA 即是用写代码的方法代替录制宏
3.宏在Excel中的地位
虽然宏看起来不够灵活,但对于学习VBA编程是非常重要的。
3.1提高代码编写效率
3.2帮助学习VBA知识
01-03、VBA基础知识
1.VBA概念
Visual Basic for Applications(VBA)是一种Visual Basic的一种宏语言,主要能用来扩展Windows的应用程式功能
VBA是寄生于VB应用程序的版本,必须依赖于父程序,如EXCEL,CAD,CORELDRAW
2.VBA和VB的区别
2.1. VB是编写应用程序,而VBA是使已有的应用程序(EXCEL、CAD)自动化
2.2.VB具有自己的开发环境,而VBA必须寄生于已有的应用程序.
2.3.运行VB开发的应用程序,不必安装VB,而VBA开发的程序必须依赖于它的父应用程序
3.VBA的用途
3.1.规范用户的操作,控制用户的操作行为
3.2.操作界面人性化,方便用户操作
3.3.多步骤,重复步骤可以通过执行VBA代码来迅速实现
3.4.实现一些无法实现的功能
4.VBA功能展示
01-04、Excel VAB窗口介绍
1.VBE就是VBA的编辑窗口
1.1按住ALT+F11快捷键
1.2开发工具-Visual Basic
2.VBE窗口简介
2.1立即窗口:即使可以看到结果
2.2本地窗口:按F8键可以看到程序运行的步骤和每一步的结果
3.过程(子过程和函数过程)
3.1一般过程
Sub aaa()
End Sub
3.2自定义函数过程
Function ff()
End Functiom
4.第一个VBA程序
Sub 我的第一个程序()
MsgBox "我会VBA啦!"
End Sub
5.运行宏的方法,直接运行和图像运行
图像运行:在Excel菜单中点插入--图形--画一个图形--点击图形右键--指定宏--确定即可
01-05、Excel VBA代码编写规则
1.VBE常见的设置
1.1工具--选项 里面可以修改编写代码时的字体颜色和字号等
1.2注释 不会参与运行,开发自己看的,需要在代码前加单引号(’)
统一加上解除注释块:选上工具栏的工具--右键--调出编辑窗口,上面有一个解除注释块和一个设置注释块。
1.3代码的运行
上面的符号分别代表运行,暂停和终止运行的命令
F8可以一步一步的运行代码,可以检查代码的问题所在
1.4代码的帮助
将代码选中(抹黑),按F1即可以出来代码的使用方法
2.代码的编写规则
2.1子过程
2.2函数过程
3.代码的换行(下划线+空格+换行)
01-06对象
1对象:现实中的对象,是真实存在的物体
在Excel中的对象指:工作薄、工作表、单元格、图表、透视表之类的
2.集合:也是一种特殊的对象,不过没有指定的哪个对象,知识一种统称,如“人”就是一个集合的叫法,在Excel中的集合如:workbooks,worksheets,cells等等
3.常用的代码操作对象
3.1工作薄(Workbooks)
workbooks(N)第N个工作薄
workbooks(“工作薄名”)
ActiveWorkbooks活动工作薄
ThisWorkbook代码所在的工作薄
3.2工作表(Worksheets)
Sheets(N) 第N个工作表
Sheets("工作表名")
SheetN 第N个工作表
ActiveSheet 活动工作表
worksheets 与 Sheets的区别
3.3单元格(cells)
Range ("单元格地址")
Cells(行号,列号)
[A1]单元格简写
Activecell 活动单元格
Selection 当前被选取的区域
01-07、属性
VBA属性:指对象所具有的特征
人的属性:姓名,年龄,身份证号,住址等
例:sub 属性()
Debug.Print sheet1.Name '.name即为工作表sheet1的属性
Debug.Print Sheet1.Range("a1").Value '.value即为工作表sheet1的属性
End Sub
例:Sub 属性赋值()
Sheet2.Name = "改变自己"
Sheet2.Range("a1") = "学习VBA"
End Sub
01-08、方法
方法:实际上是对对象的一种操作,他是一种动作,一种行为
例:
Sub 选择方法()
Range("a1:a10").Select ‘选择了a1:a10单元格,没有指定工作表就默认为当前活动工作表’
End Sub
Sub 复制方法()
Sheet1.Range("a1:a10") = 1 '将1写入表一的a1:a10区域
Sheet1.Range("a1:a10").Copy Sheet2.Range("a1") '将表一的a1:a10区域的值复制到表2的a1
End Sub
Sub 删除方法()
Sheets(3).Delete
End Sub
01-09、常量和变量
1.常量:常量是定义了之后不会变化的量
常量定义格式:Const 常量名=常量表达式
Sub 常量()
Const pi=3.1415926
End Sub
2.变量:在定义之后还能再次赋值的量
变量定义格式:Dim 变量 As 变量类型
Sub 变量()
Dim a As Integer
a=344 '此时a=344
a=3455 ‘再次赋值a=3455
End Sub
3.常量和变量的应用
Sub 应用()
Const pi=3.1415926
Dim a As Integer
a-200
Debug.print pi*a
End Sub
4.注意事项
4.1.VBA允许使用未定义的变量,默认是变体变量
4.2.变量强制性声明Option Explicit 或者在工具-编辑中设置每个程序都必须有变量声明
5.变量的命名规则
5.1以字母开头
5.2不能用保留字 如if end等
5.3字符个数不能超过255
5.4统一范围内必须是唯一的
01-10、数据类型
1.VBA中常见的数据类型
' 类型 注释 简写 占用内存
' Integer 整型 % 2Byte
' Single 单精度 ! 4Byte
' Double 双精度 # 8Byte
' Long 长整型 & 4Byte
' String 字符型 $ 定长或变长( 变长字符串最多可包含大约 20 亿 ( 2^31)个字符。 定长字符串可包含 1 到大约 64K ( 2^16 ) 个字符。)
' Currency 货币型 @ 8Byte
例:
Sub 数据类型()
Dim a As Integer
Dim b%
End Sub
Sub 多数据类型声明()
Dim a As Integer, b As Single, c As String
Dim d%, e!, f$
End Sub ‘中间用,隔开
01-11、判断语句之if
VBA中的IF条件判断语句,就像函数中的IF一样,可以单条件也可以多条件
例1:
Sub 判断语句()
Dim a As Integer, b As Integer
a = 2
b = 2
If a = b Then MsgBox "相等"
End Sub
例2:
'if判断语句有换行的话,就需要end if来结束
Sub 判断语句2()
Dim a As Integer, b As Integer
a = 2
b = 2
If a = b Then
MsgBox "相等"
End If
End Sub
例3:
'if……then……else……end if
Sub 判断语句3()
Dim a As Integer, b As Integer
a = 2
b = 3
If a = b Then
MsgBox "相等"
Else
MsgBox "不相等"
End If
End Sub
01-12、判断语句if的多条件
例:
Sub 多条件判断()
‘if 条件 then 结果 elseif 条件 then 结果 elseif 条件 then 结果elseif 条件 then 结果……else 结果……end if
If Sheet1.Range("b1") >= 90 Then
Sheet1.Range("b2") = "优秀"
ElseIf Sheet1.Range("b1") >= 80 Then
Sheet1.Range("b2") = "良好"
ElseIf Sheet1.Range("b1") >= 60 Then
Sheet1.Range("b2") = "中等"
Else
Sheet1.Range("b2") = "较差"
End If
End Sub
VBA中的IIF函数与工作表函数if的语法结构是一致的
例单条件:
Sub IIF函数应用()
Cells(2, 3) = IIf(Cells(1, 2) > 80, "优秀", "不优秀")
End Sub
例多条件:
Sub IIF函数应用2()
Cells(2, 3) = IIf(Cells(1, 2) >= 90, "优秀", _
IIf(Cells(1, 2) >= 80, "良好", IIf(Cells(1, 2) >= 60, "中等", "较差")))
End Sub
01-12B、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
例:
If Sheet1.Range("b1") >= 90 Then
Sheet1.Range("b2") = "优秀"
ElseIf Sheet1.Range("b1") >= 80 Then
Sheet1.Range("b2") = "良好"
ElseIf Sheet1.Range("b1") >= 60 Then
Sheet1.Range("b2") = "中等"
Else
Sheet1.Range("b2") = "较差"
End If
End Sub
01-13、判断语句之SELECT
Select Case 语句 根据表达式的值来决定执行几组语句中的一种
例1:
Sub select多条件判断1()
i = 1
Select Case i
Case Is > 0
MsgBox "正数"
Case Else
MsgBox "负数"
End Select
End Sub
例2:
Sub select多条件判断()
Select Case Sheet1.[d1].Value
Case "A"
Sheet1.[a3] = "A型血的你,不是一个怎么样的人"
Case "B"
Sheet1.[a3] = "B型血的你,更不是一个怎么样的人"
Case "AB"
Sheet1.[a3] = "AB型血的你,更不是一个怎么样的人"
Case "O"
Sheet1.[a3] = "O型血的你,是个不做的人"
End Select
End Sub
01-14、循环语句之do……loop
do……loop 循环语句,直到循环到满足某个条件
Sub 循环()
Dim a As Integer
Do
a = a + 1
If a > 10 Then
MsgBox "终于大于10"
Exit Do
End If
Loop
End Sub
01-15、循环语句之do……loop实例
例:
程序:
Sub 等级()
Dim rs As Integer
rs = 1
Do
rs = rs + 1
If rs > 10 Then
Exit Do
Else
If Cells(rs, 2) > 90 Then Cells(rs, 3) = "√"
End If
Loop
End Sub
01-16、循环语句之do while……loop
do while 当……的时候,里面包含一个if函数的判断
根据上面的例子进行改写:
Sub 循环while()
Dim rs As Integer
rs = 1
Do While Cells(rs, 2) <>"" '当单元格不等于空的时候
rs = rs + 1
If Cells(rs, 2) > 90 Then Cells(rs, 3) = "√"
Loop
End Sub
01-17、循环语句之do until……loop
do until 直到什么的时候结束
根据上面的例子进行改写:
Sub 循环until()
Dim rs As Integer
rs = 1
Do Until Cells(rs, 2) = "" '该单元格为空吗,为空的话就执行loop,否则就继续
rs = rs + 1
If Cells(rs, 2) > 90 Then Cells(rs, 3) = "√"
Loop
End Sub
例1:隔行填色
代码:
Sub 隔行填色()
Dim rs As Integer
rs = 2
Do Until Sheet1.Range("a" & rs) = ""
Sheet1.Range("a" & rs & ":" & "g" & rs).Interior.ColorIndex = 7
rs = rs + 2
Loop
End Sub
01-18、循环语句之while和until位置变化
while和until不但可以放在do后面,也可以放在loop后面
事实上有时候循环在最后一行进行判断,更具有意义
Sub doloop的最后判断循环()
Dim pss As String, i As Single
Do
i = i + 1
If i > 3 Then Exit Do '输入3次后就退出循环,只有3次机会
pss = InputBox("请输入密码")
Loop Until pss = "123" '当密码为123的时候结束循环
End Sub
Sub doloop的最后判断循环()
Dim pss As String, i As Single
Do
i = i + 1
If i > 3 Then Exit Do '输入3次后就退出循环,只有3次机会
pss = InputBox("请输入密码")
Loop while pss = "123" '当密码不是123的时候结束循环
End Sub
01-18B、循环语句do……loop小结
Do [{While | Until} 表达式] ‘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. Exit Do ,Do...Loop,If...Then通常结合使用.
4. 如果 Exit Do 使用在嵌套的 Do...Loop 语句中,则 Exit Do 会将控制权转移到 Exit Do 所在位置的外层循环。
例:
Sub test()
Dim a%
Do
a = a + 1
If a > 10 Then
MsgBox a & "终于大于10"
Exit Do
End If
Loop
End Sub
Sub Test2()'注意这是一个死循环,按F8运行(中止死循环:ctrl+暂停键)
Do
b = b + 1
Do
a = a + 1
If a > 3 Then MsgBox "即将跳出内层循环": Exit Do
Loop
MsgBox "即将进行外层循环"
Loop
End Sub
01-19、循环语句for each ……next
当需要处理集合成员时,一般会用for each……next,实际上就是处理对象
例子:在a2:a10单元格中,A1的全部标记为红色
Sub foreach循环1()
Dim rng As Range, n As Integer
For Each rng In Sheet1.Range("a2:a10") '取出a2:a10中的内容
If rng = "A1" Then rng.Interior.ColorIndex = 3 '填充颜色
Next
End Sub
例2:取出工作表的名称
Sub foreach循环2()
Dim wsh As Worksheet, a As Byte
For Each wsh In Worksheets '取出工作表中的每个sheet表
n = n + 1
Sheet1.Cells(n, 3) = wsh.Name '将取出的工作表名称放入第3 列
Next
End Sub
01-20、循环语句之for……next
for……next也是循环语句,与之前的do……loop不同的是for……next含有一个内置的计数器
例:从1一直加到100
Sub fornext循环()
Dim i As Integer, j As Integer
For i = 1 To 100
j = j + i
Next
MsgBox j
End Sub
例:知道单价和数量计算金额
Sub fornext循环2()
Dim rng As Integer
For rng = 2 To 21
Sheet2.Cells(rng, 4) = Sheet2.Cells(rng, 2) * Sheet2.Cells(rng, 3)
Next
End Sub
01-20B、For...NEXT小结与实例
For...Next 语句
以指定次数来重复执行一组语句
语法
For 计数变量 = 初始值 To 终止值 [Step 步长值]
[执行的一条或多条语句]
[Exit For]
[执行的一条或多条语句]
Next [计数变量]可以忽略不写
注意:1.循环中可以在任何位置放置任意个 Exit For 语句,随时退出循环。
2.Exit For与 If...Then经常一起使用,目的是:找到符合条件后,跳出循环,而不必再进行不必要的循环。
例:
Sub fornext示例()
Dim i As Integer, j As Integer
For i = 2 To 16 Step 1
If Sheet3.Cells(i, 1) = "2班" Then Exit For
Next i
'计算出第一次出现2班人数的位置
For j = 2 To 16 Step 1
If Sheet3.Cells(j, 1) = "3班" Then Exit For
Next j
'计算出第一次出现3班人数的位置
MsgBox "2班的人数是" & j - i
'用第一次出现3班的位置减去2班出现的位置即为2班的人数
End Sub
3.可以将一个 For...Next 循环放置在另一个 For...Next 循环中,组成嵌套循环。
For I = 1 To 10
For J = 1 To 10
For K = 1 To 10
...
Next K
Next J
Next I
例:
01-21、用语句for……next制作九九乘法表
01-22、exit与end语句
exit 是退出当前语句
1、exit do 2、exit for 3、exit function 4、exit sub
例:找第一次出现田七的位置
Sub 田七位置()
Dim n As Integer
For n = 2 To 7
If Sheet1.Cells(n, 1) = "田七" Then Exit For
Next
MsgBox "田七首次出现的位置为" & n & "行"
End Sub
end结束一个过程或者块
1、end 2、end if 3、end select 4、end sub
01-23、跳转语句
GoTo line无条件的转移到过程中指定的行
注意 太多的GoTo语句,会是程序代码不容易月底及调试
尽可能使用结构化的控制语句(Do……loop,for……next,if then……else)
例:
Sub dotoline()
Dim str As String, k As Integer
123:
k = k + 1
If k > 3 Then Exit Sub
ste = InputBox("请登录用户名:")
If str <> "admin" Then GoTo 123
End Sub
例2:判断是否迟到
1/3是时间里面的8:00
01-24、错误分支语句
计算总分:
需要知道错误发生在第几行:
01-25、with语句
with语句,当对某个对象执行一系列语句时,不用重复指出对象的名称。
with的嵌套使用
01-26、VBA与公式
01-27、VBA与函数1
Sub 带工作表函数的计算()
Dim i As Integer
For i = 1 To 10
Sheet4.Cells(i, 4) = "=sum(a" & i & ":b" & i & ")"
Next
End Sub
Sub 公式带引号的计算()
Cells(12, 1) = "=countif(a1:a10,"">9"")"
'当公式中含有引号的时候,就要将原来的引号再加上引号,及双引号
Cells(13, 1) = "=sum(indirect(""a1:a10""))"
End Sub
01-28、VBA与函数2
借用工作表函数
Application.WorksheetFunction. 在VBA编辑窗口中输入这样的代码,可以调用工作表函数
例如:Application.WorksheetFunction.Sum
Sub 调用工作表函数()
MsgBox Application.WorksheetFunction.CountIf(Range("a1:a10"), "钢笔")
MsgBox Application.CountIf(Range("a1:a10"), "钢笔")
MsgBox WorksheetFunction.CountIf(Range("a1:a10"), "钢笔")
End Sub
上面的例子说明Application和WorksheetFunction在调用函数的时候可以省略其中之一
Sub VBA函数()
MsgBox VBA.Format(Range("b1"), "yyyy年m月d日")
MsgBox Format(Range("b1"), "yyyy年m月d日")
End Sub
当VBA和工作表函数不够用的时候,就需要用到自定义函数了,下面看从身份证中提取性别
'自定义函数()
Function sex(rng As Range)
sex = IIf(Mid(rng, 15, 3) Mod 2, "男", "女")
End Function
01-29、VBA与运算符
1)赋值运算符 :=
2)数学运算符: &(字符连接符)、+(加)、-(减)、Mod(取余)、\(整除)、*(乘)、/(除)、-(负号)、^(指数)
3)逻辑运算符:Not(非)、And(与)、Or(或)、Xor(异或)、Eqv(相等)、Imp(隐含)
4)关系运算符: = (相同)、<>(不等)、>(大于)、<(小于)、>=(不小于)、<=(不大于)、Like
like用来比较两个字符串?
Print 任何单一字符
* 零个或多个字符。
# 任何一个数字 (0–9)。
[charlist] charlist.中的任何单一字符?
[!charlist] 不在 charlist 中的任何单一字符。
例:
Sub likess()
a = 1 Like "[!2]" '1不是非2中的任意一个
End Sub '按F8可以看到结果为True
01-30、like的运算符
01-31、like运算符的运用
例子:
代码:
Sub 运用()
Dim i As Integer, j As Integer, n As Integer
For i = 2 To 6
For j = 2 To 14
If Cells(j, "a") Like Cells(i, "e") Then n = n + 1
Cells(i, "f") = n
Next
n = 0
Next
End Sub
01-32、综合运用
以上例子中,找出未盘点的编码
代码
第一章结束