Excel vba基础

笔记涉及课程地址为VBA是个什么玩意_哔哩哔哩_bilibili

一、框架

Sub def_name() '自定义子过程名称
    Msgbox "hello world!"
End Sub '框架

二、变量与单元格输入

Sub def_name()
    n = 1
    [A1] = n
End Sub

1、变量的数据类型

数据类型	存储空间	范围	简写
byte	1个字节	0~255
integer	2个字节	-32768~32767	%
long	4个字节	-2147483648~2147483647	&
string	10个字节加字符串长度	0~大约20亿	$
date	8个字节	100年1月1日~9999年12月31日
variant	16个字节或22个字节

2、声明变量格式

sub def_name()
    dim 变量名 as 数据类型
    dim n as integer '声明变量格式
    dim n% '简写

    '声明多个变量
    dim n%, y$, x&
end sub

三、对象（工作簿、表、单元格）

工作簿	工作表		单元格
Workbooks("工作簿名")	Sheets(n) 第n个工作表	按工作表的顺序	Range("单元格地址")
ThisWorkbook 代码所在工作簿	Sheetn 第n个工作表	按系统工作表名	Cells(行, 列)
ActiveWorkbook 活动工作簿	Sheets("工作表名")	按工作表名	[A1] 单元格简写
	ActiveSheet 活动工作表		Activecell 活动单元格

sub 单元格赋值()
    range("A1") = 1
    cells(1, 1) = 1
    [A1] = 1

    '多单元格区域
    range("A1:B2") = 1

    'set 用于赋值为单元格区域而非区域内值的数组
    dim s as range
    set s = range("A1:B2")
end sub

四、属性

sub 常用属性()
    '工作簿位置
    workbooks("xxx.xlsm").path

    '工作表数量
    sheets.count

    '工作表名称
    sheets(1).name
    sheets(1).name = "重命名工作表"

    '单元格值
    range("A1").value

    '单元格位置
    Activecell.address '返回：A1

    '单元格颜色
    range("A1").interior.colorindex
end sub

语句格式：父对象.子对象.属性：

ThisWorkbook.Sheet(3).Range("A1").interior.colorindex

五、动作

sub actions()
    '添加工作簿
    dim wb as workbook
    set wb = workbooks.add

    '保存工作簿
    wb.saveas Thisworkbook.path & "\" & "工作簿名称"

    '打开工作簿
    workbooks.open("/Users/ChenLW/Documents/行云/数据/Database.xlsx")

    '关闭工作簿
    Activeworkbook.close

    '添加工作表（命名）
    worksheets.add.name = "自定义sheet名称"
    worksheets.add(after:=sheets(sheets.count)) '向后添加工作表

    '复制工作表
    Thisworkbook.sheet("Sheet1").copy Activeworkbook.sheets(1)

    '指定活动单元格
    range("A1").Activate

    '复制单元格
    range("A1").copy [A2]
    
    range("A1").copy
    range("A2").pastespecial xlpastevalues'只粘贴值

    '删除单元格
    range("A1").delete

    '清除单元格值
    range("A1").clear
    range("A1").clearcontents '只清除值

    '移动单元格
    range("A1").cut [A2]
end sub

六、if语句

sub test()
    dim n%
    if n>1 then
        msgbox"n>1"
    elseif n<1 then
        msgbox"n<1"
    else
        msgbox"n=1"
    end if
end sub

七、循环

1、for循环

sub test()
    dim n%
    for n = 2 to 9 step 4 '左右均包含、步长为4
        for y = 1 to 8
            pass
        next y
    next n
end sub

• • 语法：for循环的语法比foreach循环更加灵活，可以控制循环的起始值、终止值和步长等参数。而foreach循环的语法比较简单，只需要指定要遍历的集合或数组即可。
  • 性能：在遍历数组时，for循环的性能比foreach循环更好，因为for循环直接使用数组的索引来访问元素，而foreach循环需要先获取迭代器，然后再通过迭代器来访问元素。但在遍历集合时，foreach循环的性能比for循环更好，因为集合的元素数量可能会动态变化，而foreach循环可以自动适应集合的变化。
  • 可读性：foreach循环比for循环更易于理解和阅读，因为它的语法更加简洁明了，不需要关注循环的起始值、终止值和步长等参数。

sub test()
    '已打开工作簿循环
    dim n as workbook
    for each n in workbooks
        pass
    next
    
    '工作表循环
    dim n as worksheets
    for each n in worksheets
        pass
    next

    '单元格区域循环
    dim n as range
    for each n in range("A1:A10")
        pass
    next

