Excel VBA编程

文章目录

  • 如何创建VBA
  • VBA语法规则
    • 声明变量
    • 给变量赋值
    • 让变量存储的数据参与运算
    • 关于声明变量的其他知识
    • 变量的作用域
    • 特殊的变量——数组
    • 声明多维数组
    • 声明动态数组
    • 其他创建数组的方法
    • 数组函数
      • 利用UBound求数组的最大索引号
      • 利用LBound函数求最小索引号
      • 求多维数组的最大和最小索引号
      • 用join函数将一维数组合并成字符串
      • 将数组内容写入工作表中
      • 数组的存取
    • 特殊数据的专用容器——常量
  • 对象,集合及对象的属性和方法
    • VBA中的运算符
      • 算数运算符
      • 比较运算符
      • 通配符
      • 逻辑运算符
  • VBA内置函数
  • 执行程序执行的基本语句结构
    • if语句
      • select case语句
      • for 循环
      • for each...next语句循环处理集合或数组中的成员
      • do while循环
      • 使用Goto语句,让程序转到另一条语句去执行
      • with语句,简写代码
  • 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

  1. 进入开发工具窗口

2.选择插入模块,然后插入过程,选择子程序


VBA语法规则

在Excel中,数据只有文本,数值,日期值,逻辑值和错误值五种类型。但是在VBA中,数据类型跟Excel不完全相同。根据数据的特点,VBA将数据分为布尔型(boolean),字节型(byte),整数型(integer),单精度浮点型(single),双精度浮点型(double),货币型(currency),小数型(decimal),字符串型(string),日期型(date),对象型等等

声明变量

声明变量,其实就是指定该变量的名称及其可存储的数据类型,要在VBA中声明一个变量,有以下几种方法:

  1. Dim 变量名 as 数据类型
  2. Private 变量名 as 数据类型,用private声明变量,该变量将会变成私有变量
  3. Public 变量名 as 数据类型,用public变量定义的变量是公有变量
  4. static 变量名 as 数据类型,如果使用static声明变量,这个变量将会变成静态变量,当程序结束后,静态变量会保持其原值不变。

给变量赋值

  1. 给数据类型的变量赋值,应该要使用这个语句:let 变量名称 = 要存储的数据
  2. 给对象类型的变量赋值,应该使用下面的语句:“set 变量名称 = 要存储的对象名称

让变量存储的数据参与运算

  1. 数据型变量参与运算


2. 对象型变量参与运算

关于声明变量的其他知识

  1. 如果要声明多个变量可以将代码写成下面形式:


2. 可以使用变量类型声明符定义变量类型

数据类型类型声明字符
Integer%
Long&
Single
Double#
currency@
string$
  1. 声明变量可以不指定变量类型:在VBA中声明变量是,如果不确定会将类型的数据存储在变量中,可以在声明变量时,只定义变量的名字,而不是变量的类型。如果声明变量时,只指定变量的名称而不指定变量的数据类型,VBA默认将该变量定义为Variant类型,如果一个变量被声明为variant类型,俺么它能够存储任何数据类型
  2. 强制声明所有变量:如果担心自己忘记在程序中忘记声明变量,可以在模块开头,输入“Option Explicit

变量的作用域

按作用域划分,VBA中的变量可以划分为本地变量,模块变量和公共变量。

作用域描述
单个过程在一个过程中使用dim或者static语句声明的变量,作用域为本过程,即只有声明变量的语句所在的过程能够使用它,这样的变量,称为本地变量
单个模块在模块的第一个过程之前使用dim或者private语句声明的变量,作用域为声明变量的语句所在模块中的所有过程,即该模块中所有的过程都可以使用它,这样的边框称为模块级变量
所有模块在一个模块的第一个过程之前使用public语句声明的变量,作用域为所有模块,即所有模块中的过程都可以使用它,这样的变量称为公共变量

特殊的变量——数组

  1. 数组就是同种类型的多个变量的集合
  2. 数组中的元素可以通过索引值取出
  3. 声明数组时应该声明数组的大小
    public dim 数组名称 (a to b)as 数据类型
  4. 给数组赋值就是给数组的每个元素分别赋值

声明多维数组

Dim arr(a,b) as Integer

这里是声明一个叫arr数组,其形状为a行b列

声明动态数组

当声明数组时,不知道要在这个数组中填入多少个数据时,可以使用动态数组。将数组声明为动态数组时,不能直接使用变量,需要之后使用redim 重新制定数组大小

Sub test()a = Application.WorksheetFunction.CountA(rang("A:A"))Dim arr() As StringReDim arr(1 To a)
End Sub

其他创建数组的方法

  1. 使用array函数声明数组
Sub arraytest()Dim arr As Variantarr = Array(1, 2, 3, 4)MsgBox "arr数组的第2个元素为:" & arr(1)End Sub


2. 使用是split函数声明数组

Sub arraytest()Dim arr As Variantarr = Split("叶枫,空空,小月,老祝", ",")MsgBox "arr数组的第2个元素为:" & arr(1)End Sub

  1. 通过单元格区域直接创建数组
Sub arraytest()Dim arr As VariantRange("A1:C3").Value = 5arr = Range("A1:C1").ValueRange("e1:G1").Value = arrEnd Sub

数组函数

利用UBound求数组的最大索引号

Sub arraytest()Dim arr As VariantRange("A1:C3").Value = 5arr = Range("A1:C1").ValueMsgBox "数组的最大索引号是:" & UBound(arr)End Sub

利用LBound函数求最小索引号

Sub arraytest()Dim arr As VariantRange("A1:C3").Value = 5arr = Range("A1:C1").ValueMsgBox "数组的最小索引号是:" & LBound(arr)End Sub

求多维数组的最大和最小索引号

Sub arraytest()Dim arr(10, 100) As IntegerDim a As Integer, b As Integera = UBound(arr, 1)b = UBound(arr, 2)MsgBox "第一维的最大索引是:" & a & Chr(13) & "第二维的最大索引是:" & bEnd Sub

