VBA的应用.docx

VBA的应用.docx

《VBA的应用.docx》由会员分享，可在线阅读，更多相关《VBA的应用.docx（16页珍藏版）》请在冰豆网上搜索。

VBA的应用

1、SUB的应用

单元格作为VBA入门的一个必需掌握的概念，单元格的引用有多种方式，现在根据自己的经验及参考相关的书本来整理涉及单元格引用的知识。

最近发现自己对单元格方面的知识并没有完全掌握，收到了罗版的VBA自学宝典，结合以前的那些相关资料，一边学习，一边把相关的知识点记录下来。

一、选取所有单元格。

1.Sub选取全部单元格一（）

2.  Cells.Select

3.EndSub

复制公式或代码

注：

cells是所有单元格的集合。

1.Sub选取全部单元格二（）

2.Rows.Select

3.EndSub

复制公式或代码

而Rows则是所有行的集合。

1.Sub选取全部单元格三（）

2.Columns.Select

3.EndSub

复制公式或代码

而columns则是所有列的集合。

无论是所有单元格的集合，还是所有行或列的集合选取，都是工作表中全选所有单元格的表示方式。

二、Range引用来选取单元格

1、Range引用单元格方式1：

Range（"单元格地址"）

例如：

Range（"A1"）:

表示单元格A1，注意：

单元格地址要加双引号

值得注意的是：

双引号的输入必须是在半角状态下输入的，否则会出现错误。

"单元格地址"里面的单元格地址不区分相对引用和绝对引用的，无论你是使用Range（"A1"）、Range（"$A1"）、Range（"$A$1"）还是Range（"A$1"），都是引用通一个单元格。

一般为了简化作用，直接用Range（"A1"）这种形式来表示引用单元格。

2、Range引用单元格方式2：

Range（"单元格区域"）

在VBA数组中，经常遇到arr=Range（"A1:

D10"）这种数组赋予方式，而里面出现了Range（"单元格区域"）的这种单元格区域引用。

从这个最常见的例子中，我们可以发现，"A1:

D10"代表了一个单元格区域的引用，其中A1是该区域的左上角上面的单元格，而D10则是该区域右下角下面的单元格。

但大家可以从下面两个例子来发现：

1.Subq1（）

2.Range（"A1:

D10"）.Select

3.EndSub

4.Subq2（）

5.Range（"D10:

A1"）.Select

6.EndSub

复制公式或代码

这两个小例子得到的结果都是选取A1:

D10这个单元格区域

值得大家注意的，以下的这些通过索引号作为参数的引用，都是比较陌生的。

比如以下这个小例子：

例子1：

1.SubT1（）

2.Range（"B2:

E6"）

（1）.Select

3.EndSub

复制公式或代码

注释：

该例子代表了选取B2：

E6区域中的第一个单元格的，即选取B2

例子2：

1.SubT2（）

2.Range（"B2:

E6"）（3）.Select

3.EndSub

复制公式或代码

注：

该例子代表选取B2：

E6区域中的第三个单元格，即D2，而单元格的区域的读取一般是从左到右，然后再从上到下进行读取的，故该区域的前3个单元格分别是B2、C2、D2，而第3个单元格就是为D2

例子3：

1.SubT3（）

2.Range（"B2:

E6"）（3.5）.Select

3.EndSub

复制公式或代码

注意：

当里面的索引号为小数时，excel会对其进行四舍五入，比如例子3中的3.5，excel会把它转换为4，故例子3中的代表是B2:

E6区域的第4个单元格选取，即选取E2

例子4：

1.SubT4（）

2.Range（"B2:

E6"）（3.2）.Select

3.EndSub

复制公式或代码

而例子4中，因为四舍五入的关系，得到的结果是选取该区域的第3个单元格，即选取D2

例子5：

1.SubF1（）

2.Range（"B2:

E6"）（1,4）.Select

3.EndSub

复制公式或代码

注：

在例子5中，Range（"单元格区域"）（行号，列号）,所代表的是选取该单元格区域中第几行与第几列交叉的那个单元格，而例子5中代表是选取B2:

