图书在版编目（CIP）数据

别怕，Excel VBA其实很简单／Excel之家编著．—北京：人民邮电出版社，2012.10

ISBN 978-7-115-28909-4

I．①别…　II．①E…　III．①表处理软件　IV．①TP391.13

中国版本图书馆CIP数据核字（2012）第149668号

内 容 提 要

本书考虑到大多数读者没有编程基础的实际情况，用浅显易懂的语言和生动形象的比喻，并配合大量插画，介绍Excel中看似复杂的概念和代码、从简单的宏录制、VBA编程环境和基础语法的介绍，到常用对象的操作与控制、Excel事件的调用与控制、用户界面设计、代码调试与优化、都进行了形象的介绍。

本书适合想提高工作效率的办公人员，特别是经常需要处理、分析大量数据的相关人员，以及财经专业的高校师生阅读。

别怕，Excel VBA其实很简单

◆　编　　著　Excel Home

责任编辑　马雪伶

◆　人民邮电出版社出版发行

北京市崇文区夕照寺街14号

邮编　100061

电子邮件　315@ptpress.com.cn

网址　http://www.ptpress.com.cn

北京天宇星印刷厂印刷

◆　开本：787 × 1092　1/16

印张：20.75

字数：445千字

印数：1-5000册

2012年10月第1版

2012年10月北京第1次印刷

ISBN 978-7-115-28909-4

定价：49.00元

读者服务热线：（010）67132692　印装质量热线：（010）67129223　反盗版热线：（010）67171154

广告经营许可证：京崇工商广字第0021号

目录

序

前言

第1章　走进Excel VBA的世界

1.1　不会Excel的人，真伤不起

1.1.1　做不完的表

1.1.2　神速的“超人”

1.1.3　你是怎样做工资条的

1.2　走自己的“录”，让别人重复去吧

1.2.1　什么是宏

1.2.2　用宏录下Excel操作

1.2.3　让录下的操作再现一遍

1.3　还可以怎样执行宏

1.3.1　给宏设置快捷键

1.3.2　将宏指定给按钮

1.3.3　将宏指定给常用工具栏按钮

1.4　是谁“挡住”了宏

1.4.1　宏为什么不能工作了

1.4.2　怎样修改宏安全级

1.5　VBA，Excel里的编程语言

1.5.1　录制宏不能解决的问题

1.5.2　让工资条一“输”到底

1.5.3　VBA编程，让你的表格更加灵活

1.5.4　什么是VBA

1.5.5　宏和VBA有什么关系

第2章　开始VBA编程的第一步

2.1　揭开神秘面纱背后的真面目

2.1.1　程序保存在哪里

2.1.2　应该怎样编写程序

2.2　程序里都有什么

2.2.1　代码

2.2.2　过程

2.2.3　模块

2.2.4　对象

2.2.5　对象的属性

2.2.6　对象的方法

2.2.7　关键字

2.3　VBA的编程环境——VBE

2.3.1　打开VBE编辑器

2.3.2　主窗口

2.3.3　菜单栏

2.3.4　工具栏

2.3.5　工程资源管理器

2.3.6　属性窗口

2.3.7　代码窗口

2.3.8　立即窗口

2.4　试写一个简单的VBA程序

2.4.1　添加或删除模块

2.4.2　动手编写程序

2.5　解除疑惑，一“键”倾心

第3章　Excel VBA基础语法

3.1　语法，编程的基础

3.1.1　这个笑话很凉快

3.1.2　VBA也有语法

3.1.3　学习VBA语法难吗

3.2　VBA里的数据类型

3.2.1　打酱油的故事

3.2.2　走进Excel的商店

3.2.3　VBA中有哪些数据类型

3.3　存储数据的容器：常量和变量

3.3.1　常量和变量

3.3.2　使用变量

3.3.3　使用常量

3.3.4　使用数组

3.4　集合、对象、属性和方法

3.4.1　对象，就像冰箱里的鸡蛋

3.4.2　对象的属性

3.4.3　对象的方法

3.5　连接的桥梁，VBA中的运算符

3.5.1　算术运算符

3.5.2　比较运算符

3.5.3　连接运算符

3.5.4　逻辑运算符

3.5.5　应该先进行什么运算

3.6　内置函数

3.6.1　VBA中的函数

3.6.2　VBA中有哪些函数

3.7　控制程序执行，VBA的基本语句结构

3.7.1　If…Then语句

3.7.2　Select Case语句

3.7.3　For…Next语句

3.7.4　Do While语句

3.7.5　Do Until语句

3.7.6　For Each…Next语句

3.7.7　其他的常用语句

3.8　Sub过程，基本的程序单元

3.8.1　关于VBA过程

3.8.2　编写Sub过程需要了解的内容

3.8.3　从另一个过程执行过程

3.8.4　过程的作用域

3.9　自定义函数，Function过程

3.9.1　试写一个函数

3.9.2　使用自定义函数

3.9.3　怎么统计指定颜色的单元格个数

3.9.4　声明函数过程，规范的语句

3.10　合理地组织程序，让代码更优美

3.10.1　代码排版，必不可少的习惯

3.10.2　怎样排版代码

3.10.3　注释，让代码的意图清晰明了

第4章　常用对象

4.1　与Excel交流，需要熟悉的常用对象

4.1.1　VBA编程与炒菜

4.1.2　VBA是怎么控制Excel的

4.1.3　应该记住哪些对象

4.2　一切由我开始，最顶层的Application对象

4.2.1　ScreenUpdating属性

4.2.2　DisplayAlerts属性

4.2.3　EnableEvents属性

4.2.4　WorksheetFunction属性

4.2.5　给Excel梳妆打扮

4.2.6　她和她的孩子们

4.3　管理工作簿，了解Workbook对象

4.3.1　Workbook与Workbooks

4.3.2　认识Workbook，需要了解的信息

4.3.3　实际操作，都能做什么

4.3.4　ThisWorkbook与ActiveWorkbook

4.4　操作工作表，认识Worksheet对象

4.4.1　认识Worksheet对象

4.4.2　操作工作表

4.4.3　Sheets与Worksheets

4.5　核心，至关重要的Range对象

4.5.1　多种方法引用Range对象

4.5.2　还可以怎样得到单元格

4.5.3　操作单元格，还需要了解什么

4.5.4　亲密接触，操作单元格

4.6　不止这些，其他常见的对象

4.6.1　名称，Names集合

4.6.2　单元格批注，Comment对象

4.6.3　给单元格化妆

4.7　典型的技巧与示例

4.7.1　创建一个工作簿

4.7.2　判断工作簿是否打开

4.7.3　判断工作簿是否存在

4.7.4　向未打开的工作簿中录入数据

4.7.5　隐藏活动工作表外的所有工作表

4.7.6　批量新建工作表

4.7.7　批量对数据分类

4.7.8　将工作表保存为新工作簿

4.7.9　快速合并多表数据

4.7.10　汇总同文件夹下多工作簿数据

4.7.11　为工作表建立目录

第5章　Excel事件

5.1　让Excel自动响应你的行为

5.1.1　让Excel自动问好

5.1.2　事件，VBA里的自动开关

5.1.3　事件过程

5.1.4　编写事件过程

5.2　Worksheet事件

5.2.1　关于Worksheet事件

5.2.2　常用的Worksheet事件

5.2.3　Worksheet事件列表

5.3　Workbook事件

5.3.1　关于Workbook事件

5.3.2　常用的Workbook事件

5.3.3　Workbook事件列表

5.4　别样的自动化

5.4.1　MouseMove事件

5.4.2　不是事件的事件

5.5　典型的技巧与示例

5.5.1　一举多得，快速录入数据

5.5.2　我该监考哪一场

5.5.3　让文件每隔一分钟自动保存一次

第6章　用户界面设计

6.1　在Excel中自由地设计界面

6.1.1　关于用户界面

6.1.2　控件，必不可少的调色盘

6.2　使用控件，将工作表当作画布

6.2.1　在工作表中使用窗体控件

6.2.2　在工作表中使用ActiveX控件

6.2.3　窗体控件和ActiveX控件的区别

6.3　与用户交互，简单的输入输出对话框

6.3.1　InputBox函数

6.3.2　Application对象的InputBox方法

6.3.3　MsgBox函数

6.3.4　Application对象的FindFile方法

6.3.5　Application对象的GetOpenFilename方法

6.3.6　Application对象的GetSaveAsFilename方法

6.3.7　Application对象的FileDialog属性

6.4　构建用户窗体，自己设计交互界面

6.4.1　关于用户窗体

6.4.2　添加一个用户窗体

6.4.3　设置窗体的属性

6.4.4　在窗体上添加控件

6.4.5　显示窗体

6.4.6　关闭窗体

6.4.7　使用控件

6.4.8　用键盘控制控件

6.5　改造Excel现有的界面

6.5.1　更改标题栏的程序名称

6.5.2　显示或隐藏菜单栏

6.5.3　显示或隐藏工具栏

6.5.4　设置窗口

6.5.5　其他设置

6.6　典型的技巧或示例

6.6.1　设计一张调查问卷

6.6.2　职工信息管理界面

6.6.3　一个简易的登录窗体

第7章　代码调试与优化

7.1　VBA中可能会发生的错误

7.1.1　编译错误

7.1.2　运行时错误

7.1.3　逻辑错误

7.2　VBA程序的3种状态

7.2.1　设计模式

7.2.2　运行模式

7.2.3　中断模式

7.3　Excel已经准备好的调试工具

7.3.1　让程序进入中断模式

7.3.2　为程序设置断点

7.3.3　使用Stop语句

7.3.4　使用立即窗口

7.3.5　使用本地窗口

7.3.6　使用监视窗口

7.4　错误处理的艺术

7.4.1　Go Error GoTo标签

7.4.2　On Error Resume Next

7.4.3　On Error GoTo 0

7.5　让代码跑得更快一些

7.5.1　合理地使用变量

7.5.2　避免反复引用相同的对象

7.5.3　尽量使用函数完成计算

7.5.4　去掉多余的激活和选择

7.5.5　合理使用数组

7.5.6　关闭屏幕更新

序

VBA，让效率飞起来

当加班成为常态，改变在所难免

十年前的我，加班是家常便饭的事，每天成堆的工作总是压得我喘不过气来，我和同事们就像一枚枚棋子，蜷缩在办公室里，在电脑前紧张地忙碌着，不知目睹了多少个华灯初上到灯火阑珊的夜晚。

2000年8月的一天我的目标是完成生产成本核算系统的最后一个报表模块的开发。有了它，就可以方便地查询和计算每一种产成品在任意一个工序上的成本明细项目，还可以在不同月份之间进行结转、对比。

我喜欢在晚上写程序，因为晚上安静，能让我的思路飞扬，让代码随着键盘声快速地推进，等待大功告成的那一刻出现。

你不会以为我是程序员吧？

不，事实上，我是财务部的成本主管。我就职于一家制造型企业，有IT部门，但是没有程序员。说白了，我就是千百万个成天和Excel表格“耳鬓厮磨”的一员。

我们公司的产品有几十种，涉及的材料有几千种，每个产品又有N道工序，每道工序由数量不等的作业人员进行生产。我的工作，就是计算和分析所有产成品和半成品的生产成本，包括材料、人工和杂费。计算依据主要包括生产部门提交的各产品工序的工时记录表，仓库提交的材料进销数据，HR部门提供的工资单明细。

这样的计算任务并不轻松，计算目标复杂，原始数据繁多，有些甚至不是电子文档，而且只有我一个人。再而且，时间非常紧张，因为每个月交报表的时间是固定的。

也许你想问，这么复杂的计算用Excel？你们公司难道没有ERP吗？

有的，而且声名不小，价格不菲。但在我看来，公司的ERP虽有一定的作用，不过却存在很多局限性，局部线条偏粗，又有些笨拙，很难给出我需要的结果。

所以，我必须借助Excel。

回想中学时的《政治》课本上说，资本家为了榨取更多的剩余价值，有两种方法：一是延长工作时间，二是提高生产效率。我现在清楚地认识到，为了及时准确地完成计算任务，方法同样有两个：加班，或者提高计算效率。

我当然不愿意加班。同时，加班的产出也是有限的，并不能解决任务重时间紧的根本性问题。

所以，我必须提高效率。

一次自动核算成本系统的开发经历，让我受益无穷

自从接了公司的生产成本核算系统这个活，我的Excel水平突飞猛进。从最初的焦头烂额，到现在的从容应对，我通过不断优化计算方法，完善成本核算模板，减轻工作量。

有人说，学好Excel可以以一当十。年轻的我凭着一腔热情，还真没有注意到：公司的产品规模在不断扩大，计算任务随之加重，我依然可以按时交报表。由于我舍得下力气去研究，直到后来协助工作的同事被上司调派去负责别的内容。

我的想法很简单，多做就是多学习，付出一定有回报。

我的成本核算模板，按产品区分，主要使用的是Excel的函数、公式和数据透视表，可以实现成本计算的半自动化——输入原始数据，结果自动生成。原始数据，一部分来源于上个月的成本数据，一部分从ERP中导出。

模板完善后，我的工作重心不再是计算，而是处理这样那样的原始数据。这是一件相当繁琐无聊的事情。导出、保存、打开、复制、粘贴、切换、关闭，奈何我200APM[1]的手速，因为涉及几百个文件的数据处理，至少得一两天时间，处理过程中还很容易出错。

问题是，公司的产品数量一直在增加，并处于变化中，这让我再次想起了“水管工的故事”。

于是，我决定继续挖掘Excel的潜力，其实也是我自身的潜力。两个月以后，我用Excel VBA代码替代了80%的成本原始数据处理工作。只要按一次键，数据就能乖乖地按规定的路线在几百个Excel文件之间流转，就像欢乐的浪花在美丽的小河中荡漾。

说真的，没有什么事情，比看着自己写的代码正常运行，让复杂无比的工作灰飞烟灭的感觉更有成就感了。

了解到Excel VBA与众不同的威力后，我的激情再一次被点燃，我决定要自己写一个成本计算分析系统，我希望以后每个月的成本计算分析都是全自动的。

经过持续不断地学习和研究，我想，今天晚上，终于可以达成了这一目标了。

透视VBA的知与行

转眼间迈入2012年了。

我们都生活在信息社会中，生活在一个前所未见的充斥着海量数据的年代。无论是企业还是个人，每天都要接触无数以数据为载体的信息。

数据，甚至已经成为了企业或个人的替代品。

不相信？

一家你未曾亲身到访甚至未曾接触过其产品的企业，对你来说意味着什么？它无非会成为财务报表或统计报表上的一堆林林总总的数据，诸如生产规模、员工人数、利润水平……

一个你未曾谋面未曾听说的人，对你来说意味着什么？就好像进入婚恋网站搜索对象，这些陌生人只不过是个人指标数据的集合体，诸如身高、体重、职业、收入……

想要在这样一个时代生存，处理数据的能力是必须的，因为实在有太多数据要处理了。广大Excel的用户，尤其是Excel的重度用户肯定对此深有体会。

作为Excel Home的站长和一名培训讲师，我接触过许多各式各样的数据处理要求，也体验或亲身参与过许多基于Excel的解决方案。这些宝贵经验让我对Excel提供的各项功能有更深的理解。

在Excel中制作计算模型，主力军非函数与公式莫属。300多个不同功能的函数在公式中灵活组合，可以创造无数种算法，再加上数组和名称的配合使用，几乎可以完成绝大多数计算任务。

要论数据分析和报表生成的便捷，不得不提到数据透视表，这是Excel最厉害的本领，厉害在于其功能强大的同时，使用起来却非常简单。

但如果只会这两样，仍然会有很多时候感到束手束脚，究其根本在于以下几方面。

1．函数和公式只能在其所在的位置返回结果，而无法操作数据表格的任意位置，更不能操作表格的任意属性（比如设置单元格的填充色，或删除单元格）。

2．函数和公式、数据透视表都需要规范的数据源，但往往我们工作量最大之处就在于获取和整理原始数据。比较麻烦的情况之一就是原始数据很可能是位于某个文件夹下的几十份表格。

3．使用函数和公式、数据透视表制作的解决方案，难以具备良好的交互性能。因为它们只能存在于单元格中，与普通数据是处于同一个平面的。

4．对于业务流程较为复杂、数据项经常变化的计算很难处理。

5．无法迅速省力地完成大量的重复操作。

所以，永远不要忘记Excel还有一个杀手级的功能——VBA。

VBA是什么，怎么用，在本书中会给出详细的答案。这里，我只想说，只有这个功能才真正让Excel成为了无所不能的数据处理利器，才让我们有机会可以彻底地高效办公。

很多人认为VBA很神秘，认为会写代码是自己不可能实现的事情。虽然我不能保证人人都能学会VBA，但我可以保证如果你能学会函数和公式，你也能学会VBA，因为它们的本质是相同的。函数和公式无非是写在单元格中的一种简短代码罢了。

所以，如果你曾经觉得自己连Excel函数和公式也搞不定，现在却能熟练地一口气写下好几个函数嵌套的公式，那么你学VBA不会有问题。

在我眼里，VBA就好像“独孤九剑”。这武功最大的特点是遇强则强，遇弱则弱。如果你每天面对的数据非常有限，计算要求也很简单，那么用VBA就是高射炮打蚊子了。但如果你是Excel重度用户，经常需要处理大量数据，而Excel现有功能无法高效完成计算任务时，就可以考虑让VBA上场，一举定乾坤。

今时不同往日，互联网的发展使得技术和经验的分享非常方便。如果说十年前你想用VBA实现任何一个小功能都需要先掌握全部语法，然后一行一行代码自己写的话，那么现在Excel Home上有太多太多现成的实现不同目标的VBA代码，许多代码甚至已经到了拿来即用的程度。

所以，如果你的时间非常有限，也没有兴趣成为一个Excel开发者，你只需要快速地学习掌握Excel VBA的基本语法，然后到互联网上去淘代码来用到自己的工作中。如果你投入的时间多一点点，你会发现你很快就能看懂别人的代码，然后做出简单的修改后为自己所用。这个过程，是不是和你当年学Excel函数和公式的经历很类似？

等你做到这一步，你会发现原来公司里那个很厉害的会写代码的Excel高手的秘密武器原来是这样的啊，以后可以少请他吃饭以换取他为你写个小功能了……

因为工作的关系，我接触过很多信息化工具，也了解过一些编程语言，我发现所有工具的本质是相通的。每种工具都有其优缺点，有其专属的场合。这种专属并非指不可替代，而是说最佳选择。

因此，我不赞成VBA至上的观点，因为尽管VBA无所不能，但如果我们事事都写代码，那还要Excel本身的功能干嘛？我也反对VBA无用的观点，你暂时用不上怎么能说明此工具无用？甚至说，你根本就不会用这工具，怎么知道你用不上？

用VBA，是为了更高效。不用，也是因为同样的目的。

但是，会了VBA，你将拥有高效的更多种选择。不会，你就没有。这一点，高效人士都懂的。

Excel Home创始人、站长　周庆麟

2012年2月22日

[1]　APM，指每分钟击键次数。

前言

本书以培养学习兴趣为主要目的，遵循实用为主的原则，深入浅出地介绍Excel VBA的基础知识。书中摒弃了枯燥乏味的科技说明文风格，避开难学少用的粗枝繁叶，利用生动形象的比拟和浅显易懂的语言去描述Excel VBA中看似复杂的概念和代码，借用实用的例子来了解编程的思路和技巧，为读者提供练习和思考的空间，讲练结合，让读者亲自体验VBA编程的乐趣及方法。

本书配套示例文件和视频教程，请到Excel Home网站（http://www.excelhome.net）获取。

阅读对象

如果你是使用Excel的工作人员，长期以来被无穷的数据折磨得头昏脑胀，希望通过学习VBA找到高效的解决方法；如果你是在校大中专院校学生，有兴趣学习Excel VBA，为今后的职业生涯先锻造一把利剑；如果长期以来你一直想学VBA，却始终入门无路，那都是本书最佳的阅读对象。

当然，在阅读本书之前，你得对Windows操作系统和Excel有一定的了解。

写作环境

VBA虽然依附于Office软件，但它本身是一门独立的编程语言，因此，VBA在不同版本Office中的用法几乎没有差异。

为照顾多数Excel 2003用户的使用习惯，本书以Windows XP和Excel 2003为写作环境。但使用Excel 2007和Excel 2010的用户不必担心，因为书中涉及的知识点，绝大多数在Excel 2007和Excel 2010中同样适用。

后续服务

在本书的编写过程中，尽管作者始终尽心尽力，但仍无法避免存在不足之处。如果你在阅读过程中有任何意见或建议，敬请反馈给我们，我们将根据你宝贵的意见或建议进行改进，继续努力，争取做得更好。如果你在学习过程中遇到困难或疑惑，也可以和我们交流。

你可以通过以下任意一种方式和我们互动：

1．访问http://club.excelhome.net，通过论坛和我们进行交流；

2．访问http://t.excelhome.net，参加ExcelHome免费培训班；

3．如果你是微博控，可以关注我们的微博：http://weibo.com/iexcelhome（新浪），http://t.qq.com/excel_home（腾讯）。那里会向你推荐很多优秀的学习资源，向你介绍很多适用的Excel技巧。

致谢

本书由周庆麟策划及统稿，由罗国发进行编写。感谢美编马佳妮完成了全书的精彩插画，这些有趣的插画让本书距离“趣味学习，轻松理解”的目标更进了一步。

Excel Home论坛管理团队和Excel Home免费在线培训中心教管团队长期以来都是Excel Home图书的坚实后盾，他们是Excel Home中最可爱的人。最为广大会员所熟知的代表人物有朱尔轩、林树珊、刘晓月、吴晓平、方骥、杨彬、朱明、郗金甲、黄成武、孙继红、王建民、周元平、陈军、顾斌等，在此向这些最可爱的人表示由衷的感谢。在本书正式出版前，有幸邀请到多位Excel Home会员进行志愿预读，他们以高超的技术和无比的细心，帮助我们对书稿做出了进一步的完善。本次预读活动由吴晓平和赵文妍负责，团队成员为（排名不分先后）陈智勇、刘冠、潘新水、曲天非、邵武、向绪霞、许春富、燕铁艳、叶兆锋、张建元和张敏。在此，特向预读团队表示最真诚的感谢！

衷心感谢Excel Home的百万会员，是他们多年来不断的支持与分享，才营造出热火朝天的学习氛围，并成就了今天的Excel Home系列图书。

Excel Home简介

Excel Home（http://www.excelhome.net）是微软在线社区联盟成员，是一个主要从事研究、推广以Excel为代表的Microsoft Office软件应用技术的非营利性网站。自1999年由Kevin Zhou（周庆麟）创建以来，目前已成长为全球最具影响力的华语Excel资源网站之一，拥有大量原创技术文章、视频教程、Addins加载宏及模板。

Excel Home社区是一个颇具学习氛围的技术交流社区。截止到2012年8月，注册会员人数逾200万，同时也产生了26位Office方面的MVP（微软全球最有价值专家），中国大陆地区的Office MVP被授衔者大部分来自本社区。现在，社区的版主团队包括数十位祖国大陆及港澳台地区的Office技术专家，他们都身处各行各业，并身怀绝技！在他们的引领之下，越来越多的人取得了技术上的进步与应用水平的提高，越来越多的先进管理思想转化为解决方案并被部署。

