1. 数据类型
变量定义:Dim 变量名 As 数据类型
变量赋值:变量名 = 值
1.1 数值型
1.1.1 整数
(1) Byte:1字节(0~255)
(2) Integer:2字节(-32768~32767)
(3) Long:4字节(-2147483648~2147483647)
1.1.2 小数
(1) Single:7位有效整数加小数 // 整数为0可含7位小数,第8位四舍五入
(2) Double:15位有效整数加小数 // 第16位四舍五入
1.2 字符型
(1) String: // 变量名 = "abc"
1.3 日期型
(1) Date: // Dim d As Date // d = Date 系统日期, d = Time 系统时间
1.4 数组
(1) 一维数组:Dim 数组名(开始下标含 To 终止下标含) As 数据类型 // Dim arr(1 To 5) As Byte
(2) 二位数组:Dim 数组名(下标 To 下标, 下标 To 下标) As 数据类型 // Dim arr(0 To 3, 0 To 2) As ..
(3) 动态数组:Dim 数组名() As 数据类型 // ReDim 数组名(下标 To 变量)
注释:数组赋值 数组(下标)=值,数组取值 数组(下标),开始下标随意指定可以是0也可是1
2. 运算符
(1) 算术运算符:加( + ),减( - ),乘( * ),除( / ),余( Mod ) // 5 Mod 2
(2) 赋值运算符:= // vba不识别 += 运算
(3) 比较运算符:等( = ),不等( <> ),大于( > ),大等( >= ),小于( < ),小等( <= )
注释:Like // 变量 like "abc" ("a*" 多字符,"a?"单字符,"a#"单数字),Is // Range("A1") Is Range("B1")
(4) 逻辑运算符:And,Or,Not
3. 流程控制
3.1 分歧
(1) If分歧:If 条件 Then ... [ElseIf 条件 Then ...] [Else ...] End If
(2) Select分歧:Select Case 变量 Case 变量 = 值 ... [...] Case Else ... End Select
注释:select case 变量 case is > 值 (用is代替变量)
3.2 循环
(1) for 循环:For 变量 = 初值 To 终值 [step 1]... Next 变量 // For i = 0 To 5 ... Next i
(2) for each 循环:For Each 变量 In 区域 ... Next [变量]// For Each i in Range("A1:B2") ... Next
(3) while 循环:Do While 条件 ... 变量=变量+1 Loop // 先判断后循环
(4) do while 循环:Do ... 变量=变量+1 Loop While 条件 // 先处理后判断
注释:退出循环 Exit For, Exit Do
4. 函数
注释:内置函数用 (vba.函数名)
4.1 VBA函数
4.1.1 判断函数
(1) 数值判断:VBA.IsNumeric(变量) // 判断变量内容是否为数值,可判断字符串
(2) 判断初始化:VBA.IsEmpty(变量) // 判断变量是否初始化(Dim定义或赋值都为False)
(3) 判断空变量:VBA.IsNull(变量) // 判断变量的值是否为Null
4.1.2 数值函数
略
4.1.3 字符函数
(1) 字符串拼接(+):"a"+"b"="ab" // 符号两侧与变量用空格分割,1+1=2,"1"+1=2,1+"1"=2,"1"+"1"=2
(2) 字符串拼接(&):"a"+"b"="ab" 推荐使用 // 符号两侧与变量用空格分割,1+1="11","1"+1="11",1+"1"="11","1"+"1"="11"
(3) 取字符长度:Len(变量) // 获取字符长度 Len("a啊") = 2
(4) 截取字符:Left(变量, 长度) | Right(变量, 长度) // 从一侧获取指定长度的字符串
4.1.4 日期函数
(1) 系统日期:Date // "2024/3/9",Date + 1 = 加1天,
(2) 系统时间:Time // "23:48:32"
(3) 系统日期时间:Now // "2024/3/9 23:48:32",Now + 1 = 加1天,
4.1.5 函数定义与调用
(1) 无返回值函数:Sub 函数名([ByVal a As 类型, ...]) ... End Sub // 参数(ByVal a As String)
(2) Sub调用:Call 函数名([参数])
(3) 有返回值函数:Function 函数名([ByVal a As 类型]) As 返回值类型 ... End Function
(4) Function调用:变量 = 函数名([参数]) // 无参函数 函数名 或 函数名() 都识别
注释:function 返回值设定,函数名同名变量=值
4.2 Excel函数
注释:公式包含函数
4.2.1 运算符
(1) 算术运算符:加(+) 减(-) 乘(*) 除(/) 余(%) 阶乘(^)
(2) 比较运算符:=, <>, >, >=, <, <=
(3) 字符串拼接:&
4.2.2 单元格引用
(1) 相对引用:A1 // 下拖=>A2,A3.. 右拖=>B1,C1..
(2) 相对引用(固定行):A$1 // 1固定,拖动公式,下拖=>A1,A1.. 右拖=>B1,C1..
(3) 相对引用(固定列):$A1 // A固定,拖动公式,下拖=>A2,A3.. 右拖=>A1,A1..
(4) 绝对引用:$A$1 // 都固定,拖动公式,下拖=>A1,A1.. 右拖=>A1,A1..
4.2.3 判断函数
(1) if(表达式, 真值, 假值)
(2) iferror(函数, 出错误返回值) // 解决,空字符串做算术运算,截取字符错误 等问题
(3) AND(表达式, 表达式, 最多30个...) // 表达式全为真,返回true
(4) OR(表达式, 表达式, 最多255个) // 只要一个表达式为真,返回true
(5) NOT(表达式) // 取反
4.2.4 字符函数
(1) 截取:LEFT(文本, 长度), RITHE(文本, 长度), Mid(文本, 1开始位置, 长度)
(2) 取长:LEN(文本) // 字符长度(汉字占1)
(3) 大小写转换:Lower(大写文本), Upper(小写文本)
(4) 内容替换文本:Substitute(目标文本, 旧字符, 新字符 [,出现位置默认全替换])
(5) 位置替换文本:Replace(目标文本, 1开始位置, 长度, 新字符)
(6) 精确查找:Find(查询字符, 目标文本, 1开始位置) //得到1开始的位置,区分大小写
(7) 模糊查找:Search(查询字符*, 目标文本, 1开始位置) // 不区分大小写,支持通配符
(8) 去除空格:Trim(文本) // 去除首尾全部空格,并将中间连续空格合并成1个
4.2.5 关联内容查找
(1) Lookup(对象, 目标区域, 结果区域) //目标与结果需1,1对应,对象大于目标取最大是,小于目标错误
(2) VLookup(对象, 含结果目标区域, 结果在区域中列号, 是否模糊查找) // 上下查,目标必须首列
(3) Hlookup 同上,从左到右查找
4.2.6 其它
(1) 数值求和:Sum(区域) Value(区域) // sum只能计算数值,value可计算文本单元格
(2) 行列数:Column(区域)取列号可与vlookup联合动态计算列 Columns(列数),Row(.) Rows(..)
4.3 正则表达式
Dim reg as Object
set reg = CreateObject("VBScript.RegExp")
with reg
.Global=True '全部匹配
.ignorecase = True '不区分大小写
.Pattern = "正则"
set mc = .Execute("匹配源文本")
xx=mc(0) '取第一个匹配结果
for each item in mc '多结果获取
'…
next item
end with
5. 宏录制
简单操作可通过录制宏完成
点击【录制宏】,执行拖动,光标移动,复制粘贴等操作,【结束录制 】。
注释:ctrl+上下做有内容的光标移动,当上下左右移动光标时,需打开【使用相对引用】使相对于当前光标做上下左右,而不是直接跳转到指定单元格。
点击【宏】,选取好光标位置后,执行宏命令。
6. 文件
脚本打开本地excel文件
Sub test()
Dim OpenFilename As String
OpenFilename = Application.GetOpenFilename("MicrosoftExcelbook, *.xlsx")
If OpenFilename <> "False" Then
On Error Resume Next
Workbooks.Open OpenFilename
On Error GoTo 0
End If
End Sub
7. 工作簿
工作簿 》 工作表 》多单元格(行,列,区域)》 单元格
7.1 工作簿
7.1.1 工作簿操作
(1) 选中工作簿:Workbooks("xx.xlsx").Worksheets("xx").Range("A1") //指定工作簿区域取值赋值
(2) 保存工作簿:ActiveWorkbook.Save // If Not ActiveWorkbook.Saved Then ActiveWorkbook.Save End If
7.1.2 工作簿事件
打开工作簿(Open),添加工作表(NewSheet),删除工作表(SheetBeforeDelete),选取工作表(SheetActivate),内容输入(BeforePrint),双击工作表(SheetBeforeDoubleClick),右击工作表(SheetBeforeRightClick),保存(BeforeSave),关闭(BeforeClose)
7.2 工作表
注释:默认当前工作簿,也可指定工作簿 Workbooks("xx.xlsx").
7.2.1 工作表操作
(1) 选中工作表:Worksheets("sheet名" | 1开始下标).Select // 使工作表激活
(2) 激活工作表:Worksheets("xx").Activate // 可操作隐藏工作表
(3) 添加工作表:Worksheets.add // 添加到开头
(3) 设置打印区:Worksheets("xx").PageSetup.PrintArea = "A1:B15"
7.2.2 工作表事件
删除工作表(BeforeDelete)选取工作表(Activate),双击工作表(BeforeDoubleClick),右击工作表(BeforeRightClick),单元格变更(Change),选择区域变化(SelectionChange)
7.3 单元格
注释:默认当前工作表,也可指定工作表 Worksheets("名").
7.3.1 区域
(1) 连续区域选中:Range("A1:C3").Select // 选中A1到C3所有单元格
(2) 间断区域选中:Range("A1,C3").Select // 选中A1与C3单元格,Range("A1").Select 单选
(3) 光标移动:Range("A1").End(xlUp).Select // xlUp 最上(ctrl+↑),xlDown 最下,xlToLeft 最左,xlRight 最右
(4) 区域光标偏移:Range("C1:D2").Offset(1, 1).Select // 向下向左偏移,负数向上向右
(5) 复制单元格: r1.copy r2 // r1=Range("A1:B15") r2=Range("A16:B31") ,清除Application.CutCopyMode=false
注释:worksheet(xx).[A1:B15] 也可获取区域
7.3.2 行
(1) 全工作表行选中:Rows.Select
(2) 多行选中:Rows("3:5").Select // 选中第3行到第5行
(3) 单行选中:Rows(2).Select // 指定行号。Rows("3:4").Rows(1).Select 选中多行中的第几行
(4) 获取行号:Rows(Rows.Count).End(xlUp).Row // 首列由下到上,第一个有值单元格行号
(5) 删除行:Rows(3).delete // 下边行上移
7.3.3 列
(1) 全工作表列选中:Columns.Select
(2) 多列选中:Columns("D:F").Select // 选中D列到F列
(3) 单列选中:Columns("D" | 2).Select // 指定列名 或 列号选中
(4) 获取列号:Columns(Columns.Count).End(xlToLeft).Column // 获取数值
(5) 删除列: Columns(3).delete // 右边列左移
7.3.4 单元格
(1) 选中单元格:Cells(行数, 列数).Select // Cells(1,1).Select 选中1行1列
(2) 选中单元格:Cells(行数, "列字母").Select // Cells(1, "B").Select 选中1行B列
(3) 选中单元格:[B2].Select // 选中指定名字的单元格
(4) 获取行号或列号:Cells(1,1).Row | Cells(1,1).Column
7.3.5 通用操作
对象:Range,Rows,Columns,Cells
(1) 单元格赋值:对象.Value = 值 // 直接单元格的值,光标不变
(2) 自动调整行高:对象.EntireRow.AutoFit
(3) 自动调整列宽:对象.EntireColumn.AutoFit
7.3.6 单元格样式
对象:Range,Rows,Columns,Cells
(1) 获取对象:Set 变量 = Range("A2:D3") | Rows(1) | Columns(1) | Cells(1,1)
(2) 合并单元格:变量 .Merge
(3) 对齐:变量 .HorizontalAlignment = xlCenter // 居中,xlLeft, xlRight
(3) 字号:变量 .Font.size = 值
(5) 字体颜色:变量 .Font.ColorIndex = 1 // 黑色(1)
(6) 字体加粗:变量 .Font.Blod = True
(7) 背景色:变量 .Interior.ColorIndex = 1 // 黑色(1)
(8) 边框:变量.Borders.LineStyle = True
8. 窗体
8.1 窗体
(1) 显示窗体:窗口名.Show
8.2 控件
8.2.1 标签(Label)
(1) 标签文本:Label名.Caption = "文本内容"
(2) 标签显示:Label名.Visible = True // True显示, False不显示
(3) 指向提示:Label名.ControlTipText
注释:背景色(BackColor),字体(Font),字体颜色(ForeColor) ...
事件:Click,DblClick,MouseDown,MouseUp,MouseMove
8.2.2 文本框(TextBox)
(1) 文本框内容:TextBox名.Value = "内容" | TextBox名.Text = "内容"
(2) 文本框显示:TextBox名.Visible = True
(3) 文本框活性:TextBox名.Enabled = True // True 可入力,False不可入力
注释:背景色(BackColor),字体(Font),字体颜色(ForeColor) ...
事件:Change,DblClick,MouseDown,MouseUp,MouseMove,KeyDown,KeyUp,KeyPress
8.2.3 下拉框(ComboBox)
(1) 文本框取值:ComboBox名.Value // 获取当前选择的值
(2) 文本框添加项目:ComboBox名.AddItem 值
(3) 清空文本框:ComboBox名.Clear
注释:显示(Visible),活性(Enabled),字体(Font),字体颜色(ForeColor) ...
事件:Change,Click,DblClick,MouseDown,MouseUp,MouseMove,KeyDown,KeyUp,KeyPress
8.2.4 列表框(ListBox)
(1) 列表框取值:ListBox名.Value // 获取当前选择的值
(2) 列表框添加项目:ListBox名.AddItem 值 [, 0开始索引]
(3) 列表框添加项目:ListBox名.RemoveItem 索引
(4) 获取选中状态:ListBox名.Selected(0开始下标) // True选中,False未选中,赋值改变状态
(5) 获取总件数:ListBox名.ListCount
(6) 清空列表框:ListBox名.Clear
注释:判断选中是否有选中项 if list.listindex <> -1
注释:循环 listbox.list(i)
注释:显示(Visible),活性(Enabled),字体(Font),字体颜色(ForeColor) ...
事件:Change,Click,DblClick,MouseDown,MouseUp,MouseMove,KeyDown,KeyUp,KeyPress
8.2.5 复选框(CheckBox)
分组名:GroupName相同为一组
(1) 列表框取值:CheckBox名.Value // 获取当前复选框是否选中
(2) 列表框赋值:CheckBox名.Value = True // True选中,False未选中
(3) 文本信息:CheckBox名.Caption
注释:显示(Visible),活性(Enabled),字体(Font),字体颜色(ForeColor) ...
事件:Change,Click,DblClick,MouseDown,MouseUp,MouseMove,KeyDown,KeyUp,KeyPress
8.2.6 单选框(OptionButton)
分组名:GroupName相同为一组
(1) 列表框取值:OptionButton名.Value // 获取当前复选框是否选中
(2) 列表框赋值:OptionButton名.Value = True // True选中,False未选中
(3) 文本信息:OptionButton名.Caption
注释:显示(Visible),活性(Enabled),字体(Font),字体颜色(ForeColor) ...
事件:Change,Click,DblClick,MouseDown,MouseUp,MouseMove,KeyDown,KeyUp,KeyPress
8.2.7 按钮(CommandButton)
(1) 按钮显示名:CommandButton名.Caption
注释:显示(Visible),活性(Enabled),字体(Font),字体颜色(ForeColor) ...
事件:Change,Click,DblClick,MouseDown,MouseUp,MouseMove,KeyDown,KeyUp,KeyPress
8.2.8 框架(Frame)
(1) 框架标题:Frame名.Caption
注释:显示(Visible),活性(Enabled),字体(Font),字体颜色(ForeColor) ...
8.2.9 其它
(1) 图片:Image
(2) 多页:Page // 多个Tag页
9. 开发环境
调出菜单:打开Excel > 文件 > 选项 > 自定义功能区 > 勾选右侧【开发工具】选项
开发工具:Excel菜单栏 > 开发工具 > 点击【Visual Basic】
注释:'注释内容 // 单引号后为注释内容
控制台输出:Debug.Print log内容 // ctrl+G或窗口(i)打开 控制台
弹窗信息:MsgBox "弹窗信息"
输入窗口:变量 = InputBox("提示信息", "窗口标题", "入力默认值", 显示器x坐标, 显示器y坐标)
保存vba文件:excel需另存为xlsm后缀文件