VBA（学习笔记）

大家好，欢迎来到IT知识分享网。

1. 数据类型

变量定义：Dim 变量名 As 数据类型

变量赋值：变量名 = 值

1.1 数值型

1.1.1 整数

(1) Byte：1字节（0~255）

(2) Integer：2字节（-32768~32767）

(3) Long：4字节（-~）

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(文本, 长度), RIGHT(文本, 长度), 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

6.1 读写Excel文件

Dim target as Workbook '打开目标文件 set target = Workbooks.Open("c:\xx.xlsx") '读取或更改目标文件 target.Worksheets("xx")... '保存关闭文件 target.close() True

6.2 Input,Output读写Txt文件

6.2.1 读文件 

set os= CreateObject("ADODB.Stream") os.Charset = "utf-8" os.Open os.LoadFromFile("c:\xx.txt") '方式1：读取文件全部内容 fileText = os.ReadText() args = Split(fileText, Chr(10)) '按换行符拆分文本 '方式2：按行读取 Open("c:\xx.txt") for Input as #1 '默认读Ansi编码，其它编码可能会乱码 Do until EOF(1) '循环读取每行，直到结尾 Line Input #1, rowStr '从文件读取一行存放到rowStr中 Loop Close #1 '关闭文件流 

 6.2.2 写文件

'覆盖原文件内容 Open("c:\xx.txt") for Output as #1 Print #1, "str" '清空文件，每次换行追加内容。 Close #1 '追加数据 Open("c:\xx.txt") for Append as #1 Print #1, "str" '换行在末尾追加内容 Close #1

6.3 ADODB读写文件

Dim ado as New Adodb.stream ado.open ado.Type=2 '1二进制读写，2文本读写 ado.Charset="utf-8" ado.LoadFromFile "c:\xx.txt" '读数据 fileText=ado.readText '读取文件内容 '写数据 ado.Position=ado.Size '使光标移动到末尾，在文件末尾追加内容 ado.WriteText(vbCrLf) '追加换行 ado.WriteText("xxx") '不换行直接追加内容 ado.SaveToFile "c:\xx.txt", 2 '2为覆盖原文件保存 ado.close

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后缀文件