Excel VBA编程教程(基础一)(Microsoft)
VBA介绍
Visual Basic for Applications(VBA)是 VisualBasic 的一种宏语言,是微软开发出来在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。主要能用来扩展 Windows 的应用程序功能,特别是Microsoft Office软件。
说简单点,VBA 是运行在 Microsoft Office 软件之上,可以用来编写非软件自带的功能的编程语言。Office 软件提供丰富的功能接口,VBA 可以调用它们,实现自定义的需求。基本上,能用鼠标和键盘能做的事情,VBA 也能做。
正如前文所述,VBA 可以运行在 Office 软件上,包括 Excel、Word、PPT、Outlook 等。VBA 语言在 Office 软件中是通用的,基本语法和用法都相同。但是每一个软件具有自己独有的对象,例如 Excel 有单元格对象,Word 有段落对象,PPT 有幻灯片对象。
编写第一个VBA宏
「宏」:简单的说,宏是一段可以运行的 VBA 代码片段。
step one 创建启用宏的工作簿
首先新建一个工作簿,并将工作簿保存为「启用宏的工作簿」类型。详细步骤查看这篇文章。
step two 打开 VBA 编辑器
通过功能区「开发工具 → 代码→Visual Basic」或快捷键 Alt + F11 打开 VBA 编辑器。详细步骤参考这篇文章。
step three
Excel 工作簿中的 VBA 代码通常保存在工作表对象或模块中。本例中,我们用模块保存 VBA 代码。
首先选中左侧工程列表中的工作簿,后右键,在弹出的选项列表中,选择「插入」。二级菜单中,选择「模块」,完成插入新模块。
插入完成后,在工程列表对应工作簿内,在模块文件夹下,显示新插入的模块。新插入的模块的默认名称是,模块 x,本例中是模块1。在属性窗口,可以修改模块的名称。
step four 打开模块
双击上一步插入的模块1,在右侧代码窗口区域里,将打开模块1的代码编辑器。在右侧代码窗口中当前打开的代码编辑器所对应的模块,在编辑器标题末尾可以看到。
step five 创建一个宏(VBA 过程)
在代码编辑窗口中,输入以下代码:
Sub MyCode()
End Sub
这段代码是一个空的 VBA 过程,只有开始和结束。过程的其他代码,都在这中间编写。
其中 Sub
是 Subroutine
的缩写,直译过来是子程序。在 VBA
中表示一个过程,也是代表一个过程的开始。下一行 End Sub
表示过程的结束。
step six 编写 VBA 过程实体部分
过程的实体部分指的是,真正会被执行的部分。上一步创建的是一个空的 VBA 过程,里面不包含实体代码。虽然可以正常运行,但是不会有结果输出。
这一步我们编写 VBA 过程的实体部分。将如下代码输入到过程的开始和结束处之间。
MsgBox "Hello World"
step seven 运行代码
现在我们已经写完了一个完整的 VBA 过程,现在运行它,看一下结果。
将光标放置在代码的任何一处,点击工具栏上的运行「▶ 」按钮,或按 F5,运行代码。
可以看到运行结果,Excel 弹出一个对话框,显示内容正是在代码中编写的内容。
认识VBA编辑器
编辑器中每个模块的基本用法如下:
- 工具栏:编辑器命令栏,与 Excel 功能区域类似,包含 Excel VBA 开发相关的命令。
- VBA 工程:显示当前 VBA 工程包含的所有对象。通常,一个工作簿就是一个 VBA 工程,其中包括 Excel
对象、工作表对象、模块等。 - 属性窗口:查看和设置选中对象的属性的窗口。
- 代码编辑窗口:实际编写代码的位置。编写、修改、保存代码,都在这里进行。
- 立即窗口:代码运行过程中,打印出的内容,在立即窗口中显示。一般用于调试代码。
管理VBA工程
通常,一个工作簿就是一个 VBA 工程,其中包括 Excel 对象、工作表对象、模块等。当多个工作簿同时打开时,他们公用同一个 VBA 编辑器,VBA 工程界面显示所有的 VBA 工程。
插入/删除模块
在一个 VBA 工程中想要插入新的模块时,可在 VBA 工程右键,选择插入类型即可。
要删除模块,选中模块,右键,选择「移除 模块」
语法介绍
程序结构示例
首先,本篇将使用以下代码,介绍各种程序结构,大家可以先看一下。
Sub MyCode()
'声明循环变量和是否为空变量
Dim i As Integer
Dim isBlank As Boolean
'循环 A2-A10 单元格
For i = 2 To 10
'存储单元格是否为空的结果
isBlank = Cells(i, 1).Value = ""
'如果为空,则用上方的单元格的值填充当前单元格
If isBlank Then
Cells(i, 1) = Cells(i - 1, 1)
End If
Next i
End Sub
以上代码运行后,在 A2:A10 单元格区域,依次判断每一个单元格是否为空,如果是空,则用上一个单元格的值填充。
过程
过程是 VBA 中,程序实际运行的最小结构。单独的一行或多行代码无法运行,必须把它们放置在一个过程里,才能运行。
在示例中,Sub 过程名()
开头,End Sub
为结尾部分是一个过程的主题,其余代码需要放置在两者之间。
Sub MyCode()
End Sub
程序语句
语句,是表示一个完整意思的一行代码。
例如,示例中第一行,声明变量就是一条语句。它表示,声明一个整型变量。同理,第二行、第三行和其余的每一行都是语句。VBA 中的过程,就是由这样一条条语句构成的。
Dim i As Integer
Dim isBlank As Boolean
通常,一行就是一个语句,除非它用换行符或合并符号.
Excel VBA 对象
我们学习 VBA 的最终目的是操纵 Excel,完成一些特定的目标。其中,操纵 Excel 就是通过 Excel VBA 对象完成的。
在示例中,Cells()
就是一个 Excel VBA 对象,表示一个单元格,提供行号和列号指定单元格。
程序运行结构
接下来是程序结构中最核心的部分,也是最有意思的部分,程序运行结构。大部分编程语言都具备基本的三种程序运行结构,分别是顺序结构、循环结构、判断结构。各种简单到复杂的算法,都是由这三种基本的结构,相互组合而完成。
1.顺序结构
首先是基本的顺序结构。顺序结构,顾名思义就是按照顺序依次执行。VBA 中的顺序就是从上到下、从左到右的顺序。
在示例中,首先运行两个声明语句,然后运行循环结构,以此类推。值得注意的事,当程序有嵌套时,嵌套的部分也是按照顺序指定的。
2.循环结构
第二个基本结构是循环结构。当使用循环结构时,循环部分代码,按照指定的循环次数,循环重复执行。
在我们的示例中,For 至 Next i 之间的代码就是一个循环代码。
VBA 中,有多种循环结构,本例中是 For 循环结构。For 循环结构中,第一行指定循环次数,最后一行表示开始下一个循环。
'循环开始
For i = 2 To 10
'这里是循环的代码
Next i
3.判断结构
最后一种基本结构是,判断结构。简单来说,该结构中,当提供的表达式为真(True)时,判断结构的主体部分才会被执行,否则跳过。
在示例代码中,If 开头和 End If 结尾处是典型的判断结构。第一行,判断 isBlank 变量是否为真,如果是,则执行判断结构主体部分,否则跳过。
'如果为空,则用上方的单元格的值填充当前单元格
If isBlank Then
Cells(i, 1) = Cells(i - 1, 1)
End If
运行
下面,我们实际运行我们的示例代码,看一看它具体的效果。
Excel 是一个对象,这个对象包含很多属性和子对象,而 VBA 是可以操作这些对象的工具,实现各种各样的效果。例如,Excel 包括 Range 对象,即单元格对象,使用 VBA 可以改变单元格对象的填充颜色属性。用代码表示如下。
'A1 单元格的填充颜色设置为颜色号为 49407 的颜色
Range("A1").Interior.Color = 49407
- Range(“A1”)、Interior、Color 等是 Excel 具备的对象和属性;
- 对象和属性的操作,是通过 VBA 语言实现的,即上面是一行 VBA 代码。中间的等号(=)是 VBA 语言的赋值符号,也是能改变单元格填充颜色的关键所在。
变量
变量是存储数据的一种表达方式。在程序开始,可以声明一个变量,指定变量的类型(数字、文本、逻辑值等),并给变量赋值。在程序其他地方,就可以用该变量,使其存储的值参与运算。
'声明一个文本类型的变量
Dim val As String
'给 val 变量赋值,即 "Hello World"
val = "Hello World"
'在 A1 单元格写入 val 变量存储的数据
Range("A1").Value = val
数组
组表示一组同类型的数据的集合,是 VBA 中最重要的概念之一。以下面的代码为例:
'创建数组
Dim Val(1 to 4) As String
'给数组的元素赋值
Val(1) = "Excel"
Val(2) = "Word"
Val(3) = "PowerPoint"
Val(4) = "Outlook"
基础运算符
使用 VBA 开发某项功能,本质上是,对变量进行基础的运算和操作,例如加减乘除比较等。为此,VBA 提供了很多运算符和操作符,利用它们可以实现复杂的运算。
今天先学习 VBA 提供的基础运算符和操作,以及它们的基本用法。VBA 运算符可以分为以下 6 类:
- 赋值运算符
- 算数运算符
- 比较运算符
- 逻辑运算符
- 连接运算符
- 其他运算符
VBA 中的连接运算符用于连接 2 个或多个文本。其用法与 Excel 公式中的 & 符号相同。
连接操作符
运算符 | 说明 | 示例 |
---|---|---|
& | 连接两个文本 | “Zhang” & ” ” & “San” -> “Zhang San” |
其他操作符
运算符 | 说明 |
---|---|
_ (下划线) | 将一行代码分解成两行 |
: ( 英文冒号) | 将两行代码放置在一行 |
比较运算符
比较运算符,比较提供的两个变量,如果符合比较条件,返回 True,否则,返回 False。
假设 a = 10
,b = 3
,->
表示结果。
运算符 | 说明 | 示例 |
---|---|---|
= | 比较两个值是否相等 | a = b -> False |
> | 大于 | a > b -> True |
>= | 大于等于 | a >= b => False |
< | 小于 | a < b -> False |
<= | 小于等于 | a <= b -> False |
<> | 不等于 | a <> b -> True |
- VBA 运算符基础教程(加减乘除+高级操作)
- VBA 数据类型基础教程(程序更高效、更精准)
注释
通常,一段代码写完后,不会完全没问题。在实际使用过程中可能需要修改,符合最新的需求。过一段时间再打开查看时,可能已经忘记了当时的思路,不能很快想起来有些代码实际的用途,更不用说让别人查看了。这时,就需要注释出场了。
注释是对代码的一种解释,不影响代码的运行。VBA 中的注释语句是,以英文单引号 (')
开头,后接需要解释说明的内容。
选择结构
if Then 结构
选择结构中,If Then
结构是最基础的一个。它只有条件表达式真时,执行的代码。
If Then
结构基本语法如下,其中 End If
是选择结构的结束标志。
If 条件表达式 Then
'表达式为真时,执行的代码
End If
现在我们看实际的例子,判断学生是否及格,及格条件是成绩 ≥60。如果及格,在C列对应单元格填写“及格”。具体代码如下:
Sub MyCode()
Dim i As Integer
For i = 2 To 10
If Cells(i, "B").Value >= 60 Then
Cells(i, "C") = "及格"
End If
Next i
End Sub
我们可以看到,我们使用 B 列中的学生成绩与 60 分比较,如果≥60分,就在 C 列填写及格。
条件表达式是 Cells(i, "B").Value >= 60
,选择性执行的代码部分是 Cells(i, "C") = "及格"
。
其中,For 语句是表示循环结构,这里只需知道程序从第一个学生循环到最后一个学生,依次判断每个学生的成绩。循环结构将在下一篇中做详细介绍。
将以上代码运行后,可以看到运行结果如下:
If Else 结构
很多时候,我们根据表达式的真假,真时执行一块代码,假时执行另一块代码。这种需求可以使用 If Else
结构实现。
If Else
结构中,条件表达式在真时,执行Then
后的代码;条件表达式为假时,执行 Else
后的代码。基本语法如下:
If 条件表达式 Then
'真时执行的代码
Else
'假时执行的代码
End If
我们继续看实际的例子。在上一个例子的基础上,这次对不及格的学生,在C列填入不及格。代码如下:
Sub MyCode()
Dim i As Integer
For i = 2 To 10
If Cells(i, "B").Value >= 60 Then
Cells(i, "C") = "及格"
Else
Cells(i, "C") = "不及格"
End If
Next i
End Sub
在这个例子中,条件表达式 Cells(i, "B").Value >= 60
为假时,表示学生成绩低于60分,即不及格。这时就执行 Else
后的代码。
程序运行结果如下:
循环结构
VBA 中程序循环结构基础,以及多种循环结构形式。包括子类在内,VBA 中常使用的循环结构包括 6 种,它们是:
循环结构 | 说明 |
---|---|
For … Next 循环 | 按指定次数循环执行 |
For Each 循环 | 逐一遍历数据集合中的每一个元素 |
Do While … Loop 循环 | 当条件为真时,循环执行 |
Do … Loop While 循环 | 当条件为真时,循环执行。无论条件真假,至少运行一次 |
Do Until … Loop 循环 | 直到条件为真时,循环执行 |
Do … Loop Until 循环 | 直到条件为真时,循环执行。无论条件真假,至少运行一次 |
此外,学习了两种跳出循环的语句,它们是:
跳出语句 | 说明 |
---|---|
Exit For | 跳出 For 循环 |
Exit Do | 跳出 Do While/Until 循环 |
本文介绍两个常用的循环
For 循环
- For … Next 循环
使用 For ... Next
循环可以按指定次数,循环执行一段代码。For 循环使用一个数字变量,从初始值开始,每循环一次,变量值增加或减小,直到变量的值等于指定的结束值时,循环结束。
For ... Next
循环语法如下:
For [变量] = [初始值] To [结束值] Step [步长]
'这里是循环执行的语句
Next
其中:
- [变量] 是一个数字类型变量,可在循环执行的语句里使用。
- [初始值] 和 [结束值] 是给定的值;
- [步长] 是每次循环时,变量的增量。如果为正值,变量增大;如果为负值,变量减小。
下面看一个实际的例子,求 1 至 10 数字的累积和。
Sub MyCode()
Dim i As Integer
Dim sum As Integer
For i = 1 To 10 Step 1
sum = sum + i
Next
End Sub
可以看到,For 循环使用i
变量,循环 10 次,i
的值从 1 到 10 变化。
值得注意的是,For 循环的 Step 值如果是 1,则 Step 关键词可省略。上述过程循环部分可写成如下方式:
For i = 1 To 10
sum = sum + i
Next
- Do While 循环
Do While
循环用于满足指定条件时循环执行一段代码的情形。循环的指定条件在 While
关键词后书写。
Do While … Loop
循环,根据 While 关键词后的条件表达式的值,真时执行,假时停止执行。基本语法如下:
Do While [条件表达式]
'循环执行的代码
Loop
其中,只要 [条件表达式] 为真,将一直循环执行。[条件表达式] 一旦为假,则停止循环,程序执行 Loop 关键词后的代码。
看一个实际的例子,还是求 1- 10 累积和。
Sub MyCode()
Dim i As Integer
Dim sum As Integer
i = 1
Do While i <= 10
sum = sum + i
i = i + 1
Loop
End Sub
i
变量的初始值是 1,根据 While
后的条件,只要 i
变量小于等于 10,后续的代码就可以一直循环执行。
这里为了演示使用了 Do While
循环,实际情况下,这种求和问题,使用 For
循环更简洁。
With 结构语法
With
结构由 With
和 End With
两个语句构成,对象的属性和方法都写在两者之间。基本语法如下:
With [对象]
.[属性] = [数据]
.[方法]
'其他属性和方法
End With
With
结构里,对象的属性和方法均有点 (.)符号开始,后接对象的属性名和方法名。
With 结构实例
现在看一个实际的例子,需要将工作簿中 Sheet1 工作表设置新名称,然后设置标签颜色为黑色,最后隐藏工作表。
如果不用 With 结构,代码如下:
Sub MyCode()
Worksheets("Sheet1").Name = "新名称"
Worksheets("Sheet1").Tab.ThemeColor = xlThemeColorLight1
Worksheets("Sheet1").Visible = xlSheetHidden
End Sub
可以看到,每个语句都重复写 Worksheets(“Sheet1”) 部分。
使用 With 结构,可以避免重复写同一个对象名,代码如下:
Sub MyCode()
With Worksheets("Sheet1")
.Name = "新名称"
.Tab.ThemeColor = xlThemeColorLight1
.Visible = xlSheetHidden
End With
End Sub
复制实例
本实例实现的是把很多excel表格的一些数据单元复制到一个新的文档上。
Sub 合并当前目录下所有工作簿的全部工作表()
Dim MyPath, MyName, AWbName
Dim Wb As Workbook, WbN As String
Dim G As Long
Dim Num As Long
Dim BOX As String
Application.ScreenUpdating = False
MyPath = ActiveWorkbook.Path
MyName = Dir(MyPath & "\" & "*.xls")
AWbName = ActiveWorkbook.Name
Num = 0
Do While MyName <> ""
If MyName <> AWbName Then
Set Wb = Workbooks.Open(MyPath & "\" & MyName)
Num = Num + 1
With Workbooks(1).ActiveSheet
.Cells(.Range("A65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)
'For G = 1 To Sheets.Count
Wb.Sheets(1).Range("A3:E3").Copy .Cells(.Range("A65536").End(xlUp).Row + 2, 1)
Wb.Sheets(1).Range("C9:D18").Copy .Cells(.Range("A65536").End(xlUp).Row + 2, 1)
'Next
WbN = WbN & Chr(13) & Wb.Name
Wb.Close False
End With
End If
MyName = Dir
Loop
Range("A1").Select
Application.ScreenUpdating = True
MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下:" & Chr(13) & WbN, vbInformation, "提示"
End Sub