用join函数将一维数组合并成字符串

Sub arraytest()Dim arr As Variantarr = Array(1, 2, 3, 4)Text = Join(arr)MsgBox TextEnd Sub

将数组内容写入工作表中

Sub arraytest()Dim arr As Variantarr = 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").ValueCase Is >= 90Range("C2").Value = "优秀"Case Is >= 80Range("C2").Value = "良好"Case Is >= 60Range("C2").Value = "及格"Case Is < 60Range("c1").Value = "不及格"End SelectEnd Sub

for 循环

在VBA中定义for循环的语法规则如下:

for 循环变量=初值 to 终值 step 步长值循环体
next 循环变量名

for循环都要以next结尾

Sub test()Dim irow As ByteDim i As ByteFor i = 1 To 10 Step 1Select Case Range("B" & i).ValueCase Is > 100Range("C" & i).Value = "信息错误"Case Is >= 90Range("C" & i).Value = "优秀"Case Is >= 80Range("C" & i).Value = "良好"Case Is >= 60Range("C" & i).Value = "及格"Case Is < 60Range("C" & i).Value = "不及格"End SelectNext i
End Sub

for each…next语句循环处理集合或数组中的成员

当需要循环处理一个数组的每个元素或者集合中的每个成员时,使用for each……next语句

Sub test()Dim i As Bytej = 1For Each sht In WorksheetsRange("D" & j).Value = sht.Namej = j + 1Next shtEnd Sub

do while循环

do while语句可以分为两种,按照设置循环条件的位置区分,可以分为开头判断式和截尾判断式。其语句结构如下:

  1. 开头判断式
do [while 循环条件]循环体exit do循环体
loop
  1. 截尾判断式
do 循环体exit do循环体
loop [while 循环条件]

每个do语句都必须以loop结尾,当循环进行到loop处时,会重新回到do语句判断条件是否成立

Sub test()Dim i As Bytei = 1DoWorksheets.Addi = i + 1Loop While i < 5
End Sub

使用Goto语句,让程序转到另一条语句去执行

要让goto语句清楚的知道要转向的目标语句,可在目标语句之前加上一个带冒号的文本字符创,或者不带带冒号的数字标签,然后在goto的后面写上标签名

Sub test()Dim i As IntegerDim sum As Longi = 1
x:    mysum = mysum + ii = i + 1If i <= 100 Then GoTo xMsgBox "1到100的和为:" & mysum
End Sub

with语句,简写代码

当需要对相同的对象进行多次操作时,往往会编写一些重复的代码。如果不想重复多次的录入相同的语句,可以用with语句简化

Sub fontest()With Worksheets("Sheet1").Range("A1").Font.Name = "仿宋".ColorIndex = 3.Bold = True.Size = 12End With
End Sub

sub过程,基本的程序单元

sub过程的基本结构

[private|public|static] sub 过程名([参数列表])语句块
[exit sub]语句块
end sub

private或public用来声明过程的作用域,同时只能使用一个,可以省略,如果省略,默认使用public。如果选用static,过程执行结束后,会继续保存过程中变量的值

在过程中调用过程

  1. 直接使用过程名调用过程,过程名与参数之间用英文逗号隔开
subname,arg1,arg2sub runsub()subadd
end sub
  1. 使用call关键字调用过程,参数写在过程小括号中,不同参数之间用逗号隔开
call 过程名(args,arg2)
sub runsub()call subadd
end sub
  1. 使用application对象的run方法调用过程
application.run "subname,arg1,arg2"
sub runsub()application.run "subadd"

sub过程中的参数传递

在VBA中,过程的参数传递主要有两种形式:按引用传递和按传递。默认情况下,过程是按照引用的方式传递参数的。如果程序通过引用的方式传递参数,只会传递保存数据的内存地址,在过程中对参数的任何修改都会影响原始的数据。

Sub shtadd(shtcount As Integer)Worksheets.Add Count:=shtcountshtcount = 8MsgBox "shtcount的值:" & shtcount
End SubSub test()Dim c As Integerc = 2Call shtadd(c)MsgBox "参数过程中的值为:" & c
End Sub

自定义函数,function过程

声明一个自定义函数

public function 函数名([参数])函数体函数名= 结果
end function

无论function过程包含多少代码,要执行多少计算,都应该将最后的计算结果保存在过程名称中,这相当于其他语言中的函数return内容

使用自己定义的函数

  1. 在Excel中使用:
    如果定义的函数没有被定义为私有过程,那么我们可以通过【插入函数】在Excel中使用我们自定义的函数。

Public Function fun()fun = Int(Rnd() * 10) + 1
End Function

  1. 在VBA过程中使用
Sub test()MsgBox fun()
End Sub

function example

Public Function count_color(arr As Range, c As Range)Dim rng As RangeFor Each rng In arrIf rng.Interior.Color = c.Interior.Color Thencount_color = 1 + count_colorEnd IfNext rngEnd Function

设置函数为易失性函数,让自定义函数也能重复计算

有时,当工作表重新计算后,自定义函数并不会重新计算。如果想让工作表重新计算后,自定义的函数也能随之重新计算,就应该讲自定义函数定义为易失性函数。要将一个自定义函数定义为易失性函数,只需要在function过程开始时添加下面一行代码

application.voliatile true

example


Public Function fun()application.voliatile truefun = 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 = FalseRange("A1:A10").Value = 10MsgBox "刚才输入的结果是10,你看到了吗?"Range("A1:A10").Value = 100MsgBox "刚才的输入是100,你看到了吗"Application.ScreenUpdating = TrueEnd Sub

设置DisplayAlerts属性禁止显示警告对话框

当我们在Excel中执行某些操作时,Excel会显示一个警告框,让我们确定是否执行这些操作。由于很多原因,我们都希望Excel在程序执行中不显示类似的警告对话框,这样可以通过设置application对象的displayalerts属性为false来实现