Excel Home是Office技术应用与学习的先锋，通过积极举办各种技术交流活动，开办完全免费的在线学习班，创造了与众不同的社区魅力并持续鼓励技术的创新与进步。网站上的优秀文章在微软（中国）官网上同步刊登，让技术分享更加便捷。另一方面，原创图书的出版加速了技术成果的传播共享，从2007年至今，Excel Home已累计出版Office技术类图书数十本，在Office学习者中赢得了良好的口碑。

Excel Home专注于Office学习应用智能平台的建设，旨在为个人及各行业提升办公效率、将行业知识转化为生产力，进而实现个人的知识拓展及企业的价值创造。无论是在校学生、普通职员还是企业高管，在这里都能找到您所需要的。创造价值，这正是Excel Home的目标之所在。

Let's do it better!

第1章

走进Excel VBA的世界

VBA就像一座神秘的城堡，对很多人来说都是神秘的。很多人想走进VBA的世界，却始终找不到打开大门的钥匙。

什么是VBA？

怎样学习VBA？

面对这些问题，让我们从身边开始，一起探索，一起解答。

1.1　不会Excel的人，真伤不起

1.1.1　做不完的表

数据采集、数据处理、数据分析……这是小张每天都在做的工作。老板的需求和基础数据一样每天都在改变，而小张做表的速度却永远也跟不上老板敏捷的思维。

不同的数据，相同的操作。小张感叹：“和数据打交道的日子，真烦！”

单位来了新同事，接手小张平时的工作。

1.1.2　神速的“超人”

终于告别上万条的数据，离开乱七八糟的报表，脱离“苦海”的日子，小张的日子要多舒心有多舒心。可是……

电话里，老板那可以撑爆整幢大楼的赞扬声和新同事腼腆的笑容，让小张心里很不是滋味：“一个小时和一星期，中间的差距不仅只是时间。不会Excel的人，真伤不起！”

1.1.3　你是怎样做工资条的

小张决定向新同事取取经……

同事打开一张工资表，如图1-1所示，让小张把它做成工资条，如图1-2所示。

图1-1　工资表

图1-2　工资条

小张熟练地拿起鼠标，选中工资表头所在行→复制→选中第二条工资记录所在行→单击右键→插入复制单元格。

完成后，又按同样的操作进行第三条，第四条……

新同事看完后，笑了：“如果是1000条记录的工资表，这样做需要多久？”

小张苦笑，也只能苦笑。

工作的内容不少，但都是重复的操作。而这种重复不但枯燥而且费时，尽管小张天天时时刻刻都在做表，却永远也跟不上老板的节奏。

1.2　走自己的“录”，让别人重复去吧

新同事的建议让小张感到很茫然。

面对小张满脑子的疑问，新同事耐心地给他解释，并示范操作过程……

1.2.1　什么是宏

就像用摄像机录下来的视频，在Excel里，宏就是Excel用户使用宏录制器录下的一组操作。

选中工资表头所在行→复制→选中工资记录所在行→单击右键→插入复制单元格，这是小张制作工资条时重复的操作。

1.2.2　用宏录下Excel操作

录制宏前需要进行一些简单的设置，如图1-3所示。

图1-3　录制宏前的设置

完成上述设置后就可以录制宏了，如图1-4所示。

图1-4　用宏录下Excel操作

这样，宏就录制好了。

1.2.3　让录下的操作再现一遍

录制完成后，通过下面的方法运行宏，如图1-5所示。

图1-5　执行宏

如果要继续插入新的工资表头，就继续执行宏。

练习小课堂

学会使用录制和执行宏代替手工完成重复操作后，小张很高兴。他发现工作中很多问题都可以借助宏来提高工作效率。

可是，他不明白相对引用和绝对引用的区别，你能分别录制不同的宏，执行它们，找到它们之间的区别吗？

参考答案

绝对引用：如果使用绝对引用，在执行宏的过程中，无论选中了哪个单元格，宏都在特定的单元格中执行录制的操作。

相对引用：如果使用相对引用，在执行宏的过程中，将以活动单元格为A1单元格，宏在相对于活动单元格的特定单元格中执行录制的操作。如果你想让录制的宏可以在任意区域中使用，就使用相对引用。

1.3　还可以怎样执行宏

【宏】对话框里的“执行”按钮就是运行宏的开关。不够方便，不够快捷，是这个开关的缺点。如果你不喜欢这个开关，可以选择其他执行宏的方法。

1.3.1　给宏设置快捷键

录制宏前，可以在【录制新宏】对话框里为宏设置快捷键，如图1-6所示。

图1-6　录制宏前为宏设置快捷键

也可以在录制宏后进行设置，如图1-7所示。

图1-7　录制宏后给宏设置快捷键

给宏设置快捷键后，就可以按下相应的组合键执行宏。

注意：因为给宏指定的快捷键会覆盖Excel默认的快捷键。例如：把<Ctrl+C>指定给某个宏，那在Excel中按下<Ctrl+C>组合键将不再执行复制操作。

1.3.2　将宏指定给按钮

不便记忆，不易上手。快捷键虽快却不实用。

无论出于什么目的，都应尽量让设计的表格显得直观一些。

拿过电视机的遥控板，扫一眼就知道该按下哪个按钮来加减声音，按下哪个按钮来调节频道。

如果你担心忘记为宏设置的快捷键，可以绘制一块直观形象的“遥控板”，通过单击按钮来执行宏。图1-8所示为将宏指定给按钮的方法。

图1-8　将宏指定给按钮

如果是已经添加的按钮，可以用鼠标右键单击它，在右键菜单中执行【指定宏】菜单命令打开【指定宏】对话框，再将宏指定给按钮，如图1-9所示。

图1-9　打开指定宏对话框

当按钮呈编辑状态（如果不是编辑状态，可以先用鼠标右键单击它）时，单击按钮表面，更改标签为“生成工资条”，调整按钮的大小和位置，完成后单击按钮外的任意区域退出对按钮的编辑，如图1-10所示。

图1-10　更改标签后的按钮

完成上述设置后即可单击按钮执行宏，如图1-11所示。

图1-11　单击按钮执行宏

还可以用同样的方法将宏指定给图片或自选图形等。

1.3.3　将宏指定给常用工具栏按钮

将宏指定给常用工具栏按钮的操作步骤如图1-12所示。

图1-12　将宏指定给自定义工具栏按钮

还可以在右键菜单中对按钮进行其他的设置，如更改按钮的名称、图像等，如图1-13所示。

图1-13　设置自定义按钮

完成后关闭【自定义】对话框，就可以单击自定义的按钮执行宏了。

1.4　是谁“挡住”了宏

1.4.1　宏为什么不能工作了

有时，打开一个保存有宏的工作簿或试图执行一个宏时，Excel会显示如图1-14所示的对话框，而并不执行宏。

图1-14　禁用宏的提示对话框

这是小张遇到的一个新问题，他再次向新同事求助。

1.4.2　怎样修改宏安全级

修改宏的安全级的操作如图1-15所示。

图1-15　打开【安全性】对话框

如果希望录制的宏或编写的VBA程序得到运行的机会，应将安全级设置为“中”或“低”。

如果设置为“中”，每次打开文件时，Excel都会显示【安全警告】对话框，让用户选择启用或禁用宏，如图1-16所示。

图1-16　打开文件时的安全警告对话框

如果将安全级设置为“低”，打开文件时Excel不会给出任何提示并直接启用工作簿里所有的宏，如果工作簿里带有恶意代码，这样做是非常危险的，所以，建议将安全级设置为“中”。

注意：在Excel 2003中，修改宏安全级后需要关闭工作簿再重新打开它，修改才能生效。

1.5　VBA，Excel里的编程语言

1.5.1　录制宏不能解决的问题

尽管可以录下用户在Excel里的操作，但却不能满足用户所有的需求。

1.5.2　让工资条一“输”到底

Step 1：查看已经录制的宏，见图1-17。

图1-17　录制的宏

Step 2：在第一行代码"Sub生成工资条()"的后面添加两行新代码：

在最后一行代码"End Sub"的前面添加一行代码：

图1-18　修改后的宏

Step 3：关闭窗口，返回Excel工作表界面，重新执行宏，所有的工资条就全部完成了，如图1-19所示。

图1-19　一次性生成所有工资条

1.5.3　VBA编程，让你的表格更加灵活

不管你是否知道应该怎样修改和使用录制的宏，但从小张的故事里，应该看到了修改前与修改后的宏在工作效率上的差别。

实际上，在运行宏的过程中，我们总希望能自主地判断和选择需要执行的操作或计算，而录制的宏并不能满足类似的需求。这就要求我们对宏进行适当的修改，甚至自己动手编写满足需要的代码，即：使用VBA编程。

1.5.4　什么是VBA

VBA（Visual Basic For Application）是一种编程语言，是建立在Office中的一种应用程序开发工具。可以利用VBA有效地扩展Excel的功能，设计和构建人机交互界面，打造自己的管理系统，帮助Excel用户更有效地完成一些基本操作、函数公式等不能完成的任务，从而提高工作效率。

同你的名字一样，VBA也只是一个名字，一种编程语言的名字。

1.5.5　宏和VBA有什么关系

VBA是编程语言，宏是用VBA代码保存下来的程序。

录制的宏只是VBA里最简单的程序，正因为如此，录制的宏存在许多的缺陷：如无法进行判断和循环，不能显示用户窗体，不能进行人机交互……

要想打破这些局限，让自己的程序更加自动化和智能化，仅仅掌握录制和执行宏是远远不够的，还需要掌握VBA编程的方法，自主地编写VBA程序。

这就是我们学习VBA的目的。

第2章

开始VBA编程的第一步

优秀的运动员总是非常注重起跑的第一步。

准确的姿势，恰到好处的起跑时间，再加上中间的努力拼搏，总能带给运动员优秀的成绩。

比赛的最终赢家不但有过硬的运动天赋，而且一定经过长期学习和坚持训练的过程。

想成为一名优秀的VBA编程者，最先应该了解什么？让我们一起来学习。

2.1　揭开神秘面纱背后的真面目

从小张的故事里走出来，我们正式开始学习Excel VBA。

既然录制的宏就是VBA程序，那让我们打开第1章1.2.2小节中录制的宏所在的工作簿文件，一起研究研究。

2.1.1　程序保存在哪里

参照图2-1所示的操作，可以查看宏对应的代码。

图2-1　查看录制的宏

这个窗口是VBE窗口，打开它，在右面的【代码窗口】中可以看到一串代码，这就是使用宏录制器录下来的操作，以代码的形式保存在模块里。

练习小课堂

动手录几个不同的宏，比一比，宏的代码有什么共同的地方？把你总结的结论写下来，然后再继续后面的内容。

2.1.2　应该怎样编写程序

图2-2所示为录制宏得到的程序。

图2-2　录制宏得到的程序

2.2　程序里都有什么

为了在编程时能更加得心应手，有必要先花点时间了解一下在编程的过程中，会反复提到的一些概念。

2.2.1　代码

VBA的程序由代码组成，可以通过录制宏或自主编写得到VBA代码。

2.2.2　过程

用VBA代码把完成一个任务的所有操作保存起来就是一个VBA过程。一个过程可以有任意多的操作，可以有任意长的代码。

在本书中，只介绍Sub过程和Function过程。

2.2.3　模块

模块是保存过程的地方，一个模块可以保存多个不同类型的过程。

2.2.4　对象

用代码操作和控制的东西即为对象，如工作簿、工作表、单元格、图片、图表、透视表等。

2.2.5　对象的属性

每个对象都有属性，属性是对象包含的内容或特点。

从对象的属性，可以了解该对角具有的性质和特点。如字体的颜色，颜色就是字体的属性；按钮的宽度，宽度就是按钮的属性。从对象的属性还可以了解到这个对象包含了哪些其他的对象。如Sheet1工作表的A1单元格，A1单元格就是Sheet1工作表的属性；A1单元格的内容，内容就是A1单元格的属性。

在书写时，对象和属性之间用点（.）连接，对象在前，属性在后，如A1单元格的内容，用汉字表达为：A1.内容

写成代码为：

对象的某些属性也是对象，属性和对象是相对而言的。

2.2.6　对象的方法

每个对象都有方法，方法是指在对象上执行的某个动作。如选中A1单元格，“选中”是在A1单元格这个对象上执行的操作，就是A1单元格的方法。

对象和方法之间也用点（.）连接，对象在前，方法在后，如选中A1单元格写成代码为：

2.2.7　关键字

关键字是VBA中的保留字或符号，如语句名称、函数名称、运算符等都是关键字。

2.3　VBA的编程环境——VBE

在第2章2.1.1小节中打开的窗口就是编写VBA程序的地方——VBE（Visual Basice Editor），了解VBA程序中经常提到的概念后，我们再花一点时间来熟悉它。

2.3.1　打开VBE编辑器

要进入VBE，首先必须启动Excel程序，启动Excel后，要切换到VBE窗口，常用的方法有以下几种。

方法一：按<Alt+F11>组合键。

方法二：依次执行【工具】→【宏】→【Visual Basic编辑器】菜单命令，如图2-3所示。

图2-3　利用菜单命令打开VBE

方法三：右键单击工作表标签，执行【查看代码】菜单命令，如图2-4所示。

图2-4　利用右键菜单打开VBE

方法四：单击【Visual Basic】工具栏中的【Visual Basic编辑器】按钮，如图2-5所示。

图2-5　利用Visual Basic工具栏打开VBE

方法五：单击【控件工具箱】中的“查看代码”按钮，如图2-6所示。

图2-6　利用控件工具箱打开VBE

方法六：利用【控件工具箱】新建一个ActiveX控件，双击控件打开VBE窗口，如图2-7所示。

图2-7　利用控件打开VBE

2.3.2　主窗口

进入VBE后，首先看到的就是VBE的主窗口，主窗口通常由【工程资源管理器】、【属性窗口】、【代码窗口】、【立即窗口】、【菜单栏】和【工具栏】组成，如图2-8所示。

图2-8　VBE的主窗口

2.3.3　菜单栏

VBE的【菜单栏】和Excel 2003的菜单栏类似，包含了VBE中各种组件的命令。

2.3.4　工具栏

默认情况下，【工具栏】位于【菜单栏】的下面，可以在【视图】→【工具栏】菜单里显示或隐藏它，如图2-9所示。

图2-9　显示或隐藏工具栏

2.3.5　工程资源管理器

在【工程资源管理器】中可以看到所有打开的Excel工作簿和已加载的加载宏，一个Excel的工作簿就是一个工程，工程名称为"VBA Project（工作簿名称）"。【工程资源管理器】中最多可以显示工程里的4类对象，即Excel对象（包括Sheet对象和ThisWorkbook对象）、窗体对象、模块对象和类模块对象，如图2-10所示。

图2-10　工程资源管理器

但并不是所有工程里都包含这类对象，新建的Excel文件只有Excel类对象。

2.3.6　属性窗口

可以在【属性窗口】中查看或设置对象的属性。

2.3.7　代码窗口

【代码窗口】由对象列表框、过程列表框、边界标识条、代码编辑区、过程分隔线和视图按钮几部分组成，如图2-11所示。

图2-11　代码窗口栏

【代码窗口】是编辑和显示VBA代码的地方，【工程资源管理器】中的每个对象都拥有自己的【代码窗口】，如果想将VBA程序写在某个对象里，首先应在【工程资源管理器】中双击以激活它的【代码窗口】。反过来，如果想查看某个对象里保存有哪些程序，也必须先在【工程资源管理器】中双击以激活它的【代码窗口】。

2.3.8　立即窗口

在【立即窗口】中直接输入命令，回车后将显示命令执行后的结果，如图2-12所示。

图2-12　使用立即窗口执行代码

【立即窗口】一个很重要的用途是调试代码，相应的内容请参阅第7章7.3.4小节。

如果打开VBE窗口后，【立即窗口】（或其他窗口）没有显示，可以在【视图】菜单中设置显示它，如图2-13所示。

图2-13　利用视图菜单显示窗口

2.4　试写一个简单的VBA程序

运行Excel程序，新建一个工作簿，进入VBE，让我们动手编写一个简单的程序，当程序运行后，用一个对话框说出现在的心情。

2.4.1　添加或删除模块

因为VBA程序一般保存在模块里，所以在编写程序前，应先添加一个模块来保存它。

添加模块

方法一：利用菜单命令插入模块的具体操作如图2-14所示。

图2-14　利用菜单命令插入模块

方法二：利用右键菜单插入模块的具体操作如图2-15所示。

图2-15　利用右键菜单插入模块

练习小课堂

怎样添加用户窗体和类模块？试一试，然后再继续后面的内容。

参考答案

方法一：右键单击【工程资源管理器】中的空白处，在【插入】菜单选择要插入的对象；

方法二：单击菜单栏中的【插入】菜单，选择要插入的对象。

方法参照2.4.1小节中插入模块的方法。

删除模块

如果工程中有多余的模块，可以删除它。

方法一：利用文件菜单移除模块的具体操作如图2-16所示。

图2-16　利用文件菜单移除模块

方法二：利用右键菜单移除模块的具体操作如图2-17所示。

图2-17　利用右键菜单移除模块

注意：删除模块后，同时也将删除保存在该模块中的所有程序。

2.4.2　动手编写程序

Step 1：在代码窗口中添加一个空过程，如图2-18所示。

图2-18　插入空过程

当然，你也可以在【代码窗口】中手动录入这些代码。

Step 2：将下面的代码写到前文两行代码的中间，如图2-19所示。

图2-19　添加代码后的过程

Step 3：运行过程，如图2-20所示。

图2-20　运行程序

2.5　解除疑惑，一“键”倾心

对刚刚涉足VBA世界的我们，面对满眼陌生的代码，类似这样的困惑总是很多。如果你想知道类似MsgBox这样的关键字是什么意思，F1键会是你最的好帮手，如图2-21所示。

图2-21　利用F1键查询MsgBox函数的帮助信息

帮助是Excel VBA自带的一本百科全书，它能有效地帮助你解答在学习或编程过程中遇到的许多困惑。记住F1键，它是打开这本书的一把“金钥匙”。

第3章

Excel VBA基础语法

厨师做菜有做菜的正确方法，一盘菜放一包盐肯定不行。

司机开车有应该遵守的规则，红灯亮了不停车肯定乱套。

踢足球不能用手，打篮球不能用脚。

无规矩不成方圆，做什么事都有应该遵循的法则。做菜咸了，汽车乱窜了，运动员开始抱着足球满球场乱跑了……这些都是不遵守规则的现象。

VBA编程也有必须遵循的规则，这一章，我们将一起了解VBA编程应该遵循的法则——VBA语法。

3.1　语法，编程的基础

3.1.1　这个笑话很凉快

什么是语法？

语文老师说：“语法就是说话的方法，正确表达应该遵循的法则。”

语法告诉我们：是妈妈煎鸡蛋，不是鸡蛋煎妈妈。鸡蛋不能说两片，应说两个。

3.1.2　VBA也有语法

写一个程序，就像写一篇作文，不遵循语法规则，肯定要闹笑话。

工作簿该怎么称呼，工作表该怎么表示，在VBA里都有规定，不是随心所欲的。

所以，学习VBA应先了解VBA语句的表达方式，只有这样，才能读懂并编写正确的代码。

3.1.3　学习VBA语法难吗

对很多一提到语法就头痛的人，这是最担心的一个问题。

学习VBA语法是一个打基础的过程，就像练习武功前先要日日夜夜地扎马步，练习唱歌前要天天吊嗓子。这样的过程对多数人来说是枯燥无味的，但同时又是必须的。

VBA语法究竟难不难，就看你是否能坚持学下去。

3.2　VBA里的数据类型

3.2.1　打酱油的故事

小A来到商店，“老板，打一斤酱油。”

老板：“……你拿菜篮子打酱油？”

离家不远，五分钟后。

“老板，打酱油。”

“拿个小小的花椒油瓶，你真有才。”

第三次，提着水桶……

……

选不对容器，打不回酱油。菜篮子装不了酱油；花椒油瓶装不下一斤酱油；水桶很大，能装酱油，但杀鸡却派上了牛刀……

3.2.2　走进Excel的商店

Excel就是一间“商店”，商店里摆着各种各样的数据，作为Excel的用户，每天都在重复着打酱油的故事。

职工编号、职工姓名、身份证号、出生年月、联系电话等，都是在Excel里天天打的酱油，如图3-1所示。

图3-1　Excel里的数据

酱油是液体，面条是固体，商店的老板知道应该把谁放在桶里，把谁放在纸箱里。

在Excel里，姓名、出生年月、基本工资这些不同的数据就像商店里不同的商品，为了便于区分，Excel把它们分为不同的类型。如文本、日期、数值等。

面对这些不同类型的数据，编写程序时，你得告诉Excel，应该选择哪种类型的容器来保存它们，如图3-2所示。

图3-2　Excel里的数据

3.2.3　VBA中有哪些数据类型

数据类型就是对同一类数据的统称，如文本、日期、数值等。

VBA里的数据类型有：字节型（Byte），整数型（Integer），长整数型（Long），单精度浮点型（Single），双精度浮点型（Double），货币型（Currency），小数型（Decimal），字符串型（String），日期型（Date），布尔型（Boolean）等，如表3-1所示。

表3-1　VBA中的数据类型

数据类型

存储空间（字节）

范围描述

Byte

1

保存0∼255的整数

Boolean

2

保存逻辑判断的结果：True或False

Integer

2

保存−2768∼32767的整数

Long

4

保存−2147483648∼2147483647的整数

Single

4

负值范围：−3.402823E38∼−1.401298E-45

正值范围：1.401298E-45∼3.402823E38

Double

8

负值范围：−1.79769313486232E308∼−4.94065645841247E-324

正值范围：4.94065645841247E-324∼1.79769313486232E308

Currency

8

数值范围：−922337203685477.5808∼922337203685477.5807

Decimal

14

不含小数时：+/−79228162514264337593543950335

包含小数时：+/−7.9228162514264337593543950335

最小非零数字：+/−0.0000000000000000000000000001

Date

8

日期范围：100年1月1日∼9999年12月31日

时间范围：0:00:00∼23:59:59

String（变长）

10字节加字符串长度

0到大约20亿个字符

String（定长）

字符串长度

1到大约65400个字符

Object

4

对象变量，用来引用对象

Variant（变体型）

除了定长String数据及用户定义类型外，可以包含任何种

类的数据。如果是数值，最大可达Double的范围；如果是

字符，与变长String的范围一样

用户自定义

每个元素的范围与它本身的数据类型的范围相同

不同的数据类型告诉Excel应该以什么形式来保存它。

面对不同类型的数据，在编程时，应先告诉程序按什么数据类型来保存或处理它，如图3-3所示。

图3-3　不同的数据使用不同的数据类型

练习小课堂

表3-2是某单位建立员工工资档案时会用到的数据，你能为它们指定最合适的数据类型吗？

表3-2　员工信息

3.3　存储数据的容器：常量和变量

3.3.1　常量和变量

常量和变量是VBA存储数据的两种容器。

一个酱油瓶可以打多次酱油，第一斤酱油用完了，拿到小买部满满的一瓶又提着回来。变量就像酱油瓶，可以随时随地把里面原有的酱油倒掉，再装入新的酱油。

而常量就像袋装酱油的包装袋，一旦往里面装入酱油，就不能更换其他的酱油。

因此，无论存储什么类型的数据，变量都可以更换内容，重复使用，而常量不可以。这是变量和常量的区别。