E6区域中的第1行第4列的单元格，在该区域中，所符合条件的单元格为E2。

值得关注的是，（行号，列号）里面的参数可以使用0或者负数。

当行号的参数为0的时候，则向该区域的左上角单元格向上偏移一个单位，当列号为0时候，则向该区域左上角单元格向左偏移一个单位；如果参数是负数的时候，在原来的基础上追加偏移量。

具体请看例子6、7：

1.SubF1（）

2.Range（"B2:

E6"）（0,0）.Select

3.EndSub

复制公式或代码

例子6中是选取了A1单元格，即B2：

E6区域左上角单元格B2分别向上偏移一行，向左偏移一列，所得到的单元格为A1

例子7：

1.SubF1（）

2.Range（"C3:

E6"）（-1,-1）.Select

3.EndSub

复制公式或代码

例子7中代表了C3：

E6区域的左上角单元格C3分别向上偏移两行，向左偏移两列，所得到的单元格为A1

3、Range（"列标"&行数）的单元格引用方式：

例子7：

1.SubY（）

2.Range（"B"&10）.Select

3.EndSub

复制公式或代码

从这个最简单的例子，一个最常见的单元格引用方式。

注意：

列标需要加上双引号，并且要用到"&"把列标和行号连接起来。

经常用到的一个例子：

往A1:

A100里面分别填充1到100等这100个数字

1.Suba（）

2.DimiAsInteger

3.Fori=1To100

4.  Range（"A"&i）=i

5.Nexti

6.EndSub

复制公式或代码

从中可以看到这种单元格方式引用的作用。

4、Range（"定义名称"）的单元格引用：

首先在excel2007中，公式→定义名称→把"A1:

A100"区域定义为"数字"。

便可以使用这种方式，具体请看以下例子:

1.SubB（）

2.Range（"数字"）.Select

3.EndSub

复制公式或代码

注：

A1：

A100这个区域已经定义名称为"数字"，该代码表示选取A1:

A100单元格区域

注意：

定义名称也必须添加双引号，并且是半角下输入的。

5、Range方式引用多个区域：

前面详细说了：

Range（"A1:

B10"）这样类似的区域引用方式，但值得注意的是，那只是连续区域的引用。

还有一些不连续的区域，则需要在区域中间加,号

比如以下例子：

1.Sub多区域引用（）

2.Range（"A1,C1,D3:

E8"）.Select

3.EndSub

复制公式或代码

注意：

"A1,C1,D3:

E8"这个为不连续的区域，中间需要用,号隔开，并且不连续的区域直接还是要用双引号作为标记，这个代码表示选取A1、C1、D3:

E8。

这里主要要关注,号的使用。

但是，该方式引用有一个限制:

参数的长度不能超过256个字符，否则产生运行错误。

连接上面那个例子，给出另外一种使用方式：

1.Sub多区域引用2（）

2.Union（Range（"A1"）,Range（"C1"）,Range（"D3:

E8"））.Select

3.EndSub

复制公式或代码

该例子中主要使用了union来进行连接，这种方式得到的结果和上面的那个例子所得到的结果是一样的。

这两种方式，都是对不连续区域进行连接，相对比，还是上面的那个例子比较简洁。

 Range嵌套使用

主要语法：

Range（cell1,cell2）,其中cell1和cell2是必选参数。

Cell1代表指定区域目标的左上角单元格，Cell2代表指定区域目标右下角单元格。

具体看例子

1.Suba（）

2.Range（Range（"A1"）,Range（"E4"））.Select

3.EndSub

复制公式或代码

注：

该例子表示选取A1：

E4区域

而下面这个例子：

1.SubB（）

2.Range（Range（"A1:

A3"）,Range（"E2"））.Select

3.EndSub

复制公式或代码

注：

上面这个例子表示选取A1：

E3区域。

注意不是选取A1：

E2的区域。

VBA会从两个区域中最左上角的单元格作为新的区域作为参照起点，再取两个区域所跨越的最大行作为新的区域行数，取两个区域跨越的最大列作为新区域的列数。

