文档库 最新最全的文档下载
当前位置:文档库 › 第七篇 Excel自动化

第七篇 Excel自动化

ActiveSheet对象可用来引用处于活动状态的工作表。
https://www.wendangku.net/doc/a815657776.html,活动状态的工作薄名称

第41章 初识VBA
VBA全称为Visual Basic for Application,它是Visual
Basic的应用程序版本。作为功能强大的工具,VBA使Excel形成了独立的编程环境。本章将简要介绍什么是VBA以及如何学习Excel VBA。
41.1 什么是宏
在很多应用软件中都有宏的应用。什么是宏呢?计算机词典中有多种对于宏的定义。在此无需花费大量时间去研究那些拗口的定义哪个更准确。本书中讨论的宏仅限于微软Office软件包设计的一个特殊功能,目的是让用户的一些任务实现自动化。
与其他用于软件开发的单独编程语言不同,宏代码只能“寄生”于Excel文件之中,并且宏代码不能编译为可执行文件。
41.2 VBA的应用场景
Excel VBA作为一种扩展工具,得到了越来越广泛的应用,原因在于,很多实际应用中复杂的Excel操作都可以利用Excel
VBA得到简化。一般来说,Excel VBA可以应用在如下几个方面:
自动执行重复的操作
进行复杂的数据分析对比
生成报表和图表
个性化用户界面
Offic组件的协同工作
Excel二次开发
41.3 在Excel 中录制宏代码
41.3.1 录制宏是最好的学习工具
录制宏不仅是Excel 中一个非常实用的功能,而且是学习 VBA的好帮手。在 Excel 中,有两种方法可以开始录制一个宏。
1.利用Excel菜单:“工具”——宏——录制新宏,在“录制新宏”对话框中,设置宏的名称、保存位置以及快捷键,再单击“确定”按钮,就可以开始录制一个新的宏。
系统默认录制新宏的名称为“Macro”加数字序号的形式,例如Macro1、Macro2等等,建议用户使用能够代表代码功能的宏名称。宏名称可以包含字母、数字和下划线,但第一个字符必须是字母或中文字符,不能是数字,也就是“1Macro”不可以作为宏名称。建议在宏名称中不要使用中文字符,否则在非中文版的Excel中使用该宏时可能会出现兼容性问题。
2.利用Visual Basic工具栏
步骤1:单击视图——工具栏——Visual Basec,将显示工具栏。
步骤2:单击第二个按钮“录制宏”,同样会出现“录制新宏”对话框。
步骤3:单击“录制新宏”对话框的“确定”按钮,系统将开始录制宏,Visual Basic工具栏的第二个按钮将变为“停止录制”。
开始录制宏后,Excel中绝大部分操作将被记录为宏代码,此时可以开始在Excel

中进行相关的操作。操作结束后,单击Visual
Basic工具栏的“停止录制”按钮,将停止本次录制宏。单击“查看代码”按钮或按组合键就可以打开VBE(V
isual Basic Editor,即VBA集成开发环境)窗口,在代码窗口中将看到刚才录制的宏代码,下一章中将讲述VBE中主要窗口的功能。
对于VBA的初学者,最困难的事情就是想要实现一个功能,却不知道代码从何写起,录制宏可以很好地帮助大家。在Excel中进行操作的同时录制宏,就可以看到整个操作过程的代码,请注意这只是一个“半成品”,经过必要的修改才能得到更高效更智的代码。
41.3.2 录制宏的局限性
录制宏可以忠诚地记录Excel 操作,但也有其本身的局限性,主要表现在以下几个方面:
1、录制宏产生的代码不一定完全等同于用户的操作,例如用户设置保护工作表时输入的密码就无法记录在代码中,设置工作表控件的属性也无法产生相关的代码。
2.录制宏产生的代码执行效率不高,因为代码中大量使用Activate和Select等方法,影响了代码的执行效率,在实际应用中需要进行相应的优化。
第42章 VBA的组装工厂——Visual Basic Editor
Visual Basic
Editor(以下简称VBE),是指Excel及其他Office组件中集成的VBA编辑器,本章将介绍VBE中主要功能窗口的功能。
42.1VBE窗口介绍
42.1.1 VBE窗口介绍
在VBE界面中,除了和一般Windows应用程序类似的菜单和工具栏外,其工作区中还提供了多个功能窗口供用户选择。单击VBE“视图”菜单,将显示菜单项,用户可根据需要和使用习惯选择在VBE中显示的功能窗口。
42.1.2 工程窗口
工程窗口以树形结构显示Excel中的所有工程,即Excel中所有已经打开的工作簿,包含隐藏工作簿的加载宏。每个工程作为一个树结构的根结点,一般显示为“VBAProject(Book1.xls)”的形式。
42.1.3 属性窗口
属性窗口会列出选取对象的属性,用户可以修改这些属性的值。当选取了多个控件时,属性窗口会列出所有控件所共有的属性;如果此时更改某个属性的值,那么被选中的多个控件的相应属性会同时被修改。属性窗口所示分为两部分:对象框和属性列表。
42.1.4 代码窗口
代码窗口用来输入、显示以及编辑VBA代码。打开对象的代码窗口后,可以查看模块或对象中的代码和在它们之间进行复制和粘贴。
42.1.5 立即窗口

在立即窗口中键入或粘贴一行代码,然后按键可以执行该代码。在代码中使用Debug.Print可将内容输出到立即窗口中。
注意:立即窗口中的代码是不能被保存的,关闭Excel后,立即窗口中的内容将丢失。
第43章 VBA语言基础
VBA作为一种编程语言,具有其自身特有的语法规则。本章将介绍VBA编程的基础知识,包括变量与常量、过程、程序结构以及对象的属性、方法和事件。
43.1 变量与常量
43.1.1 数据类型
数据类型用来决定可保存何种数据。VBA中的数据类型包括Byte,Boolean,Integer,Long,Currency,Decimal,Single,Double,Date,Stri
ng,Object,Variant(默认)和用户定义类型等。不同数据类型所需要的存储空间并不相同。
数据类型关键字类型标识符字节数
字节型Byte无1
布尔型Boolean无2
整数型Integer%2
长整数型Long&4
货币型Currcncy@8
小数点型Decimal无14
单精度型Single!4
双精度型Double#8
日期型Date无8
字符串型(定长)String$字符长度(1~65 400)
字符串型(变长)String$字符长度+10
对象型Object无4
变体型Variant无以上任意类型,可变