3.3.2　使用变量

存储在变量里的数据可以更换，因此变量通常用来存储在程序运行过程中需要临时保存的数据或对象。

声明变量

就像指定瓶子的名称和用途一样，声明变量就是指定变量的名称和可以存储的数据类型。可以用语句：

如：

声明为String（变长）的变量最长可以串储约20亿个字符，见表3-1，如果要声明定长的String变量，就在声明时指定它可以存储的数据的长度，如：

指定变量的数据类型后，该变量只能存储指定类型的数据，而不能存储其他类型的数据。

使用变量类型声明符

只有部分数据类型可以使用类型声明符，如表3-3所示。

表3-3　类型声明符

数据类型

类型声明字符

Integer

%

Long

&

Single

!

Double

#

Currency

@

String

$

声明多个变量

声明多个变量，可以写在同一个Dim后面，变量名之间用逗号隔开。

也可以用不同的语句声明：

如果不指定变量类型

什么是Variant

Variant类型也称为变体型。

之所以称为变体，是因为Variant类型的变量可以根据需要存储的数据类型改变自己的类型与之匹配。就像一个无穷大的大水缸，不管你有多少斤酱油都可以装在里面，不管是什么东西都可以装在里面。

为什么要声明变量类型

同上街打酱油一样，尽管大水缸可以装下任意多的酱油，但如果预先已经知道自己只打一斤酱油，你会不会选择背着大水缸去？

相比水缸，带着酱油瓶会走得更快。计算机也一样，运行程序时，数据占用的字节越小，程序运行就越快，所以，声明变量为合适的数据类型是一个好习惯。

Variant类型比其他数据类型占用更大的存储空间（见表3-1），因此，编写VBA程序时，除非必须需要，否则应避免声明变量为Variant类型。

强制声明所有变量

方法一：在模块的第一句手动输入代码："Option Explicit"。

Step 1：插入一个模块，在代码窗口中输入下面的程序，如图3-4所示。

图3-4　强制声明变量

Step 2：运行程序，出现提示，如图3-5所示。

图3-5　执行程序后出错

方法二：按图3-6所示设置完成后，VBA会在每个模块的第一句自动写下"Option Explicit"而无需用户手动输入。

图3-6　设置强制声明变量

还可以这样声明变量

如：

变量的作用域

我家厨房里的酱油瓶只供我的家人使用，因为它只属于我家。村头的那口老井，谁都可以在里面打水，因为它是全村共有的。

酱油瓶和水井的作用域不同，决定了哪些人有资格使用它。

变量的作用域决定变量可以在哪个模块或过程中使用。VBA中的变量有3种不同级别的作用域，如表3-4所示。

表3-4　变量的作用域

作用域

描述

单个过程

在一个过程中使用Dim或Static语句声明的变量，作用域为本过程，即只有声明变量

的语句所在的过程可以使用它。这样的变量称为本地变量

单个模块

在模块的第一个过程之前使用Dim或Private语句声明的变量，作用域为声明变量

的语句所在模块里的所有过程，即该模块里所有的过程都可以使用它。这样的变量

称为模块级变量

所有模块

在一个模块的第一个过程之前使用Public语名声明的变量，作用域为所有模块，即所

有模块里的过程都可以使用它。这样的变量称为公共变量

图3-7∼图3-9所示为不同类型的变量的声明语句。

图3-7　声明本地变量

图3-8　声明模块级变量

图3-9　声明公共变量

注意：公共变量必须在模块对象中声明，在工作表、窗体等对象中，即使使用Public语句声明变量，该变量也只是模块级变量。

把数量存储到变量里

把数据存储到变量里，称为给变量赋值。

如果给文本、数值、日期等数据型变量赋值，语句为：

给变量赋值后，当使用这个数据时，可以直接使用变量名称代替对应的数据。如：

这个程序定义一个String型的变量，然后给变量赋值，最后把变量的值定入活动工作表的A1单元格中。运行程序，结果如图3-10所示。

图3-10　使用变量

如果给对象变量（Object型，如单元格）赋值，语句为：

如：

这个程序在Sheet1工作表的A1单元格中输入文本“欢迎来到Excel Home论坛”，运行程序，结果如图3-11所示。

图3-11　使用对象变量

练习小课堂

如果要声明变量存储表3-5中的职工信息，请写出声明变量和给变量赋值的语句，把表格的内容补充完整吗？

表3-5　变量存储表

参考答案

3.3.3　使用常量

常量通常用来存储一些固定的、不会被修改的值，如圆周率、个人所得税的税率等。

常量也需要声明，声明常量不但要指定常量的名称及数据类型，还要在声明的同时给常量赋值，并且赋值后的常量不能再重新赋值。

添加模块

如：

常量也有作用域

同声名变量一样，在过程的中间使用Const语句声明的常量为本地常量，只可以在声明常量的过程里使用；如果在模块的第一个过程之前使用Const语句声明常量，该常量将被声明为模块级常量，该模块里的所有过程都可以使用它；如果想让声明的常量在所有模块中都能使用，应在模块里的第一个过程之前使用Public语句声明它可参阅图3-7、图3-8、图3-9。

3.3.4　使用数组

什么是数组

数组也是变量，是同种类型的多个变量的集合。

1瓶酱油是1个变量，商店里，货架的第1层摆着5瓶酱油，如图3-12所示。

图3-12　货架上的酱油

5瓶酱油就是5个变量。因为5个变量都是酱油，所以可以把5个变量看成是由5个元素组成的一个数组，用“酱油”这个名称统一称呼它们。“酱油”是数组的名称，5是数组的元素个数。

怎么表示数组里的一个元素

客人让售货员去货架上取酱油：“左边第2瓶。”

售货员心里默数：“1、2，对，就是你。”

索引号指明元素在数组里的位置，把它和其他元素区别开来。所以，客人要的这瓶酱油用VBA代码可以表示为：

如果想表示货架上的第4瓶酱油，代码为：

数组有什么特点

（1）数组共享同一个名字，即数组名；

（2）数组由多个同种类型的变量组成；

（3）数组中的元素按次序存储在数组中，通过索引号进行区分；

（4）数组也是变量。

练习小课堂

生活中很多地方都在使用数组。七年级（6）班有50个同学，你知道“七年级6班（12）”表示什么吗？如果想表示七年级（6）班第35位同学用VBA代码该怎么写？

参考答案

（1）“七年级6班（12）”表示七年级6班的第12位同学。

（2）七年级6班的第35位同学用VBA代码表示为：七年级6班（35）。

声明数组

数组有大小。数组的大小告诉VBA，这个数组最多可以存储多少个元素。

初一学生报名入学后开始分班级，校长说：“七6班分50个学生”，50就确定了“七6班”这个数组的大小：这个班最多只能有50个同学。

校长的这个举动就是在声明数组。声明数组除了要指定数组名称及数据类型，还应指定数组的大小。

所以，“七6班”这个数组用VBA代码可以这样声明：

给数组赋值

分班后，班主任老师拿着学生花名册开始给同学编学号。1号是张青，2号是邓成……50号是冯吉。这就是给数组赋值的过程。

给数组赋值，同给变量赋值一样。

如要把“孔丽”这个字符串赋给一维数组arr中的第20个元素，代码为：

给数组赋值时，要分别给数组里的每个元素赋值，赋值的方法与给变量赋值相同。

给学生分班和编学号的过程可以用VBA代码写成：

声明数组时，也可以用一个自然数指定数组元素的大小，该自然数为数组的最大索引号，如：

但是，如果在模块的第一句写上"Option Base 1"，尽管只使用一个自然数确定数组的大小，数组起始索引号也是1，而不是0。

练习小课堂

试一试，用VBA代码声明一个10个元素的Integer类型的数组，并将1到10的自然数保存到数组里，你能做到吗？

参考答案

或

数组的维数

无论货架上的5瓶酱油，还是七6班的50个同学，都可以把它们看成是整整齐齐排成一个横排的元素。

第1个，第2个，第3个……第20个……第50个，总是可以这样引用它们。

像这样排成一个横排的数组，称为一维数组。除了一维数组，在VBA中还可以使用多维数组。

货架共有3层，每层5瓶酱油，这时，数组里的元素不再是排成一个横排而是三个，或者说，这个数组由三个一维数组组成，如图3-13所示。

图3-13　三层货架上的酱油

这样由多个横排组成的数组称为二维数组，二维数组可以看成由多个一维数组组成。

买酱油的客人说：“我要第2层的第4瓶。”写成VBA代码就是：

声明多维数组

货价有3层，每层5瓶酱油。如果要声明这个数组，语句为：

这个语句可以改写为：

练习小课堂

七年级有8个班，每班50个同学，你能声明一个名称为“七年级”的二维数组保存这8个班同学的姓名吗？如果要把七年级7班的第30个同学的姓名“张林”赋给数组里对应的元素，你知道代码应该怎么写吗？

参考答案

如果有3个货架，每个3层，每层摆5瓶酱油，如图3-14所示。

图3-14　摆满3层货架上的酱油

“第2个货架第3层的第4瓶酱油。”这也是数组“酱油”里的1个元素，用VBA代码表示为：

这样的数组，可以视为由多个二维数组组成，称为三维数组。还有四维、五维，甚至更多维的数组。

在Excel里，写在一个单元格里的数据就像货架上的一瓶酱油，工作表中的一行就像一层货架，一张工作表或一个单元格区域就是一个多层的货架，两张工作表就是两个货架，两个工作簿就是放着相同货架的两个商店……

练习小课堂

如果1个学生姓名占1个单元格，把1个存储100个学生姓名的一维数组“七年级”写入Excel工作表的单元格里，会占多大的区域？

参考答案

因为1个单元格存储一个学生姓名，所以把存储100个学生姓名的一维数组写入Excel工作表中应占同一行里连续的100个单元格，如：A1:CV1区域。

声明动态数组

如果在声明数组时，不能确定会往这个数组里存储多少个元素，即不能预知数组的大小，可以在首次定义数组时括号内为空，写成：

然后在程序中使用ReDim语句重新指定它的大小。

如：A列有很多职工姓名，想把这些职工姓名存储在数组arr中，但预先并不知道A列的职工姓名有多少个，在定义数组时代码可以这样：

用这样的方式声明的数组称为动态数组。

注意：已经定义大小的数组同样可以用ReDim语句重新指定它的大小。

其他常用的创建数组的方式

方法一：使用Array函数创建数组

运行上述代码，结果如图3-15所示。

图3-15　使用Array函数创建数组

方法二：使用Split函数创建数组

Split函数把一个文本字符串按照指定的分隔符分开，返回一个一维数组，数组最小索引号是0。

运行上述代码，结果如图3-16所示。

图3-16　使用Split函数创建数组

方法三：通过Range对象直接创建数组

如果想把一个单元格区域的值直接存储到数组里，可以直接把单元格区域的值赋给变量名。

如：

运行上述代码，运行结果如图3-17所示。

图3-17　通过Range对象创建数组

UBound和LBound函数

使用UBound和LBound函数可以计算数组的最大和最小索引号。

一个一维数组arr，要想知道它的最大索引号是多少，代码为：

如果想知道它的最小索引号，代码为：

如果想知道数组有多少个元素，可以使用代码：

如：

运行上述代码，结果如图3-18所示。

图3-18　使用UBount和LBound函数

如果是一个多维数组，求它的最大或最小索引号，还需指定数组的维数，如：

运行上述代码，结果如图3-19所示。

图3-19　数组的最大索引号

Join函数

Join函数将一个一维数组里的元素使用指定的分隔符连接成一个新的字符串。

运行上述代码，结果如图3-20所示。

图3-20　使用Join函数

将数组写入单元格区域

如想将一维数组arr里的第23个元素写入活动工作表中的A1单元格，代码为：

也可以将数组里的所有元素批量写入一个单元格区域：

运行上述代码，结果如图3-21所示。

图3-21　将一维数组批量写入单元格区域

无论是一维数组还是二维数组，将数组批量写入单元格区域时，单元格区域的大小必须与数组的大小一致，如：

运行上述代码，结果如图3-22所示。

图3-22　单元格的大小必须与数组的大小一致

3.4　集合、对象、属性和方法

3.4.1　对象，就像冰箱里的鸡蛋

什么是对象

对象就是东西，是用代码操作和控制的东西，属于名词。

打开工作簿，工作簿就是对象；复制工作表，工作表就是对象；删除单元格，单元格就是对象……

对象的层次结构

厨房里放着冰箱，冰箱里有碗，碗里装着早餐要吃的鸡蛋。无论是厨房、冰箱、碗还是鸡蛋，都是东西，都是对象，如图3-23所示。

图3-23　厨房的结构图

厨房里除了冰箱，还有消毒柜和电饭锅；冰箱里放着装有鸡蛋的碗，还放着装着水果的盘子和装着牛奶的瓶子，如图3-23所示。

图3-24　厨房里的多个对象

一个Excel工作簿就像一间大厨房，一个工作簿里可以有多张工作表，一张工作表里有多个单元格区域，如图3-25所示。

图3-25　工作簿里的对象

一个对象可以包含其他对象，同时又包含在其他对象里，不同的对象总是这样有层次地排列着。

集合——多个同类型的对象

集合也是对象，是对多个同种类型的对象的统称。

冰箱里有很多碗，无论装着鸡蛋还是瘦肉，都属于同一类对象，可以统称为“碗”。但是这个集合里并没有装牛奶的瓶子，因为瓶子不是碗，和碗不属于一类。

一个打开的工作簿，里面有多张工作表，无论工作表的名称是什么，表里保存什么数据，它们都属于工作表集合，即：Worksheets。

怎样取到装鸡蛋的碗

要吃鸡蛋，让孩子去取。

“去厨房，把冰箱里装着鸡蛋的碗拿来。”碗存放的地点（厨房的冰箱里）以及碗的特征（装着鸡蛋）都要介绍清楚，这样，孩子才不会弄错。

VBA中怎样取到集合里的一个对象

取到想要取的对象，称为“引用对象”。

很多个工作簿，若干张工作表，数不清的单元格，怎么表示"Book1"工作簿中"Sheet2"工作表中的"A2"单元格？

就像取冰箱里装鸡蛋的碗一样，在哪间房的冰箱里拿，拿什么碗，都要叙述清楚。

引用对象就像引用硬盘上的文件，要按从大到小的顺序逐层引用。

但并不是每一次引用对象都必须严谨地从第1层开始。

如果Book1工作簿是活动工作簿，前面的代码可以写为：

如果Sheet2工作表是活动工作表，代码甚至还可以简写为：

3.4.2　对象的属性

什么是属性

每个对象都有属性。对象的属性可以理解为该对象包含的内容或具有的特点。

苹果是有颜色的，颜色就是苹果的属性。我的衣服，衣服就是我的属性。

Sheet2工作表的A2单元格，A2单元格是Sheet2工作表的属性；A2单元格的字体，字体是A2单元格的属性；字体的颜色，颜色是字体的属性。

“的”字后面的，总是“的”字前面的对象的属性。

在VBA中，用点（.）代替“的”字：

我的衣服→我.衣服

Sheet2工作表的A2单元格的字体的颜色→Worksheets("Sheet2").Range("A2").Font.Color

对象的相对性

某些对象的某些属性，返回的是另一个对象，如Sheet1工作表的Range属性，返回的是e对像（即单元格），但单元格本身也是一种对象。作为一种对象，它也有自己的属性，如字体（Font），而字体也是对象，也有属性，如颜色。

对象和属性是相对的。单元格相对于字体是对象，相对于工作表是属性。

如果想准确地知道Value（或其他）是方法还是属性，可以在【代码窗口】中将光标定位到它的中间，按F1键，查看帮助里的信息，如图3-26所示。

图3-26　查看Value的帮助信息

3.4.3　对象的方法

什么是方法

方法是在对象上执行的某个操作，属于动词。

如剪切单元格，剪切是在单元格上执行的操作，就是Range对象的方法；选中工作表，选中是在工作表上执行的操作，就是Wroksheet对象的方法；保存工作簿，保存就是Workbook对象的方法……

方法和属性的区别

属性返回对象包含的内容或具有的特点，如颜色、大小等。方法是对对象的一种操作，如选中、激活等。

怎样分辨方法和属性

除了通过查看帮助来分辨属性和方法，还可以在【代码窗口】中按<Ctrl+J>组合键，或者在对象的后面写上点，在自动显示的【属性／方法列表】中根据图标的颜色来分辨，带绿色图标的项是方法，其他的都是属性，如图3-27所示。

图3-27　对象的属性／方法列表

如果在对象的后面输入点后没有显示【属性／方法列表】，则先在【选项】对话框的【编辑器】选项卡里勾选【自动列出成员】复选框，如图3-27所示。

图3-28　设置自动列出成员

3.5　连接的桥梁，VBA中的运算符

程序执行的过程就是对数据进行运算的过程。不同的数据类型可以进行不同的运算，按数据运算类型的不同，VBA里的运算符主要分为算术运算符、比较运算符、连接运算符和逻辑运算符。

3.5.1　算术运算符

算术运算符用于算术运算，返回值的类型为数值型。

3+1，5−4，6*8，7^4，这些都是算术运算。算术运算符包括+、−、*、/、\、^、Mod等，各运算符的作用如表3-6所示。

表3-6　算术运算符及作用

运算符

作用

示例

+

求两个数的和

5+9=14

−

求两个数的差；求一个数的相反数

8−5=3

−3=−3

*

求两个数的积

6*5=30

/

求两个数的商

5/2=2.5

\

整除（两数相除取商的整数）

5\2=2

^

指数运算（求一个数的某次方）

5^3=5*5*5=125

Mod

求模运算（两数相除取余数）

12 Mod 9=3

3.5.2　比较运算符

比较运算符用于比较运算，如比较两个数的大小。返回值为Boolean型，只能为True或False。比较运算符及其作用如表3-7所示。

表3-7　比较运算符及作用

在图3-29所示的成绩表中，如果要知道第一条记录中学生的总分是否达到500分，语句为：

如果要判断B2单元格里的考生是否姓李，代码为：

图3-29　学生成绩表

练习小课堂

如果想知道B2单元格的考生姓名里是否有“刚”字，代码该怎么写？

参考答案

除了*，Like运算还可以使用其他通配符。VBA中的通配符及其作用如表3-8所示。

表3-8　VBA中的通配符

通配符

作用

示例

*

代替任意多个字符

“李家军”Like“*家*”=True

？

代替任意的一个字符

“李家军”Like“李??”=True

#

代替任意的一个数字

“商品5”Like“商品#”=True

[charlist]

代替位于charlist中的任意一个字符

"I" Like "[A-Z]"=True

[!charlist]

代替不在charlist中的任意一个字符

"I" Like "[!H-J]"=False

练习小课堂

根据图3-29所示的学生成绩表，用学过的运算符，你能写出其他表达式吗？请任意写出4个填在下面的表格里，然后再继续后面的内容。

参考答案

表达式

说明

Range("J2").Value>= 500

判断J2的分数是否达到500

Range("D3").Value > 90

判断D3的分数是否大于90

Range("C4").Value > Range("D4").Value

判断C4的分数是否大于D4的分数

Range("B5").Value Like "孟*"

判断B5的学生是否姓孟

Range("B5").Value Like "*军"

判断B5的学生姓名是否以“军”字结尾

3.5.3　连接运算符

连接运算符用来连接两个文本字符串，有+和&两种，如图3-30所示。

图3-30　在立即窗口中使用连接运算符

+可以用作算术运算的加运算，也可以用于文本连接运算。如果+运算符两边的表达式都是文本字符串，则执行连接运算；如果+运算符两边的表达式包含数值，则执行算术运算，如图3-31所示。

图3-31　在立即窗口中使用+运算符

当使用&运算符时，无论运算符左右两边是何种尖型的数据，都执行连接运算。

3.5.4　逻辑运算符

逻辑运算符用于判断逻辑运算式的真假，参与运算的数据为逻辑型数据，返回结果为Boolean型，只能为True或False。辑逻运算符及其作用如表3-9所示。

表3-9　逻辑运算符及作用

图3-29所示的学生成绩表，如果想判断第一条记录中语文、数学两个学科中是否有及格（大于或等于60分）的科目，语句为：

如果语文成绩和数学成绩分别为85分和49分，则这个表达式的计算过程可以用脱等式表示为：

3.5.5　应该先进行什么运算

在VBA中，要先处理算术运算符，接着处理连接运算符，然后处理比较运算符，最后再处理逻辑运算符。可以用括号来改变运算顺序。

运算符按运算的优先级由高到低的次序排列为：括号→指数运算（乘方）→求相反数→乘法和除法→整除（两数相除取商的整数）→求模运算（两数相除取余数）→加法和减法→字符串连接→比较运算→逻辑运算，如表3-10所示。

表3-10　运算符的优先级

练习小课堂

用脱等式计算出下面表达式的结果。

2580 > (1000 + 4000) Or 150 < 236

“学号：” & 1006110258 Like “*258” And (125 + 120 + 140) > 400

参考答案

3.6　内置函数

3.6.1　VBA中的函数

合理使用函数不但可以节省处理数据的时间，提高工作效率还可以降低编程的难度，减少编写代码的工作量。

作为一种编程语言，VBA中也有函数。

在VBA中使用VBA内置函数与在工作表中使用工作表函数类似，如想知道当前的系统时间可以用Time函数（见图3-32）。

图3-32　利用Time函数返回当前系统时间

3.6.2　VBA中有哪些函数

VBA中的所有函数都可以在帮助里找到，如图3-33所示。

图3-33　在VBA帮助中查看函数

函数很多，我们并不用很精确地全部记住它们，只需大概了解即可，如果在编写代码时，忘记了某个函数的拼写，可以在【代码窗口】中先键入"VBA."，系统会自动显示【函数列表】供你选择，如图3-34所示。

图3-34　自动显示函数列表

3.7　控制程序执行，VBA的基本语句结构

3.7.1　If…Then语句

应该选择什么问候语

“应该选择什么问候语？”这是小丽的烦恼。

她带着这个问题走进了VBA课堂……

If语句来帮忙

针对小丽的问题，老师给她提了一个建议。

把这句VBA代码译为汉语就是：

练习小课堂

试一试，如果中午12点后提示下午好，你能仿照老师写的代码写一个这样的语句吗？

参考答案

当需要判断两次时

如果时间在12点之前，提示“上午好！”，否则提示“下午好！”，像这样的问题可以用"If…Then"来编号不同的句子。如：

相当进行两次比较运算时，这种语句并不是最佳的选择，可以只用一个if语句代替它：

练习小课堂

你还能用"If…Then…Else"造其他的句子吗？

“如果活动工作表的A1单元格为空，则提示‘没有输入内容’，否则提示‘已经输入内容’”。把这个句子翻译出来，并运行它，看自己写对了吗？

参考答案

如果你不习惯阅读一行很长的代码，还可以把If语句写成块的形式，我们也不推荐将二次判断的If语句写成一行。

这些代码是怎么工作的

“如果……那么……否则”，If语句总是可以用这个句式来描述它的执行流程。结合这个思路，可以给If语句绘制出执行的流程图，如图3-35所示。

图3-35　If语句的流程图

你知道吗？把程序写在【代码窗口】里，将光标定位在程序的中间，可以按F8键逐句执行语句观察程序的执行流程。

更多判断的时候