与Range相关的选取整行或整列：

1.Suba（）

2.Range（"A:

A"）.Select

3.EndSub

复制公式或代码

上面这个例子，表示选取A列，注意，无论是"A:

A"或者是"A:

a",代表的都是选取A列，里面不区分大小写。

另外表示选取第一列，还可以用下面的代码：

1.Subyy（）

2.Columns

（1）.Select

3.EndSub

复制公式或代码

1.Subaa（）

2.Columns（"A"）.Select

3.EndSub

复制公式或代码

1.Subaa1（）

2.Columns（"A:

A"）.Select

3.EndSub

复制公式或代码

而选取第一行可以用下面的例子：

1.Subb（）

2.Range（"1:

1"）.Select

3.EndSub

复制公式或代码

而选取第一行，还可以使用下面的代码：

1.Subyy1（）

2.Rows

（1）.Select

3.EndSub

复制公式或代码

1.Subaa2（）

2.Rows（"1"）.Select

3.EndSub

复制公式或代码

1.Subaa3（）

2.Rows（"1:

1"）.Select

3.EndSub

复制公式或代码

三、Cells引用单元格

1、Cells（行数，列数）

请看下面例子：

1.Suba（）

2.Cells（1,2）.Select

3.EndSub

复制公式或代码

该例子表明选取B1单元格

2、Cellls（行数，列标）

请看例子：

1.Subba（）

2.Cells（2,"B"）.Select

3.EndSub

复制公式或代码

该例子中表明选取B2单元格，但该方法引用单元格永远只能引用一个，并且不能引用区域。

3、Range.cells（横坐标，纵坐标）

这种方式引用单元格是以其父对象Range左上角单元格作为参照系，向下累加的坐标系数来指定单元格。

请看下例：

1.Subaa（）

2.Range（"B2:

F10"）.Cells（2,2）.Select

3.EndSub

复制公式或代码

该代码表示B2：

F10单元格中横坐标为2、纵坐标为2的单元格C3，该参照是以B2为基准的。

另外，cells里面可以使用小数，不过VBA会对其进行四舍五入再计算，具体看下例：

1.Subau（）

2.Range（"B2:

F10"）.Cells（3.8,3.4）.Select

3.EndSub

复制公式或代码

该代码表示B2：

F10单元格中横坐标为4、纵坐标为3的单元格D5。

4、Range.cells（索引号）

当使用单个索引号作为参数时，它表示父对象中的一个索引子集。

其编号方式是先行后列、先左后右。

请看下例：

1.Subau（）

2.Range（"B2:

F10"）.Cells（3）.Select

3.EndSub

复制公式或代码

该例子表示B2：

F10区域中第3个单元格D2。

这种方式比较少见，也比较少用。

四、[A1]方式引用单元格，该单元格表示的简化方法。

单元格的简化表达方式：

[单元格地址]

表示单元格A1，简写为[A1]

注意：

单元格地址外是中括号（[]）,单元格地址不能使用双引号。

1.Sub选取单元格A1的方法（）

2.[A1].Select

3.EndSub

复制公式或代码

该例子表示选取单元格A1。

另外，该方式可以用来表示选取单元格区域：

1.Sub选取（）

2.[A1:

D10].Select

3.EndSub

复制公式或代码

表示选取A1：

D10区域。

而[]之间可以使用逗号开选取不连续单元格，具体请看下例：

1.Sub选取（）

2.[A1:

D10,H1,H10].Select

3.EndSub

复制公式或代码

表示选取A1：

D10、H1、H10等区域的单元格。

从该例子看出，[A1]的方式是最简单的写法。

2、变量的定义

3、VBA变量详解

4、　　变量是用于临时保存数值的地方.每次应用程序运行时,变量可能包含不同的数值,而在程序运行时,变量的数值可以改变.

5、　　为了说明为什么需要变量,可以按照如下步骤创建一个简单的过程:

6、　　1）创建一个名为"你叫什么名字"的过程.

7、　　2）在过程中输入如下代码:

8、　　Inputbox"输入你的名字:

