菜鸟也懂VBA-excle2007VBA入门教程

此文来自EXCLE论坛，仅供学习交流使用

前言

1 为什么要学VBA？

随着你的Excel水平不断的提高，以前你不会函数，现在会函数了，相信大家尝试到函数的甜头，可以批量操作一些数据，如，从文本里提取数字，按条件求和，自动生成工资条，考勤表，查询表，输入一个人的身份证号码，会自动填好“出生地”，“出生年月日”，“年龄”，“性别”等等，一谈起函数大家都会“不亦乐乎”。“津津乐道”，“和函数相见恨晚”，说实在话，我当时也是一种这样的心态，我学会的第一个函数if，当时高兴地睡不着觉。因为它可以判断“不及格，及格，良好，优秀”，感觉到这个函数太神奇了。对工作太有用了，于是我下定决心一定要把函数学好，当然中间也产生过“放弃”的念头。为什么呢？因为每一个函数的参数都是长长的，不认识的英文单词，然后又有一些朋友叫我查看Excel自带的“帮助”文件。极少一部分看“帮助”文件还是可以看懂的，大多数根本看“帮助”文件看不懂，发现自带的“帮助”是便于有一定基础的使用者查找和学习某个知识点，而对于新手来说，也像“侠客行”里的“石破天”看不懂石壁上的蝌蚪文，不能明白其意思。呵呵，一扯又扯远了，还是回到主题上来。当我们的函数学到了一定的水平时，可是，还是发现有一些问题不能解决。如果要解决用的函数特别复杂且很公式很长，有的根本用函数不能解决了。如一个单元格又有文字，又有数字，且数字出现的位置没有规律，且不只一次出现。要求把数字提取出来。又如提取工作薄里的各工作表名来制作目录。当然我们可以用函数实现，但是特别麻烦。又如，把多作工作簿汇总到一个工作簿里一个工作表时，方便我们汇总，现有的Excel功能无法批量操作，只能一个一个的，通过复制粘贴来完成。如果我们会VBA ，你又到了Ecel里的另一个天地了。开个玩笑，Excel VBA，就像“九阴真经”里的第九层，也就是说，你会了Excel的VBA，那么你就可以随心所欲驾驭Excel表格，别人要三天才能完成的报表，你只要半天，甚至更短的时间，这样大大可以提高你的工作效率，上班时，别人上班时忙来忙去，你倒是很轻松。

2 VBA的作用（提高工作效率）

2.1 完成Excel现有功能不能实现的功能 2.2 使重复的工作不再重复

2.3 自定义函数（方便不会用函数的朋友）

2.4 实现“人机对话”（通过弹出一些窗体和对话框）

2.5 自定义Excel选项卡（制作出适合自己的工作界面选项卡和功能） 2.6 开发一些小程序（如“进销存”和一些小工具）

3 学VBA的基础

3.1 要有一点函数基础，和会Excel基础操作。建议学完办公高级班的学生和在职办公人员学习VBA 3.2 学VBA需要很好的英文基础吗？

答案是否定，学习Excel里的VBA和英文基础没有什么必然联系，因为Excel里的VBA那些关键字什么的都是最基础的英语单词，只要你上过初中，大部分还都能认识，再者他们还都是固定的，你就是不认识，硬记下来也足够了，Excel里的VBA又不是让你用英语写文章，也不是让你通读英语文章！

目录

第一讲VBA介绍和VBE编辑环境 ........................................................................................................................ 1 第二讲 录制宏 指定宏 运行宏 修改宏 保存宏 加载宏 ................................................................................ 5 第三讲 VBA 语法基础 .......................................................................................................................................... 8 第四讲 单元格对象 ............................................................................................................................................ 12 第五讲 VBA语句 ................................................................................................................................................. 15 第六讲 代码调试和错误处理 ............................................................................................................................ 20 第七讲 工作表对象 ............................................................................................................................................ 23 第八讲 工作簿对象 ............................................................................................................................................ 26 第九讲 事件 ........................................................................................................................................................ 27 第十讲 窗体与控件 ............................................................................................................................................ 29 第十一讲 窗体与控件实例——开发隐藏工作表工具 .................................................................................... 33 第十二讲自定义函数 .......................................................................................................................................... 36 第十三讲 VBA数组基础（一） ......................................................................................................................... 38 第十四讲 VBA数组实例应用（二） ............................................................................................................... 42 第十五讲 字典基础知识（一） ........................................................................................................................ 45 第十六讲 字典实例应用（二） ........................................................................................................................ 48 第十七讲 自定义右键菜单 ................................................................................................................................ 50 第十八讲 自定义选项卡 .................................................................................................................................... 51

Excel 2007 VBA 教程

第一讲VBA介绍和VBE编辑环境

为什么要学VBA？

1 提高工作效率，在我们Excel办公中，许多工作是重复的，我们可以通过录制宏把那些动作录制下来。下一

次做时就不要重复做了，只要点一下一个按钮，就帮我们完成了。

2 现有功能很难实现或者实现要分许多步骤且比较复杂，方便一些新手操作和普通用户操作。

3 实现一些适合自己工作的一些特殊功能，因为每一个人，每一个行业存在的不同需求，而微软公司不能面面

俱到，有时要通过我们自己用VBA来实现。

4 可以定制个性化的选项卡和命令，实现方便自己工作的操作的窗口界面 5 开发一些小程序 VBA介绍

6 VBA是VB的一个分支，是一门简单易用的编程语言，说的通俗一点就是由一些代码和语句按照一定的逻辑组

成，能实现一些功能和作用的代码 VBE窗口介绍 7 VBA窗口

7.1.1 用快捷键（Alt+F11），打开就可以看到（图 1）的界面，这个就是VBE窗口界面,和2003版本的

没有变化，还是一样的。

图 1

图1的上面是菜单栏和工具栏，和平常的2003版本的Excel菜单栏和有工具栏一样， 图1的左边是“工程资源管理器”和属性窗口 图1的右下边是“立即窗口”

立即窗口的作用，便于快速验证VBA语句，在立即窗口中输入msgbox \我在学习VBA\然后按一下回车键，就会弹出一个提示框。

7.5.1 用双击“工程资源管理器”里的Sheet1(Sheet1),就可以看到如（图 2）所示的

就可以看到对象列标表和过程列标框，白色的区域那一块就是我们要写代码的区域。

7.2 7.3 7.4 7.5

第 1 页 共 52 页

Excel 2007 VBA 教程

图 2

8

添加模块，窗体，类模块（打开VBE窗口是没有这个三个模块的，只有对象模块） 8.1 插入菜单，模块（有的也叫做标准模块） 8.2 插入菜单，类模块 8.3 插入菜单，窗体 9 删除模块，窗体，类模块

9.1 如果想不要模块，窗体，类模块，把光标对准相应的模块右击，移除模块。

10 对象浏览器窗口，视图菜单，对象浏览器（也可以按快捷F2）可以帮我们找到对象的属性，方法，事件的一

些用法。

11 设置VBE的编辑环境，为了便于自己编写和查看，调试代码，要对VBE编辑窗口一些设置进钩选，选择适合

自己的的选项。如（图 3）

图 3

11.1 字号大小：工具菜单——>>选项——>>编辑器格式——>>大小（S）——>>如（图 4），一般我们设置

为11磅

第 2 页 共 52 页

Excel 2007 VBA 教程

图 4

11.2 自动弹出成员列表：自动弹出成员列这个功能对于初学者和老用户来说都是非常有用，因为我们刚学

的时候，特别是英文基础差一点的学生来说，可以给大家很大的帮助。举个例子，工具菜单，选项，编辑器，钩起“自动列出成员（L）”，然后在任何代码窗口中输入“Sheets加上一个点号”，就会弹出如（图 5）所以的成员列表，绿色图标的是方法，有一个手形的图标是属性

图 5

11.3 要求声明变量：举个例子，工具菜单——>>选项——>>编辑器——>>钩起“要求变量声明（R）”，然后

新建一个标准模块，就会在新建标准模块的最顶端会自动添加Option Explicit然后在标准模块里输入，按F5执行代码，就会弹出（图 6）这样的提醒，如果我们把Option Explicit这一句删除，再按F5执行代码就不会弹出（图 6）这样的提醒 Sub test()