43.1.2 变量
变量用于保存在程序运行过程中需要临时保存的值或对象,在程序运行过程中其值可以发生改变。
在VBA中,变量无需声明就可以直接使用,此时该变量为变体变量。但使用之前声明变量是一个良好的编程习惯,同时也可以提高程序的运行效率。
在VBA中用Dim语句声明变量。下述代码声明局部变量a为整数型变量。
Dim a as Integer

使用类型标识符可以简化为:
Dim a%

注意:如果在同一个语句中同时声明多个变量,如下面的Dim语句中声明了两个变量,其中的变量a实际声明为Variant变量,则应该使用如下代码:

Dim a as Integer,b as Integer
变量赋值使用等号,等号右侧可以是表达式。如下代码是为变量a赋值。
a = 128+56
43.1.3 常量
常量用于存储固定信息,其值不会发生改变,使用常量可以增加程序的可读性。例如VBA中的常量vbGreen,其值为65
280,在代码中设置绿色时使用常量vbGreen,使得代码更具可读性。
在VBA中用Const语句声明常量。如下代码声明字符型常量ClubName。

Const clubName As String = "ExcelHome"

43.2 运算符
VBA中有如下4种运算符:
1.算术运算符:用来进行数学计算的运算符。
2.比较运算符:用来进行比较的运算符。
3.连接运算符:用来合并字符串的运算任。
4.逻辑运算符:用来执行逻辑运算的运算符。
连接运算符包括"&"运算符和"+"运算符两种。
43.3 过程
过程(Procedure)是可以执行的语句序列单元,所有可执行的代码必须包含在某个过程内,任何过程都不能嵌套在其他过程中。过程的名称只能在模块级别进行定义。
VBA中有3种过程,即Sub过程、Function过程和Property过程。
1.Sub过程执行指定的操作,但不返回运行结果,以关键Sub开关和关键字End
Sub结束。可以通过录制宏生成Sub过程或在VBE窗口里直接编写。
2.Function过程执行指定的操作,可以返回运行结果,以关键字Function开关和关键字End
Function结束。Function过程可以在其他过程中调用,也可以在工作表的公式中使用,就像Excel的内置函数一样。
3.Property过程用于设置和获取自定义对象属性的值,或者用来设置对另外一个对象的引用。
43.4 程序结构
VBA中的程序结构与控制和大多数编程语言相同,下面介绍最基本的几种程序结构。
43.4.1 条件语句
程序代码经常用到条件判断,并且根据结果执行不同的代码。在VBA中有If/Then语句和Select Case语句两种条件语句。
下面的If/Then语句判断活动单元格的内容,如果是“Excelhome”则将其字号设置为10,否则将字号设置为9。
If ActiveCell.Value = "ExcelHome" Then ActiveCell.Font.Size = 10 Else
ActiveCell.Font.Size=9
43.4.2 循环语句
对程序中多次重复执行的某段代码就可以使用循环语句。在VBA中循环语句有多种形式,包括For循环、Do循环和While循环。下面的For循环实现1~10的累加功能。
Sub ForLoop()
Dim i As Integer, iSum As Integer
iSum = 0
For i = 1 To 10
iSum = iSum + i
Next
MsoBox iSum,,"ForLoop"
End Sub
43.4.3 With语句
With语句可以在一个单一对象或一个用户定义类型上执行一系列的语句。使用With语句不仅可以简化程序代码,而且可以提高代码的运行效率。With/End
With结构中以“.”开头的语句相当于引用了With语句中指定的对象。当程序一旦进入W

ith/End
结构,With语句指定的对象就不能改变。因此不能用一个With语句来设置多个不同的对象。如下代码是使用With语句设置活动工作表的相关属性。
With ActiveSheet
.Visible = True
.Cells(1,1 ) = "ExcelHome"
.Name = .Cells(1,1)
End With
43.5 对象
对象代表应用程序中的元素,例如工作表、单元格、图表或窗体等。应用程序提供的对象按照层次关系进行排列管理。Excel应用程序中的顶级对象是Application对象的子对象,反之,Application对象是这些对象的的父对象。
许多子对象都有自己的子对象。例如Workbook对象包含Worksheets对象,或者说,Workbook对象是Worksheets对象的父对象。Worksheets对象是一种称为集合中的单个Worksheet对象。
43.5.1 属性
属性是指对象的特征、如大小、颜色或屏幕位置,也可指某一方面的行为,诸如对象是否被激活或是否可见。通过修改对象的属性值可以改变对象的特性。如下代码是设置活动工作表的名称为“ExcelHome”。
https://www.wendangku.net/doc/a815657776.html, = “ExcelHome”
43.5.2 方法
方法指对象能执行的动作。例如使用Worksheets对象的Add方法可以添加一个新的工作表,代码如下:
Worksheets.Add
在代码中,属性和方法都是通过连接符“.”来和对象连接的。
43.5.3 事件
事件是一个对象可以辨认的动作,像单击鼠标或按下某键等,并且可以指定代码针对此动作来做出响应。用户操作、程序代码的执行和系统本身都可以触发相关的事件。
第44章 与Excel进行交互
在Excel中,系统提供了各式各样的对话框与用户进行交互;在使用VBA编写程序时,为了提高代码的灵活性和程序的友好度,也经常需要实现用户与Excel的交互功能。本章将介绍如何InputBox和MsgBox实现输入和输出信息,以及如何调用Excel的内置对话框。
44.1使用MsgBox输出信息
MsgBox函数通常应用于如下几种情况:
1.输出代码最终运行结果
2.产生一个消息框用于提醒用户
3.在代码运行过程中显示某个变量的值,用于调试代码
MsgBox函数的语法格式如下:
MsgBox(prompt[,buttons][,title] [,helpfile,context])
prompt参数用于设置消息框的提示文本信息,最大长度为1
023个字符。显然这么多的字符无法显示在同一行,如果代码中没有使用强制换行,系统将按照每行102个字符进行自动换行

