ch2 VBA编程基础.docx

1、ch2 VBA编程基础第二章 VBA编程基础本章包括 理解VBA语言元素，其中包括变量、数据类型、常量和数组 使用VBA内置的函数 处理对象和集合 控制过程的执行本章讨论VBA中某些关键的语言元素和编程概念。如果以前使用过其他的编程语言，那么可能比较熟悉这些内容。然而，VBA有它独特的地方，因此即使经验丰富的编程人员也会发现一些新的内容。2.1 VBA的语言元素概览首先从一个简单的VBA Sub过程开始。在下面的代码中，过程存储在一个VBA模块中，此过程计算了前100个整数的总和，当代码结束执行后，显示出一条表示结果的消息。Sub SumDemo() 一个演示VBA的例子 Dim iTotal

2、 As Integer, i As Integer iTotal = 0 For i = 1 To 100 iTotal = iTotal + i Next i MsgBox (iTotal)End Sub这个过程使用了一些常见的VBA语言元素，其中包括一个注释(注释行前面用单引号标识)、两个变量(iTotal和i)、两条赋值语句(iTotal =0 和 iTotal = iTotal + i)、一个循环结构(For Next)和一个VBA语句(MsgBox)。所有这些都会在以后的小节中阐述。VBA的语句与C语言的语句有一些不一样的地方。默认情况下，一句代码为一行，不用“；”标识语句的结束。当

3、然，也可以将多个语句写在一行，其中用“：”分隔，也可以将一个语句(如果这个语句太长，不便于阅读的话)拆成几行，在分拆时，用“空格+_”来分拆它们。2.2 注释注释就是以单引号开头的描述性文本，VBA将完全忽略这些文本。养成使用注释是一个好的编程习惯，因为这将极大地提高程序的可读性。为了更好地使用注释，这里给出一些建议： 使用注释简要描述编写的每个过程的目的 使用注释描述对过程所做的修改 使用注释指出正在以一种与众不同的或者不标准的方式使用函数或者构造 使用注释描述变量的目的，以便本人和其他人都能够明白名称所隐藏的内涵 使用注释描述为了克服Excel的故障而开发出的解决办法 与其编写代码后再添加

4、注释，倒不如编写代码的同时写注释提示：如果要测试一个不含某个特殊语句或者一组语句的过程，不用删除这些语句，只要将这些语句转换为注释即可。方法是，在语句开始的地方插入一单引号，之后当执行这个过程的时候，VBA将忽略这些语句。如果要把注释再转换回语句，删除单引号即可。VBE的“编辑”工具栏包含了两个这要的按钮，一个是“设置注释块”，单击它将选中的语句(块)转换成注释；另一个是“解除注释块”，单击它可以将选中的注释转换回语句。2.3 变量、数据类型和常量VBA的主要目的就是处理数据。某些数据存在于对象中，比如工作表的单元格区域内，其他的数据存储在自定义的变量中。“变量”只是命名的位于计算机内存中的存

