ExcelVBA知识经典学

《ExcelVBA知识经典学》由会员分享，可在线阅读，更多相关《ExcelVBA知识经典学（50页珍藏版）》请在装配图网上搜索。

1、1 Excel VBA 学习1、基础知识1.什么是 VBA？当前辈们使用 Excel 的时候，他们惊奇的发现：Excel 那是相当的彪悍，几乎任何的数据分析与处理，它都可以 近乎完美(实际上不可能完美)完成。但是在使用的过程中，他们也同样发现，有很多工作是要重复做的。如果要想自动重复的完成这些工作，必须要借助其他的编程语言和工具。微软为了简化这个过程，开发了一个通用的自动化语言，这个就是 VBA(Visual Basic for Application)。所有支持 VBA 的应用程序都可以方便的自动化执行某些固定的步骤，除此以外，支持VBA 的应用程序之间也可以通过 VBA 这个平台进行互操作

2、。由于这里讨论的是Excel 中的 VBA，所以我后面的总结内容都是以 Excel 中的 VBA 为主。VBA 是 VB 的一个子集，它们之间有些地方是不同的：1)、VB程序可以独立的部署和运行，但是VBA程序不能离开宿主程序运行。2)、VB程序是一个编译型的语言，程序需要编译后执行，而VBA程序是解释执行的脚本语言。通过 VBA 这个工具，Excel 就可以完成许多自动化的任务，并且可以充分利用Office 其它组件的功能。2.VBA 能做什么？知道了 VBA 是什么东东后，那么它能干什么呢？确定的说，VBA 基本能做一切 Excel 能做的事，比如打印，生成报表，分析数据，生成图表等，这是

3、VBA 最大的优点。不需要额外实现这些标准的功能，只需要简单的调用就可以了，Excel 已经做好了一切准备。除了这个好处，VBA 其实还能处理很多的任务：1）、自定义 Excel的外观，菜单，工具栏等。2）、重复执行自动化操作。3）、操作文件和文件夹4）、访问数据库并执行相关操作5）、访问网络6）、操作 XML7）、获取系统信息8）、操作和自动化Office 其它组件(支持 VBA的其他公司的产品也可以操作)除了上述的任务外，VBA 还有很多其它的功能有待发掘。3.VBA 与 MacroMacro 是一组 Excel 能理解并执行的命令集合。借助宏录制器，我们能得到这些宏命令翻译后的 VBA

4、代码。这是最简洁获得VBA 代码的方式，也是 VBA 开发最主要的模式。4.VBA 小结2 VBA 的语法很简单，这里就不再详述了。我个人学习新语言的习惯都是，先了解一下这个语言出现的背景，然后了解一下语言改进的地方，最后是实践一下基本的语法，研究语言运行的机制和部署情况。经过这个步骤以后，我就做几个实际小例子巩固巩固。我学习VBA 的小结如下：Module 是 VBA 组织代码的最小单元。VBA 是不分大小写的，所以更要养成良好的编程习惯。代码可以一行写多句，用:隔开，也可以一句写在多行，行末用_标识。Module 成员的访问限定：Public:当前程序中的所有模块都可以访问该成员，如果方法

5、默认不加限定符的话，当Public 处理；Public 成员只能在 Module 中定义。Dim/Private:只有本 Module 内部的所有方法才能访问这些成员。当然方法是不能用Dim 定义的。Friend：只能用于对象模块或者窗体模块，作用范围是当前程序中的其他对象模块可以访问该成员。Const 定义恒定变量的时候，只能在Module 中定义，不能在方法内定义；前面可以加Public/Private 限定。有一点比较恶心，对象赋值用Set.=，其余的一切赋值用=，包括对象的属性赋值。可以使用 Type 在模块级别中定义包含一个或多个元素的用户自定义的数据类型。根据使用情况(比如需要的存

6、储大小)选用合适的变量类型，一般都应该明确定义变量类型，不要使用默认的 Variant 类型。当需要使用变长集合的时候，可以考虑使用动态数组(使用 ReDim 和 Preserve)。Nothing、Empty 与 Null 的比较：Nothing：这是一个指向空对象的对象引用。将对象引用设置为Nothing，就释放了那个对象。如果没有其他的引用指向对象，VB/VBA 就将销毁这个对象。可以使用“Obj is Nothing”的方式检查。Empty：这是一个象 Integer 或者 String 一样的变量类型，它表示了一个还没有进行初始化的变量。它与 Null 的意义不同，Null 表示没有

7、合法数据。例如数组，集合刚定义，还没有赋值之前就是这个状态，可以使用内置方法IsEmpty 检查。Null：这是一个象 Integer 或者 String 一样的变量类型，它表示一个没有合法数据的变量。这有别于 zero、Nothing、Empty 或者 vbNullString。Null 参与的运算，都将产生Null 结果。可以用内置方法 IsNull 检查。使用For Each 语句枚举集合成员。使用For语句执行固定次数的循环。使用Do While/Until.Loop或者While.Wend语句执行不定次数的循环。3 使用If 语句执行 2 分支的选择。使用Switch Case 语句

