大家好，又见面了，我是你们的朋友全栈君。
文章目录
- 如何创建VBA
- VBA语法规则
- 声明变量
- 给变量赋值
- 让变量存储的数据参与运算
- 关于声明变量的其他知识
- 变量的作用域
- 特殊的变量——数组
- 声明多维数组
- 声明动态数组
- 其他创建数组的方法
- 数组函数
- 利用UBound求数组的最大索引号
- 利用LBound函数求最小索引号
- 求多维数组的最大和最小索引号
- 用join函数将一维数组合并成字符串
- 将数组内容写入工作表中
- 数组的存取
- 特殊数据的专用容器——常量
- 对象，集合及对象的属性和方法
- VBA中的运算符
- 算数运算符
- 比较运算符
- 通配符
- 逻辑运算符
- VBA中的运算符
- VBA内置函数
- 执行程序执行的基本语句结构
- if语句
- select case语句
- for 循环
- for each…next语句循环处理集合或数组中的成员
- do while循环
- 使用Goto语句，让程序转到另一条语句去执行
- with语句，简写代码
- if语句
- sub过程，基本的程序单元
- sub过程的基本结构
- 在过程中调用过程
- sub过程中的参数传递
- 自定义函数，function过程
- 声明一个自定义函数
- 使用自己定义的函数
- function example
- 设置函数为易失性函数，让自定义函数也能重复计算
- 操作对象
- VBA中常用的对象
- application对象操作
- 使用ScreenUpdating属性设置更新屏幕
- 设置DisplayAlerts属性禁止显示警告对话框
- 借助worksheetfunction属性使用工作表函数
- 设置属性，更改Excel的工作界面
- application的子对象
- workbook对象
- 引用workbook对象
- 访问workbook对象属性
- 创建工作簿——add
- 用open方法打开工作簿
- activate激活工作簿
- 保存工作簿
- close——关闭工作簿
- worksheet对象
- add方法新建工作表
- 设置name属性，更改工作表的标签名称
- 用delete方法删除工作表
- 激活工作表的两种方法
- 用copy方法复制工作表
- 使用move移动工作表
- 设置visible属性，隐藏或者显示工作表
- 访问count属性，获得工作簿中工作表的数目
- range对象
- 用range属性引用单元格
- 用cell属性引用单元格
- 引用整行单元格
- 引用整列单元格
- 使用union方法合并多个单元格区域
- range对象的offset属性
- range对象的resize属性
- worksheet对象的usedrange属性
- range对象的currentregion属性
- range对象的end属性
- value属性——单元格中的内容
- count属性，获得区域中包含的单元格个数
- 通过address属性获取单元格地址
- 用activate和select方法选中单元格
- copy方法复制单元格区域
- cut方法剪切单元格区域
- 用delete方法删除指定的单元格
- 操作对象的一些例子
- 根据需求创建工作簿
- 判断某个工作簿是否已经打开
- 判断文件夹中是否存在指定名称的工作簿文件
- 向未打开的工作簿中输入数据
- 隐藏活动工作表外的所有工作表
- 批量新建指定名称的工作表
- 批量对数据进行分离，并保存到不同的工作表中
- 将多张工作表中的数据合并到一张工作表中
- 将工作簿中的每张工作表都保存为单独的工作簿文件
- 将多个工作簿中的数据合并到同一张工作表中
- 为同一工作簿中的工作表建一个带链接的目录
- 执行程序的自动开关——对象的事件
- 让excel自动相应我们的操作
- 使用工作表事件
- worksheet对象的change事件
- 禁用事件，让事件过程不再自动执行
- selectionchange事件：当选中的单元格改变时发生
- 高亮选择区域的相同值
- 用批注记录单元格中数据的修改情况
- 常用的worksheet事件
- 使用工作簿事件
- open事件：当打开工作簿时发生
- beforeclose事件： 在关闭工作簿之前发生
- sheetchange事件：更改任意工作表中的单元格时发生
- 常用的workbook事件
- 不是事件的事件
- application对象的onkey方法
- Application对象的OnTime方法
- 让文件自动保存
- 设置自定义的操作界面
- 控件，搭建操作界面必不可少的零件
- 在工作表中使用控件
- 在工作表中使用ActiveX控件
- 不需设置，使用现成的对话框
- 用InputBox函数创建一个可输入数据的对话框
- 用input方法创建交互对话框
- 使用msgbox函数创建输出对话框
- 使用FindFile方法显示【打开】对话框
- 用GetOpenFileName方法显示【打开】对话框
- 用GetSaveAsFilename方法显示【另存为】对话框
- 使用application对象的FileDialog属性获取目录名称
- 使用窗体对象设置交互界面
- 用代码操作自己设计的窗体
- 显示窗体
- 将窗体显示为无模式窗体
- 关闭或隐藏已显示的窗体
- 用户窗体的事件应用
- 借助Initialize事件初始化窗体
- 借助QueryClose事件让窗体自带的【关闭】按钮失效
- 为窗体的控件设置功能
- 为【确定】按钮添加事件过程
- 给控件设置快捷键
- 更改控件的Tab键顺序
- 用窗体设计一个简易的登陆窗体
- 控件，搭建操作界面必不可少的零件
- 调试与优化编写的代码
- On Error GoTo标签
- on error resume next
- On Error GoTo 0
如何创建VBA
- 进入开发工具窗口
2.选择插入模块，然后插入过程，选择子程序
VBA语法规则
在Excel中，数据只有文本，数值，日期值，逻辑值和错误值五种类型。但是在VBA中，数据类型跟Excel不完全相同。根据数据的特点，VBA将数据分为布尔型（boolean），字节型(byte)，整数型(integer)，单精度浮点型(single)，双精度浮点型(double)，货币型(currency)，小数型(decimal)，字符串型（string），日期型（date）,对象型等等
声明变量
声明变量，其实就是指定该变量的名称及其可存储的数据类型，要在VBA中声明一个变量，有以下几种方法：
- Dim 变量名 as 数据类型
- Private 变量名 as 数据类型，用private声明变量，该变量将会变成私有变量
- Public 变量名 as 数据类型，用public变量定义的变量是公有变量
- static 变量名 as 数据类型，如果使用static声明变量，这个变量将会变成静态变量，当程序结束后，静态变量会保持其原值不变。
给变量赋值
- 给数据类型的变量赋值，应该要使用这个语句：let 变量名称 = 要存储的数据
- 给对象类型的变量赋值，应该使用下面的语句：“set 变量名称 = 要存储的对象名称”
让变量存储的数据参与运算
- 数据型变量参与运算
2. 对象型变量参与运算
关于声明变量的其他知识
- 如果要声明多个变量可以将代码写成下面形式：
2. 可以使用变量类型声明符定义变量类型
数据类型 | 类型声明字符 |
|---|---|
Integer | % |
Long | & |
Single | ！ |
Double | # |
currency | @ |
string | $ |
- 声明变量可以不指定变量类型：在VBA中声明变量是，如果不确定会将类型的数据存储在变量中，可以在声明变量时，只定义变量的名字，而不是变量的类型。如果声明变量时，只指定变量的名称而不指定变量的数据类型，VBA默认将该变量定义为Variant类型，如果一个变量被声明为variant类型，俺么它能够存储任何数据类型
- 强制声明所有变量：如果担心自己忘记在程序中忘记声明变量，可以在模块开头，输入“Option Explicit”
变量的作用域
按作用域划分，VBA中的变量可以划分为本地变量，模块变量和公共变量。
作用域 | 描述 |
|---|---|
单个过程 | 在一个过程中使用dim或者static语句声明的变量，作用域为本过程，即只有声明变量的语句所在的过程能够使用它，这样的变量，称为本地变量 |
单个模块 | 在模块的第一个过程之前使用dim或者private语句声明的变量，作用域为声明变量的语句所在模块中的所有过程，即该模块中所有的过程都可以使用它，这样的边框称为模块级变量 |
所有模块 | 在一个模块的第一个过程之前使用public语句声明的变量，作用域为所有模块，即所有模块中的过程都可以使用它，这样的变量称为公共变量 |
特殊的变量——数组
- 数组就是同种类型的多个变量的集合
- 数组中的元素可以通过索引值取出
- 声明数组时应该声明数组的大小 “public dim 数组名称 （a to b）as 数据类型”
- 给数组赋值就是给数组的每个元素分别赋值
声明多维数组
代码语言：javascript复制Dim arr(a,b) as Integer这里是声明一个叫arr数组，其形状为a行b列
声明动态数组
当声明数组时，不知道要在这个数组中填入多少个数据时，可以使用动态数组。将数组声明为动态数组时，不能直接使用变量，需要之后使用redim 重新制定数组大小
代码语言：javascript复制Sub test()
a = Application.WorksheetFunction.CountA(rang("A:A"))
Dim arr() As String
ReDim arr(1 To a)
End Sub
其他创建数组的方法
- 使用array函数声明数组
Sub arraytest()
Dim arr As Variant
arr = Array(1, 2, 3, 4)
MsgBox "arr数组的第2个元素为：" & arr(1)
End Sub
2. 使用是split函数声明数组
代码语言：javascript复制Sub arraytest()
Dim arr As Variant
arr = Split("叶枫，空空，小月，老祝", "，")
MsgBox "arr数组的第2个元素为：" & arr(1)
End Sub
- 通过单元格区域直接创建数组
Sub arraytest()
Dim arr As Variant
Range("A1:C3").Value = 5
arr = Range("A1:C1").Value
Range("e1:G1").Value = arr
End Sub
数组函数
利用UBound求数组的最大索引号
代码语言：javascript复制Sub arraytest()
Dim arr As Variant
Range("A1:C3").Value = 5
arr = Range("A1:C1").Value
MsgBox "数组的最大索引号是：" & UBound(arr)
End Sub利用LBound函数求最小索引号
代码语言：javascript复制Sub arraytest()
Dim arr As Variant
Range("A1:C3").Value = 5
arr = Range("A1:C1").Value
MsgBox "数组的最小索引号是：" & LBound(arr)
End Sub求多维数组的最大和最小索引号
代码语言：javascript复制Sub arraytest()
Dim arr(10, 100) As Integer
Dim a As Integer, b As Integer
a = UBound(arr, 1)
b = UBound(arr, 2)
MsgBox "第一维的最大索引是：" & a & Chr(13) & "第二维的最大索引是：" & b
End Sub
用join函数将一维数组合并成字符串
代码语言：javascript复制Sub arraytest()
Dim arr As Variant
arr = Array(1, 2, 3, 4)
Text = Join(arr)
MsgBox Text
End Sub将数组内容写入工作表中
代码语言：javascript复制Sub arraytest()
Dim arr As Variant
arr = Array(1, 2, 3, 4, 5, 6, 7)
Range("A4:A11").Value = Application.WorksheetFunction.Transpose(arr)
End Sub其中transpose是对数组进行列转换。数组内容如果不够填充单元格长度，超出的数据会以NA值填充。若需要填充的单元格数目不够数组长度，那么会按照顺序依次填充。
数组的存取
当将Excel表中的数据传递给数组时，默认建立的是一个二维数组，因此在取数组的值时，需要传递两个数值进去，如果只传入一个数组，会出现下标越界的警告。数组的第一个数指定行，第二个数指定列
代码语言：javascript复制arr = range("B1:C1").value
msgbox arr(1,2)特殊数据的专用容器——常量
常量常常用来存储一些固定不变的数据，如利率，税率和圆周率等。**声明常量时，英同时定义常量的名称，可存储的数据类型以及存储在其中的数据。语句为：
代码语言：javascript复制const 常量名称 as 数据类型 = 存储在常量中的数据同定义变量一样，在过程内部使用const语句定义的常量称为本地常量，只可以在声明常量的过程中使用；
如果在模块的第一个过程之前使用const语句声明常量，该常量被称为模块级常量，该模块中的所有过程都可以使用它；
如果想让声明的常量在所有模块中都可以使用它，那么应该在模块的第一个过程之前使用public语句将它声明为公共常量。
对象，集合及对象的属性和方法
对象就是东西，使用VBA代码操作和控制的东西，属于名词。在VBA中，Excel的工作簿，工作表，单元格等都是对象，图表，透视表，图片等也都是对象，甚至于单元格的边框线，插入的批注都是对象…
集合也是对象，它是对多个相同类型对象的统称。
每个对象都有属性，对象的属性可以理解为这个对象包含的内容或者具有的特征。对象和属性是相对而言的。单元格相对于字体来说的对象，但是单元格相对于工作表而言是属性
方法是在对象上执行的某个动作或者操作，每个对象都有其对应的一个或者多个方法。方法和属性的区别是属性返回对象包含的内容或者具有的特点，如子对象、颜色、大小等；方法是对对象的一种操作，如选中，激活等
VBA中的运算符
算数运算符
运算符 | 作用 | 示例 |
|---|---|---|
| 求两个数的和 | 5 9=14 |
– | 求两个数的差，或者求一个数的相反数 | 8-3=5 |
* | 求两个数的积 | |
/ | 求两个数的商 | |
求连个数相处后所得的商的整数 | 52=2 | |
^ | 求某个数的次方 | |
Mod | 求两个数相除后的余数 | 12 mod 9 =3 |
比较运算符
运算符 | 作用 | 语法 | 返回结果 |
|---|---|---|---|
= | 比较两个数据是否相等 | expression1=expression2 | 相等返回TRUE，不相等返回false |
<> | 不等于 | expression1<> expression2 | 与上相反 |
> | 比较两个数的大小 | expression1> expression2 | |
>= | 比较两个数的大小 | expression1>= expression2 | |
< | 比较两个数的大小 | expression1<expression2 | |
<= | 比较两个数的大小 | expression1<=expression2 | |
is | 比较两个对象的引用变量 | 对象1 is 对象2 | 当对象1和对象2 引用相同的对象时返回TRUE，否则返回false |
like | 比较两个字符串是否匹配 | 字符串1 like 字符串2 | 当字符串1与字符串2匹配时返回TRUE，否则返回false |
通配符
通配符 | 作用 | 代码举例 |
|---|---|---|
* | 代替任意多个字符 | “李家俊” like “李*” |
? | 代替任意单个字符 | “李家俊” like “李??” |
# | 代替任意单个数字 | “商品5” like “商品#” |
[charlist] | 代替位于charlist中的任意一个字符 | “I” like “[A-Z]” |
[!charlist] | 代替不位于charlist中的任意一个字符 | “I” like “[!A-Z]” |
逻辑运算符
运算符 | 作用 | 语句形式 | 计算规则 |
|---|---|---|---|
and | 执行逻辑“与”运算 | 表达式1 and 表达式2 | 当表示式1和表达式2的值都为TRUE时，返回TRUE，否则返回false |
or | 执行逻辑 “或”运算 | 表达式1 or 表达式2 | 二者之一为真返回TRUE，同时为false，返回false |
not | 执行逻辑“非”运算 | not 表示 | 取反运算 |
xor | 执行逻辑“异或”运算 | 表达式1 xor 表达式2 | 当表达式1和表达式2返回的值不相同时返回TRUE，否则返回false |
eqv | 执行逻辑“等价”运算 | 表达式1 eqv 表达式2 | 当表达式1和表达式2返回的值相同时返回TRUE，反之false |
Imp | 执行逻辑“蕴含”运算 | 表达式1 imp 表达式2 | 当表示1的值为TRUE，表达式2的值为false时返回false，否则返回TRUE |
VBA内置函数
函数虽然很多，但是我们不需要很精确的记住它们。**如果记得某个函数大致拼写，在编写代码时只要在【代码窗口】中输入“VBA.”，就可以在系统显示的函数列表中选择需要使用的函数。
执行程序执行的基本语句结构
if语句
在VBA中，if语句的规则如下：
代码语言：javascript复制if 条件 then 语句 else 条件
select case语句
尽管使用if语句可以解决“多选一”的问题，但当判断的选择条件过多时，使用多个elseif语句或多个if语句，就像一句话里用了太多的如果，会为理解代码逻辑带来困难。通常，当需要在三种或以上的策略中做出选择时，我们会选择使用select case 语句来解决问题
代码语言：javascript复制Sub test()
Select Case Range("B2").Value
Case Is >= 90
Range("C2").Value = "优秀"
Case Is >= 80
Range("C2").Value = "良好"
Case Is >= 60
Range("C2").Value = "及格"
Case Is < 60
Range("c1").Value = "不及格"
End Select
End Subfor 循环
在VBA中定义for循环的语法规则如下：
代码语言：javascript复制for 循环变量=初值 to 终值 step 步长值
循环体
next 循环变量名for循环都要以next结尾
代码语言：javascript复制Sub test()
Dim irow As Byte
Dim i As Byte
For i = 1 To 10 Step 1
Select Case Range("B" & i).Value
Case Is > 100
Range("C" & i).Value = "信息错误"
Case Is >= 90
Range("C" & i).Value = "优秀"
Case Is >= 80
Range("C" & i).Value = "良好"
Case Is >= 60
Range("C" & i).Value = "及格"
Case Is < 60
Range("C" & i).Value = "不及格"
End Select
Next i
End Sub
for each…next语句循环处理集合或数组中的成员
当需要循环处理一个数组的每个元素或者集合中的每个成员时，使用for each……next语句
代码语言：javascript复制Sub test()
Dim i As Byte
j = 1
For Each sht In Worksheets
Range("D" & j).Value = sht.Name
j = j 1
Next sht
End Subdo while循环
do while语句可以分为两种，按照设置循环条件的位置区分，可以分为开头判断式和截尾判断式。其语句结构如下：
- 开头判断式
do [while 循环条件]
循环体
exit do
循环体
loop- 截尾判断式
do
循环体
exit do
循环体
loop [while 循环条件]每个do语句都必须以loop结尾，当循环进行到loop处时，会重新回到do语句判断条件是否成立
代码语言：javascript复制Sub test()
Dim i As Byte
i = 1
Do
Worksheets.Add
i = i 1
Loop While i < 5
End Sub使用Goto语句，让程序转到另一条语句去执行
要让goto语句清楚的知道要转向的目标语句，可在目标语句之前加上一个带冒号的文本字符创，或者不带带冒号的数字标签，然后在goto的后面写上标签名
代码语言：javascript复制Sub test()
Dim i As Integer
Dim sum As Long
i = 1
x: mysum = mysum i
i = i 1
If i <= 100 Then GoTo x
MsgBox "1到100的和为：" & mysum
End Sub
with语句，简写代码
当需要对相同的对象进行多次操作时，往往会编写一些重复的代码。如果不想重复多次的录入相同的语句，可以用with语句简化
代码语言：javascript复制Sub fontest()
With Worksheets("Sheet1").Range("A1").Font
.Name = "仿宋"
.ColorIndex = 3
.Bold = True
.Size = 12
End With
End Sub
sub过程，基本的程序单元
sub过程的基本结构
代码语言：javascript复制[private|public|static] sub 过程名([参数列表])
语句块
[exit sub]
语句块
end subprivate或public用来声明过程的作用域，同时只能使用一个，可以省略，如果省略，默认使用public。如果选用static，过程执行结束后，会继续保存过程中变量的值
在过程中调用过程
- 直接使用过程名调用过程，过程名与参数之间用英文逗号隔开
subname,arg1,arg2
sub runsub()
subadd
end sub- 使用call关键字调用过程，参数写在过程小括号中，不同参数之间用逗号隔开
call 过程名(args,arg2)
sub runsub()
call subadd
end sub- 使用application对象的run方法调用过程
application.run "subname,arg1,arg2"
sub runsub()
application.run "subadd"sub过程中的参数传递
在VBA中，过程的参数传递主要有两种形式：按引用传递和按值传递。默认情况下，过程是按照引用的方式传递参数的。如果程序通过引用的方式传递参数，只会传递保存数据的内存地址，在过程中对参数的任何修改都会影响原始的数据。
代码语言：javascript复制Sub shtadd(shtcount As Integer)
Worksheets.Add Count:=shtcount
shtcount = 8
MsgBox "shtcount的值：" & shtcount
End Sub
Sub test()
Dim c As Integer
c = 2
Call shtadd(c)
MsgBox "参数过程中的值为：" & c
End Sub自定义函数，function过程
声明一个自定义函数
代码语言：javascript复制public function 函数名([参数])
函数体
函数名= 结果
end function无论function过程包含多少代码，要执行多少计算，都应该将最后的计算结果保存在过程名称中，这相当于其他语言中的函数return内容
使用自己定义的函数
- 在Excel中使用： 如果定义的函数没有被定义为私有过程，那么我们可以通过【插入函数】在Excel中使用我们自定义的函数。
Public Function fun()
fun = Int(Rnd() * 10) 1
End Function
- 在VBA过程中使用
Sub test()
MsgBox fun()
End Sub
function example
代码语言：javascript复制Public Function count_color(arr As Range, c As Range)
Dim rng As Range
For Each rng In arr
If rng.Interior.Color = c.Interior.Color Then
count_color = 1 count_color
End If
Next rng
End Function设置函数为易失性函数，让自定义函数也能重复计算
有时，当工作表重新计算后，自定义函数并不会重新计算。如果想让工作表重新计算后，自定义的函数也能随之重新计算，就应该讲自定义函数定义为易失性函数。要将一个自定义函数定义为易失性函数，只需要在function过程开始时添加下面一行代码：
代码语言：javascript复制application.voliatile trueexample
代码语言：javascript复制Public Function fun()
application.voliatile true
fun = Int(Rnd() * 10) 1
End Function操作对象
VBA中常用的对象
对象 | 对象说明 |
|---|---|
Application | 代表Excel应用程序（如果在word中使用VBA，就代表word应用程序） |
Workbook | 代表Excel工作簿，一个workbook对象代表一个工作簿文件 |
worksheet | 代表Excel的工作表，一个worksheet对象代表工作簿中的一个普通工作表 |
range | 代表Excel中的单元格，可以是单个单元格，也可以是单元格区域 |
application对象操作
使用ScreenUpdating属性设置更新屏幕
application对象的ScreenUpdating属性是控制屏幕更新的开关。如果设置其为false，那么屏幕将不会更新，我们将不会看到每一步的执行结果
代码语言：javascript复制Sub test()
Application.ScreenUpdating = False
Range("A1:A10").Value = 10
MsgBox "刚才输入的结果是10，你看到了吗？"
Range("A1:A10").Value = 100
MsgBox "刚才的输入是100，你看到了吗"
Application.ScreenUpdating = True
End Sub设置DisplayAlerts属性禁止显示警告对话框
当我们在Excel中执行某些操作时，Excel会显示一个警告框，让我们确定是否执行这些操作。由于很多原因，我们都希望Excel在程序执行中不显示类似的警告对话框，这样可以通过设置application对象的displayalerts属性为false来实现
代码语言：javascript复制Sub sheettest()
Worksheets.Add Count:=5
Application.DisplayAlerts = False
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name <> ActiveSheet.Name Then
sht.Delete
End If
Next sht
Application.DisplayAlerts = True
End Sub借助worksheetfunction属性使用工作表函数
VBA中有许多的内置函数，合理使用函数可有效减少工作中的许多难题，减少编写代码的工作量。但是VBA中没有Excel的内置函数，使用worksheetfunction可以调用Excel中的内置函数。
代码语言：javascript复制Sub cunt()
Dim mycount As Integer
Range("A1:B10").Value = 101
mycount = Application.WorksheetFunction.CountIf(Range("A1:B10"), ">100")
MsgBox "这片区域大于100的单元格是：" & mycount
End Sub如果VBA中已经有了相同功能的函数，就不能再通过worksheetfunction属性引用工作表中的函数。并且并不是所有的工作表函数都可以通过worksheetfunction属性来调用
设置属性，更改Excel的工作界面
设置application对象的属性来修改Excel的界面
在【立即窗口】执行的代码 | 修改的区域 |
|---|---|
application.caption = “我的Excel” | 标题栏 |
application.caption “miscrosoft excel” | 标题栏 |
application.displayformulabar = false | 编辑栏 |
application.displaystatusbar = false | 状态栏 |
application.statusbar = “正在编辑。。。。” | 状态栏 |
application.statusbar = false | 状态栏 |
activewindow.displayheadings = false | 行标和列标 |
application的子对象
application对象的常用属性
属性 | 返回的对象 |
|---|---|
ActiveCell | 当前活动单元格 |
ActiveChart | 当前活动工作簿中的活动图表 |
Activesheet | 当前活动工作簿中的活动工作表 |
ActiveWindow | 当前活动窗口 |
ActiveWorkbook | 当前活动工作簿 |
Charts | 当前活动工作簿中的所有的图表工作表 |
selection | 当前活动工作簿中所有选中的对象 |
sheets | 当前活动工作簿中的所有sheet对象，包括普通工作表，图表工作表，Excel4.0宏工作表和5.0对话框工作表 |
worksheets | 当前活动工作簿的所有worksheet对象（普通工作表） |
workbooks | 当前所有打开的工作簿 |
workbook对象
引用workbook对象
- 通过文件索引引用
- 通过文件名引用
sub test()
workbooks(3)
workbooks("sheet1")
end sub访问workbook对象属性
代码语言：javascript复制Sub info()
Range("c1") = ThisWorkbook.Name
Range("C2") = ThisWorkbook.Path
Range("C3") = ThisWorkbook.FullName
End Sub创建工作簿——add
- 创建空白工作簿：如果直接调用workbook对象的add方法，而不设置任何参数，excel将创建一个只含普通工作表的新工作簿
- 指定用来创建工作簿的模板： 如果想将某个工作簿文件作为新建工作簿的模板，可以使用add方法的template参数指定该文件的名称及其所在的所在目录
- 指定新建工作簿包含的工作簿类型
workbooks.add
workbooks.add template:="D:filetemplate.xlsm"
workbooks.add template := xlWBATChart '让新建的工作簿包含图表工作表用add方法的参数指定新建的工作簿包含的工作表类型
参数值 | 工作簿包含的工作表类型 |
|---|---|
xlWBATWorksheet | 普通工作表 |
xlWBATChart | 图表工作表 |
xlWBATExcel4Macrosheet | 4.0宏工作表 |
xlWBATExcel4IntlMacrosheet | 5.0对话框工作表 |
用open方法打开工作簿
代码语言：javascript复制workbooks.open filename := "path"activate激活工作簿
代码语言：javascript复制workbooks("workbooks_name").activate保存工作簿
- save方法保存已存在的文件
- saveas方法将工作簿另存为新文件
- 另存为新文件后不关闭原文件
thisworkbooks.save
thisworkbooks.saveas filename:="path"
thisworkbooks.savecopyas filename :="path"close——关闭工作簿
代码语言：javascript复制workbooks.close '关闭当前打开的所有工作簿
workbooks("workbooks_name").close '关闭指定名称的工作簿
workbooks.close savechanges := true '关闭并保存对工作簿的修改worksheet对象
add方法新建工作表
- 在活动工作表前插入一张工作表
- 用before|after参数指定要插入工作表的位置
- 用count 参数指定要插入的工作表数量
worksheets.add
worksheets.add before|after := worksheet_name
worksheets.add count:=number设置name属性，更改工作表的标签名称
代码语言：javascript复制worksheets("worksheet_name").name = name用delete方法删除工作表
代码语言：javascript复制worksheets('worksheet_Name').delete激活工作表的两种方法
代码语言：javascript复制worksheets("worksheet_name").avtivate
worksheets("worksheet_name").select用copy方法复制工作表
- 将工作表复制到指定位置
- 将工作表复制到新工作簿中
worksheets('worksheet_name').copy before|after :=worksheet_name
worksheets("worksheet_name").copy使用move移动工作表
- 将工作表移动到指定位置
- 将工作表移动到新工作簿中
worksheets('worksheet_name').move before|after :=worksheet_name
worksheets("worksheet_name").move设置visible属性，隐藏或者显示工作表
代码语言：javascript复制worksheets("worksheet_name").visible =False or True访问count属性，获得工作簿中工作表的数目
代码语言：javascript复制worksheets.countrange对象
用range属性引用单元格
- 引用单个固定的单元格区域：这种方法实际上就是通过单元格地址来引用单元格
- 引用多个不连续的单元格：将range属性的参数设置为一个用逗号分成多个单元格地址组成的字符串
- 引用多个区域的公共区域：将range属性设置成为一个用空格分割的多个单元格地址组成的字符串
- 引用两个区域围成的矩形区域
range("A1:C1")
range("A1:A10,E6,E7:C12").select
range("B1:B10 A4:D6").value
range("B6:B10","D2:D8")用cell属性引用单元格
- 引用工作表中指定行列交叉的单元格
- 引用单元格区域中的某个单元格
- 将cells属性的返回结果设置为range属性的参数
- 使用索引号引用单元格
activesheet.cells(3,4) ‘选中第三行和第四列的交叉单元格D3’
range("B3:F9").cells(2,3)=10 '在B3:F9区域的第2行与第3列交接处输入100'
range(cells(1,1),cells(5,14)).select '指定A1：D5区域'
activesheet.cells(2).value=200 '指定工作表的第二个单元格为200'引用整行单元格
在VBA中，rows表示工作表或某个区域中所有行组成的集合，要引用工作表汇总指定的行，可以使用行号或者索引号两种方式
代码语言：javascript复制activesheet.rows("3:3").select '选中活动工作表中的第三行'
activesheet.rows("3:5").select '选中活动工作表的3-5行'
activesheet.rows(3) '选中活动工作表的第三行'引用整列单元格
在VBA中，columns表示工作表或某个区域中所有行组成的集合，要引用工作表汇总指定的行，可以使用行号或者索引号两种方式
代码语言：javascript复制activesheet.columns("F:G").select '选中活动工作表中的第F-G列'
activesheet.columns(3) '选中活动工作表的第6列'使用union方法合并多个单元格区域
application对象的union方法返回参数指定的多个单元格区域的合并区域，使用该方法可以将多个range对象组合在一起，进行批量操作。
代码语言：javascript复制application.union(range("A1:A10"),range("D1:D5")).select '同时选中两个区域'range对象的offset属性
使用offset属性，可以获得相对于指定单元格区域一定偏移量位置上的单元格区域。offset有两个参数，分别用来设置该属性的父对象在上下或者左右方向上偏移的行列数
代码语言：javascript复制range("B2:C3").offset(5,3).value=200 '将B2:C3区域右移3个单元格，下移5个单元格'range对象的resize属性
使用range对象的resize属性可以将指定的单元格区域有目的地扩大或者缩小，得到一个新的单元格区域。
代码语言：javascript复制range("B2").resize(4,5) '将B2区域扩大成了一个4行5列的区域'
range("B2:E6").resize(2,1) '将原区域缩小为一个两行一列的区域'worksheet对象的usedrange属性
worksheet对象的usedrange属性返回工作表中已经使用的单元格围城的矩形区域.usedrange属性返回的总是一个矩形区域，无论这些区域是否存在空行，空列或者空单元格
代码语言：javascript复制activesheet.usedrange.select '选中活动工作表中已经使用的单元格区域'range对象的currentregion属性
range对象的currentregion属性返回包含指定单元格在内的一个连续的矩形区域,空行及下面的区域以及空列及右面的区域不包含在currentregion区域内
代码语言：javascript复制range("B5").currentregion.selectrange对象的end属性
range对象的end属性返回包含指定单元格的区域最尾端的单元格，返回结果等同于在单元格中按【enter 方向键】得到的单元格
代码语言：javascript复制range("C5").end(xlUP).addressend参数及说明
可设置的参数 | 参数说明 |
|---|---|
xlToLeft | 等同于在单元格中按【end 左方向键】 |
xlToRight | 等同于在单元格中按【end 右方向键】 |
xlUp | 等同于在单元格中按【end 上方向键】 |
xlDown | 等同于在单元格中按【end 下方向键】 |
value属性——单元格中的内容
代码语言：javascript复制range("A1:B2").value="abc"count属性，获得区域中包含的单元格个数
代码语言：javascript复制range("B4:F10").count
range("B4:F10").rows.count
range("B4:F10").columns.count通过address属性获取单元格地址
代码语言：javascript复制msgbox "当前选中单元格的地址为"&selection.address用activate和select方法选中单元格
代码语言：javascript复制activesheet.range("A1:F5").activate
activesheet.range("A1:F5").selectcopy方法复制单元格区域
无论复制多少单元格，destination参数只需要指定左上角单元格坐标就好
代码语言：javascript复制range('region').copy destination：=range("other_region")cut方法剪切单元格区域
无论剪切多少单元格，destination参数只需要指定左上角单元格坐标就好
代码语言：javascript复制range('region').cut destination:=range("other_region")用delete方法删除指定的单元格
调用range对象的delete方法可以删除指定的单元格，但与手动删除单元格不同，通过VBA代码删除单元格，excel不会显示【删除】对话框。想让excel在删除指定的单元格后，按自己的意愿处理其他单元格，我们需要编写VBA代码将自己的意图告诉excel。如想删除B3所在的整行单元格，应将代码写为：
代码语言：javascript复制range("B3").entirerow.delete操作对象的一些例子
根据需求创建工作簿
利用VBA创建一个符合自己需求的工作簿，并将其保存到指定的目录中
代码语言：javascript复制Sub wbadd()
Dim wb As Workbook
Dim sht As Worksheet
Set wb = Workbooks.Add
Set sht = wb.Worksheets(1)
With sht
.Name = "员工花名册"
End With
wb.SaveAs ThisWorkbook.Path & "员工花名册.xlsx"
ActiveWorkbook.Close
End Sub判断某个工作簿是否已经打开
代码语言：javascript复制Sub isopen()
Dim i As Integer
For i = 1 To Workbooks.Count
If Workbooks(i).Name = "成绩表.xlsx" Then
MsgBox "成绩表文件已打开"
Exit Sub
End If
Next
MsgBox "文件未打开"
End Sub判断文件夹中是否存在指定名称的工作簿文件
代码语言：javascript复制Sub isexist()
Dim fil As String
fil = ThisWorkbook.Path & "员工花名册.xlsx"
If Len(Dir(fil)) > 0 Then
MsgBox "文件存在"
Else
MsgBox "文件不存在"
End If
End Sub向未打开的工作簿中输入数据
代码语言：javascript复制Sub wbinput()
Dim wb As String, xrow As Integer, arr
wb = ThisWorkbook.Path & "员工花名册.xlsx"
Workbooks.Open (wb)
With ActiveWorkbook.Worksheets(1)
xrow = .Range("A1").CurrentRegion.Rows.Count 1
arr = Array(xrow - 1, "马军", "男", #7/8/1987#, #9/1/2010#, "10年新招")
.Cells(xrow, 1).Resize(1, 6) = arr
End With
ActiveWorkbook.Close savechanges:=True
End Sub隐藏活动工作表外的所有工作表
代码语言：javascript复制Sub shtvisible()
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name <> ActiveSheet.Name Then
sht.Visible = xlSheetVeryHidden
End If
Next
End Sub批量新建指定名称的工作表
代码语言：javascript复制Sub shtadd()
Dim i As Integer
Dim sht As Worksheet
i = 1
Set sht = Worksheets("Sheet11")
Do While sht.Cells(i, "E") <> ""
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = sht.Cells(i, "E").Value
i = i 1
Loop
End Sub批量对数据进行分离，并保存到不同的工作表中
代码语言：javascript复制Sub fenlie()
Dim i As Long, bj As String, rng As Range
i = 1
bj = Worksheets("Sheet11").Cells(i, "C").Value
Do While bj <> ""
Set rng = Worksheets(bj).Range("A1048576").End(xlUp).Offset(1, 0)
Worksheets("Sheet11").Cells(i, "A").Resize(1, 7).Copy rng
i = i 1
bj = Worksheets("Sheet11").Cells(i, "C").Value
Loop
End Sub将多张工作表中的数据合并到一张工作表中
代码语言：javascript复制Sub hebing()
Dim sht As Worksheet
Set sht = Worksheets("Sheet11")
sht.Rows.Clear
Dim wt As Worksheet, xrow As Integer, rng As Range
For Each wt In Worksheets
If wt.Name <> "Sheet11" Then
Set rng = sht.Range("A1048576").End(xlUp)
xrow = wt.Range("A1").CurrentRegion.Rows.Count
wt.Range("A1").Resize(xrow, 7).Copy rng
End If
Next
End Sub将工作簿中的每张工作表都保存为单独的工作簿文件
代码语言：javascript复制Sub savetofile()
Application.ScreenUpdating = False
Dim folder As String
folder = ThisWorkbook.Path & "班级成绩表"
If Len(Dir(folder, vbDirectory)) = 0 Then
MkDir folder
End If
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name <> "Sheet11" Then
sht.Copy
ActiveWorkbook.SaveAs folder & "" & sht.Name & ".xlsx"
ActiveWorkbook.Close
End If
Next
Application.ScreenUpdating = True
End Sub将多个工作簿中的数据合并到同一张工作表中
代码语言：javascript复制Sub hzwb()
Dim bt As Range, r As Long, c As Long
r = 1
c = 7
Dim wt As Worksheet
Set wt = ThisWorkbook.Worksheets(1)
wt.Rows(r & ":1045876").ClearContents
Application.ScreenUpdating = False
Dim filename As String, sht As Worksheet, wb As Workbook
Dim erow As Long, fn As String, arr As Variant
filename = Dir(ThisWorkbook.Path & "*.xlsx")
Do While filename <> ""
If filename <> ThisWorkbook.Name Then
erow = wt.Range("A1").CurrentRegion.Rows.Count
fn = ThisWorkbook.Path & "" & filename
Set wb = GetObject(fn)
Set sht = wb.Worksheets(1)
arr = sht.Range(sht.Cells(r, "A"), sht.Cells(1048576, "B").End(xlUp).Offset(0, 5))
wt.Cells(erow, "A").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
wb.Close savechanges:=True
End If
filename = Dir
Loop
Application.ScreenUpdating = True
End Sub为同一工作簿中的工作表建一个带链接的目录
代码语言：javascript复制Sub mulu()
Dim wt As Worksheet
Set wt = Worksheets("Sheet11")
wt.Rows("1:1048576").ClearContents
Dim sht As Worksheet, irow As Integer
irow = 1
For Each sht In Worksheets
If sht.Name <> "Sheet11" Then
wt.Cells(irow - 1, "A").Value = irow - 1
wt.Hyperlinks.Add anchor:=wt.Cells(irow - 1, "B"), Address:="", SubAddress:="'" & sht.Name & "'!A1", TextToDisplay:=sht.Name
End If
irow = 1 irow
Next
End Sub其中参数anchor指定建立超链接的位置，address指定超链接的地址，subaddress指定超链接的子地址，TexttoDisplay指定用于显示超链接的文字
执行程序的自动开关——对象的事件
让excel自动相应我们的操作
- 打开thisworkbook的代码窗口
- 选择workbook对象，在声明中选择open
- 编写子程序
当某个事件放生后自动运行的过程，称为事件过程，事件过程也是sub过程 。与sub过程不同的是，时间过程的作用域，过程名称及参数都不需要我们设置，也不能随意设置。时间过程的过程名称总是由对象名称及时间名称组成的，对象在前，事件在后，二者之间用下划线连接。
使用工作表事件
工作表事件就是发生在worksheet对象中的事件，一个工作簿中可能包含多个worksheet对象，而worksheet事件过程必须写在相应的worksheet中，只有过程所在的worksheet对象中的操作才能触发相应的事件。
worksheet对象的change事件
worksheet对象的change事件告诉VBA：当过程所在工作表中的单元格被更改时自动运行程序。
编写事件过程，通常我们都采用这种方式：依次在【代码窗口】的【对象】列表框和【事件】列表框中选择相应的对象及事件名称，让VBA自动替我们设置事件过程的作用域、过程名称以及参数信息
- 更改单元格时自动执行
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address & "被更改为" & Target.Value
End Sub- 更改部分单元格时自动执行
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then
Exit Sub
End If
MsgBox Target.Address & "被更改为" & Target.Value
End Sub禁用事件，让事件过程不再自动执行
禁用事件就是执行操作后不让事件发生。在VBA中，可以设置application对象的EnableEvents属性为false来禁用事件
代码语言：javascript复制Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Offset(0, 1).Value = "测试一下"
Application.EnableEvents = True
End Subselectionchange事件：当选中的单元格改变时发生
worksheet对象的selectionchange时间告诉VBA：当更改工作表中选中的单元格区域时自动执行该事件的事件过程。
代码语言：javascript复制Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "你当前所选中的单元格是：" & Target.Address
End Sub高亮选择区域的相同值
代码语言：javascript复制Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A3:I66").Interior.ColorIndex = xlNone
If Application.Intersect(Target, Range("A3:I66")) Is Nothing Then
Exit Sub
End If
If Target.Count > 1 Then
Set Target = Target.Cells(1)
End If
Dim rng As Range
For Each rng In Range("A3:I66")
If rng.Value = Target.Value Then
rng.Interior.ColorIndex = 6
End If
Next
End Sub用批注记录单元格中数据的修改情况
代码语言：javascript复制Dim rngvalue As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
End If
Dim cvalue As String
If Target.Formula = "" Then
cvalue = "空"
Else
cvalue = Target.Text
End If
If rngvalue = cvalue Then
Exit Sub
End If
Dim rngcom As Comment
Dim comstr As String
Set rngcom = Target.Comment
If rngcom Is Nothing Then Target.AddComment
comstr = Target.Comment.Text
Target.Comment.Text Text:=comstr & Chr(10) & Format(Now(), "yyyy-mm-ddhh:mm") & _
"原内容：" & rngvalue & "修改为：" & cvalue
Target.Comment.Shape.TextFrame.AutoSize = True
End SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Formula = "" Then
rngvalue = "空"
Else
rngvalue = Target.Text
End If
End Sub常用的worksheet事件
worksheet对象一共有17个时间，可以在【代码窗口】的【事件】列表框或VBA帮助中查看这些事件 常用的worksheet事件
事件名称 | 时间说明 |
|---|---|
activate | 激活工作表时发生 |
beforeDelete | 在删除工作表之前发生 |
beforeDoubleClick | 双击工作表之后，默认的双击操作之前发生 |
beforeRightClick | 右击工作表之后，默认的右击操作发生之前 |
calculate | 重新计算工作表之后发生 |
change | 工作表中的单元格发生更改时发生 |
deactivate | 工作表由活动工作表变为不活动工作表时发生 |
followHyperlink | 单击工作表中的任意超链接时发生 |
PivotTableUpdate | 在工作表中更新数据透视表后发生 |
selectionchange | 工作表中所选内容发生更改时发生 |
使用工作簿事件
工作簿事件是发生在workbook对象中的事件，一个workbook对象代表一个工作簿，workbook对象的事件过程必须写在ThisWorkbook模块中，可以在【工程资源管理器】中找到这个模块
open事件：当打开工作簿时发生
open事件是最常用的workbook事件之一，同国外吗会使用该事件对excel进行初始化设置，如设置想打开工作簿看到的excel窗口或工作界面，显示我们自定义的用户窗体等
beforeclose事件： 在关闭工作簿之前发生
代码语言：javascript复制private sub workbook_beforeclose(cancel as boolean)
if msgbox("你确定要关闭工作簿吗？",vbyesno) =vbno then
cancel =true
end if
end subsheetchange事件：更改任意工作表中的单元格时发生
代码语言：javascript复制Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "你正在更改的是：" & Sh.Name & "工作表中的" & Target.Address & "单元格"
End Sub常用的workbook事件
事件名称 | 事件说明 |
|---|---|
activate | 当激活工作簿时发生 |
AddinInstall | 当工作簿作为加载宏安装时发生 |
AddinUninstall | 当工作簿作为加载宏卸载时发生 |
AfterSave | 当保存工作簿之后发生 |
BeforeClose | 当关闭工作簿之前发生 |
BeforePrint | 在打印指定工作簿之前发生 |
beforesave | 在保存工作簿之前发生 |
Deavtivate | 在工作簿状态作为非活动状态时发生 |
NewChart | 在工作簿新建一个图表时发生 |
Newsheet | 在工作簿新建一个工作表时发生 |
open | 打开工作簿时发生 |
sheetavtivate | 激活任意工作表时发生 |
sheetBeforeDoubleClick | 在双击任意工作表之前发生 |
sheetBeforeRightClick | 在右击任意工作表之前发生 |
sheetCalculate | 在重新计算工作表之后发生 |
SheetChange | 当更改了任意工作表的单元格之后发生 |
sheetDeactivate | 当任意工作表转为非活动状态时发生 |
sheetFollowHyperLink | 当单击工作簿中的任意超链接时发生 |
SheetPivotTableUpdate | 当更新任意数据透视表时发生 |
sheetSelectionchange | 当任意工作表中的选定区域发生更改时发生 |
WindowActivate | 在激活任意工作簿窗口时发生 |
windowdeactivate | 当任意窗口变为不活动窗口时发生 |
windowresize | 在调整任意工作簿窗口大小时发生 |
不是事件的事件
application对象的onkey方法
OnKey方法告诉excel，当按下键盘上指定的键或者组合键时，自动执行指定的程序
代码语言：javascript复制Sub test()
Application.OnKey " e", "Hello"
End Sub
Sub Hello()
MsgBox "我在学习onkey方法"
End Sub在onkey中可以设置的按键及其对应代码
要使用的键 | 应设置的代码 |
|---|---|
Backspace | {backspace} or {BS} |
Break | {Break} |
Caps Lock | {CAPSLOCK} |
Delete or Del | {DELETE} OR {DEL} |
向下箭头 | {DOWN} |
End | {END} |
Enter(数字小键盘) | {ENTER} |
ENTER | ~ |
Esc | {ESCAPE} OR {ESC} |
Home | {HOME} |
Ins | {INSERT} |
向左箭头 | {LEFT} |
Num Lock | {NUMLOCK} |
PageDown | {PGDN} |
PageUp | {PGUP} |
向右箭头 | {RIGHT} |
Scroll Lock | {SCROLLLOCK} |
Tab | {TAB} |
向上箭头 | {UP} |
F1到F15 | {F1}到{F15} |
Shift |
|
Crtl | ^ |
Alt | % |
Application对象的OnTime方法
Ontime方法告诉VBA，在指定的时间自动执行指定的过程
代码语言：javascript复制Sub test()
Application.OnTime TimeValue("14:07:00"), "Hello"
End Sub
Sub Hello()
MsgBox "我在学习ontime方法"
End SubOntime的DateSerial参数可以设置指定的年月日；Scheduled的值如果为TRUE，会新设置一个Ontime过程，如果为False，就会清除之前设置的过程，默认值为TRUE
让文件自动保存
代码语言：javascript复制Sub test()
Application.OnTime Now() TimeValue("00:05:00"), "AutoSave"
End Sub
Sub AutoSave()
ThisWorkbook.Save
Call test
End Sub设置自定义的操作界面
控件，搭建操作界面必不可少的零件
excel中有两种类型的控件：表单控件和ActiveX控件。可以在excel的【功能区】中找到它们
- 表单控件
控件名称 | 控件说明 |
|---|---|
按钮 | 用于执行宏命令 |
组合框 | 提供可选择的多个选项，用户可以选择其中一个项目 |
复选框 | 用于选择的控件，可以多项选择 |
数值调节按钮 | 通过单击控件的箭头来选择数值 |
列表框 | 显示多个选项的列表，用户可以从中选择一个选项 |
选项按钮 | 用于选择的控件，通常几个选项按钮用组合框组合在一起使用，在一组中只能同时选择一个选项按钮 |
分组框 | 用于组合其他多个控件 |
标签 | 用于输入和显示静态文本 |
滚动条 | 包括水平滚动条和垂直滚动条 |
- ActiveX控件：excel中有11中可用的ActiveX控件，但是工作表总使用的ActiveX控件不止这些，可以单击其中的【其他控件】按钮，在弹出的对话框中选择其他控件
在工作表中使用控件
- 添加表单控件
2. 绘制表单控件
3. 设置相应区域
在工作表中使用ActiveX控件
- 添加ActiveX控件
2. 设置属性
- 编写控件代码
Private Sub xb2_Click()
If xb2.Value = True Then
Range("D2").Value = "女"
xbl.Value = False
End If
End Sub
Private Sub xbl_Click()
If xbl.Value = True Then
Range("D2").Value = "男"
xb2.Value = False
End If
End Sub- 显示内容
不需设置，使用现成的对话框
用InputBox函数创建一个可输入数据的对话框
Input函数共有5个参数：
- prompt参数用于设置在对话框中显示的提示消息
- title用于设置对话框的标题
- default用于设置默认的输入值
- xpos用于设置对话框与左端与屏幕左端的距离
- ypos用于设置对话框的顶端与屏幕顶端的距离
Sub inbox()
Dim c As Variant
c = InputBox("你要在A1单元格输入什么数据？", Title:="提示", Default:="叶飞", xpos:=200, ypos:=250)
Range("A1").Value = c
End Sub
用input方法创建交互对话框
用application对象的inputbox方法也可以创建于程序互动的对话框，但要注意与Inputbox函数相比，Input方法的参数有些许不同
代码语言：javascript复制application.inputbox(prompt:="内容",title:= “标题”
default := "默认值", left := "与屏幕左端距离"
top := “与屏幕右端距离”, type := "输入数据的类型")type参数的可设置项及说明
可设置的参数值 | 方法返回结果的类型 |
|---|---|
0 | 公式 |
1 | 数字 |
2 | 文本（字符串） |
4 | 逻辑值（true or false） |
8 | 单元格引用（range对象） |
16 | 错误值 |
64 | 数值数组 |
Sub rngpinput()
Dim rng As Variant
On Error GoTo cancel
Set rng = Application.InputBox("请选择需要输入数值的单元格", Type:=8)
rng.Value = 100
cancel:
End Sub使用msgbox函数创建输出对话框
代码语言：javascript复制Sub msg()
MsgBox "你正在编辑的是：" & ThisWorkbook.Name, Buttons:=vbOKOnly vbInformation, Title:="提示"
End Sub
设置在对话框中显示的按钮样式
常数 | 值 | 说明 |
|---|---|---|
vbOkonly | 0 | 只显示【确定】按钮 |
vbOkcancel | 1 | 只显示【确定】和【取消】按钮 |
vbAbortRetryIgnore | 2 | 显示【终止】，【重试】，【忽略】三个按钮 |
vbYesNoCancel | 3 | 显示是，否，取消三个按钮 |
vbYesNo | 4 | 显示是，否两个按钮 |
vbRetryCancel | 5 | 显示重试，取消两个按钮 |
不同图标的参数设置
常数 | 值 | 说明 |
|---|---|---|
vbCritical | 16 | 显示【关键信息】图标 |
vbQuestion | 32 | 显示【警告询问】图标 |
vbExclamation | 48 | 显示【警告消息】图标 |
vbInformation | 64 | 显示【通知消息】图标 |
设置对话框中的默认按钮 常数|值|说明 vbDefaultButton1 | 0 | 第一个按钮为默认值 vbDefaultButton2 | 256 | 第二个按钮为默认值 vbDefaultButton3 | 512 | 第三个按钮为默认值 vbDefaultButton4 | 768 | 第四个按钮为默认值
指定对话框的类型
常数 | 值 | 说明 |
|---|---|---|
vbApplicationModel | 0 | 应用程序强制返回；应用程序暂停执行，直到用户消息框做出响应才继续 |
vbSystemModel | 4096 | 系统强制返回；全部应用程序都暂停执行，直到用户对消息框做出响应才继续工作 |
buttons参数的其他设置
常数 | 值 | 说明 |
|---|---|---|
vbMsgBoxHelpButton | 16384 | 在对话框中添加帮助按钮 |
vbMsgBoxForeground | 65536 | 设置显示的对话框窗口为前景窗口 |
vbMsgBoxRight | 524288 | 设置对话框中显示的文本为右对齐 |
vbMsgBoxRtlReading | 1048576 | 指定文本英在希伯来文和Alibaba系统中显示为从右到左阅读 |
MsgBox函数的返回值
常数 | 值 | 说明 |
|---|---|---|
vbOk | 1 | 单击【确定】按钮时 |
vbCancel | 2 | 单击【取消】按钮时 |
vbAbort | 3 | 单击【终止】按钮时 |
vbRetry | 4 | 单击【重试】按钮时 |
vbIgnore | 5 | 单击【忽略】按钮时 |
vbYes | 6 | 单击【是】按钮时 |
vbNo | 7 | 单击【否】按钮时 |
Sub msgbut()
Dim yn As Integer
yn = MsgBox("你确定要在A1单元格输入今天的日期吗？", vbYesNo vbQuestion)
If yn = vbYes Then
Range("A1").Value = Now()
End If
End Sub使用FindFile方法显示【打开】对话框
使用application对象的FindFile方法可以显示【打开】对话框，在对话框中选择并打开某个文件
代码语言：javascript复制Sub openfile()
If Application.FindFile = True Then
MsgBox "the file you chosed has benn open"
Else
MsgBox "你单击了【取消按钮】，操作未完成"
End If
End Sub用GetOpenFileName方法显示【打开】对话框
与findfile方法不同，使用GetOpenFileName方法是获得在对话框中选中的文件的文件名称（包含路径），而findfile是打开在对话框中选中的文件
- 选择任意文件
Sub getopen()
Dim fil
fil = Application.GetOpenFilename
If fil = False Then
MsgBox "没有任何文件"
Exit Sub
Else
Range("E6").Value = fil
End If
End Sub- 只在对话框中显示某种类型的文件
Sub getopen()
Dim fil
fil = Application.GetOpenFilename(filefilter:="图片文件,*.jpg")
If fil = False Then
MsgBox "没有任何文件"
Exit Sub
Else
Range("E6").Value = fil
End If
End Sub- 让对话框同时显示多种扩展名文件
Sub getopen()
Dim fil
fil = Application.GetOpenFilename(filefilter:="图片文件,*.jpg;*png")
If fil = False Then
MsgBox "没有任何文件"
Exit Sub
Else
Range("E6").Value = fil
End If
End Sub- 让对话框能选择显示多种类型的文件
Sub getopen()
Dim fil
fil = Application.GetOpenFilename(filefilter:="图片文件,*.jpg;*png,Excel工作簿文件,*xls;*xlsx")
If fil = False Then
MsgBox "没有任何文件"
Exit Sub
Else
Range("E6").Value = fil
End If
End Sub- 通过FilterIndex参数设置默认显示的文件类型 如果在【文件类型】下拉列表中设置了多种可选择的文件类型，就可以通过GetOpenFileName方法的FiterIndex参数，设置对话框中默认显示的文件类型
Sub getopen()
Dim fil
fil = Application.GetOpenFilename(filefilter:="图片文件,*.jpg;*png, Excel工作簿文件, *xls;*xlsx", FilterIndex:=2)
If fil = False Then
MsgBox "没有任何文件"
Exit Sub
Else
Range("E6").Value = fil
End If
End Sub- 设置允许同时选择多个文件 默认情况下，在通过GetOpenFileName方法显示的【打开】对话框中，只能同时选中一个文件，如果希望同时选中多个文件，可以将MultiSelect参数设置为TRUE
Sub getopen()
Dim fil
fil = Application.GetOpenFilename(filefilter:="图片文件,*.jpg;*png, Excel工作簿文件,*xls;*xlsx", FilterIndex:=2, MultiSelect:=True)
Range("E6").Value = fil
End Sub- 修改对话框标题
Sub getopen()
Dim fil
fil = Application.GetOpenFilename(filefilter:="图片文件,*.jpg;*png, Excel工作簿文件,*xls;*xlsx", FilterIndex:=2, MultiSelect:=True, Title:="请选择你要获取名称的文件")
Range("E6").Value = fil
End Sub用GetSaveAsFilename方法显示【另存为】对话框
要想获得选中的文件名称，还可以调用application对象的GetSaveAsFilename方法打开【另存为】对话框，在对话框文中选择文件，获得该文件包含路径信息的文件名称
代码语言：javascript复制Sub getsaveas()
Dim fil As String, filename As String, filter As String, tile As String
filename = "例子"
filter = "Excel工作簿,*xls;*xlsx,Word文档,*.doc;*.docx"
Title = "请选择要获取信息的文件"
fil = Application.GetSaveAsFilename(InitialFileName:=filename, fileFilter:=filter, Title:=Title, FilterIndex:=2)
Range("A10") = fil
End Sub使用application对象的FileDialog属性获取目录名称
如果想要获得的不是文件名，而是指定目录的路径及名称，可以使用application对象的FileDialog属性。
代码语言：javascript复制Sub getfolder()
With Application.FileDialog(filedialogtype:=msoFileDialogFilePicker)
.InitialFileName = "D:"
.Title = "请选择一个目录"
.Show
If .SelectedItems.Count > 0 Then
Range("A1").Value = .SelectedItems(1)
End If
End With
End SubmsoFileDialogType参数可以设置的常量
常量 | 说明 |
|---|---|
msoFileDialogFilePicker | 允许选择一个文件 |
msoFileDialogFolderPicker | 允许选择一个文件夹 |
msoFileDialogOpen | 允许打开一个文件 |
msoFileDialogSaveAs | 允许保存一个文件 |
使用窗体对象设置交互界面
很多时候，我们都希望自己能够设计一个交互界面，定义其中的控件及控件的功能，这就需要用到VBA中的另一类常用对象——Userform对象。一个用户窗体就是一个Userform对象，也就是大家常说的窗体对象。当在工程中添加一个窗体后，就可以在窗体上自由的添加ActiveX控件，只要通过编写VBA代码为这些控件指定功能，就能利用这些控件与excel互动
- 在工程中添加一个用户窗体
- 设置属性，改变窗体的外观
- 在窗体中添加和设置控件的功能
用代码操作自己设计的窗体
显示窗体
- 手动显示窗体
在VBE窗口中选中窗体，依次执行【运行】——>【运行子过程/窗体】命令，即可显示选中的窗体
- 在程序中用代码显示窗体
sub showform()
load inputform
inputform.show
end sub
将窗体显示为无模式窗体
- 模式窗体不能操作窗体之外的对象
要将窗体显示为模式窗体，可以使用代码
代码语言：javascript复制InputForm.show或者省略show方法的参数，或者将参数设置为vModal，VBA都会讲窗口见识为模式窗体 2. 无模式窗体允许进行窗体外的其他操作
要将窗体显示为无模式窗体，必须通过show方法制定参数
代码语言：javascript复制Inputform.show vmodeless如果将窗体显示为无模式窗体，当窗体显示后，系统会继续执行程序余下的代码，也允许我们操作窗体之外的其他对象
关闭或隐藏已显示的窗体
- 用unload命令关闭窗体
unload Inputform- 使用Hide方法隐藏窗体
inputform.hide用户窗体的事件应用
借助Initialize事件初始化窗体
Initialize事件发生在显示窗体之前，当我们在程序中使用load语句加载窗体，或者使用show显示窗体时，都会引发该时间
代码语言：javascript复制Private Sub UserForm_Initialize()
性别.List = Array("男", "女")
End Sub借助QueryClose事件让窗体自带的【关闭】按钮失效
代码语言：javascript复制Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> vbFormCode Then Cancel = True
End Subqueryclose事件过程是一个带两个参数的sub过程，其中的cancel参数确定是否响应我们关闭窗体的操作。当值为TRUE时，程序将不响应我们关闭窗体的操作。如果cancel的值为false，程序将关闭窗体。其中CloseMode参数是我们关闭窗体的方式，不同的关闭方式返回的值也不相同
CloseMode参数的返回值说明
常数 | 值 | 说明 |
|---|---|---|
vbFormControlMenu | 0 | 在窗体中单击【关闭】按钮关闭窗体 |
vbFormCode | 1 | 通过unload语句关闭窗体 |
vbAppWindows | 2 | 正在结束当前Windows操作环境的过程 |
vbAppTaskManagee | 3 | windows的【任务管理器】正在关闭整个应用 |
为窗体的控件设置功能
为【确定】按钮添加事件过程
代码语言：javascript复制Private Sub cmd_OK_Click()
Dim xrow As Long
xrow = Range("A1").CurrentRegion.Rows.Count 1
Cells(xrow, "A").Value = 姓名.Value
Cells(xrow, "B").Value = 性别.Value
Cells(xrow, "C").Value = birth.Value
姓名.Value = ""
性别.Value = ""
birth.Value = ""
End Sub给控件设置快捷键
给按钮设置了快捷键后，显示窗体时，当按下对应的快捷键就等于在窗体中用鼠标单击了该按钮。
代码语言：javascript复制Private Sub UserForm_Initialize()
性别.List = Array("男", "女")
cmd_OK.Accelerator = "N"
End Sub更改控件的Tab键顺序
只有对象被激活时，才能几首键盘输入。控件的Tab键顺序决定用户按下【Tab】或【Shift Tab】组合键后控件激活的顺序。在设计窗体时，系统会按照添加控件的先后顺序确定控件的Tab顺序。但是这个顺序是可以更改的。在VBE中选中窗体，依次执行【视图】——>【Tab键顺序】命令。调出Tab键顺序对话框，即可在其中调整控件的Tab键顺序
用窗体设计一个简易的登陆窗体
- 设计窗体显示
2. 添加代码，为控件指定功能
代码语言：javascript复制Private Sub cmd_cacel_Click()
Unload 用户登录
ThisWorkbook.Close savechanges:=False
End Sub
Private Sub cmd_ok_Click()
Application.ScreenUpdating = False
Static i As Integer
If CStr(username.Value) = Right(Names("username").RefersTo, Len(Names(username).RefersTo) - 1) And CStr(pwd.Value) = Right(Names("userword").RefersTo, Len(Names("userword").RefersTo) - 1) Then
Unload 用户登录
Else
i = i 1
If i = 3 Then
MsgBox "你无权打开工作簿"
ThisWorkbook.Close savechanges:=False
Else
msg "输入错误，你还有" & (3 - i) & "次机会输入"
username.Value = ""
pwd.Value = ""
End If
End If
Application.ScreenUpdating = True
End Sub
Private Sub pwd_set_Click()
Dim old As String, new1 As String, new2 As String
old = InputBox("请输入密码：", "提示")
If old <> Right(Names("userword").RefersTo, Len(Names("userword").referto) - 1) Then
MsgBox "原密码输入错误，不能修改", vbCritical, "错误"
Exit Sub
End If
new1 = InputBox("请输入新密码：", "提示")
If new1 = "" Then
MsgBox "新密码不能为空，修改没有完成", vbCritical, "错误"
Exit Sub
End If
new2 = InputBox("请再次输入新密码：", "提示")
If new1 = new2 Then
Names("userword").RefersTo = "=" & new1
ThisWorkbook.Save
MsgBox "密码修改完成"
Else
MsgBox "两次密码输入不一致，修改未完成", vbCritical, "错误"
End If
End Sub
Private Sub user_set_Click()
Dim old As String, new1 As String, new2 As String
old = InputBox("请输入用户名：", "提示")
If old <> Right(Names("username").RefersTo, Len(Names("username").referto) - 1) Then
MsgBox "原用户名输入错误，不能修改", vbCritical, "错误"
Exit Sub
End If
new1 = InputBox("请输入新用户名：", "提示")
If new1 = "" Then
MsgBox "新用户名不能为空，修改没有完成", vbCritical, "错误"
Exit Sub
End If
new2 = InputBox("请再次输入新用户名：", "提示")
If new1 = new2 Then
Names("username").RefersTo = "=" & new1
ThisWorkbook.Save
MsgBox "用户名修改完成"
Else
MsgBox "两次用户名不一致，修改未完成", vbCritical, "错误"
End If
End Sub调试与优化编写的代码
On Error GoTo标签
On Error GoTo 标签实际就是在“on error”的后面加了一个GoTo语句，其中的“标签”就是替goto语句设置的标签，是一个数字或者带冒号的文本。标签告诉VBA，当程序运行过程中晕倒运行时错误时，跳转到标签所在行的代码继续执行程序，实际上就是让程序跳过出错的代码，从另一个地方重新开始执行程序。
代码语言：javascript复制Sub test()
On Error GoTo a
Worksheets("abc").Select
Exit Sub
a: MsgBox "没有要选择的工作表"
End Subon error resume next
Resume Next告诉VBA，如果程序发生错误，则忽略存在错误的代码，接着执行错误行之后的代码。如果程序一开始加入On Error Resume Next语句，运行程序时，及时程序中存在运行时错误，VBA也不会中断程序，而是忽略所有存在错误的语句，继续执行出错语句后的代码
代码语言：javascript复制Sub test()
On Error Resume Next
Worksheets("abc").Select
Exit Sub
MsgBox "没有要选择的工作表"
End SubOn Error GoTo 0
使用On Error GoTo 0语句后，将关闭对程序中运行时错误的捕捉，如果程序在On Error GoTo 0语句后出现运行错误，将不会被捕捉到
代码语言：javascript复制Sub test()
On Error GoTo 0
Worksheets("abc").Select
Exit Sub
MsgBox "没有要选择的工作表"
End Sub发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/130408.html原文链接：https://javaforall.cn