5、储位置。变量可以接纳很多种的“数据类型”，从简单的布尔值(True或者False)到复杂的双精度值。给变量赋值的时候，使用等号运算符。VBA规定了有关变量名的规则： 可以使用字母、数字和一些标点符号，但是第一个字符必须是字母； VBA不区分大小写。为了使得变量的名称更具有可读性，通常编程人员使用混合的大小写(例如，interestRate而不是interestrate)。我是一个C语言规则的推崇者，所以本书凡是变量名，采用首字母小写的“驼峰规则”，而凡是函数(过程)名、自定义类型名采用首字母大写的“驼峰规则”。 变量名称中不能嵌入特殊类型的字符(#，$，%，&或者!)。 变量名称最多可以包含2

6、54个字符。在上一章中，有很多给变量命名和函数名的例子，这里不再重复。需要补充的一点是，变量名或函数名不能与VBA中的“保留字”同名。2.3.1 VBA的数据类型学习过C语言的读者都知道，在C语言程序中，所有的变量必须先声明再使用，一是为了系统为之分配内存，二是为了编译器在对程序进行编译时进行合法性检查。我们称这样的语言为“严格的”语言，这样的语言在初学者看来并不习惯。但这样的语言能使我们少犯语法错误，占用更少的系统内存，使我们更容易写出执行效率更高的程序。很遗憾，VBA并不是一种“严格的”语言，也就是说，VBA允许我们不定义就直接使用变量。这是有性能代价的，本节的后面我们将通过一个具体的实例

7、来证明这一点。要想让VBA象C语言一样，要求所有的变量“先声明，后使用”，可以程序的顶部添加一句“Option Explicit”，或者在VBE中，选择菜单“工具”“选项”，打开“选项”对话框，在“编辑器”选项卡上，勾选“要求变量声明”选项。表2.1列出了VBA的内置数据类型的分类(注意，还可以定义自定义的数据类型，详细情况请参阅本章后面的“用户自定义数据类型”一节)。表2.1 VBA内置的数据类型数据类型存储空间范围Byte1 个字节0 到 255Boolean2 个字节True 或 FalseInteger 2 个字节-32,768 到 32,767Long(长整型)4 个字节-2,147

8、,483,648 到 2,147,483,647Single (单精度浮点型)4 个字节负数时从 -3.402823E38 到 -1.401298E-45；正数时从 1.401298E-45 到 3.402823E38Double (双精度浮点型)8 个字节负数时从 -1.79769313486231E308 到 -4.94065645841247E-324；正数时从4.94065645841247E-324 到 1.79769313486232E308Currency (变比整型)8 个字节从 -922,337,203,685,477.5808 到 922,337,203,685,477.5

9、807Decimal14 个字节没有小数点时为 +/-79,228,162,514,264,337,593,543,950,335，而小数点右边有 28 位数时为 +/-7.9228162514264337593543950335；最小的非零值为 +/-0.0000000000000000000000000001Date8 个字节100 年 1 月 1 日 到 9999 年 12 月 31 日Object4 个字节任何 Object 引用String (变长)10 字节加字符串长度0 到大约 20 亿String(定长)字符串长度1 到大约 65,400Variant(数字)16 个字节任何数

10、字值，最大可达 Double 的范围Variant(字符)22 个字节加字符串长度与变长 String 有相同的范围用户自定义（利用 Type）所有元素所需数目每个元素的范围与它本身的数据类型的范围相同。一般来讲，最好使用占用字节最少却能够处理所有赋值给它的数据类型。当VBA使用了数据的时候，执行的速度与VBA为其配置的字节数有关。换句话说，相同的程序，使用的字节数越少，VBA可以访问和处理数据的速度就越快。对于工作表计算而言，Excel使用Double数据类型，为了能够在VBA中处理数字的时候不丢失任何的精度。对于整数计算而言，如果砍确信值不会超过32767，则可以使用Integer数据类型

11、。否则，使用Long数据类型。当处理Excel的工作表行编号时，就要使用Long数据类型，因为行的编号(最大值为65536)超过了Integer数据类型所允许的最大值。而处理Excel的工作表列编号时，使用Integer即可。测试Variant数据类型为了测试一下数据类型定义是否重要，我写了一个测试程序，该程序在一个循环中执行一些无意义的运算，最后显示这个程序的执行时间：Sub VariantTest() Dim x As Integer, y As Integer Dim a As Integer, b As Integer, c As Integer Dim i As Integer, j

12、 As Integer Dim startTime As Date, endTime As Date 记录程序开始运行的时间 startTime = Timer 执行计算 x = 0: y = 0 For i = 1 To 5000 For j = 1 To 5000 a = x + y + i b = y - x - i c = x - y - i Next j Next i 记录计算结束的时间 endTime = Timer 显示程序运行的时间 MsgBox Format(endTime - startTime, 0.0)End Sub在我的机器上，运行这个程序花了2.1秒(当然，根据系统

13、的不同，这个时间是不一样的)。将其中的Dim声明语句全部转换成注释，其结果是VBA使用默认的数据类型Variant。再次运行该程序，花了6.5秒，大约是之前运行时间的3倍。_2.3.2 声明变量如果不为VBA程序中使用的变量声明数据类型，那么VBA将使用默认的数据类型Variant，这样做并不是明智的选择。下面将阐述如何将变量声明为不同的类型。1 声明变量的格式声明变量最基本的语法是：Dim 变量名 As 数据类型如下面的示例声明了不同的数据类型：Dim ch As ByteDim bFound As BooleanDim i As Integer, j As IntegerDim rowIn

14、dex As LongDim rate As SingleDim total As DoubleDim firstDay As Date, lastDay As DateDim userName As String * 20, fileName As StringDim obj As Object, var As Variant注意，上面的代码中，将userName声明为一个定长串，即只能存放不超过20个字符(无能是半角字符还是汉字)，而fileName是一个变长字符串。2 变量的作用域变量的“作用域”决定变量可以用在哪个模块和过程。变量的作用域可以为表2.2中的任何一种：表2.2 变量的作用

15、域作用域如何声明这种作用域的变量单个过程在过程中包括一个Dim或者Static语句单个模块在模块内的第一个过程之前包括一个Dim或者Private语句所有模块在模块内的第一个过程之前包括一个Public语句在过程中用Static声明的变量，称为“静态变量”。静态变量的特点是，在程序执行的过程中，保持它们的值不变。如下的代码演示了这个特点：Sub StaticVariableTest() Dim i As Integer, j As Long For i = 1 To 500 j = StaticFunction Next i MsgBox jEnd SubFunction StaticFunc

16、tion() As Long Static iv As Long Dim iv As Long iv = iv + 1 StaticFunction = ivEnd Function以上代码运行时，对话框输出501。如果将“Static iv As Long”一句注释掉，取消“Dim iv As Long”一句的注释，对话框输出1。在模块内，所有的函数之外使用Private或Dim声明的变量称为模块级变量，仅能在该模块内使用。如，下面的语句声明了两个模块级变量：Dim rowIndex as LongPrivate columnIndex as Integer在模块内，所有的函数之外使用Pub

17、lic关键字声明的变量称为公共变量，这样的变量在整个程序中都可以使用。如下面的语句声明了一个String类型的公共变量fileName：Public fileName as String * 403 指定变量类型的另一种方法与BASIC一样，VBA允许将一个字符附加到变量名称的后，从而指出变量的数据类型。例如，下面的语句将myVar变量声明为一个整型，等价于语句“Dim myVar As Integer”：Dim myVar%大多数VBA数据类型都有类型声明字符，这些字符如下：数据类型 类型声明字符Integer(整型) %Long(长整型) &Single(单精度浮点型) !Double(双

18、精度浮点型) #Currency(变化整型) String(字符串) $列出这些类型声明字符是以防在阅读其他的程序中，碰到这样的方法，但并不推荐使用这种方式声明变量。4 变量名的前缀一些编程人员命名变量时，给变量加一个类型前缀，以便可以从它们的名称看出它们的数据类型。就我个人而言，通常不会使用这种方法。因为这样会使代码更加难以阅读。这些前缀是一个小写的字母，例如，如果用一个Boolean类型的变量跟踪是否已经保存工作簿，则将变量命名为bSaved。这样的话，就很清楚该变量是一个Boolean类型的变量。下面列出了一些不同数据类型的标准前缀：数据类型 前缀Boolean bInteger iLo

19、ng lSingle sDouble dCurrency cDate/Time dtString strObject objVariant v用户定义类型 u2.3.3 声明常量在程序运行时，值不会改变的量称为“常量”。声明常量的语法格式如下：Const 常量名 As 数据类型名 = 值如果不指定常量的类型，则系统将自动根据常量值确定其类型。如下面的代码演示的一样：Sub ConstantVariableTest() Const numQuarters As Integer = 4 Const rate = 0.675, period = 12 Const modName As String

20、* 20 = Macros MsgBox TypeName(rate) & , & TypeName(period)End Sub程序在运行时，显示变量rate的类型为Double，period的类型为Integer。在编码中，除了使用自定义的常量，还常常会用到系统预定义的常量。比如，上一章提到的更改页面设置的代码中，将活动工作表的页面方向设置为横向的代码：ActiveSheet.PageSetup.Orientation = xlLandscape这里人使用了系统为我们预定义的常量xlLandscape。对于这样的常量，利用“对象浏览器”很容易找到它的值。使用这样的常量，无疑会提高代码的可读

21、性。2.3.4 使用字符串VBA中的字符串包括变长串和定长串，其声明方式前面已经介绍过了。使用符号“&”或“+”可以将两个字符串连接起来。使用字符串时，不可避免地要进行字符串的处理。与字符串处理相关的函数列举如下：表2.3 字符串处理函数作用关键字比较两个字符串StrComp变换字符串StrConv大小写变换Format, LCase, UCase建立重复字符的字符串Space, String计算字符串长度Len设置字符串格式Format重排字符串LSet, RSet处理字符串InStr, Left, LTrim, Mid, Right, RTrim, Trim设置字符串比较规则Option

22、Compare运用 ASCII 与 ANSI 值Asc, Chr2.3.5 日期的使用可以使用字符串变量存储日期，但是却不能在字符串变量上执行日期计算。使用Date数据类型是处理日期的最佳方式。定义日期变量将使用8个字节的存储空间，日期的范围可以从0100年1月1日到9999年12月31日，即使是对于要求极高的财务计算来说，这也是足够的。Date类型类型还可以用于存储与时间有关的数据。在VBA中，指定日期和时间时，用符号“#”将它们括起来，如下所示：Dim today As Date, start As DateConst firstDay As Date = #1/1/1010#Const

23、noon As Date = #12:00:00 PM#很多时候，我们会用到日期时间的处理函数，这些函数如表2.4所示：表2.4 日期时间处理函数作用关键字设置当前日期或时间Date计算日期DateAdd,返回日期DateSerial,返回时间TimeSerial,设置日期或时间Date,计时Timer日期变换Day, Month, Weekday, Year时间变换Hour, Minute, Second设置当前日期或时间Date2.4 运算符VBA中的运算符包括有： 算术运算符：(乘方)、 -(减)、 *(乘)、 /(除)、 (运算前将除数和被除数转换成Byte、Integer或Long型

24、)、 Mod(求余)、 +(加法)、 &(字符串连接) 比较运算符：=、 、 、 =、 Like、Is 逻辑运算符：Not、And、Or、Xor、Eqv、Imp2.5 数组“数组”就是一组拥有相同名称的同类元素，使用数组名称和一个索引号来引用数组中的某个特定的元素。可以用Dim或者Public语句声明数组，就像声明普通的变量一样，还可以指定数组中包含的元素数目。默认情况下，数组的下标是从0开始的(这与C语言中数组的下标索引号一样)。例如，Dim monthNames(12) As String以上语句定义一个包含12个字符串变量的数组，这样每个变量就对应于一个月份的名称。如果将数组命名为“mo