8、执行 n 分支的选择。使用With语句减少重复对象的书写。在 Module 开始的时候，加上 Option Explicit可以强制变量使用前必须声明。类型定义的简短写法：例如定义整形，可简写为：Dim i%Integer%Long&Single!Double#Currency String$String*size$方法调用可以加括号，也可以不加括号；如果要是加括号，特别是含有多个参数的时候，则前面需要加上 Call。方法是支持可选参数的，参数前用Optional 标识并用=提供默认值。可选参数用参数名:=值的方式传值特别方便。连接多个变量时，尽量使用强制连接符&，少使用混和连接符+。在合适的

9、时候，尽量多使用位操作(XOR,OR,AND,NOT)等完成多个 Boolean 类型的相关判断与操作，而不是直接使用IF判断。最重要的两点：当你不知道如何下手的时候，请尝试录一个宏看看；批判吸收多个人的知识，并不是所有写出来的都是对的，实践是检验真理的唯一标准。4、简论集合VBA 的对象是有很多共性的，抛开它们所在层次的不同，它们的很多操作特征是一样的，比如它们都包括子对象集合，都包括某些激活操作，删除操作等等。像简单的Activate，Delete，Cut 类似的操作我就不详细说了。下面我先总结一下这些共性中出镜率最多的类型：Collection，然后分析一下与之类似的两种常用类型，总结它

10、们的不同。选择集合的时候，要充分考虑它们的特性和实际的问题，采用合适的集合解决问题。1.集合定义集合的方法：Dim col as New Collection Add：往集合中添加一个元素，需要提供添加到集合中的元素，也可以提供一些其它可选的参数，比如键值、位置等。Remove：移除集合中的一个元素，需要提供该元素的Index值。4 Count功能：返回集合中元素的数目。Item功能：获取集合中的一个元素，由于 Item是集合的默认属性，所以可以直接拿集合对象获取元素。参数：提供Index值(基本都是从1 开始)，Name都是可以的(使用 Name时要注意，必须给元素的Name赋过值)。例如：

11、Workbooks(1)，Worksheets(“Name”)，Workbooks.Item(1)是等价的。小例子如下：Sub TestCollection()Dim col AsNew Collection With col .Add(0).Add(1).Add(2)EndWith col.Remove(1)MsgBox col.Count Dim i As Variant ForEach i In col MsgBox i NextDim j AsIntegerFor j=1To col.Count MsgBox col.Item(j)NextEnd SubVBA 的很多对象中都包括集合，

12、但是这些集合中的对应方法已经做出了修改，使用时要注意参数和返回值。例如Workbooks集合 Add 方法就返回新添加的Workbook；Worksheets集合 Add方法返回新添加的 Worksheet。基本上内置对象的集合属性(Application.Workbooks,Workbook.Worksheets等等)，Add方法都是用于添加一个新的成员并返回这个成员，你可以使用这个引用继续完成相关的操作。集合的索引值都是从1 开始的。集合中的元素并不要求类型一样，但是为了方便处理，通常要求是一样的。可以用 With减少代码输入。可以用 For Each 遍历集合，但是VBA 要求元素必须定

13、义成Variant、Object或者自定义的Class类型；而使用For 和 Item枚举也很方便。5 2.数组看完了集合，我们再看看数组与集合的对比。数组其实也应该算是一个 集合，那么它与集合有什么不同呢？先来看看数组的特性：数组是一个数据表，数组的元素类型通常都是一致的(别忘了 Variant，Object)，在定义时声明。数组的下界默认为0，可以通过在模块开头加上“Option Base 1”强行改为1；当然也可以在定义时指定。数组可以通过ReDim和 Preserve改变大小。数组可以使用Erase重新初始化固定大小数组中的元素(如清 0，全部设为空串，Variant类型全部设为 Em

14、pty)，或者释放动态数组的空间。数组支持多维的。数组是快速，高效的数据集合(内存中顺序存贮的，所以每个元素访问都很快)。数组通常用于存储单元格的值，处理完毕后再返回给单元格。从上面其实已经看到了它与集合最主要的不同了：元素类型，长度，下界，维度，效率。既然说到数组，那我也总结一下数组常用的方式和函数：赋值的方式对于直接定义成数组的变量，循环赋值就可以了，但是除了这种方式，还有其它的方式动态生成数组，那就是可以把变量定义成Variant类型的，然后用这几种方式动态生成数组:arr=Array(1,2,3)，arr=A1:A10，或者 arr=Split(a,b,c,)。Split函数生成的数组

15、下标始终从0 开始，即使是使用了Optio n Base 强制从 1 开始了也如此。Array函数生成的数组的下标是从Optio n Base 定义的下标开始。直接使用单元格赋值这种方式生成的数组的下标是从1 开始的，即使是使用了Option Base 强制从0开始了也如此。数组生成字符串使用 Join 函数就可以了:s=Join(Array(a,b,c),)；如果数组类型不是String，则需要先转换一下。获取上下界使用 UBound(arr),LBound(arr)获得上下界。筛选数据6 使用 Filter(arr,match,include,compare)方法获得符合条件的数据，后面两

16、个参数并不是必须的。下面的小例子返回“不”包含“A“字符串的结果“D E”：Sub testArr()Dim a1 As Variant Dim i AsIntegera1=Array(A,BA,CA,D,E)a1=Filter(a1,A,False)MsgBoxJoin(a1)End SubFilter过滤是模糊过滤，注意这个特点。如果需要精确过滤，需要自己写方法解决。排序可以利用内置的Large，Small方法实现数值类型的排序。非数值类型，可以使用Worksheet中的 Rank 辅助排序，也可以自己写冒泡，快速，选择，希尔，桶排序等算法。Sub testArr()Dim a(1To5)