Set MyRg = Range(\ MsgBox MyRg End Sub

第 3 页 共 52 页

Excel 2007 VBA 教程

图 6

11.4 自动显示数据提示：举个例子，工具菜单，选项，编辑器，钩起“自动显示数据提示（S）”，然后新建

一个标准模块，输入以下代码 Sub test()

Set MyRg = Range(\ MsgBox MyRg

Set MyRg = Nothing End Sub

主要是便于代码逐步或者设置断点调试，想知道变量的值时，这时把光标移到变量上，就会弹出一个提示如（图 7）所示

图 7

12 运行过程，中断，重新设置，在工具栏上用方框框起来的三个按钮分别表为“运行子过程/用户窗体”；“中断”；

“重新设置”。如（图 8）

图 8

12.1 运行过程分为全过程运行（快捷键F5）和逐步运行（快捷F8）

12.2 重新设置，当逐步运行时，想取消逐步运行，按一下“重新设置”这个按钮。 13 中断：相当于按了一下Esc，当一个过程运行进入了死循环，这时可以按一下Esc

第 4 页 共 52 页

Excel 2007 VBA 教程

第二讲 录制宏 指定宏 运行宏 修改宏 保存宏 加载宏

一、录制宏

1、 显示“开发工具”选项卡方法：Office按钮——>>Excel选项——>>常用——>>在功能区显示“开发工

具”选项卡

2、 录制在A1单元格输入“培训”这样一个宏

3、 录制方法 “开发工具”选项卡——>>代码组——>>录制宏——>>弹出一个“录制新宏”对话框——>>

输入宏名为“输入”——>> 确定——>> 选中A1单元格——>> 输入“培训”——>单击一下编辑栏的钩——>>停止录制

二、查看宏

1、 方法一 在上一节课我们已知知道了，相信大家没有忘记那个快捷键，如果忘记了，我就晕了。Alt+F11，

双击模块1——>>便可以看到刚才录制的代码

2、 方法二 “开发工具”选项卡——>>代码组——>>单击一下Visual Basic按钮——>>双击模块1——>>

看到以下代码 Sub 输入() '

' 输入 Macro ' '

Range(\

ActiveCell.FormulaR1C1 = \培训\End Sub 三、指定宏

1、 方法一 “开发工具”选项卡——>>控件组——>>插入——>>表单控件——>>单击一下“按钮”——>>

按住左键不放，在工作表里拖拉一下——>>画完后，弹出一个对话框，指定宏——>>选中“输入”——>>确定

2、 其它方法 也可以把宏指定给其它对象，如“自选图形”里的圆，艺术字

A、 打开比方，指定给艺术字的方法 把光标定位到你要指定的艺术字——>>右击——>>指定宏——>>弹出“指定宏”对话框——>>选中“输入”——>>确定

3、 修改“按钮”的名字 方法 把光标定位到按钮上——>>右击——>>编辑文字——>>修改为“输入” 四、运行宏

1、 方法一 我们先删除A1单元格里的内容——>>单击一下“输入”按钮

2、 方法二 “开发工具”选项卡——>>代码组——>>单击一下“宏”按钮——>>选中“输入”——>>单击一

下“执行”

3、 其它方法 我们在上一节课讲过，在VBE编辑器里，把光标定位到你“输入”的宏过程任何一行代码里，

运行F5，逐步运行F8

五、修改宏

1、 选中A1:D9单元格区域，录制一个给它添加边框的宏，录制的代码如下 Sub 添加边框() '

' 添加边框 Macro ' '

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

第 5 页 共 52 页

Excel 2007 VBA 教程

Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With

With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With

With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With

With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With

With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With

With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With End Sub

2、 修改后的代码 Sub 添加边框()

With Selection.Borders

.LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With

第 6 页 共 52 页

Excel 2007 VBA 教程

End Sub

从上面的代码可以看出，录制宏会录制出许多多余的代码出来。因此要把它们去掉，绿色是注释也把删除，它是对上边框，下边框，左边框，右边框 ，中间横线，中间的竖线一个一个处理，因此用With Selection.Borders对所有的边框 六、保存宏

1、 保存带有宏的工作簿（xlsm）如图10

A、 当我们保存带有宏的工作簿时，会弹出一个这样的提示对话框如图9往往没有接触过宏的朋友会吓一跳的，其实我们把下面这个图里的字读一次，就明白了，原来要保存为“启用宏工作簿”这个是2003版有区别的，微软公司的目的就是让用户在未打开之前提醒用户这个是带有宏的工作簿

图 9

图 10

七、保存为加载宏（xlam）如图11

1、 选择“Excel加载宏（*xlam）就会自动打开这个文件夹C:\\Documents and

Settings\\Administrator\\Application Data\\Microsoft\\AddIns，然后输入你的加载宏名

图 11

2、 加载宏的特点

A、 隐藏的

B、 一打Excel程序就会打开 3、 加载“加载宏”

A、 Office按钮——>>Excel选项——>>加载项——>>转到——>>加载项对话框——>>钩起“加载项名”前面的钩——>>确定 4、 删除加载宏

A、 2007删除加载宏比较麻烦，2010版的就改良这个，2010版直接在开始工具选项卡，有一个“加载项按钮”，那么2007怎么删除它呢？Office按钮——>>Excel选项——>>加载项——>>转到——>>加载项对话框——>>去掉“加载项名”前面的钩——>>确定

八、保存为个人宏工作簿：在我们录制宏时，有时我们可以选中“保存为个人宏工作簿”，便可以在所有的工作簿

中用，不过有个缺点，会打开个人宏工作簿，所以建议大家用“加载宏”，保存“个人宏工作簿”是对VBA新手打造的。

第 7 页 共 52 页

Excel 2007 VBA 教程

第三讲 VBA 语法基础

在学习VBA之前，我们要了解VBA里的对象，属性，方法，事件，就像我们在生活中一样，和一个人谈恋爱，是不是要相互了解对方，才能步入结婚的礼堂。下面来一一介绍它们 一、对象

1) 什么是对象呢？生活中的手机，电视机，桌子等等这些就是对象，而在我们的Excel里VBA 的对象是指

什么呢？这个可能抽象一点。工作簿，工作表，艺术字，图片这些就是Excel里的对象 2) 实例 A. Workbooks 代表工作簿集合，所有的工作簿，Workbooks(i)，表示已打开的第i个工作簿 B. Workbooks (\汇总表\代表“汇总”工作簿 C. ActiveWorkbook 当前正在操作的工作簿 D. ThisWorkBook 代码所在的工作簿 E. Sheets(\汇总\代表“汇总”工作表 F. Sheet1表示第一个插入的工作表，Sheet2表示第二个插入的工作表....... G. Sheets(i) 表示按排列顺序，第i个工作表 H. ActiveSheet 表示当前活动工作表 I. Worksheet 也表示工作表，但不包括图表工作表、宏工作表等。 J. Cells 所有单元格 K. Range (\单元格地址\L. Cells(行数,列数) M. Activecell 当前选中的单元格 N. Selection 被选中的单元格或者单元格区域 二、属性

1) 属性是指对象的特点，对象固有的，如图片就有图片高度，图片的宽度，单元格就有单元格的底纹，单

元格字体的颜色，这些就是它们的的属性，打个比方，生活的一些东西，如“苹果”，苹果的形状，苹果的颜色，苹果的重量，这些就是苹果的属性。 2) 实例 A. 显示单元格A1相对引用的地址，而这个Address就是单元格Range的属性 Sub test()

MsgBox Range(\End Sub B. 代码解释 属性中间一定要用点号分开，可能有的学生会问，怎么才有能知道它有那些属性呢？打个

比方，我现在想知道工作表有那些属性，我们可以先输入Sheet1再加一下点号，就会自动弹出其相应的属性列表出来，就像我们的Excel2007版的函数一样，你输入一个字母，就会弹出以这个字母开头所有函数出来，这样大家也就不用去记这些属性具体这个英文单词怎么写，只要大概了解知道有个这样的属性就可以了

三、方法

1) 方法是作用对象的一些动作，工作表删除，工作表移动，单元格复制，这些删除，移动，复制就是相应

对象的方法。打个比方，苹果被削了，削就是苹果的方法。 2) 实例 A. 在第一个工作表前面插入一个工作表 Sub test()

Sheets.Add before:=Sheets(1) End Sub B. 代码解释 Sheets是指工作表类，也就是工作表对象，中间用一个点分开，Add就是方法了，然后再

输入一个空格，before是对Add方法的一个补充说明，格式一定要这样，输入了Before之后，再

第 8 页 共 52 页

Excel 2007 VBA 教程

输入一个冒号，接着输入一个等号，整个代码的意思，在第一个工作表前插入一个新的工作表。

四、事件

1) 事件在Excel VBA里是指一定条件下，触发过程，如双击左键，右击，改变单元格内容，选择不同的单

元格就会触发一个过程就叫做事件，事件一般都是写在相应的工作表模块里 2) 实例 A. 双击就会弹出一个问候对话框

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) MsgBox \你好\End Sub B. 代码解释：双击左键就会弹出一个问候对话框，Private Sub Worksheet_BeforeDoubleClick(ByVal

Target As Range, Cancel As Boolean这个是自动生成的，你到工作表模块里，如双击Sheet1模块，通用列框里选择Worksheet，声明列表里选择BeforeDoubleClik，Msgbox是显示对话框函数。

五、变量

1) 什么是变量 变量是指在代码运行过程中其值能够发生变化的量，举个例子，Y＝2*X，这是一个方程 ，