end sub

2、do loop循环

sub test()
    do
        pass
    loop
end sub

sub test()
    '条件成立进入循环
    do while i > 1
        pass
    loop

    '条件成立退出循环
    do until i = 1
        pass
    loop
end sub

八、数据边界（行列）

行列编号从1开始

sub test()
    x = range("A1").end(xltoright).column '获取列边界
    h = range("A1").end(xldown).row '获取行边界
end sub

end(xlup)	上边界
end(xldown)	下边界
end(xltoleft)	左边界
end(xltoright)	右边界
rows.count	获取最大行号
columns.count	获取最大列号
rows("1:10")	选择多行
range("A1:A10").rows("1")	选择制定区域的某行

sub test()
    x = cells(1, columns.count).end(xltoleft).column '获取列边界
    h = cells(rows.count, "f").end(xlup).row  '获取行边界
end sub

sub test()
    h = worksheets(1).usedrange.rows.count
    x = worksheets(1).usedrange.columns.count
end sub

sub test()
    h = range("A1").currentregion.rows.count
    x = range("A1").currentregion.columns.count
end sub

九、偏移、区域调整

sub test()
    '上负，下正，左负，右正
    range("A1").offset(8,4) '索引值均从0开始
    range("A1")(8,4) '索引值均从1开始
end sub

sub test()
    range("A1").resize(4,5) '索引从1开始
    range("A1","B2").resize(4,5) '区域范围以左上单元格（A1）为起点 
end sub

十、结束语句

sub test()
    '不同语句结束写法不同
    exit do
    exit for
    exit sub '结束整代码段
end sub

十一、跳转语句、报错判断

sub test()
    on error resume next '当代码报错时继续运行

    if err.number <> 0 then goto xx 'err.number 报错代码为0为正确，其他均为错误
        pass
    xx:
        pass
    err.clear '错误代码需重置，否则会记忆上次值
end sub

十二、工作表函数

sub test()
    '可以使用表格中的函数：sum、average等
    worksheetfunction.sumif()
end sub

十三、随机数函数

返回一个小于1但大于或等于0的数值

sub test()
    '指定区间随机数
    int((最大值 - 最小值 + 1) * rnd + 最小值)
end sub

十四、排序

sub test()
    ' 语法：
    ' Key最多3个，最少1个，用range("A1")表示
    ' order1:=1 升序，order1:=2 降序
    ' Header:=1 有表头，Header:=2 无表头
    ' Type 指定排序元素，透视表适用，非透视表可以填空
    sort(Key1, order1, Key2, Type, order2, Key3, order3, Header,
        Ordercustom, Matchcase, orentation, sortmethod,
        dataoption1, dataoption2, dataoption3)

    '示例
    range("A1:B2").sort range("a1"),1,range("B1"), ,2, header:=1
end sub

十五、查找、替换

1、查找

sub test()
    ' 语法:
    ' what 必选，查找内容
    ' after 可选，在什么之后查找
    ' lookin 可选，xlformulas 查找公式内内容；xlvalues 查找值；xlcomments 查找批注
    ' lookat 可选，xlwhole/1 精确查找，xlpart/2 模糊查找
    ' searchorder 可选，xlbyrows/1 按行查找；xlbycolumns/2 按列查找
    ' searchdirection 可选，xlnext/1 向下查找（默认）；xlprevious/2 向上查找
    ' matchbyte 可选，ture 区分大小写；false 不区分大小写（默认）
    
    find(what, after, lookin, lookat, searchorder, searchdirection, 
        matchcase, matchbyte, searchformat)
    
    ' 示例
    ' 查找hello，在A3之后开始，*为通配符可模糊查找
    range("A1:B2").find("*hello*", range("A3"), xlvalues, 1)
end sub

sub test()
    range("区域").findnext(某单元格之后) '适用于连续查找
end sub

2、替换

sub test()
    ' 语法：
    ' what 必选，替换搜索字符
    ' replacement 必选，替换字符
    ' lookat 可选，xlwhole或xlpart
    ' searchorder 可选，xlbyrows或xlbycolumns
    ' matchcase 可选，区分大小写true或false
    range().replace(what, replacement, lookat, searchorder, 
        matchcase)
end sub

十六、筛选、删除重复项

1、数据筛选