Sub sheettest()Worksheets.Add Count:=5Application.DisplayAlerts = FalseDim sht As WorksheetFor Each sht In WorksheetsIf sht.Name <> ActiveSheet.Name Thensht.DeleteEnd IfNext shtApplication.DisplayAlerts = TrueEnd Sub

借助worksheetfunction属性使用工作表函数

VBA中有许多的内置函数,合理使用函数可有效减少工作中的许多难题,减少编写代码的工作量。但是VBA中没有Excel的内置函数,使用worksheetfunction可以调用Excel中的内置函数。

Sub cunt()Dim mycount As IntegerRange("A1:B10").Value = 101mycount = 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对象

  1. 通过文件索引引用
  2. 通过文件名引用
sub test()workbooks(3)workbooks("sheet1")
end sub

访问workbook对象属性

Sub info()Range("c1") = ThisWorkbook.NameRange("C2") = ThisWorkbook.PathRange("C3") = ThisWorkbook.FullName
End Sub

创建工作簿——add

  1. 创建空白工作簿:如果直接调用workbook对象的add方法,而不设置任何参数,excel将创建一个只含普通工作表的新工作簿
  2. 指定用来创建工作簿的模板: 如果想将某个工作簿文件作为新建工作簿的模板,可以使用add方法的template参数指定该文件的名称及其所在的所在目录
  3. 指定新建工作簿包含的工作簿类型
workbooks.add 
workbooks.add template:="D:\file\template.xlsm"
workbooks.add template := xlWBATChart '让新建的工作簿包含图表工作表

用add方法的参数指定新建的工作簿包含的工作表类型

参数值工作簿包含的工作表类型
xlWBATWorksheet普通工作表
xlWBATChart图表工作表
xlWBATExcel4Macrosheet4.0宏工作表
xlWBATExcel4IntlMacrosheet5.0对话框工作表

用open方法打开工作簿

workbooks.open filename := "path"

activate激活工作簿

workbooks("workbooks_name").activate

保存工作簿

  1. save方法保存已存在的文件
  2. saveas方法将工作簿另存为新文件
  3. 另存为新文件后不关闭原文件
thisworkbooks.save 
thisworkbooks.saveas filename:="path"
thisworkbooks.savecopyas filename :="path"

close——关闭工作簿

workbooks.close  '关闭当前打开的所有工作簿
workbooks("workbooks_name").close '关闭指定名称的工作簿
workbooks.close savechanges := true '关闭并保存对工作簿的修改

worksheet对象

add方法新建工作表

  1. 在活动工作表前插入一张工作表
  2. 用before|after参数指定要插入工作表的位置
  3. 用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方法复制工作表

  1. 将工作表复制到指定位置
  2. 将工作表复制到新工作簿中
worksheets('worksheet_name').copy before|after :=worksheet_name
worksheets("worksheet_name").copy

使用move移动工作表

  1. 将工作表移动到指定位置
  2. 将工作表移动到新工作簿中
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属性引用单元格

  1. 引用单个固定的单元格区域:这种方法实际上就是通过单元格地址来引用单元格
  2. 引用多个不连续的单元格:将range属性的参数设置为一个用逗号分成多个单元格地址组成的字符串
  3. 引用多个区域的公共区域:将range属性设置成为一个用空格分割的多个单元格地址组成的字符串
  4. 引用两个区域围成的矩形区域
range("A1:C1")
range("A1:A10,E6,E7:C12").select
range("B1:B10 A4:D6").value
range("B6:B10","D2:D8")

用cell属性引用单元格

  1. 引用工作表中指定行列交叉的单元格
  2. 引用单元格区域中的某个单元格
  3. 将cells属性的返回结果设置为range属性的参数
  4. 使用索引号引用单元格
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 WorkbookDim sht As WorksheetSet wb = Workbooks.AddSet sht = wb.Worksheets(1)With sht.Name = "员工花名册"End Withwb.SaveAs ThisWorkbook.Path & "员工花名册.xlsx"ActiveWorkbook.CloseEnd Sub

判断某个工作簿是否已经打开

Sub isopen()Dim i As IntegerFor i = 1 To Workbooks.CountIf Workbooks(i).Name = "成绩表.xlsx" ThenMsgBox "成绩表文件已打开"Exit SubEnd IfNextMsgBox "文件未打开"
End Sub

判断文件夹中是否存在指定名称的工作簿文件

Sub isexist()Dim fil As Stringfil = ThisWorkbook.Path & "\员工花名册.xlsx"If Len(Dir(fil)) > 0 ThenMsgBox "文件存在"ElseMsgBox "文件不存在"End IfEnd Sub

向未打开的工作簿中输入数据

