大家好,又见面了,我是你们的朋友全栈君。
文章目录
如何创建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 数据类型” - 给数组赋值就是给数组的每个元素分别赋值
声明多维数组
Dim arr(a,b) as Integer
这里是声明一个叫arr数组,其形状为a行b列
声明动态数组
当声明数组时,不知道要在这个数组中填入多少个数据时,可以使用动态数组。将数组声明为动态数组时,不能直接使用变量,需要之后使用redim 重新制定数组大小
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函数声明数组
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求数组的最大索引号
Sub arraytest()
Dim arr As Variant
Range("A1:C3").Value = 5
arr = Range("A1:C1").Value
MsgBox "数组的最大索引号是:" & UBound(arr)
End Sub
利用LBound函数求最小索引号
Sub arraytest()
Dim arr As Variant
Range("A1:C3").Value = 5
arr = Range("A1:C1").Value
MsgBox "数组的最小索引号是:" & LBound(arr)
End Sub
求多维数组的最大和最小索引号
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函数将一维数组合并成字符串
Sub arraytest()
Dim arr As Variant
arr = Array(1, 2, 3, 4)
Text = Join(arr)
MsgBox Text
End Sub
将数组内容写入工作表中
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表中的数据传递给数组时,默认建立的是一个二维数组,因此在取数组的值时,需要传递两个数值进去,如果只传入一个数组,会出现下标越界的警告。数组的第一个数指定行,第二个数指定列
arr = range("B1:C1").value
msgbox arr(1,2)
特殊数据的专用容器——常量
常量常常用来存储一些固定不变的数据,如利率,税率和圆周率等。**声明常量时,英同时定义常量的名称,可存储的数据类型以及存储在其中的数据。语句为:
const 常量名称 as 数据类型 = 存储在常量中的数据
同定义变量一样,在过程内部使用const语句定义的常量称为本地常量,只可以在声明常量的过程中使用;
如果在模块的第一个过程之前使用const语句声明常量,该常量被称为模块级常量,该模块中的所有过程都可以使用它;
如果想让声明的常量在所有模块中都可以使用它,那么应该在模块的第一个过程之前使用public语句将它声明为公共常量。
对象,集合及对象的属性和方法
对象就是东西,使用VBA代码操作和控制的东西,属于名词。在VBA中,Excel的工作簿,工作表,单元格等都是对象,图表,透视表,图片等也都是对象,甚至于单元格的边框线,插入的批注都是对象…
集合也是对象,它是对多个相同类型对象的统称。
每个对象都有属性,对象的属性可以理解为这个对象包含的内容或者具有的特征。对象和属性是相对而言的。单元格相对于字体来说的对象,但是单元格相对于工作表而言是属性
方法是在对象上执行的某个动作或者操作,每个对象都有其对应的一个或者多个方法。方法和属性的区别是属性返回对象包含的内容或者具有的特点,如子对象、颜色、大小等;方法是对对象的一种操作,如选中,激活等
VBA中的运算符
算数运算符
运算符 | 作用 | 示例 |
---|---|---|
+ | 求两个数的和 | 5+9=14 |
– | 求两个数的差,或者求一个数的相反数 | 8-3=5 |
* | 求两个数的积 | |
/ | 求两个数的商 | |
\ | 求连个数相处后所得的商的整数 | 5\2=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语句的规则如下:
if 条件 then 语句 else 条件
select case语句
尽管使用if语句可以解决“多选一”的问题,但当判断的选择条件过多时,使用多个elseif语句或多个if语句,就像一句话里用了太多的如果,会为理解代码逻辑带来困难。通常,当需要在三种或以上的策略中做出选择时,我们会选择使用select case 语句来解决问题
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 Sub
for 循环
在VBA中定义for循环的语法规则如下:
for 循环变量=初值 to 终值 step 步长值
循环体
next 循环变量名
for循环都要以next结尾
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语句
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 Sub
do while循环
do while语句可以分为两种,按照设置循环条件的位置区分,可以分为开头判断式和截尾判断式。其语句结构如下:
- 开头判断式
do [while 循环条件]
循环体
exit do
循环体
loop
- 截尾判断式
do
循环体
exit do
循环体
loop [while 循环条件]
每个do语句都必须以loop结尾,当循环进行到loop处时,会重新回到do语句判断条件是否成立
Sub test()
Dim i As Byte
i = 1
Do
Worksheets.Add
i = i + 1
Loop While i < 5
End Sub
使用Goto语句,让程序转到另一条语句去执行
要让goto语句清楚的知道要转向的目标语句,可在目标语句之前加上一个带冒号的文本字符创,或者不带带冒号的数字标签,然后在goto的后面写上标签名
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语句简化
Sub fontest()
With Worksheets("Sheet1").Range("A1").Font
.Name = "仿宋"
.ColorIndex = 3
.Bold = True
.Size = 12
End With
End Sub
sub过程,基本的程序单元
sub过程的基本结构
[private|public|static] sub 过程名([参数列表])
语句块
[exit sub]
语句块
end sub
private或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中,过程的参数传递主要有两种形式:按引用传递和按值传递。默认情况下,过程是按照引用的方式传递参数的。如果程序通过引用的方式传递参数,只会传递保存数据的内存地址,在过程中对参数的任何修改都会影响原始的数据。
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过程
声明一个自定义函数
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
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过程开始时添加下面一行代码:
application.voliatile true
example
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,那么屏幕将不会更新,我们将不会看到每一步的执行结果
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来实现
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中的内置函数。
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对象属性
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:\file\template.xlsm"
workbooks.add template := xlWBATChart '让新建的工作簿包含图表工作表
用add方法的参数指定新建的工作簿包含的工作表类型
参数值 | 工作簿包含的工作表类型 |
---|---|
xlWBATWorksheet | 普通工作表 |
xlWBATChart | 图表工作表 |
xlWBATExcel4Macrosheet | 4.0宏工作表 |
xlWBATExcel4IntlMacrosheet | 5.0对话框工作表 |
用open方法打开工作簿
workbooks.open filename := "path"
activate激活工作簿
workbooks("workbooks_name").activate
保存工作簿
- save方法保存已存在的文件
- saveas方法将工作簿另存为新文件
- 另存为新文件后不关闭原文件
thisworkbooks.save
thisworkbooks.saveas filename:="path"
thisworkbooks.savecopyas filename :="path"
close——关闭工作簿
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属性,更改工作表的标签名称
worksheets("worksheet_name").name = name
用delete方法删除工作表
worksheets('worksheet_Name').delete
激活工作表的两种方法
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属性,隐藏或者显示工作表
worksheets("worksheet_name").visible =False or True
访问count属性,获得工作簿中工作表的数目
worksheets.count
range对象
用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表示工作表或某个区域中所有行组成的集合,要引用工作表汇总指定的行,可以使用行号或者索引号两种方式
activesheet.rows("3:3").select '选中活动工作表中的第三行'
activesheet.rows("3:5").select '选中活动工作表的3-5行'
activesheet.rows(3) '选中活动工作表的第三行'
引用整列单元格
在VBA中,columns表示工作表或某个区域中所有行组成的集合,要引用工作表汇总指定的行,可以使用行号或者索引号两种方式
activesheet.columns("F:G").select '选中活动工作表中的第F-G列'
activesheet.columns(3) '选中活动工作表的第6列'
使用union方法合并多个单元格区域
application对象的union方法返回参数指定的多个单元格区域的合并区域,使用该方法可以将多个range对象组合在一起,进行批量操作。
application.union(range("A1:A10"),range("D1:D5")).select '同时选中两个区域'
range对象的offset属性
使用offset属性,可以获得相对于指定单元格区域一定偏移量位置上的单元格区域。offset有两个参数,分别用来设置该属性的父对象在上下或者左右方向上偏移的行列数
range("B2:C3").offset(5,3).value=200 '将B2:C3区域右移3个单元格,下移5个单元格'
range对象的resize属性
使用range对象的resize属性可以将指定的单元格区域有目的地扩大或者缩小,得到一个新的单元格区域。
range("B2").resize(4,5) '将B2区域扩大成了一个4行5列的区域'
range("B2:E6").resize(2,1) '将原区域缩小为一个两行一列的区域'
worksheet对象的usedrange属性
worksheet对象的usedrange属性返回工作表中已经使用的单元格围城的矩形区域.usedrange属性返回的总是一个矩形区域,无论这些区域是否存在空行,空列或者空单元格
activesheet.usedrange.select '选中活动工作表中已经使用的单元格区域'
range对象的currentregion属性
range对象的currentregion属性返回包含指定单元格在内的一个连续的矩形区域,空行及下面的区域以及空列及右面的区域不包含在currentregion区域内
range("B5").currentregion.select
range对象的end属性
range对象的end属性返回包含指定单元格的区域最尾端的单元格,返回结果等同于在单元格中按【enter+方向键】得到的单元格
range("C5").end(xlUP).address
end参数及说明
可设置的参数 | 参数说明 |
---|---|
xlToLeft | 等同于在单元格中按【end+左方向键】 |
xlToRight | 等同于在单元格中按【end+右方向键】 |
xlUp | 等同于在单元格中按【end+上方向键】 |
xlDown | 等同于在单元格中按【end+下方向键】 |
value属性——单元格中的内容
range("A1:B2").value="abc"
count属性,获得区域中包含的单元格个数
range("B4:F10").count
range("B4:F10").rows.count
range("B4:F10").columns.count
通过address属性获取单元格地址
msgbox "当前选中单元格的地址为"&selection.address
用activate和select方法选中单元格
activesheet.range("A1:F5").activate
activesheet.range("A1:F5").select
copy方法复制单元格区域
无论复制多少单元格,destination参数只需要指定左上角单元格坐标就好
range('region').copy destination:=range("other_region")
cut方法剪切单元格区域
无论剪切多少单元格,destination参数只需要指定左上角单元格坐标就好
range('region').cut destination:=range("other_region")
用delete方法删除指定的单元格
调用range对象的delete方法可以删除指定的单元格,但与手动删除单元格不同,通过VBA代码删除单元格,excel不会显示【删除】对话框。想让excel在删除指定的单元格后,按自己的意愿处理其他单元格,我们需要编写VBA代码将自己的意图告诉excel。如想删除B3所在的整行单元格,应将代码写为:
range("B3").entirerow.delete
操作对象的一些例子
根据需求创建工作簿
利用VBA创建一个符合自己需求的工作簿,并将其保存到指定的目录中
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
判断某个工作簿是否已经打开
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
判断文件夹中是否存在指定名称的工作簿文件
Sub isexist()
Dim fil As String
fil = ThisWorkbook.Path & "\员工花名册.xlsx"
If Len(Dir(fil)) > 0 Then
MsgBox "文件存在"
Else
MsgBox "文件不存在"
End If
End Sub
向未打开的工作簿中输入数据
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
隐藏活动工作表外的所有工作表
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
批量新建指定名称的工作表
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
批量对数据进行分离,并保存到不同的工作表中
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
将多张工作表中的数据合并到一张工作表中
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
将工作簿中的每张工作表都保存为单独的工作簿文件
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
将多个工作簿中的数据合并到同一张工作表中
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
为同一工作簿中的工作表建一个带链接的目录
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来禁用事件
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Offset(0, 1).Value = "测试一下"
Application.EnableEvents = True
End Sub
selectionchange事件:当选中的单元格改变时发生
worksheet对象的selectionchange时间告诉VBA:当更改工作表中选中的单元格区域时自动执行该事件的事件过程。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "你当前所选中的单元格是:" & Target.Address
End Sub
高亮选择区域的相同值
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
用批注记录单元格中数据的修改情况
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 Sub
Private 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事件: 在关闭工作簿之前发生
private sub workbook_beforeclose(cancel as boolean)
if msgbox("你确定要关闭工作簿吗?",vbyesno) =vbno then
cancel =true
end if
end sub
sheetchange事件:更改任意工作表中的单元格时发生
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,当按下键盘上指定的键或者组合键时,自动执行指定的程序
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,在指定的时间自动执行指定的过程
Sub test()
Application.OnTime TimeValue("14:07:00"), "Hello"
End Sub
Sub Hello()
MsgBox "我在学习ontime方法"
End Sub
Ontime的DateSerial参数可以设置指定的年月日;Scheduled的值如果为TRUE,会新设置一个Ontime过程,如果为False,就会清除之前设置的过程,默认值为TRUE
让文件自动保存
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方法的参数有些许不同
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函数创建输出对话框
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方法可以显示【打开】对话框,在对话框中选择并打开某个文件
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方法打开【另存为】对话框,在对话框文中选择文件,获得该文件包含路径信息的文件名称
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属性。
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 Sub
msoFileDialogType参数可以设置的常量
常量 | 说明 |
---|---|
msoFileDialogFilePicker | 允许选择一个文件 |
msoFileDialogFolderPicker | 允许选择一个文件夹 |
msoFileDialogOpen | 允许打开一个文件 |
msoFileDialogSaveAs | 允许保存一个文件 |
使用窗体对象设置交互界面
很多时候,我们都希望自己能够设计一个交互界面,定义其中的控件及控件的功能,这就需要用到VBA中的另一类常用对象——Userform对象。一个用户窗体就是一个Userform对象,也就是大家常说的窗体对象。当在工程中添加一个窗体后,就可以在窗体上自由的添加ActiveX控件,只要通过编写VBA代码为这些控件指定功能,就能利用这些控件与excel互动
- 在工程中添加一个用户窗体
- 设置属性,改变窗体的外观
- 在窗体中添加和设置控件的功能
用代码操作自己设计的窗体
显示窗体
- 手动显示窗体
在VBE窗口中选中窗体,依次执行【运行】——>【运行子过程/窗体】命令,即可显示选中的窗体
- 在程序中用代码显示窗体
sub showform()
load inputform
inputform.show
end sub
将窗体显示为无模式窗体
- 模式窗体不能操作窗体之外的对象
要将窗体显示为模式窗体,可以使用代码
InputForm.show
或者省略show方法的参数,或者将参数设置为vModal,VBA都会讲窗口见识为模式窗体
2. 无模式窗体允许进行窗体外的其他操作
要将窗体显示为无模式窗体,必须通过show方法制定参数
Inputform.show vmodeless
如果将窗体显示为无模式窗体,当窗体显示后,系统会继续执行程序余下的代码,也允许我们操作窗体之外的其他对象
关闭或隐藏已显示的窗体
- 用unload命令关闭窗体
unload Inputform
- 使用Hide方法隐藏窗体
inputform.hide
用户窗体的事件应用
借助Initialize事件初始化窗体
Initialize事件发生在显示窗体之前,当我们在程序中使用load语句加载窗体,或者使用show显示窗体时,都会引发该时间
Private Sub UserForm_Initialize()
性别.List = Array("男", "女")
End Sub
借助QueryClose事件让窗体自带的【关闭】按钮失效
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> vbFormCode Then Cancel = True
End Sub
queryclose事件过程是一个带两个参数的sub过程,其中的cancel参数确定是否响应我们关闭窗体的操作。当值为TRUE时,程序将不响应我们关闭窗体的操作。如果cancel的值为false,程序将关闭窗体。其中CloseMode参数是我们关闭窗体的方式,不同的关闭方式返回的值也不相同
CloseMode参数的返回值说明
常数 | 值 | 说明 |
---|---|---|
vbFormControlMenu | 0 | 在窗体中单击【关闭】按钮关闭窗体 |
vbFormCode | 1 | 通过unload语句关闭窗体 |
vbAppWindows | 2 | 正在结束当前Windows操作环境的过程 |
vbAppTaskManagee | 3 | windows的【任务管理器】正在关闭整个应用 |
为窗体的控件设置功能
为【确定】按钮添加事件过程
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
给控件设置快捷键
给按钮设置了快捷键后,显示窗体时,当按下对应的快捷键就等于在窗体中用鼠标单击了该按钮。
Private Sub UserForm_Initialize()
性别.List = Array("男", "女")
cmd_OK.Accelerator = "N"
End Sub
更改控件的Tab键顺序
只有对象被激活时,才能几首键盘输入。控件的Tab键顺序决定用户按下【Tab】或【Shift+Tab】组合键后控件激活的顺序。在设计窗体时,系统会按照添加控件的先后顺序确定控件的Tab顺序。但是这个顺序是可以更改的。在VBE中选中窗体,依次执行【视图】——>【Tab键顺序】命令。调出Tab键顺序对话框,即可在其中调整控件的Tab键顺序
用窗体设计一个简易的登陆窗体
- 设计窗体显示
2. 添加代码,为控件指定功能
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,当程序运行过程中晕倒运行时错误时,跳转到标签所在行的代码继续执行程序,实际上就是让程序跳过出错的代码,从另一个地方重新开始执行程序。
Sub test()
On Error GoTo a
Worksheets("abc").Select
Exit Sub
a: MsgBox "没有要选择的工作表"
End Sub
on error resume next
Resume Next告诉VBA,如果程序发生错误,则忽略存在错误的代码,接着执行错误行之后的代码。如果程序一开始加入On Error Resume Next语句,运行程序时,及时程序中存在运行时错误,VBA也不会中断程序,而是忽略所有存在错误的语句,继续执行出错语句后的代码
Sub test()
On Error Resume Next
Worksheets("abc").Select
Exit Sub
MsgBox "没有要选择的工作表"
End Sub
On Error GoTo 0
使用On Error GoTo 0语句后,将关闭对程序中运行时错误的捕捉,如果程序在On Error GoTo 0语句后出现运行错误,将不会被捕捉到
Sub test()
On Error GoTo 0
Worksheets("abc").Select
Exit Sub
MsgBox "没有要选择的工作表"
End Sub
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/130408.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...