Excel-VBA相关

目录：

一、数据类型

二、VBA命名规则

三、比较运算符与逻辑运算符

九、VBA中的筛选和 with 语句

十五、SET 表示把一个对象赋值给变量

十六、DIR 指定一个文件路径，其文件路径下有这个文件，返回文件名，没有这个文件，返回空

二十三、GetOpenFilename与Dialogs

二十四、使用ADO连接外部Excel数据源

二十五、Shape对象详解

二十六、Vba中使用Name修改文件名

一、数据类型：

数据类型的最大值：

1bytes（1字节）=8bit，8bit可存储2⁸个数字，负数占一半，正数和0占一半

例如：整型类型所占2个字节，2字节=16位，可存储2¹⁶个数据，负数占一半，正数和0占一半

在定义变量时，不知道定义的变量类型，此时可以设置成Variant（变体型）类型

二、VBA命名规则：

1.可以使用字母、数字个一些标点符号，但是第一个字符必须为字母

2.不能使用空格和句点

3.不能含有特殊的声明字符（#、￥、%、&、、！）

4.变量名不能超过254个字符

5.变量名不区分大小写

空白符：空格符、制表符、换行符等统称为空白符，主要用于规范程序格式

三、比较运算符与逻辑运算符

运算符的结合性：

对于算数运算符而言，计算的顺序为从左至右

对于赋值运算符而言，计算的顺序为从右至左

四、For Next循环

跳出FOR循环，用exit for（常于if搭配使用）

用于按指定的次数执行循环体

For each in Next循环：

针对一个集合或是数组中的每个元素循环执行操作

For each in next相当于python中的for I in range

他会遍历对象中的每一个元素

如将其他工作表的名称放入到部门表中

五、分支结构

分支控制语句（IF语句、Select Case语句）

IF结构：

两种语言不同的点在于Python将VBA中IFElseIF的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_datecase.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_datecaeate.xlsx")
'关闭
ActiveWorkbook.Close
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使用)

八、VBA中的选区问题

Selection：当前选区，表示当前选中的单元格区域

ActiveCell：活动单元格，有且仅有一个

UsedRange：已使用区域，表示使用过的所有区域，（对某个单元格进行格式设置，即使没有输入内容，也属于已使用区域）

CurrentRegion：当前区域，表示此单元格四周相连的单元格区域

九、VBA中的筛选和 with 语句

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

十二、VBA函数

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类型自动跳转到文件夹的时候删除，即第一步的窗口中删除）

十五、SET 表示把一个对象赋值给变量

十六、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中的查找

VBA中的FIND功能很强大，完全可以替换Vlookup函数，甚至突破Vlookup函数的局限性，比如，姓名不在数据第一列、查找数据横向的任何一个单元格（改变offset参数）、对单元格进行清除格式设置等等一系列操作

举例：

十八、数组

一维数组：

二维数组：

在VBA中最经常涉及的就是读写工作表中的单元格，如果读写操作次数较多，或者涉及到的单元格数量较多，往往会导致程序运行效率较低。这时经常用到的解决方法就是使用数组保存数据，在内存中进行数据处理和加工，最后一次行更新工作表，这样的操作方式往往会极大提升程序的运行效率。

对比：

在for循环中尽量不用range，而是用数组替代，当代码量特别大的时候它们的区别就会特别明显

数组应用例子：

数组可以帮我们虚拟出来一个内存空间，临时存放一些中间用到的数据（辅助列）

数组可以直接用在函数里

Dim 与 ReDim:

Dim里面的东西必须用常量，Dim arr(1 to i)就会报错

上述图片中的数组应用例子，只能有4行，如果A列为N个产品呢？此时我们就需要将数组中的元素个数设置为1 to n

十九、ActiveX控件

在开发工具的设计模式下，可点击右键进行属性设置

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有四个参数：（这里只需掌握三个即可）

第一个参数表示文本框内容

第二个参数表示按钮显示类型

第三个参数表示弹出框标题

例如：

效果图：

样式参数

对于第二个参数来说：需要多种样式组合就用加号连接

Msgbox的返回值：

返回值的对应结果：

返回1说明点击了确定，以此类推

二十二、Inputbox详解

Inputbox有两个，一个是函数，一个是方法

Inputbox函数

Inputbox方法

二十三、GetOpenFilename与Dialogs

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过程即可

二十四、使用ADO连接外部Excel数据源

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_dateAdata.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:dataEdata.xlsx;extended properties=""excel 12.0;HDR=YES"""
conn.Close

ACCESS文件
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:dataAdata.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"