当X＝1时，Y＝2，当X＝2时，Y＝4，当X＝3时，Y＝6， 这个X就是一个变量 2) 变量中不能使用的一些符号 # * % ！，用的最多的是 _

3) 变量不区分大小写，也可以使用中文，但实际应用中很少用，我们用英文表示变量名一般也会采取一些

特点，如文本型的变量用Str ，这样让人一看就这个变量表示文本型数据类型String 4) 为什么要声明变量 A. 如果数据量不大，在Excel里VBA不声明变量也没有影响的，但是，如果数据量很大时，不声明，

程序的运行速度就会慢许多。 B. 打个比方，你打了一个电话给公交车公司你要租一辆车，但是你没有说你要租一辆多少人坐的车，

那么出租车公司就会给你一坐100个人的车，而实际人你才要一个30人坐的车。大车的费用肯定比小车的费用贵，这样你不划算啊，变量也是这样，你不声明，它就给存储空间最大的个数据类型给你那不声明的变量。 C. 再打个比方：小明的妈叫小明去菜市场去一斤打酱油，小明从家里拿了一个麻袋去，小明妈妈拉住

小明了，说麻袋不能装酱油，会漏掉，于是小明提了了一个大水缺，在路上，小明摔了一跤，酱油也没有了，水缺也破了，小明真是陪了夫人又折兵，后来他妈妈告诉小明，下次你去打酱油就拿一个装一斤矿泉水瓶子去就可以了。呵呵，相信听了这个故事肯定笑了。也明白了为什么要声明定义变量。

5) 变量声明方式 A. 格式一 Dim 变量名 as 数据类型

Dim i as Integer B. 格式二 dim 变量名 as 数据类型，变量名 as 数据类型 记得用用逗号分开

Dim I as Integer，Rg as Range，Str as String

6) 强制声明 A. 变明使用前声明是一个好习惯，但我们会忘记，怎么办呢 B. 方法一 Alt+F11——>>工具菜单——>>选项——>>编辑器选项卡中——>>钩起“要求变量声明”复

选框 如图 12

图 12

第 9 页 共 52 页

Excel 2007 VBA 教程

C. 方法二 在代码模块顶端输入 Option Explicit 7) 声明变量的一种简写形式 A. 我们经常会看这样的一种定义变量的格式 Dim I% ，大家会问，这个是什么意思呢？，把变量I数

据类型定义为整型。常见的有整型Integer用% 长整型Long用& 字符串String用$ 单精度浮点型Single用! 双精度浮点型Double用#

8) 变量的作用域

变量的生命周期，也就是变量什么时候销毁，释放内存，因为变量只是暂时的存在内存中的，根据这样把变量分为 过程级变量，模块级变量，全局性变量，静态变量 A. 过程变量：定义变量是写在过程中的，其作用是在过程运行中，当过程结束变量也就销毁了。

1. 实例 Sub test()

Dim i As String i = \佛山小老鼠\ MsgBox i End Sub B. 模块级变量：定义变量写在模块的顶端，在此模块中可以调用此变量，其值还是存在，别的模块就

不行了 1. 实例

Dim Str As String Sub test1()

Str = \小老鼠\ MsgBox Str

Str = \佛山\End Sub Sub test2() MsgBox Str Str = \End Sub

代码解释 先运行Test1，然后运行Test2，大家可以看到对话框显示“佛山小老鼠”，也就是说运行过程Test2时，把过程Test1里的小老鼠也继承下来了。 C. 全局性变量 也是定义变量写在模块的顶端，不过就不是用Dim来定义了，要用Public，这个变量

就在所有的模块都可以调用，也就是这个变量值一直存在，直到把Excel程序关闭，变量值才会销毁。

1. 实例

先插入两个模块，在模块1中输入以下代码 Public Str As String Sub test1()

Str = \佛山小老鼠\ MsgBox Str End Sub

在模块2中输入以下代码 Sub test2() MsgBox Str End Sub

然后先运行模块1中的代码，然后再运行模块2中的代码，大家就会发现对话框中显示了“佛山小老鼠”，相信大家都明白了这个道量，另外全局性变量不能重复定义，重复定义就会报错。

第 10 页 共 52 页

Excel 2007 VBA 教程

9) 静态变量

静态变量 定义静态变量是在写在过程中，用Static定义，结束后，变量值仍旧保留，但是大家要用和模级变量区分，模块级变量是值作用于此模块所有过程，而静态变量只值只作用于本过程，不作有于其它过程。 A. 实例 Sub Test1()

Static i As Integer MsgBox i i = i + 1 End Sub Sub Test2() MsgBox i End Sub 代码解释：先运行Test1过程，显示i为0，再运行Test1过程为1，再运行Test1过程为2，再运行Test1过程为3，我们发现每运行一次结果会加1，因为我们有一个累加变量 i=i+1,其值还是保留，但当我们再行Test2时，可是那个i还是0，说明静态变量只作用于Test1，而不作用于Test2

六、常量

1) 常用申明用关键字Const，其值在过程运行中不会发现变化 2) 常量作用域 A. 和变量一样 B. 实例 计算圆面积

Sub test1() '过程开始

Dim S As Single '定义变量S为单精变浮点型，目的让其有保留二位小数，如果你定义Integer就没有小数点位数了

Const Pai = 3.14 '定义常量Pai r = 2 '给r赋值为2

S = Pai * r ^ 2 '计算圆的面积公式

MsgBox \算出的圆的面积为：\显示圆面积结果 End Sub C. 代码截图如图 13

图 13

第 11 页 共 52 页

Excel 2007 VBA 教程

第四讲 单元格对象

单元格和单元格区域是用户操作最多的一个对象，它可以表示一行，一列，一个区域，整个工作表，关键大家要理解它的引用方式 一、单元格的引用方式

1) Range表示 A. 如表示A1单元格 Range (\B. 实例

Sub Range表示()

Range(\佛山小老鼠\End Sub 2) Cells表示 A. 如表示D3单元格 Cells(3,4) B. 实例

Sub Cells表示()

Cells(3, 4) = \佛山小老鼠\End Sub

3) Activecell表示 A. 实例

Sub ActiveCell表示()

ActiveCell = \佛山小老鼠\End Sub

二、单元格区域的引用方式

A. 实例“表示单元格区域A1到D9的区域，有下面几种表法方法 Sub test1()