Sub wbinput()Dim wb As String, xrow As Integer, arrwb = ThisWorkbook.Path & "\员工花名册.xlsx"Workbooks.Open (wb)With ActiveWorkbook.Worksheets(1)xrow = .Range("A1").CurrentRegion.Rows.Count + 1arr = Array(xrow - 1, "马军", "男", #7/8/1987#, #9/1/2010#, "10年新招").Cells(xrow, 1).Resize(1, 6) = arrEnd WithActiveWorkbook.Close savechanges:=TrueEnd Sub

隐藏活动工作表外的所有工作表

Sub shtvisible()Dim sht As WorksheetFor Each sht In WorksheetsIf sht.Name <> ActiveSheet.Name Thensht.Visible = xlSheetVeryHiddenEnd IfNext
End Sub

批量新建指定名称的工作表

Sub shtadd()Dim i As IntegerDim sht As Worksheeti = 1Set sht = Worksheets("Sheet11")Do While sht.Cells(i, "E") <> ""Worksheets.Add after:=Worksheets(Worksheets.Count)ActiveSheet.Name = sht.Cells(i, "E").Valuei = i + 1LoopEnd Sub

批量对数据进行分离,并保存到不同的工作表中

Sub fenlie()Dim i As Long, bj As String, rng As Rangei = 1bj = Worksheets("Sheet11").Cells(i, "C").ValueDo While bj <> ""Set rng = Worksheets(bj).Range("A1048576").End(xlUp).Offset(1, 0)Worksheets("Sheet11").Cells(i, "A").Resize(1, 7).Copy rngi = i + 1bj = Worksheets("Sheet11").Cells(i, "C").ValueLoopEnd Sub

将多张工作表中的数据合并到一张工作表中

Sub hebing()Dim sht As WorksheetSet sht = Worksheets("Sheet11")sht.Rows.ClearDim wt As Worksheet, xrow As Integer, rng As RangeFor Each wt In WorksheetsIf wt.Name <> "Sheet11" ThenSet rng = sht.Range("A1048576").End(xlUp)xrow = wt.Range("A1").CurrentRegion.Rows.Countwt.Range("A1").Resize(xrow, 7).Copy rngEnd IfNext
End Sub

将工作簿中的每张工作表都保存为单独的工作簿文件

Sub savetofile()Application.ScreenUpdating = FalseDim folder As Stringfolder = ThisWorkbook.Path & "\班级成绩表"If Len(Dir(folder, vbDirectory)) = 0 ThenMkDir folderEnd IfDim sht As WorksheetFor Each sht In WorksheetsIf sht.Name <> "Sheet11" Thensht.CopyActiveWorkbook.SaveAs folder & "\" & sht.Name & ".xlsx"ActiveWorkbook.CloseEnd IfNextApplication.ScreenUpdating = TrueEnd Sub

将多个工作簿中的数据合并到同一张工作表中

Sub hzwb()Dim bt As Range, r As Long, c As Longr = 1c = 7Dim wt As WorksheetSet wt = ThisWorkbook.Worksheets(1)wt.Rows(r & ":1045876").ClearContentsApplication.ScreenUpdating = FalseDim filename As String, sht As Worksheet, wb As WorkbookDim erow As Long, fn As String, arr As Variantfilename = Dir(ThisWorkbook.Path & "\*.xlsx")Do While filename <> ""If filename <> ThisWorkbook.Name Thenerow = wt.Range("A1").CurrentRegion.Rows.Countfn = ThisWorkbook.Path & "\" & filenameSet 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)) = arrwb.Close savechanges:=TrueEnd Iffilename = DirLoopApplication.ScreenUpdating = TrueEnd Sub

为同一工作簿中的工作表建一个带链接的目录

Sub mulu()Dim wt As WorksheetSet wt = Worksheets("Sheet11")wt.Rows("1:1048576").ClearContentsDim sht As Worksheet, irow As Integerirow = 1For Each sht In WorksheetsIf sht.Name <> "Sheet11" Thenwt.Cells(irow - 1, "A").Value = irow - 1wt.Hyperlinks.Add anchor:=wt.Cells(irow - 1, "B"), Address:="", SubAddress:="'" & sht.Name & "'!A1", TextToDisplay:=sht.NameEnd Ifirow = 1 + irowNext
End Sub

其中参数anchor指定建立超链接的位置,address指定超链接的地址,subaddress指定超链接的子地址,TexttoDisplay指定用于显示超链接的文字

执行程序的自动开关——对象的事件

让excel自动相应我们的操作

  1. 打开thisworkbook的代码窗口
  2. 选择workbook对象,在声明中选择open
  3. 编写子程序

当某个事件放生后自动运行的过程,称为事件过程,事件过程也是sub过程 。与sub过程不同的是,时间过程的作用域,过程名称及参数都不需要我们设置,也不能随意设置。时间过程的过程名称总是由对象名称及时间名称组成的,对象在前,事件在后,二者之间用下划线连接。

使用工作表事件

工作表事件就是发生在worksheet对象中的事件,一个工作簿中可能包含多个worksheet对象,而worksheet事件过程必须写在相应的worksheet中,只有过程所在的worksheet对象中的操作才能触发相应的事件。

worksheet对象的change事件

worksheet对象的change事件告诉VBA:当过程所在工作表中的单元格被更改时自动运行程序。

编写事件过程,通常我们都采用这种方式:依次在【代码窗口】的【对象】列表框和【事件】列表框中选择相应的对象及事件名称,让VBA自动替我们设置事件过程的作用域、过程名称以及参数信息

  1. 更改单元格时自动执行
Private Sub Worksheet_Change(ByVal Target As Range)MsgBox Target.Address & "被更改为" & Target.ValueEnd Sub
  1. 更改部分单元格时自动执行
Private Sub Worksheet_Change(ByVal Target As Range)If Target.Column <> 1 ThenExit SubEnd IfMsgBox Target.Address & "被更改为" & Target.ValueEnd Sub

禁用事件,让事件过程不再自动执行

禁用事件就是执行操作后不让事件发生。在VBA中,可以设置application对象的EnableEvents属性为false来禁用事件

Private Sub Worksheet_Change(ByVal Target As Range)Application.EnableEvents = FalseTarget.Offset(0, 1).Value = "测试一下"Application.EnableEvents = True
End Sub

selectionchange事件:当选中的单元格改变时发生

worksheet对象的selectionchange时间告诉VBA:当更改工作表中选中的单元格区域时自动执行该事件的事件过程。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)MsgBox "你当前所选中的单元格是:" & Target.AddressEnd Sub

高亮选择区域的相同值

Private Sub Worksheet_SelectionChange(ByVal Target As Range)Range("A3:I66").Interior.ColorIndex = xlNoneIf Application.Intersect(Target, Range("A3:I66")) Is Nothing ThenExit SubEnd IfIf Target.Count > 1 ThenSet Target = Target.Cells(1)End IfDim rng As RangeFor Each rng In Range("A3:I66")If rng.Value = Target.Value Thenrng.Interior.ColorIndex = 6End IfNext
End Sub

用批注记录单元格中数据的修改情况