处理,多数情况下这并不符合用户的使用习惯。
在文本信息中使用vbCrLf或 vbNewLine常量可以进行强制换行。
示例44.1 显示多行文本信息 
步骤1:打开一个新的工作簿文件,按Alt+F11组合键切换到VBE窗口。
步骤2.在工程窗口中插入“模块”,修改其名称为“MsgBoxDemo”。
步骤3.在模块MsgBoxDemo中写入如下代码。
Sub MultiLineDemo()
'定义变量
Dim MsgStr As String
'生成提示信息
MsgStr = "欢迎加入Excel Home论坛!"&vbCrLf
MsgStr =MsgStr & "Excel Home 是微软技术社区聪明成员" & vbCrLf
MsgStr = MsgStr & "Let's do it better!"
'显示消息框
MsgBox MsgStr,,"欢迎"
 End Sub
步骤4.返回Excel界面,运行宏MultiLineDemo,将显示消息框。
buttons参数用于指定消息框显示按钮的数目及形式、图标样式和缺少按钮等。组合使用参数值可以显示多种不同风格的消息框;省略buttons参数时,消息框只显示一个确定按钮。
44.2 如何利用InpuitBox输入
程序中往往需要用户输入很多内容,例如数字、日期或文本等,这就需要使用InpuitBox获取用户输入。
使用VBA提供的InpuitBox函数可以实现用户输入,其语法格式为:
InpuBox(prompt[, title] [, default] [,xpos] [, helpfile, context])
输入框中必须显示相关的提示信息,即prompt参数,否则用户无法知道需要输入什么样的内容。设置输入框的标题,即title参数,使得输入框更接近Excel的内置对话框风格;如果省略该参数,则输入框的标题为“Micrlsoft
Excel”。
注意:用户在输入框中输入的内容是否满足要求,需要在代码中进行相应判断,以保证后续程序可以正确地执行。
除了InputBox函数之外,Excel
VBA的InpuBox方法(Application.InputBox)也可用于接收用户输入的信息,二者的用法基本相同。区别在于InpuBox方法可以指定返回值的数据类型。其语法格式为:
InpuBox(Prompt,Title,Default, Left, Top, HelpFile, HelpContextId, Type)
示例44.2 利用InpuBox方法输入员工号信息
步骤1.打开—个新的工作簿文件,按Alt+F11组合键切换到VBE窗口。
步骤2.在工程窗口中插入“模块”,修改其名为“InputBoxDemo”。
步骤3.在模块InputBoxDemo中写入如下代码。
Sub ExcelInputBoxDemo()
'定义变量
Dim newID As Integer
Do
'提示用户输入

员工号
newID = Application.InputBox("请输入员工号(四位数字):", "员工信息管理系统", Type:=1)
'如果输入的是四位员工号就退出循环
Loop Until Len(CStr(newID)) = 4
'显示信息框
MsgBox "您输入的员工号为 " & newID, vbInformation, "提示信息"
End Sub
步骤4.返回Excel界面,运行宏ExcekInputBoxDemo,将显示输入框;如果输入“abcd”后单击“确定”,将显示消息框。由此可以看出,使用InputBox方法,系统将根据Type参数判断输入的数据类型是否符号要求。
注意:在VBA代码中直接使用InputBox相当于调用VBA的InputBox函数。
44.3 Excel内置对话框
用户使用Excel时,系统出现的对话框统称为Excel内置对话框,例如单击“文件”——“打开”将显示“打开”对话框。VBA程序与用户之间的交互也可以借助这些内置对话框来实现。
Application对象的Dialogs集合中包含大部分Excel内置对话框,每种对话框对应一个VBA常量。在VBA帮助中搜索“内置对话框参数列表”;可以查看所有的内置对话框参数列表。
使用Show方法可以显示一个内置对话框,下面代码将显示“打开”对话框。
Application.Dialogs(xlDialogOpen).Show
第45章 自定义函数
自定义函数与Excel工作表函数相比具有更强大、更灵活的功能。自定义函数可以用来简化公式,也可以用来完成Excel工作表函数无法完成的功能。
45.1 什么是自定义函数
自定义函数(User-defined Worksheet Functions
简称UDF)就是用户创建的用于满足特定需求的函数,是对于Excel内置工作表函数的一个补充。Excel已经提供了数百个工作表函数可供选择使用,有必要创建自定义函数吗?答案是肯定的。自定义函数的优势在于:
1.自定义函数可以简化公式。一般情况下,组合使用Excel工作表函数完全可以满足绝大多数应用,但是复杂的公式有可能太冗长和繁琐,其可读性非常差,不易于修改,除了公式的作者之外,公式的使用者可能很难理解公式的含义。此时就可以通过使用自定义函数来进行简化。
2.自定义函数与Excel工作函数相比具有更强大和更灵活的功能。Excel实际应用中,要求是千变万化的,仅仅使用Excel工作表函数常常不能圆满地解决问题。此时就可以考虑使用自定义函数来满足实际工作中的个性化需求。
与Excel工作表函数相比,自定义函数的弱点也是显而易见的,就是自定义函数的效率要远远低于Excel工作表函数功能,应该使用45.3节中讲述的方法进行引用。
 

 45.2 函数的参数与返回值