"

9、　　现在不要担心inputbox语句的语法,将在第六学时中了解到有关这条命令的更多信息.

10、　　3）按下F5键运行过程,这时会显示一个输入框,要求输入你的名字.

11、　　4）输入你的名字并按"确定"按钮,则结束该过程.

12、　　你输入的名字到那里去了?

如何找到用户在输入框中输入的信息?

在这种情况下,需要使用变量来存储用户输入的结果.

13、　　4.4.1变量的数据类型

14、　　使用变量的第一步是了解变量的数据类型.变量的数据类型控制变量允许保存何种类型的数据.表4-1列出了VBA支持的数据类型,还列出了各种类型的变量所需要的存储空间和能够存储的数值范围.

15、数据类型　　　　　存储空间　　　　数值范围

16、Byte　　　　　　　1字节　　　　　　0-255

17、Booleam　　　　　　2字节　　　　　True或者False

18、Integer　　　　　　2字节　　　　　-32768-32767

19、Long（长整型）　　　　4字节　　　　-2147483648-2147483647

20、Single　　　　　　　4字节　　　　　负值范围:

-3.402823E38--1.401298E-45

21、正值范围:

1.401298E-45-3.402823E38

22、Double　　　　　　　8字节　　　　负值范围:

-1.79769313486232E308--494065645841247E-324

23、正值范围:

4.94065645841247E-324-1.79769313486232E308

24、Currency　　　　　　8字节　　　　-922337203685477-922337203685477

25、Decimal　　　　　　14字节　　　　不包括小数时:

+/-79228162514264337593543950335

26、包括小数时:

+/7.9228162514264337593543950335

27、Date　　　　　　　8字节　　　　　1000年1月1日-9999年12月31日

28、Object　　　　　　4字节　　　　　任何引用对象

29、String（长字符串）　10字节+1字节/字符　　　0-约20亿

30、String（固定长度）　字符串的长度　　　　　　1-约65400

31、Varient（数字）　　16字节　　　　　　　　　Double范围内的任何数值

32、Varient（文本）　　22字节+1字节/字符　　　　数据范围和变长字符串相同

33、表4-1VBA数据类型

34、　　作为ABV程序员，一个目标是选择需要存储空间尽量小的数据类型来保存所需要的数据，这正是表4-1提供各种数据类型存储空间的原因。

例如，要保存诸如班级学生总数这样的小数字，那么Byte数据类型就足够了。

在这种情况下，使用Single数据类型只是对计算机存储空间的浪费。

35、　　4.4.2用Dim语句创建变量（声明变量）

36、　　现在,你对变量可以使用的数据类型已经比较熟悉了,以下我们将创建变量.创建变量可以使用Dim语句,创建变量通常成为"声明变量"Dim语句的基本语法如下:

37、　　Dim变量名AS数据类型

38、　　这条语法中的变量名代表将要创建的变量名.对变量的命名规则和对过程的命名规则相同.这条语句中的数据类型部分可以是表4-1中的任何一种数据类型.

39、　　变量名必须以字母开始,并且只能包含字母数字和特定的特殊字符,不能包含空格句号惊叹号,也不能包含字符@&$#.名字最大长度为255个字符

40、　　在接下来的练习中将说明如何在VBA中使用变量,你将要输入你的名字,并用一个消息框将其显示出来.具体步骤如下:

41、　　1）创建一个名为"显示你的名字"的子程序.

42、　　2）输入以下代码:

43、　　PublicSub显示你的名字（）

44、　　Dims名字AsString

45、　　s名字=Inputbox（"请输入你的名字:

"）

46、　　Msgbox"你好"&s名字

47、　　EndSub

48、　　3）将鼠标放到过程中的任何地方,按下F5键运行过程,会显示一个输入框.

49、　　4）输入你自己的名字并按回车键,会显示一个消息框,显示的文字中包含你自己的名字.

50、　　5）单击"确定"按钮,返回过程中.