Dim rngvalue As StringPrivate Sub Worksheet_Change(ByVal Target As Range)If Target.Cells.Count > 1 ThenExit SubEnd IfDim cvalue As StringIf Target.Formula = "" Thencvalue = "空"Elsecvalue = Target.TextEnd IfIf rngvalue = cvalue ThenExit SubEnd IfDim rngcom As CommentDim comstr As StringSet rngcom = Target.CommentIf rngcom Is Nothing Then Target.AddCommentcomstr = Target.Comment.TextTarget.Comment.Text Text:=comstr & Chr(10) & Format(Now(), "yyyy-mm-ddhh:mm") & _"原内容:" & rngvalue & "修改为:" & cvalueTarget.Comment.Shape.TextFrame.AutoSize = TrueEnd Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Target.Cells.Count > 1 ThenExit SubEnd IfIf Target.Formula = "" Thenrngvalue = "空"Elserngvalue = Target.TextEnd IfEnd 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 thencancel =trueend 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 SubSub 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 SubSub Hello()MsgBox "我在学习ontime方法"
End Sub

Ontime的DateSerial参数可以设置指定的年月日;Scheduled的值如果为TRUE,会新设置一个Ontime过程,如果为False,就会清除之前设置的过程,默认值为TRUE

让文件自动保存

Sub test()Application.OnTime Now() + TimeValue("00:05:00"), "AutoSave"
End SubSub AutoSave()ThisWorkbook.SaveCall test
End Sub

设置自定义的操作界面

控件,搭建操作界面必不可少的零件

excel中有两种类型的控件:表单控件和ActiveX控件。可以在excel的【功能区】中找到它们

  1. 表单控件
控件名称控件说明
按钮用于执行宏命令
组合框提供可选择的多个选项,用户可以选择其中一个项目
复选框用于选择的控件,可以多项选择
数值调节按钮通过单击控件的箭头来选择数值
列表框显示多个选项的列表,用户可以从中选择一个选项
选项按钮用于选择的控件,通常几个选项按钮用组合框组合在一起使用,在一组中只能同时选择一个选项按钮
分组框用于组合其他多个控件
标签用于输入和显示静态文本
滚动条包括水平滚动条和垂直滚动条
  1. ActiveX控件:excel中有11中可用的ActiveX控件,但是工作表总使用的ActiveX控件不止这些,可以单击其中的【其他控件】按钮,在弹出的对话框中选择其他控件

在工作表中使用控件

  1. 添加表单控件


2. 绘制表单控件


3. 设置相应区域

在工作表中使用ActiveX控件

  1. 添加ActiveX控件


2. 设置属性

  1. 编写控件代码
Private Sub xb2_Click()If xb2.Value = True ThenRange("D2").Value = "女"xbl.Value = FalseEnd If
End SubPrivate Sub xbl_Click()If xbl.Value = True ThenRange("D2").Value = "男"xb2.Value = FalseEnd IfEnd Sub
  1. 显示内容

不需设置,使用现成的对话框

用InputBox函数创建一个可输入数据的对话框

Input函数共有5个参数:

  1. prompt参数用于设置在对话框中显示的提示消息
  2. title用于设置对话框的标题
  3. default用于设置默认的输入值
  4. xpos用于设置对话框与左端与屏幕左端的距离
  5. ypos用于设置对话框的顶端与屏幕顶端的距离
Sub inbox()Dim c As Variantc = InputBox("你要在A1单元格输入什么数据?", Title:="提示", Default:="叶飞", xpos:=200, ypos:=250)Range("A1").Value = cEnd 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 VariantOn Error GoTo cancelSet rng = Application.InputBox("请选择需要输入数值的单元格", Type:=8)rng.Value = 100
cancel:End Sub

使用msgbox函数创建输出对话框

Sub msg()MsgBox "你正在编辑的是:" & ThisWorkbook.Name, Buttons:=vbOKOnly + vbInformation, Title:="提示"End Sub

设置在对话框中显示的按钮样式

常数说明
vbOkonly0只显示【确定】按钮
vbOkcancel1只显示【确定】和【取消】按钮
vbAbortRetryIgnore2显示【终止】,【重试】,【忽略】三个按钮
vbYesNoCancel3显示是,否,取消三个按钮
vbYesNo4显示是,否两个按钮
vbRetryCancel5显示重试,取消两个按钮

不同图标的参数设置

常数说明
vbCritical16显示【关键信息】图标
vbQuestion32显示【警告询问】图标
vbExclamation48显示【警告消息】图标
vbInformation64显示【通知消息】图标

设置对话框中的默认按钮
常数|值|说明
vbDefaultButton1 | 0 | 第一个按钮为默认值
vbDefaultButton2 | 256 | 第二个按钮为默认值
vbDefaultButton3 | 512 | 第三个按钮为默认值
vbDefaultButton4 | 768 | 第四个按钮为默认值

指定对话框的类型

常数说明
vbApplicationModel0应用程序强制返回;应用程序暂停执行,直到用户消息框做出响应才继续
vbSystemModel4096系统强制返回;全部应用程序都暂停执行,直到用户对消息框做出响应才继续工作

buttons参数的其他设置

常数说明
vbMsgBoxHelpButton16384在对话框中添加帮助按钮
vbMsgBoxForeground65536设置显示的对话框窗口为前景窗口
vbMsgBoxRight524288设置对话框中显示的文本为右对齐
vbMsgBoxRtlReading1048576指定文本英在希伯来文和Alibaba系统中显示为从右到左阅读

MsgBox函数的返回值

常数说明
vbOk1单击【确定】按钮时
vbCancel2单击【取消】按钮时
vbAbort3单击【终止】按钮时
vbRetry4单击【重试】按钮时
vbIgnore5单击【忽略】按钮时
vbYes6单击【是】按钮时
vbNo7单击【否】按钮时
Sub msgbut()Dim yn As Integeryn = MsgBox("你确定要在A1单元格输入今天的日期吗?", vbYesNo + vbQuestion)If yn = vbYes ThenRange("A1").Value = Now()End IfEnd Sub

使用FindFile方法显示【打开】对话框