VBA中参数有两种传递方式:按值传递(关键字ByVal)和按地址传递(ByRef)。参数的默认传递方式为按地址传递,因此如果希望使用这种方式传递参数,可以省略参数前的关键字。
这两种传递方式的区别在于,按值传递只是将参数值的副本传递到调用过程中,在过程中对于参数的修改,并不改变参数的原始值;按地址传递是将该参数的引用传递到调用过程中,在过程中任何对于参数的修改都将改变参数的原始值。
注意:由于按地址传递方式会修改参数的原始值,所以需要谨慎使用。
函数属于Function过程,其区别于Sub过程之处在于,Function过程可以提供返回值。函数可以返回一个单一值或数组。如下面的自定义函数TaxRate根据工资数返回相应的税费税率,如果在工作表中使用公式实现则需要多层If结构嵌套。
Function TaxRate(Salary)
Select Case Salary - 1000
Case Is <0
TaxRate = 0
Case Is <=500
TaxRate = 0.05
Case Is <= 2000
TaxRate = 0.1
Case Is <= 5000
TaxRate = 0.15
Case Else
TaxRate =0.2
End Select
End Function
45.3 如何在VBA中引用工作表函数
由于工作表函数的效率远远高于自定义函数,因此对于工作表函数已经实现的功能,可以在VBA代码中直接引用工作表函数,其语法格式为:
Application.WorksheetFunction.工作表函数名称
WorksheetFunction.工作表函数名称
Application.工作表函数名称
在VBA中Application对象可以省略,所以第二种语法格式实际上是第一种语法格式的简化。为了方便读者识别,本书后续章节中所有对于工作表的函数的引用都将采用第一种格式。
在VBA代码中调用工作表函数时,函数参数的顺序与在工作表单元格公式中相同,但是具体表示方法会略有不同,例如在工作表中使用公式示A1和A2单元格的和,公式为:
=SUM(A1,A2)
其中参数为两个单元格的引用A1和A2。而在VBA代码中调用工作表函数SUM时,需要使用VBA中单元格的引用方法,如下所示:
Application.WorksheetFunction.Sum(Cells(1,1),Cells(2,1))
45.4 在VBA中引用自定义函数
除非自定义函数不使用任何参数,否则自定义函数不能通过单击VBE菜单“运

行”——“运行子过程/窗体”来运行自定义函数过程。在VBA代码中,只能在另一个过程里调用该自定义函数。
45.5 在工作表中引用自定义函数
在工作表单元格公式引用自定义函数的方法和引用普通Excel工作表函数的方法基本相同。
步骤1.单击选中目标单元格。
步骤2.单击菜单“工具”——选项,在“视图”选项卡中,确认已经选中“编辑栏”。
步骤3.单击“编辑栏”的“插入函数”按钮,或单击菜单“插入”——“公式”。
步骤4.在“插入函数”对话框中选择类别“用户定义”,在“选择函数”列表框中将显示可供选择的全部自定义函数名称。
注意:使用关键字Private声明的私有自定义函数不会出现在“插入函数”对话框中,私有自定义函数不能用于公式里,只能在另外一个VBA过程里调用这些私有的自定义函数。
步骤5.单击自定义函数名称,然后单击“确定”,假设选定的函数为TaxRate。
步骤6.在“函数参数”对话框中输入相关参数,单击“确定”,单元格中将出现相应的计算结果。
45.6 自定义函数的限制 
并非所有的功能都可以在自定义函数中实现。在工作表单元格公式中引用自定义函数时,不能更改Microsoft
Excel的环境,这意味着自定义函数不能执行以下任何操作:
1.在工作表中插入、删除单元格或设置单元格格式。
2.更改其他单元格中的值。
3.在工作簿中移动、重命名、删除或添加工作表。
4.更改任何环境选项,例如计算模式或屏幕视图。
5.向工作簿中添加名称。
6.设置属性或执行大多数方法。
其实,Excel中的内置工作表函数同样也不能更改Microsoft Excel的环境,函数只能执行计算以在输入它们的单元格中返回某个值或文本。
如果在其他过程中调用自定义函数就不存在上述限制。尽管如此,为了规范代码,建议所有上述需要更改Microsoft
Excel环境功能的代码在Sub过程中实现。
45.7如何制作加载宏
加载宏(英文名称为Add-in)是一类程序的统称,它们可以为Microsoft
Excel添加可选的命令和功能。例如,“分析工具库”加载宏程序提供了一套数据分析工具,在进行复杂统计或工程分析时,可以节省操作步骤,提高分析效率。
Microsoft
Excel有三种类型的加载宏程序:Excel加载宏、自定义的组件对象模型(COM)加载宏和自动化加载宏。本节讨

论的加载宏特批Excel加载宏。
理论上来说,任何一个工作簿可以制作成为加载宏,但是某些工作簿不适合制作成为加载宏,例如一个包含图表的工作簿,如果该工作簿转换为加载宏,那么就无法查看该图表,除非利用VBA代码将图表所在的工作表拷贝成为一个新的工作簿。
制作加载宏的步骤非常简单,一般来说有两种方法可以将普通工作簿转换为加载宏。
1.在VBE的工程窗口中双击ThisWorkBook,按F4显示属性窗口,在其中修改IsAddin属性的值为True。
2.另存为加载宏。
步骤1.在Excel窗口中单击菜单“文件”——“另存为”。
步骤2.在“另存为”对话框中,单击保存类型下拉列表框,选择“Microsoft Office Excel加载宏(*.xla)”。
步骤3.选择保存位置,加载宏的缺省目录为“c:\Documents and Settings\<用户登录名>\Application
Data\Microslft\Addlns\”。
步骤4.单击“确定”按钮。
系统默认的加载宏扩展名为XLA,但并非一定要用XLA作为加载宏的扩展名,使用任意的扩展名都不会影响加载宏的功能。为了便于识别,建议使用XLA作为加载宏的扩展名。
第46章 如何操作工作簿、工作表和单元格
在Excel中,对工作簿、工作表和单元格的操作,多数都可以利用VBA代码实现两样的效果。本章介绍了工作簿对象的Workshee
t对象的引用方法以及添加删除对象的方法。Range对象是Excel最基本也是最常用的对象之一,对于Rabge对象处理的方法也有多种,本章将进行详细的介绍。
46.1 Workbook对象
Workbook对象代表Microsoft
Excel工作簿,也就是通常据说的Excel文件,每个Excel文件都是一个Workbook对象。Workbook集合代表所有已经打开的工作簿,加载宏除外。
在代码中经常用的Workbook对象是ThisWorkbook和ActiveWorkbook。
1.ThisWorkbook对象指代码所在的Workbook对象。
2.ActiveWorkbook对象指Excel中活动窗口中的Workbook对象。
46.1.1 引用Workbook对象
使用Workbooks属性引用工作簿有如下两种方法。
1.使用工作簿序号引用Workbook对象,语法格式为:
Workbooks.Item(工作簿序号)
工作簿序号是指创建或打开工作簿的顺序号,Workbooks(1)代表Excel应用程序中创建或打开的第一个工作簿,而Workbook(
Workbooks.Count)为最后一个工作簿,其中Workbooks.Count返回Workbooks集合中包含的Workbook对象的个数。即便是隐藏工作簿也包括在序号计数中,也就是说可以使用工作簿序

