VBA的应用.docx

1、VBA的应用1、 SUB的应用单元格作为VBA入门的一个必需掌握的概念，单元格的引用有多种方式，现 在根据自己的经验及参考相关的书本来整理涉及单元格引用的知识。最近发现自己对单元格方面的知识并没有完全掌握，收到了罗版的VBA自学宝典，结合以前的那些相关资料，一边学习，一边把相关的知识点记录下来。一、选取所有单元格。 1. Sub 选取全部单元格一() 2. Cells.Select 3. End Sub 复制公式 或代码注：cells是所有单元格 的集合。 1. Sub 选取全部单元格二() 2. Rows.Select 3. End Sub 复制公式 或代码而Rows则是所有行的集合。 1.

2、 Sub 选取全部单元格三() 2. Columns.Select 3. End Sub 复制公式 或代码而columns则是所有列的集合。无论是所有单元格的集合，还是所有行或列的集合选取，都是工作表中全选所有单元格的表示方式。二、Range引用来选取单元格1、Range引用单元格方式1：Range(单元格地址)例如：Range(A1):表示单元格A1，注意：单元格地址要加双引号值得注意的是：双引号的输入必须是在半角状态下输入的，否则会出现错误。单元格地址里面的单元格地址不区分相对引用和绝对引用的，无论你是使用Range(A1)、Range($A1)、 Range($A$1)还是Range(A

3、$1)，都是引用通一个单元格。一般为了简化作用，直接用Range(A1)这种形式来表示引用单元格。2、Range引用单元格方式2：Range(单元格区域)在VBA数组中，经常遇到arr=Range(A1:D10)这种数组赋予方式，而里面出现了Range(单元格区域)的这种单元格区域引用。从这个最常见的例子中，我们可以发现，A1:D10代表了一个单元格区域的引用，其中A1是该区域的左上角上面的单元格，而D10则是该区域右下角下 面的单元格。但大家可以从下面两个例子来发现： 1. Sub q1() 2. Range(A1:D10).Select 3. End Sub 4. Sub q2() 5.

4、Range(D10:A1).Select 6. End Sub 复制公式 或代码这两个小例子得到的结果都是选取A1:D10这个单元格区域 值得大家注意的，以下的这些通过索引号作为参数的引用，都是比较陌生的。比如以下这个小例子：例子1：1. Sub T1() 2. Range(B2:E6)(1).Select 3. End Sub 复制公式 或代码注释：该例子代表了选取B2：E6区域中的第一个单元格的，即选取B2例子2： 1. Sub T2() 2. Range(B2:E6)(3).Select 3. End Sub 复制公式 或代码注：该例子代表选取B2：E6区域中的第三个单元格，即D2，而单

5、元格的区域的读取一般是从左到右，然后再从上到下进行读取 的，故该区域的前3个单元格分别是B2、C2、D2，而第3个单元格就是为D2例子3： 1. Sub T3() 2. Range(B2:E6)(3.5).Select 3. End Sub 复制公式 或代码注意：当里面的索引号为小数时，excel会对其进行四舍五入，比如例子3中的3.5，excel会把它转换为4，故例子3 中的代表是B2:E6区域的第4个单元格选取，即选取E2例子4： 1. Sub T4() 2. Range(B2:E6)(3.2).Select 3. End Sub 复制公式 或代码而例子4中，因为四舍五入的关系，得到的结果

