VBA 知识点记录

索引

目录

一、VBA中使用公式

1. 公式可以在Application.Worksheet.Function/vba 中找到

2. 发生错误的处理方式：On Error 

3. 将字母/中文转换为数字

4. 文本函数的应用——split 函数+数组

二、VBA自定义函数

1. 定义基础函数，带参数的函数

2. 定义带参数的过程

3. 设置代码库，每次打开excel能使用常用代码

三、将对象赋值到变量

四、Dir 函数

1. dir 函数可以用来判断文件存在不存在

2. dir 函数中使用通配符可以返回同名的不同扩展名后缀的文件

---

一、VBA中使用公式

1. 公式可以在Application.Worksheet.Function/vba 中找到

写参数要按照VBA的格式来写，比如A列普通表达是A:A，但是VBA格式是 range(sheet2."a:a") 或 range(sheets(2)."a:a")

注意点：如果前面不加application，公式不能自动唤起

常用VBA 函数（vba.后加类型函数唤起）

图片来自王佩丰vba课程第八讲 

判断是否是数字：information 信息类函数 isnumeric

2. 发生错误的处理方式：On Error 

官方解释：On Error 语句 (VBA) | Microsoft Learn

在一般的场景中，用的是 On Error Resume Next ， 写在循环前面，指定发生运行时错误时，控件将紧跟在发生错误的语句后面的 语句 ，并继续执行。

控制循环结束

用条件语句

If 条件成立 Then 
    Exit For '或者Exit Sub
End if

注意点：考虑在循环前是否恢复条件为原始状态，否则循环将会破坏条件

例如：在5个工作表内查询A1单元格对应的内容填在B1，条件为A1单元格不为空时停止循环，若循环前没有清空B1单元格, 那么有可能会导致查询结果与实际不同。

3. 将字母/中文转换为数字

可以用VAL() 转换

也可以用变量*1 转换

4. 文本函数的应用——split 函数+数组

'拆分单元格
range.split(range("a2"),"-")

'拆分单元格，并拿出结果的第一个
range.split(range("a2"),"-")(0)

二、VBA自定义函数

1. 定义基础函数，带参数的函数

'sub 变function 公式

Function 2times(x)
    2times = x*2
End Function

定义函数后，可以直接在单元格使用，可以在模块过程中使用（sub）

案例1：将数字串转化为日期格式

function rqzh(zfc as string)

    rqzh = dateseiral(mid(zfc,1,4),mid(zfc,5,2),mid(zfc,7,2))

end function

案例2：提取字符串中的用符号隔开的第n个字符串

function tqzf (str1 as string, delimiter as string, i as integer)

    tqzf = split(str1,delimiter)(i-1)

end function

2. 定义带参数的过程

sub xxx() 括号中自定义参数 在其他的过程中可以用call xxx(参数a) 来调用

3. 设置代码库，每次打开excel能使用常用代码

新建某个excel，在模块中增加代码，最后保存为XLA 或XLAM 文件，保存路径不要更改；

exel开发工具中，点击加载项，点击新增的代码库；

新打开一个excel文件，excel选项→快速访问工具栏→从下列位置选择命令→宏→选择新增的代码

同理可以设置自定义函数到函数库，保存为xla/xlam文件

三、将对象赋值到变量

以往 可以 

sub test()
    dim i  as integer
    i=3
end sub

将常量赋值给变量

但是这样写会报错：

sub test2()
    dim sht as worksheet

    sht = sheets.add

    sht.name = "新表"

end sub

原因是：sheets.add 是对象，不能直接把对象赋值给变量，需要做一个小小的改动：在 语句前写一个set

set sht = sheets.add

四、Dir 函数

1. dir 函数可以用来判断文件存在不存在

dir(文件地址路径) 

如果文件存在，返回文件名，如果文件不存在，结果为空

2. dir 函数中使用通配符可以返回同名的不同扩展名后缀的文件

sub test()

    dim str as string 

    str = dir("C:\Documents\my_book.xls*")

end sub

如上代码，如果文件夹中有 my_book的xlsx文件，那么str 返回结果为my_book.xlsx

若有两个同名文件，但是扩展名后缀不同呢？也就是说，如果还有一个my_book.xls文件呢？

在代码中，加一行不带参数的dir语句

sub test()

    dim str as string 

    str = dir("C:\Documents\my_book.xls*")  '返回结果为 my_book.xls
    str1 = dir  '返回结果为 my_book.xlsx

end sub

利用dir函数+循环即可在excel中导入制定文件夹下的所有文件

Sub wjhb()
Dim str As String
Dim wb As Workbook

str = Dir("d:\data\*.xls*") '设置导入文件的文件夹

For i = 1 To 100
    Set wb = Workbooks.Open("d:\data\" & str) '设置工作簿为变量
    
    wb.Sheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) '当前工作簿导入文件夹内excel文件工作簿
    ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = Split(wb.Name, ".")(0) '命名
    
    wb.Close
    str = Dir
    If str = "" Then 
        Exit For
    End If '错误处理
Next
End Sub