号引用隐藏的Workbook对象。
Item属性是大多数对象集合的默认属性,因此可以省略Item关键字,简化为下面的语法形式:
Workbooks(工作簿序号)
2.使用工作簿(或加载宏)名称引用Workbook对象,语法格式为:
Workbooks(工作簿名称)
利用Workbook对象的Name属性可以返回工作簿名称,但是Name为只读性,不能利用Name属性修改工作簿名称;如果需要更改工作簿名称,应使用Workbook对象的SaveAs方法以其他名称保存工作簿。下面代码将工作簿Book1.xls另存到C:\temp目录,文件名称为ExcelHome.xls,如果不指定目录,则新的工作簿保存在与原来工作簿相同的目录中。
Workbooks("Book1.xls").SaveAs"c:\temp\ExcelHome.xls"
46.1.2 打开一个已经存在的工作簿
使用Workbooks对象的Open方法可以打开一个已经存在的工作簿,其语法格式如下:
Workbooks.Open FileName:="c:\temp\ExcelHome.xls"
注意:参数名和参数值之间应该使用“:=”符号,而不是等号。
参数名称可以省略,代码简化为:
Workbooks.Open"c:\temp\ExcelHome.xls"
46.1.3 遍历工作簿
对于两种不同的引用工作簿的方法,分别可以使用For Each循环和For/Next循环遍历Workbooks集合中的Workbook对象。
示例46.1 遍历工作簿名称
步骤1.在工程中插入模块,并修改其名称为“AllWorkBook"。
步骤2.在模块AllWorkBook中写入如下代码。
Sub AllWorkBook1()
'声明变量
Dim WK As Workbook, iRow As Integer
ActiveSheet.Cells(1, 1) = "AllWorkBook1 运行结果"
iRow = 2
'循环取得WorkBooks集合中的所有WorkBook对象
For Each WK In Application.Workbooks
'将工作簿的名称写入工作表第一列
ActiveSheet.Cells(iRow, 1) = https://www.wendangku.net/doc/a815657776.html,
'行号递增
iRow = iRow + 1
Next
End Sub
Sub AllWorkBook2()
'声明变量
Dim i As Integer, iRow As Integer
ActiveSheet.Cells(1, 2) = "AllWorkBook2 运行结果"
iRow = 2
'设置循环变量的初值和终止值
For i = 1 To Application.Workbooks.Count
'将工作簿的名称写入工作表第二列
ActiveSheet.Cells(iRow, 2) = Workbooks(i).Name
'行号递增
iRow = iRow + 1
Next
End Sub
步骤3.运行宏AllWorkBook1。
步骤4.运行宏AllWorkBook2。 
运行结果,两个过程的结果分别显示在第一列和第二列,内容完全相同,实际应用中可以根据需

要选择任何一种遍历方法。这两种遍历方法适用于多数对集合,如遍历Worksheets集合中的Worksheet对象。
46.1.4 添加一个新的工作簿
在Excel中单击菜单“文件”——新建,然后单击新建工作簿窗口的“空白工作簿”;或单击标准工具栏的“新建”按钮,可以在Excel中产生一个新的工作簿。利用WorkBook对象的Add方法也可以实现添加一个新的工作簿,其语法格式为:
Workbooks.Add
46.1.5 保护工作簿
从安全角度考虑,可以为工作簿设置密码。下面代码设置活动工作簿的保密密码为“abc”。
ActiveWorkbook.Protect Password:="abc"
如果需要修改工作簿,可以利用Unprotect方法取消工作簿的保护。
ActiveWorkbook.Unprotect Password:="abc"
46.1.6 关闭工作簿
使用WorkBook对象的Close方法可以关闭打开的工作簿。如果该工作簿有更改,Excel将显示对话框,询问是否保存更改。
关闭工作簿时设置SaveChanges参数值为False,将放弃所有对该工作簿的更改,并且不会出现保存提示框。
ActiveWorbook.Close SaveChanges:=False
46.2 Worksheet对象
Worksheet对象代表一张工作表。Worksheet对象既是Worksheets集合的成员,同时又是Sheets集合的成员。Worksheets集合包含工作簿中所有的Worksheet对象。Sheets集合除了包含工作簿中所有的Worksheet对象外,还包含工作簿中所有的图表工作表(Chart)对象和宏表对象。
ActiveSheet对象可用来引用处于活动状态的工作表。
46.2.1 引用Worksheet对象
对于Worksheet对象,有如下3种引用方法。
1.使用工作表序号引用Worksheet对象,语法格式为:
Worksheets(工作表序号)
工作表序号是按照工作表的排列顺序依次编号的,Worksheets(1)代表工作簿中的第一个工作表,而Worksheets(Worksheets.Co
unt)代表最后一个工作表,其中Worksheets.Count返回Worksheets集合中包含的Worksheet对象的个数。隐藏工作表也包括在序号计数中,也就是说可以使用工作表序号引用隐藏的Worksheet对象。
2.使用工作表名称引用Worksheet对象,语法格式为:
Worksheets(工作表名称)
使用工作表名称引用Workbook对象时,工作表的名称不区分大小写字母,因此Worksheets(“sheet1”)引用的是同一个工作表,但是Worksheet对象的Name属性返回值是工作表的实际名称,可能和引用工作表时的名称有大小写区别。
3.使用工作表的代码名(Codename)引用Worksheet对象。假设工

作簿中有3个工作表。
在VBE窗口中查看工程窗口和属性窗口。在工程窗口中Worksheet对象显示为“工作表代码名(工作表名称)”的形式,对应在属性窗口中,“名称”栏为代码名,“Name”栏为工作表名称。使用代码名Sheet1等同于Worksheets(Sht3)。工作表的名称和其代码名也可以相同。
46.2.2 遍历工作簿中的所有工作表 
遍历工作表的方法与遍历工作簿的方法完全相同,可以使用For Each循环或For/Next循环,具体请参阅46.1.3小节。
46.2.3 添加新的工作表
在Excel单击菜单“插入”——“工作表”可以在当前工作簿中插入一个新的工作表。使用Add方法也可以在工作簿中插入一个新的工作表,其语法格式为:
Sheets.Add
提示:插入指定名称的工作表可以使用代码https://www.wendangku.net/doc/a815657776.html, =
"newSheet",虽然在VBA帮助中没有说明Add方法具有Name属性,但上述代码是可以运行。需要注意的是,采用这个简化方式时,无法使用Add方法的参数。
46.2.4 拷贝和移动工作表
Worksheet对象的Copy方法和Move方法可以实现工作表的拷贝和移动。其语法格式为:
Copy(Befor,After)
Move(Before,After)