6、是选取该区域的第3个单元格，即选取D2例子5： 1. Sub F1() 2. Range(B2:E6)(1, 4).Select 3. End Sub 复制公式 或代码注：在例子5中，Range(单元格区域)(行号，列号),所代表的是选取该单元格区域中第几行与第几列交叉的那个单元 格，而例子5中代表是选取B2:E6区域中的第1行第4列的单元格，在该区域中，所符合条件的单元格为E2。值得关注的是，(行号，列号）里面的参数可以使用0或者负数。当行号的参数为0的时候，则向该区域的左上角单元格向上偏移一个单位，当列号为0时候，则向该区域左上角单元格向左偏移一个单位；如果参数是负数的时候， 在原来的基础

7、上追加偏移量。具体请看例子6、7： 1. Sub F1() 2. Range(B2:E6)(0, 0).Select 3. End Sub 复制公式 或代码例子6中是选取了A1单元格，即B2：E6区域左上角单元格B2分别向上偏移一行，向左偏移一列，所得到的单元格为A1例子7： 1. Sub F1() 2. Range(C3:E6)(-1, -1).Select 3. End Sub 复制公 式或代码例子7中代表了C3：E6区域的左上角单元格C3分别向上偏移两行，向左偏移两列，所得到的单元格为A13、Range(列标 & 行数)的单元格引用方式：例子7： 1. Sub Y() 2. Range(

8、B & 10).Select 3. End Sub 复制公 式或代码从这个最简单的例子，一个最常见的单元格引用方式。注意：列标需要加上双引号，并且要用到&把列标和行号连接 起来。经常用到的一个例子：往A1:A100里面分别填充1到100等这100个数字 1. Sub a() 2. Dim i As Integer 3. For i = 1 To 100 4. Range(A & i) = i 5. Next i 6. End Sub 复制公 式或代码从中可以看到这种单元格方式引用的作用。4、Range(定义名称)的单元格引用：首先在excel2007中，公式定义名称把A1:A100区域定义为数

9、字。便可以使用这种方式，具体请看以下例子: 1. Sub B() 2. Range(数字).Select 3. End Sub 复制公 式或代码注：A1：A100这个区域已经定义名称为数字，该代码表示选取A1:A100单元格区域注意：定义名称也必须添加双引号，并且是半角下输入的。5、Range方式引用多个区域：前面详细说了：Range(A1:B10）这样类似的区域引用方式，但值得注意的是，那只是连续区域的引用。还有一些不连续的区域，则需要在区域中间 加,号比如以下例子： 1. Sub 多区域引用() 2. Range(A1,C1,D3:E8).Select 3. End Sub 复制公 式或代

10、码注意：A1,C1,D3:E8这个为不连续的区域，中间需要用,号隔开，并且不连续的区域直接还是要用双引号作为标 记，这个代码表示选取A1、C1、D3:E8。这里主要要关注,号的使用。但是，该方式引用有一个限制:参数的长度不能超过256个字符，否则产生运行错误。连接上面那个例子，给出另外一种使用方式： 1. Sub 多区域引用2() 2. Union(Range(A1), Range(C1), Range(D3:E8).Select 3. End Sub 复制公 式或代码该例子中主要使用了union 来进行连接，这种方式得到的结果和上面的那个例子所得到的结果是一样的。这两种方式，都是对不连续区域

11、进行连接，相对比，还是上面的那个例子比较简洁。 Range嵌套使用主要语法：Range(cell1,cell2),其中cell1和cell2是必选参数。Cell1代表指定区域目标的左上角单元格，Cell2代表指定区域目标右下角单元格。具体看例子1. Sub a() 2. Range(Range(A1), Range(E4).Select 3. End Sub 复制公式或代码注：该例子表示选取A1：E4区域而下面这个例子：1. Sub B() 2. Range(Range(A1:A3), Range(E2).Select 3. End Sub 复制公式或代码注：上面这个例子表示选取A1：E3区域

12、。注意不是选取A1：E2的区域。VBA会从两个区域中最左上角的单元格作为新的区域作为参照起点， 再取两个区域所跨越的最大行作为新的区域行数，取两个区域跨越的最大列作为新区域的列数。与Range相关的选取整行或整列：1. Sub a() 2. Range(A:A).Select 3. End Sub 复制公 式或代码上面这个例子，表示选取A列，注意，无论是A:A或者是A:a,代表的都是选取A列，里面不区分大小写。另外表示选取第一列，还可以用下面的代码： 1. Sub yy() 2. Columns(1).Select 3. End Sub 复制公 式或代码1. Sub aa() 2. Colum

13、ns(A).Select 3. End Sub 复制公 式或代码1. Sub aa1() 2. Columns(A:A).Select 3. End Sub 复制公 式或代码而选取第一行可以用下面的例子： 1. Sub b() 2. Range(1:1).Select 3. End Sub 复制公 式或代码而选取第一行，还可以使用下面的代码： 1. Sub yy1() 2. Rows(1).Select 3. End Sub 复制公 式或代码1. Sub aa2() 2. Rows(1).Select 3. End Sub 复制公 式或代码1. Sub aa3() 2. Rows(1:1).S

14、elect 3. End Sub 复制公 式或代码三、Cells引用单元格1、Cells(行数，列数)请看下面例子： 1. Sub a() 2. Cells(1, 2).Select 3. End Sub 复制公式 或代码该例子表明选取B1单元格2、Cellls(行数，列标)请看例子： 1. Sub ba() 2. Cells(2, B).Select 3. End Sub 复制公式 或代码该例子中表明选取B2 单元格，但该方法引用单元格永远只能引用一个，并且不能引用区域。3、Range.cells(横坐标，纵坐标）这种方式引用单元格是以其父对象Range左上角单元格作为参照系，向下累加的坐标

15、系数来指定单元格。请看下例： 1. Sub aa() 2. Range(B2:F10).Cells(2, 2).Select 3. End Sub 复制公式 或代码该代码表示B2：F10单元格中横坐标为2、纵坐标为2的单元格C3，该参照是以B2为基准的。另外，cells里面可以使用小数，不过VBA会对其进行四舍五入再计算，具体看下例： 1. Sub au() 2. Range(B2:F10).Cells(3.8, 3.4).Select 3. End Sub 复制公式 或代码该代码表示B2：F10单元格中横坐标为4、纵坐标为3的单元格D5。4、Range.cells(索引号)当使用单个索引号作

16、为参数时，它表示父对象中的一个索引子集。其编号方式是先行后列、先左后右。请看下例： 1. Sub au() 2. Range(B2:F10).Cells(3).Select 3. End Sub 复制公式 或代码该例子表示B2：F10区域中第3个单元格D2。这种方式比较少见，也比较少用。四、A1方式引用单元格，该单元格表示的简化方法。单元格的简化表达方式：单元格地址表示单元格A1，简写为A1 注意：单元格地址外是中括号(),单元格地址不能使用双引号。 1. Sub 选取单元格A1的方法() 2. A1.Select 3. End Sub 复制公式 或代码该例子表示选取单元格A1。另外，该方式可

17、以用来表示选取单元格区域： 1. Sub 选取() 2. A1:D10.Select 3. End Sub 复制公式 或代码表示选取A1：D10区域。而之间可以使用逗号开选取不连续单元格，具体请看下例： 1. Sub 选取() 2. A1:D10,H1,H10.Select 3. End Sub 复制公式 或代码表示选取A1：D10、H1、H10等区域的单元格。从该例子看出，A1的方式是最简单的写法。 2、 变量的定义3、 VBA变量详解 4、 变量是用于临时保存数值的地方.每次应用程序运行时,变量可能包含不同的数值,而在程序运行时,变量的数值可以改变. 5、 为了说明为什么需要变量,可以按照

18、如下步骤创建一个简单的过程: 6、 1)创建一个名为你叫什么名字的过程.7、 2)在过程中输入如下代码:8、 Inputbox 输入你的名字:9、 现在不要担心inputbox语句的语法,将在第六学时中了解到有关这条命令的更多信息.10、 3)按下F5键运行过程,这时会显示一个输入框,要求输入你的名字.11、 4)输入你的名字并按确定按钮,则结束该过程. 12、 你输入的名字到那里去了?如何找到用户在输入框中输入的信息?在这种情况下,需要使用变量来存储用户输入的结果.13、 4.4.1 变量的数据类型 14、 使用变量的第一步是了解变量的数据类型.变量的数据类型控制变量允许保存何种类型的数据.

19、表4-1列出了VBA支持的数据类型,还列出了各种类型的变量所需要的存储空间和能够存储的数值范围. 15、 数据类型存储空间数值范围16、 Byte1字节0 - 25517、 Booleam2字节True或者False18、 Integer2字节-32768 - 3276719、 Long(长整型)4字节-2147483648 - 214748364720、 Single4字节负值范围:-3.402823E38 - -1.401298E-4521、 正值范围:1.401298E-45 - 3.402823E3822、 Double8字节负值范围:-1.79769313486232E308 - -

20、494065645841247E-32423、 正值范围:4.94065645841247E-324 - 1.79769313486232E30824、 Currency8字节-922337203685477 - 92233720368547725、 Decimal14字节不包括小数时:+/-7922816251426433759354395033526、 包括小数时:+/7.922816251426433759354395033527、 Date8字节1000年1月1日 - 9999年12月31日28、 Object4字节任何引用对象29、 String(长字符串）10字节+1字节/字符0

21、- 约20亿30、 String(固定长度）字符串的长度1 - 约6540031、 Varient（数字）16字节Double范围内的任何数值32、 Varient（文本）22字节+1字节/字符数据范围和变长字符串相同33、 表4-1 VBA数据类型 34、 作为ABV程序员，一个目标是选择需要存储空间尽量小的数据类型来保存所需要的数据，这正是表4-1提供各种数据类型存储空间的原因。例如，要保存诸如班级学生总数这样的小数字，那么Byte数据类型就足够了。在这种情况下，使用Single数据类型只是对计算机存储空间的浪费。35、 4.4.2 用Dim语句创建变量(声明变量) 36、 现在,你对变量

22、可以使用的数据类型已经比较熟悉了,以下我们将创建变量.创建变量可以使用Dim语句,创建变量通常成为声明变量 Dim语句的基本语法如下: 37、 Dim 变量名 AS 数据类型 38、 这条语法中的变量名代表将要创建的变量名.对变量的命名规则和对过程的命名规则相同.这条语句中的数据类型部分可以是表4-1中的任何一种数据类型. 39、 变量名必须以字母开始,并且只能包含字母数字和特定的特殊字符,不能包含空格句号惊叹号,也不能包含字符 & $ #.名字最大长度为255个字符 40、 在接下来的练习中将说明如何在VBA中使用变量,你将要输入你的名字,并用一个消息框将其显示出来.具体步骤如下: 41、

23、1)创建一个名为显示你的名字的子程序.42、 2)输入以下代码:43、 Public Sub 显示你的名字()44、 Dim s名字 As String45、 s名字 = Inputbox(请输入你的名字:)46、 Msgbox 你好& s名字47、 End Sub48、 3)将鼠标放到过程中的任何地方,按下F5键运行过程,会显示一个输入框.49、 4)输入你自己的名字并按回车键,会显示一个消息框,显示的文字中包含你自己的名字.50、 5)单击确定按钮,返回过程中. 51、 在Dim语句中不必提供数据类型.如果没有数据类型,变量将被定义为Variant类型,因为VBA中默认的数据类型是Vari

