excelVBA教程.docx

1、excelVBA教程佛山小老鼠说VBA前言1为什么要学VBA？随着你的Excel水平不断的提高，以前你不会函数，现在会函数了，相信大家尝试到函数的甜头，可以批量操作一些数据，如，从文本里提取数字，按条件求和，自动生成工资条，考勤表，查询表，输入一个人的身份证号码，会自动填好“出生地”，“出生年月日”，“年龄”，“性别”等等，一谈起函数大家都会“不亦乐乎”。“津津乐道”，“和函数相见恨晚”，说实在话，我当时也是一种这样的心态，我学会的第一个函数if，当时高兴地睡不着觉。因为它可以判断“不及格，及格，良好，优秀”，感觉到这个函数太神奇了。对工作太有用了，于是我下定决心一定要把函数学好，当然中间也产

2、生过“放弃”的念头。为什么呢？因为每一个函数的参数都是长长的，不认识的英文单词，然后又有一些朋友叫我查看Excel自带的“帮助”文件。极少一部分看“帮助”文件还是可以看懂的，大多数根本看“帮助”文件看不懂，发现自带的“帮助”是便于有一定基础的使用者查找和学习某个知识点，而对于新手来说，也像“侠客行”里的“石破天”看不懂石壁上的蝌蚪文，不能明白其意思。呵呵，一扯又扯远了，还是回到主题上来。当我们的函数学到了一定的水平时，可是，还是发现有一些问题不能解决。如果要解决用的函数特别复杂且很公式很长，有的根本用函数不能解决了。如一个单元格又有文字，又有数字，且数字出现的位置没有规律，且不只一次出现。要求

3、把数字提取出来。又如提取工作薄里的各工作表名来制作目录。当然我们可以用函数实现，但是特别麻烦。又如，把多作工作簿汇总到一个工作簿里一个工作表时，方便我们汇总，现有的Excel功能无法批量操作，只能一个一个的，通过复制粘贴来完成。如果我们会VBA ，你又到了Ecel里的另一个天地了。开个玩笑，Excel VBA，就像“九阴真经”里的第九层，也就是说，你会了Excel的VBA，那么你就可以随心所欲驾驭Excel表格，别人要三天才能完成的报表，你只要半天，甚至更短的时间，这样大大可以提高你的工作效率，上班时，别人上班时忙来忙去，你倒是很轻松。2VBA的作用（提高工作效率）2.1完成Excel现有功能

4、不能实现的功能 2.2使重复的工作不再重复2.3自定义函数（方便不会用函数的朋友）2.4实现“人机对话”（通过弹出一些窗体和对话框）2.5自定义Excel选项卡（制作出适合自己的工作界面选项卡和功能）2.6开发一些小程序（如“进销存”和一些小工具）3学VBA的基础3.1要有一点函数基础，和会Excel基础操作。建议学完办公高级班的学生和在职办公人员学习VBA3.2学VBA需要很好的英文基础吗？答案是否定，学习Excel里的VBA和英文基础没有什么必然联系，因为Excel里的VBA那些关键字什么的都是最基础的英语单词，只要你上过初中，大部分还都能认识，再者他们还都是固定的，你就是不认识，硬记下来

5、也足够了，Excel里的VBA又不是让你用英语写文章，也不是让你通读英语文章！第一讲VBA介绍和VBE编辑环境为什么要学VBA？1提高工作效率，在我们Excel办公中，许多工作是重复的，我们可以通过录制宏把那些动作录制下来。下一次做时就不要重复做了，只要点一下一个按钮，就帮我们完成了。2现有功能很难实现或者实现要分许多步骤且比较复杂，方便一些新手操作和普通用户操作。3实现一些适合自己工作的一些特殊功能，因为每一个人，每一个行业存在的不同需求，而微软公司不能面面俱到，有时要通过我们自己用VBA来实现。4可以定制个性化的选项卡和命令，实现方便自己工作的操作的窗口界面5开发一些小程序VBA介绍6VB

6、A是VB的一个分支，是一门简单易用的编程语言，说的通俗一点就是由一些代码和语句按照一定的逻辑组成，能实现一些功能和作用的代码VBE窗口介绍7VBA窗口7.1.1用快捷键（Alt+F11），打开就可以看到（图 1）的界面，这个就是VBE窗口界面,和2003版本的没有变化，还是一样的。图 17.2图1的上面是菜单栏和工具栏，和平常的2003版本的Excel菜单栏和有工具栏一样， 7.3图1的左边是“工程资源管理器”和属性窗口7.4图1的右下边是“立即窗口”7.5立即窗口的作用，便于快速验证VBA语句，在立即窗口中输入msgbox 我在学习VBA 然后按一下回车键，就会弹出一个提示框。7.5.1用双