使用application对象的FindFile方法可以显示【打开】对话框,在对话框中选择并打开某个文件

Sub openfile()If Application.FindFile = True ThenMsgBox "the file you chosed has benn open"ElseMsgBox "你单击了【取消按钮】,操作未完成"End IfEnd Sub

用GetOpenFileName方法显示【打开】对话框

与findfile方法不同,使用GetOpenFileName方法是获得在对话框中选中的文件的文件名称(包含路径),而findfile是打开在对话框中选中的文件

  1. 选择任意文件
Sub getopen()Dim filfil = Application.GetOpenFilenameIf fil = False ThenMsgBox "没有任何文件"Exit SubElseRange("E6").Value = filEnd If
End Sub
  1. 只在对话框中显示某种类型的文件
Sub getopen()Dim filfil = Application.GetOpenFilename(filefilter:="图片文件,*.jpg")If fil = False ThenMsgBox "没有任何文件"Exit SubElseRange("E6").Value = filEnd If
End Sub
  1. 让对话框同时显示多种扩展名文件
Sub getopen()Dim filfil = Application.GetOpenFilename(filefilter:="图片文件,*.jpg;*png")If fil = False ThenMsgBox "没有任何文件"Exit SubElseRange("E6").Value = filEnd If
End Sub
  1. 让对话框能选择显示多种类型的文件
Sub getopen()Dim filfil = Application.GetOpenFilename(filefilter:="图片文件,*.jpg;*png,Excel工作簿文件,*xls;*xlsx")If fil = False ThenMsgBox "没有任何文件"Exit SubElseRange("E6").Value = filEnd If
End Sub
  1. 通过FilterIndex参数设置默认显示的文件类型
    如果在【文件类型】下拉列表中设置了多种可选择的文件类型,就可以通过GetOpenFileName方法的FiterIndex参数,设置对话框中默认显示的文件类型
Sub getopen()Dim filfil = Application.GetOpenFilename(filefilter:="图片文件,*.jpg;*png, Excel工作簿文件, *xls;*xlsx", FilterIndex:=2)If fil = False ThenMsgBox "没有任何文件"Exit SubElseRange("E6").Value = filEnd If
End Sub
  1. 设置允许同时选择多个文件
    默认情况下,在通过GetOpenFileName方法显示的【打开】对话框中,只能同时选中一个文件,如果希望同时选中多个文件,可以将MultiSelect参数设置为TRUE
Sub getopen()Dim filfil = Application.GetOpenFilename(filefilter:="图片文件,*.jpg;*png, Excel工作簿文件,*xls;*xlsx", FilterIndex:=2, MultiSelect:=True)Range("E6").Value = fil
End Sub
  1. 修改对话框标题
Sub getopen()Dim filfil = 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 Stringfilename = "例子"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 = "请选择一个目录".ShowIf .SelectedItems.Count > 0 ThenRange("A1").Value = .SelectedItems(1)End IfEnd With
End Sub

msoFileDialogType参数可以设置的常量

常量说明
msoFileDialogFilePicker允许选择一个文件
msoFileDialogFolderPicker允许选择一个文件夹
msoFileDialogOpen允许打开一个文件
msoFileDialogSaveAs允许保存一个文件

使用窗体对象设置交互界面

很多时候,我们都希望自己能够设计一个交互界面,定义其中的控件及控件的功能,这就需要用到VBA中的另一类常用对象——Userform对象。一个用户窗体就是一个Userform对象,也就是大家常说的窗体对象。当在工程中添加一个窗体后,就可以在窗体上自由的添加ActiveX控件,只要通过编写VBA代码为这些控件指定功能,就能利用这些控件与excel互动

  1. 在工程中添加一个用户窗体

  1. 设置属性,改变窗体的外观

  1. 在窗体中添加和设置控件的功能

用代码操作自己设计的窗体

显示窗体

  1. 手动显示窗体

在VBE窗口中选中窗体,依次执行【运行】——>【运行子过程/窗体】命令,即可显示选中的窗体

  1. 在程序中用代码显示窗体
sub showform()load inputforminputform.show
end sub

将窗体显示为无模式窗体

  1. 模式窗体不能操作窗体之外的对象

要将窗体显示为模式窗体,可以使用代码

InputForm.show

或者省略show方法的参数,或者将参数设置为vModal,VBA都会讲窗口见识为模式窗体
2. 无模式窗体允许进行窗体外的其他操作

要将窗体显示为无模式窗体,必须通过show方法制定参数

Inputform.show vmodeless

如果将窗体显示为无模式窗体,当窗体显示后,系统会继续执行程序余下的代码,也允许我们操作窗体之外的其他对象

关闭或隐藏已显示的窗体

  1. 用unload命令关闭窗体
unload Inputform
  1. 使用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参数的返回值说明

常数说明
vbFormControlMenu0在窗体中单击【关闭】按钮关闭窗体
vbFormCode1通过unload语句关闭窗体
vbAppWindows2正在结束当前Windows操作环境的过程
vbAppTaskManagee3windows的【任务管理器】正在关闭整个应用

为窗体的控件设置功能

为【确定】按钮添加事件过程

