《ExcelVBA知识经典学习》由会员分享，可在线阅读，更多相关《ExcelVBA知识经典学习（62页珍藏版）》请在金锄头文库上搜索。
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）、操作 XML 7）、获取系统信息 8）、操作和自动化Office 其它组件 (支持 VBA的其他公司的产品也可以操作)除了上述的任务外， VBA 还有很多其它的功能有待发掘。3. VBA 与 MacroMacro 是一组 Excel 能理解并执行的命令集合。 借助
4、宏录制器， 我们能得到这些宏命令翻译后的 VBA 代码。这是最简洁获得VBA 代码的方式，也是 VBA 开发最主要的模式。4. VBA 小结2 VBA 的语法很简单，这里就不再详述了。我个人学习新语言的习惯都是，先了解一下这个语言出现的背景，然后了解一下语言改进的地方，最后是实践一下基本的语法，研究语言运行的机制和部署情况。经过这个步骤以后，我就做几个实际小例子巩固巩固。我学习VBA 的小结如下：Module 是 VBA 组织代码的最小单元。VBA 是不分大小写的，所以更要养成良好的编程习惯。代码可以一行写多句，用 “:“隔开，也可以一句写在多行，行末用“_“标识。Module 成员的访问限定
5、：Public: 当前程序中的所有模块都可以访问该成员，如果方法默认不加限定符的话，当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 或者 Strin
7、g 一样的变量类型， 它表示了一个还没有进行初始化的变量。它与 Null 的意义不同， Null 表示没有合法数据。例如数组，集合刚定义，还没有赋值之前就是这个状态，可以使用内置方法IsEmpty 检查。Null：这是一个象 Integer 或者 String 一样的变量类型，它表示一个没有合法数据的变量。这有别于 zero、Nothing 、Empty 或者 vbNullString 。Null 参与的运算，都将产生Null 结果。可以用内置方法 IsNull 检查。使用“For Each“ 语句枚举集合成员。使用“For“语句执行固定次数的循环。使用“Do While/Until.Loop
8、“或者“While.Wend“语句执行不定次数的循环。3 使用“If“ 语句执行 2 分支的选择。使用“Switch Case“ 语句执行 n 分支的选择。使用“With“语句减少重复对象的书写。在 Module 开始的时候，加上 “Option Explicit“可以强制变量使用前必须声明。类型定义的简短写法：例如定义整形，可简写为：Dim i% Integer % Long “ “ “注意访问Access与 Excel的连接字符串中，Provider是相同的；但是Excel 需要多加上Extended Properties，这里的版本号随着Excel的版本不同而不同。使用的 SQL 语句与
9、 Access中基本相同，除了以下几点： 使用 Sheet名字作为表名的时候需要加$ 符号和方括号 如果 Sheet名字中有空格的，名字两边可以用单引号括起来，例如：My Sheet$ sSQL1 = “SELECT * FROM Sheet1$“ 如果表名是Sheet级别的名字，可以把这个表名接在$符号后面sSQL2 = “SELECT * FROM Sheet1$SheetLevelName;“ 如果表名是一个Range 范围，也直接加在$符号后面sSQL3 = “SELECT * FROM Sales$A1:E89;“36 如果表名是一个Workbook级别的名字，可以直接使用sSQL4
10、 = “SELECT * FROM BookLevelName;“注意了， OLE DB 默认认为Worksheet中的第一行是列名，而不是数据。如果不是这种情况，那么需要告诉OLE DB ，第一行也是数据，这个时候需要在连接字符串中的Extended Properties中加上 “HDR=No” 。如下列所示：sConnect = “Provider=Microsoft.ACE.OLEDB.12.0;“ “ HDR=No“;“ 注意上面的引号数目使用 ADO访问 Text中的数据对于含有大量格式化的文本数据，例如csv 文件，使用ADO 简直就是如鱼得水，你能充分享受SQL 带来的强大便捷性
11、。连接字符串示例：sConnect = “Provider=Microsoft.ACE.OLEDB.12.0;“ “ “注意了， Provider与访问Excel是一样的，不同的是，为了方便处理多个这样的文件，连接字符串中不需要提供文件名字；而在SQL 语句中的表名，使用的是文件名。如下面的例子：sSQL = “SELECT * FROM Sales.csv WHERE Type=Art;“同样的， OLE DB 默认认为文件中的第一行是列名，而不是数据。如果不是这种情况，那么需要告诉OLE DB ，第一行也是数据，这个时候需要在连接字符串中的Extended Properties中加上 “
12、HDR=No”，参考上面的设置。到这里，基本的ADO 操作已经总结完了，但是ADO 的功能绝对不仅仅如此，它还包括另外一大块内容，那就是RDS 编程模型。 RDS 模型解决的是网络数据访问问题，这个在Excel中比较少见，这里就不重点介绍了，有兴趣的可以自己琢磨琢磨看。10 、多行多列数据展示对于多行多列数据，基本使用Worksheet中的 Range就可以处理了。但是在UserForm类型的应用中，也时常需要处理多行多列的数据。在UserForm中，显示和处理多列数据，通常可以有的选择有以下几种： DataGrid，ListView，ListBox。前面两类相当强大，但是也比较复杂，一般的简
13、单应用中用不到。DataGridDataGrid控件天生就是为了处理像Recordset这种数据而准备的，与它类似的数据控件( 其它公司提供的数据控件)也还有其它几种，这里并不是重点，所以简单介绍一下。默认情况下，VBE 中是看不到这个控件的，需要下载DATGDCHS.dll并放到下，然后运行命令“regsvr32 DATGD37 CHS.dll”注册。注册完成后，选择“Tools“菜单下的 “Additional Controls.“ 菜单项，添加对应的控件到工具箱中就可以了。DataGrid最重要的属性就是DataSource属性，就是设置表格的数据源。其它的也可以设置列的一些属性，这里就
14、不详细介绍了。需要的同学请自备Google并自行搜索。ListViewListView也是处理这类型数据的一个选择，功能也比较强大，特别是可以设置图标，在很多时候还是很适合使用的。下面的伪代码介绍了常用的属性和方法：Dim currentItem As ListItem 清除 listview1中的标题ListView1.ColumnHeaders.Clear 清除 listview1中的内容ListView1.ListItems.Clear With Sheets(1) 添加列标题ListView1.ColumnHeaders.Add 1, , .Cells(1, 1), ListView1
15、.Width / 9ListView1.ColumnHeaders.Add 2, , .Cells(1, 2), ListView1.Width / 9 显示为报表视图ListView1.View = lvwReport 选取整行ListView1.FullRowSelect = True 复选框ListView1.CheckBoxes = TrueListView1.BackColor = RGB( 255 , 199 , 9) For i = 5To .A65536.End(xlUp).Row 为 listview1控件里面的行添加内容Set currentItem = ListView1
16、.ListItems.Add() currentItem.Text = .Cells(i, 1) 为行标赋值currentItem.SubItems(1) = .Cells(i, 2) currentItem.SubItems(2) = .Cells(i, 3) currentItem.SubItems(3) = .Cells(i, 4) 为里面的第三列调整相应的字体大小及颜色With currentItem.ListSubItems.Item(2) If .Text -1 Then ListBox1.RemoveItem ListBox1.ListIndex End If 清空 ListBox ListBox1.Clear 任务 3 ：获取 ListBox中行列的总数ListCount - 行总数，遍历的时候很有用。ColumnCount - 获取或设置列的数目。需要使用多列的时候，一般需要先设置ColumnCount为期望的数目。任务 4 ：获
