第3章 EXCEL VBA程序设计技术Word格式文档下载.docx
《第3章 EXCEL VBA程序设计技术Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《第3章 EXCEL VBA程序设计技术Word格式文档下载.docx（49页珍藏版）》请在冰豆网上搜索。
吴昆
职员
100
80
1003
201
欧阳天平
财务部
部长
150
1004
202
陈玉
2100
50
1005
301
张浩中
采购部
1006
302
李腾飞
1007
303
袁国
1500
30
1008
401
孙有曲
销售部
350
1009
402
周宏菲
1600
1010
403
邓位思
1200
1011
501
刘建丰
市场部
1012
502
何菲菲
1900
210
合计
X.1.2过程程序
1.创建过程
Sub过程是一系列由Sub和EndSub语句所包含起来的VBA语句，它们会执行动作却不能返回一个值。
Sub过程可有参数，例如常数、变量、或是表达式等来调用它。
如果一个Sub过程没有参数，则它的Sub语句必须包含一个空的圆括号。
创建一个过程需要两个基本步骤。
首先，需要向工作簿中添加一个模块。
接着需要向模块中添加一个工程。
对于创建的每一个应用程序，只需添加一次模块。
可以使用多个模块，但这是不必要的。
某些开发者喜欢使用多个模块，以便根据他们的目的或者窗体对过程进行组织。
下面举例创建一个过程，它显示一个消息框。
（1）打开一个新工作簿。
（2）选择"
工具"
-"
宏"
VisualBasic编辑器"
，打开VBA编辑器窗口。
（3）在`VBA编辑器的左面，可以看到“工程资源管理器”窗口。
在工程资源管理器窗口的“Thisworkbook"
上单击鼠标右键，选择“插入”-“模块”，这样就将一个模块添加到应用程序中了。
（如果你没有看见“工程资源管理器”窗口，可以按Ctrl+R）。
（4）选择“插入”“过程”，显示“添加过程”对话框。
（5）输入“第一个程序”作为过程名字。
在“类型”分组框中，确认选择了“子程序”。
单击“确定”按钮。
这样一个新的过程就添加到模块中了。
可以在模块中看到以PublicSub第一个过程（）开始，以EndSub结束的语句结构。
VBA对子程序和函数有如下的命名规则：
名字中可用字母、数字和下划线。
名字中不能包含空格句号惊叹号，也不能包含字符@&
$#。
名字最多可以包含255个字符。
（6）在过程中插入光标，输入以下语句并回车：
在输入Msgbox后，会自动弹出一个消息框告诉你有关这条命令的信息，可进行选择，通过这种自动列表技术，避免了繁琐的记忆。
PublicSub第一个程序（）
MsgBox"
EXCELVBA程序"
EndSub
对Msgbox的进一步说明：
语法：
MsgBox（prompt[,buttons][,title][,helpfile,context]）
在MsgBox函数的语法中，括号内的参数是此函数的命名参数。
方括号所包含的参数是选择性的。
在MsgBox函数中，唯一必须提供的参数（prompt）是做为提示的文本。
在代码中可以利用位置或名称来指定函数与方法的参数。
若利用位置来指定参数，则必须根据语法中的顺序，利用逗号来分隔每一个参数，例如：
回答正确!
"
0,"
提示"
若以名称来指定参数，则须使用参数名称或跟着冒号与等号（:
=），最后再加上参数值。
可以任何的顺序来指定命名参数，例如：
MsgBoxTitle:
="
Prompt:
回答正确"
函数以及某些方法的语法会利用圆括号将参数封闭起来。
这些函数和方法会返回值，所以必须用圆括号将参数封闭起来，才可以赋值给变量。
如果忽略返回值或是没有传递所有的参数，则可以不用圆括号。
方法若不返回值，则不用将参数用圆括号封闭起来。
2.运行过程
创建过程后，可以通过几种方式运行。
可以直接使用“运行”菜单，“运行子程序/用户窗体”工具栏按钮或按下F5键。
按F5后，选择要运行的过程：
运行结果：
3.保存对模块所做的改变
要保存新过程,需要保存过程所驻留的工作簿。
可以用VBA编辑器保存工作簿。
具体步骤如下:
选择"
文件"
保存"
。
则工作簿和模块与过程都保存下来了。
4．提高程序的可读性
尽管一行VBA代码最多可以包含1024个字母，但为了程序容易阅读，最好将长的语句打断为两行甚至多行。
VBA使用一个专门的连续线（下划线）置于一行代码的末尾，表明下一行是这行的连续。
注释不但使代码更容易理解，而且它在VBA过程的测试和处理问题中都是很有用的。
例如，你执行一个过程时，它可能和期望的运行不一致，对于那些可能产生问题的代码行，如果想略过它们，但是以后可能还需要用到它们，就可以在它们前面加一个引号注释掉它们，而不必删除它们。
X.1.3变量的应用
1.用Dim语句声明变量
声明变量可以使用Dim语句,基本语法如下:
Dim变量名AS数据类型
条语法中的变量名代表将要声明的变量名。
对变量的命名规则和对过程的命名规则相同。
变量名必须以字母开始,并且只能包含字母数字和特定的特殊字符,不能包含空格、句号惊叹号,也不能包含字符@&
名字最大长度为255个字符
举例：
输入一个信息，然后显示出来。
（1）创建一个名为"
显示信息"
的子程序。
（2）输入以下代码:
PublicSub显示信息（）
DimM_msgAsString
M_msg=InputBox（"
请输入信息:
）
你输入的信息是：
&
M_msg
InputBox函数显示一个信息提示用户输入数据，这个对话框有两个按钮——“确定”和“取消”，当你点击确定时，InputBox函数返回用户输入在信息框里的信息；
当你点击取消时，函数则返回空字符串。
（3）按下F5键运行过程。
4）输入“会计电算化”后按回车键（输入其他内容也可）,会显示一个消息框。
在Dim语句中如果如果没有数据类型,变量将被定义为Variant类型,因为VBA中默认的数据类型是Variant。
Variant数据类型占用存储空间较大，而且它将影响程序的性能，因此应当在程序中确定变量的类型。
2.作用域
变量可以在两个地方定义常量和变量:
过程中定义和在模块顶部一个名为"
通用声明"
的区域内定义。
而且定义的位置就决定了作用域。
在过程中声明,该变量只能在该过程中使用,其他过程中不能使用这个变量和常量,这种变量为局部变量或过程级变量。
在模块顶部声明的变量称为模块级变量，该变量在该模块的所有过程中都可以使用。
公共级变量可以在应用程序的任何过程中使用，不论过程和变量是否定义在一个模块。
这就使得公共级变量在使用中十分灵活，但是公共级变量在程序运行时一直保留在内存中，这样就占用了系统资源。
要创建公共级变量，可以用Public语句，具体语法如下：
PublicvariablenameAsdatatype
要创建公共级常量，具体语法如下：
PublicconstCONSTANAMEdatatype=value
公共级变量或常量必须在模块顶部的“通用声明”区域中定义。
举例：
PublicM_dmAsCharacters
PublicM_nameAsCharacters
3.声明数组
数组的声明方式和其他的变量是一样的，它可以使用Dim、Static、Private或Public语句来声明。
标量变量（非数组）与数组变量的不同在于通常必须指定数组的大小。
若数组的大小被指定的话，则它是个固定大小数组。
若程序运行时数组的大小可以被改变，则它是个动态数组。
数组是否从0或1索引是根据OptionBase语句的设置。
如果OptionBase没有指定为1，则数组索引从0开始。
（1）声明固定大小的数组
下面这行代码声明了一个固定大小的数组，它是个21行乘以21列的Integer数组：
DimM_Array（20,20）AsInteger
第一个参数代表的是行；
而第二个参数代表的是列。
与其他变量的声明一样，除非指定一个数据类型给数组，否则声明数组中元素的数据类型为Variant。
数组中每个数组的数字型Variant元素占用16个字节,每个字符串型Variant元素占用22个字节。
（2）声明动态数组
若声明为动态数组，则可以在执行代码时改变数组大小。
可以利用Static、Dim、Private或Public语句来声明数组，并使括号内为空，如下示例所示。
DimM_Array（）AsSingle
可以在过程中使用ReDim语句来做隐含性的数组声明。
对于过程中的数组范围，可以使用ReDim语句去改变它的维数。
然而当执行这个命令时，数组中存在的值会丢失。
若要保存数组中原先的值，则可以使用ReDimPreserve语句来扩充数组。
例如，下列的语句将XArray数组扩充了5个元素，而原本数组中的当前值并没有消失掉。
ReDimPreserveXArray（UBound（XArray）+5）
X.1.4控件的应用
1.向工作表添加控件
用EXCEL设计界面时,要将控件添加到工作表上,可以按以下步骤操作:
（1）选择工具下的“自定义”,显示"
窗体"
工具栏。
标签"
控件。
（3）将鼠标定位到某单元格,此时鼠标变成小十字。
（4）按下左键,拖动鼠标，确定控件的大小,放开鼠标左键。
（5）在“标签1”上单击右键,选择"
编辑文字"
现在可以输入文字。
完成后,单击任何单元格退出文字编辑。
（6）通过以上步骤可以添加其它所需要的控件到工作表中。
2.设置控件的特性
每个控件都有一系列的特性，需要进行设置来满足不同的需要。
设置控件的特性,可以在该控件上按右键完成。
3.给控件命名
当创建一个控件时，EXCEL会自动给它指定一个默认的名字,但不便于理解和记忆。
更改控件的名字，先选中某个控件,再在位于公式栏上的"
名字"
编辑框输入控件名字。
这样就给控件更改了名字。
4.使用用户窗体
要创建功能复杂的应用程序,并且方便用户输入数据,那么应该使用用户窗体。
用户窗体可以作为程序的对话框和窗口。
向用户窗体添加控件基本类似于向工作表添加控件,然而第一步要创建一个用户窗体。
这可以通过VBA编辑器实现。
具体按以下步骤操作:
（1）打开工作簿,选择"
VBA编辑器"
打开VBA编辑器。
（2）在VBA编辑器中选择工具栏上的"
插入用户窗体"
按钮或者选择"
插入"
菜单,从下拉菜单中选择"
用户窗体"
在VBA编辑器中出现一个名为"
Userform1"
的窗体,"
控件工具箱"
同时出现,在其中有部分新的控件。
这些新的控件是:
①切换按钮:
该控件如果被选中,那么会保持被按下的状态。
如果再次单击它就恢复为没有按下的状态。
EXCEL工具栏中有几个这样的按钮,例如:
全屏显示"
"
加粗"
下划线"
以及"
工具栏中的"
切换网格"
等。
②选项卡条（TabStrip）:
它是包含多个选项卡的控件。
通常用来对相关的信息进行组织或分类。
例如:
用选项卡条来显示各个地区的销售信息,可以给每个地区设置一个选项卡。
在默认时,选项卡包含两页,分别叫做TAB1和TAB2,可以添加更多的选项卡。
③多页:
外观类似选项卡条,是包含一页或多页的控件。
选项卡条给人相似的外观,而多页控件的各页包含各自不同的控件,有各自不同的布局。
多页的例子很多,例如:
设置控件格式"
对话框和"
菜单中的"
选项"
对话框。
格式"
单元格..."
④图像控件:
它允许向窗体上放置图片。
图片格式须为 :
*.bmp，*.cur，*.gif，*.ico，*.jpg,*.wmf。
⑤RefEdit:
它外观象文本框，通过这个控件可以将用户窗体折叠起来，以便选择单元格区域。
在用户窗体设计过程中可以对其进行预览，方法是在VBA编辑器中选择该窗体，单击“运行”菜单中的三角符号“运行子过程/用户窗体”，运行窗体的另一种方法是按F5键。
X.1.5EXCELVBA代码结构
1.对象、属性和方法
（1）面向对象概述
①面向对象方法的产生
使用VBA，可以创建工程控制Excel的许多东西，同样也可以控制很多其它的应用程序。
VBA具有控制和管理各种各样的对象的能力。
但是，“对象”是什么呢？
“对象”是你通过VBA控制的工作簿，工作表，工作表里的单元格区域，图表或者工具条，这些只是一些用Excel时想要控制的东西的举例。
这些东西就是对象。
在面向对象的方法之前，程序设计所采用的是面向过程的方法。
面向过程的程序设计的一般思路是：
自顶向下，逐步求精；
把程序结构按功能分成若干个基本的模块，这些模块之间尽量相对独立，功能尽可能单一，每一个模块都是由顺序、选择和循环三种结构组成，模块化的方法的具体实现是使用子程序。
由于这种结构化的程序设计的方法使用了模块分解的方法，有效地把一个复杂的程序抽象成一些简单的易于控制和处理的子程序，使设计便于管理和维护。
由于用户图形界面的广泛使用，软件的规模也越来越大，要求也越来越高，使软件的开发越来越困难。
为了适应软件开发的发展，在80年代中期提出了面向对象的概念。
面向对象的方法的中心思想是：
像真实的世界由真实的物体组成一样，软件的程序设计也可看成由一系列单独的对象组成，每一个对象都有自已的特定的特征和行为。
软件开发从本质上讲，就是对软件所要处理的问题做出正确的认识，并把这种认识表述出来。
所谓面向对象的方法，就是将数据和对数据的操作放在一起，组成一个相互依存、不可分开的整体——对象。
面向对象的程序设计方法之所以能受到如此广泛的欢迎，其中的一个很重要的原因就是可以使软件设计者通过可执行代码的共享使程序创建更容易。
不用为每一个方法保留一个单独的拷贝，相同的VBA对象类型能够共享它们的方法的代码。
②对象
一个对象可以是一个真实存在的物体，也可以是一个抽象的事件，但不管它是真实物体也好，抽象事件也好，它总是可以用静态特征和动态特征来描述。
所谓静态特征，就是物体的颜色、大小等可以用某种具体的数据来描述的特征；
所谓动态特征，就是物体所能表现出来的行为和所具有的功能等特征。
与真实物体类似，在程序设计中所用到的对象的概念，也可以由静态特征和动态特征来表现。
在这里静态特征称为属性，动态特征称为方法。
这些属性和方法在一起就定义了一个对象。
对象是系统中用来描述事物的一个基本单位。
面向对象的程序设计方法将数据和程序封装到封闭的对象中，这样就使设计、组织和使用这些复杂的数据结构和由那些数据所完成的功能变得很简单。
软件应用程序中的每一个对象都包含了程序代码和数据，并合成了一个简单的项。
大部分应用程序中都包含了很多种不同类型的对象。
VBA中提供了访问其它应用程序和其它应用程序中的对象的方法。
要使VBA获得对主应用程序的过程的控制，就要用VBA代码操纵主应用程序中的对象。
使用类模块，甚至可以创建自已的对象。
例如，在Excel中，工作簿、工作表、数据区域、图表、图形、工具栏、菜单等都是对象。
通过对这些对象的使用就可以实现对Excel的操作。
③属性
每一种对象都有一些特征,在VBA里，这些对象的特征被称为“属性”。
例如，工作簿对象有名称属性；
区域对象有列、字体、公式、名称、行、样式和值等属性。
这些对象属性是可以设置的。
通过设置对象的属性控制对象的外观和位置。
对象属性一次只能设置为一个特定的值。
例如，当前工作簿不可能同时有两个不同的名称。
属性决定了一个对象的外观和行为，要改变一个对象的外观和行为，可以直接通过改变对象的属性来实现。
要改变一个Excel对象的外观和行为，可以通过对属性的赋值来实现。
我们要想让工作表变得好看一些，就可以改变它的颜色属性，要想让字体变大一些，就可以改变它的Font属性。
通过属性不仅能改变对象的行为，还能查看对象当前的状态。
例如要想知道工作表的名称，可以直接访问它的Name属性，要想知道工作簿的存储路径、文件夹和名称，则可以访问它的FullName属性。
对象都有属性，用来描述或设置对象的特征。
可以使用VBA来设置对象的属性，也可以对一个对象的某些属性进行修改，从而定义该对象，还可以引用某对象的属性值。
使用属性时，应将对象和属性组合在一起，中间用句点分隔。
语法：
<
对象>
.<
属性>
<
参数>
说明：
设置或引用某对象的属性。
若该属性带有参数或需要为带参数的属性指定参数时，则指定参数以进一步描述该对象；
若该参数返回值，则应在参数两边加上括号。
变量>
=<
将某对象的属性值赋值给一个变量，以便于在程序中使用。
例如，Range对象有一个Value属性，可以用VBA代码引用该对象的属性值，也可以修改该属性
④方法
现实的物体几乎都有一些内在的行为和功能。
例如，人可以从事工作、消费，汽车可以用来运输，房子可以用来住，等等。
方法可以改变对象的属性值，方法也可以对储存在对象中的数据实施某些操作。
方法很象VBA编程中的过程，但它属于某个对象，必须通过特定的对象才能访问方法。
虽然相同类型的对象能够共享它们的方法代码，但是当用户访问某个特定的对象时，该方法只能作用于调用该方法的对象。
对象都有方法，一个方法就是在对象上执行的某个动作。
为对象指定方法时，应将对象和方法组合在一起，中间用句点分隔。
方法>
为某对象指定方法。
若该方法带有参数或需要为带参数的方法指定参数时，则指定参数以执行进一步的动作；
⑤类
面向对象方法中的类，是具有相同的属性和方法的一组对象的集合。
它为属于该类的全部对象提供了抽象的描述，其内部包括属性和方法两个主要部分。
类与对象的关系犹如模具与铸件的关系一样。
一个属于某个类的对象称为该类的一个实例。
实际上整个VBA的对象体现是一个分层结构。
对象分层结构的概念意味着对象可以包含其它对象，而其它对象又可以包含别的对象。
这种“包含”是通过一种名叫“集合”的特殊类型的对象来实现的。
集合对象的唯一目的是用来包含其它对象。
被给定集合所包含的对象都是同一类型的。
（2）对象的引用
既然已经有了对象的定义，我们就可以使用这些已有的对象来完成一些实际的任务。
但是，在使用对象之前，必须要作的事情是：
要让Excel知道现在用的是一个对象，而且需要明确这个对象是属于哪一个类，这个对象具体值是什么。
使用对象首先要定义：
这是一个属于某个类的对象，然后再给它赋一个具体的值。
给对象变量赋值的语句和给普通变量赋值的语句不同。
给普通变量赋值的语句只要直接用一个等号就可以了，而给一个对象变量赋值则需要用到Set关键字。
Excel的对象模型是通过层次结构很有逻辑地组织在一起的，一个对象可以是其它对象的容器，可以包含其它的对象，而这些对象又包含其它的对象。
位于顶层的是Application对象，也就是Excel应用程序本身，它包含Excel中的其它的对象，如Workbook对象；
一个Workbook对象包含其它一些对象，如Worksheet对象；
而一个Worksheet对象又可以包含其它对象，如Range对象，等等。
这就是Excel的对象模型。
例如，Range对象在Excel对象模型中的位置为：
Application对象
Workbook对象
Worksheet对象
Range对象
知道了某对象在对象模型层次结构中的位置，就可以用VBA代码方便地引用该对象，从而对该对象进行操作，并以特定的方式组织这些对象，使Excel能根据您的需要自动化地完成工作任务。
因此，要熟练掌握ExcelVBA编程，必须理解Excel的对象模型。
（3）使用对象
使用VBA可以处理某个对象的整个集合，或者某集合中的一个单独的对象。
集合（“对象名”）或集合（对象索引号）
引用集合中的某个对象，即对象名或对象索引号所代表的对象。
例如，Worksheets（“Sheet1”）引用集合Worksheets中的工作表Sheet1；
若Sheet1是集合中的第一个工作表对象，还可以写为Worksheets
（1）。
特别地，“Sheets”集合由工作簿中的所有工作表（包括图表工作表）组成。
若要引用工作簿中的第一个工作表，可采用语句Sheets
（1）表示。
通过点运算引用某对象的成员
我们可以用句点连接对象名来限定是对某个对象成员的引用，同时也指定了该对象成员在对象层次结构中的位置。
对象名>
.…
后一对象是前一对象的成员，限定了对前一对象所包含的对象成员的引用。
设置对象变量
对象变量是代表一个完整对象的变量，如工作表或单元格区域。
用Dim或Public语句来声明对象变量。
Dim（或Public）<
变量名>
AS<
将<
声明为一个<
对象。
一般可将对象名直接设为Object，即任意对象。
但如果您知道变量将作用到的对象的话，最好将其设置为具体的对象。
例如，语句：
DimDataAsRange，将变量Data声明为一个Range对象。
在将变量声明为一个对象变量后，用Set语句将某对象赋值给该变量。
Set<
某对象>
对象，再将某对象赋值给该变量。
2.对象处理常用语句
要使用VBA有效地处理Excel对象模型，则需要经常使用With…EndWith语句和ForEach…Next语句，它们可以简化对对象和集合的处理。
（1）With
升级会员 