51、　　在Dim语句中不必提供数据类型.如果没有数据类型,变量将被定义为Variant类型,因为VBA中默认的数据类型是Variant.你知道这一点后,最初的反应也许是觉得应该不用自己决定数据类型,而将一切抛给VBA.这种观念是完全错误的.你必须决定选择使用何种数据类型。

因为Variant数据类型占用存储空间较大（16或22字节）而且它将影响程序的性能。

VBA必须辨别Variant类型的变量中存储了何种类型的数据。

52、　　4.4.3变量命名的惯例

53、　　下表给出了推荐的变量命名惯例

54、数据类型　　　　短前缀　　　　长前缀

55、Array　　　　　　a　　　　　　ary

56、Boolean　　　　　f　　　　　　bin

57、Byte　　　　　　　b　　　　　　bit

58、Currency　　　　　c　　　　　　cur

59、Double　　　　　　d　　　　　　dbl

60、Date/Time　　　　dt　　　　　　dtm/dat

61、Integer　　　　　i　　　　　　int

62、Long　　　　　　　l　　　　　　lng

63、Object　　　　　　o　　　　　　obj

64、Single　　　　　　　　　　　　sng

65、String　　　　　　s　　　　　　str

66、Variant　　　　　v　　　　　　var

67、　　表4-2变量命名的前缀

68、　　4.4.4使用数组

69、　　如果你使用过其他编程序语言,可能对数组已经比较熟悉了.数组是具有相同数据类型并共同享有一个名字的一组变量的集合.数组中的元素通过索引数字加以区分,定义数组的方法如下:

70、　　Dimarray_name（n）Astype（其中n是数组元素的个数）

71、　　例如,如果要创建保存10个学生名字的数组,可以用以下语句:

72、　　Dims学生名字（9）AsInteger

73、　　注意,括号中的数字是9而不是10.这是因为在默认的情况下,第一个索引数字是0.数组在处理相似信息时非常有用.假设要处理15门考试成绩,可以创建15个独立的变量,这意味着要使用15个Dim语句。

也可以创建一个数组来保存考试成绩，具体如下：

74、　　Dims考试成绩（14）AsInteger

75、　　声明数组时的另一种方法是不给定大小。

可以在程序运行时定义其大小。

通过创建动态数组就可以做到。

例如，你的程序要创建一表格，可以提示用户输入表格的行和列的数目。

声明动态数组的语法如下：

76、　　Dimdyn_array（）Astype

77、　　对数组声明后可以在程序运行时用：

ReDim语句指定数组的大小：

78、　　ReDimdyn_array（）（array_size）

79、　　参数array_size代表数组的新大小。

如果要保留数组的数值，请在ReDim语句后使用保留字Preserve,具体语法如下：

80、　　ReDimPreservedyn_array（array_size）

81、　　4.4.5变量赋值

82、　　声明变量后就可以给变量赋值。

请注意下列语句中为数组变量赋值时索引数字的使用。

83、　　程序清单4-4

84、　　Dimi人数AsInteger

85、　　Dimi考试成绩AsInteger

86、　　DimiAsInteger

87、　　i人数=inputbox（"输入学生的人数：

"）

88、　　ReDimPreservei考试成绩（i数量）

89、　　Fori=1toi人数

90、　　i考试成绩（i）=inputbox（"输入考试成绩"&i）

4、变量类型

数据类型

存储空间大小

范围

Byte

1个字节

0到255

Boolean

2个字节

True或False

Integer

2个字节

-32,768到32,767

Long

（长整型）

4个字节

-2,147,483,648到2,147,483,647

Single

（单精度浮点型）

4个字节

负数时从-3.402823E38到-1.401298E-45；正数时从1.401298E-45到3.402823E38

Double

（双精度浮点型）

8个字节

负数时从-1.79769313486232E308到

-4.94065645841247E-324；正数时从4.94065645841247E-324到1.79769313486232E308

Currency

（变比整型）

8个字节

从-922,337,203,685,477.5808到922,337,203,685,477.5807

Decimal

14个字节

没有小数点时为+/-79,228,162,514,264,337,593,543,950,335，而小数点右边有28位数时为+/-7.92