Before和After均为可选参数,二者只能选择一个。Copy和Move方法不仅可以实现同一个工作簿之内的工作表的拷贝和移动,也可以实现工作簿之间的工作表拷贝和移动。下面的代码可以将工作簿Book1.XLS中的工作表Sheet1拷贝到工作簿Book2.XLS中,并放置在原有的第3个工作表之前。
Workbooks("Book1.xls").Sheets("Sheet1").Copy
Before:=Workbooks("Book2.xls").Sheets(3)
46.2.5 如何保护工作表
为了防止工作表被意外修改可以设置工作表保护密码。Worksheet对象Protect方法有很多可选参数,其中Password参数用于设置保护密码。
ActiveSheet.Protect "ExcelHome"
46.2.6 删除工作表
使用Worksheet对象的Delete方法删除工作表时,将会出现提示框,单击“删除”完成删除工作表。
如果不希望在删除工作表时出现这个提示框,可以使用DisplayAlerts禁止提示框的显示。
Application.DisplayAlerts = False
Worksheets("Sheet1").Delete
Application.DisplayAlerts = True
注意:代码中如果使用了Application.DisplayAlerts=False,
在使用Application.DisplayAlerts=True恢复之前,所有的系统提示信息都是将被屏蔽。如果没有使用代码进行恢复,则在代码运行结束后,Micorosoft
Excel将该属性设置为True。

46.3 Range对象
Range对象代表工作表中的单个单元格或多个单元格组成的区域,该区域可以是连续的也可以是非连续的。虽然单元格是Excel操作的基本单位,但是Excel中不存在单元格对象。
46.3.1 引用单个单元格
在VBA代码中有多种方法可以用来引用单个单元格。
1.使用“[单元格名称]”的形式:这是在写法上最简单的一种引用方式。其中单元格名称与在工作表单元格公式中使用的A1样式单元格名称完全相同,如[C5]代表工作表中的C5单元格。在这种引用方式中,单元格名称不能使用变量。
2.使用Cells属性:Cells属性返回一个Rabge对象。其语法格式为:
Cells(RowIndex,ColumnIndex)
Cells属性的参数为行号和列号。行号是一个数值,其范围为1~65
536。列号可以是数值,其范围为1~256;也可以是字母形式的列标,其范围为“A”~“IV”。工作表所支持的列数量为256,其列标为“IV”。同样是引用C5单元格,可以有两种写法:
Cells(5,3)
Cells(5,"c")
2.使用Range(单元格名称)形式:其中单元格名称可以使用变量或表达式。在参数名称的表达式中可以使用"&"连接符,连接两个字符串。
Range(“C5”)
46.3.2 单元格格式的常用属性
常用的单元格格式有字体大小、字体颜色、背景色以及边框等,下面的代码将设置“A1:D10”区域的格式为:红色11号字,背景色为青色,并添加边框。
Sub CellFormat()
With Range("A1:D10")
With .Font
'设置字号
'Size = 11
'设置字体颜色为红色
'Color = vbRed
End With
'设置单元格边框线
Borders.LineStyle = xlContinuous
'设置单元格背景色为青色
.Interior.Color = vbCyan
End With
End Sub
46.3.3 添加批注
Comment对象代表单元格的批注,是Comments集合的成员。Comment对象并没有Add方法,添加批注需要使用Range对象的AddCo
mment方法。下述代码在活动单元格添加批注,内容为“ExcelHome”。
Activecell.AddComment "ExcelHome"
利用For Each循环可以遍历Comments集合中的所有Comment对象。
46.3.4 如何表示一个区域
Range属性除了可

以返回单个单元格,也可以返回单元格区域。Range的语法格式如下:
Range(cell1,cell2)
参数Cell必须为A1样式引用,是一个单元格或区域的名称字符串。参数Cell2,可以是一个包含单个单元格、整列或整行的Range对象,也可以是一个单元格或区域的名称字符串。
如果引用以A3单元格和C6单元格之间所包含的单元格区域对象,可以使用如下几种方法:
Range(“A3:C6”)
Range([A3],[C6])
Range(Cells(3,1),Cells(6,3))
Range(Range("A3"),Range("C6"))
第一种Range(“A3,C6”)引用方式是最常用的方式,其中的冒号是区域操作符,其含义是以两个A1样式单元格为顶点的矩形单元格区域。
46.3.5 如何定义名称
在工作表公式中,经常通过定义名称来简化工作表单元格公式。本节所批的名称是单元格区域的定义名。Workbook对象的Names集合代表工作簿中所有名称组成的集合。Add方法用于指定新的名称,参数RefersToR1C1用于指定单元格区域,格式为R1C1引用方式。利用Range对象的Name属性,指定名称的代码为:
Range("A3:D6").Name = "data"
46.3.6 选中工作表的指定区域
在VBA代码中经常要引用某些特定区域,CurrentRegion属性和UsedRange属性是两个最常用的属性。
CurrentRegion属性返回Range对象,就是通常据说的当前区域。当前区域是一个由任意空行和空列包围的最小矩形单元格区域。按Ctrl+Shift+8组合键可以选中当前区域,选中着色区域内的任意单元格时,即使该单元格没有内容,按Ctrl+Shift+8组合键,同样会选中相应的着色区域。
UsedRange属性返回Range对象,代表指定工作表上的已使用区域,该区域是由工作表中已经被使用的单元格组成的矩形单元格区域。这里的“使用”与单元格是否有内容无关,即使只是改变了单元格的格式,这个单元格也是已经被告使用,它将被包括在UsedR
ange属性返回的Range对象中。
可以使用Rabge对象的Select方法或Activate方法来检查相应区域的范围。
https://www.wendangku.net/doc/a815657776.html,edRange.Select
https://www.wendangku.net/doc/a815657776.html,edRange.Activate
46.3.7 特殊区域——行与列
行与列是工作表中经常用到的两个Range对象,对于行与列的引用既可以使用Rows属性和Columns属性,也可以使用Range属性。
引用第1行至第5行的区域可以使用如下几种形式:
Rows(“1:5”)
Range(“A1:IV5”)
Range(“1:5”)
列的引用方法与上述行的引用方式类似。