17、AsIntegerDim b(1To5)AsStringDim i AsInteger a(1)=111 a(2)=121 a(3)=234 a(4)=11 a(5)=9 For i=1To5 b(i)=Application.WorksheetFunction.Large(a,i)NextMsgBoxJoin(b,)End Sub经典排序的实现可参看：http:/ 取得最大值最小值使用 WorksheetFunction内置的最大，最小值函数即可。MsgBox Application.WorksheetFunction.Max(arr)数组置换使用内置的WorksheetFunction.T

18、ranspose函数。PublicSub TestTranspose()Dim d(1To3)AsStringd(1)=a7 d(2)=bd(3)=ca1.Resize(1,UBound(d)=d d1.Resize(UBound(d),1)=WorksheetFunction.Transpose(d)End SubTranspose方法用于将一组单元格的行列置换，输入的参数要求是数组格式的。Transpose方法可以用于将数组的数组(数组的元素是数组，要求元素数组长度一致)转换成二维数组。Transpose方法返回的类型为Variant，返回的数组的下界始终为1，即使是使用了Option B

19、ase 强制定义为其它值也如此。3.字典在很多 Collection 的不方便处理的情况下，字典常常作为扩展手段来使用。字典的含义就不啰嗦了，下面总结一下字典。字典的创建方式：方式一：“后期绑定”，定义一个Object变量，直接赋值-Set d=CreateObject(Scripting.Dictionary)。方式二：“前期绑定”，加入 c:windowssystem32scrrun.dll 引用，然后明确创建类型-Dim d as New Dictionary 字典的四个属性:CompareMode-决定 key的比较方法，BinaryCompare(默认)是采用二进制方式比较：即a,A

20、 是不同字符；TextCompare采用文本方式比较：即a,A 是相同字符。其它的比较方式可以参考相关的文档。Count-计算字典中的条目数量，返回Long值。Key-键值，通常是整数或字符串；可以是除数组外的任何类型；Key 具有唯一性。Item-可以是任何对象(不含自定义数据)。字典的六个方法:Add-向字典添加内容(添加的内容是对象时注意不要忘记使用Set)。Exists-判断 keys中有没有要找的key，返回 true 或 false。Keys-返回字典的Key 数组，数组下标始终从0 开始，无视Option Base强制定义的值。Items-返回字典的Item数组，数组下标始终从0

21、 开始，无视Option Base 强制定义的值。Remove-按照 key从字典中删除一个项目。8 RemoveAll-清空字典。简单的看一个小例子：PublicSub TestDic()Dim d AsObjectSet d=CreateObject(Scripting.Dictionary)d(A)=1 d(B)=2 d(C)=3Change Key d.Key(A)=DChange Value d(D)=5MsgBox d(D)MsgBox d.Count End SubVBA 中字典具有以下的优点：添加、删除元素的速度比Collection要快，避免了大量的循环与逻辑判断，可使用的方

22、法也比较多、比较方便。字典的使用也基本上是围绕这些优点展开的，比如：利用Key 的唯一性，快速去除重复的数据，通常配合Transpose方法可以完成许多这种任务。关于字典的知识，可以参看牛人的详细解释和常用示例：http:/ VBA 与 Excel内置的函数，能够使我们处理起任务来事半功倍。这些函数不仅使用方便，而且效率一般都比较高(有些是例外的，特别是某些工作表函数)，比我们自己写的要高效的多。1.VBA内置的函数VBA 内置函数是VBA 种可以直接使用的函数，很多处理函数也相当有用。调用方式：直接使用函数，或者使用VBA 调用。例如Shell()或者 VBA.Shell()。VBA 内置的

23、函数主要涉及以下几类：测试类函数：IsNumeric(x)-是否为数字,返回 Boolean结果。IsDate(x)-是否是日期,返回 Boolean结果。IsEmpty（x）-是否为Empty,返回 Boolean结果。IsArray(x)-指出变量是否为一个数组。IsError(expression)-指出表达式是否为一个错误值。IsNull(expression)-指出表达式是否不包含任何有效数据(Null)。IsObject(identifier)-指出标识符是否表示对象变量。数学函数：9 Sin(X)、Cos(X)、Tan(X)、Atan(x)-三角函数，单位为弧度。Log(x)、E

24、xp(x)-返回 x 的自然对数，指数。Abs(x)-返回 x 的绝对值。Int(number)、Fix(number)-都返回参数的整数部分，区别：Int 将-8.4 转换成 -9，而 Fix 将-8.4 转换成 -8。Sgn(number)-返回一个 Variant(Integer)，指出参数的正负号。Sqr(number)-返回一个Double，指定参数的平方根。VarType(varname)-返回一个Integer，指出变量的子类型。Rnd(x)-返回 0-1之间的单精度数据，x 为随机种子。Round(x,y)-把 x 四舍五入得到保留y 位小数的值。字符串函数：Trim(stri

25、ng)、Ltrim(string)、Rtrim(string)-去掉 string左右两端空白，左边的空白，右边的空白。Len(string)-计算 string长度Replace(expression,find,replace)-替换字符串。Left(string,x)、Right(string,x)、Mid(string,start,x)-取 string左/右/指定段 x 个字符组成的字符串Ucase(string)、Lcase(string)-转换字符串为大、小写Space(x)-返回 x 个空白的字符串Asc(string)-返回一个integer，代表字符串中首字母的字符代码Chr

26、(charcode)-返回 string，其中包含有与指定的字符代码相关的字符InStr()-返回一个字符串在另外一个字符串中的位置，返回值为Variant(Long)型。转换函数：CBool(expression)-转换为Boolean型CByte(expression)-转换为Byte型CCur(expression)-转换为 Currency型CDate(expression)-转换为Date型CDbl(expression)-转换为Double型CDec(expression)-转换为Decemal型CInt(expression)-转换为Integer型CLng(expression

27、)-转换为 Long型CSng(expression)-转换为Single型CStr(expression)-转换为String型CVar(expression)-转换为 Variant型Val(string)-转换为数据型Str(number)-转换为 String 10 时间函数：Now、Date、Time-返回一个Variant(Date)，根据计算机系统设置的日期和时间来指定日期和时间。Timer-返回一个Single，代表从午夜开始到现在经过的秒数。TimeSerial(hour,minute,second)-返回一个 Variant(Date)，包含具有具体时、分、秒的时间。Dat

28、eDiff(interval,date1,date2,firstdayofweek,firstweekofyear)-返回Variant(Long)的值，表示两个指定日期间的时间间隔数目。Second(time)-返回一个 Variant(Integer)，其值为 0 到 59 之间的整数，表示一分钟之中的某个秒。Minute(time)-返回一个Variant(Integer)，其值为 0 到 59 之间的整数，表示一小时中的某分钟。Hour(time)-返回一个 Variant(Integer)，其值为 0 到 23 之间的整数，表示一天之中的某一钟点。Day(date)-返回一个Vari

29、ant(Integer)，其值为 1 到 31 之间的整数，表示一个月中的某一日Month(date)-返回一个 Variant(Integer)，其值为 1 到 12 之间的整数，表示一年中的某月。Year(date)-返回 Variant(Integer)，包含表示年份的整数。Weekday(date,firstdayofweek)-返回一个Variant(Integer)，包含一个整数，代表某个日期是星期几。其它常用函数：Shell-运行一个可执行的程序。InputBox-这个太熟悉了，简单输入对话框。这个需要注意与Application.InputBox(更强大，内置容错处理，选择取消

30、后返回false)区分，而这个函数不含有容错处理，而且选择取消后返回空串(零个字节的字符串)。MsgBox-这个更不用说了，简单信息显示对话框，其实也是一种简单的输入手段。Join-连接数组成字符串。Split-拆分字符串成数组。RGB-返回指定R、G、B 分量的颜色数值。Dir-查找文件或者文件夹。IIF(expression,truePart,falsePart)-IF 语句的“简化版本”(比喻，当然并不一样)；expression为true的话返回truePart，否则返回falseParth。Choose(index,choice1,.choiceN)-选择指定Index的表达式，In

31、dex可选范围是1 到选项的总数。Switch(exp1,value1,exp2,value2,.expN,valueN)-从左至右计算每个exp的值，返回首先为true 的表达式对应的value部分。如果所有的exp值都不为true，则返回Null。注意虽然只返回一个部分，但是这里所有的表达式exp1到 expN都是要被计算的，实际使用中要注意这个副作用。部分内容来自下面的连接，感谢楼主的无私奉献。大家入门学学还是很不错的，推荐一下：http:/ 调用方式：Application.WorksheetFunction或者直接WorksheetFunction。例如 Application.Wo

32、rksheetFunction.Max()或者 WorksheetFunction.Max()。VBA 内置的函数是用于处理程序数据的，是为VB 语言服务的，所有VBA 宿主环境都可以使用这些内置的功能。但是对于Worksheet中的对象，似乎这些通用的函数并不能提供最佳的实践。所以针对Sheet，又存在另外一套相关的处理函数，虽然它们与VBA 中的某些函数作用是一样的，但是从“工作表函数”这个名字上就可以看出，对于工作表中的对象的所有操作，比如对单元格求和，求单元格中最大值等，使用工作表函数必将具有先天的优势(当然了，工作表函数基本上都是可以在Excel单元格中直接输入“=”然后就可以使用的

33、)。虽然从实际的操作中，我们可能发现，使用内置的工作表函数并不一定是最快，最高效的，但无疑是最直接，最省事的。这里简单总结一下常用的几类函数。全部的函数说明参见文末的MSDN链接。数学函数类：BesselI(贝塞尔函数)BesselJ BesselK BesselY Power（指数）Log(对数，还有以不同)In(自然对数)Fact(阶乘)FactDouble(半数阶乘，意思就是偶数的只计算偶数阶乘，奇数的只奇数奇数阶乘)PI(圆周率)弦值计算类：Acos Acosh Asin Asinh Atan2 Atanh Cosh Sinh Tanh 数制转换类：Bin2Dec Bin2Hex Bi

34、n2Oct Dec2Bin Dec2Hex Dec2Oct Hex2Bin Hex2Dec Hex2Oct Oct2Bin Oct2Dec Oct2Hex Degrees与 Radians(弧度角度互换).数值处理类：Ceiling(arg1,arg2)-数值舍入处理，把 arg1舍入处理成arg2的最接近的倍数(大于等于传入的参数)。Floor(arg1,arg2)-数值舍入处理，把arg1舍入处理成arg2的最接近的倍数(小于等于传入的参 数)。Round-按指定的位数四舍五入，返回类型是Double。MRound-按指定位数四舍五入，参数是Variant，返回类型是Double.Roun

35、dDown-舍去指定位数后面的小数，总是小于等于传入的参数，其它的基本同Round。RoundUp-舍去指定位数后的小数总是进1，总是大于等于传入的参数，其它的基本同Round。Fixed-按指定的位数四舍五入，返回类型是String，可以指定显示不显示逗号(第三个参数决定，False则显示逗号，True则不显示逗号).Odd-返回比参数大的最接近的奇数。Even-返回比参数大的最接近的偶数。数值运算类：Average AverageIf AverageIfs Max Min Large Small Sum SumIf SumIfs SumProduct SumSq SumX2MY2 SumX

36、2MY2 SumX2PY2 SumXMY2 Count CountA CountBlank CountIf CountIfs Frequency-计算第二个数组的每个元素在第一个数组中出现的次数，返回一个与第二个数组同长的一个数组。一般参数和返回值都是Range。Lcm-计算数值的最小公倍数。Product -返回所有参数的乘积。Quotient-返回两个数整除的值，忽略余数。12 逻辑判断类：And-如果所有参数都为True，则返回True；只要有一个返回False，则返回False。Or-如果所有参数都为False，则返回False；只要有一个返回True,则返回 True。IsErr-检

37、查是不是除了#N/A外的错误值.IsError-检查是不是错误值(#N/A,#VALUE!,#REF!,#DIV/0!,#NUM!,#NAME?,或者#NULL!).IsEven-检查是否是偶数.IsOdd-检查是否是奇数.IsLogical-检查是不是布尔值.IsNA-检查值是否是错误值#N/A（值不可用）。IsNonText-检查是否是非文本(空的单元格返回true)。IsNumber-检查是不是数字。IsText-一般用于判断单元格中内容是否是文本。Delta-判断两个Variant的值是否相等，相等则返回1，否则返回0。数据操作类：Choose-返回第一个参数Index指定的值.与 V

38、BA 内置的函数Choose有类似的功能。Asc-把双字节字符变成单字节字符。Lookup,VLookup,HLookup-查找单元格数组中与给定值相同的值，文本等等。Match-查找并返回单元格数组中与指定值相同的单元格的相对Index值。Find,FindB,Search,SearchB-返回第一个字符串在第二个字符串中的位置(位置是从1 开始的，不是基于 0 的)。Replace,ReplaceB-字符串替换，可以指定开始的位置以替换的字符数，控制更为精细。Substitute-直接进行字符串替换，不需要指定开始位置，可控性差，但是使用简单。Rept-按照指定次数的重复构造字符串并返回。

39、Text-按照一定的格式把值转换成文本。Index-一般用于返回一组单元格中某块区域中某行某列的值。Median-计算一个Double的数值，这个数值将参数分为相同数目两组，一组比这个值大，一组比这个值小。这个值可能正好出现在参数中，也可能不出现在这些参数中。Mode-返回传入的数组，或一组值中出现次数最多的值.Prope-格式化字符串中的每个单词，把首字母转成大写，其它的转成小写。RandBetween-返回介于两个数之间的随机数，返回值为Do uble型。Rank-返回指定的数在一个Range对象值中排过序后的位置(可以用第三个参数指定按降序或升序排，默认是降序)，比如单元格d1 到 d4

40、 的值为(1,4,3,4)，那么 4 的 Rank值就是 1(忽略第三个参数是按降序找第一个匹配，然后返回位置)。Transpose-把一个数组的行列互换，这个方法主要是针对单元格的，所以数组的长度(65535)，和每个元素的长度(255)都有限制。如果这个方法由于这些因素失败了，可以尝试一下这个方案：http:/ 7(Saturday)。WeekNum-返回指定日期是一年中的第几周。基本上以Variant为参数的函数都是可以直接传入单元格的。以 B 结尾的函数代表是推荐使用于双字节的字符语言的，比如汉子，日语等。不以B 结尾的函数代表13 的是推荐使用于单字节字符语言的，例如英语，德语等。对

41、于不同的语言，这 2 个函数返回的结果可能是有差异的。加上前缀“D”的函数是特别针对Range对象或数据库数据的相关数学运算，例如DMax，DMin，DCount，DSum。除了这些常用的函数，工作表函数还包含了相当多的高级数学计算函数，比如矩阵，方差，分布，统计,利率，虚数计算相关的函数，具体需要使用的时候查阅MSDN就可以了。在 MSDN上，很多函数的很多参数是必须的，但是使用的时候，编译器的提示是说这些参数不是必须的，这个时候以编译器为准。谨记，实践是检验真理的唯一标准。基本上，如果参数是需要传入一组数的函数，都可以传入一个数组或单元格。实际学习过程中，我是先了解这些内置的函数能干什么，

42、但并不太关注细节；等实际使用的时候，才会找到需要的函数，匹配实现细节的。全部的函数说明参见下面的MSDN链接：http:/ 或者是http:/ 6、文件系统文件系统是平时任务中用到的相当多的一个方面，所以这里专门给总结一下文件系统方面的操作。数据库文件(例如 Access文件)其实也是一种特殊的文件格式，也可以通过这里介绍的方式处理，但是作为特殊的数据格式文件，VBA 中有特别的方式处理它，这个会在后面数据处理中总结。这里的文件指的就是普通的 Excel文件、文本文件、二进制文件、XML 文件等。在 VBA 中操作文件主要是通过以下几种方式：一、使用Excel中的对象处理文件Excel中代表

43、Excel文件的对象是Workbook，所以操作文件的主要手段也就是利用Workbook或者 Workbooks集合的相关方法。主要的方法总结如下：1、打开文件打开 Excel文件：Workbooks.Open。打开文本文件：Workbooks.OpenText。打开 XML 文件：Workbooks.OpenXML。打开数据库文件：Workbooks.OpenDatabase。注意：使用Open方法也可以打开文本文件，但建议使用OpenText方法。此方法是载入一个文本文件，并将其作为包含单个工作表的工作簿进行分列处理，然后在此工作表中放入经过分列处理的文本文件数据。2、保存文件文件的保存可

44、以通过几种方式实现：使用 Workbook对象的 Save或 SaveAs方法。使用 Application.Dialogs，显示标准的“另存为”对话框。14 使用 Application.GetSaveAsFilename可以调出标准的“另存为”对话框，获取用户文件名，但并不真正保存任何文件，然后可以使用代码保存文件。还有Application.GetOpenFileName也可以调出标准的“打开”对话框，返回用户选中或填写的文件名，但是返回后并不真打开选中文件。3、关闭文件关闭文件可以使用Workbooks集合或 Workbook对象的 Close 方法。前者是关闭所有打开的工作 簿，后者

45、关闭特定的工作簿。总结：利用 Excel 对象的方法进行Excel 文件的操作是最简单，也是最方便的。虽然利用Workbook对象也可以打开其他的一些文件，但是都不是最佳的手段，而且这个对象对文件的操控性很弱，无法实现很多文件系统的操作，所以一般操作文件的时候，都要结合其他的集中手动来共同协作完成任务。二、使用VBA内置的文件处理函数处理文件VBA 内置了很多用于文件操作的语句和函数，可以满足大多数情况下的文件的相关操作。下面我总结一下。（一）文件处理打开文件：Open ForAS For 后面的打开模式不同，决定了后面的内容处理方式也不同，常用的有打开成读入模式，写入模 式，追加模式，二进制

46、模式，随机模式等等。如果要打开二进制文件，则可以选择后两种模式。As 后面可以指定打开后的文件号(1 到 511)，指定了文件号以后，VBA几乎所有内置的文件处理函数就都使用这个文件号处理文件。一般可以用FreeFile函数获得没有使用的文件号。以 Open语句打开文件，并不是我们通常的双击一个文件打开显示到屏幕上，而是将其存放在磁盘上的数据读入到缓冲区，不是可视化的打开。这种打开是不需要密码的，即使你的Excel文件设置了打开密码，还是照打开不误，这也正是Excel文件不安全的根源所在。辅助函数：FreeFile函数：获得没有使用的文件号Width函数：设置文档中每行的宽度(0255)。如果

47、 width 等于 0，则行的长度不受限制。width 的缺省值为 0。关闭文件：Close，Reset 说明：打开文件后，必须在使用完后关闭文件，这里的文件名可以传入打开时指定的文件号。如果使用Close 语句，但是关闭的文件名省略，则会关闭所有使用Open语句打开的文件。使用Reset函数会关闭所有 Open语句打开的文件，并将文件缓冲区的数据全部写入磁盘。重命名文件：Name 拷贝文件：FileCopy 移动文件：Name函数也可以，修改全路径即可。判断文件是否存在：Dir 删除文件:Kill 读取文件内容读取文件的内容通常需要的辅助函数(一般用于标识读取的位置)：EOF 函数：EOF(

48、filenumber)功能：返回一个Integer，它包含 Boolean 值 True，表明已经到达为 Random 或顺序Input 打开的文件的结尾。15 LOF 函数：LOF(filenumber)功能：返回一个Long，表示用 Open 语句打开的文件的大小，该大小以字节为单位。Loc 函数：LOc(filenumber)功能：返回一个Long，在已打开的文件中指定当前读/写位置。Seek#语句功能：可以用Seek 语句指定Get/Input语句的读取位置；一般结合使用LOF 函数，使用循环读取所有数据。Seek函数功能：返回一个Long值，在用Open 语句打开的文件中指定当前的读

49、/写位置。说明：在使用Get 语句读取文件时，必须用LOF 函数来判断是否到达文件末尾，而不是用EOF函数。可以使用Seek函数判断当前位置，然后与LOF 的值比较。例如：Do While Seek(1)OLE DB DAO RDO ADO；从处于的层次看(非绝对，部分有交叉)从底层到高层依次是：ODBCOLE DBRDO、DAO、ADO。从作用上来说，都是MDAC（Microsoft Data Access Components）微软数据库访问组件的成员。下面简单总结一下各种访问方式：1、ODBC ODBC(全称 Open Database Connectivity)在底层建立了一组数据访问

50、规范（标准），并提供一组对数据库访问的标准API 函数接口。应用程序可以通过ODBC访问用户具有ODBC驱动程序的任何本地28 或远程数据源，一般是SQL Server、Oracle等关系数据库，不能访问非关系型数据库。它最接近底层，执行效率最高，但是开发效率比较低。2、OLEDB OLE DB是基于 COM 技术的一组接口规范，是系统级数据访问接口，OLE DB 提供通用数据访问的方式，不管数据是以何种形式存储，可以对电子邮件、文本文件、复合文件、数据表等各种各样的数据通过统一的接口进行存取的一种技术。可以用来访问各种关系型或者非关系型数据源，如ORACLE、SQL Server、Acces

51、s、Excel等等。它比较接近底层，执行效率比较高，开发效率也有一定提升。3、DAO DAO(全称 Data Access Objects)是基于MICROSOFT的 JET 技术设计的面向对象的数据访问接口，最早在VB 中使用，DAO 可以访问的数据库有早期的Access、FoxPro、dBASE、Paradox、Excel和 Lotus WK 等数据库。4、RDO RDO(全称 Remote Data Objects，远程数据对象访问)是以 ODBC 为基础，依赖ODBC API、选定的 ODBC 驱动程序以及后端数据库引擎实现大部分的功能，RDO 具备基本的ODBC 处理方法，所以可以直

52、接执行大多数ODBC API 函数。RDO 是从 DAO 派生的，同 DAO 最大的不同在于其数据库处理模式。DAO 是针对 Records和 Fields，而 RDO 是按照Rows和 Columns来处理。此外DAO 是访问Access的 Jet 引擎（Jet 是 ISAM）的接口，而 RDO 则是访问ODBC 的接口。可见，RDO 是综合了DAO/Jet、ODBC 等优点。5、ADO ADO(全称 ActiveX Data Objects)是基于 OLE DB(COM技术)技术设计的应用层数据访问接口，对 OLE DB 接口进行封装，所以使用中仍然是通过OLE DB 桥接数据库。所以，A

53、DO 同 OEL DB一样提供了通用数据访问功能，支持关系数据库和非关系数据库的访问。ADO 功能上对DAO 和 RDO 进行了批判吸收，达到了效率与开发的折中。各种数据访问技术综合对比如下：-访问接口易用性运行性能可扩展性突出特点能否访问非关系数据源技术层次-ODBC 差最高差可进行底层控制否底层-OLE DB 较差较高好可访问非关系DB 能底层-DAO 好较高一般通用标准，应用广泛否高层-RDO 好较高一般访问 JET 性能最好能高层-ADO 最好高好优秀执行效率与开发效率能高层-综上所述，ADO是平衡了了执行效率与开发效率，综合了各种数据访问方式优点的数据访问方式；同时也是微软推荐的数据

54、访问方式。29 9、通用ADO数据访问模型ADO 是基于 OLE DB 的数据访问技术。它不直接与数据交互，而是把这个任务交给了OLE DB，这么做带来了相当好的可扩展性和适应性。它提供了编程语言和统一数据访问方式；它允许开发人员编写访问数据的代码而不用关心数据库是如何实现的，而只用关心数据库的连接。由于 ADO 是基于COM 实现的，所以基本上任何语言都可以使用这种数据访问技术，VBA 也不例外。一、ADO对象模型ADO 对象库中主要有9 个对象，即：Connection、Command、Recordset、Record、Field、Error、Property、Parameter和 Str

55、eam。呈现的形式基本是5 大可以独立创建的基本对象，4 大对象集合，如下图所示。这 9 个对象中以Connection、Command、Recordset是最为常用的，很多情况下只需要这三个对象即可完成数据的读取和操作，对于一些很简单的应用，甚至使用它们中的任何一个就可以独立完成任务。下面是各个对象/集合的简略说明：Connection：代表与数据源的连接与操作环境，基本任何的操作都是针对特定的Connection完 成 的。常用属性：ConnectionString(最重要)，ConnectionTimeout，State(连接的状态)，Errors 常用方法：Open，Execute，C

56、lose 常用事件：ExecuteComplete，ConnectComplete 虽然 Command对象与Recordset对象都可以在需要的时候自己隐式的创建一个Connection对象，但是对于需要多次执行命令或查询的场景，还是需要提供一个公共的Connection对象(这个对象的创建与销毁都是需要时间的)来共用。而且对于同一个连接字符串，ADO 会采用连接池(存放 Connection对象)的方式减少资源的浪费。ConnectionString中主要需要设置Provider，Data Source，Initial Catalog，User ID，Password，Integrated

57、 Security等值，这些大家应该都很熟悉了。当然有些值(像 Provider，Mode这种)也可以在 Connection 的相关属性中设置。Connection需要先执行Open方法打开后，才能Execute一些命令，最后需要使用Close关闭(通常为了保险起见，需要先检查State，再关闭)以释放资源。Command：代表执行的添加、修改，删除、查询数据源的命令。常用属性：ActiveConnection(设置连接字符串)，CommandText(核心属性)，CommandType，Parameters(一般是存储过程的参数)30 常用方法：CreateParameter，Execut

58、e(可以有返回值)。Recordset：代表执行查询命令后的结果集。常用属性：ActiveConnection(设置连接字符串)，BOF，EOF，CursorLocation，Filter，Sort，State，Fields(返回数据的类型)常用方法：Open，Close，Move，MoveFirst，MoveNext，Find，NextRecordset 通常配合Range对象的 CopyFromRecordset方法获取数据，但是这个方法只能获得值，一般需要匹配 Fields属性获取列信息。如果需要精确控制每行每列的值，可以使用RecordCount，Fields以循环的形式获取结果集中的

59、每个值。For i=1To rst.RecordCount For j=0To rst.Fields.Count-1 Sheet1.Cells(i+1,j+1)=rst.Fields(j)Next j rst.MoveNext Next i 如果返回的结果集为空，则BOF 与 EOF 都为 True。通常它们也可以配合Fields精确控制结果集中的每个值。Find 方法基本上是支持与SQL 中 Where语句基本相同的语法。Error与 Errors：代表与数据源相关的操作的详细错误信息，Errors是 Connection对象的属性。Parameter与 Parameters：代表基于参数化

60、查询或存储过程的 Command 对象相关联的参数或自变量，Parameters是 Command对象的属性。Field与 Fields：代表使用普通数据类型的数据的列，Fields是 Recordset对象的属性。Property与 Properties：代表 ADO 对象的描述或控制对象的行为，分为内置属性(通过对象直接调用)和动态属性(通过集合使用 MyObject.Properties(0)或 MyObject.Properties(Name)语法来引用)。Record：代表记录集中的一行、或文件系统的一个文件或一个目录。Stream：用于读写以及处理二进制数据或文本流。二、ADO编程

61、模型ADO 的目标是访问、编辑和更新数据源，而编程模型体现了为完成该目标所必需的系列动作的顺序。ADO 提供类和对象完成以下活动：连接到数据源(Connection对象)，并可选择开始一个事务。可选择创建对象来表示SQL 命令 (Command)。可选择在SQL 命令中指定列、表和值作为变量参数(Parameter)。执行命令 (使用 Command、Connection 或 Recordset对象完成)。31 如果命令按行返回，则将行存储在缓存中(使用 Recordset对象)。可选择创建缓存视图，以便能对数据进行排序、筛选和定位(使用 Recordset对象)。通过添加、删除或更改行和列编

62、辑数据(使用 Recordset对象)。在适当情况下，使用缓存中的更改内容来更新数据源(使用 Recordset对象)。如果使用了事务，则可以接受或拒绝在完成事务期间所作的更改；结束事务 (Connection)。释放相关对象(通常是把对象设为Nothing，别忘了Set 关键字)。不管是否显式的使用了Connection对象，这个对象在整个访问数据库的过程中，是始终存在的。其实除了连接字符串以及数据库特有的一些特性(比如 SqlServer支持存储过程)外，访问各种数据库的基本流程和处理的语句，包括SQL 语句都是差不多的。三、ADO实践ADO对象创建对于 COM 对象的创建方式，大家应该很

63、熟悉了，有两种方式：后期绑定：使用CreateObject方法。Dim cnn AsObject,rst AsObjectSet cnn=CreateObject(ADODB.Connection)Set rst=CreateObject(ADODB.Recordset)前期绑定：先引用“Microsoft ActiveX Data Objects 2.x Library”(尽量选择高版本)，然后直接就可以使用了。下面两种写法都可以：Dim cnn1 As ADODB.Connection Set cnn1=New ADODB.Connection Dim cnn2 AsNew ADODB.C

64、onnection 前期绑定能更好的利用VBE(或者说是VBIDE)的 Intellisense。使用 ADO 访问各种类型的数据库，基本上除了连接字符串不同，专有特性不同，使用ADO 的其它过程基本都差不多，所以下面几种类型数据文件的操作，重点都是介绍各自不同的地方。使用 ADO访问 Access中的数据连接字符串示例：Provider和 Data Source是必须的Mode控制访问数据库方式，本例中是排他访问PublicConst AccessConnection AsString=_ Provider=Microsoft.ACE.OLEDB.12.0;&_ Data Source=C:

65、FilesNorthwind 2007.accdb;&_ Mode=Share Exclusive;&_ 32 User ID=Admin;&_ Password=password 在本例中，使用的是Access 2007，所以使用的Provider是“Microsoft.ACE.OLEDB.12.0”。下面是一个伪代码例子，基本上涵盖了所有的步骤：PublicSub PlainTextQuery()省去了定义的部分 sConnect=Provider=Microsoft.ACE.OLEDB.12.0;&_ Data Source=C:FilesNorthwind 2007.accdb sSQ

66、L=SELECT.Set rsData=New ADODB.Recordset rsData.Open sSQL,sConnect,adOpenForwardOnly,adLockReadOnly,adCmdText IfNot rsData.EOF Then Sheet1.Range(A2).CopyFromRecordset rsData rsData.Close With Sheet1.Range(A1:B1).Value=Array(Company,Contact Name).Font.Bold=TrueEndWith 调整列宽 Sheet1.UsedRange.EntireColumn.AutoFit Else rsData.Close MsgBox Error:No records returned.,vbCritical EndIf 销毁对象Set rsData=NothingEnd Sub像上面所说那样，解决一个问题，可以使用不同的对象。这里仅使用Recordset就解决了所有的问题，但是当需要使用Connection或者 Command对象的时候，毫不犹豫的使用它们。