24、ant.你知道这一点后,最初的反应也许是觉得应该不用自己决定数据类型,而将一切抛给VBA.这种观念是完全错误的.你必须决定选择使用何种数据类型。因为Variant数据类型占用存储空间较大（16或22字节）而且它将影响程序的性能。VBA必须辨别Variant类型的变量中存储了何种类型的数据。52、 4.4.3 变量命名的惯例 53、 下表给出了推荐的变量命名惯例 54、 数据类型短前缀长前缀55、 Arrayaary56、 Booleanfbin57、 Bytebbit58、 Currencyccur59、 Doubleddbl60、 Date/Timedtdtm/dat61、 Integeri

25、int62、 Longllng63、 Objectoobj64、 Singlesng65、 Stringsstr66、 Variantvvar67、 表4-2 变量命名的前缀 68、 4.4.4 使用数组 69、 如果你使用过其他编程序语言,可能对数组已经比较熟悉了.数组是具有相同数据类型并共同享有一个名字的一组变量的集合.数组中的元素通过索引数字加以区分,定义数组的方法如下: 70、 Dim array_name(n) As type (其中n是数组元素的个数) 71、 例如,如果要创建保存10个学生名字的数组,可以用以下语句:72、 Dim s学生名字(9) As Integer73、 注

26、意,括号中的数字是9而不是10.这是因为在默认的情况下,第一个索引数字是0.数组在处理相似信息时非常有用.假设要处理15门考试成绩,可以创建15个独立的变量,这意味着要使用15个Dim语句。也可以创建一个数组来保存考试成绩，具体如下：74、 Dim s考试成绩（14） As Integer75、 声明数组时的另一种方法是不给定大小。可以在程序运行时定义其大小。通过创建动态数组就可以做到。例如，你的程序要创建一表格，可以提示用户输入表格的行和列的数目。声明动态数组的语法如下：76、 Dim dyn_array() As type77、 对数组声明后可以在程序运行时用：ReDim语句指定数组的大小

