Excel 与VBA 程序设计
标题： Excel 与VBA 程序设计 版本： mini 作者： 马维峰
Email： mweifeng@gmail.com Blog： http://maweifeng.cnblogs.com 时间： 2005-6-20 － 2006-3-31 序 I
Excel 与VBA 程序设计 Copyright ? 2005 马维峰
序
笔者大概从 98 年开始学习VB，从喜欢到失望，从失望到欣喜，从欣喜到平淡，大概 是很多自己一样的程序员的学习心路。而对于Office 的强大功能，对于VBA，领会的却很 晚。例如Excel，虽然也一直知道其功能很强大，但到底如何强大，有什么有别于其他同 类软件的特色，却不甚清楚。大概在2003 年，应工作需要，仔细查阅了一些Excel 的资料， 开始学习Excel 数据处理和VBA 开发。因为当时已是一个熟练的VB 程序员，所以VBA 语法并不是难点和重点，而在很多书中没有很清楚讲解的问题，例如一个工作表内的某些 数据如何获得，某个或某几个单元格的值怎么高效的获取和赋值，如何打开关闭Excel 文 件，如何正确部署最后的程序，如何绘制复杂的图表，等等诸如此类的问题，反而经常会 困惑自己很久。因此，本书将以笔者的学习经验为依据，以一个程序员的角度，讲解Excel VBA 开发的种种问题，并对一些笔者在实际中遇到的大多数Excel VBA 开发的书中较少涉 及的内容作深入探讨，对于一些设计问题、效率问题、程序风格，书中都会给出笔者的建 议。
书中关于运行效率的说明，都经过笔者亲自测试，具体测试方法在运行效率一节有详 细说明。书中包括的代码风格之类的部分观点只是个人喜好问题，特此说明，读者可以根 据自己的判断取舍。写作过程中，有时会觉得有太多的问题需要说明，却限于篇幅，不能 一一展开；有时又不知如何下笔，不知如何才可以清楚简洁的讲清楚一个问题。对于很多 自己的经验或者教训，只能在合适的时候插入只言片语，古人言“中有苦心而不能显”，“中 有调剂而人不知”，大概如是。
书中的代码、例子和文字是紧密配合的，没有了这些内容，也就失去了全书的灵魂所 在。很多的说明文字必须通过代码来体会，这是笔者很多年来自己的体会。
本书不求对 Excel 和VBA 面面俱到的介绍，而且这也是不可能的。从内容选择和取舍 来说，本书更注重实用，从笔者的经验出发，从应用的角度来介绍Excel VBA 的内容，而 不是相反。
目前写一本关于 VBA 的书好像有些不合时宜，毕竟VBA 和VB 一样，是属于“落后” 和“过时”的技术，VB 6 的使用者，如笔者，大多已经转移到.net 平台之下，那么，VBA 的命运如何，我们还不得而知？但至少，在很长的一段时间内，作为Office 开发的方式，
序 II
Excel 与VBA 程序设计 Copyright ? 2005 马维峰
VBA 和VSTO 应该会共存，而对于非专业程序员，首选应该还是VBA，此为其一。其二， VBA 开发的核心在于Office 的对象模型的掌握，而这也是本书的重点之所在。
本书的读者应该可以较熟练的使用Excel，例如可以使用公式，可以自定义公式；熟
悉基本的Excel 的概念和名词，例如宏、加载宏。对于基本没有程序设计经验的读者，第 二章比较系统的介绍VBA 语法和集成开发环境（IDE），对于熟悉VBA 或VB 的读者，可 以略过这一章。其实对于Office 系列的开发，语法只是很小的一部分，主要的难点和问题 在于相应的对象模型及其应用，所以书中的大多数内容其实只是围绕Excel 对象模型的解 释和讲解。
详细来说，本书的读者可以细分为：
1. 应用 Excel 作为基础平台，提供相应解决方案的程序员；
2. 各类科研工作者，应用 Excel 进行数据处理，这其实是本书最初的写作动机； 3. 在各类企事业部门需要进行大量机械性和重复性的信息、数据处理工作，希望可 以利用Excel 自动化这些工作的人员；
4. 其他对 Excel 自动化和VBA 编程感兴趣的读者。
本书使用的 Excel 版本是2003，但书中绝大多数内容并未涉及Excel 2000 之后的内容； 除了少数内容，书中所介绍的内容也与Excel 97 内容兼容。对于较新版本的内容，在介绍 时都尽可能的做了说明。 最后祝学习愉快！
*** *** *** *** *** *** *** ***
关于书中的符号、提示、代码等的说明 ", 所有关于菜单工具栏的操作以以下形式表示： “文件 － 打开” ", 对于一些技巧，需要提醒说明的问题，文中都已以下形式做了说明：
序 III
Excel 与VBA 程序设计 Copyright ? 2005 马维峰 打开 IDE 环境的方法
", 通过“工具 ― 宏 ― VISUAL BASIC 编辑器” ", 通过快捷键“ALT + F11”
", 右键单击工具栏，选择“VISUAL BASIC”，此工具栏有录制宏，打开VBA IDE
等的快捷按钮
", 程序代码以以下方式显示（黄底、字体为 Courier New、5 号）： #001 Function MyAdd(varA, varB) As Variant #002 MyAdd = varA + varB #003 End Function
", 对于一些需要说明的问题，一般以脚注方式列出。
目录 i
Excel 与VBA 程序设计 Copyright ? 2005 马维峰
目 录
序 ..................................................................................................................................................... I 目
录 ...........................................................................
.................................................................. I 1. 前
言 .........................................................................................................................................1
1.1. 关于 EXCEL 和VBA ...............................................................................................................1 1.2. EXCEL 作为开发平台..............................................................................................................2 1.3. 宏、加载宏和VBA................................................................................................................3 2. VBA 简
介................................................................................................................................6
2.1. VBA 及其IDE 初步...............................................................................................................6 2.1.1. VBA 集成开发环境（IDE）的组成.............................................................................6 2.1.2. 在 VBA IDE 下进行开发.............................................................................................10 2.1.3. 善用工具及其他...........................................................................................................12 2.2. 模块、函数和过程...............................................................................................................13 2.2.1. 模块...............................................................................................................................13 2.2.2. 过程...............................................................................................................................15 2.2.3. 函数...............................................................................................................................17 2.2.4. 调用过程和函数...........................................................................................................18 2.3. 数据类型与变量...................................................................................................................19 2.3.1. 常量和变量...................................................................................................................19 2.3.2. 数据类型.......................................................................................................................20 2.3.3. 运算符...........................................................................................................................22 2.3.4. 数组...............................................................................................................................23 2.3.5. 自定义数据类型...........................................................................................................24 2.3.6. 枚举类型.......................................................................................................................25 2.3.7. 变量的作用域（生存周期） .......................................................................................26 2.3.8. 字符串...........................................................................................................................27 2.3.9. 日期和时间...................................................................................................................29 2.4. VBA 语言基础......................................................................................................................30 2.4.1. 处理简单的用户输入输出...........................................................................................30 2.4.2. 控制程序流程...............................................................................................................31 2.4.3. 条件语句.......................................................................................................................31 2.4.4. 循环语句.......................................................................................................................34 2.4.5. With 语句......................................................................................................................37 2.4.6. Exit 语句.......................................................................................................................38 2.5. 用户窗体 ...............................................................................................................................39 2.5.1. 设计用户窗体...............................................................................................................39 2.5.2. 事件驱动.......................................................................................................................42
目录 ii
Excel 与VBA 程序设计 Copyright ? 2005 马维峰
2.5.3. 使用控件.......................................................................................................................42 2.6. 调试 VBA 代码.....................................................................................................................44 2.6.1. 错误的类型...................................................................................................................44
2.6.2. 使用 Debug 对象..........................................................................................................45 2.6.3. VBA 的调试工具..........................................................................................................45 2.7. 错误处理 ...............................................................................................................................46 2.7.1. 设置错误捕获...............................................................................................................47 2.7.2. 编写错误处理实用程序...............................................................................................47 2.7.3. 提供从错误处理程序跳出的出口...............................................................................47 2.7.4. 错误处理的简单示例...................................................................................................48 3. EXCEL 的对象模
型..............................................................................................................50
3.1. EXCEL 对象模型简介............................................................................................................50 3.2. APPLICATION 对象.................................................................................................................52 3.2.1. 控制 Excel 状态和显示的属性....................................................................................53 3.2.2. 返回对象的属性...........................................................................................................54 3.2.3. 执行操作.......................................................................................................................56 3.2.4. Window 对象和Windows 集合...................................................................................60 3.2.5. Application 事件..........................................................................................................60 3.3. WORKBOOK 对象..................................................................................................................63 3.3.1. Workbooks 集合............................................................................................................63 3.3.2. Workbook 的属性.........................................................................................................63 3.3.3. Sheets 集合....................................................................................................................65 3.3.4. Workbook 的方法........................................................................................................67 3.3.5. Workbook 的事件.........................................................................................................68 3.4. WORKSHEET 对象..................................................................................................................69 3.5. RANGE 对象...........................................................................................................................72 3.5.1. 返回或获得Range 对象...............................................................................................72 3.5.2. Range 对象的常用属性和方法....................................................................................75 4. 数据处
理 ...............................................................................................................................80
4.1. 概述 ......................................................................................................................................80 4.2. EXCEL 数据处理的方式和流程............................................................................................81 4.3. 操作数据文件.......................................................................................................................82 4.3.1. 使用 Excel 对象操作数据文件....................................................................................83 4.3.2. 使用 VBA 语句操作文件.............................................................................................90 4.3.3. FileSystemObject 对象模型.......................................................................................101 4.4. 操作数据 .............................................................................................................................110 4.4.1. 工作表数据引用.........................................................................................................110 4.4.2. 操作文本.....................................................................................................................113 4.4.3. 操作数值.....................................................................................................................117 4.4.4. Excel 数据表函数.......................................................................................................121 5. 高级话
题 ..............................................................................................................................123
5.1. EXCEL VBA 程序的类型和部署.........................................................................................123
目录 iii
Excel 与VBA 程序设计 Copyright ? 2005 马维峰
5.1.1. Excel VBA 程序的类型..............................................................................................123 5.1.2. 加载宏和一般电子表格程序的优缺点.....................................................................123 5.1.3. 部署.............................................................................................................................124 5.2. VBA 程序的安全性和保护................................................................................................124 5.3. 自动化其他OFFICE 组件....................................................................................................125 5.3.1. 启动其他Office 组件.................................................................................................126 5.3.2. 与其他 Office 组件交互.............................................................................................129 5.4. 通过其他程序自动化EXCEL..............................................................................................131 5.4.1. 创建 Excel 对象..........................................................................................................131 5.4.2. Excel 自动化中的事件...............................................................................................132 5.4.3. 使用 Excel 完成业务逻辑..........................................................................................133 5.5. 关于 EXCEL 工程的引用.....................................................................................................134 5.6. 提高效率的一些建议.........................................................................................................135 5.6.1. 尽量使用Excel 的内置函数......................................................................................135 5.6.2. 尽量减少使用对象引用.............................................................................................136 5.6.3. 高效使用Range 对象.................................................................................................137 5.6.4. 减少对象的激活和选择.............................................................................................138 5.6.5. 关闭屏幕更新.............................................................................................................138 5.6.6. 提高关键代码的效率.................................................................................................138 5.6.7. 代码执行时间的测算.................................................................................................139 6. 附
录 .....................................................................................................................................141
6.1. VBA 命名规则....................................................................................................................141 6.1.1. 变量、常量、自定义类型和枚举.............................................................................141 6.1.2. 过程和函数.................................................................................................................142 6.1.3. 模块、类模块和用户窗体.........................................................................................142 6.1.4. VBA 工程...................................................................................................................143 6.2. VBA 代码规范....................................................................................................................143 6.2.1. 代码的排版.................................................................................................................143 6.2.2. 注释.............................................................................................................................144 6.2.3. 程序版本.....................................................................................................................145 6.2.4. 一些基本原则.............................................................................................................145
1. 前言 1
Excel 与VBA 程序设计 Copyright ? 2005 马维峰
1. 前言
1.1. 关于 Excel 和VBA
Microsoft Excel 不仅仅是一个被广泛应用的电子表格软件，Excel 除了具有一般电子表 格软件的数据处理、统计分析、图表功能外，Excel 最大的特点是集成了VBA 环境。从