第一章、Excel VBA基础知识

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、综合运用

以上例子中，找出未盘点的编码

代码

第一章结束