27、：78、 ReDim dyn_array()(array_size)79、 参数array_size代表数组的新大小。如果要保留数组的数值，请在ReDim语句后使用保留字Preserve,具体语法如下：80、 ReDim Preserve dyn_array(array_size)81、 4.4.5 变量赋值 82、 声明变量后就可以给变量赋值。请注意下列语句中为数组变量赋值时索引数字的使用。 83、 程序清单4-484、 Dim i人数 As Integer85、 Dim i考试成绩 As Integer86、 Dim i As Integer87、 i人数 = inputbox(输入学生的

28、人数：)88、 ReDim Preserve i考试成绩(i数量)89、 For i = 1 to i人数90、 i考试成绩(i) = inputbox(输入考试成绩& i )4、变量类型数据类型 存储空间大小范围Byte1 个字节0 到 255Boolean2 个字节True 或 FalseInteger 2 个字节-32,768 到 32,767Long(长整型)4 个字节-2,147,483,648 到 2,147,483,647Single (单精度浮点型)4 个字节负数时从 -3.402823E38 到 -1.401298E-45；正数时从 1.401298E-45 到 3.402823E38Double (双精度浮点型)8 个字节负数时从 -1.79769313486232E308 到 -4.94065645841247E-324；正数时从4.94065645841247E-324 到 1.79769313486232E308Currency (变比整型)8 个字节从 -922,337,203,685,477.5808 到 922,337,203,685,477.5807Decimal14 个字节没有小数点时为 +/-79,228,162,514,264,337,593,543,950,335，而小数点右边有 28 位数时为 +/-7.92