25、nthNames”，则可以将这个数组的第一个元素称之为monthNames(0)，第二个元素为monthNames(1)。为了改变这种情况，声明数组时可以使用以下的语句：Dim monthNames(1 To 12) As String现在这个数组的第一个元素是monthNames(1)，而不是monthNames(0)。如果你不喜欢这样的声明方式，又想让数组的下标索引号从1开始，可以在模块的开始加上以下语句：Option Base 1以上的数组我们称之为一维数组，还可以声明多维数组，比较常用的是二维数组。以下的语句声明了一个二维的数组：Dim cell(20,20) As Integer该数

26、组拥有2020个整型元素组成。对于一个已经声明的数组，可以用LBound取出其最小下标，用UBound取出其最大下标，用Erase将数组中元素的值擦除。以VBA中还可以定义动态数组，如下面的代码所示：Private Sub DynamicArray() Dim arr() As Integer, i As Integer 在使用ReDim对arr进行重定义以前，任何对arr的访问都会引发一个运行时错误即使是LBound(arr)或UBound(arr)也不行 i = InputBox(请输入一个整数) ReDim arr(i) As Integer MsgBox LBound(arr) & t

27、o & UBound(arr)End Sub2.6 对象变量“对象变量”是代表一个完整对象的变量，比如单元格区域或者工作表。对象变量很重要，因为可以显著地简化代码，并使代码的执行速度更快。与普通的变量一样，用Dim或者Public语句声明对象变量。例如，下面的语句将inputArea声明为一个Range对象：Dim inputArea As Range对象变量必须使用Set赋值，如下面的代码所示：inputArea = ActiveSheet.Range(A1)2.7 用户自定义数据类型VBA允许创建自定义的数据类型，用户定义数据类型可以方便地处理一些数据类型。如果应用程序要处理客户的信息，可