不仅要判断时间是否大于中午12点，还要判断是否大于下午6点。需要对条件判断两次以上，这是小丽遇到的新问题。

小丽带着这个问题去求助老师，老师教给她另一种解决方法。

3.7.2　Select Case语句

尽管使用If语句可以有效地解决多次判断的问题，当面对在3种或更多策略中做出选择时，使用Select Case语句会更适合。

练习小课堂

第3章3.7.2小节中的程序，如果省略Case Else子句，应该怎样写？

参考答案

Select Case语句的执行流程，与If…Then…ElseIf语句一样，可以在【代码窗口】中按F8键观察得到：程序将Select Case后面的测试表达式与各个Case子句后面的表达式列表进行对比，如果测试表达式的值在表达式列表中，则执行对应的子句，然后退出整个语句块，执行End Select后面的语句，否则将继续进行判断，如图3-36所示。

图3-36　Select Case语句的执行流程图

因为Select Case语句一旦找到匹配的值后即跳出整个语句块，所以，为了减少判断的次数，在设置条件时，应尽量把最有可能发生的情况写在前面。

练习小课堂

表3-11是给学生成绩评定等级的程序，其中有部分代码或代码说明没有写出来。请你把它补充完整，然后运行程序，看自己都写对了吗？

表3-11

参考答案

学会用判断语句选择合适的问候语，小丽很高兴。笑过之后，她惊奇地发现，原来工作中每天都在做着类似的判断。

图3-37所示为单位职工考核得分表。

图3-37　职工考核得分表

现要根据考核得分，按图3-38所示的星级评定标准为职工评定星级。

图3-38　星级评定标准

小丽决定用Select Case语句编写一个程序来解决这个问题。

3.7.3　For…Next语句

小丽对自己写的程序很满意。

但是，在工作中需要处理的数据却复杂得多，如图3-38所示。

图3-39　实际上需要处理的数据

小丽求助老师，老师说，可以使用For…Next循环语句批量处理。

可以结合For…Next语句的执行流程图来理解这个程序，如图3-40所示。

图3-40　For…Next循环语句执行流程图

For…Next语句总是写成这样：

老师给小丽的程序是这样的：首先定义循环变量i的初值和终值分别是2和19，当程序执行到For语句时，判断变量i的值是否大于终值19，如果不大于，则执行For和Next中间的语句，直到Next语句，再返回For语句处再次进行判断，直到循环变量的值大于终值19，退出循环，执行Next后面的语句。

练习小课堂

（1）根据代码说明，把表3-12中的程序补充完整，让程序运行后，能把100以内的正奇数按1,3,5,7……的顺序写进A列的单元格里。

表3-12

（2）你还能用同样的方法找出100以内能被3整除的数，并按顺序写入A列单元格吗？试一试。

参考答案

（1）

（2）

或

3.7.4　Do While语句

如果使用Do While语句来解决3.7.3小节中为职工评定星级的问题，可以把第一条记录作为起点，依次判断H列的单元格是否为空。

如果不为空，则执行Select Case语句进行星级评定，直到单元格内容为空退出循环。

还可以在结尾处判断循环条件，语句为：

Do While循环语句是当逻辑表达式的值为False时退出循环，但结尾判断式的语句是在执行一次循环体后再判断循环条件，因此，当循环条件一开始就为False时，比开头判断式要多执行一次循环体，其他时候执行次数相同。

练习小课堂

试一试，用结尾判断式的Do While语句来解决3.7.3小节中为职工评定星级的问题。

参考答案

3.7.5　Do Until语句

Do Until语句也有开头判断和结尾判断两种语句形式。

开头判断式：

结尾判断式：

与Do While语句不同的是：Do While语句是当逻辑表达式的值为False时退出循环，而Do Until语句是当逻辑表达式的值为True时退出循环。

练习小课堂

试一试，分别用Do Until语句的两种形式编写程序来解决3.7.3小节中评定职工星级的问题。

参考答案

结尾判断式：

结尾判断式：

3.7.6　For Each…Next语句

当前活动工作簿中有许多工作表，但并不知道数量。如果要把所有工作表的名称按次序写入活动工作表的A列，For Each…Next是更适合的循环语句。

无论工作簿中有多少张工作表，执行程序后，都会将所有工作表的标签名称依次写入当前活动工作表的A列单元格中，如图3-41所示。

图3-41　把工作表标签名称写入A列

使用For Each…Next循环语句时，不需要定义循环条件，如果要在一个集合或一个数组中循环时，同其他循环语句相比，For Each…Next要灵活得多。

注意：当在一个数组里循环时，不能对数组元素进行赋值或重新赋值，对于已经赋值的对象数组也只能修改元素的属性。

练习小课堂

用For Each…Next语句编写一个程序将1到100的自然数输入A1:A100单元格区域。

参考答案

3.7.7　其他的常用语句

GoTo语句，让程序转到另一条语句去执行

GoTO地点，译成中文是“去到指定的地点”。在VBA中，GoTo语句也可以这样理解。

在VBA中，指定地点可以在目标代码所在行前加上一个带冒号的字符串或不带冒号的数字作为标签，然后在GoTo的后面写上标签名。如：

GoTo语句大多用于错误处理时，参阅7.4小节，因为它会影响程序的结构，增加阅读和调试的难度，所以除非必须需要，否则应尽量避免使用GoTo语句。

With语句，让代码更简单

当需要对相同的对象进行多次操作时，往往会编写一些重复的代码。如：

这是一个设置A1单元格字体的程序。因为是对同一个对象的多个属性进行设置，所以4行代码的前半部分都是相同的。如果你不想多次重复录入相同的代码，可以用With语句来简化输入。

合理使用With语句，不但可以减少代码的输入量，还能提高程序的运行效率。

3.8　Sub过程，基本的程序单元

做什么事都有一个过程。

烧水，倒水，拿毛巾……倒水，这是洗脸的过程。买菜，洗菜，切菜，炒菜，盛菜，这是做菜的过程。打开工作簿，输入数据，保存工作簿，退出Excel程序，这是数据录入的过程。

过程就是做一件事情的经过，由不同的操作按先后顺序排列、组合起来。

3.8.1　关于VBA过程

什么是VBA过程

打开工作簿，输入数据，保存工作簿，退出Excel程序。这是一个录入数据的过程。把这些操作写成VBA代码，按先后顺序组合起来就是一个VBA过程。

所以，VBA过程就是完成某个给定任务的代码的有序组合。

VBA里有哪些过程

VBA的基本过程有Function过程和Sub过程两种。

3.8.2　编写Sub过程需要了解的内容

关于Sub过程

录制的宏就是Sub过程，录制宏也只能生成Sub过程。

可以录制一个复制A1:A8单元格到C1:C8单元格的宏，结合宏来认识Sub过程的结构。

知道了过程的结构，就可以依葫芦画瓢，像做填空题一样随心所欲地编写Sub过程了。

应该把过程写在哪里

宏保存在哪里，还记得吗？是的，模块。过程也是保存在模块里。

和录制的宏一样，过程保存在模块里，所以编写过程，应先插入一个模块来保存它（参阅2.4.1小节），插入模块后，双击激活它的【代码窗口】，就可以在【代码窗口】中编写过程了。

练习小课堂

学会编写Sub过程后，你有什么想要完成的操作？试着编写一个过程，让程序替你完成。

参考答案

答案参考书中的任意一个Sub过程。

并不是只有模块对象才能保存过程，Excel对象（或窗体对象）也能保存过程，如图3-42所示。

图3-42　Excel类模块

为了避免发生错误，建议将Sub过程和Function过程保存模块对象中。

和一个文件夹可以保存多个文件一样，一个模块也可以保存多个过程。如果需要，你也可以像给文件分类那样，建不同的模块来保存功能不同的过程。

声明Sub过程，规范的语句

尽管一个Sub过程可以包含任意多的代码，但就像做20桌饭菜，为了更有效、更有条理地把任务完成，总是需要有明确的分工：张三洗菜，负责完成洗菜的过程，李四烧饭，负责烧饭的过程，最后把大家的过程合起来就完成了整个任务。

分工后，哪个过程出现问题，比如菜没洗干净，就直接去找张三。

编程也一样，当需要处理的任务比较复杂时，可以用多个小过程去完成，每个过程负责完成一个特定的、较为简单的目的，最后通过执行这些小过程来完成最终目的。

3.8.3　从另一个过程执行过程

下面是在3.7.1小节中编写的过程：

如果想在另一个过程里使用代码执行它，常用的方法有如下三种。

方法一：输入过程名称以及参数，参数用逗号隔开。

方法二：在过程名称以及参数前使用Call关键字，参数用括号括起来，并用逗号隔开。

方法三：利用Application对象的Run方法，语句形式如下。

3.8.4　过程的作用域

过程按作用域的不同分为公共过程和私有过程。

公共过程

公共厕所、公共汽车……戴着“公共”的帽子，意味着这个东西大家都可以使用。

私有过程

家里买了一辆私家车，肯定不会让街上的每个人都坐着它去上班。哪辆是公共汽车，哪辆是私家车，为了区别开来，要给它贴上特殊的标识。

如果想把模块中的所有过程全部声明为私有过程（包括已经声明为公共过程的过程），只需在模块中第一个过程之前写上"Option Private Module"即可，如图3-42所示。

图3-43　将模块里所有过程声明为私有过程

谁有资格调用私有过程

如果一个过程被声明为私有过程，只有这个模块里的过程才能调用它。如果想让其他模块中的过程也能调用，应该把它声明为公共过程。

私有过程不会在【宏】对话框里显示，如图3-44所示。

图3-44　私有过程不在宏对话框里显示

练习小课堂

编写过程，分别调用不同模块的私有过程和公共过程，都能调用吗？

参考答案

可以调用其他模块中的公共过程，不能调用其他模块中的私有过程。

3.9　自定义函数，Function过程

Function过程也称为函数过程。编写一个Function过程，就编写了一个函数。

函数可以完成很多复杂的计算。如想求A列的和，可以用SUM函数；想知道A列有多少个“张三”，可以用COUNTIF函数。

Excel并没有提供解决这个问题的工作表函数。这时，可以根据需要自己编写一个。

3.9.1　试写一个函数

Function过程同Sub过程一样，都是保存在模块里，所以，在编写函数前，应先插入一个模块（参阅第2章2.4.1小节）来保存它。

插入模块后，双击模块激活它的【代码窗口】，即可开始编写函数。

如果想让函数生成一个1∼10之间的随机整数，完整的程序为：

3.9.2　使用自定义函数

自定义的函数可以在工作表中使用，也可以在VBA过程里使用。

在工作表中使用自定义函数

在工作表中使用自定义函数同使用工作表函数类似，如图3-45所示。

图3-45　在工作表中使用自定义函数

自定义的函数可以在【插入函数】对话框里找到，如图3-46所示。

图3-46　查看自定义函数

自定义的函数可以和其他函数嵌套使用，如图3-47所示。

如：

图3-47　嵌套使用自定义函数

在VBA过程中使用自定义函数

在VBA中使用自定函数与使用VBA的内置函数一样，如图3-48所示。

图3-48　在VBA中使用自定义函数

3.9.3　怎么统计指定颜色的单元格个数

问题一：单元格是什么颜色

在Excel里，可以通过RGB函数指定不同的颜色，如想将活动工作表中B1单元格的底纹设置为黄色，代码为：

怎么统计指定颜色的单元格个数

要知道A1:A10里有少个黄色单元格，可以让VBA替我们数一下，是黄色的累计，不是黄色的排除。

在工作表里输入函数，可以看到函数返回的计算结果，如图3-49所示。

图3-49　统计黄色单元格的个数

还可以通过颜色索引号来引用某个颜色，在Excel 2003中，默认情况下，黄色的颜色索引号为6，所以上面的代码还可以写为：

ColorIndex属性引用的是某个索引号上的颜色，而Color返回的是真实颜色。因为颜色的索引号可以更改，所以使用ColorIndex属性引用到的颜色不一定都相同，因此函数不一定能返回正确的结果。

用参数指定计算区域

在工作表中使用函数时，可以通过函数参数指定计算统计的单元格区域。自定义函数也可以使用参数。

如果需要统计的单元格区域不是固定的，可以用变量代替程序里的A1:A10单元格区域，让用户在使用自定义函数时通过函数参数指定区域。

为函数设置参数后，如果要统计A1:C10中黄色底纹单元格的个数，输入公式“=count color(A1:C10)”即可，如图3-50所示。

图3-50　使用参数的自定义函数

给自定义函数指定第2参数

还可以给函数设置第2参数，通过第2参数指定要统计的颜色。

在工作表中使用自定义的函数，如图3-51所示。

图3-51　用参数指定需要统计的颜色

如果需要，还可以为函数添加第3参数，第4参数……

设置自定义函数为易失性函数

有时，当工作表重新计算之后，自定义函数并不会重新计算。如在工作表中使用第3章3.9.1小节中生成随机的自定义函数后，按F9键重算工作表，函数并不会生成新的随机值。

但如果在函数开始添加一条语句，添加语句后，无论何时重新计算工作表，函数都会重新计算，得到新的结果。

注意：使用Application.Volatile True语句是将自定义函数声明为易失性函数。当工作表发生重算后，易失性函数会重新计算函数的值。但因为更改单元格的背景颜色不会让工作表重算，所以，无论是否使用该语句，更改单元格的颜色后，本节中编写的自定义函数CountColor都不会重新计算。

3.9.4　声明函数过程，规范的语句

声明Function过程的语句和声明Sub过程的语句类似。同Sub过程一样，Function函数也分公共函数和私有函数，如果想声明一个私有函数，请一定要加上Private关键字。

3.10　合理地组织程序，让代码更优美

编程就像做事，得讲究条理。

先做什么，后做什么，安排好了，程序才不会改错。

除了在操作上要有条理之外，也应尽量让代码条理清晰，便于阅读。所以，在编程时，除了要遵循VBA的语法规则外，还应养成一些良好的习惯。

3.10.1　代码排版，必不可少的习惯

两篇同样的稿纸，是否经过精心排版，对读者的吸引力肯定不一样。要想让自己编写的程序清晰易懂，排版的过程也必不可少。图3-52所示为排版前后的代码。

图3-52　排版前后的代码

3.10.2　怎样排版代码

缩进，让代码更有层次

缩进可以使程序更容易阅读和理解，在VBA中，过程的语句要比过程名缩进一定的字符，在If、Select Case、For…Next、Do…Loop、With语句等之后也要缩进，一般缩进4个空格，如图3-53所示。

图3-53　缩进的代码

但在缩进某行或某块代码时，并不用手动在代码前敲入4个空格，可以选中代码块（如果是一行，只需将光标定位到行首而不用选中它），按下Tab键（或依次执行【编辑】→【缩进】菜单命令）即可将代码统一缩进一个Tab宽度，如图3-54所示。

图3-54　利用菜单命令缩进代码块

如果选中已缩进的代码，按<Shift+Tab>组合键（或依次执行【编辑】→【凸出】菜单命令），则将选中的代码取消缩进一个Tab宽度。

Tab宽度默认为4个空格，可以在【选项】对话框里修改如图3-55所示。

图3-55　设置Tab宽度

更改长行代码为短行代码

当一条语句过长时，可以在句子的后面输入一个空格和下划线（_），然后换行，把一行代码分成两行。如：

虽然可以把一行代码分成两行、三行甚至更多，但盲目分行却不是好习惯，一般当一行代码的长度超过80个字符时，才考虑分行。

把多行合并为一行

在第一行代码后加上英文冒号，可以接着写第二行代码。通过这样的方式可以把多行短代码合并成一行代码。

尽管可以把多行代码写在同一行，但是这样会给阅读增加许多麻烦，所以除非必须需要，否则并不提倡这样做。

3.10.3　注释，让代码的意图清晰明了

注释就像商品的说明书，介绍代码的功能及意图。

编写的程序有什么用途，可以通过注释语句作简要介绍，让代码更加易读易懂。

添加注释语句

注释语句以英文单引号开头，后面是注释的内容。可以放在代码的末尾，也可以单独写在一行，如图3-56所示。

图3-56　为代码添加注释

当注释语句单独成一行时，还可以使用Rem代替单引号。

相信我，多数人不出3个月就会忘记自己写的程序代码的用途。所以，哪怕只是为自己，也应该为较为重要的代码添加注释。

妙用注释

在调试程序时，如果不想运行某行代码，可以在代码前加上单引号（或Rem），让它成为注释语句，而不用删除它。当要恢复这些代码时，只要将单引号（或Rem）删除即可（如图3-57所示），这个技巧在调试代码时经常都会用到。

图3-57　注释某句不需要的代码

如果要注释或取消注释一块代码，还有简单的方法，如图3-58所示。

图3-58　批量添加注释

图3-58　批量添加注释（续）

如果要取消注释，把代码还原成普通代码，就选中注释代码块，单击【编辑】工具栏中的【解除注释块】按钮，如图3-59所示。

图3-59　批量取消注释

第4章

常用对象

只会心法和招式，有力无处使；只有棒，不懂要诀，只会乱打一通，都是失败！

编写VBA程序就像练打狗棒法。会心法和招式后，也要有根打狗棒才行。

在VBA中，对象就是碧绿如玉的打狗棒，而语法就是心法和招式，教我们如何控制手中的打狗棒，耍出变化精微、招术奇妙的棒法来。在前面我们已经修炼了编程的心法和招式，下面就一起来学习如何使用“棒”吧。

4.1　与Excel交流，需要熟悉的常用对象

4.1.1　VBA编程与炒菜

菜是怎么炒出来的

巧妇难为无米之炊，再聪明伶俐的媳妇只守着空灶台也煮不出香喷喷的饭菜，必须打开冰箱，取出瘦肉、葱、蒜……然后洗、切、炒，最后大勺一挥，一盘色香味美的鱼香肉丝才能摆上饭桌，如图4-1所示。

图4-1　炒菜的步骤

编程就如炒菜，盘子里的菜就是按炒菜的方法对材料进行加工写出的程序。

VBA编程需要的源材料就是VBA里的对象。

想要编写VBA程序，首先要懂得如何打开“冰箱”，在“冰箱”里找到合适的东西，取出并加工它。

这个“冰箱”在Excel里称为对象模型。

什么是对象模型

就像厨房里的东西一样，Excel中的对像总是层次分明地组织在一起，一个对象可以包含其他对象，也可以包含在其他对象里（参阅3.4节）。

这种对象的排列模式称为对象模型。Excel中的所有对象都可以在对象模型里找到。

怎么打开对象模型

图4-2　打开对象类型

4.1.2　VBA是怎么控制Excel的

VBA通过操作不同的对象来控制Excel

作为一个Excel用户，每天都在重复打开、关闭工作簿，输入、清除单元格内容的操作，这些操作都是在操作对象。

是的，我们每天都在用动作编程。

实际上，VBA程序就是用代码记录下来的一个或一组操作。如想在"Sheet1"工作表的A1单元格输入数值100，完整的代码为：

无论是用动作还是用代码，都是在操作对象。

所以，编写VBA程序，就是利用VBA语句引用对象并有目的地操作它。

4.1.3　应该记住哪些对象

VBA编程就像炒菜

菜市场的菜花样繁多，买菜时应该买什么？红烧鱼很香，但家里从来不吃，买菜时要不要买？

买菜只买需要的，而不用买下整个菜市场。认识对象也是如此，并不用记住所有的对象，只需要熟悉它的结构和组成，记住经常的即可。对于那些不常用或根本不会用到的，只要在需要用到时能熟悉打开帮助菜单，像查字典一样找到它就够了。

偶尔那么一天，忽然想吃红烧鱼了，出门打个的，告诉司机：“菜市场！”一去一来，十五分钟，搞定！

应该记住哪些常用的对象

不买的菜坚决不买，要买的也千万不要落下。

菜市场，一去一来，十五分钟，的确不远。

但是菜洗好了，发现没有买油，然后出门打的……

十五分钟后，炒菜炒到一半，却发现没有买辣椒，于是，再一次出门打的……

又十五分钟后，菜快炒好了，忽然发现没有买葱……

最后的最后，鱼香肉丝终于端上了餐桌。来之不易的饭菜，真不简单。

存在冰箱里的当然是生活常用品。

学习VBA，需要记住哪些常用的对象？想一想，日常工作中经常会操作哪些对象（见表4-1）。

表4-1　Excel VBA常用的对象

对象

对象说明

Application

代表Excel应用程序

Workbook

代表Excel中的工作簿，一个Workbook对象代表一个工作簿文件

Worksheet

代表Excel中的工作表，一个Worksheet对象代表工作簿里的一张普通工作表

Range

代表Excel中的单元格，可以是单个单元格，也可以是单元格区域

4.2　一切由我开始，最顶层的Application对象

打开对象模型，最顶端的Application对象是起点，它代表Excel程序本身，就像一棵树的根，Excel里所有的对象都以它为起点，生根发芽，开枝散叶。

实际编程时，会经常用到它的许多属性和方法。

4.2.1　ScreenUpdating属性

一个小故事

新来的老师站在讲台上，手指窗户边：“喂，那个边上的同学……对，就是你，头大的那位男孩，起来回答个问题。”

大头儿子，班上的学习委员，老师真会挑。

“38加25。”“63，”不假思索就回答了。

“再减15。”“48。”

“再减36。”“12。”

……

五年级的学生，二年级的问题，大头儿子有些不服气。

“老师，你能不能一次说完，我算有10步运算的。”

老师：“……”

如果不需要中间的结果，计算时把计算过程记在心里。

大头儿子说：“我很喜欢这样的问答方式。”

Excel里天天都在多步提问

在用Excel处理数据时，需要做的操作往往不只是一步。

上述代码执行的过程如图4-3所示。

图4-3　程序的执行过程

不显示计算结果到屏幕上

同样的程序，稍加修改后，再一次执行它，观察它的运行过程，如图4-3所示。

图4-4　程序的执行过程2

练习小课堂

对比两个程序的执行过程，你发现它们之间的区别了吗？设置ScreenUpdating属性有什么作用？试着小结一下。

参考答案

区别在于是否将程序运行过程中的计算结果显示到屏幕上。

设置ScreenUpdating属性为False，将看不到程序的执行过程，可以加快程序的执行速度，让程序显得直观、专业。

4.2.2　DisplayAlerts属性

烦人的删除工作表

这是一个删除工作表的小程序：

但是，程序运行后并不顺畅，如图4-5所示。

图4-5　删除工作表前的警告对话框

取消显示警告对话框

如果想取消显示对话框，只需要对程序作简单的修改：

修改完成后，再次运行程序，警告对话框彻底消失了。

DisplayAlerts属性，详细的介绍

Application对象的DisplayAlerts属性决定在程序运行中是否显示警告信息，默认值为True，如果不想在程序运行时被提示和警告信息打扰，可在程序开始时将属性设为False。但如果在程序中修改了该属性为False，在程序结束前请记得将它设回True。

4.2.3　EnableEvents属性

可以通过设置Application对象的EnableEvents属性来启用或禁用事件。

什么是事件

事件是能被Excel认识的一个操作动作（参阅5.1.2小节）。

Excel里的许多操作都会触发事件，如打开工作簿、关闭工作簿等。用户可以编写不同的代码来响应这些事件，当触发某个事件时，自动执行指定的代码。

自动写入单元格地址

编写一个程序，当选中工作表中的单元格时，自动在单元格中写入该单元格的地址，如图4-6所示。

图4-6　在工作表对象中输入程序