Private Sub cmd_OK_Click()Dim xrow As Longxrow = Range("A1").CurrentRegion.Rows.Count + 1Cells(xrow, "A").Value = 姓名.ValueCells(xrow, "B").Value = 性别.ValueCells(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键顺序

用窗体设计一个简易的登陆窗体

  1. 设计窗体显示


2. 添加代码,为控件指定功能

Private Sub cmd_cacel_Click()Unload 用户登录ThisWorkbook.Close savechanges:=False
End SubPrivate Sub cmd_ok_Click()Application.ScreenUpdating = FalseStatic i As IntegerIf 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) ThenUnload 用户登录Elsei = i + 1If i = 3 ThenMsgBox "你无权打开工作簿"ThisWorkbook.Close savechanges:=FalseElsemsg "输入错误,你还有" & (3 - i) & "次机会输入"username.Value = ""pwd.Value = ""End IfEnd IfApplication.ScreenUpdating = TrueEnd SubPrivate Sub pwd_set_Click()Dim old As String, new1 As String, new2 As Stringold = InputBox("请输入密码:", "提示")If old <> Right(Names("userword").RefersTo, Len(Names("userword").referto) - 1) ThenMsgBox "原密码输入错误,不能修改", vbCritical, "错误"Exit SubEnd Ifnew1 = InputBox("请输入新密码:", "提示")If new1 = "" ThenMsgBox "新密码不能为空,修改没有完成", vbCritical, "错误"Exit SubEnd Ifnew2 = InputBox("请再次输入新密码:", "提示")If new1 = new2 ThenNames("userword").RefersTo = "=" & new1ThisWorkbook.SaveMsgBox "密码修改完成"ElseMsgBox "两次密码输入不一致,修改未完成", vbCritical, "错误"End IfEnd SubPrivate Sub user_set_Click()Dim old As String, new1 As String, new2 As Stringold = InputBox("请输入用户名:", "提示")If old <> Right(Names("username").RefersTo, Len(Names("username").referto) - 1) ThenMsgBox "原用户名输入错误,不能修改", vbCritical, "错误"Exit SubEnd Ifnew1 = InputBox("请输入新用户名:", "提示")If new1 = "" ThenMsgBox "新用户名不能为空,修改没有完成", vbCritical, "错误"Exit SubEnd Ifnew2 = InputBox("请再次输入新用户名:", "提示")If new1 = new2 ThenNames("username").RefersTo = "=" & new1ThisWorkbook.SaveMsgBox "用户名修改完成"ElseMsgBox "两次用户名不一致,修改未完成", vbCritical, "错误"End IfEnd Sub

调试与优化编写的代码

On Error GoTo标签

On Error GoTo 标签实际就是在“on error”的后面加了一个GoTo语句,其中的“标签”就是替goto语句设置的标签,是一个数字或者带冒号的文本。标签告诉VBA,当程序运行过程中晕倒运行时错误时,跳转到标签所在行的代码继续执行程序,实际上就是让程序跳过出错的代码,从另一个地方重新开始执行程序。

Sub test()On Error GoTo aWorksheets("abc").SelectExit Sub
a: MsgBox "没有要选择的工作表"End Sub

on error resume next

Resume Next告诉VBA,如果程序发生错误,则忽略存在错误的代码,接着执行错误行之后的代码。如果程序一开始加入On Error Resume Next语句,运行程序时,及时程序中存在运行时错误,VBA也不会中断程序,而是忽略所有存在错误的语句,继续执行出错语句后的代码

Sub test()On Error Resume NextWorksheets("abc").SelectExit SubMsgBox "没有要选择的工作表"End Sub

On Error GoTo 0

使用On Error GoTo 0语句后,将关闭对程序中运行时错误的捕捉,如果程序在On Error GoTo 0语句后出现运行错误,将不会被捕捉到

Sub test()On Error GoTo 0Worksheets("abc").SelectExit SubMsgBox "没有要选择的工作表"End Sub

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/13983.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

VBA提高篇_07 Goto跳转 / Exit退出 /VBA错误处理

文章目录 使用逻辑变量控制循环使用Goto语句任意跳转捷径:使用Exit语句跳出结构保险: 使用错误处理改善用户体验On Error Goto Lablex:On Error Resume Next 使用逻辑变量控制循环 使用Goto语句任意跳转 经常在错误处理时使用 捷径:使用Exit语句跳出结构 注意: 避免使用while……

陈表达学VBA-msgbox辨别用户选择和代码调用

今天有个老客户提出需求&#xff1a; 模块&#xff1a;sub 过程A( ) ①如果是单独使用时则显示msgbox选择消息框 ②如果A过程调用B过程&#xff0c;则不显示消息框&#xff0c;默认选择是 msgbox没有设置默认值这个功能&#xff0c;msgbox也没有参数可以失败是用户点击还是代…

chatgpt赋能python:Python调用VBA宏:如何在Python中实现调用VBA宏

Python调用VBA宏&#xff1a;如何在Python中实现调用VBA宏 Python是一种强大的编程语言&#xff0c;它有许多不同的应用场景。在商业和办公自动化领域中&#xff0c;Microsoft Excel在十分重要且广泛使用。 Excel与VBA宏的结合为用户提供了一个灵活易用的工具&#xff0c;能够…

2023高考语文,用ChatGPT挑战全国卷作文,已达到双一流高校学生水平?

前言 2023年高考语文结束啦&#xff0c;今天我们用ChatGPT来挑战高考作文&#xff0c;一起来看看它的表现如何&#xff1f;ChatGPT突然爆火网络&#xff0c;它真的会取代人类的工作吗&#xff1f; 什么是ChatGPT&#xff1f; ChatGPT是由OpenAI开发的&#xff0c;OpenAI是一家…

关于我用chat gpt生成了一篇nba比赛前瞻预测文章,大家觉得怎么样!

4月8日&#xff0c;NBA常规赛将迎来洛杉矶湖人对阵菲尼克斯太阳的比赛。作为联盟中两支实力强劲的球队&#xff0c;这场比赛将是一场值得期待的对决。目前战绩方面&#xff0c;湖人队以31胜18负的战绩排名西部第5位&#xff0c;而太阳队则以38胜13负的战绩排名西部第2位。不难看…

如何用ChatGPT预测和优化市场需求,提前抢占市场份额?

预测和优化市场需求&#xff0c;并在市场份额上提前抢占优势&#xff0c;是市场营销中至关重要的一环。下面是一个简要的方案&#xff0c;利用ChatGPT来实现这一目标。 数据收集和分析&#xff1a; 首先&#xff0c;我们需要收集大量的市场数据&#xff0c;包括消费者行为、购…

在chatGPT的加持下Notes开发的速度