28、能需要创建一个用户定义数据类型“CustomerInfo”，代码如下：Type CustomerInfo company As String * 25 contact As String * 15 regionCode As Integer sales As LongEnd Type创建了用户定义数据类型之后，可以使用Dim语句将变量声明为这种类型。通常，定义数组为这种类型。例如：Dim customers(10) As CustomerInfo该数组中的这10个元素由4个部分组成(当用户定义数据类型CustomerInfo时指定)，可以采用如下语句引用记录的某个特殊的部分：customers

29、(1).company = Acme Toolscustomers(1).contact = Tim Robertsoncustomers(1).regionCode = 3customers(1).sales = 150677还可以作为一个整体处理数组中的元素。例如，从customers(1)复制信息到customers(2)中，可以使用的语句：customers(2) = customers(1)上面的语句等同于下面的语句块：customers(2).company = customers(1).companycustomers(2).contact = customers(1).cont

30、actcustomers(2).regionCode = customers(1).regionCodecustomers(2).sales = customers(1).sales2.8 使用内置函数与大多数编程语言一样，VBA包含各种内置的函数，它们可以简化计算和操作。通常，函数允许执行其他很难完成甚至不可能完成的操作。很多VBA函数都与Excel的工作表函数类似(或者一样)。例如，VBA函数UCase，它将字符串参数的值转换为大写字母，该函数等同于Excel的工作表函数UPPER。本书的附录B中，列出了所有的VBA函数，还对每个函数做了简要的描述，详细的描述与使用参考VBA的帮助系统。在VBA中，不仅可以使用内置的VBA函数，甚至还可以使用Excel工作表函数。为了使用工作表函数，只要在函数名称的前面加上下列语句：Application.WorksheetFunction如下面的代码所示：Dim total As IntegerDim calArea As RangeSet calArea = Acti