完成后返回该工作表区域，选中任意一个单元格，Excel会自动将该单元格的地址写入单元格中，如图4-7所示。

图4-7　自动填写单元格地址

这个程序是Worksheet_SelectionChange事件的应用，当用户更改选中的单元格时，自动运行Sub与End Sub之间的代码。

什么是禁用事件

禁用事件就是执行操作后不让事件发生。

如果禁用了事件，更改选中的单元格，Sub与End Sub之间的代码并不会运行。

在VBA里，可以设置Application对象的EnableEvents的属性为False来禁用事件。

练习小课堂

程序1和程序2是几乎完全相同的两个程序，用图4-6所示的方式将两个程序输入到不同的工作表对像中，然后分别在两张工体表中选中不同的单元格，看看程序运行的效果有什么不同？

程序1：

程序2：

参考答案

运行程序1，程序将陷入死循环，无法得到预想的结果。执行程序2后，能得到预期的结果。

设置EnableEvents属性为False后，当选中单元格后，Excel不会再自动运行程序，即禁用了该事件。

通过对比，你知道EnableEvents属性有什么作用吗？把自己的心得写下来。

4.2.4　WorksheetFunction属性

没有这样的函数，真遗憾

VBA里有许多内置函数可供使用，但在实际应用中，并不是所有的问题都能找到合适的函数来解决。

如想统计A1:B50单元格中数值大于1000的单元格有多少个，就没有现成的函数，需要编写Function或Sub过程来统计。

为什么不使用COUNTIF函数

因为VBA里没有COUNTIF函数。其实不只COUNTIF，SUMIF、TRANAPOSE、VLOOKUP等常用函数在VBA中也没有。

虽然VBA里没有这些函数，但并不意味着它们都不能在VBA中使用。在VBA里，可以使用Appplication对象的WorksheetFunction属性调用部分工作表函数。

这个问题，也可以使用工作表中的COUNTIF函数来完成。

注意：如果VBA里已经有了相同的函数，不能再引用工作表中的函数，否则会出错。如要使用Len函数计算"ABCDE"的长度，应该写成Len（"ABCDE"），而不能写成Application.WorksheetFunction.Len（"ABCDE"）。

4.2.5　给Excel梳妆打扮

Excel就像一个漂亮的姑娘，你可以随心所欲地打扮她。梳个漂亮的发型，画一画眉毛，换一件漂亮的衣服……小女孩的脸上有鼻子、眼睛、嘴巴等五官，Excel的脸上也有“五官”，如图4-8所示。

图4-8　Excel的界面

如果你不想看到她的某个“器官”，可以把它隐藏起来，如果你觉得她的单眼皮不好看，可以动手改造一下（参阅6.5节）。

练习小课堂

（1）可以设置Application对象的某些属性来更改Excel的界面。

运行Excel程序，进入VBE，在立即窗口里运行表4-2中的每句代码，然后把看到的结果写下来。

表4-2

在【立即窗口】中输入代码

修改区域

代码执行后的效果

Application.Caption = "我的Excel"

标题栏

Application.Caption = "Microsoft Excel"

标题栏

Application.DisplayFormulaBar = False

编辑栏

Application.DisplayStatusBar = False

状态栏

Application.StatusBar = "正在计算，请稍候……"

状态烂

Application.StatusBar = False

状态栏

ActiveWindow.DisplayHeadings = False

行标和列标

（2）可以更改的项目很多，如果你不知道该用什么代码，别忘记使用录制宏功能。

手动完成并录下表4-3列出的操作，然后将相应的代码填在表格里。

表4-3

代码执行后的效果

代码

隐藏工作表标签

隐藏水平滚动条

隐藏垂直滚动条

显示绘图工具栏

隐藏常用工具栏

隐藏网格线

参考答案

（1）

在立即窗口中输入代码

修改区域

代码执行后的效果

Application.Caption = "我的Excel"

标题栏

更改标题栏中显示的程序名称为“我的Excel”

Application.Caption = "Microsoft Excel"

标题栏

更改标题栏中显示的程序名称为默认的"Microsoft Excel"

Application.DisplayFormulaBar = False

编辑栏

隐藏【编辑栏】

Application.DisplayStatusBar = False

状态栏

隐藏【状态栏】

Application.StatusBar = "正在计算，请稍候……"

状态烂

更改【状态栏】中显示信息为“正在计算，请稍候……”

Application.StatusBar = False

状态栏

恢复【状态栏】为初始状态

ActiveWindow.DisplayHeadings = False

行标和列标

隐藏【行标】和【列标】

（2）

代码执行后的效果

代码

隐藏工作表标签

ActiveWindow.DisplayWorkbookTabs = False

隐藏水平滚动条

ActiveWindow.DisplayHorizontalScrollBar = False

隐藏垂直滚动条

ActiveWindow.DisplayVerticalScrollBar = False

显示绘图工具栏

Application.CommandBars("Drawing").Visible = False

隐藏常用工具栏

Application.CommandBars("Standard").Visible = False

隐藏网格线

ActiveWindow.DisplayGridlines = False

4.2.6　她和她的孩子们

把对象模型这本家谱打开，Application是家族的起点，开枝散叶，不同的孩子住在不同的地方。

可以通过引用Application对象的属性返回不同的子对象。

所以，引用对象必须把每一级的对象名称写清楚。如：

引用Application的每一个子对象都可以使用这种引用方式，但对于某些特殊的对象却不必这么严谨，如想在当前选中的单元格里输入数值300，因为“选中的单元格”是一个特殊的对象，所以代码可以写为：

Application可以省略不写，直接将式码写为：

除了Selection，还可以使用其他属性引用某些特殊对象，如表4-4所示。

表4-4　Application的常用属性

属性

说明

ActiveCell

当前活动单元格

ActiveChart

当前活动工作簿中的活动图表

ActiveSheet

当前活动工作簿中的活动工作表

ActiveWindow

当前活动窗口

ActiveWorkbook

当前活动工作簿

Charts

当前活动工作簿中所有的图表工作表

Selection

当前活动工作簿中所有选中的对象

Sheets

当前活动工作簿中所有Sheet对象，包括普通工作表、图表工作表、Ms Excel 4.0宏表工作表和Ms Excel 5.0对话框工作表

Worksheets

当前活动工作簿中的所有Worksheet对象（普通工作表）

Workbooks

当前所有打开的工作簿

4.3　管理工作簿，了解Workbook对象

4.3.1　Workbook与Workbooks

什么是Workbooks

就像英语里的可数名词，Workbook代表一个工作簿，加s后的Workbooks表示当前打开的所有工作簿，即工作簿集合（参阅3.4.1小节）。

怎么引用单个工作簿

引用工作簿，就是指明工作簿的位置及名称。

体育老师嘴里的“同学”是一个笼统的称呼，是所有同学的集合，谁该去示范呢？同学们都很迷茫，因为老师没有使用正确的引用方式指明同学的身份。

引用工作簿，指明了工作簿的身份，VBA才知道应该操作谁。

引用工作簿常用的方法有两种。

方法一：利用索引号引用工作簿

同数组里元素的索引号（参阅3.3.4小节）类似，索引号指明一个工作簿在工作簿集合里的位置，如图4-9所示。

图4-9　工作簿的索引号

操场上，同学们整整齐齐地排成一队，张姣排在第3位。老师：“第3个同学，出列！”，大家都知道，叫的是张姣。

如果要引用Workbooks集合里的第3个Workbook，可以使用代码：

可以省略Item，直接简写为：

方法二：利用工作簿名引用工作簿

第一次排队，张姣站在第3位，第二次排队，站在第8位。如果老师一直在那嚷嚷：“3号出列！”还能把她叫出来吗？

这时候，更适合的做法应该是叫同学的名字：“张姣，到你演示了。”

引用工作簿也如此，如果不能确定索引号，使用工作簿的名称引用会更准确一些。

如想引用"Book1"工作簿，代码为：

可以给工作簿的文件加上扩展名，写成：

练习小课堂

新建一个工作簿，在不保存的情况下，打开【立即窗口】，分别运行代码：

试一试，都能运行吗？在一个已经保存的工作簿（文件名称为"Book1.xls"）中。

想一想，什么时候可以使用扩展名，什么时候不能使用扩展名？把你的总结写下来。

参考答案

（1）如果是新建的工作簿，在不保存（即该文件不存在）的情况下，引用时不能加扩展名；

（2）如果是已经存在的文件，当系统设置不显示文件的扩展名时，引用时可以使用扩展名，也可以不使用；

（3）如果是已经存在的文件，当系统设置显示文件的扩展名时，引用时必须使用扩展名。

所以，对于一个已经存在的文件，使用带扩展名的名称引用它会准确一些。

4.3.2　认识Workbook，需要了解的信息

想了解它，就替它做张名片

Excel就像一个美丽的美眉，想和她交朋友，必须掌握她的基本信息，如图4-10所示。

图4-10　名片上人物的信息

要了解Workbook，也可以做一张名片记录它的基本信息，如图4-11所示。

图4-11　等待完善的工作簿名片

填写这张名片的信息，可以读取Workbook对象的Name属性、Path属性和FullName属性值，如图4-12所示。

图4-12　完善后的名片

让名片更详细

Workbook对象拥有很多的属性和方法供你将名片完善，想知道它有哪些属性或方法，可以在帮助里查看，如图4-13所示。

图4-13　Workbook对象的帮助

4.3.3　实际操作，都能做什么

创建一个工作簿文件

可以给Add方法设置参数：

也可以通过参数指定新建工作簿中包含的工作类型：

Excel一共有4种类型的工作表，可以在【插入】对话框里看到，如图4-14所示。

图4-14　Excel中4种类型的工作表

如果想让新建的工作簿包含指定类型的工作表，可以使用xlWBATCHart、xlWBATExcel4lntlMacroSheet、xlWBATExcel4MacroSheet或XlWBATWorksheet作为参数，如图4-15所示。

图4-15　4种不同工作表对应的参数

打开工作簿

打开一个Excel文件，最简单的方法就是使用Workbooks的Open方法。

参数名称可以省略不写：

除了Filename参数，Open方法还有14个参数，让用户决定以何种方式打开指定的文件，可以通过系统的帮助来了解更多的信息。

激活工作簿

打开了5个工作簿文件，但同一时间只能有一个窗口是活动的。调用Workbooks对象的Activate方法可以激活一个工作簿。

保存工作簿

保存工作簿就调用Workbook对象的Save方法。

如果想将文件另存为一个新的文件，或者是第一次保存一个新建的工作簿，就用SaveAs方法。

使用SaveAs方法将工作簿另存为新文件后，将自动关闭原文件，打开新文件，如希望继续保留原文件不打开新文件，可以用SaveCopyAs方法。

关闭工作簿

如果想关闭指定的某个工作簿文件，应用代码指定。

如果工作簿被更改过而且没有保存，关闭工作簿前Excel会询问用户是否保存更改，如图4-16所示。

图4-16　是否保存工作簿的对话框

如果不想显示该对话框，可以给Close方法设置参数：

4.3.4　ThisWorkbook与ActiveWorkbook

同是Application对象的属性，同是返回Workbook对象，但二者并不是等同的。ThisWorkbook是对程序所在工作簿的引用，ActiveWorkbook是对活动工作簿的引用。

打开一个工作簿，在工作簿中输入并运行下面的程序，查看程序运行的结果，如图4-17和图4-18所示。

图4-17　ThisWorkbook

图4-18　ActiveWorbook

4.4　操作工作表，认识Worksheet对象

4.4.1　认识Worksheet对象

Worksheet与Worksheets

一个Worksheet对象代表一张普通工作表，Worksheets是多个Worksheet对象的集合，包含指定工作簿中所有的Worksheet对象。

引用工作表

可以使用工作表的索引号或标签名称引用它，如图4-19所示。

图4-19　工作表的索引号和标签名称

如图4-17所示，如果要引用第1张工作表，可以选用以下3条语句中的其中1条：

除此之外，还可以使用工作表的代码名称引用工作表。

工作表的代码名称可以在【工程资源管理器】或【属性窗口】中看到，如图4-20所示。

图4-20　工作表的代码名称和标签名称

因为代码名称只能在【属性窗口】里修改，不会随工作表标签名称或索引号的变化而变化。因此，当工作表的索引号或标签名称经常变化时，使用代码名称引用工作表会更方便。

使用代码名称引用工作表，只需直接写代码名称。如在图4-20中的“第一张工作表”的A1单元格输入10，代码为：

查看工作表的代码名称，可以读取它的CodeName属性，如果想知道活动工作表的代码名称，代码为：

4.4.2　操作工作表

新建工作表

使用Worksheets对象的Add方法：

可以用参数给新建的工作表指定位置：

还可以同时插入多张工作表：

编写程序时，可以同时使用多个参数：

练习小课堂

试一试，在当前工作簿中最后一张工作表前插入两张工作表，你能做到吗？把你的程序写下来。

参考答案

更改工作表标签名称

如果你想更改工作表的标签名称，就设置它的Name属性：

如果是新建的工作表，可以在程序中更改：

也可以在新建工作表的同时指定它的标签名称：

但如果同时添加多张工作表（即Count参数值大于1时），并不能使用一句代码同时命名。

Add方法有哪些参数

在【代码窗口】中，输入完对象的方法名称后按空格，VBE会自动显示该方法的所有参数，用户可以根据提示进行输入，如图4-21所示。

图4-21　输入Add方法时的提示

删除工作表

使用Worksheet对象的Delete方法可以删除指定的工作表。

练习小课堂

删除工作表时，会弹出一个询问的警告对话框。想取消显示它，还记得用什么方法吗（参阅4.2.2小节）？编写一个删除“工资表”的Sub过程，你能做到吗？

参考答案

激活工作表

激活工作表就是让处于不活动状态的工作表变为活动工作表。激活工作表可以用Activate方法和Select方法。

练习小课堂

（1）按步骤操作，把你得到的结论写出来：

Step 1：隐藏工作簿中的第一张工作表，试一试用下面的两种方法激活它，能完成吗？

Step 2：试一试用下面的两种方法同时选中工作簿中的所有工作表，能完成吗？

（2）你知道使用Select和Activate两种方法的区别吗？把你的结论写下来。

参考答案

当工作表隐藏时，调用它的Select会出错。用Activate方法不用同时选中多张工作表，但用Select方法可以同时选中未隐藏的多张工作表。

复制工作表

使用Copy方法可以复制工作表，如图4-22所示。

图4-22　复制工作表

如果不使用参数，默认将工作表复制到新工作簿中，如图4-23所示。

图4-23　不使用参数复制工作表

练习小课堂

（1）使用参数和不使用参数时，复制的工作表名称一样吗？如果想把“工资表”复制到“出勤登记表”前，更名为“工资表备份”，你知道完整的程序应该怎样写吗？试一试，写下来。

（2）动手写一个程序，将“工资表”复制到新工作簿中，工作表名为“工资表备份”，同时将文件保存到D盘根目录下，文件名称为“7月工资表xls”，要求保存工作簿后，原工作簿仍可以操作。

代码

代码说明

声明过程

复制“工资表”到新工作簿

把复制的工作表更名为“工资表备份”

将工作簿另存为到D盘，文件名为“7月工资表.xls”，保存后原工作簿可修改

关闭Copy方法生成的工作簿，不保存更改

结束过程

参考答案

（1）使用参数复制工作表时，将把工作表复制到同一工作簿中，Excel自动为工作表命名，与原工作表不同。不使用参数复制工作表时，将把工作表复制到新工作簿中，名称与原来相同。

（2）代码

移动工作表

移动工作表的操作与复制工作表类似。

隐藏或显示工作表

可以设置工作表的Visible属性显示或隐藏该工作表，如图4-24所示。

图4-24　通过属性窗口隐藏或显示工作表

无论以何种方式隐藏了“工作表”工作表，想用代码显示它，可以用下面4句代码中的任意一句：

获取工作表的数目

想知道当前工作簿中共有几张工作表，可以读取Worksheets的Count属性值，运行结果如图4-25所示。

图4-25　求工作表数量

4.4.3　Sheets与Worksheets

有人说，它们相同

有人说，Sheets同Worksheets没有区别，如图4-26所示。

图4-26　在立即窗口中执行命令

但是，它们相同吗

其实Sheets与Worksheets代表两种不同的集合。

Excel里一共有4种不同类型的工作表，Sheets表示工作簿里所有类型的工作表的集合，而Worksheets只表示普通工作表的集合，如图4-27所示。

图4-27　Sheets与Worksheets集合的区别

练习小课堂

试一试，在工作簿中插入不同类型的工作表后，在【立即窗口】中执行下面的代码，返回的值还相同吗？

参考答案

Sheets.Count返回各种类型的工作表的数量之和，Worksheets.Count返回普通工作表的数量。

关于Sheets与Worksheets

Sheets与Worksheets集合里的对象都有标签名称（Name），代码名称（CodeName）、索引号（index）等属性，也都有Add、Delete、Copy和Move等方法，设置属性或调用方法的操作类似。但因为Sheets集合包含更多类型的工作表，所以其包含的方法和属性比Worksheets集合多。

4.5　核心，至关重要的Range对象

Range对象代表工作表中的单元格或单元格区域，包含在Worksheet对象中。

4.5.1　多种方法引用Range对象

操作单元格，需要先引用单元格。

信封上的收信地址，告诉邮递员应该把信投到哪家邮箱。只有写清楚地址，信才不会寄错。

引用单元格，就像写在信封上的地址，如果要把某商品的销售数量50保存到活动工作簿中Sheet1工作表的A1单元格中，代码为：

这里的Range("A1")就是引用A1单元格的一种形式。在VBA里，引用单元格有多种方法。

Worksheet（或Range）对象的Range属性

如果单元格已经被定义为名称，参数还可以是表示名称名的字符串或字符串变量，如图4-28所示。

图4-28　定义的名称

如果要引用多个不连续的区域，可以在各区域间添加逗号，如图4-29所示。

图4-29　引用多个不连续区域

如果想引用相交区域（公共区域），可以在多个区域间添加空格，如图4-30所示。

图4-30　引用相交区域

还可以使用两个参数来引用两个区域围成的矩形区域，如图4-31所示。

图4-31　使用两个参数引用单元格

Worksheet（或Range）对象的Cells属性

这是引用Range对象的另一种形式，返回指定工作表或单元格区域中指定行与列相交的单元格。

如果引用的是Range对象的Cells属性，将返回指定单元格区域中指定行与列相交的单元格，如图4-32所示。

图4-32　引用Range对象的Cells属性

Cells属性还可以用作Range属性的参数：

Cells可以只使用一个参数：

如果引用的是Worksheet对象的Cells属性，在Excel 2003中索引号的值为1到16777216（65536行×256列）。

单元格按从左往右，从上到下的顺序编号，即A1为第1个单元格，B1为第2个单元格，C1为第3个单元格……A2为第257个单元格……如图4-33所示。

图4-33　工作表中单元格的索引号

如果引用的是Range对象的Cells属性，索引号的范围为1到这个单元格区域包含的单元格的个数。

但索引号可以大于单元格区域里的单元格个数，如果索引号大于单元格个数，系统会自动将单元格区域在行方向上进行扩展（列数不变），然后再引用，如图4-34所示。

图4-34　当索引号大于单元格个数时

如果不使用任何参数，Cells属性将返回指定对象中的所有单元格：

练习小课堂

试一试，能用Worksheet对象的Cells属性引用A1:B10单元格区域吗？用Range属性和Cells属性引用单元格的区别是什么，你发现了吗？总结一下，并写下来。

参考答案

使用Cells属性只能引用一个单元格，不能引用多个单元格。

更简短的快捷方式

如果想引用某个单元格或单元格区域，可以直接将单元格地址（A1样式）写在中括号里。如果单元格或单元格区域已被定义为名称，也可以直接把名称名写在中括号里：

[]是Application对象的Evaluate方法的简写形式，这种简写形式非常适合引用一个固定的Range对象。但是因为不能在方括号中使用变量，所以这种引用方式缺少灵活性。

4.5.2　还可以怎样得到单元格

引用整行

如果引用的是Range对象的Rows属性，则返回单元格区域里的指定行，如图4-35所示。

图4-35　引用单元格区域里的指定行

引用整列

引用整列时参数可以是列标，也可以是索引号。

练习小课堂

根据提示，将表4-5中的代码补充完整。

表4-5　补充代码

提示

代码

用两种引用方式引用活动工作表的B列

引用活动工作表中的B列到D列

引用B列到E列区域中的第2列

参考答案

Application对象的Union方法

Union方法像一支强烈的粘合剂，将不连续的多个单元格区域粘在一起，可以同时对其进行操作，如图4-36所示。

图4-36　使用Union方法选中两个不连续的单元格区域

练习小课堂

表4-6中是一个未完成的程序，请根据代码说明将程序补充完整。让程序运行后，选中活动工作表中A1:D10单元格区域里与A1单元格内容相同的所有单元格，如图4-37所示。

图4-37　程序执行效果图

表4-6

参考答案

Range对象的Offset属性

“牛头市同兴路2号张大明转张姣（收）”，张姣是张大明的堂妹，写信的人不知道她家住哪里，所以让张大明转交。这是写地址的另一种方式。

就像寄信一样，如果想把500保存到A1单元格的某个邻居（单元格）里，可以利用Range对象的Offset属性。

从A1单元格出发，先向下移动2行，再向右移动3列，到达的单元格就是要保存数据的地方，如图4-38所示。

图4-38　Offset属性

修改Offset的参数可以控制移动的方向和距离。如果参数是正数，表示向下或向右移动，如图4-33所示；如果参数为负数，表示向上或向左移动；如果参数为0，则不移动，如图4-39所示。

图4-39　利用Offset属性的参数控制偏移的方向和距离

Range对象的Resize属性

使用Range对象的Resize属性扩大或缩小指定的单元格区域，得到一个新的单元格区域，如图4-40和图4-41所示）。

图4-40　使用Resize属性扩大单元格区域

图4-41　使用Resize属性收缩单元格区域

Worksheet对象的UsedRange属性

UsedRange是Worksheet对象的属性，返回工作表中已经使用的单元格围成的矩形区域，如图4-42所示。

图4-42　使用UsedRange属性返回Range对象

UsedRange属性返回的工作表中所有已经使用的单元格围成的矩形区域，而不管这些区域间是否有空行、空列或空单元格，如图4-43所示。

图4-43　使用UsedRange属性返回Range对象

Range对象的CurrentRegion属性

CurrentRegion属性返回当前区域，即以空行和空列的组合为边界的区域，如图4-44所示。

图4-44　使用CurrentRegion属性返回Range对象

练习小课堂

通过对比，你能指出Worksheet的UsedRange属性与CurrentRegion属性之间的异同吗？试着总结一下。

参考答案

UsedRange属性返回指定工作表中已使用的单元格围成的矩形区域，而不管该区域中是否有空行、空列或空单元格；CurrentRegion属性返回当前区域，这个区域总是小于或等于UsedRange属性返回的区域。

Range对象的End属性

End属性返回当前区域结尾处的单元格，等同于在源单元格按<End+方向键（上、下、左、右）>得到的单元格，如图4-45所示。

图4-45　使用End属性

End属性的参数一共有4个可选项，如表4-7所示，效果如图4-46所示。

表4-7　End属性的参数及说明

参数

说明

xlToLeft

向左移动，等同于在源单元格<按Ctrl+左方向键>