大家好&#xff0c;才是真的好。 chatGPT在国内外引起了广泛的话题讨论&#xff0c;Notes/Domino界的人也不甘示弱&#xff0c;不仅有人研究了怎么样使用chatGPT来进一步加快Notes的应用开发&#xff0c;而且还直接考虑到在Notes/Domino调用chatGPT功能。 是的&#xff0c;No…

还能承认错误,这个AI要杀疯了...

距离我上一篇文章提到ChatGPT&#xff0c;到现在&#xff0c;它又火了好几度&#xff0c;最新的口号是&#xff1a;关于 AI 的问题&#xff0c;可以直接问 AI&#xff0c; ChatGPT号称可以回答后续问题&#xff0c;承认错误&#xff0c;挑战不正确的前提&#xff0c;还能帮你修…

NLP与ChatGPT的碰撞:大模型和小模型联合发力

ChatGPT真的太火了&#xff01;作为NLP的终结者&#xff0c;ChatGPT又会与NLP发生怎么样的碰撞&#xff1f;大模型可以替代小模型吗&#xff1f;NLP又将何去何从&#xff1f;今天给大家推荐一本好书&#xff1a;《基于NLP的内容理解》&#xff01; 文章目录 一、背景二、书籍介…

讯鸿喜讯丨民建广州市委科技与信息化委员会莅临讯鸿

2023年2月17日&#xff0c;民建广州市委科技与信息化委员会成员莅临广州讯鸿网络技术有限公司&#xff08;以下简称讯鸿网络&#xff09;&#xff0c;举办首期“星五分享汇”活动&#xff0c;并召开2023年第一次全体委员会议。此次活动主要围绕科技与信息化等领域的前沿、热点问…

ChatGPT工作提效之在程序开发中的巧劲和指令(创建MySQL语句、PHP语句、Javascript用法、python的交互)

ChatGPT工作提效之程序开发中的巧劲 前言一、创建MySQL数据表1.创建指令2.交互评价 二、PHP交互语句1.创建指令2.交互评价 三、javascript的交互用法1.创建指令2.交互评价 四、python的交互1.创建指令2.交互评价 总结 前言 ChatGPT是一个基于GPT模型训练的聊天机器人&#xff…

微软 Reactor x 广州图书馆公益讲座|ChatGPT 漫谈

点击蓝字 关注我们 编辑&#xff1a;Alan Wang 排版&#xff1a;Rani Sun 微软 Reactor 为帮助广开发者&#xff0c;技术爱好者&#xff0c;更好的学习 .NET Core, C#, Python&#xff0c;数据科学&#xff0c;机器学习&#xff0c;AI&#xff0c;区块链, IoT 等技术&#xff0…

如何正确使用ChatGPT的Prompts

大多数人在使用ChatGPT时可能会遇到一些问题。以下是一些常见问题及其解决方法&#xff1a; 缺乏示例&#xff1a;在提示中没有包含示例&#xff0c;这可能导致结果不准确。为了增加获得所需结果的机会&#xff0c;您可以在标准提示中添加与任务相关的示例。 忽略使用角色&…

宕机超 12 小时,损失过亿,唯品会基础平台负责人被“祭天”

整理 | 朱珂欣 出品 | CSDN程序人生&#xff08;ID&#xff1a;coder_life&#xff09; 对于后端程序员来说&#xff0c;“高并发”并非新鲜的话题&#xff0c;经历过一次服务器宕机&#xff0c;职业生涯才“完整”。 但如果事故超过 12 小时&#xff0c;或许会直接造成职业…

ChatGPT扫盲微服务

1. 微服务架构介绍 微服务架构是一种分布式系统架构&#xff0c;将一个大型应用程序拆分成多个小型服务。每个服务都是独立的、自治的&#xff0c;可以独立部署、升级和扩展。微服务架构的主要特点包括&#xff1a; 松耦合&#xff1a;微服务之间的通信采用轻量级的协议&#x…

腾讯云AI绘画好不好用,与同类产品对比有何优势?来看看ChatGPT是如何回答的。

最近有不少开发者来咨询&#xff0c;那么多AI绘画平台&#xff0c;该如何选择&#xff1f; 既要性价比又要效果好&#xff0c;还能有保障&#xff0c;大厂当然是首选&#xff0c;毕竟算法、算力、储存与服务有足够的保障。 有开发者问腾讯云的AI绘画怎么样&#xff1f; 秉持…

「智能新能源」时代,谁才是真正的硬核技术玩家?

2022 年中国新能源车产销超过 680 万辆&#xff0c;新能源车渗透率 27.6%&#xff0c;其中有 80% 的产销来自于中国新能源汽车品牌。在这几个数据背后&#xff0c;反映的是&#xff1a; 中国的新能源汽车产业发展提速&#xff0c;正在加快从燃油车向新能源车的战略大转型&…

深度学习--优化器篇(超保姆级+附代码包含常用各种优化器)

引言 在深度学习过程中总会在代码中遇到优化器.Adam()&#xff0c;在上一篇实现卷积神经网络CNN的代码分析中也提到了优化器的概念,那么优化器如何通俗的理解呢&#xff1f;个人通俗理解(仅供参考)&#xff1a;为梯度下降法配置的一个的"领航员"&#xff0c;寻找模型…

李宏毅 深度学习

目录 深度学习与自然语言处理 | 斯坦福CS224n 课程带学与全套笔记解读&#xff08;NLP通关指南完结&#xff09;pytorch快速入门csdn快速入门OS包PIL包Opencv包Dataset类Tensorboard的使用torchvision.transforms 的使用torchvision中数据集的使用DataLoader的使用(torch.util…

自动驾驶索引

自动驾驶感知、融合、规控&#xff1a; https://blog.csdn.net/yuan2520?typeblog Backlog&#xff1a; 自动驾驶之心 https://blog.csdn.net/CV_Autobot?typeblog OpenPilot分析 | 从图像到油门/刹车 https://blog.csdn.net/CV_Autobot/article/details/129036228?spm1001.…