Range(\End Sub Sub test2()

Range(\End Sub Sub test3()

Range(Cells(1, 1), Cells(9, 4)) = 5 End Sub Sub test4()

Range([A1], [D9]) = 6 End Sub B. 其它方式引用单元格区域

1. Selection 2. UsedRange 3. CurrentRegion 4. Union 5. Intersect 6. Resize

它们的代码用法如下

第 12 页 共 52 页

Excel 2007 VBA 教程

Sub test1()

Range(\End Sub Sub test2()

Range(\End Sub Sub test3()

Range(Cells(1, 1), Cells(9, 4)) = 5 End Sub Sub test4()

Range([A1], [D9]) = 6 End Sub Sub test5()

Selection = 7 End Sub Sub test6()

ActiveSheet.UsedRange = 8 '注意一定要在UsedRange前加父对象 End Sub Sub test7()

Range(\'表示选中和A1单元格相连接的所有单元格，这个连接是指有数据的连接 End Sub Sub test8()

Application.Union([A1], [C1], [E1], [G1]).Select '表示同时选中A1，C1，E1，G1单元格 End Sub Sub test9()

' Intersect方法经常用到了，有时可能用户选择了整列，而这一列有数据的单元格不多，代码里用

'了循环语句，如果不用Intersect这个来选择区域，运行程序时可能就出现Excel程序卡死 Application.Intersect(ActiveSheet.UsedRange, Selection).Select End Sub

Sub test10()

'Resize属性常用在数组转置，有时不确定区域有多大，就会用Resize来填充数据 '格式：单元格对象.Resize(行数，列数）

Range(\表示从D1单元格开始，向下总共有9行，当然包括D1在内， '向右总共二列，当然也包括D列在内，最后得列一个D1到E9的一个区域 End Sub

三、行和列的引用方式

1) 用Range来表示行和列 Sub test1()

Range(\表示第一行被选中 End Sub Sub test2()

Range(\表示第5行到第9行被选中 End Sub Sub test3()

第 13 页 共 52 页

Excel 2007 VBA 教程

Range(\表示A列到D列被选中 End Sub Sub test7()

Range(\表示D9单元格所在的行，也就是第9行 End Sub

Sub test8()

Range(\表示D9单元格所在的列，也就是第4列 End Sub

2) 用Rows和Columns表示行和列 Sub test4()

Rows(\可以不加双引号，也可以加双引号 End Sub Sub test5()

Rows(\表示1行到4行被选中，记得要加双引号 End Sub Sub test6()

Dim i As Integer i = 9

Rows(\表示1行到9行被选中 End Sub Sub test9()

Rows.Select '表示工作表中所有的行被选中，也就是全选工作表 End Sub

Sub test10()

Columns.Select '表示工作表中所有的列被选中，也就是全选工作表 End Sub

四、用Offset属性来引用单元格

1) 单元格对象.Offset(偏移行数，偏移列数)，备注：往左往上就是负数，往右往下就是正数 2) 实例 Sub test()

Range(\单元格B1被选中 End Sub 五、定位单元格

1) 定位到A列最后一个有数据的单元格 Sub MyRow()

i = Cells(Rows.Count, 1).End(xlUp).Row MsgBox i End Sub

2) 定位到第一行最后一个有数据的单元格 Sub MyCol()

i = Cells(1, Columns.Count).End(xlToLeft).Column MsgBox i End Sub

第 14 页 共 52 页

Excel 2007 VBA 教程

第五讲 VBA语句

前面一讲我们学习了单元格的引用，这一讲我们来学习VBA里的语句，VBA里的语句有好多，下面把一些常用的讲一下

一、输入语句

1) 用Inputbox函数实现 A. Inputbox函数有七个参数，主要我们理解前面三个参数，第一个参数是“提示语”第二参数是标题，

第三参数是输入框中的默认值 B. 实例

Sub test1() '这个程序有时会报错 Dim i As Single, S As Single

r = InputBox(\请输入圆的半径\圆半径\空\ S = 3.14 * r ^ 2

MsgBox \圆的面积是:\End Sub

Sub test2() '这个程序是完善的，不会报错 Dim i As Single, S As Single

r = InputBox(\请输入圆的半径\圆半径\ If r = \ S = 3.14 * r ^ 2

MsgBox \圆的面积是:\End Sub

2) 用InputBox方法实现 A. InputBox方法有8个参数，主要我们理解前三个参数和最后一个参数，第一个参数是“提示语，第

二参数是标题，第三参数是输入框中的默认值，第八参数是指返回的数据类型 B. 实例

'用InputBox方法实现

Sub test3() '这个程序是完善的，不会报错

Dim i As Single, S As Single '定义变量i,S为单精度型数据类型 r = InputBox(\请输入圆的半径\圆半径\弹出输入对话框 If TypeName(r) = \

'如果对话框点了取消或者点了关闭以及没有填数据就退出程序， S = 3.14 * r ^ 2 '面积公式 MsgBox \圆的面积是:\End Sub

Sub test4() '这个程序有时会报错

Set rg = Application.InputBox(\请选择你要查找的区域\查找区域\ rg.Value = \老鼠\End Sub

Sub test5() '这个程序是完善的，不会报错，如果第八参数为8，那么用 On Error GoTo 100来防错了 Dim rg As Range '定义rg为单元格对象变量

On Error GoTo 100 '如果遇到错误，就跳到100那一行

Set rg = Application.InputBox(\请选择你要查找的区域\查找区域\ ' 把选中的区域赋值给rg

rg.Value = \老鼠\给rg赋值为小老鼠 100:

第 15 页 共 52 页

Excel 2007 VBA 教程

End Sub

二、输出语句

1) 用函数Msgbox实现 A. 函数Msgbox有五个参数，主要理解第一个参数是“提示”，第二参数是“对话框的图标和按钮的数

目和形式”，且用数字代替时，那些数字是可以相加了，第三参数是对话框的标题 B. 第二参数对照表

常数 vbOKOnly VbOKCancel

值 0 1

描述

只显示 OK 按钮。

显示 OK 及 Cancel 按钮。

显示 Abort、Retry 及 Ignore 按钮。 显示 Yes、No 及 Cancel 按钮。 显示 Yes 及 No 按钮。 显示 Retry 及 Cancel 按钮。 显示 Critical Message 图标。 显示 Warning Query 图标。 显示 Warning Message 图标。 显示 Information Message 图标。

VbAbortRetryIgnore 2 VbYesNoCancel VbYesNo VbRetryCancel VbCritical VbQuestion VbExclamation VbInformation

C.

3 4 5 16 32 48 64

实例

Sub test1()

MsgBox \你好\问候\End Sub Sub test2()

MsgBox \你好\问候\为什么加4呢、看一下第二参数的对照表就明白了，因为要两个按钮 End Sub Sub test3()

ant = MsgBox(\你去过学校吗？\问候\ If ant = vbYes Then MsgBox \我去过了\ Else

MsgBox \天下雨，我没有去\ End If End Sub

三、With 语句

第 16 页 共 52 页

Excel 2007 VBA 教程

With语句的作用是精简代码，提高运行速度，如果我们多次读取对象影响运行的速度的。 1) 实例

Sub test()

With Range(\对A1单元格进行处理 .Font.ColorIndex = 3 '字体颜色为红色 .Interior.ColorIndex = 5 '底纹为蓝色 .Font.Size = 48 '字号为48

.Font.Name = \华文行楷\字体为华文行楷\ End With '结束处理 End Sub Sub test1()

Range(\清除A1格式 End Sub

四、条件判断语句

条件判断语句在编写代码用的比较多，主要有 1) if?then

Sub test1()

If Range(\单元格没有填好数据\温馨提示\End Sub

2) if?then?end if

Sub test2()

If Range(\

MsgBox \单元格没有填好数据\温馨提示\ End If End Sub

3) if?then?else?end if

Sub test3()

If Range(\

MsgBox \单元格没有填好数据\温馨提示\ Else

MsgBox \单元格的数据是：\ End If End Sub

4) if?then?elseif?then?else?end if

Sub test4() '只能判断一个

If Range(\如果C3单元格小于60，那么 Range(\不及格\单元格显示不及格

ElseIf Range(\又如果C3单元格小于70，那么 Range(\及格\单元格显示及格

ElseIf Range(\又如果C3单元格小于80，那么 Range(\良好\单元格显示良好 Else '否则

Range(\优秀\单元格显示优秀 End If End Sub

5) select case?case?end select

第 17 页 共 52 页

Excel 2007 VBA 教程

Sub test6() '这个程序当C3单元格大于100时，那么D3单元格没有显示了 Select Case Range(\ Case Is < 60

Range(\不及格\ Case Is < 70

Range(\及格\ Case Is < 80

Range(\良好\ Case Is <= 100

Range(\优秀\ End Select End Sub

6) select calse?case?case else?end select

Sub test7() '这个程序就可以了，大于等于80以上的全部是优秀 Select Case Range(\ Case Is < 60

Range(\不及格\ Case Is < 70

Range(\及格\ Case Is < 80

Range(\良好\ Case Else

Range(\优秀\ End Select End Sub

Sub test8() '如果条件是区间时，如从几到几，就不能加Is了 Dim i As Integer i = Weekday(Date) Select Case i Case 1 To 6

MsgBox \今天是工作日\ Case Else

MsgBox \今天休息\ End Select End Sub 五、循环语句

1) For?Next A. 实例 在A1到A9单元格里依次输入1到9 Sub test1()

Dim i As Byte '定义变量i为字节型 For i = 1 To 9 'i从1到9循环

Cells(i, 1) = i '从单元A1开始写于i Next i '下一个 End Sub

Sub test2()’这个是在A1到A9依次输入9到到1

Dim i As Integer, k As Integer '定义变量i为字节型,注意如果从大到小，就不能定义i变量

第 18 页 共 52 页

Excel 2007 VBA 教程

为字节型 k = 1

For i = 9 To 1 Step -1 'i从9到1循环 Cells(k, 1) = i '从单元A1开始写于i k = k + 1 Next i '下一个 End Sub

2) For Each?next A. 实例在A1到A9，E3到E9，C3，C7单元格填充红色的底纹 Sub test3()

Dim Rg As Range

For Each Rg In Range(\ Rg.Interior.ColorIndex = 3 Next Rg End Sub

3) Do While?Loop A. 实例 在A1到A9单元格里依次输入1到9 Sub test4()

Dim i As Integer i = 1

Do While i < 9

Cells(i, 1) = i i = i + 1 Loop End Sub

Sub test5()’变形的 Dim i As Integer i = 0 Do

Cells(i + 1, 1) = i + 1 i = i + 1 Loop While i < 9 End Sub

4) Do Untile?Loop A. 实例 在A1到A9单元格里依次输入1到9 Sub test6()

Dim i As Integer i = 1

Do Until i > 9

Cells(i, 1) = i i = i + 1 Loop End Sub

备注（代码分换行的方法：如果一行没有写完，要换行。先按一下空格，然后加一个_

第 19 页 共 52 页

Excel 2007 VBA 教程

第六讲 代码调试和错误处理

当一个程序完成后，在编程员手上，能正常运行，但其实工作还有没有结束，特别是复杂的程序，因为这里也许里面还存在一些编程员没有发现的错误，所以当一个软件开发完之后，首先发布的版本是抢鲜版，如现在办公软件Office2013就是，有的是试用版，测试版，都是为了完善程序。一个学程序的人，都会经过这一步的，首先是代码正确，经过一段时间学习之后要保证不同环境和条件下的也能正常运行，也就是所谓的容错，还有就是兼容性，如Excel有2003版，2007版，2010版，程序能否在这个版本里都能正常运行。说了一大堆，其实我个人认为的有些程序调试的时间比写代码的时间还要长，可见这个过程的重要性。因此很有必要学习这一讲。 一、错误类型

1) 语法错误 A. 如函数Msgbox，如果把它的结果赋值给变量Ant，如果不给它的参数加一个括号就会出现红色的字 Sub test1() '这个是正确的表示

MsgBox \不能作为除数\提示\End Sub

Sub test2() '这个是错误的表示

ant = MsgBox\不能作为除数\提示\要给MsgBox里的参数括号括起来，才不会显示红色的字体 End Sub 2) 编译错误 A. 如定义变量，把关键字if定义为变量 Sub test3()

Dim if AS Integer End Sub 3) 运行错误 A. 因为数据类型Integer值的范围是-32768到32767之间的整数，而数据类型Long值的范围是

-2147483648到2147483647，而我们的程序求和已经超出了32767，所以把变量S定义变量类型为Integer不行，要改为Long，所以当程序运行时，报错为“运行时错误，溢出” Sub test4() '这是错误的

Dim i As Integer, s As Integer For i = 1 To 10000 s = s + i Next i

MsgBox \最后结果是：\End Sub

Sub test5() '这是正确的

Dim i As Integer, s As Long For i = 1 To 10000 s = s + i Next i

MsgBox \最后结果是：\End Sub 4) 逻辑错误 A. 实例 在第一个工作表前插入一张新的工作表，且取名为“总表”，第一次运行正常，但再运行一次

时就报错了

Sub test6() '第一次运行正常，再运行一次就报错了 Sheets.Add before:=Worksheets(1)

第 20 页 共 52 页

Excel 2007 VBA 教程

Worksheets(1).Name = \总表\End Sub

Sub test7() '这个不会报错

On Error Resume Next '为了防止在删除之前没有一个叫“总表”的工作表，因为没有我们怎么删除呢？

Application.DisplayAlerts = False '屏蔽询问对话框 Sheets(\总表\删除总表这个工作表 Application.DisplayAlerts = True '开启询问对话框

Sheets.Add before:=Worksheets(1) '在第一个工作表前面插入一个新的工作表 Worksheets(1).Name = \总表\这个新的工作表取名为“总表” End Sub

二、处理错误

1) 预防

比如，0不能作为除数，同样文本也不能作为除数，当单元格为空时，那么其值也算是0，你可能会说，那有那么傻，不可能用文本作为除数呢？有时会发生，所以我们在编程时就要自己先想到用户在操作时会发生什么情况，其实有时自己也没有发现，只有多次测试才能发现。 Sub test9() ' 预防错误，防止了B2单元格没有数据和值是0时

If Range(\单元格是空的或者它的值是0\Sub

s = Range(\ MsgBox \结果是: \End Sub

Sub test10() ' 预防错误，防止了B2单元格没有数据和值是0时，说不定一个用户会输入一个汉字放在B2单元格

'那么怎么办呢，还要加上语句才行 Dim s As Single

If Range(\\单元格是空的或者它的值是0或者是文本\ s = Range(\ MsgBox \结果是: \End Sub

也许你会说，曹老师，不要那么麻烦吧，用On Error Resume Next就解决了，不错，这就是我们下面要讲的忽略错误 2) 忽略错误

Sub test11() ' 忽略错误,但是有一个缺点啊，它最后弹出一个对话框，但是没有值啊？怎么办呢，如果没有下面那个Msgbox函数就OK了

On Error Resume Next '遇到错误断续往下运行 s = Range(\ MsgBox \结果是: \End Sub

3) 引导错误

Sub test12() ' 忽略错误，引导错误的

On Error GoTo 100: '遇到错误跳到100: 处，这样就绕过了Msgbox函数 s = Range(\ MsgBox \结果是: \100:

第 21 页 共 52 页

Excel 2007 VBA 教程

End Sub 三、代码调试

代码调试是一项繁琐的工作，我在编写“Excel完美工具箱”插件时，代码调试有时从晚上11点到早上三四点，长达4-5个小时的调试，当然在调试的时要修改一些代码，代码调试同时也提高自己编程水平，这个我深有体会，如设置断点，在本地窗口查看各变量的情况，然后找到原因在那，在第一讲时，我们简单提过一些，VBA编辑环境中，工作模式可分为三种，设计模式，也就是我们正常编辑代码时，运行模式，就是F5和F8运行时的状态，最后一种是中断模式，当程序遇到错误是程序会自动停止进入中断模式。设置断点，当程序运行到断点处，也会进入中断模式。下面我们讲几种调试的方法 1) 逐语句运行 A. 把光标定位到某一过程的任何一句里，然后按一下F8就会从第一句开始，有黄色颜色标示着第一行，

再按一下F8，黄色颜色从第一行跑到了第二行。也就是每按一次F8就执行一行代码。此时我们就可以查看各变量的情况便于我们查找错误。但逐语句有时太慢了，特别是多层循环语句，我们可以到调试菜单，运行到光标处。当然也可以用“设置断点”下面我们讲解它 B. 设置断点

1. 设置断点的方法有

a) 直接在你要设置断点那一句代码左边边框处，单击一下左键，如图图 14

图 14

b) 11把光标定位到你要设置断点的那一行里，然后按一下F9就可以了 2. 清除断点的方法

a) 直接用左键单击代码边框处那红色圆点，就清除了那一行的断点 b) 如果设置断点多的化，调试菜单下的清除所有断点命令。 3. 设置断点后怎样运行代码

a) 把光标定位到过程中的某一行代码处按快捷F5，再按一下F5就运行到下一个断点。 4. 查看变量情况

a) 把光标移到变量就会显示变量的值，如15

图 15

b) 11在本地窗口查看各变量的情况，这种方法我经常用到，如图

图 16

第 22 页 共 52 页

Excel 2007 VBA 教程

第七讲 工作表对象

一. 工作表的引用

1. 以名称方式引用：Worksheets (\工作表名称\，Worksheets是工作表的集合，包括所有工作表。

（1） 实例

Sub test1() '这是用名称来引用工作表名

Worksheets(\总表\佛山小老鼠\End Sub

2. 以索引号方式引用：Worksheets(数字)或者Sheets(数字)，这两者的区别在前面的课程里提过，Sheets

也是工作表的集合，把其它的工作表也包括在内，如图表工作表，宏表工作表。而Worksheets只包括我平常操作的工作表。如Sheet1,Sheet2 （1） 实例

Sub test2() '这是用索引号来引用工作表名

On Error Resume Next '目的是为了防止此工作簿中没有“佛山小老鼠”这个工作表报错 Application.DisplayAlerts = False '屏蔽询问对话框

Worksheets(\佛山小老鼠\删除“佛山小老鼠”这个工作表 Application.DisplayAlerts = True '打开“询问对话框”

Sheets.Add After:=Worksheets(Sheets.Count) '在最后一张工作表后插入一张新工作表 Worksheets(Sheets.Count).Name = \佛山小老鼠\把最后一张工作表改为“佛山小老鼠” End Sub

3. 其它方式的引用

（1） 用ActiveSheet引用

Sub test3() '用ActiveSheet引用

MsgBox \活动工作表的名字是：\End Sub

二. 添加工作表

1. 添加工作表的方法是Add,其表达式Add(Before/After,Count,Type)。Before前面，After后面，Count

插入的数量，Type是插入的类型，如正常工作表Xlworksheet,图表工作表Xlchart，还有宏表工作表这里就不多说了。如果省略第二参，第三参数就会插入一张正常工作表 （1） 实例 在第一张工作表前面插入5张工作表

Sub test4() '在第一张工作表前面插入5张工作表 Sheets.Add Before:=Worksheets(1), Count:=5 End Sub

三. 删除工作表

1. 按名称来删除 Worksheets(工作表名称).Delete 记得给工作表名加双引号

2. 按索引号来删除 Worksheets(数字).Delete 这个数字就是我们在工作簿看到工作表位置。 3. 用代码删除工作表，会弹出一个“询问对话框”如图 17

图 17

4. 解决询问对话框的问题

其实前面许多实例里我都用了，只是没有和大家说详细，这次和大家讲清楚，用了这下面的代码，

第 23 页 共 52 页

Excel 2007 VBA 教程

Application.DisplayAlerts = False '屏蔽询问对话框 Worksheets(1).Delete '删除第一个工作表

Application.DisplayAlerts = True '打开“询问对话框” 5. 至少要保留一张可见工作表 6. 实例 删除第一个工作表

Sub test1() '会弹出询问对话框的 Worksheets(1).Delete End Sub

Sub test2() '不会弹出询问对话框的 Application.DisplayAlerts = False Worksheets(1).Delete

Application.DisplayAlerts = True End Sub

四. 实例 提取各工作表名制作目录

Sub 提取工作表名()

Dim i As Integer '定义i为整型变量

On Error Resume Next '防止工作簿里没有目录工作表，在删除时报错 Application.DisplayAlerts = False '关闭询问对话框 Worksheets(\目录\删除目录工作表

Application.DisplayAlerts = True '打开询问对话框

Sheets.Add before:=Sheets(1) '在第一个工作表前插入一张新的工作表 Sheets(1).Name = \目录\把第一个工作表取名为“目录”

Cells(1, 1) = \目录\在目录工作表中的A1单元写于“目录”两字

For i = 2 To Worksheets.Count 'Worksheets.Count统计工作的总数，整句代码的意思遍历每一个作表

Cells(i, 1) = Sheets(i).Name '依次把工作表的名字写A列 Next i '下一个i End Sub Sub 清空()

Worksheets(\目录\ Worksheets(\目录\ End Sub

五. 实例 提取各工作表名制作目录且还要建立超链接

Sub 提取工作表名()

Dim i As Integer '定义i为整型变量

On Error Resume Next '防止工作簿里没有目录工作表，在删除时报错 Application.DisplayAlerts = False '关闭询问对话框 Worksheets(\目录\删除目录工作表

Application.DisplayAlerts = True '打开询问对话框

Sheets.Add before:=Sheets(1) '在第一个工作表前插入一张新的工作表 Sheets(1).Name = \目录\把第一个工作表取名为\目录\

Cells(1, 1) = \目录\在目录工作表中的A1单元写于\目录\两字

For i = 2 To Worksheets.Count 'Worksheets.Count统计工作的总数，整句代码的意思遍历每一个作表

Cells(i, 1) = Sheets(i).Name '依次把工作表的名字写A列

ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:=\

第 24 页 共 52 页

Excel 2007 VBA 教程

SubAddress:=Worksheets(i).Name & \ '由代码过长，选打一个_然后加一个空格，这是把代码分行

' 根据录制的代码修改，把选中的Selection改为Cells(i,1),把 \改为Worksheets(i).Name & \，一定要是记得是工作表名

'所以加了一个Name属性，把“佛山小老鼠”改为Cells(i, 1).Value Next i '下一个i End Sub Sub 清空()

Worksheets(\目录\ Worksheets(\目录\ End Sub

'这是我们在A1单元格输入“佛山小老鼠”之后录制的一个给它添加超链接的宏得到的宏代码 'Sub Macro1() ''

'' Macro1 Macro '' ' ''

' ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=\' \佛山小老鼠\'End Sub

六. 实例 提取工作表名制目录且要建立超链接还要添加一个“返回目录”的按钮

Sub 提取工作表名()

Dim i As Integer '定义i为整型变量

On Error Resume Next '防止工作簿里没有目录工作表，在删除时报错 Application.DisplayAlerts = False '关闭询问对话框 Worksheets(\目录\删除目录工作表

Application.DisplayAlerts = True '打开询问对话框

Sheets.Add before:=Sheets(1) '在第一个工作表前插入一张新的工作表 Sheets(1).Name = \目录\把第一个工作表取名为\目录\

Cells(1, 1) = \目录\在目录工作表中的A1单元写于\目录\两字

For i = 2 To Worksheets.Count 'Worksheets.Count统计工作的总数，整句代码的意思遍历每一个作表

Worksheets(1).Activate '一定要加上这一句，由于我们为了添加按钮时，把添加按钮的那个工作表设置为活动工作表，添加之后还要把“目录”工作表设置为当前工作表 Cells(i, 1) = Sheets(i).Name '依次把工作表的名字写A列

ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:=\

SubAddress:=Worksheets(i).Name & \ '由代码过长，先打一个_然后加一个空格，这是把代码分行

' 根据录制的代码修改，把选中的Selection改为Cells(i,1),把 \改为Worksheets(i).Name & \，一定要是记得是工作表名

'所以加了一个Name属性，把“佛山小老鼠”改为Cells(i, 1).Value ' 添加“返回目录”按钮

'因为2007录制不了艺术字代码，2003和2010可以，下面的代码是我在2003版里录制修改后的 Worksheets(i).Shapes.AddTextEffect(msoTextEffect32, \返回目录\黑体\ msoTrue, msoFalse, 600#, 20.25).Select

第 25 页 共 52 页

Excel 2007 VBA 教程

Sheets(i).Select Sheets(i).Activate

ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:= _ \ Range(\ Next i '下一个i End Sub Sub 清空()

Dim Sp As Shape, sh As Worksheet

Worksheets(\目录\ For Each sh In Worksheets sh.Activate

For Each Sp In ActiveSheet.Shapes Sp.Delete Next Sp Next sh End Sub

第八讲 工作簿对象

一. 工作簿的引用方式

1. 以名称引用

(1). 实例

Sub test2() ' 把“汇总”工作簿打开

MsgBox Workbooks(\汇总.xlsm\End Sub

2. 以索号引用

(1). 实例

Sub test1()

MsgBox Workbooks(1).Name End Sub

3. Thiswokbook 表示代码所在的的工作簿

(1). 实例

Sub test3()

MsgBox ThisWorkbook.Name End Sub (2). 11

4. Activeworkbook 表示当前活动工作簿

(1). 实例

Sub test4()

MsgBox ActiveWorkbook.Name End Sub

二. 保存工作簿

1. 另存为的方法

(1). 实例 在桌面上新建一个工作簿，取名为“汇总”，且工作簿密码是1234

Sub test1()

第 26 页 共 52 页

Excel 2007 VBA 教程

Dim wb As Workbook '定义wb工作簿型对象变量 Set wb = Workbooks.Add '把新建的工作簿赋给wb With wb '处理工作簿wb

.SaveAs Filename:=\我的文档\\桌面\\汇总.xlsx\另存为桌面，取名为“汇总” .Password = \给工作簿 wb设置密码为1234 End With '结束处理 End Sub

三. 打开工作簿

1. 用Open方法，它的参数有许多，我现在只用了第一个参数：要打开的工作簿所在的路径，其它参数没有，

可以自己去看帮助。 (1). 实例 打开“F第八讲VBA对象”文件中的“汇总”工作簿

Sub test1()

Workbooks.Open Filename:=ThisWorkbook.Path & \汇总.xlsm\End Sub

四. 从不打开的工作簿里提取数据

1. 实例

Sub Test1()

Dim Wb As Workbook '定义Wb为工作簿对象型变量 Dim MyPth As String '定义MyPth为文本型变量

Application.ScreenUpdating = False '关闭屏幕刷新

MyPth = ThisWorkbook.Path & \数据源.xlsx\把数据源工作簿路径赋给MyPth Set Wb = GetObject(MyPth) '把返回路径上的文件引用且赋值给Wb

With Wb.Sheets(1).Range(\工作簿里工作表1和A1单元格相连的区域

Range(\把Wb工作簿里的工作表1数据写于活动工作表里以A1单元格为区域

'Rows.Count是2^20行,Columns.Count2^14列,它们的对象是 Wb.Sheets(1).Range(\，也就是起到复制整个工作表的作用 Wb.Close False '关才Wb工作簿，且不保存更改 End With '

Set Wb = Nothing '释放内存

Application.ScreenUpdating = True '打开屏幕刷新 End Sub

第九讲 事件

一. 事件分类

(1). 应用程序事件 经常在类模块中定义这种事件 (2). 工作簿事件 如工作簿的Open事件，一打开工作簿就就执行过程

Private Sub Workbook_Open() ?? End Sub

实例 一开工作作簿就问好 Private Sub Workbook_Open() MsgBox \佛山小老鼠你好！\End Sub (3). 工作表事件 如工作表里的Change事件，一改变工作表单元里的内容就触发，工作表事件用的用比

第 27 页 共 52 页

Excel 2007 VBA 教程

较多

a. Private Sub Worksheet_Change(ByVal Target As Range)，一改变单元格内容就触发 ?? End Sub

b. Private Sub Worksheet_SelectionChange(ByVal Target As Range)一选择别的单元格就触发

?? End Sub

实例一 防止看错行

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Myrow As Integer '定义变量Integer为整型数据类型 Myrow = Target.Row 'Target当前活动单元格，取得其行号

Cells.Interior.ColorIndex = xlNone '把整个工作表的底纹设置为无，目的是为了删除上一次的

Rows(Myrow).EntireRow.Interior.ColorIndex = 8 '给Myrow这一整行添加底纹 End Sub

实例二 自动补齐卡号，由于卡号前面的是一样，只是后面的不一样，便不能用单元格格式设置 Private Sub Worksheet_Change(ByVal Target As Range) '工作表事件，一改变内容就发生 If Target.Column = 1 Then '如果活动单元格的列号是等于1，那么

If Target.Count <> 1 Then Exit Sub '又如果选中的不只是一个单元格，那么就退出运行

MyTart = Target.Value '把活动单元格的值赋给MyTarg Application.EnableEvents = False '关闭联动事件

Target = \& \& MyTart '在原来数据前面加6210260500059330 MyTart = \把变量MyTart清空

Application.EnableEvents = True '打开联动事件 End If

Columns(\列自动适合列宽 End Sub

实例三 在B列任意单元格输入任何文本后，A列与其所对应的单元格 自动显示出当天的日期 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then '如果当前单元格的列号2

If Target.Offset(0, -1) = \又如果A列对应的单元不为空且B列活动单元也不为空，那么

Target.Offset(0, -1) = Date '在A列对应的单元格输入当前日期 End If End If

Columns(1).AutoFit 'A列自动适合列宽 End Sub

(4). 其它事件

a. OnTime事件

表达式 Application.OnTime 开始时间, 过程名 结束时间 布尔值

解释如果省略第三参数，开始时间就是过程运行时间，如果没有省略，那第三参数就是过程运行时间，第四参数如果是False就是停止OnTime事件 实例 在A1单元格显示当前的时间且带有秒数的格式 Sub Mystar()

Range(\在A1单元格写于时间，用了Format函数，格

第 28 页 共 52 页

Excel 2007 VBA 教程

式用时分秒

Application.OnTime Now + TimeValue(\在原有的时间加一秒执行 End Sub

Sub MyStop()

On Error Resume Next '为什么要加一句防错语句，因为先前当没有执行Mystar过程，就会报错

Application.OnTime Now + TimeValue(\记得是第四个参数用了False,所以它前面还有二个逗号 End Sub b. OnKey事件

1. 表达式 Application.OnKey 快捷键, 过程名

a. 一些常用控制键的代码 Ctrl ^ Shift + Alt % ，其它的键要加{}，而26个字母不

要加，如Ctrl+D这样表示\；再加Shift+F1这样表示\b. 实例 按快键Ctrl+F9，输入当天的星期 在普通模块里输入 Sub 输入星期()

Selection = Format(Now, \End Sub

Sub 打开快捷键()

Application.OnKey \输入星期\End Sub

Sub 关闭快捷键()

Application.OnKey \End Sub

在工作簿模块里输入

Private Sub Workbook_BeforeClose(Cancel As Boolean) Call 关闭快捷键 End Sub

Private Sub Workbook_Open() Call 打开快捷键 End Sub 2. 11

第十讲 窗体与控件

如果要编辑稍大一点的程序，大家就要用到窗体和控件了，其实也就是我们平常所说的对话框，达到人机对接，下面我们来一一讲解它们 一、窗体

1、 新建窗体

A、 Alt+F11——>>打开VBE编辑器——>>插入菜单——>>窗体

B、 把光标定位到“资源管理器”处——>>右击——>>插入——>>用户窗体 2、 删除窗体

A、 把光标定位到资源管理器上——>>右击——>>移除窗体 3、 窗体的Caption属性，是显示在窗体的标题

A、 实例 加载时修改窗体的标题为“身份证”,把光标定位到窗体上——>>右击——>>查看代码——>>对象列表选择窗体名称UserForm1——>>过程列表中选择Initialize如图 18，输入以下代码 Private Sub UserForm_Initialize()

第 29 页 共 52 页

Excel 2007 VBA 教程

UserForm1.Caption = \车模\End Sub

然后新建一个普通的模块里输入 Sub test()

UserForm1.Show 0 '运行窗体，当Show后面是0是，表示窗体打开之后还可以操作工作表

'如果省略的化或者为1的化，表示窗体打开之后就不能再操作工作表了，如果要操作就要关闭窗体

End Sub

图 18

4、 修改窗体的名称

A、 Alt+F11——>>视图菜单——>>属性窗口，也可以按快捷键F4——>>名称——>>输入“异同项”，效果如图 19

图 19

5、 显示窗体

A、 窗体名称 .Show 0

后面用0还是用1在上面我已讲过，这里不再啰嗦了 6、 关闭窗体

A、 Unload.窗体名称

如果在窗体模块里也可以用me来代替窗体名称 7、 在窗体中添加按钮（各按钮如所图 20示

第 30 页 共 52 页

Excel 2007 VBA 教程

图 20

A、 标签 Label B、 文本框 TextBox

C、 命令按钮 CommandButton D、 图像控件 Image E、 复选框 CheckBox

F、 切换按钮 ToggleButton

实例 仿制一个QQ登录界面，如图图 21

图 21

G、 组合框 ComboBox

1. 可以输入文字相当于文本框的作用，且还有列表框的作用，也就是组合框集文本框和列表框特

点一起

2. 给组合框添加数据

实例 添加一个月份组合框

Private Sub UserForm_Initialize() Dim i As Byte For i = 1 To 12

Me.ComboBox1.AddItem i & \月\ Next i

Me.ComboBox1.ListIndex = 0’第一个值为默认值 End Sub

3. 单个读取 Me.ComboBox1.Value H、 列表框 ListBox

1. 只能选择，不能输入 2. 给列表框添加数据

实例 添加一个月份列表框

Private Sub UserForm_Initialize() Dim i As Byte For i = 1 To 12

Me.ListBox1.AddItem i & \月\ Next i

Me.ListBox1.ListIndex = 0 End Sub

3. 单个读取列表框中的值

第 31 页 共 52 页

Excel 2007 VBA 教程

Private Sub CommandButton1_Click() MsgBox Me.ListBox1.Value End Sub I、 框架 Frame

J、 选项卡控件 TabStrip K、 多页控件 MultiPage L、 滚动条 ScrollBar

M、 数值调节按钮 SpinButton N、 单选按钮 OptionButton O、 RefEdit控件

主要用来选择区域，当然我们也可以inputbox选区域，不过没有那么方便，RefEdit控件在Excel里选区域显得更专业一点，不过Vb里没有这个控件，不知为什么？ 实例 批量上下标

Private Sub CommandButton1_Click() Dim MyRg As Range '定义变量

If Me.OptionButton1 = True Then '如果单选按钮1被选中，那么 For Each MyRg In Application.Intersect(ActiveSheet.UsedRange, Range(Me.RefEdit1.Value)) '遍历选中的所有单元格

'Intersect的作用是选中区域和有数据区域交集，这样就不用循环选中的每一个单元格，大大提高运行速度

'Me.RefEdit1.Value取出RefEdit1的值，作为Range的参数 MyRg.Characters(Start:=Len(MyRg) - Me.ComboBox1.Value + 1, Length:=Me.ComboBox1.Value).Font.Superscript = True

'上面这段代码大家可以通过录制得到，然后修改一下得到。代码的意思，把后面Me.ComboBox1.Value个字标示上标

'ComboBox1.Value是组合框的值，也就是说标示后面多少个，由它决定的。下面的代码也是这个意思，我就不多作解释了 Next MyRg

ElseIf Me.OptionButton2 = True Then For Each MyRg In Application.Intersect(ActiveSheet.UsedRange, Range(Me.RefEdit1.Value))

MyRg.Characters(Start:=Len(MyRg) - Me.ComboBox1.Value + 1, Length:=Me.ComboBox1.Value).Font.Subscript = True Next MyRg Else For Each MyRg In Application.Intersect(ActiveSheet.UsedRange, Range(Me.RefEdit1.Value)) With MyRg.Font

.Superscript = False '取消上标 .Subscript = False '取消下标 End With Next End If End Sub

Private Sub CommandButton2_Click()

第 32 页 共 52 页

Excel 2007 VBA 教程

End '退出 End Sub

Private Sub RefEdit1_Change()

TextBox1.Text = RefEdit1.Value '当RefEdit1.Value选完后，直接把它的值赋给文本框1，其实这个代码没有作用，便于显示出来，看选区 End Sub

Private Sub UserForm_Initialize() '加载窗体时 Dim i As Byte '定义变量

Me.OptionButton1.Value = 1 '加载窗体时把第一个单选按钮设置为默认的 For i = 1 To 5

Me.ComboBox1.AddItem i '给组合框赋值1，2，3，4，5 Next

Me.ComboBox1.ListIndex = 0 '让1显示为组合框的默认值 End Sub Sub test()

Myf1.Show 1 '显示窗体 End Sub

最后的效果图如图 22

图 22

第十一讲 窗体与控件实例——开发隐藏工作表工具

这一讲，相对前面来说，有一定的难度了，但是大家也要明白，你总有一天要步入这个程序开发中，如果你没有达到这一步，也就是总是VBA的门外汉，所以大家要坚持，不要退缩，其实那时学的时候也和你们一样，也放弃过，就是现在我还是有太多太多的不明白，看到一些高手写的程序，只要你天天学习，不放弃，那么你不懂就少一些，呵呵。说了一大堆话，老师也是为大家打打气，坚持 ，加油。 开发批量隐藏工作表工具的方法与步骤

一. 插入窗体与添加控件

二. 编写代码（因为代码后面都有解释，我这里就不多说了）

1. 普通模块里的代码

Sub test()

隐藏工具.Show 1 '打开隐藏工作表工具 End Sub

Sub auto_open() '一打开工作簿就执行下面的代码 如图24所示

MsgBox \记得打开---隐藏工作表工具的快捷是Ctrl+D\佛山小老鼠提醒你，不要忘记了！\温馨提示\ 'Chr(10)是换行

第 33 页 共 52 页

Excel 2007 VBA 教程

Call 打开快捷键 '呼叫“打开快捷”过程 ，然后运行它 End Sub

Sub 打开快捷键()

Application.OnKey \记得一定要小写字母，且不要加大括号，快捷键Ctrl+D执行test过程 End Sub

Sub 关闭快捷键()

Application.OnKey \记得一定要小写字母，且不要加大括号，把关联的过程名为空就删除了

'为了让其自动关闭，用了工作簿关闭事件，当然打开快捷键也就可以放在工作簿打开事件里 End Sub

2. 窗体模块里代码

Private Sub CommandButton1_Click() '执行隐藏

Dim i As Integer, k As Integer, s As Integer '定义变量

For k = 0 To Me.ListBox1.ListCount - 1 '遍历列表框中的所有成员

If Me.ListBox1.Selected(k) = True Then '判断列表框中那一行是选中的，如果有，那么 s = s + 1 '累加S 目的为了在后面判断是不是所有的工作表是否被选中 End If Next k

If Sheets.Count = s Then MsgBox \不能删除所有工作表，只少要留一张可见工作表\ '如果所有的工作表被选中，因为隐藏至少要保留一张，那么就结束程序

If Me.OptionButton1.Value = True Then '如果单选按钮1被选中，那么就要执行一般隐藏 For i = 0 To Me.ListBox1.ListCount - 1 '遍历列表框所有成员 If Me.ListBox1.Selected(i) = True Then '如果被选中

Worksheets(Me.ListBox1.List(i)).Visible = 0 '那么相对应的工作表就要一般隐藏，Me.ListBox1.List(i)得到是工作表名 End If ' Next i '

Else '否则就执行下面的代码

For i = 0 To Me.ListBox1.ListCount - 1 ''遍历列表框所有成员 If Me.ListBox1.Selected(i) = True Then '如果被选中'

Worksheets(Me.ListBox1.List(i)).Visible = xlSheetVeryHidden ''那么相对应的工作表就要深度隐藏 End If ' Next i ' End If End Sub

Private Sub CommandButton2_Click() '显示 Dim i As Integer '定义变量

For i = 0 To Me.ListBox1.ListCount - 1 '遍历列表框所有成员 If Me.ListBox1.Selected(i) = True Then ''如果被选中'

Worksheets(Me.ListBox1.List(i)).Visible = 1 '那么相对应的工作表就要显示 End If ' Next i ' End Sub

Private Sub CommandButton3_Click() '全选

第 34 页 共 52 页

Excel 2007 VBA 教程

Dim i As Integer '定义变量

For i = 0 To Me.ListBox1.ListCount - 1 '遍历列表框里所有成员

Me.ListBox1.Selected(i) = True '把一个一个依次选中，Selected(i) = True是表示选中，反之为False是不选中 Next i End Sub

Private Sub CommandButton4_Click() '反选 Dim i As Integer '定义变量

For i = 0 To Me.ListBox1.ListCount - 1 '遍历列表框里所有成员 If Me.ListBox1.Selected(i) = True Then '如果被选中'' Me.ListBox1.Selected(i) = False '取消选中 Else '否则，如果没有被选中的，那么

Me.ListBox1.Selected(i) = True '被选中 End If Next i ' End Sub

Private Sub UserForm_Initialize() '窗体加载时

Dim i As Integer, Mycount As Integer '定义变量

Mycount = Sheets.Count '统计工作簿里有多少个工作表，且把值赋给Mycount For i = 1 To Mycount '遍历所有工作表

Me.ListBox1.AddItem Sheets(i).Name '把工作表名一一加载到列表框中 Next i '

Me.ListBox1.MultiSelect = 1 'MultiSelect = 2 表示可以借助控件键Shift Ctrl进行多选， 'MultiSelect = 0 表示只能选选择一行。'MultiSelect = 1也能选多行，但是不能配合着Ctrl和Shift键进行多选

Me.OptionButton1.Value = True '使单元按钮1为默认值

Me.ListBox1.Selected(0) = True '使列表框中的第一个被选中 End Sub

三. 批量隐藏工作表的效果图如23

图 23 图 24

第 35 页 共 52 页

Excel 2007 VBA 教程

第十二讲自定义函数

一. 为什么要自定义函数（Function过程）

一说到函数，大家都说会Excel博大精深，确是，可以给我们工作上带来许多方便，Excel本身的函数有几百个了，那我们为什么还要自定义函数呢？

1. 简化求解过程，用其它公式做出来公式长且复杂 2. 弥补Excel内置函数的不足 二. 创建自定义函数过程

1. 方法一 Alt+F11——>>插入模块——>>插入过程——>>类型——>>函数。结果如图图 25

图 25

2. 方法二 手动输入

Function 函数名() ?? End Function

三. 自定义函数实例

1. 自定义提取活动工作表名的函数

Function 提取活动工作表名() '函数名是\提取活动工作表名\ Application.Volatile '声明为易失性函数

提取活动工作表名 = ActiveSheet.Name '把活动工作表赋值给函数名\提取活动工作表名\End Function

2. 自定义提取工作表名函数

Public Function 提取工作表名(i As Integer) '提取工作表名为中文函数名 Application.Volatile '声明为易失性函数

If i <= Sheets.Count Then '条件，不能让i的值超过工作表数目，如果超过就显示为空

提取工作表名 = Sheets(i).Name '按位置顺序把工作表名依次赋给函数名“提取工作表名” Else '

提取工作表名 = \ End If End Function

3. 自定义按颜色求和函数

Public Function SumColor(MyRg As Range, Col As Range) '定义函数名为SumColor Dim rg As Range, S As Long '定义相关的变量

第 36 页 共 52 页

Excel 2007 VBA 教程

Application.Volatile '声明为易失性函数

ColIndex = Col.Interior.ColorIndex '提取单元格Col的底纹颜色值赋值给ColIndex

For Each rg In Application.Intersect(ActiveSheet.UsedRange, MyRg) '遍历参数MyRg里的有数据区域

If rg.Interior.ColorIndex = ColIndex Then '如果MyRg区域里的单元格有底纹颜色和CoIdndex相同的，那么

S = S + rg.Value '单元格Rg的值到累加到S上 End If ' Next rg '

SumColor = S '最后把S的值赋给函数SumColor End Function

四. VBA中调用工作表函数

1. 在VBA中调用工作表函数需要在工作表函数前加上WorksheetFunction属性。应用于Application对象

的WorksheetFunction属性返回WorksheetFunction对象，作为VBA中调用工作表函数的容器，在实际应用中可省略Application对象识别符

2. 完整的表达式：Application.WorksheetFunction.工作表函数名 3. 要注意的是，函数参数一定要用VBA里引用方式 4. 实例 标示重复值

Private Sub CommandButton1_Click()

Dim Myarray As Range, mgr As Range, i As Integer '定义相关的变量

If RefEdit1.Value = \请选择你要标示的区域\佛山小老鼠提醒\

'如果控件RefEdit1没有选择区域，那么就退出，且提示，注意一行简写形式，你记住就可以了 'vbCritical你也可以改为数值16，显示警告的图标

Set Myarray = Range(RefEdit1) '把控件的选区赋给Myarray,因为是对象，所以用了Set,另外把控件RefEdit1选区转为单元格，一定要在前面加Range

Myarray.ClearFormats '清除选区原有颜色，目的是区分为了再标示，如果不清除如果原有字体也有红色，那样就和结果混了

For Each mgr In Application.Intersect(ActiveSheet.UsedRange, Myarray) '遍历选区有数据的单元格

'Intersect(ActiveSheet.UsedRange, Myarray)这一句，我不再多解释了，目的了防止整行整列选中，导致程序被卡死。

If Application.WorksheetFunction.CountIf(Myarray, mgr) > 1 Then '这一句就是举个例子的目的

'我们可以引用工作表里的内置函数，不过要注意的用法，单元格，单元格区域的引用方式。如果有重复

mgr.Font.ColorIndex = 3 '字体的颜色为红色 End If Next mgr '

Unload Me '关闭窗体 End Sub

Private Sub CommandButton2_Click() Unload Me ''关闭窗体 End Sub

第 37 页 共 52 页

本文来源：https://www.bwwdw.com/article/mez.html