xlToRight

向右移动，等同于在源单元格<按Ctrl+右方向键>

xlUp

向上移动，等同于在源单元格<按Ctrl+上方向键>

xlDown

向下移动，等同于在源单元格<按Ctrl+下方向键>

图4-46　使用End属性的参数

什么时候会用到End属性

可以用Range对象的End属性来解决这个问题，如图4-47所示。

图4-47　在A列的第一个空单元格输入内容

注意：如果A列全为空单元格，那Range("A65536").End(xlUp)返回的是A1单元格，同样的代码实际上是在A2单元格输入数据，如图4-48所示。

练习小课堂

（1）如果想让数值总是输入第一个空单元格，你有什么好办法？

图4-48　当A列全为空时

（2）除了使用End属性，还能用哪些方法得到A列的第一个非空单元格？能不能用CurrentRegion属性和UsedRange属性？试一试。

参考答案

（1）

（2）

4.5.3　操作单元格，还需要了解什么

单元格里的内容，Value属性

如果单元格是一个瓶子，Value就是装在瓶子里的东西。

输入内容，修改数据，这些都是在设置Range对象的Value属性。

读取单元格的内容就是读取它的Value属性值。

Value是Range对象的默认属性，在给区域赋值时可以省略：

但为了保证程序运行过程中不出现意外，建议养成保留Value属性而不省略它的习惯。

单元格个数，Count一下就知道

Range对象的Count属性返回指定的单元格区域中包含的单元格个数。

如想知道B4:F10一共有多少个单元格，程序为：

得到的结果如图4-49所示。

图4-49　利用Count属性返回单元格个数

如想知道某个区域的行数或列数，代码为：

单元格地址，Address属性

想知道某个单元格的地址，可以读取它的Address属性，如图4-50所示。

图4-50　使用Address属性

4.5.4　亲密接触，操作单元格

选中单元格，Activate与Select方法

选中活动工作表的A1:B10单元格，代码可以为：

练习小课堂

尽管使用Activate和Select都可以选中指定的单元格，但两种方法并不完全相同。

在A1:B10单元格区域呈选中状态时，分别用两种方法选中B5单元格，得到的结果一样吗？Select和Activate方法的区别是什么？

参考答案

选中单元格区域后，再使用Activate方法激活该区域里的一个单元格，该区域依然呈选中状态，只改变活动单元格为激活的单元格。如果使用Select方法选中区域里的一个单元格，则只有用Select方法选中选择的单元格呈选中状态。

选择性清除单元格

一个单元格里不仅数据，还有格式、批注等。不同的内容，可以使用【编辑】菜单里相应的菜单命令清除它们，如图4-51所示。

图4-51　利用菜单命令选择性清除单元格内容

练习小课堂

无论是清除内容还是格式，代码都可以通过录制宏得到。

试一试，结合录制宏，写出表4-8中的代码。

表4-8

代码说明

代码

清除B2:B15单元格所有包括批

注、内容、注释、格式等内容

清除B2:B15单元格的批注

清除B2:B15单元格的内容

清除B2:B15单元格的格式

参考答案

复制单元格区域

开始之前，录制一个复制A1单元格到C1的宏，让我们在现成代码的基础上来学习复制单元格。

但在执行复制或粘贴操作之前并不需要先选中单元格，所以录制的代码可以简化为：

宏代码中省略了Destination参数名，完整的语句是：

练习小课堂

复制单元格就像是做填空题，只要把源单元格和目标单元格填入相应的位置即可。事实上，在VBA中，不仅复制，所有的语句都是在做填空题。

把Sheet1工作表的A1:A10复制到Sheet2工作表的B1:B10，你能写出这个程序吗？

参考答案

如果要复制的单元格区域不能确定大小，可以只指定一个单元格作为目标区域的最左上角单元格，如图4-52所示。

图4-52　只指定目标区域的最左上角单元格

练习小课堂

如果只想粘贴源区域里的数值，代码应该怎么写？

请录制一个选择性粘贴数值的宏，结合代码，编写一个程序，把A1:D10单元格的数值复制到F1:10，不修改目标单元格的格式。

参考答案

或

剪切单元格

调用Range对象的Cut方法可以剪切单元格。

删除单元格

调用Range对象的Delete方法可以删除单元格，同手动删除单元格一样，用VBA删除单元格后，也有4个删除选项，但用VBA删除时Excel并不会给出图4-53的【删除】对话框供你选择，需要在程序中用代码指定。

图4-53　删除单元格时的4个选项

练习小课堂

（1）结合录制宏，你能把表4-9中的代码写下来吗？

表4-9

代码说明

代码

删除B5单元格，删除后右侧单元格左移

删除B5单元格，删除后下方单元格上移

删除B5单元格所在的行

删除B5单元格所在的列

（2）如果代码中不使用参数，如：

Excel会按表4-9中的哪种情况进行操作？

参考答案

（1）

（2）如果在删除时不使用参数，直接写为：

执行代码删除单元格后下方单元格上移，即相当于语句：

4.6　不止这些，其他常见的对象

4.6.1　名称，Names集合

名称，就是名字

Excel中定义的名称就是给单元格区域（或数值常量、公式）取的名字。一个自定义的名称就是一个Name对象，Names是工作簿中定义的所有名称的集合。

关于Names的详细信息，可以在帮助里看到，如图4-54所示。

图4-54　在帮助里查看名称的信息

录制的宏告诉我们，怎样新建一个名称

R5C[−2]："R"后面的数字代表行号，"C"后面的数字代表列号。"R5C[−2]"表示指定行与指定列相交的单元格。

C[−2]中的[]是什么

是否加中括号，决定单元格的引用方式是相对还是绝对引用。没有加中括号时使用绝对引用方式，反之则为相对引用。

R5表示工作表中的第5行，C[−2]表示活动单元格左边的第2列。R5C[−2]是对活动单元格左边第2列与工作表中第5行相交的单元格的引用，如图4-55所示。

图4-55　R5C[−2]引用的单元格

如果要在行方向上使用相对引用，就在行号上加中括号，如果要在列方向上使用绝对引用，就去掉列号上的中括号。

可以使用A1样式的引用

定义名称，更简单的方式

怎样引用名称

可以用名称名引用名称：

也可以用名称的索引号引用名称：

4.6.2　单元格批注，Comment对象

口香糖瓶子的标签上写有“绿茶薄荷味”，指明口香糖的口味，这是标签的作用。

单元格的批注就像贴在瓶子上的标签，对单元格作注释或说明。批注本身并不影响单元格内的数值，也不参与或影响计算。

在Excel里，一个批注就是一个Comment对象，Comments是工作簿中所有Comment对象的集合。

给单元格添加批注

利用VBA新建的批注如图4-56所示。

图4-56　利用VBA新建的批注

注意：如果单元格中已经有批注，再用程序为它添加批注时程序会出错，如图4-57所示。

图4-57　在已经有批注的单元格中添加批注

怎么知道单元格中是否有批注

还可以这样操作批注

4.6.3　给单元格化妆

校长喜欢看什么样的成绩表

张老师将新计算好的成绩表（见图4-58）拿给校长看，校长扫了一眼，微笑着拍拍他的肩膀说：“小张，以后的表格稍微设计一下，美观一点。”

图4-58　张老师做的成绩表

“校长觉得这个表不美观……那……”美观？怎样才叫美观？

表格也需要化妆

没有丑女人，只有懒女人。女人需要打扮，表格也一样。我们可以像打扮自己一样，把表格打扮得漂漂亮亮。

字体、单元格的底纹、边框等都是可以打扮的对象。

设置字体，Font对象

Font对象（字体）决定表格里的内容以什么样的姿势表现出来。

给单元格添加底纹

给表格设置边框

其他设置

可以在【单元格格式】对话框中进行其他设置，如图4-59所示，如果想用代码完成却不知道代码该怎么写，可以手动操作，用宏录制器录下它。

图4-59　【单元格格式】对话框

练习小课堂

试着编写一个完整的程序，替张老师打扮一下成绩表。

当张老师把你做好的成绩表拿到校长手里时，校长一定会很满意的。

4.7　典型的技巧与示例

4.7.1　创建一个工作簿

编写一个程序，按要求创建一个新工作簿，并把它保存到指定的文件夹。

Step1：插入一个模块（参阅2.4.1小节）。

Step2：在模块中输入下面的代码。

Step 3：运行程序后可以在本文件所在的文件夹中看到新建的文件，如图4-60所示。

图4-60　用程序创建工作簿文件

4.7.2　判断工作簿是否打开

打开的工作簿很多，想知道名称为“成绩表”的工作簿是否打开，程序可以这样写：

练习小课堂

4.7.2小节中的程序先计算出当前打开的工作簿个数，再利用索引号引用工作簿，依次判断工作簿的名称是否是“成绩表.xls”。

根据同样的思路，你能编写一个程序，判断当前活动工作簿中是否存在标签名称为“一年级”的工作表吗？试一试，编写一个这样的程序，如果没有这张工作表，就新建一张标签名称为“一年级”的工作表放在所有工作表之前，如果工作表已存在，将其移动到所有工作表之前。

参考答案

或

4.7.3　判断工作簿是否存在

文件夹中存了许多工作簿文件，想知道“员工花名册.xls”文件是否存在，可以用这个程序：

4.7.4　向未打开的工作簿中录入数据

4.7.5　隐藏活动工作表外的所有工作表

4.7.6　批量新建工作表

一张成绩表的C列保存着许多不同的班级名称，如图4-61所示。

图4-61　成绩表

根据C列的班级名新建不同的班级工作表，工作表以班级名命名，可以用这个程序：

运行程序后VBA会自动完成新建工作表的任务，如图4-62所示。

图4-62　程序运行前后

练习小课堂

但是，C列的单元格有可能是重复的，如图4-63所示。

图4-63　实际的工作表

试一试，编写这个批量新建工作表的程序，你能做到吗？

参考答案

4.7.7　批量对数据分类

不同的班级工作表建好了，接下来将根据班级名对成绩表进行分类，将各个班级的记录分类到相应的工作表里。

运行程序后的效果如图4-64所示。

图4-64　运行程序前后

练习小课堂

如果班级工作表中原来已经有记录，执行程序，在班级工作表中添加记录前，要将原有记录清除，你知道怎么修改程序吗？

参考答案

执行4.7.7小节中对数据分类的程序前，先执行下面的程序，清除各班级表中的数据记录。

4.7.8　将工作表保存为新工作簿

工作簿里有许多班级成绩表，如果想把各个班级的成绩表保存为一个单独的工作簿文件，可以用这个程序，如图4-65所示。

图4-65　将工作表保存为工作簿

4.7.9　快速合并多表数据

工作簿中保存着各个班级的成绩表，如果想把所有班级的成绩表汇总到一张工作表中，汇总多表数据，可以用这个程序：

4.7.10　汇总同文件夹下多工作簿数据

某文件夹下有许多工作簿，如图4-66所示，每张工作簿里只有一张工作表且结构相同，如图4-67所示，分别保存各班级的学生名册。

图4-66　同一文件夹下的多个工作簿

图4-67　工作表的结构

把各个工作簿中保存的信息汇总到同文件夹中另一个工作簿的同一张工作表里，这是领导交给小张的工作。完成这个任务，可以用这个程序。

执行程序后不用手动去复制粘贴，所有的记录全都汇总到程序所在工作簿的活动工作表中，如图4-68所示。

图4-68　汇总后的工作表

4.7.11　为工作表建立目录

工作簿中有许多工作表，想为工作表建一个目录，可以用这个程序，如图4-69所示。

图4-69　建立工作表目录

第5章

Excel事件

楼梯间的电灯开关

楼梯间装有电灯，但小丽记性不好，上楼后老是忘记关灯，而且每天晚上在漆黑的墙壁上寻找开关也让她感到头痛。

同事说，可以装一个声光控开关。

声光控开关，装上后真的很方便，小丽对这个自动化的电灯开关很满意。

VBA中也有开关

按钮、图片、快捷键等都是VBA程序的开关。这类开关只有按下，程序才会运行。

声光控开关认识小丽走到楼梯口的动作，当“听”到这个动作发出的声音后就会自动打开电灯。

我们每天都在Excel里执行不同的操作，其中的某些特殊操作是Excel认识的，所以可以像安装声光控开关一样，给VBA程序安装一个自动运行的开关，当我们做了某个Excel认识的操作后自动运行指定的程序。

5.1　让Excel自动响应你的行为

5.1.1　让Excel自动问好

给程序加代码，让Excel自动问好，如图5-1所示。

图5-1　输入程序

完成后关闭并保存工作簿。重新打开它，就可以看到效果了，如图5-2所示。

图5-2　打开文件后自动问好

在本例中，不用手动单击按钮运行程序，这是因为我们给程序安装了一个自动开关。

5.1.2　事件，VBA里的自动开关

什么是事件

声控开关认识小丽在楼梯口踢高跟皮鞋的动作，所以当开关“听”到这个动作发出的声音后就自动打开电灯。这里踢高跟皮鞋的动作就是事件。

在Excel里，事件就是一个能被对象识别的操作。

事件是怎么控制程序的

“当有人踢皮鞋的时候自动开灯。”这是声控开关记住的规则。在Excel中，事件也按类似的规则控制程序。

“当打开工作簿的时候自动运行程序。”这是Workbook_Open事件控制程序的规则，于是，每次打开工作簿时，都会自动运行这个程序。

“当……的时候自动运行程序”，总是可以用这样的语句去描述一个Excel VBA的事件过程。

练习小课堂

试一试，把5.1.1小节中编写的程序写在模块对象里，再次打开工作簿，Excel向你问好了吗？猜一猜，为什么会出现这种情况？

参考答案

不能运行，原因参阅5.1.3小节。

5.1.3　事件过程

因为Workbook（工作簿）对象能识别Open（打开）这个动作，所以打开工作簿就会自动运行相应的程序。

像这种当某个事件发生后自动运行的过程称为事件过程。事件过程也是Sub过程。

事件过程必须写在特定对象所在的模块中，而且只有过程所在的模块里的对象才能触发这个事件。

5.1.4　编写事件过程

事件过程的过程名由Excel自动设置，以“对象名称_事件名称”的形式存在，不能更改。

进入VBE，想编写关于哪个对象的事件过程，就在【工程资源管理器】中双击激活该对象所在模块的【代码窗口】。

如果想写这样的一个程序，当激活Sheet1工作表时，自动完成某些操作或计算，步骤如图5-3所示。

图5-3　编写事件的过程

完成后重新激活代码所在的工作表，程序就运行了。

5.2　Worksheet事件

5.2.1　关于Worksheet事件

Worksheet事件是发生在Worksheet对象里的事件，如图5-4所示。

图5-4　Worksheet对象

Worksheet事件过程必须写在相应的Worksheet对象里，只有过程所在的Worksheet对象里的操作才能触发该事件。

5.2.2　常用的Worksheet事件

Worksheet_Change事件：自动提示更改的内容

Worksheet_Change告诉Excel，当过程所在工作表的单元格发生更改时自动运行程序过程，程序必须写在相应的工作表对象里，如图5-5所示。

图5-5　在工作表里添加事件过程

在Sub与End Sub的中间添加执行的语句：

完成后返回该工作表区域，更改任意单元格的内容，按回车确认后可以看到程序运行的效果，如图5-6所示。

图5-6　更改单元格内容后自动运行程序

练习小课堂

（1）如果只有更改A列单元格时才自动运行程序，程序应该怎样写？

（2）无论是手动还是使用VBA代码更改单元格，都会触发Worksheet的Change事件。

当单元格被修改后，自动在单元格内容的前面加上“新内容：”，如图5-7所示，在工作表模块中输入下面的程序，可以实现预期的目的吗？如果不能，程序应该怎样写？

图5-7　希望程序运行的效果

参考答案

（1）

（2）

因为用程序更改单元格后会再次触发Worksheet_Change事件，再次运行程序，所以原程序不能得到预期的目的。可以将程序更改为：

Worksheet_SelectionChange事件：你选中了谁

Worksheet_SelectionChange事件告诉Excel，当工作表中选定的单元格发生改变时自动运行程序，运行后的效果如图5-8所示。

图5-8　程序运行后的效果

练习小课堂

（1）编写程序，当选中A1单元格时，提示A1单元格的内容。

（2）编写程序，当选中的单元格不是A列的单元格时，自动选中同行A列的单元格。

参考答案

（1）

（2）

Worksheet_Activate事件：自动提示工作表名

Worksheet_Activate事件告诉Excel，当激活工作表时自动运行程序。

在Sheet1工作表模块里写入程序，重新激活工作表，程序就自动运行了，如图5-9所示。

图5-9　激活工作表时自动运行程序

Worksheet_Deactivate事件：禁止选中其他工作表

Worksheet_Deactivate事件告诉Excel，当工作表由活动工作表变为不活动工作表时自动运行过程。

在Sheet1工作表模块中写入下面的程序：

输入程序后，当激活其他工作表时，Excel会进行提示，并自动重新激活Sheet1工作表，如图5-10所示。

图5-10　禁止激活其他工作表

5.2.3　Worksheet事件列表

Worksheet对象一共有9个事件可供使用，如表5-1所示。

表5-1　Worksheet对象的事件列表

事件名称

事件说明

Activate

激活工作表时发生

BeforeDoubleClick

双击工作表之后，默认的双击操作之前发生

BeforeRightClick

右击工作表之后，默认的右击操作之前发生

Calculate

重新计算工作表之后发生

Change

工作表中的单元格发生更改时发生

Deactivate

工作表由活动工作表变为不活动工作表时发生

FollowHyperlink

单击工作表中的任意超链接时发生

PivotTableUpdate

在工作表中更新数据透视表之后发生

SelectionChange

工作表中所选内容发生更改时发生

5.3　Workbook事件

5.3.1　关于Workbook事件

Workbook事件是发生在Workbook对象里的事件。

进入VBE，在【工程资源管理器】中可以看到ThisWorkbook模块，如图5-11所示。

图5-11　ThisWorkbook模块

5.3.2　常用的Workbook事件

Open事件

Workbook_Open事件告诉Excel，当打开工作簿时自动运行程序。

练习小课堂

“当打开工作簿后，总是让第一张工作表成为活动工作表”，这样的程序你知道应该怎样写吗？（可参阅5.1.1小节）

参考答案

在ThisWorkbook对象中写入程序：

保存关闭工作簿，再重新打开它即可。

BeforeClose事件

Workbook_BeforeClose事件告诉Excel，在关闭工作簿之前自动运行程序。

在ThisWorkbook模块中输入该程序后，每次关闭工作簿都会自动运行程序，让你选择是否关闭工作簿，如图5-12所示。

图5-12　关闭工作簿前的提示框表

Workbook_SheetChange事件

Workbook_SheetChange事件告诉Excel，当工作簿里任意一个单元格被更改时，自动运行程序。

练习小课堂

你知道Workbook_SheetChange事件与Worksheet_Change事件的异同吗？

你认为什么时候应该使用Workbook_SheetChange事件？什么时候应该使用Worksheet_Change事件？

参考答案

如果只想在指定的某张工作表中更改选中的单元格时自动运行程序，使用Worksheet_Change事件比较适合；如果希望选中工作簿中任意一张工作表里的单元格都自动运行程序，使用Workbook_SheetChange事件更适合。

5.3.3　Workbook事件列表

Workbook事件名称及相应说明见表所示。

表5-2　Workbook事件列表

事件名称

事件说明

Activate

当激活工作簿时发生

AddinInstall

当工作簿作为加载宏安装时发生

AddinUninstall

当工作簿作为加载宏卸载时发生

AfterXmlExport

在保存或导出指定工作簿中的XML数据之后发生

AfterXmlImport

在刷新现有XML数据连接或新的XML数据被导入任意一个打开的工作簿后发生

BeforeClose

在关闭工作簿前发生。如果工作簿已更改，则此事件在询问用户是否保存更改之前发生

BeforePrint

在打印指定工作簿（或其中任何内容）之前发生

BeforeSave

在保存工作簿前发生

BeforeXmlExport

在保存或导出指定工作簿中的XML数据之前发生

BeforeXmlImport

在刷新现有XML数据连接或新的XML数据被导入任意一个打开的工作簿前发生

Deactivate

在工作簿从活动状态转为非活动状态时发生

NewSheet

在工作簿中新建工作表时发生

Open

在打开工作簿时发生

PivotTableCloseConnection

在数据透视表的连接关闭之后发生

PivotTableOpenConnection

在数据透视表的连接打开之后发生

SheetActivate

在激活任意工作表时发生

SheetBeforeDoubleClick

在双击任意工作表时（默认的双击操作之前）发生

SheetBeforeRightClick

在右键单击任意工作表时（默认的右键单击操作之前）发生

SheetCalculate

在重新计算工作表时或在图表上绘制更改的数据之后发生

SheetChange

当更改了任何工作表中的单元格时发生

SheetDeactivate

当工作表从活动工作表变为不活动工作表时发生

SheetFollowHyperlink

当单击工作簿中的任何超链接时发生

SheetPivotTableUpdate

在更新数据透视表的工作表后发生

SheetSelectionChange

当任意工作表上的选定区域发生更改时发生（但图表工作表上的选定区域发生改变时，不会发生此事件）

Sync

当作为“文档工作区”一部分的工作簿的本地副本与服务器上的副本进行同步时发生

WindowActivate

在激活任意工作簿窗口时发生

WindowDeactivate

当任意工作簿窗口由活动窗口变为不活动窗口时发生

WindowResize

在调整任意工作簿窗口的大小时发生

5.4　别样的自动化

5.4.1　MouseMove事件

当鼠标指针移动到按钮上时，按钮迅速闪开。鼠标和按钮，就像在玩老鹰捉小鸡的游戏。这样的效果可以用命令按钮的MouseMove事件实现，如图5-13所示。

图5-13　在工作表中添加一个按钮

新添加的按钮，可以通过在【属性窗口】中设置它的属性来改变它的外观样式，如图5-14所示。

图5-14　利用属性窗口设置按钮的属性

完成后在按钮所在工作表的【代码窗口】中输入程序，如图5-15所示。

图5-15　在代码窗口中写入程序

完成后返回工作表区域，在【控件工具箱】中单击【退出设计模式】按钮，如图5-16所示。

图5-16　退出设计模式完成编辑

完成后就可以用鼠标指针去“抓”按钮了，试一试，你能把鼠标指针放到按钮上面吗？如果按钮跑远了，想让按钮回到初始位置，就单击Back按钮。

在工作表中再添加一个按钮，命名为"Back"，双击按钮，在代码窗口中输入程序：

5.4.2　不是事件的事件

除了对象的事件，Application对象还有两种方法，可以像事件一样让程序自动运行。

Application对象的OnKey方法

OnKey方法告诉Excel，当在键盘上按下指定键或组合键时自动运行程序。

Step 1：进入VBE，新插入一个模块，在模块中输入程序：

Step 2：把需要自动运行的代码全部写在单独的过程Test里，保存在模块中：

步骤如图5-17所示。

图5-17　输入程序的步骤

Step 3：运行ok过程，返回工作表区域，按<Shift+E>组合键即可自动运行Test过程，如图5-18所示。

图5-18　当按下<Shift+e>后

如果想知道还能设置哪些快捷键，可以查看OnKey方法的帮助信息，如图5-19所示。

图5-19　查看OnKey的帮助信息

Application对象的OnTime方法

