Excel-VBA相关
目录：
十六、DIR 指定一个文件路径，其文件路径下有这个文件，返回文件名，没有这个文件，返回空
数据类型的最大值：
1bytes（1字节）=8bit，8bit可存储28个数字，负数占一半，正数和0占一半
例如：整型类型所占2个字节，2字节=16位，可存储216个数据，负数占一半，正数和0占一半
在定义变量时，不知道定义的变量类型，此时可以设置成Variant（变体型）类型
1.可以使用字母、数字个一些标点符号，但是第一个字符必须为字母
2.不能使用空格和句点
3.不能含有特殊的声明字符（#、￥、%、&、、！）
4.变量名不能超过254个字符
5.变量名不区分大小写
空白符：空格符、制表符、换行符等统称为空白符，主要用于规范程序格式
运算符的结合性：
对于算数运算符而言，计算的顺序为从左至右
对于赋值运算符而言，计算的顺序为从右至左
跳出FOR循环，用exit for（常于if搭配使用）
用于按指定的次数执行循环体
For each in Next循环：
针对一个集合或是数组中的每个元素循环执行操作
For each in next相当于python中的for I in range
他会遍历对象中的每一个元素
如将其他工作表的名称放入到 部门 表中
分支控制语句（IF语句、Select Case语句）
IF结构：
两种语言不同的点在于Python将VBA中IF\ElseIF的Then换成了冒号，VBA中的ElseIF不能简写成Elif
Select Case语句
针对多重判定任务反复进行循环操作处理
While循环的两种写法：
DO…Until循环
Until语句又称为“直到型”循环结构处理语句。也就是直到条件成立的时候才结束循环体。
工作表有四种：
'工作表的选择
Sheet1.Select '选择工作表1
Sheets(3).Select '选择第三个工作表
Sheets("一月").Select '选择名称为一月的工作表
'插入工作表
Sheets.Add '在当前工作表前面插入新的工作表
Sheets.Add , after:=Sheet3 '在sheet3后面插入新的工作表
'在sheet3后面插入100个新的工作表
Dim i As Integer
For i = 1 To 100
Sheets.Add , after:=Sheet3
Next
'统计工作簿中工作表的数量
Sheet5.Range("a1") = Sheets.Count '在工作表的A1单元格中输出工作簿中所有工作表的数量
Sheet5.Range("a1") = Sheet1.Name '在工作表的A1单元格中输出I作表1的名称
Sheet1.Name = "汇总" '将工作表1的名称改为汇总
'使其总是在最后张工作表后面插入新表
Sheets.Add , after:=Sheets(Sheets.Count)
'创建十二个月的工作表
Dim i As Integer
For i = 1 To 12
Sheets.Add , after:=Sheets(Sheets.Count) '这里的Sheets.Count表示插入前的工作表数
Sheets(Sheets.Count).Name = i & "月" '这里的Sheets.Count表示插入后的工作表数，与上句相差1
Next
Excel.Application.DisplayAlerts = False '关闭Excel 的报错机制
Sheet3.Delete '删除工作表3
Excel.Application.DisplayAlerts = True '开启Excel 的报错机制
'插入100张工作表
Sheets.Add , Count:=100
'删除100张工作表
Dim i As Integer
Excel.Application.DisplayAlerts = False
For i = 1 To 100
Sheets(1).Delete '总是删除左起第一 张工作表,这里也可以用step -1 倒序删除
Next
Excel.Application.DisplayAlerts = True
Sheet1.Copy , after:=Sheets(2) '复制工作表，并且将其置于左起第二 张工作表之后
Sheet1.Copy , after:=Sheets(Sheets.Count) '复制工作表，并且始终将其置在最后端
操作工作簿:
Sub open_file()
'关闭错误提示框
Application.DisplayAlerts = False
'关闭屏幕更新
Application.ScreenUpdating = False
'打开路径文件，文件名需要填写完整
Workbooks.Open Filename:=("E:\Excel_date\case.xlsx")
'给活动工作簿的表1添加值
ActiveWorkbook.Sheets(1).Range("A1") = "到此一游o"
'保存
ActiveWorkbook.Save
'关闭
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub
Sub create_file()
'创建新的工作簿
Workbooks.Add
ActiveWorkbook.Sheets(1).Range("A1") = "测试内容"
'文件另存为
ActiveWorkbook.SaveAs Filename:=("E:\Excel_date\caeate.xlsx")
'关闭
ActiveWorkbook.Close
End Sub
将工作表另存为工作簿：
代码:
Sub test() Dim sht As Worksheet For Each sht In Sheets sht.Copy ActiveWorkbook.SaveAs Filename:=("D:\data\" & sht.Name & ".xlsx") ActiveWorkbook.Close
next End Sub
单元格是EXCEL中最小的对象
单元格对象Range：(跳转到指定单元格搭配select使用)
Range：
Sub try_case_2()
'弹出A1单元格所在的当前区域的最后一个单元格的行号
MsgBox Range("A1").End(xlDown).Row
'从a65536单元格向上移动到第一个有数据的单元格
MsgBox Range("A65536").End(xlUp).Row
End Sub
Range("A6").EntireRow.Select '选中A6所在单元格的整行
Range("A6").Resize(1, 3).Select '以A6单元格为参照，重新框选选区
Range("H7:L7").Copy , Range("N7") '将H7:L7复制到N7
'注意:如果是复制一整行的话，最好在相同列复制
Range("A7").EntireRow.Copy , Range("A10")
'这样就会报错，因为列号不匹配，会导致粘贴区域范围不够
Range("A7").EntireRow.Copy , Range("B10")
Sub 合并单元格()
Dim rng As Range
For Each rng In Range("H21:O21") '将H21与H22合并，直到021与022合并
rng.Resize(2, 1).Merge
Next
End Sub
注意：如果活动单元格不是sheet1，那么以下代码就会报错
Sub case_1()
Sheet1.Range("c2").Select
End Sub
如下就不会报错：
Sub case_1()
Sheet1.Select
Sheet1.Range("c2").Select
End Sub
Cells指定单元格对象： (跳转到指定单元格搭配select使用)
Selection：当前选区，表示当前选中的单元格区域
ActiveCell：活动单元格，有且仅有一个
UsedRange：已使用区域，表示使用过的所有区域，（对某个单元格进行格式设置，即使没有输入内容，也属于已使用区域）
CurrentRegion：当前区域，表示此单元格四周相连的单元格区域
1.筛选的原理就是将不符合条件的数据隐藏起来（行高设为0）
Sub 筛选()
Sheet8.Range("$A$9:$C$13").AutoFilter Field:=2, Criteria1:="凯迪拉克"
'Field:=2表示数据区域第几列应用筛选
'Criteria1:="凯迪拉克"表示筛选条件
Sheet8.Range("$A$9:$C$13").AutoFilter
'关闭筛选
End Sub
2.With与End With：
作用就是节省代码，使代码简洁，书写方便
Sub case_1()
With Sheet1
.Range("A1") = 1
.Range("A2") = 2
.Range("A3") = 3
Sheet2.Range("A1") = 1
End Sub
只有前面加点的才会应用with
工作表事件：
例如：用事件在工作表中修改颜色，即单元格选区发生改变，所在选区的那一行设置为黄色底纹，其余行的底纹颜色为透明
Sub change_color()
Cells.Interior.Pattern = xlNone
Selection.EntireRow.Interior.Color = 65535
End Sub
Change事件的注意：
点击表格自动更新事件：
工作簿事件：
举例：在保存工作簿前的时候备份数据到指定文件下且文件名字为当前时间
Application.WorksheetFunction.函数名 ‘工作表函数调用方式
On Error Resume Next ‘程序崩溃请继续执行代码
举例:考生成绩统计查询系统
题目示例
Sub try_1()
Dim i, k, n As Integer
For i = 2 To Sheets.Count
k = k + Application.WorksheetFunction.CountA(Sheets(i).Columns("A")) - 1
v = v + Application.WorksheetFunction.CountIf(Sheets(i).Columns("F"), "男")
n = n + Application.WorksheetFunction.CountIf(Sheets(i).Columns("F"), "女")
Next
Sheet1.Range("D26") = k
Sheet1.Range("D27") = v
Sheet1.Range("D28") = n
End Sub
Sub try_2()
On Error Resume Next '如果代码崩溃，继续执行代码
Dim i As Integer
Sheet1.Range("D14").ClearContents
For i = 2 To Sheets.Count
Sheet1.Range("D14") = Application.WorksheetFunction.VLookup(Sheet1.Range("D9"), Sheets(i).Columns("A:H"), 5, 0)
Sheet1.Range("D16") = Application.WorksheetFunction.VLookup(Sheet1.Range("D9"), Sheets(i).Columns("A:H"), 6, 0)
Sheet1.Range("D18") = Application.WorksheetFunction.VLookup(Sheet1.Range("D9"), Sheets(i).Columns("A:H"), 3, 0)
Sheet1.Range("D20") = Application.WorksheetFunction.VLookup(Sheet1.Range("D9"), Sheets(i).Columns("A:H"), 8, 0)
Sheet1.Range("D22") = Sheets(i).Name
If Sheet1.Range("D14") <> "" Then
Exit For '结束循环
End If
Next
End Sub
1.isnumeric(参数)
判断参数是不是数字
2.参数=val(参数)
将参数转为数值型
l = InputBox("请输入你要按哪列分")
If IsNumeric(l) = False Or l < 1 Then
Exit Sub
End If
l = Val(l)
VBA函数调用方式：
完整写法：
VBA.Strings.Left (参数)
第二个单词表示VBA函数类型，使用函数要准确写出其所属类型
3.instr
用来确定参数2，在参数1中处于第几位的，如果参数2没在参数1中则返回0（find函数查找的时候，如果没有会程序崩溃）
举例：截取@前面的字符：
Sub try()
Sheet1.Range("B2") = VBA.Strings.Left(Sheet1.Range("A2"), VBA.Strings.InStr(Sheet1.Range("A2"), "@") - 1)
End Sub
结果：
4.split
Split(参数1，参数2)表示在参数1中用参数2进行分割，得到的结果为一个数组
数组的第一个值为0，即用python中索引的方式去取数，索引从0开始
Sub try()
Dim i As Integer
For i = 2 To Range("A65535").End(xlUp).Row
If InStr(Range("A" & i), "-") <> 0 Then
Range("B" & i) = Split(Range("A" & i), "-")(2) & "年 第" & Split(Range("A" & i), "-")(3) & "周"
End If
Next
End Sub
结果：
在工作中使用自定义的函数
可以将VBA中的函数作为一个新的函数，这样这个函数不仅可以在SUB过程中被调用，也可以在工作表中被调用
例如:将VBA函数中的Split函数封装为 截取字符 函数，这样工作表中和sub过程中都可以调用
结果:
创建带参数的SUB：
根据表格内容创建新的工作表，工作表名为表格内容
按值传递与按引用传递：
想让代码在任何工作簿中都可以使用，需要做三件事：
1.将代码放入代码库中
点击已经写好代码的工作簿的另存为---浏览---保存类型选择xlam或者xla
Excel会自动跳到保存加载宏的文件夹，起好名字点击保存
2.告诉Excel每次打开工作簿都加载此代码
3.将代码设置一个按钮（添加到快速访问工具栏）
先关闭打开的工作簿，打开一个新的工作簿---文件---选项
但是需要注意的是，加载宏的代码必须具有可移植性，相当于软代码的感觉，要保证这块代码在任何工作簿中都可以完整且正确的执行
加载宏的代码一定不能出现Thisworkbook这种类型的自动，因为在加载宏的文件里，Thisworkbook代表那个原本的xla文件，可以使用变量，将变量定义为当前的活动工作簿即可
在删除代码库的时候，需要先将第二步中的加载项取消勾选，然后再找到路径去删除（可以另存为选择xlam类型自动跳转到文件夹的时候删除，即第一步的窗口中删除）
十六、DIR 指定一个文件路径，其文件路径下有这个文件，返回文件名，没有这个文件，返回空
DIR里支持通配符操作，可以帮助我们回避掉文件同名，格式不同的情况
Dir多条符合值的辨析:
当有多个文件符合Dir的结果时，第一个Dir需要加条件，并返在内存中存储一个符合结果的序列，其后的Dir如果不加文件路径,返回的是结果序列的第二个值，以此类推。
如果只有两个符合结果的值，第一个Dir返回的是序列一的值，第二个返回序列二的值，第三个返回空，第四个会报错
例如：（文件路径下苏州只有两个 苏州.xls和苏州.xlsx）
于是，我想查找文件路径下所有的文件可以这样写:
Dir的壳子：
打开路径文件里的所有数据，然后执行一些操作，然后关闭
案例：将路径文件下工作簿里的表复制到指定工作簿中
Sub try()
Dim i, v As Integer
Dim str As String
Dim sht As Worksheet
str = Dir("E:\Excel_date\*.*")
For i = 1 To 100
Set wb = Workbooks.Open("E:\Excel_date\" & str)
For v = 1 To wb.Sheets.Count
wb.Sheets(v).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = Split(wb.Name, ".")(0) & wb.Sheets(v).Name
Next
wb.Close
str = Dir
If str = "" Then
Exit For
End If
Next
End Sub
VBA中的FIND功能很强大，完全可以替换Vlookup函数，甚至突破Vlookup函数的局限性，比如，姓名不在数据第一列、查找数据横向的任何一个单元格（改变offset参数）、对单元格进行清除 格式设置 等等一系列操作
举例：
一维数组：
二维数组：
在VBA中最经常涉及的就是读写工作表中的单元格，如果读写操作次数较多，或者涉及到的单元格数量较多，往往会导致程序运行效率较低。这时经常用到的解决方法就是使用数组保存数据，在内存中进行数据处理和加工，最后一次行更新工作表，这样的操作方式往往会极大提升程序的运行效率。
对比：
在for循环中尽量不用range，而是用数组替代，当代码量特别大的时候它们的区别就会特别明显
数组应用例子：
数组可以帮我们虚拟出来一个内存空间，临时存放一些中间用到的数据（辅助列）
数组可以直接用在函数里
Dim 与 ReDim:
Dim里面的东西必须用常量，Dim arr(1 to i)就会报错
上述图片中的数组应用例子，只能有4行，如果A列为N个产品呢？此时我们就需要将数组中的元素个数设置为1 to n
在开发工具的设计模式下，可点击右键进行属性设置
1.CommandButton控件（按钮）
常用属性：
Enabled可用性
Caption标题
Visible可见性
常用事件：Click事件（点击事件）
2.Label控件（标签）
常用属性：
Enabled可用性
Caption标题
Visible可见性
一般没有应用的事件，就一个文本框而已
3. OptionButton控件（选项按钮）
常见属性：
Caption 标题
Enabled 可用性
GroupName 组名
Value 返回值
Visible 可见性
常见事件: Click事件（点击事件）
4. SpinButton控件（微调按钮）
可以根据长宽比调整是上下布局还是左右布局
常见属性：
Enabled可用性
Value 返回值 可返回按钮改变之后的数值大小
Visible可见性
Min 最小值
Max 最大值
常用事件：
Change事件
5.chexbox按钮（复选框）
一组之中各个选项不会互斥，即都可以选择
但是可以用事件，也可以将chekbox做成互斥的模样，即click事件，然后另外一个的值为False
6.Textbox（文本框控件）
常见属性：
Enabled 可用性
Value 返回值
Visible 可见性
PasswordChar 密码字符
即保护用户输入信息掩码
TabIndex 按下Tab键时的切换顺序，多个控件之间用Tab键切换，可在属性设置
注意值为0开始
举例事件：输入账号密码的文本框事件举例
7. ComboBox复合框控件（下拉框）
常用属性：
Enable Value Visible
List 数据源列表
常用方法：
AddItem 增加一个下拉项目
RemoveItem 移除一个项目
Clear 清除
举例：使用的时候可以结合各种事件来调用相应的sub
'增加下拉框的选项
Sub test1()
UserForm1.ComboBox1.AddItem "苏州"
End Sub
'把单元格一列数据设置为下拉的数据源
Sub test2()
For i = 2 To 6
UserForm1.ComboBox1.AddItem Sheet1.Range("a" & i)
Next
End Sub
'删除掉下拉框里的一项
Sub test3()
Call test2
UserForm1.ComboBox1.RemoveItem (1) '删除第二项
End Sub
'清空下拉框数据源
Sub test4()
Call test2
UserForm1.ComboBox1.Clear
End Sub
'计算下拉框里有多少个选项
Sub test5()
Call test2
MsgBox UserForm1.ComboBox1.ListCount
End Sub
'把下拉框数据源里某个值输出到单元格
Sub test6()
Call test2
Range("c2") = UserForm1.ComboBox1.List(1)
End Sub
'把下拉框里所有的选项输出到单元格区域
Sub test7()
Call test2
Range("d1:d5") = UserForm1.ComboBox1.List
End Sub
8.listbox（列表框）
举例：使用的时候可以结合各种事件来调用相应的sub
'增加列表框的选项
Sub test1()
UserForm1.ListBox1.AddItem "苏州"
End Sub
'把单元格一列数据设置为列表框的数据源
Sub test2()
For i = 2 To 5
UserForm1.ListBox1.AddItem Sheet1.Range("a" & i)
Next
End Sub
'删除掉列表框里的一项
Sub test3()
Call test2
UserForm1.ListBox1.RemoveItem (1) '删除第二项
End Sub
'清空列表框数据源
Sub test4()
Call test2
UserForm1.ListBox1.Clear
End Sub
'计算列表里有多少个选项
Sub test5()
Call test2
MsgBox UserForm1.ListBox1.ListCount
End Sub
'把组合框数据源里某个值输出到单元格
Sub test6()
Call test2
Range("c2") = UserForm1.ListBox1.List(1)
End Sub
'把下拉框里所有的选项输出到单元格区域
Sub test7()
Call test2
Range("d1:d4") = UserForm1.ListBox1.List
End Sub
'把一个区域的一列数据设置为列表框的数据源
Sub test8()
UserForm1.ListBox1.List =Range("d1:d4").value
End Sub
二十、窗体
窗体对应属性：
Enabled 可用性
Caption 标题
Visible 可见性
ShowModal 显示模式
True表示独占，False表示不独占，设置独占之后此窗口以外的对象则不能被点击，当有多个窗体的独占模式有错误时，会报以下错误：
窗体常用方法：
Show 显示
Hide 隐藏
窗体常用事件：
UserForm_Activate 激活
UserForm_QueryClose 退出
在窗体背后写代码时，直接双击就可以进入事件代码编辑区域，双击窗体上的按钮可以直接跳转到该按钮对应的事件代码编辑区域
窗体常用方法可与事件搭配使用：
例如当工作簿一打开，就显示此窗口
Msgbox有四个参数：（这里只需掌握三个即可）
第一个参数表示文本框内容
第二个参数表示按钮显示类型
第三个参数表示弹出框标题
例如：
效果图：
样式参数
对于第二个参数来说：需要多种样式组合就用加号连接
Msgbox的返回值：
返回值的对应结果：
返回1说明点击了确定，以此类推
Inputbox有两个，一个是函数，一个是方法
Inputbox函数
Inputbox方法
1.GetOpenFilename
返回所选文件的完整路径
那么根据前面Dir壳子的思路，可以利用GetOpenFilename也做一个打开文件，关闭文件的壳子，但是不同的是文件是用户自己选de
Sub try()
Dim arr()
Dim i As Integer
Dim wb As Workbook
arr = Application.GetOpenFilename("Excel文件,*.xls*", 1, , , True)
On Error Resume Next '防止用户没有选择文件直接点击取消
If arr(1) <> "False" Then
For i = LBound(arr) To UBound(arr)
Set wb = Workbooks.Open(arr(i))
'这里写需要进行的操作
wb.Close
Next
End If
End Sub
2.Application.Dialogs
用于操作Excel中所有的对话框
在使用的时候需要注意有的操作要先选中对象，才能再执行对话框，比如指定宏的对话框，需要先选中指定宏的控件，在执行sub过程即可
1、在VBE界面中工具—引用—勾选Microsoft ActiveX Data Object x.x Library（x.x表示版本号）
2、连接代码
Sub test()
Dim conn As New ADODB.Connection
Dim sql As String
conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=E:\Excel_date\Adata.accdb"
sql = "" '双引号里是一组sql代码语句，要用双引号把整个代码括起来 "
Columns("A:Z").ClearComments
Range("A2").CopyFromRecordset conn.Execute(sql) '如果sql代码没有返回值那么就只保留coon.Execute(sql)的代码
conn.Close
End Sub
以下给出各种数据库连接的语句（即上文加粗部分）
Excel文件
conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\Edata.xlsx;extended properties=""excel 12.0;HDR=YES"""
conn.Close
ACCESS文件
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\Adata.accdb"
Mysql数据库
conn.Open "Provider=SQLOLEDB;DataSource=" & Path & ";Initial Catolog=" & strDataName
MSSQL数据库
conn.Open "Provider=MSDASQL;Driver={SQL Server};Server=" & Path & ";Database=" & strDataName
Oracle数据库
conn.Open "Provider=madaora;Data Source=MyOracleDB; User Id=UserID; Password=Password"
插入图片案例：
图表对象：
在不知道如何操作的时候一定要想到录制宏
Name 案例：
将图片文件的名字改为姓名加身份证号：
当做很复杂的程序（点击切换题目点击计算分数）等需要编程的东西我们使用Active X 控件，对于调查文件等只需要勾选的情况，就可以使用表单控件，又小又灵活
表单控件的所有控件都要设置单元格链接地址来获取返回值
选中控件---右键 设置空间格式---控制
Active X控件有分组属性，在表单控件中只能使用分组框来框选表单控件来达到分组的目的
那么如何隐藏分组框还让他发挥作用的，这里就涉及到触类旁通的思想，在VBA的学习过程中要不断地去尝试触类旁通的思想，推测思想以及录制宏的方式去解决
相当于Mysql中like，只不过稍微有点区别
* 任意字符
# 任意一位数字
？ 一位字符
！ 逻辑非
[A-Z] 大写字母
[A-Z a-z] 字母
[0-9] 数字
[!A-Z] 非大写字母
[!0-9] 非数字
这里的共有与私有相当于python中的局部变量和全局变量
对于sub而言，在一个模块中可以相互调用，在另外一个模块中也可以相互调用（sub 默认为 public 公有的）
对于private sub而言，其在一个模块中可以调用private sub，另外的模块就不能调用（private 私有的）
对于变量而言：
在实际开发过程中，尽量少用public 定义全局变量，可以使用定义一个函数作为桥梁进行传递：
静态变量：
普通变量用Dim声明，过程结束后内存就会释放其变量的存储空间，多次执行的话，每次都要开辟新的空间
静态变量用Static声明，过程结束内存也不会释放其变量的存储空间，多次执行的话，就会在原基础上继续进行运算
用于保存以类或对象方式编写的代码。
通过创建类模块，可以创建用户自定义类和对象。
使用已建立的类模块，不需要编码者了解具体是如何工作的，因此可以实现共享代码
bilibili视频讲解地址：https://www.bilibili.com/video/bv14i4y1g7dH
类模块变量定义方法： Dim 变量名 as new 类模块名
定义方式：
方法1
工具-引用-浏览-找到scrrun.dll-确定
Dim dic As New Dictionary
方法2 （推荐）
Dim dic
Set dic = CreateObject("Scripting.Dictionary")
可以把字典理解成只有两列数据的数组
字典有两个优点：
一是查询非常快根据键值配对value
二是可以自动去重
可以将需要赋值给字典的单元格区域设置成数组模式，既快速又简洁
在单元格给数组区域赋值的时候随便几行几列都没问题，在给字典赋值的时候只能是一列键值，一列value而已，将数组的某一列区域赋值给字典的keys，可以达到去重的效果