sub test()
    ' 语法：（只列举4个）
    ' field 筛选的列，索引从1开始
    ' criteria1 条件1
    ' operator 指定筛选类型：
    '  xland criteria1和criteria2并且关系
    '  xlor criteria1和criteria2或者关系
    '  xltop10items 显示最大值的项
    '  xlbottom10items 显示最小值的项
    '  xltop10percent 显示指定最大xx%的项
    '  xlbottom10percent 显示指定最小xx%的项
    '  xlfiltervalues 筛选值，配合array多条件筛选 (range("A1").autofilter 2,array("值内容"), xlfiltervalues)
    '  xlfiltercellcolor 单元格颜色
    '  xlfilterfontcolor 字体颜色
    '  xlfiltericon 筛选图标
    '  xlfilterdynamic 动态筛选
    ' visibledropdown 筛选字段是否显示下拉箭头，true(默认)/false
    autofilter(field, criteria1, operator, criteria2, 
        subfield, visibledropdown)

    '示例：range为表任一单元格即可，第二列，
    range("A1").autofilter 2, ">10", xland, "<20"

    '关闭筛选状态
    sheet1.autofiltermode = false

    '颜色计算逻辑
    y = range("A1").interior.color
    red = y mod 256
    gre = y \ 256 mod 256
    blu = y \ 256^2 mod 256
    range("A1").autofilter 2, RGB(red, gre, blu), xlfiltercellcolor

    '动态筛选
    range("A1").autofilter 2, xlFilterAllDatesInPeriodJanuary, xlfilterdynamic '月份筛选
    range("A1").autofilter 2, xlFilterAboveAverage, xlfilterdynamic '筛选高于平均值的值
end sub

2、删除重复项

sub test()
    ' 删除A列重复项
    range("A:A").removeduplicates columns:=1, header:=xlNo
end sub

十七、并集、交集

sub test()
    '并集两个单元格
    s = union(cell(1,2), cell(1,1))
    '可以通过迭代实现集合累加
    s = union(s, cell(1,1))
end sub

sub test()
    '两个区域重复的区域
    intersect(range("A1:C3"),range("B1:C2"))
end sub

十八、条件定位

' type简写：
' 2，含有常量的单元格
' -4123，含有公式的单元格
' -4144，含有批注的单元格
' 4，空单元格
' 12，所有可见单元格
' 11，所用区域中的最后一个单元格
' -4172，任意格式的单元格
' -4174，含有验证条件的单元格
' -4173，含有相同格式的单元格
' -4175，含有相同验证条件的单元格

' value 可选，如果type=2、-4123，则用于确定包含哪些类
' 16，有错误的单元格
' 4，有逻辑值的单元格
' 1，有数值的单元格
' 2，有文本的单元格

sub test()
    Range('要定位的区域').SpecialCells(Type,Value)
end sub

十九、自动填充

sub test()
    '语法：destination 填充区域，且必须包含源单元格；type 填充类型
    range().autofill(destination, type)

    '示例
    range("A1").autofill range("A1:A5")
end sub

二十、with语句

对单个对象或用户定义类型执行一系列语句。即可以简化代码输入方式。

sub test()
    with range("A1:A2")
        .borders(xlinsidevertical).linestyle = xlcontinuous '列黑框
        .borders(xlinsidehorizontal).linstyle = xlcontinuous '行黑框
        .borders(xledgeleft).linestyle = 
    end with
end sub

二十一、遍历文件

sub test()
    dim loc$
    loc = dir("/Users/ChenLW/Desktop/")
    '只找docx文件
    loc = dir("/Users/ChenLW/Desktop/*.docx")
    '显示内层文件夹
    loc = dir("/Users/ChenLW/Desktop/", vbdirectory)
    do
        n = n+1
        '遍历文件
        cells(n, 1) = loc
        loc = dir
    loop until loc=""
end sub

二十二、超链接

sub test()
    ' 语法:
    ' anchor 必选，超链接定位标记，可为range或shape
    ' address 必选，超链接地址
    ' subaddress 可选，超链接子地址
    ' sreentip 可选，显示提示
    ' texttodisplay 可选，显示字符，默认为地址
    sheet.hyperlinks.add(anchor, address, subaddress, sreentip, 
        texttodisplay)
    
    '示例
    sheet.hyperlinks.add range("A1"), "/Users/ChenLW/Desktop/*.docx", "sheet1!a1", "xx文件"
    
end sub

二十三、字符索引值