OnTime方法告诉Excel，当到指定的时间时自动运行程序（可以是指定的某个时间，也可以是指定的一段时间之后），如图5-20所示。

Step 1：进入VBE，新插入一个模块，在模块中写入程序。

Step 2：把提醒休息时间的语句写在另一个程序Test里，保存在模块中。

图5-20　输入程序的步骤

Step 3：运行oT过程，开始工作，一个小时后Excel会自动运行Test过程，如图5-21所示。

图5-21　Excel的温馨提示

练习小课堂

发现了吗？

无论是OnKey还是OnTime方法，想让指定的程序自动运行，都必须先运行该方法所在的程序，如果不运行ok或oT过程，指定的Test过程并不会自动运行。

如果想省去手动运行ok或oT过程的步骤，想一想，你有什么好的办法？

参考答案

在ThisWorkbook对象中输入下面的过程即可。

5.5　典型的技巧与示例

5.5.1　一举多得，快速录入数据

商店里每售出一件商品，都要把相应的信息输入到“商品销售登记表”中，如图5-22所示。

图5-22　商品销售登记表

Step 1：建一张参照表，指明各种商品的商品名称、商品代码、单价等信息，如图5-23所示。

图5-23　登记表里的参照表

Step 2：在该工作表模块里写入程序。

Step 3：返回工作表区域，在商品名称列输入商品名称的首字母，查看程序运行效果，如图5-24所示。

图5-24　输入内容前后

5.5.2　我该监考哪一场

一张监考安排表，密密麻麻的全是监考老师的名字，如图5-25所示。

图5-25　监考安排表

Step 1：在“监考安排表”所在的工作表模块中输入程序。

Step 2：返回工作表区域，想知道张姣老师监考哪些考场，就用鼠标选中她的姓名所在的任意一个单元格，如图5-26所示。

图5-26　高亮显示指定同一姓名的老师所在的单元格

练习小课堂

如果想高亮显示工作表中选中单元格所在的行和列，应该怎么编写程序？

参考答案

如果数据区域是B3:Q22，可以在数据所在的工作表对象中输入下面的程序。

5.5.3　让文件每隔一分钟自动保存一次

“晕死，怎么又停电了，我做了一上午的表格还没保存。”这一幕，经常发生在我同事的身上。

看着他欲哭无泪的样子，我只能深表同情。其实，这样的遗憾是可以避免的。

如果你担心自己会有同样悲催的经历，可以编写一个程序，让你的文件每隔一段时间自动保存一次。

Step 1：新插入一个模块，在模块中输入程序，如图5-27所示。

图5-27　在模块中输入程序

Step 2：在ThisWorkbook模块中输入程序，如图5-28所示。

图5-28　在ThisWorkbook模块中输入程序

Step 3：保存修改，关闭并重新打开工作簿，你就可以放心使用，而不用担心文件没有保存了。

练习小课堂

想一想，为什么要在ThisWorkbook模块中写入5.5.3小节Step 2中的程序？

参考答案

目的是让打开工作簿时，自动运行otime过程，实现自动保存的目的。

第6章

用户界面设计

6.1　在Excel中自由地设计界面

6.1.1　关于用户界面

为什么要设计用户界面

用户界面就像电视机的遥控板，是用户与程序进行互动的窗口。一个合理的程序，总会设计一个或多个供用户操作的界面。

用户界面不仅能为操作提供便利，还能增强视觉感，使程序显得更直观、更专业。

怎样设计用户界面

同绘画一样，设计界面就是用户按自己的意愿，在工作表或用户窗体中有目的地添加控件，并给这些控件指定功能。

6.1.2　控件，必不可少的调色盘

Excel里有两种类型的控件：窗体控件（【窗体】工具栏中的控件）和ActiveX控件（【控件工具箱】中的控件）。

窗体控件

要向工作表中添加窗体控件，应先调出【窗体】工具栏，如图6-1所示。

图6-1　调出【窗体】工具栏

【窗体】工具栏中一共有16个控件，其中有9个可以添加到工作表中，如图6-2所示，控件说明见表6-1所示。

图6-2　窗体工具栏中可使用的控件

表6-1　各种窗体控件的说明

控件名称

控件说明

标签

用于输入和显示静态文本

分组框

用于组合其他多个控件

按钮

用于执行宏命令

复选框

选择控件，可以多项选择

选项按钮

用于选择的控件，通常几个选项按钮用组合框组合在一起

使用，在一组中只能同时选择一个选项按钮

列表框

显示多个选项的列表，用户可以从中选择一个选项

组合框

提供可选择的多个选项，用户可以选择其中的一个项目

滚动条

包括水平滚动条和垂直滚动条

微调控件

通过单击控件的箭头来选择数值

ActiveX控件

要添加ActiveX控件，应先调出【控件工具箱】，如图6-3所示。

图6-3　调出控件工具栏

默认情况下，【控件工具箱】中有11个控件，如图6-4所示。

图6-4　控件工具箱中默认的控件

但能使用的ActiveX控件并不只有11个，单击【控件工具箱】中的【其他控件】按钮，如图6-5所示。

图6-5　选择其他的控件

6.2　使用控件，将工作表当作画布

窗体控件和ActiveX控件就像两个调色盘，画蓝天的时候从里面选择蓝色，画白云的时候从里面选择白色。而Excel的工作表就像一张大画布，你可以在上面任意勾勒，绘出一幅漂亮的蓝天白云图。

6.2.1　在工作表中使用窗体控件

添加一个组合框控件

调出【窗体】工具栏，选中组合框控件，按住鼠标左键的同时，拖动鼠标即可在工作表中添加控件，如图6-6所示。

图6-6　添加控件

设置控件格式

设置控件格式的步骤如图6-7所示。

图6-7　设置控件

如果想修改控件的名称，可以选中控件，在名称框里修改，如图6-8所示。

图6-8　修改控件的名称

使用控件

使用窗体控件的步骤如图6-9所示。

图6-9　使用窗体控件

6.2.2　在工作表中使用ActiveX控件

向工作表中添加选项按钮

在【控件工具箱】中选择【选项按钮】控件，按住鼠标左键的同时，拖动鼠标即可在工作表中添加一个选项按钮，如图6-10所示。

图6-10　在工作表中添加选项按钮

设置控件格式

新添加的按钮，可以在【属性窗口】中设置它的属性来更改它的外观样式，如图6-11所示。

图6-11　设置ActiveX控件的格式

继续在工作表中添加一个选项按钮控件，在【属性窗口】中设置标签为“女”，名称为xb2，如图6-12所示。

图6-12　新添加的ActiveX控件

为控件添加程序

ActiveX控件与窗体控件不同，在使用前，需要用户针对控件编写相应的代码。前文添加的两个控件，如果想知道用户选择的是“男”还是“女”，得分别给这两个控件编写事件过程，如图6-13所示。

图6-13　为控件添加事件过程

用同样的方法为控件xb2编写事件过程，如图6-14所示。

图6-14　为控件添加的程序代码

使用控件

返回工作表区域，单击【控件工具箱】中的【退出设计模式】按钮，就可以使用控件了，如图6-15所示。

图6-15　在工作表中使用ActiveX控件

6.2.3　窗体控件和ActiveX控件的区别

窗体控件只能在工作表中通过设置控件的格式或指定宏来使用，ActiveX控件拥有很多属性和事件，可以在工作表和用户窗体中使用。如果是以编辑数据为目的，一般使用窗体控件就可以了，但如果在编辑数据的同时还要进行其他操作，使用ActiveX控件会灵活得多。

6.3　与用户交互，简单的输入输出对话框

对话框是程序与用户进行交互的工具，用来输入或输出信息。

6.3.1　InputBox函数

InputBox函数创建一个接受用户输入的对话框，供用户输入数据。

运行程序后Excel会显示一个对话框，如图6-16所示。

图6-16　运行程序后显示的对话框

根据提示，在对话框中输入姓名后单击【确定】按钮，输入的姓名即可自动写进活动工作表的A1单元格，如图6-17所示。

图6-17　利用对话框输入姓名

编写代码时，所有的参数名称都是可以省略的。

除了prompt参数外，其实参数都可以省略。

也可以只省略其中的一个或几个参数。

6.3.2　Application对象的InputBox方法

使用Application对象的InputBox方法也可以创建接受用户输入的对话框。

除了Left与Top参数，InputBox方法的其他参数与InputBox函数的参数的作用相同。

InputBox方法的Left和Top参数指定对话框在Excel工作表窗口中的位置，如图6-18所示，而InputBox函数的xpos与ypos参数指定对话框在整个屏幕窗口中的位置，如图6-15所示。

图6-18　InputBox方法的Top与Left参数

既生InputBox函数，何生InputBox方法

对比一下它们的参数区别，可以知道二者的区别。

完整的参数，可以在VBA帮助里看到，也可以在输入代码的时候，在代码窗口里看到，如图6-19所示。

图6-19　在代码窗口中查看参数

InputBox函数只能返回一个String型的字符串，而InputBox方法返回的数据类型不确定，并且，InputBox方法比InputBox函数多一个Type参数。

Type参数有什么作用

设置InputBox方法的Type参数值，指定返回的数据类型，如表6-2所示。

表6-2　Type参数说明

参数值

含义

0

公式

1

数字

2

文本（字符串）

4

逻辑值（True或False）

8

单元格引用（Range对象）

16

错误值，如#N/A

64

数值数组

下面的程序让用户选择一个单元格区域，然后在单元格区域输入数值100，如图6-20所示。

图6-20　使用InputBox方法

如果返回值为多种类型中的一种，就把Type参数的值设为相应类型的和。

练习小课堂

在表6-2中，你知道Type的参数值为什么是0、1、2、4、8……这样有间隔而不是连续的自然数吗？

参考答案

因为中间空开的数值（如3）可以是多种参数值之和（如1+2），为了不混淆，所以参数值是有间隔而不是连续的自然数。

6.3.3　MsgBox函数

使用MsgBox函数，可以创建一个对话框，告诉用户某些信息，并等待用户单击其中某个按钮后继续运行。

其效果如图6-21所示。

图6-21　MsgBox函数创建的对话框

设置显示的按钮

MsgBox函数一共有6种按钮设定，如表6-3和图6-22所示。

表6-3　Msgbox的6种按钮设定

常数

值

说明

vbOkonly

0

只显示【确定】按钮

vbOkCancel

1

显示【确定】和【取消】两个按钮

vbAbortRetryIgnore

2

显示【终止】、【重试】和【忽略】3个按钮

vbYesNoCancel

3

显示【是】、【否】和【取消】3个按钮

vbYesNo

4

显示【是】和【否】两个按钮

vbRetryCancel

5

显示【重试】和【取消】两个按钮

图6-22　Msgbox函数6种按钮设定

设置显示的图标样式

MsgBox函数一共有4种图标样式，如表6-4和图6-23所示。

表6-4　Msgbox的4种图标样式

常数

值

说明

vbCritical

16

显示“关键信息”图标

vbQuestion

32

显示“警告询问”图标

vbExclamation

48

显示“警告消息”图标

vbInformation

64

显示“通知消息”图标

图6-23　不同的图标样式

可以同时设置显示的按钮和图标，在两个常数或值之间用+号连接：

其效果如图6-24所示。

图6-24　设置按钮及显示图标

设置缺省按钮

默认情况下按回车键即可执行的按钮称为缺省按钮，如图6-25所示。

图6-25　缺省按钮

如果要修改第二个按钮为缺省按钮，可以设置Buttons参数的参数值。

其效果如图6-26所示。

图6-26　设置第2个按钮为缺省按钮

如果想设置第三个、第四个按钮为缺省按钮，相应的参数如表6-5所示。

表6-5　设置缺省按钮的参数

常数

值

说明

vbDefaultButton1

0

第一个按钮为缺省按钮

vbDefaultButton2

256

第二个按钮为缺省按钮

vbDefaultButton3

512

第三个按钮为缺省按钮

vbDefaultButton4

768

第四个按钮为缺省按钮

指定对话框类型

Buttons参数还有第四组设定值，用来决定对框的类型，如表6-6所示。

表6-6　对话框的类型

常数

值

说明

vbApplicationModal

0

应用程序强制返回；暂停执行应用程序，直到用户对消息框

作出响应才继续工作

vbSystemModal

4096

系统强制返回；暂停执行所有程序，直到用户对消息框作出

响应才工作

MsgBox函数的返回值

设置Buttons参数可以让对话框显示不同的按钮，单击不同的按钮，将返回不同的值，如表6-7所示，程序可以根据返回值选择不同的操作，如：

表6-7　MsgBox函数的返回值

常数

值

描述

vbOK

1

单击【确定】按钮

vbCancel

2

单击【取消】按钮

vbAbort

3

单击【终止】按钮

vbRetry

4

单击【重试】按钮

vbIgnore

5

单击【忽略】按钮

vbYes

6

单击【是】按钮

vbNo

7

单击【否】按钮

6.3.4　Application对象的FindFile方法

使用Application对象的FindFile方法可以显示【打开】对话框，用户可以在对话框中选择并打开文件，如图6-27所示。

图6-27　用FindFile方法打开文件

6.3.5　Application对象的GetOpenFilename方法

可以调用Application对象的GetOpenFilename方法显示【打开】对话框，在对话框里选择文件，获得文件名称。

虽然同是显示【打开】对话框，但GetOpenFilename方法并不会打开选中的文件，而是返回所选文件的文件名（含路径）字符串，如图6-28所示。

图6-28　不使用参数时

可以使用FileFilter参数限制可选择的文件类型，如图6-29所示。

图6-29　使用参数限制文件类型

如果希望能在两种或多种类型的文件中选择，可以修改参数值，如图6-30所示。

图6-30　使用参数限制文件类型

也可以增加文件类型下拉列表中的项目，如图6-31所示。

图6-31　增加文件类型下拉列表中的项目

除了filefilter，GetOpemFilename方法还有其他参数，如图6-32所示。

图6-32　当设置更多参数时

6.3.6　Application对象的GetSaveAsFilename方法

可以调用Application对象的GetSaveAsFilename方法打开【另存为】对话框，在对话框里选择文件，获得文件名，如图6-33所示。

图6-33　用GetSaveAsFilename方法获取文件名

6.3.7　Application对象的FileDialog属性

使用Application对象的FileDialog属性可以获得指定目录的路径及名称，如图6-34所示。

图6-34　获取目录名称

除了msoFileDialogFolderPicker，filedialogtype参数还可以选用其他的值，如表6-8所示。

表6-8　msoFileDialogType常量列表

常量

说明

msoFileDialogFilePicker

允许选择一个文件

msoFileDialogFolderPicker

允许选择一个文件夹

msoFileDialogOpen

允许打开一个文件

msoFileDialogSaveAs

允许保存一个文件

6.4　构建用户窗体，自己设计交互界面

6.4.1　关于用户窗体

用户窗体是Excel中的另一对象——UserForm对象。用户可以在窗体上自由添加ActiveX控件，并利用这些控件从用户那里获得信息，或将信息输出给用户。

6.4.2　添加一个用户窗体

添加一个用户窗体的方法如图6-35和图6-36所示。

方法一：

图6-35　利用菜单命令插入窗体

方法二：

图6-36　利用右键菜单插入窗体

6.4.3　设置窗体的属性

作为对象，用户窗体也有自己的属性，如名称、大小等。

新窗体的所有属性都是默认的，可以在【属性窗口】中重新设置，如图6-37所示。

图6-37　设置窗体

想将窗体设置为何种样式，就在【属性窗口】里修改对应的属性。

为了便于查询，Excel允许用户按不同的排序方式查看对象的属性，如图6-38所示。

图6-38　按分类序查看对象属性

如果对属性列表中某个属性不太熟悉，选中属性名称，按<F1>键，即可查看关于它的帮助信息，如图6-39所示。

图6-39　查看帮助信息

6.4.4　在窗体上添加控件

新插入的窗体，只是一个空白的对话框，要想实现同用户交互的目的，需要往窗体上添加不同的控件。要向窗体中添加控件，应先调出【工具箱】，如图6-40所示。

图6-40　调出工具箱

向窗体中添加控件的方法如图6-41和图6-42所示。

图6-41　向窗体中添加控件

图6-42　设置控件的属性

练习小课堂

参照添加和设置标签控件的方法，继续在窗体上添加其他控件，设计一个简单的信息录入界面，如图6-43所示。

图6-43　信息录入界面

6.4.5　显示窗体

显示窗体就是把设计好的窗体显示给用户。

手动显示窗体

手动显示窗体的方法如图6-44所示。

图6-44　手动显示窗体

用代码显示窗体

显示一个窗体要经过两个步骤：

如果在调用窗体的Show方法前窗体没有加载，Excel会自动加载这个窗体，然后再显示它。

所以显示窗体可以省略加载窗体的语句，直接调用窗体对象的Show方法。

窗体的显示模式

模式窗体：窗体显示后将停止执行“显示窗体”之后的代码，直到退出或隐藏窗体，并且只有退出或隐藏窗体后，才可以操作窗体外的其他元素，如图6-45所示。

图6-45　显示模式窗体

无模式窗体：窗体显示后会继续执行程序里余下的语句，并且可以操作其他窗体或界面，如图6-46所示。

图6-46　显示无模式窗口

6.4.6　关闭窗体

手动关闭窗体

手动关闭窗体如图6-47所示。

图6-47　手动关闭窗体

使用代码关闭窗体

如果想取消显示窗体，可以隐藏或卸载它。

尽管隐藏和卸载窗体都能将窗体从屏幕上删除，但因为显示一个隐藏的窗体比显示一个卸载的窗体用的时间短，所以当需要反复使用某个窗体时，建议用Hide方法隐藏，而不用Unload语句卸载它。

6.4.7　使用控件

作为对象，窗体和窗体上的控件，都有不同的事件。

想让窗体真正工作起来，应为窗体和控件编写相应的事件过程。

初始化窗体，UserForm对象的Initialize事件

加载窗体时会触发Initialize事件。在这个事件中，可以对窗体、变量等进行初始化设置，如图6-48所示。

图6-48　使用Initialize事件

设置性别复合框的条目为“男”和“女”后如图6-49所示。

图6-49　使用控件

为命令按钮添加事件过程

Step 1：用同样的方法给“确定”按钮添加事件过程。

Step 2：给【退出】按钮添加事件过程。

使用窗体录入数据

完成上述设置后，显示窗体，就可以使用窗体向工作表中录入数据了，如图6-50所示。

图6-50　使用窗体录入数据

6.4.8　用键盘控制控件

更改控件的<Tab>键顺序

只有对象具有焦点时，才能接受键盘输入。控件的<Tab>键顺序决定用户在按下<Tab>键或<Shift+Tab>组合键后激活控件的顺序。在设计用户窗体时，系统会按添加控件的先后顺序确定控件的<Tab>键顺序。当然，这个顺序是可以更改的，如图6-51所示。

图6-51　更改控件的<Tab>键顺序

给控件指定快捷键

给控件指定快捷键如图6-52所示。

图6-52　给控件设置快捷键

设置【确定】按钮的Accelerator属性为N后，按下<Alt+N>组合键，就等同于在窗体中单击【确定】按钮。

6.5　改造Excel现有的界面

6.5.1　更改标题栏的程序名称

Excel的标题栏用于显示程序及文件名称，默认的程序名称为"Microsoft Excel"，可以设置Application对象的Caption属性修改它，如图6-53所示。

图6-53　更改标题栏程序名称

6.5.2　显示或隐藏菜单栏

隐藏菜单栏的代码如下所示：

其效果如图6-54所示。

图6-54　隐藏菜单栏

也可以使用循环语句来隐藏所有的菜单项：

如果要隐藏全部的菜单项，代码可以为：

设置的属性不同，最后得到的效果也不相同，如图6-55所示。

图6-55　设置Visible与Enabled属性的区别

6.5.3　显示或隐藏工具栏

默认情况下，Excel窗口中显示的工具栏只有【常用】工具栏和【格式】工具栏，如图6-56所示。

图6-56　默认显示的工具栏

可以通过相应的代码来隐藏或显示它们，如图6-57所示。

图6-57　隐藏常用和格式工具栏

如果Excel窗口中显示的工具栏不只两种，如图6-58所示。

图6-58　实际可能会同时显示很多工具栏

想隐藏窗口中显示的所有工具栏，可以使用程序：

其效果如图6-59所示。

图6-59　隐藏所有工具栏

6.5.4　设置窗口

还可以对工作表的窗口进行设置，如图6-60所示。

图6-60　设置窗口

6.5.5　其他设置

6.6　典型的技巧或示例

6.6.1　设计一张调查问卷

Excel Home免费培训中心第一期培训结束了，希望通过调查问卷了解学员对培训的评价和建议，因此，需要设计一张调查问卷，如图6-61所示。

图6-61　用Excel设计的调查问卷

问卷的内容包括学员基本资料、单项选择、多项选择和学员建议4部分。

设计学员基本资料部分

设计学员基本资料部分的操作如图6-62所示。

图6-62　设置学员基本资料

设计单项选择部分

设计单项选择部分的操作如图6-63所示。

图6-63　设计单项选择部分

设计多项选择部分

设计多项选择部分的操作如图6-64所示。

图6-64　设计多项选择部分

设计其他部分

其他部分的设计如图6-65所示。

图6-65　其他部分的设计

装饰问卷界面

Step 1：设置适合的背景或边框线、字体及其颜色，如图6-66所示。

图6-66　修饰界面

Step 2：取消显示网格线，如图6-67所示。

图6-67　取消显示网格线

Step 3：隐藏多余的行和列区域。

自动登记调查结果

为方便统计分析，学员填写完问卷后，要将结果保存到工作簿中的“调查结果”工作表里，如图6-68所示，可以编写一个程序来实现这个目的。

图6-68　保存结果的工作表

Step 1：插入一个标准模块，在模块中输入程序。

Step 2：在“问卷”工作表中添加一个按钮，更改标签为“提交问卷”，如图6-69所示，将编写的程序指定给按钮。

图6-69　添加按钮

Step 3：填写并保存问卷结果，如图6-70所示。

图6-70　使用问卷

保护问卷工作表

如果你担心设计好的表格会被别人修改，可以锁定该工作表。

Step 1：锁定单元格，如图6-71所示。

图6-71　锁定单元格

取消锁定要输入数据的单元格，如图6-72所示。

图6-72　取消锁定单元格

Step 2：保护工作表，如图6-73所示。

图6-73　保护工作表

6.6.2　职工信息管理界面

阿强是单位人事部的工作员，负责管理所有职工的档案，如图6-74所示。

图6-74　职工信息表

带着这个念头，阿强决定动手设计一个职工信息管理界面。

设计界面内容，添加ActiveX控件

添加ActiveX控件，如图6-75所示。

图6-75　添加ActiveX控件

美化界面

你可以根据需要随意对工作表进行装饰，如设置单元格的边框、底纹、字体、将多余的行和列隐藏，锁定不需要修改的单元格，设置工作表背景等，如图6-76所示。

图6-76　美化后的界面及各个控件的功能

给控件添加代码

管理界面中每个按钮要达到的目的都可以通过编写不同的事件过程来实现。

因为所有的控件都绘制在工作表中，因此所有的事件过程都必须保存在控件所在的工作表对象里。

右键单击工作表标签，执行【查看代码】菜单命令，在【代码窗口】中输入代码：

完成后返回工作表区域，退出设计模式，就可以使用设计的界面了。