例如引用A列~E列的区域可以使用如下几种形式:
Colums("A:E")
Range("A1:E65536")
Range("A:E")
46.3.8 删除单元格
Range对象的Delete方法可删除一个单元格或单元格区域。下面代码将删除C3:F5单元格区域,其下的替补单元格向上移动,也就是原来C6:F8单元格区域将向上移动到被删除的区域。
Range("C3:F5").Delete Shift:=xlShiftUp
46.3.9 插入单元格
Range对象的Insert方法可在工作表中插入一个单元格或单元格区域,其他单元格作相应移动以腾出空间。下面代码在工作表的第2行插入单元格,原工作表的第2行单元格将占据第3行的位置。
Rows(2).Insert
46.3.10 合并区域与相交区域
Union方法返回Range对象,代表两个或多个区域的合并区域,其参数为Range类型。
Application.Union(Range("A3:D6"),Range("C5:F8"))

Intersect方法返回Range对象,代表两个或多个单元格区域重叠的矩形区域,其参数为Range类型,如果参数单元格区域没有重叠区域,那么结果为Nothing。
Application.Intersect(Range("A3:D6"),Range("C5:F8"))
利用 Intersect方法可以判断某个单元格区域是否完全包含在另一个单元格区域中。
第47章 事件的应用
在Excel
VBA中,事件是指对象可以辨认的动作。用户可以指定VBA代码来对这些动作做出响应。Excel可以监视多种不同类型的事件,Excel中的工作表、工作簿、应用程序、图表工作表、查询表和控件等不同对象都有不同的事件,而且每个对象都有多种相关的事件,本章将主要介绍工作表和工作簿的常用事件。
47.1 事件过程
事件过程作为一种特殊的Sub过程,在事件被触发时执行,如果事件过程包含参数,系统会为相关参数赋值。事件过程必须写入相应的模块中才能发挥作用,工作簿事件过程须写入Thisworkbook模块中,工作表事件过程则须写入相应的工作表模块中;且只有过程所在工作表的行为可以触发该事件。
47.2 工作表事件
工作表事件发生在特定的Worksheet对象中。Worksheet对象也是Excel最常用的对象之一,因此实际应用中经常会用到Worksheet对象事件。
47.2.1 Change事件
工作表中的单元格被用户手工修改或被VBA代码修改时,将触发工作表Change事件。值得注意的是,虽然事件的名称是Change
但是并非工作表中单元格的任何变化都能触发该事件。
Change事件的参数Target是Change变量,代表工作表中发生变化

的区域,它可以是一个单元格也可以攻玉是多个单元格组成的区域。在实际应用中,用户通常希望只有工作表中的某些特定单元格区域发生变化时,才激活Change事件,这就需要在Change事件中对Target参数进行判断。
示例47.1 自动记录数据录入日期
在工作表ChangDemo的代码窗口中写入如下代码:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
'判断是否选中了单个单元格
If .Count = 1 Then
'判断单元格是否在第一列
If .Column = 1 Then
'禁止事件激活
Application.EnableEvents = False
'在相应行的第二列输入当前日期
Target.Offset(0, 1) = Date
'恢复事件激活
Application.EnableEvents = True
End If
End If
End With
End Sub
返回Excel界面,在工作表ChangDemo中的A列中输入备忘内容,Change事件将自动在B列的相应行写入当前日期。修改工作表中其他列的单元格(如C列),工作表的Change事件同样会被触发,但是因为不满足代码中的判断条件,所以不会执行写入日期的代码。
如何禁止事件的激活
上述代码使用Application.EnableEvents=False为防止事件被意外多次激活。Application对象的EnableEvents属性可以设置是否允许对象的事件被激活。上述代码中如果没有禁止事件激活的代码,在写入当前日期的代码执行后,工作表的Change事件被再次激活,事件代码被再次执行。某些情况下,这种事件的意外激活会重复多次发生,甚至造成死循环导致事件代码重复调用,无法结束运行。因此在可能意外触发事件的时候,需要设置Application.EnableEvents=False禁止事件激活。但这个设置并不能限制控件的事件被激活。
EnableEvents属性的值不会随着事件过程的执行结束而自动恢复为True,也就是说需要在代码运行结束之前进行恢复。如果代码被异常终止,而EnableEvents属性的值仍然为False,则相关的事件都无法激活。恢复办法是在VBE的立即窗口中执行Application.EnableEven
ts=True。
47.2.2 SelectionChange事件
工作表中选定区域的范围发生变化将触发工作表的SelectionChange事件。SelectionChange事件的参数Target是Range变量,代表工作青史被选中的区域,相当于Selection属性返回的Range对象。
示例47.2 高亮显示工作表中选定区域所在的行和列
在工作表SelectionChangeDemo中写入如下的SelectionChange事件代码。
Private Sub Worksheet_