sub test()
    '语法
    instr("起始位"(可选), "搜索内容", "字符串")
    
    ' 索引值从1开始
    a = "hello world!"
    b = instr(a,"e")

    ' 倒序查找
    a = "helle"
    b = instrrev(a,"e") '找到最后一个e
end sub

二十四、like比较

sub test()
    '语法
    ' pattern: ? 单一字符；* 零个或多个字符；# 任一数字；
    ' [charlist] charlist中任一字符；[!charlist] 不在charlist中任一字符
    result = sting like pattern
    
    '示例
    result = "123" like "???" 'result返回True或false
    result = "1个苹果" like "#*"
    result = "1个苹果" like "#[个只]*"
    
end sub

二十五、name语句

sub test()
    ' 移动并重命名
    name "/Users/ChenLW/Desktop/xx.xlsx" as "/Users/ChenLW/other/123.xlsx"
end sub

二十六、mkdir语句

sub test()
    ' 可新建内层文件夹
    mkdir "/Users/ChenLW/Desktop/文件夹1/文件夹2"
end sub

二十七、数组

1、数组

sub test()
    ' 声明3行6列数组
    dim arr(1 to 3, 1 to 6)
    
    ' 索引从1开始
    ' 横排
    arr = [{1,2,3}] 'arr(1) 返回 1
    ' 竖排
    arr = [{1;2;3}]
    ' 多行多列
    arr = [{1,2,3;4,5,6}]

    ' 更改数据
    arr(1) = 3
end sub

2、数组边界

sub test()
    dim arr(1 to 5, 2 to 6)
    
    a = ubound(arr, 1) '第一维上界，即最大下标，返回5

    b = lbound(arr, 2) '第二维下界，即最大下标，返回2

end sub

3、数组筛选

sub test()
    '语法
    'sourcearray 必选，待筛查的数组（一维）
    'match 必选，查找字符串
    'include 可选，布尔值，默认为true包含，false不包含
    'compare 可选，字符查找对比方式，默认为0区分大小写，1不区分
    filter(sourcearray, match, include, compare)

    '示例
    arr = Array("a", "ab", "c")
    brr = filter(arr, "a") '返回 "a", "ab"
end sub

4、清空数组

sub test()
    arr = [{1,2,3}]
    erase arr '清空数组
end sub

5、去除空值

sub test()
    arr = [a1:a10]
    arr = split(application.trim(join(application.transpose(ar))))
    '
    '逻辑拆解
    '转置transpose为数组-join为一个字符串-trim去除空值，保留每个字符之间一个空值-对空值split分离
end sub

6、去除重复项

sub test()
    on error resume next '存在错误将继续执行
    dim brr() '收集无重复值的数组
    arr = [a1:a10]v '需去重区域
    redim brr(1 to ubound(arr))
    for i lbound(arr) to ubound(arr)
        n = worksheetfunction.match(arr(i, 1), brr, 0) '在之前的去重区域找是否有相同项
        if n = "" then
            x = x + 1
            brr(x) = arr(i, 1)
        end if
        n = ""
    next i
end sub

二十八、组合、拆分

1、拆分函数

sub test()
    ' 语法
    ' expression 必选，需拆分的字符串
    ' delimiter 可选，分隔符，默认为空格
    ' limit 可选，返回字符串数量（如有多个可选前n个拆分）
    ' compare 可选，区分大小写，0区分（默认）；1不区分
    split(expression, delimiter, limit, compare)

    '示例
    a = "hello,world!"
    result = split(a,",")
end sub

2、组合函数

sub test()
    arr = array(1,2,3)
    join(arr) '返回"1 2 3"，默认空格间隔

    join(arr, ",")'返回"1,2,3"

end sub

二十九、字典

1、绑定字典库

sub test()
    set d = createobject("scripting.dictionary")
end sub

2、方法

sub test()
    ' key不能重复
    dim d as new dictionary
    d.add "key1", "value1"
    d.add "key2", "value2"
end sub

sub test()
    m = d("key1") 'm返回value1

    '利用arr
    arr = d.items
    m = arr(0) 'm返回value1

    arr = d.keys
    n = arr(0) 'm返回key1
end sub

sub test()
    n = d.exists("key1") 'n返回True
end sub

sub test()
    d.remove("key1")

    d.removeall '全部清空
end sub

3、属性

sub test()
    d.item("key1") '返回value1

    '修改value
    d("key1")="value3"
end sub

sub test()
    '修改key
    d.key("key1")="key3"
end sub

sub test()
    n = d.count 'n返回key数量
end sub

