一、VBA介绍
1、宏和VBA的关系
vba是编程语言,宏是用vba代码保存下来的程序。录制的宏是vba里最简单的程序,正因为如此,录制宏存在许多缺陷:如无法进行判断和循环,不能显示用户窗体,不能进行人机交互……
解决录制宏的这些问题,需要掌握vbs编程的方法,自主的编写vba程序。
2、VBA程序结构
3、VBE介绍
VBE即Visual Basic Editor即VBA的编程环境
1)打开VBE编辑器
Alt+F11(Alt+F8是查看宏)
依次执行:(2003版本)工具——宏——Visual Basic编辑器,(2007以上版本)在“视图”下的“宏”选项卡下
右键单击工作表标签,执行“查看代码”命令
2)主窗口:包含“工程资源管理器”、“属性窗口”、“菜单栏”、“工具栏”、“代码窗口”、“立即窗口”
3)菜单栏:包含VBE中各种组件的命令
4)工具栏:可以在“视图”——“工具栏”菜单里显示或隐藏
5)工程资源管理器:在这里可以看到所有打开的Excel工作簿和已加载的宏,一个Excel的工作簿就是一个工程,工程名称为“VBA Project(工作簿名称)”,这里最多可以显示工程里的4类对象,即Excel对象(包括sheet对象和ThisWorkbook对象)、窗体对象、模块对象和类模块对象。
6)属性窗口:在这里查看或设置对象的属性
7)代码窗口:包含对象列表框、过程列表框、边界标识条、视图按钮、代码编辑区、过程分界线。
8)立即窗口:一个重要用途是用来调试代码,想显示立即窗口,可以在视图选项卡中选择或者用快捷键“Ctrl+G”
4、牛刀小试:用vba生成工资条
二、VBA数据类型
1、VBA中的数据类型
数据类型就是对同一组数据的统称,如文本、日期、数值等。
VBA里的数据类型有:字节型(Byte)、整数型(Integer)、长整数型(Long)、单精度浮点型(Single)、双精度浮点型(Double)、货币型(Currency)、小数型(Decimal)、字符串型(String)、日期型(Date)、布尔型(Boolean)等,如表3-1
类型声明符:用特殊符号代替变量类型进行变量类型声明,例如Dim str$ 中$代表String类型。只有部分数据类型可以使用类型声明符。
三、VBA变量、常量
1、变量命名要求
变量必须以字母或汉字开头,不能包含空格、句号、感叹号、@、&、$和#,最长不能超过255个字符(一个汉字计2个字符)
2、声明变量
示例:
Dim str As String 声明一个String类型(变长)的变量,名称是str
Dim str As String*10 声明一个String类型(定长,最大存储10个字符)的变量,名称为str
Dim str$ 声明一个String(变长)类型变量,$变量类型声明符,代表String
Dim x, y, z As String
Dim str As String,nu As Integer 不同变量之间用逗号隔开
Dim str 每个变量都要指定数据类型,如果不指定,默认为Variant类型
3、声明常量
常量定义:Const 变量名称 As 数据类型=数值
示例:
Const pi As Single=3.14
4、变量的作用域
Public 变量名称 As 数据类型 【公有变量】
Private 变量名称 As 数据类型 【私有变量】
Static 变量名称 As 数据类型 【静态变量,整个代码运行期间值不变】
单个变量:本地变量
单个模块:模块级变量,用Dim或Private
所有模块:公共变量,用Public
5、强制声明所有变量(未定义变量则提示,否则不提示)
Option Explicit 在模块的第一句表示。
可以在VBE下的“工具”——“选项”——“编辑器”选项卡中进行设置,这里设置后,每个模块的第一句会自动写下“Option Explicit”,无需手动输入。
6、给变量赋值
语句为:[Let]变量名称 = 数据 这里的Let可以省略,即:变量名称=数据
例如:
Dim str As String
Let str = “一起来学习VBA”
语句为:Set 变量名称=对象 这里Set千万不能少。
例如:
Dim rng = Range '声明rng变量为Range类型
Set rng = Worksheets('sheet1').Range('A1') '给变量rng赋值
rng.Value='欢迎来到ExcelHome论坛' '将文本写入变量指定的单元格
四、VBA数组
1、一维数组
声明:Public|Dim 数组名(a to b) As 数据类型
示例:
Dim 七6班(1 to 50) As String '声明一个String类型的数组,名称为“七6班”,可以存储50个元素。
等价于:
Public|Dim Arr(0 to 49) As String
Public|Dim Arr(49) As String '数组索引默认是从0开始计数,如果在模块的第一句写“Option Base 1”,则数组的起始索引从1开始,而不是0.
七6班(1)='张青'
七6班(2)='邓城'
……
七6班(50)='冯吉'
2、多维数组:
Dim 酱油(1 to 3,1 to 20)
等价于
Dim 酱油(2,19)
3、多维动态数组
不确定数组中存储多少个元素,即不能预知元素的大小,可以在首次定义数组时括号内为空,写成:
Dim 数组名称()
例子:
Sub dtsz() Dim arr() As String '定义多维动态数组 Dim n As Long '统计A列有多少非空单元格 n = Application.WorksheetFunction.CountA(Range('A:A')) MsgBox n '使用Dim语句声明变量时,括号内的参数不能是变量,所以必须使用ReDim语句重新指定大小 ReDim arr(1 To n) As String '重新定义数组的大小End Sub
4、其他常用的创建数组的方式
1)使用Array函数创建数据
2)使用split创建数组
Sub ArrayTest() Dim arr As Variant '定义变量 '无论是否在模块中写入Option Base 1,Split函数返回的数组的最小索引都是0 arr = Split('邓成,林梅,张青,孔丽,冯吉维,孔佳', ',') '第一个参数包含分隔符或字符串变量,第二个参数是分隔符 MsgBox 'arr数组的第2个元素为:' & arr(1)End Sub
3)通过Range对象直接创建数组
5、数组相关函数:UBound和LBound函数
UBound(arr) 获取数组arr的最大索引号
LBound(arr) 获取数组arr的最小索引号
数组的元素个数可以表示为:UBound(arr)-LBound(arr)+1
Sub arrcount() Dim arr(10 To 50) 'Char(13)表示回车,_表示代码换行连接符 MsgBox '数组的最大索引号是:' & UBound(arr) & Chr(13) _ & '数组最小的索引号是:' & LBound(arr) & Chr(13) _ & '数组的元素个数是:' & UBound(arr) - LBound(arr) + 1End Sub
6、数组相关函数:Join函数
将一个以为数组里的元素使用指定的分隔符连接成一个新的字符串
Sub joinTest() Dim arr As Variant, txt As String arr = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9) '分割符@可以省略,如果省略,默认使用空格作为分隔符 txt = Join(arr, '@') MsgBox txtEnd Sub
将数组写入单元格区域
五、VBA运算符
1、算术运算符
用于算术运算,返回值类型为数值型。
2、比较运算符
用于比较运算
3、连接运算符
连接运算符用来连接两个文本字符串,有+和&两种
示例:
a='欢迎来到'
b='ExcelHome论坛!'
?a+b '问号?告诉VBA在立即窗口中显示问号后面命令的结果,可以用Print关键字代替问号。
欢迎来到ExcelHome论坛!
?a & b
欢迎来到ExcelHome论坛!
?4+5 '符号+两边都是数值,执行算术运算
9
?'4'+5 '其中5是数值,执行算术运算
9
?'4'+'5' '两个都是文本,执行连接运算
45
4、逻辑运算符
逻辑运算符用于判断逻辑运算式的真假,参与逻辑运算的数据为逻辑型数据,返回结果为Boolean型,只能为True或False。
5、VBA中的通配符
6、运算符优先级
在VBA中要优先处理蒜素运算符,接着处理连接运算符,然后处理比较运算符,最后再处理逻辑运算符,可以用括号来改变运算顺序。
7、换行符
VBA中字符换行显示需要使用换行符来完成。下面是常用的换行符 'chr(10) 可以生成换行符 'chr(13) 可以生成回车符 'vbcrlf 换行符和回车符 'vbCr 等同于chr(10) 'vblf 等同于chr(13)'例:Sub test3() MsgBox '我爱' & Chr(10) & 'Excel' ' MsgBox '我爱你' & Chr(13) & 'Excel' ' MsgBox '今天' & vbCrLf & '我是大王'End Sub
六、VBA内置函数
使用VBA中内置函数与在工作表中使用工作表函数类似。
例如,我们想知道当前系统时间
VBA中的内置函数有哪些?查看VBA内置函数的方法:
1、在VBE中“帮助(H)”——“Microsoft Visual Basic 帮助(H) F1”——“Visual Basic 语言参考”——“函数” 或者在VBE下快捷键“F1”
地址:https://docs.microsoft.com/zh-cn/office/vba/language/reference/functions-visual-basic-for-applications
2、在VBE代码窗口中首先键入“VBA.”系统会自动提示“函数列表”,如图
七、VBA控制结构
1、If…Then语句
Sub SayHello1() If Time < 0.5 Then MsgBox '早上好!' If Time >= 0.5 Then MsgBox '下午好!'End SubSub SayHello2() If Time < 0.5 Then MsgBox '早上好!' Else MsgBox '下午好!' End IfEnd SubSub SayHello3() If Time < 0.5 Then MsgBox '早上好!' ElseIf Time > 0.75 Then MsgBox '晚上好!' Else MsgBox '下午好!' End IfEnd Sub
2、Select Case语句
Sub xingji() Dim xj As String Select Case Cells(2, 'H') Case Is < 85 xj = '不评定' Case Is < 100 xj = '一星级' Case Is < 115 xj = '二星级' Case Is < 130 xj = '三星级' Case Is < 150 xj = '四星级' Case Else xj = '五星级' End Select Cells(2, 'I') = xjEnd Sub
3、For…Next语句
语法结构:
Sub xingji() Dim xj As String, i As Integer For i = 2 To 19 Step 1 Select Case Cells(i, 'H') Case Is < 85 xj = '不评定' Case Is < 100 xj = '一星级' Case Is < 115 xj = '二星级' Case Is < 130 xj = '三星级' Case Is < 150 xj = '四星级' Case Else xj = '五星级' End Select Cells(i, 'I') = xj Next iEnd Sub
4、Do While语句
对应示例:
Sub xingji() Dim xj As String, i As Integer i = 2 Do While Cells(i, 'H') <> '' Select Case Cells(i, 'H') Case Is < 85 xj = '不评定' Case Is < 100 xj = '一星级' Case Is < 115 xj = '二星级' Case Is < 130 xj = '三星级' Case Is < 150 xj = '四星级' Case Else xj = '五星级' End Select Cells(i, 'I') = xj i = i + 1 LoopEnd Sub
对应示例:
Sub xingji() Dim xj As String, i As Integer i = 2 Do Select Case Cells(i, 'H') Case Is < 85 xj = '不评定' Case Is < 100 xj = '一星级' Case Is < 115 xj = '二星级' Case Is < 130 xj = '三星级' Case Is < 150 xj = '四星级' Case Else xj = '五星级' End Select Cells(i, 'I') = xj i = i + 1 Loop While Cells(i, 'H') <> ''End Sub
5、Do Until语句
do Until 后的逻辑表达式为False则执行循环体,否则退出循环,跟Do While相反。
对应示例:
Sub xingji() Dim xj As String, i As Integer i = 2 Do Until Cells(i, 'H') = '' Select Case Cells(i, 'H') Case Is < 85 xj = '不评定' Case Is < 100 xj = '一星级' Case Is < 115 xj = '二星级' Case Is < 130 xj = '三星级' Case Is < 150 xj = '四星级' Case Else xj = '五星级' End Select Cells(i, 'I') = xj i = i + 1 LoopEnd Sub
对应示例:
Sub xingji() Dim xj As String, i As Integer i = 2 Do Select Case Cells(i, 'H') Case Is < 85 xj = '不评定' Case Is < 100 xj = '一星级' Case Is < 115 xj = '二星级' Case Is < 130 xj = '三星级' Case Is < 150 xj = '四星级' Case Else xj = '五星级' End Select Cells(i, 'I') = xj i = i + 1 Loop Until Cells(i, 'H') = ''End Sub
6、For Each…Next语句
当前活动工作簿中有许多工作表,但并不知道数量。如果要把所有工作表的名称按次序写入活动工作表的A列,For Each…Next是更适合的循环。
示例1:
Sub shtName() Dim sht As Worksheet, i As Integer i = 1 For Each sht In Worksheets Cells(i, 'A') = sht.Name i = i + 1 Next shtEnd Sub
示例2:
7、GoTo语句
“去到指定地点”,用来让程序转到另外一条语句去执行。
Sub he() Dim mysum As Long, i As String i = 1x: mysum = mysum + i i = i + 1 If i <= 100 Then GoTo x MsgBox '1到100的自然数和是:' & mysun End Sub
8、With语句
当需要对相同的对象进行多次操作时,会编写一些重复代码
八、过程Sub
1、Sub定义语句
声明sub过程的规范语句
'所有[]内容都是可选的'Exit Sub:可选语句,执行它将中断执行并退出过程'如果选用Static,运行程序的过程中将保存该过程里声明的本地变量'Private和Public用于声明过程的作用域名,如果省略,过程默认为公共过程[Private|Public][Static] Sub 过程名([参数列表]) [语句块] [Exit Sub] [语句块]End Sub
2、Sub间调用
被调用过程定义:
方法一:过程名[参数1,参数2…]
带参数调用
方法二:Call 过程名[(参数1,参数2…)]
方法三:利用Application对象的Run方法,Application.Run 表示过程名的字符串(或字符串变量)[,参数1,参数2…]
3、过程的作用域
公共过程:可以跨模块调用,用Public 或省略不写。
私有过程:只能在模块内调用,用Private
九、自定义Function
Function过程也称为函数过程,编写一个Function过程,就是编写一个函数。
1、定义语法格式
'最后必须将结果赋值给函数名称[Private|Public][Static] Function 函数名([参数列表])[As 数据类型] [语句块] [函数名=过程结果] [Exit Function] [语句块] [函数名=过程结果]End Function
2、定义函数
定义函数:生成1-10之间的随机整数
3、使用函数
4、函数实例
RGB函数中,R代表红色,G代表绿色,B代表蓝色
RGB(255,255,0) 表示黄色
'最后必须将结果赋值给函数名称Function CountColor() Dim rng As Range For Each rng In Range('A1:A10') If rng.Interior.Color = RGB(255, 255, 0) Then CountColor = CountColor + 1 End If Next rngEnd Function
'最后必须将结果赋值给函数名称Function CountColor(arr As Range, c As Range) Dim rng As Range For Each rng In arr If rng.Interior.Color = c.Interior.Color Then CountColor = CountColor + 1 End If Next rngEnd Function
工作表重新计算(按F9重新计算,或重启工作簿)之后,自定义函数并不会重新计算。
如果将自定义函数设置为易失性函数,无论何时重新计算工作表,函数都会重新计算。
使用命令:Application.Volatile True
十、代码美化
合理缩进:tab
取消缩进:Shift+Tab
更改长行代码为短行代码,在子句后面输入一个空格和一个下划线(_),然后换行,这就把一行代码分成两行。
Sub test() Application.Workbooks('Book1').Worksheets('sheet1') _ .Range('A1:D100').Font.Bold = TrueEnd Sub
把多行合并为一行,在第一行代码后加上英文冒号(:),可以接着写第二行代码
注释:用英文单引号(')表示后面的语句为注释
注释Rem:用Rem注释只能注释一整行,注释行不能有代码逻辑
Sub test() '=================注释 Application.Workbooks('Book1').Worksheets('sheet1') _ .Range('A1:D100').Font.Bold = True Rem ==============注释End Sub
批量注释:在VBE窗口中,“视图”——“工具”——“编辑”——“设置注释块” 【或解除注释块】
十一、VBE其他设置
自动列出成员设置:VBE下“工具”——“选项”——“编辑器”选项卡下——“自动列出成员”
联系客服