7、击“工程资源管理器”里的Sheet1(Sheet1),就可以看到如（图 2）所示的就可以看到对象列标表和过程列标框，白色的区域那一块就是我们要写代码的区域。图 28添加模块，窗体，类模块（打开VBE窗口是没有这个三个模块的，只有对象模块）8.1插入菜单，模块（有的也叫做标准模块）8.2插入菜单，类模块8.3插入菜单，窗体9删除模块，窗体，类模块9.1如果想不要模块，窗体，类模块，把光标对准相应的模块右击，移除模块。10对象浏览器窗口，视图菜单，对象浏览器（也可以按快捷F2）可以帮我们找到对象的属性，方法，事件的一些用法。11设置VBE的编辑环境，为了便于自己编写和查看，调试代码，要对VBE编辑

8、窗口一些设置进钩选，选择适合自己的的选项。如（图 3）图 311.1字号大小：工具菜单选项编辑器格式大小（S）如（图 4），一般我们设置为11磅图 411.2自动弹出成员列表：自动弹出成员列这个功能对于初学者和老用户来说都是非常有用，因为我们刚学的时候，特别是英文基础差一点的学生来说，可以给大家很大的帮助。举个例子，工具菜单，选项，编辑器，钩起“自动列出成员（L）”，然后在任何代码窗口中输入“Sheets加上一个点号”，就会弹出如（图 5）所以的成员列表，绿色图标的是方法，有一个手形的图标是属性图 511.3要求声明变量：举个例子，工具菜单选项编辑器钩起“要求变量声明（R）”，然后新建一个标准

9、模块，就会在新建标准模块的最顶端会自动添加Option Explicit然后在标准模块里输入，按F5执行代码，就会弹出（图 6）这样的提醒，如果我们把Option Explicit这一句删除，再按F5执行代码就不会弹出（图 6）这样的提醒Sub test() Set MyRg = Range(A1) MsgBox MyRgEnd Sub图 611.4自动显示数据提示：举个例子，工具菜单，选项，编辑器，钩起“自动显示数据提示（S）”，然后新建一个标准模块，输入以下代码Sub test() Set MyRg = Range(A1) MsgBox MyRg Set MyRg = NothingEnd

10、 Sub主要是便于代码逐步或者设置断点调试，想知道变量的值时，这时把光标移到变量上，就会弹出一个提示如（图 7）所示图 712运行过程，中断，重新设置，在工具栏上用方框框起来的三个按钮分别表为“运行子过程/用户窗体”；“中断”；“重新设置”。如（图 8）图 812.1运行过程分为全过程运行（快捷键F5）和逐步运行（快捷F8）12.2重新设置，当逐步运行时，想取消逐步运行，按一下“重新设置”这个按钮。13中断：相当于按了一下Esc，当一个过程运行进入了死循环，这时可以按一下Esc第二讲 录制宏 指定宏 运行宏 修改宏 保存宏 加载宏一、录制宏1、显示“开发工具”选项卡方法：Office按钮Exc

11、el选项常用在功能区显示“开发工具”选项卡2、录制在A1单元格输入“培训”这样一个宏3、录制方法 “开发工具”选项卡代码组录制宏弹出一个“录制新宏”对话框输入宏名为“输入” 确定 选中A1单元格 输入“培训”单击一下编辑栏的钩停止录制二、查看宏1、方法一 在上一节课我们已知知道了，相信大家没有忘记那个快捷键，如果忘记了，我就晕了。Alt+F11，双击模块1便可以看到刚才录制的代码2、方法二 “开发工具”选项卡代码组单击一下Visual Basic按钮双击模块1看到以下代码Sub 输入() 输入 Macro Range(A1).Select ActiveCell.FormulaR1C1 = 培训

12、End Sub三、指定宏1、方法一 “开发工具”选项卡控件组插入表单控件单击一下“按钮”按住左键不放，在工作表里拖拉一下画完后，弹出一个对话框，指定宏选中“输入”确定2、其它方法 也可以把宏指定给其它对象，如“自选图形”里的圆，艺术字A、打开比方，指定给艺术字的方法 把光标定位到你要指定的艺术字右击指定宏弹出“指定宏”对话框选中“输入”确定3、修改“按钮”的名字 方法 把光标定位到按钮上右击编辑文字修改为“输入”四、运行宏1、方法一 我们先删除A1单元格里的内容单击一下“输入”按钮2、方法二 “开发工具”选项卡代码组单击一下“宏”按钮选中“输入”单击一下“执行”3、其它方法 我们在上一节课讲过

13、，在VBE编辑器里，把光标定位到你“输入”的宏过程任何一行代码里，运行F5，逐步运行F8五、修改宏1、选中A1:D9单元格区域，录制一个给它添加边框的宏，录制的代码如下Sub 添加边框() 添加边框 Macro Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .Ti