练习小课堂

利用工作表设计的界面很容易被人改动，试一试，能不能利用窗体来设计这个界面？

参考答案

参阅6.6.3小节中设置登录界面的方法。

6.6.3　一个简易的登录窗体

设计登录窗体界面

Step 1：新插入一个窗体，用鼠标指针调整大小，直到满意为止，如图6-77所示。

图6-77　新插入的窗体

Step 2：在窗体上添加控件，如图6-78所示。

图6-78　登录窗体上的控件

Step 3：更改窗体的名称及标题栏显示的文本，并对窗体作适当装饰，如图6-79所示。

图6-79　设置窗体属性

Step 4：设置文字框的属性（如字体），特别设置输入密码的文字框的PasswordChar属性为“*”，如图6-80所示。

图6-80　设置密码输入框

为控件指定功能

因为只有当用户名和密码都输入正确后，才显示Excel的编辑界面，所以在打开工作簿时，应先将Excel界面隐藏，同时显示设计的登录窗体。

Step 1：在ThisWorkbook模块中写入程序：

Step 2：设置初始用户名和密码。

新建名称来保存用户名，如图6-81所示。

图6-81　新建名称保存用户名

再新建一个名称来保存登录密码，名称名为：UserWord，初始密码为：1234。

练习小课堂

为了不让别人在【定义名称】对话框中看到保存用户名和密码的名称，可以将定义的名称隐藏。怎样隐藏名称（参阅4.6.1小节），动手试一试。

参考答案

Step 3：为【确定】按钮添加代码。

双击【确定】按钮，在打开的【代码窗口】中输入程序：

Step 4：为【退出】按钮添加代码。

双击【退出】按钮，在【代码窗口】中输入程序：

Step 5：为【更改用户名】按钮添加代码。

双击【更改用户名】按钮，在【代码窗口】中输入程序：

Step 6：为【更改密码】按钮添加代码。

双击【更改密码】按钮，在【代码窗口】中输入程序：

Step 7：设置单击窗体关闭按钮失效，如图6-82所示。

图6-82　设置单击窗体关闭按钮失效

双击登录窗体的空白处，在【代码窗体】中输入程序：

设置完成后保存并关闭工作簿，重新打开它，就可以使用登录窗体了。

第7章

代码调试与优化

在Word里写一篇讲话稿，无论多么认真仔细，都会出现一些失误。如不小心打上几个错别字或写下几个病句等，要想一次性完成一篇优秀的文章而不出现任何错误是极少见的。

编写程序也是如此，在编写过程中，总会有一些问题被自己忽略。

文章需要修改，代码也需要调试。

7.1　VBA中可能会发生的错误

7.1.1　编译错误

如果编写代码时不遵循VBA的语法规则，如未定义变量、函数或属性名称拼写错误、语句不配对（如有If没有End If，有For没有Next）等都会引起编译错误，如图7-1所示。

存在编译错误的程序，运行时系统会显示一个提示对话框，程序不会被执行，如图7-1所示。

图7-1　运行存在编译错误的程序

7.1.2　运行时错误

如果程序在运行过程中试图完成一个不可能完成的操作，如除以0、打开一个不存在的文件、删除一个打开的文件等都会发生运行时错误。

运行存在运行时错误的程序，执行到错误代码所在行时，Excel会显示一个错误提示对话框，如图7-2所示。

图7-2　运行存在运行时错误的程序

7.1.3　逻辑错误

当程序中的代码没有语法问题，程序运行时，也没有不能完成的操作，但程序运行结束后，却不能得到预期的结果，这样的错误称为逻辑错误。

把1到10的自然数依次写进A1:A10单元格，如果程序写成这样：

这个程序里的每句代码都没有语法错误，也没有不能完成的操作，但运行后却不能得到预期的效果，如图7-3所示。

图7-3　程序运行前后

编写程序时，很多原因都会引起逻辑错误。如：循环变量的初值和终值设置错误，变量类型不正确，代码顺序不正确等，而这些代码单独存在并没有任何问题。

同其他两种错误不同，存在逻辑错误的程序，运行后程序会正常执行，Excel并不会给出任何提示。所以，逻辑错误最难被发现，但在所有错误类型中占的比例却最大。

因此，调试代码时，多数时间都是在修改程序中存在的逻辑错误。

7.2　VBA程序的3种状态

7.2.1　设计模式

设计模式是用户设计和编写VBA程序时程序所处的模式。当程序处于设计模式时，用户可以对程序进行任意修改。

7.2.2　运行模式

程序正在运行时的模式称为运行模式。

在运行模式下，用户可以通过输入输出对话框与程序“对话”，也可以查看程序的代码，但不能修改程序。

7.2.3　中断模式

中断模式是程序被临时中断执行（暂停执行）时所处的模式。

在中断模式下，用户可以检查程序存在的错误或修改程序的代码，可以逐句执行程序，一边发现错误，一边更正错误。

7.3　Excel已经准备好的调试工具

对于不太复杂的程序，寻找错误并不太难。当程序过长时，从满堆的代码中查找和修正错误就要伤神得多。

幸运的是，Excel已经准备好了一套方便有效的代码调试工具，善用它，可以使调试代码的工作变得更简单、更快捷。

7.3.1　让程序进入中断模式

正因为在中断模式下可以一边运行程序，一边发现错误并修正错误，所以调试代码多数时间都选择在中断模式下进行。

出现编译错误时

如果程序存在编译错误，运行时系统会自动显示错误提示对话框，如图7-1所示。

对话框上有两个按钮，单击【帮助】按钮可以查看该错误的帮助信息；单击【确定】按钮即可进入中断模式，如图7-4所示。

图7-4　出现编译错误时

出现运行时错误

如果程序存在运行时错误，运行后会停止在发生错误的代码所在行，自动显示错误提示对话框，如图7-2所示。

这时可以单击对话框上的【调试】按钮让程序进入中断模式，如图7-5所示。

图7-5　出现运行时错误

中断一个正在执行的程序

如果程序中没有出现编译错误和运行时错误，程序会一直执行，直到结束。如果出现死循环，会一直执行下去，不会中止。如：

如果一个程序正在运行，当按下<Esc>键或<Ctrl+Break>组合键后，系统会中断执行它，并弹出提示对话框，如图7-6所示，单击对话框上的【调试】按钮即可让程序进入中断模式。

图7-6　中断一个正在运行的程序

7.3.2　为程序设置断点

什么是断点

断点像公路中途在检查站，当汽车开到这里就得停车接受检查。

如果你怀疑程序中某行（或某段）的代码存在问题，可以在该处设置断点。当程序运行到断点所在行时会暂停执行，停止在断点所在行，进入中断模式，如图7-7所示。

图7-7　程序停止在断点所在行

进入中断模式后，可以按<F8>键逐句执行程序，观察运行情况，从而发现并修正存在的错误。

给程序设置断点

方法一：

设置断点的方法一，如图7-8所示。

图7-8　利用<F9>键设置或清除断点

方法二：

设置断点的方法二，如图7-9所示。

图7-9　利用菜单命令设置或清除断点

方法三：

设置断点的方法三，如图7-10所示。

图7-10　单击边界条设置或清除断点

如果想清除程序中的所有断点，可以依次执行【调试】→【清除所有断点】菜单命令（或按<Ctrl+Shift+F9>组合键），如图7-11所示。

图7-11　清除程序中的所有断点

7.3.3　使用Stop语句

给程序设置的断点会在关闭文件的同时自动取消，如果你需要重新打开工作簿后继续使用设置的断点，可以使用Stop语句。

在程序里加入一个Stop语句，就像给程序设置了一个断点，当程序运行到Stop语句时，会停止在Stop语句所在行，进入中断模式，如图7-12所示。

图7-12　使用Stop语句中断程序

Stop语句在重新打开文件后依然存在，当不再需要Stop语句时，需要手动清除它。

7.3.4　使用立即窗口

如果你怀疑程序中的错误是因为变量设置错误引起的，可以在程序中使用Debug. Print语句将程序运行中变量或表达式的值输出到【立即窗口】中，程序运行结束后，在【立即窗口】中查看变量值的变化情况，如图7-13所示。

图7-13　使用立即窗口查看变量的值

如果程序处于中断模式下，也可以将光标移到变量名称上，直接查看变量的值，如图7-14所示。

图7-14　在中断模式下查看变量的值

7.3.5　使用本地窗口

在中断模式下，还可以利用【本地窗口】查看变量的数据类型和当前值，如图7-15所示。

图7-15　使用本地窗口查看变量的值和数据类型

如果【本地窗口】没有打开，可以依次执行【视图】→【本地窗口】菜单命令打开它，如图7-16所示。

图7-16　调出本地窗口

7.3.6　使用监视窗口

在中断模式下还可以使用【监视窗口】观察程序中变量或表达式的值。

使用【监视窗口】来监视变量或表达式前，必须先定义要监视的变量或表达式，监视表达式可以在设计模式或中断模式下定义。

使用快速监视

使用快速监视如图7-17所示。

图7-17　快速监视

完成后为程序设置断点，运行程序，就可以在【监视窗口】中看到相应的信息了，如图7-18所示。

图7-18　使用监视窗口

手动添加监视

手动添加监视如图7-19所示。

图7-19　手动添加监视

只有当程序处于中断模式时才能使用【监视窗口】，所以只有将程序切换到中断模式，【监视窗口】才能正常工作。

编辑或删除监视表达式

编辑或删除监视表达式如图7-20所示。

图7-20　编辑或删除监视

7.4　错误处理的艺术

因为总会有许多意想不到的错误发生，如激活一个不存在的工作表，删除一个已经打开的文件，所以无论多么认真和仔细，都不能避免程序在运行时发生错误。

但有些错误是可以预先知道的，所以可以在程序中加入一些错误处理的代码，保证程序正常运行。

VBA通过On Error语句捕获运行时错误，该语句告诉VBA，如果运行程序时出现错误应该怎么做。

On Error语句有3种形式。

7.4.1　Go Error GoTo标签

Go Error GoTo该语句告诉VBA，当发生错误时，继续执行标签所在行及之后的代码。

没有出现错误的提示对话框，如图7-21所示。

图7-21　使用On Error捕获错误

关于标签的设置，请参阅3.7.7小节中对GoTo语句的介绍。

7.4.2　On Error Resume Next

该语句告诉VBA：如果程序发生错误，继续执行错误行后面的代码。

如果在程序中加入On Error Resume Next语句，运行程序时，即使程序中存在运行时错误，也不会中断程序，显示错误信息，并且会继续执行错误语句之后的代码。

运行这个程序后，无论当前活动工作簿中是否存在abc工作表，都不会出现错误信息，也不会出现最后Msgbox函数的对话框。

发现了吗？

在程序中，总是把"On Error GoTo标签"或"On Error Resume Next"放在可能出错的代码之前，这是因为只有On Error语句之后出现的运行时错误才能被捕捉到，所以通常把捕捉错误的语句放在程序开始处。

7.4.3　On Error GoTo 0

使用On Error GoTo 0语句后，将关闭对程序中运行时错误的捕捉。如果在On Error GoTo 0语句后，代码再出现运行时错误，尽管在程序一开始已经写入"On Error GoTo标签"或"On Error Resume Next"，运行时错误都不会被捕捉到，如图7-22所示。

图7-22　关闭错误捕捉

7.5　让代码跑得更快一些

7.5.1　合理地使用变量

声明变量为合适的数据类型

不同的数据类型占据不同大小的内存空间，如表3-1所示，数据所占内存空间的大小直接影响计算机处理数据的速度。因此，为了提高程序的效率，在声明变量时，在满足需求的前提下，应该尽量选择占用字节少的数据类型。

尽量不使用Variant型数据

Variant是VBA中一种特殊的数据类型，所有没有声明数据类型的变量都默认为Variant型。但Variant型所占据的存储空间远远大于其他数据类型，所以除非必须需要，否则应避免声明变量为Variant型。

不要让变量一直待在内存里

如果一个变量只在一个过程里使用，请不要声明它为公共变量，尽量减少变量的作用域，这是一个好习惯。

如果你不再需要使用某个变量（尤其是对象变量）了，请记得释放它，不要让它一直呆在内存里。

7.5.2　避免反复引用相同的对象

无论是引用对象，还是调用对象的方法或属性，都会用到点（.）运算符，每次运行程序，计算机都会对这些点（.）运算符进行解析，当点（.）运算符过多时，会花去不少的时间。

在这个程序中，ThisWorkbook.Worksheets(1).Range("A1")是每一句代码都反复引用的对象。

引用对象不可避免，但当反复引用同一个对象时，可以用一些方法来简化它，从而减少点（.）运算符。

使用With语句简化引用对象

还可以使用嵌套的With语句进一步简化程序：

关于With语句，请参阅3.7.7小节的内容。

使用变量简化引用对象

除了With语句，还可以使用变量来简化对相同对象的引用。如：

还可以借助With语句进一步简化输入：

7.5.3　尽量使用函数完成计算

Excel已经准备了很多现成的函数（工作表函数和VBA内置函数），尽管你可以通过编写程序去实现相同的目的，但对于相同的计算，使用函数比编写程序解决效率要高很多。

7.5.4　去掉多余的激活和选择

如果你的程序是通过录制宏得到的，那里面一定有很多的激活和选择操作，即Activate方法和Select方法。如：

这是一个复制单元格的程序，调用了4次Select方法。但事实上并不需要激活工作表，选中单元格后才能执行复制、粘贴操作，所以这些选中工作表和单元格的代码都是多余的，程序可以简化为：

去掉多余的操作，不仅可以让程序更简洁，降低阅读和调试的难度，还能提高程序运行的速度。

7.5.5　合理使用数组

下面的程序把1到65536的自然数写入A1:A65536中。

逐个将数据写入单元格，在笔者的电脑上，程序运行的时间是2.52秒，如图7-23所示。

图7-23　逐个将数据写入单元格需要的时间

很明显，这样的操作是比较费时的。

如果想提高运行速度，可以先把数据写入数组，再通过数组批量写入单元格，如：

使用数组后的程序只需要0.09秒的时间，速度提高了28.1倍，如图7-24所示，差距显而易见。

图7-24　数组批量写入单元格所需的时间

将一维数组写入A列单元格前，必须先使用工作表的TRANSPOSE函数将数组进行转置。如果想省去转置的计算步骤，可以直接将数组定义为一个多行一列的二维数组，如：

7.5.6　关闭屏幕更新

设置Application对象的ScreenUpdate属性为False（参阅4.2.1小节），在程序运行过程中关闭屏幕更新，可以在一定程度上缩短程序运行的时间。

如果你的程序很短，需要做的操作很少，那代码是否优化，也许差别并不大。但如果要处理的数据很多，进行的操作很复杂，编写的程序很长时，优化程序代码是很有必要的。

无论你现在是否接触到这些复杂的问题，但请相信我，养成一个良好的习惯，编写简洁、高效的代码会给你学习和使用VBA，最终成为一个VBA高手带来很大的帮助。

Table of Contents

书名页

版权页

目录

序

前言

第1章　走进Excel VBA的世界

1.1　不会Excel的人，真伤不起

1.1.1　做不完的表

1.1.2　神速的“超人”

1.1.3　你是怎样做工资条的

1.2　走自己的“录”，让别人重复去吧

1.2.1　什么是宏

1.2.2　用宏录下Excel操作

1.2.3　让录下的操作再现一遍

1.3　还可以怎样执行宏

1.3.1　给宏设置快捷键

1.3.2　将宏指定给按钮

1.3.3　将宏指定给常用工具栏按钮

1.4　是谁“挡住”了宏

1.4.1　宏为什么不能工作了

1.4.2　怎样修改宏安全级

1.5　VBA，Excel里的编程语言

1.5.1　录制宏不能解决的问题

1.5.2　让工资条一“输”到底

1.5.3　VBA编程，让你的表格更加灵活

1.5.4　什么是VBA

1.5.5　宏和VBA有什么关系

第2章　开始VBA编程的第一步

2.1　揭开神秘面纱背后的真面目

2.1.1　程序保存在哪里

2.1.2　应该怎样编写程序

2.2　程序里都有什么

2.2.1　代码

2.2.2　过程

2.2.3　模块

2.2.4　对象

2.2.5　对象的属性

2.2.6　对象的方法

2.2.7　关键字

2.3　VBA的编程环境——VBE

2.3.1　打开VBE编辑器

2.3.2　主窗口

2.3.3　菜单栏

2.3.4　工具栏

2.3.5　工程资源管理器

2.3.6　属性窗口

2.3.7　代码窗口

2.3.8　立即窗口

2.4　试写一个简单的VBA程序

2.4.1　添加或删除模块

2.4.2　动手编写程序

2.5　解除疑惑，一“键”倾心

第3章　Excel VBA基础语法

3.1　语法，编程的基础

3.1.1　这个笑话很凉快

3.1.2　VBA也有语法

3.1.3　学习VBA语法难吗

3.2　VBA里的数据类型

3.2.1　打酱油的故事

3.2.2　走进Excel的商店

3.2.3　VBA中有哪些数据类型

3.3　存储数据的容器：常量和变量

3.3.1　常量和变量

3.3.2　使用变量

3.3.3　使用常量

3.3.4　使用数组

3.4　集合、对象、属性和方法

3.4.1　对象，就像冰箱里的鸡蛋

3.4.2　对象的属性

3.4.3　对象的方法

3.5　连接的桥梁，VBA中的运算符

3.5.1　算术运算符

3.5.2　比较运算符

3.5.3　连接运算符

3.5.4　逻辑运算符

3.5.5　应该先进行什么运算

3.6　内置函数

3.6.1　VBA中的函数

3.6.2　VBA中有哪些函数

3.7　控制程序执行，VBA的基本语句结构

3.7.1　If…Then语句

3.7.2　Select Case语句

3.7.3　For…Next语句

3.7.4　Do While语句

3.7.5　Do Until语句

3.7.6　For Each…Next语句

3.7.7　其他的常用语句

3.8　Sub过程，基本的程序单元

3.8.1　关于VBA过程

3.8.2　编写Sub过程需要了解的内容

3.8.3　从另一个过程执行过程

3.8.4　过程的作用域

3.9　自定义函数，Function过程

3.9.1　试写一个函数

3.9.2　使用自定义函数

3.9.3　怎么统计指定颜色的单元格个数

3.9.4　声明函数过程，规范的语句

3.10　合理地组织程序，让代码更优美

3.10.1　代码排版，必不可少的习惯

3.10.2　怎样排版代码

3.10.3　注释，让代码的意图清晰明了

第4章　常用对象

4.1　与Excel交流，需要熟悉的常用对象

4.1.1　VBA编程与炒菜

4.1.2　VBA是怎么控制Excel的

4.1.3　应该记住哪些对象

4.2　一切由我开始，最顶层的Application对象

4.2.1　ScreenUpdating属性

4.2.2　DisplayAlerts属性

4.2.3　EnableEvents属性

4.2.4　WorksheetFunction属性

4.2.5　给Excel梳妆打扮

4.2.6　她和她的孩子们

4.3　管理工作簿，了解Workbook对象

4.3.1　Workbook与Workbooks

4.3.2　认识Workbook，需要了解的信息

4.3.3　实际操作，都能做什么

4.3.4　ThisWorkbook与ActiveWorkbook

4.4　操作工作表，认识Worksheet对象

4.4.1　认识Worksheet对象

4.4.2　操作工作表

4.4.3　Sheets与Worksheets

4.5　核心，至关重要的Range对象

4.5.1　多种方法引用Range对象

4.5.2　还可以怎样得到单元格

4.5.3　操作单元格，还需要了解什么

4.5.4　亲密接触，操作单元格

4.6　不止这些，其他常见的对象

4.6.1　名称，Names集合

4.6.2　单元格批注，Comment对象

4.6.3　给单元格化妆

4.7　典型的技巧与示例

4.7.1　创建一个工作簿

4.7.2　判断工作簿是否打开

4.7.3　判断工作簿是否存在

4.7.4　向未打开的工作簿中录入数据

4.7.5　隐藏活动工作表外的所有工作表

4.7.6　批量新建工作表

4.7.7　批量对数据分类

4.7.8　将工作表保存为新工作簿

4.7.9　快速合并多表数据

4.7.10　汇总同文件夹下多工作簿数据

4.7.11　为工作表建立目录

第5章　Excel事件

5.1　让Excel自动响应你的行为

5.1.1　让Excel自动问好

5.1.2　事件，VBA里的自动开关

5.1.3　事件过程

5.1.4　编写事件过程

5.2　Worksheet事件

5.2.1　关于Worksheet事件

5.2.2　常用的Worksheet事件

5.2.3　Worksheet事件列表

5.3　Workbook事件

5.3.1　关于Workbook事件

5.3.2　常用的Workbook事件

5.3.3　Workbook事件列表

5.4　别样的自动化

5.4.1　MouseMove事件

5.4.2　不是事件的事件

5.5　典型的技巧与示例

5.5.1　一举多得，快速录入数据

5.5.2　我该监考哪一场

5.5.3　让文件每隔一分钟自动保存一次

第6章　用户界面设计

6.1　在Excel中自由地设计界面

6.1.1　关于用户界面

6.1.2　控件，必不可少的调色盘

6.2　使用控件，将工作表当作画布

6.2.1　在工作表中使用窗体控件

6.2.2　在工作表中使用ActiveX控件

6.2.3　窗体控件和ActiveX控件的区别

6.3　与用户交互，简单的输入输出对话框

6.3.1　InputBox函数

6.3.2　Application对象的InputBox方法

6.3.3　MsgBox函数

6.3.4　Application对象的FindFile方法

6.3.5　Application对象的GetOpenFilename方法

6.3.6　Application对象的GetSaveAsFilename方法

6.3.7　Application对象的FileDialog属性

6.4　构建用户窗体，自己设计交互界面

6.4.1　关于用户窗体

6.4.2　添加一个用户窗体

6.4.3　设置窗体的属性

6.4.4　在窗体上添加控件

6.4.5　显示窗体

6.4.6　关闭窗体

6.4.7　使用控件

6.4.8　用键盘控制控件

6.5　改造Excel现有的界面

6.5.1　更改标题栏的程序名称

6.5.2　显示或隐藏菜单栏

6.5.3　显示或隐藏工具栏

6.5.4　设置窗口

6.5.5　其他设置

6.6　典型的技巧或示例

6.6.1　设计一张调查问卷

6.6.2　职工信息管理界面

6.6.3　一个简易的登录窗体

第7章　代码调试与优化

7.1　VBA中可能会发生的错误

7.1.1　编译错误

7.1.2　运行时错误

7.1.3　逻辑错误

7.2　VBA程序的3种状态

7.2.1　设计模式

7.2.2　运行模式

7.2.3　中断模式

7.3　Excel已经准备好的调试工具

7.3.1　让程序进入中断模式

7.3.2　为程序设置断点

7.3.3　使用Stop语句

7.3.4　使用立即窗口

7.3.5　使用本地窗口

7.3.6　使用监视窗口

7.4　错误处理的艺术

7.4.1　Go Error GoTo标签

7.4.2　On Error Resume Next

7.4.3　On Error GoTo 0

7.5　让代码跑得更快一些

7.5.1　合理地使用变量

7.5.2　避免反复引用相同的对象

7.5.3　尽量使用函数完成计算

7.5.4　去掉多余的激活和选择

7.5.5　合理使用数组

7.5.6　关闭屏幕更新