sub test()
    d.comparemode = 0 '是否区分大小写，0为区分，1为不区分
    d.add "key1", "value1"
    d.add "KEY1", "VALUE1"
end sub

4、应用（分类汇总）

sub test()
    set d = createobject("scripting.dictionary")
    arr = range("a1").currentregion '需汇总区域
    for i = 2 to ubound(arr)
        d(arr(i, 1)) = d(arr(i, 1)) + arr(i, 2) '1列为名称，2列为值
    next i
    '导出结果
    [c1:c5] = worksheetfuntion.transpose(d.keys)
    [d1:d5] = worksheetfuntion.transpose(d.items)
end sub

三十、正则表达式

1、代码格式

sub test()
    dim b, n
    set re = createobject("vbscript.regexp") '绑定正则库
    with re
        .Global = True 'True全局匹配（如有多个结果），False局部匹配
        .Pattern = "\d+" '正则表达式
        set b = .execute(range("a1")) '正则匹配
            for each c in b
                n = n + 1
                a.offset(0, n) = c
                a.offset(1, n) = .replace(a, "") '将匹配到的部分替换
            next c
    end with
        
    '分组匹配
    with re
        .Global = True
        .Pattern = "(\d{4}-\d{2}-\d{2})-(\d{2})-(\d{2})-(\d{2})" '时间格式，分组数据用()
        set b = .execute(range("a1"))
            年月日 = b(0).submatches(0) '(\d{4}-\d{2}-\d{2})
            时 = b(0).submatches(1) '(\d{2})
            分 = b(0).submatches(2) '(\d{2})
            秒 = b(0).submatches(3) '(\d{2})
end sub

2、匹配规则/元字符

\d	匹配所有单个数字(可以代替[0-9]，但不能取代)
\D	匹配单个数字以外的字符(包括字母，汉字，空格，下划线，各类符号。可以替代[a-zA-Z]，但不能取代)
[u4E00-\u9FA5]	只匹配汉字
\w	匹配单个字母、汉字、数字、和_
\W	匹配任何非单词字符(包括空格，各类符号，但不能识别下划线)
\s	匹配任何空白字符(空格、制表符、换行符等，眼睛看不到的)
\S	匹配任何非空白字符(眼睛能看到的)
\n	匹配一个换行符
\r	匹配一个回车符
\t	匹配一个制表符(Tab键)
.	匹配除 "\n"之外的任何单个字符
\b	匹配一个字母和空格的位置(不支持汉字)
\B	匹配非字母与空格的位置(跟\b相反)

3、元字符量词

*	匹配0次或多次
+	匹配一次或多次
？	匹配0次或一次
{下限：上限}	上下限
{n}	匹配n次
{n,}	匹配至少一次，无上限
{n, y}	匹配n次到y次，y>n

三十一、自定义函数

function test() '函数可以直接在Excel内使用
    application.volatile '易失性控制（实时刷新）
    ' 注意事项：
    ' 只能写在标准模块中
    
    ' 如需返回值至单元格，需要返回函数名对应值
    test = "hello world!"
end function

1、参数设置

function test(arr as range, optional c as integer = 1) 'c为参数，如不填默认为1
    if c = 1 then
        pass
    elseif c = 2 then
        pass
    elseif c = 3 then
        pass
    end if
end function
'Excel中使用：=test("A1",2)

2、应用

vlookup优化

function vlok(arr as range, col as range, return_col%) 
    'arr为要查询的单元格，col为查询值所在的单元格区域，return_col为偏移数量（左为-，右为+）
    application.volatile
    x = col.column
    set ss = col.find(arr)
    h = ss.row
    vlok = ss.offset(0, return_col)
end function

合并单元格求和

function sum_unite(arr as range, optional direction as integer = 0)
    'arr为需求和的第一个单元格，direction为求和方向，竖向合并0，横向合并1
    if direction = 0 then
        h = application.thiscell.mergearea.rows.count
        sum_unite = worksheetfunction.sum(arr.resize(h))
    elseif direction = 1 then
        y = application.thiscell.mergearea.colunms.count
        sum_unite = worksheetfunction.sum(arr.resize(1, y))
    end if
end function

三十二、事件

程序开发中的事件指的是， 可以对某一行为进行监听、处理，比如单击、修改、打开等，vba 中可以按照级别将
事件分为三类：Excel 程序级别事件、工作簿级别事件、工作表级别事件

监听和处理事件的代码，与以往写代码的位置不同，后面会分别介绍各级别事件的代码编写位置