14、ntAndShade = 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 W

15、ith 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(xlIn

16、sideHorizontal) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End WithEnd Sub2、修改后的代码Sub 添加边框() With Selection.Borders .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End WithEnd Sub从上面的代码可以看出，录制宏会录制出许多多余的代码出来。因此要把它们去掉，绿

17、色是注释也把删除，它是对上边框，下边框，左边框，右边框 ，中间横线，中间的竖线一个一个处理，因此用With Selection.Borders对所有的边框六、保存宏1、保存带有宏的工作簿（xlsm）如图10A、当我们保存带有宏的工作簿时，会弹出一个这样的提示对话框如图9往往没有接触过宏的朋友会吓一跳的，其实我们把下面这个图里的字读一次，就明白了，原来要保存为“启用宏工作簿”这个是2003版有区别的，微软公司的目的就是让用户在未打开之前提醒用户这个是带有宏的工作簿图 9 图 10七、保存为加载宏（xlam）如图111、选择“Excel加载宏（*xlam）就会自动打开这个文件夹C:Document

18、s and SettingsAdministratorApplication DataMicrosoftAddIns，然后输入你的加载宏名图 112、加载宏的特点A、隐藏的B、一打Excel程序就会打开3、加载“加载宏”A、Office按钮Excel选项加载项转到加载项对话框钩起“加载项名”前面的钩确定4、删除加载宏A、2007删除加载宏比较麻烦，2010版的就改良这个，2010版直接在开始工具选项卡，有一个“加载项按钮”，那么2007怎么删除它呢？Office按钮Excel选项加载项转到加载项对话框去掉“加载项名”前面的钩确定八、保存为个人宏工作簿：在我们录制宏时，有时我们可以选中“保存为个

19、人宏工作簿”，便可以在所有的工作簿中用，不过有个缺点，会打开个人宏工作簿，所以建议大家用“加载宏”，保存“个人宏工作簿”是对VBA新手打造的。第三讲 VBA 语法基础在学习VBA之前，我们要了解VBA里的对象，属性，方法，事件，就像我们在生活中一样，和一个人谈恋爱，是不是要相互了解对方，才能步入结婚的礼堂。下面来一一介绍它们一、对象1)什么是对象呢？生活中的手机，电视机，桌子等等这些就是对象，而在我们的Excel里VBA 的对象是指什么呢？这个可能抽象一点。工作簿，工作表，艺术字，图片这些就是Excel里的对象2)实例A.Workbooks 代表工作簿集合，所有的工作簿，Workbooks(i

20、)，表示已打开的第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.

21、Selection 被选中的单元格或者单元格区域二、属性1)属性是指对象的特点，对象固有的，如图片就有图片高度，图片的宽度，单元格就有单元格的底纹，单元格字体的颜色，这些就是它们的的属性，打个比方，生活的一些东西，如“苹果”，苹果的形状，苹果的颜色，苹果的重量，这些就是苹果的属性。2)实例A.显示单元格A1相对引用的地址，而这个Address就是单元格Range的属性Sub test() MsgBox Range(A1).Address(0, 0)End SubB.代码解释 属性中间一定要用点号分开，可能有的学生会问，怎么才有能知道它有那些属性呢？打个比方，我现在想知道工作表有那些属性，我们可

22、以先输入Sheet1再加一下点号，就会自动弹出其相应的属性列表出来，就像我们的Excel2007版的函数一样，你输入一个字母，就会弹出以这个字母开头所有函数出来，这样大家也就不用去记这些属性具体这个英文单词怎么写，只要大概了解知道有个这样的属性就可以了三、方法1)方法是作用对象的一些动作，工作表删除，工作表移动，单元格复制，这些删除，移动，复制就是相应对象的方法。打个比方，苹果被削了，削就是苹果的方法。2)实例A.在第一个工作表前面插入一个工作表Sub test() Sheets.Add before:=Sheets(1)End SubB.代码解释 Sheets是指工作表类，也就是工作表对象，

23、中间用一个点分开，Add就是方法了，然后再输入一个空格，before是对Add方法的一个补充说明，格式一定要这样，输入了Before之后，再输入一个冒号，接着输入一个等号，整个代码的意思，在第一个工作表前插入一个新的工作表。四、事件1)事件在Excel VBA里是指一定条件下，触发过程，如双击左键，右击，改变单元格内容，选择不同的单元格就会触发一个过程就叫做事件，事件一般都是写在相应的工作表模块里2)实例A.双击就会弹出一个问候对话框Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