SelectionChange(ByVal Target As Range)
With Target
'清除工作表单元格的背景色
.Parent.Cells.Interior.ColorIndex = xlNone
'设置选中区域所在行的背景色
.EntireRow.Interior.Color = vbCyan
'设置选中区域所在列的背景色
.EntireColumn.Interior.Color = vbCyan
End With
End Sub
返回Excel界面,在工作表SelectionChangeDemo中选中一个单元格区域C10:C14,显示效果,第10行至第14行以及第3列单元格高亮显示。
47.3 工作簿事件
工作簿事件发生在特定的Workbook对象中。
47.3.1 Open事件
Open事件是Workbook对象最常用的事件之一,它发生于用户打开工作簿之时。
注意:在如下两种情况下,打开工作簿不会触发Open事件。
1.在按住键的同时打开工作簿。
2.在打开文件时的宏安全警告提示框里,选择了“禁用宏”。
Open事件经常被用来自动设置用户界面,这样的好处在于,无论工作簿关闭时的状态如何,再次打开时都可以按照某个特定风格呈现在用户面前。
示例47.3 自动设置工作簿打开时的界面风格
步骤1.在Thisworkbook模块中写入如下的Open事件代码。
Private Sub Workbook_Open()
'Excel窗口最大化
Application.WindowState = xlMaximized
With ActiveWindow
'工作表窗口最大化
.WindowState = xlMaximized
'禁止显示行标和列标
.DisplayHeadings = False
End With
'激活Welcome工作表
Sheets("Welcome").Select
End Sub
步骤2.返回Excel界面,选中Sheet1工作表。
步骤3.单击Excel窗口右上角的向下还原按钮,取消窗体最大化。
步骤4.单击“文件”——“保存”。
步骤5.单击“文件”——“退出”关闭工作簿。
步骤6.单击“文件”——“打开”,再次打开刚才保存的工作簿。
步骤7.单击安全警告提示框的“启用宏”按钮。
工作簿打开后,Excel窗口是最大化的,Welcome工作表成为活动工作表,而不是关闭工作簿时的Sheet1工作表。
47.3.2 BeforeClose事件
工作簿被关闭之前BeforeClose事件被激活。BeforeClose事件经常和Open事件配合使用,在Open事件中修改的Excel设置和用户界面,可以在BeforeClose事件中进行恢复。
示例47.4 关闭工作簿时自动恢复Excel默认界面风格
在Thisworkbook模块中写入如下的代码:

Private Sub Workbook_Open()
With Application
'隐藏公式编辑栏
.DisplayFormulaBar = False
'设置鼠标指针为沙漏型
.Cursor = xlWait
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
'显示公式编辑栏
.DisplayFormulaBar = True
'恢复系统默认鼠标指针
.Cursor = xlDefault
End With
End Sub
保存并关闭工作簿,然后再次打开工作簿,公式编辑栏已经隐藏且鼠标指针改为沙漏形。而在BeforeClose事件中,对相应的设置进行了恢复,所以工作簿关闭后,Excel将恢复默认的系统设置。
47.3.3 全部工作表使用相同的事件代码
工作簿事件有几个名称是以“Sheet”开头的,这些事件的一个共同特点是,工作簿内的任意工作表的行为都将触发事件代码的执行。
如果希望所有的工作表都相应相同的工作表事件代码,有两种实现方法:
1.在每个工作表代码模块中写入相同的事件代码。
2.使用相应的工作簿事件代码。
毫无疑问,第二种方法是最简洁的实现方法。
示例47.5 高亮显示任意工作表中选定区域所在的行和列
与示例47.2相对应,如果希望在工作簿中的任意工作表都拥有这种高亮显示的效果,可以在Thisworkbook模块中写入如下事件代码:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
With Target
'清除工作表单元格的背景色
.Parent.Cells.Interior.ColorIndex = xlNone
'设置选中区域所在行的背景色
.EntireRow.Interior.Color = vbCyan
'设置选中区域所在列的背景色
.EntireColumn.Interior.Color = vbCyan
End With
End Sub
与示例47.2相比,这种方法不必在每个工作表代码模块中写入相同的事件代码,而且当工作簿中新增工作表时,也无需为新建工作表添加Change事件代码。
47.4 非对象事件
Excel提供了两种不与对象关联的特殊事件,利用Application对象的相应方法可以设置这些特殊事件。
47.4.1 OnTime事件
OnTime事件指定一个过程在将来的特定时间运行,此处的特定事件既可以是具体指定的某个时间点,也可以是指定的一段时间之后。
示例47.6 文件保存提醒
步骤1.在工作簿中插入标准模块,并在其中写入如下代码。
'定义全局变量
Public iTime As Date

Sub SaveReminder()
'判断当前工作簿是否被修改
If ThisWorkbook.Saved = False Then
'显示消息框
If MsgBox("为了防止数据丢失请保存文件" & _
vbCrLf & "点击<是>进行保存", vbYesNo, "OnTimeDemo") = vbYes Then
'如果用户选择<是>,则保存当前工作簿
ThisWorkbook.Save
End If
End If
'记录下次运行的时间点
iTime = Now + TimeValue("0:0:10")
'设置10秒后再次运行SaveReminder过程
Application.OnTime iTime, "SaveReminder"
End Sub
步骤2.在Thisworkbook中写入如下代码。
Private Sub Workbook_Open()
'记录下次运行的时间点
iTime = Now + TimeValue("0:0:10")
'设置10秒后再次运行SaveReminder过程
Application.OnTime iTime, "SaveReminder"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'取消设置的自动运行
Application.OnTime iTime, "SaveReminder", Schedule:=False
End Sub
步骤3.保存并关闭工作簿。
重新打开工作簿,在10秒钟之后将看到工作簿保存提醒消息框。
Now函数返回当前计算机系统设置的日期和时间:TimeValue("0:0:10")函数返回一个Date类型数据,相当于10秒;SaveReminder是标准模块中在指定时间执行的过程的名称。为了演示方便,示例中设定的较短的代码执行时间间隔。
首先在工作簿打开时会触发工作簿的Open事件,其中的OnTime设置10秒后运行SaveReminder过程。
在SaveReminder过程中,判断工作簿的Saved属性的值,如果为False说明工作簿已经被修改,进而提示用户进行保存,如果用户单击“是”按钮,就保存当前工作簿。在过程的最后,设置10秒后再次执行SaveReminder过程代码。
在工作簿的BeforClose事件中,利用Onkey方法的Schedule参数清除已经设置的定时运行过程,如果省略此代码,即使工作簿已经关闭,到达指定时间时,Excel将再次打开工作簿运行SaveReminder过程代码。
47.4.2 OnKey
使用OnKey方法可以设置按下特定的键或组合键时运行指定的过程代码。Excel会一直监视着用户的任何键盘操作,因此理论上可设置任何一个键或组合键来运行指定的过程代码。
注意:在工作表中输入公式或在对话框中时,OnKey设置的组合键无效。
示例47.7 为Excel设置自定义快捷键
步骤1.在工作簿中插入标准模块,在模块中写入如下代码。
Sub OnKeyDemo()
Application.OnKey"^a","CtrlA"
End Sub
Sub CtrlA()
MsgBox "

相关文档
相关文档 最新文档