24、 MsgBox 你好End SubB.代码解释：双击左键就会弹出一个问候对话框，Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean这个是自动生成的，你到工作表模块里，如双击Sheet1模块，通用列框里选择Worksheet，声明列表里选择BeforeDoubleClik，Msgbox是显示对话框函数。五、变量1)什么是变量 变量是指在代码运行过程中其值能够发生变化的量，举个例子，Y2*X，这是一个方程 ，当X1时，Y2，当X2时，Y4，当X3时，Y6， 这个X就是一个变量2)变量中不能

25、使用的一些符号 # * % ！，用的最多的是 _3)变量不区分大小写，也可以使用中文，但实际应用中很少用，我们用英文表示变量名一般也会采取一些特点，如文本型的变量用Str ，这样让人一看就这个变量表示文本型数据类型String4)为什么要声明变量A.如果数据量不大，在Excel里VBA不声明变量也没有影响的，但是，如果数据量很大时，不声明，程序的运行速度就会慢许多。B.打个比方，你打了一个电话给公交车公司你要租一辆车，但是你没有说你要租一辆多少人坐的车，那么出租车公司就会给你一坐100个人的车，而实际人你才要一个30人坐的车。大车的费用肯定比小车的费用贵，这样你不划算啊，变量也是这样，你不声明

26、，它就给存储空间最大的个数据类型给你那不声明的变量。C.再打个比方：小明的妈叫小明去菜市场去一斤打酱油，小明从家里拿了一个麻袋去，小明妈妈拉住小明了，说麻袋不能装酱油，会漏掉，于是小明提了了一个大水缺，在路上，小明摔了一跤，酱油也没有了，水缺也破了，小明真是陪了夫人又折兵，后来他妈妈告诉小明，下次你去打酱油就拿一个装一斤矿泉水瓶子去就可以了。呵呵，相信听了这个故事肯定笑了。也明白了为什么要声明定义变量。5)变量声明方式A.格式一 Dim 变量名 as 数据类型Dim i as IntegerB.格式二 dim 变量名 as 数据类型，变量名 as 数据类型 记得用用逗号分开Dim I as I

27、nteger，Rg as Range，Str as String6)强制声明A.变明使用前声明是一个好习惯，但我们会忘记，怎么办呢B.方法一 Alt+F11工具菜单选项编辑器选项卡中钩起“要求变量声明”复选框 如图 12图 12C.方法二 在代码模块顶端输入 Option Explicit7)声明变量的一种简写形式A.我们经常会看这样的一种定义变量的格式 Dim I% ，大家会问，这个是什么意思呢？，把变量I数据类型定义为整型。常见的有整型Integer用% 长整型Long用& 字符串String用$ 单精度浮点型Single用! 双精度浮点型Double用#8)变量的作用域变量的生命周期，也

28、就是变量什么时候销毁，释放内存，因为变量只是暂时的存在内存中的，根据这样把变量分为 过程级变量，模块级变量，全局性变量，静态变量A.过程变量：定义变量是写在过程中的，其作用是在过程运行中，当过程结束变量也就销毁了。1.实例Sub test() Dim i As String i = 佛山小老鼠 MsgBox iEnd SubB.模块级变量：定义变量写在模块的顶端，在此模块中可以调用此变量，其值还是存在，别的模块就不行了1.实例 Dim Str As StringSub test1() Str = 小老鼠 MsgBox Str Str = 佛山 & StrEnd SubSub test2() M

29、sgBox Str Str = End Sub代码解释 先运行Test1，然后运行Test2，大家可以看到对话框显示“佛山小老鼠”，也就是说运行过程Test2时，把过程Test1里的小老鼠也继承下来了。C.全局性变量 也是定义变量写在模块的顶端，不过就不是用Dim来定义了，要用Public，这个变量就在所有的模块都可以调用，也就是这个变量值一直存在，直到把Excel程序关闭，变量值才会销毁。1.实例 先插入两个模块，在模块1中输入以下代码Public Str As StringSub test1() Str = 佛山小老鼠 MsgBox StrEnd Sub在模块2中输入以下代码Sub tes

30、t2() MsgBox StrEnd Sub然后先运行模块1中的代码，然后再运行模块2中的代码，大家就会发现对话框中显示了“佛山小老鼠”，相信大家都明白了这个道量，另外全局性变量不能重复定义，重复定义就会报错。9)静态变量静态变量 定义静态变量是在写在过程中，用Static定义，结束后，变量值仍旧保留，但是大家要用和模级变量区分，模块级变量是值作用于此模块所有过程，而静态变量只值只作用于本过程，不作有于其它过程。A.实例Sub Test1()Static i As IntegerMsgBox ii = i + 1End SubSub Test2() MsgBox iEnd Sub代码解释：先运行Test1过程，显示i为0，再运行Test1过程为1，再运行Test1过程为2，再运行Test1过程为3，我们发现每运行一次结果会加1，因为我们有一个累加变量 i=i+1,其值还是保留，但当我们再行Test2时，可是那个i还是0