Excelchapter.ppt

第10章宏与VBA程序设计 Excel与数据处理第2版 认识 在学校 一般都要学习类似于C语言 VB VFP之类的编程语言 一般都要编写类似于排序 查询之类的程序 但工后 绝大多数人都不会再用这样的程序了 然而 这对Excel是个例外 这些程序不仅能减化繁杂的工作 而且还能使工作自动化 在EXCEL中 VBA能够使那些枯燥反复的工作变得高效 准确而自动化 若想建立自已的办公自动化数据管理系统 需要用到VBA程序 若想成为一个真正的Excel专家 需要很好地掌握VBA程序设计 本章学习目标 1 了解Excel宏的基本知识2 掌握宏的录制 编写和运行方法3 了解VBA程序的基本知识4 掌握VBA的基本数据类型5 掌握VBA宏与函数的编写和调用方法6 掌握VBA条件 循环程序的设计方法7 了解用VBA变量的作用域8 了解VBA自动宏 10 1Excel宏 宏的概念宏是用VBA程序设计语言编写或录制的程序 其中保存有一系列Excel的命令 可以被多次重复使用 宏可以自动执行复杂的任务 减少完成任务所需的步骤 VBA即VisualBasicforApplications 它是VisualBasic的一个派生体 它有针对性地对VisualBasic进行了优化和设置 两者的主要区别在于 VisualBasic开发的应用程序可以独立在Windows系统中运行 而用VBA开发的程序只能在提供它的应用程序中运行 在Excel中 可以用Excel提供的宏录制工具录制宏程序 也可以使用它提供的 VisualBasic编辑器 直接编写宏 10 1Excel宏 2 录制宏宏录制器是Excel提供的一种软件工具 它能够将用户的操作过程记录下来 并自动将所记录的操作转换成为VBA程序代码 对于经常重复进行的操作过程 可以通过宏录制器将它记录下来 当需要再次进行这些操作时 只需要运行录制的宏 Excel就能自动完成这些重复的操作 说明 当录制宏的工作开始后 所有的操作步骤都将被记录在宏中 所以应尽量减少不必要的或错误的操作 如果在录制宏时出现失误 更正失误的操作也会记录在宏中 10 1Excel宏 录制宏的案例 例10 1 某班主任共管理10个班级的学生 要为每个班级建立学生档案 档案表结构如图所示 10 1Excel宏 建立学生档案宏选择 工具 宏 录制新宏 菜单项 在弹出的下示对话框中输入宏名字 学生档案 单击 确定 10 1Excel宏 3 输入表格内容 1 单击A1单元格 在其中输入 2001级学生档案 2 单击A2单元格 在其中输入 学号 3 单击B2单元格 在其中输入 姓名 4 单击C2单元格 在其中输入 班级 5 单击D2单元格 在其中输入 性别 6 单击E2单元格 在其中输入 籍贯 7 单击F2单元格 在其中输入 寝室 8 单击G2单元格 在其中输入 电话号码 13 选择A1 G1 然后单击工具条中的跨列居中按钮 14 选择 工具 宏 停止录制 菜单命令 10 1Excel宏 4 停止录制 查看录制的宏选择 工具 宏 停止录制 选择 工具 宏 宏 菜单项 会显示 宏 对话框 选中其中的 学生档案 单击 编辑 10 1Excel宏 宏代码1Sub学生档案 2 学生档案Macro3 宏由dk录制 时间 2005 4 204 快捷键 Ctrl s5Range A1 Select6ActiveCell FormulaR1C1 2001级一班学生档案 7Range A2 Select8ActiveCell FormulaR1C1 学号 9Range B2 Select10ActiveCell FormulaR1C1 姓名 11Range C2 Select12ActiveCell FormulaR1C1 班级 13Range D2 Select14ActiveCell FormulaR1C1 性别 15Range E2 Select16ActiveCell FormulaR1C1 籍贯 17Range F2 Select18ActiveCell FormulaR1C1 寝室 10 1Excel宏 19Range G2 Select20ActiveCell FormulaR1C1 电话号码 21Range A1 G1 Select22WithSelection23 HorizontalAlignment xlCenter24 VerticalAlignment xlBottom25 WrapText False26 Orientation 027 AddIndent False28 ShrinkToFit False29EndWith30Selection Merge31EndSub 10 1Excel宏 5 宏的结构Sub宏名 命令代码1命令代码2 EndSub 6 With的结构WithSelection EndWith 10 1Excel宏 7 宏的保存保存在个人宏工作簿 Personal xls 中 保存在专门保存宏的 新的工作簿 中 保存在建立宏的当前工作薄中 10 1Excel宏 8 宏的执行通过快捷键运行宏通过对话框运行宏通过自定义工具按钮运行宏通过图形控件或窗体命令按钮运行宏通过自定义菜单运行宏 以后再讲 10 2VBA程序设计基础 1 数据类型的概念在计算机中 数据也是按不同的类别进行运算和保存的 人们称之为数据类型 同种类型的数据占用相同大小的存储空间 相互之间可以进行计算 比较或赋值等操作 不同类型的数据占用的存储空间大小不一定相同 且相互之间不能进行计算和比较等操作 2 VBA的数据类型 1 常量数值常量 如 1 2 65 98 65等 10 2VBA程序设计基础 字符及字符串常量字符类型的常量称为字符常量 字符常量要用定界符双引号 界定 例如 d 5 A 等都是字符常量 符号常量Const常量名 常量值例如 ConstPI 3 14 ConstABC OK China 10 2VBA程序设计基础 2 变量变量是在程序运行期间其值可以发生变化的数据 Dim Private Public变量名As变量类型 例如 1DimA BAsInteger2A 13B 24A 4 bA 3A在本程序段中有3个不同的值 这就是变 10 2VBA程序设计基础 VBA数值数据类型 10 2VBA程序设计基础 字符串类型在VBA中有两种类型的字符串 变长与定长的字符串 Dims1AsString 变长类型Dims2AsString 10 定长类型s1 dddkdk s2 d1234567890sssss 10 2VBA程序设计基础 布尔类型布尔类型是比较运算或逻辑运算的结果值 它只有两个取值 True和False True是比较结果为真时的值 False是比较结果为假时的值 DimAAsBooleanDimBAsBooleanA 3 5B True 10 2VBA程序设计基础 日期类型日期型数据用于保存日期 占8个字节的存储空间 以浮点数值形式保存日期 可以表示的日期范围从公元100年1月1日到公元9999年12月31日 而时间可以从0 00 00到23 59 59 日期文字以 作界定符 Dimd1 d2AsDated1 1Jul98 D1的取值是1998年7月1号d2 12 2 2000 D2的值是2000年12月2号 10 2VBA程序设计基础 变体数据类型Variant是一种特殊的数据类型 除了定长String数据及用户定义类型外 它可以包含任何种类的数据 DimarAsVariantar 12ar stringtype ar abc arar 12 23在本例中 ar的类型是不定的 10 2VBA程序设计基础 数组在VBA中 可以声明一个数组来代表一组具有相同数据类型的数据 它就是数组 假设一个班有20个同学 每个同学有5门课程 可以定义一个20行5列的二维数组来保存他们的成绩Dimstu 1To20 1To5 AsSingle这条命令定义了一个二维表格 如下所示 stu 1 1 78stu 1 2 90stu 1 3 87stu 1 4 88stu 1 5 76 数组访问方法 78 10 2VBA程序设计基础 对象 属性和方法计算机程序设计中的对象是从现实世界中抽象出来的 它与现实世界中的对象具有相同的含义 对象具有属性和方法两种特性 对象属性语法规则李立 年龄 32李立 体重 70方法的调用也要按这种语法规则李立 学习李立 授课 Excel对象示例 10 2VBA程序设计基础 宏代码中的对象Sub宏4 1Range E6 Select2ActiveCell FormulaR1C1 eqw 3Range E6 E7 Select4Selection Font Bold True5Selection Font Italic True6WithSelection Font7 Name 华文楷体 8 Size 129 Shadow False10 Underline xlUnderlineStyleNone11 ColorIndex xlAutomatic12EndWith13Selection Font ColorIndex 314EndSub 10 3子程序 子程序的两种结构子程序是VBA的最小程序单位 它必须独立存在 但在一个子程序中可以调用另外一个子程序 它有两种形式 第一种没有参数 第二种有参数 Sub子程序名 子程序代码 EndSub Sub子程序名 p1 p2 p3 子程序代码 EndSub 10 3子程序 2 子程序的调用形式1 直接调用直接调用子程序名 如果有参数 则在子程序后面直接写上调用参数 2 用Call命令调用在Call命令的后面写上了程序的名字 如果子程序有参数 则必须将参数写在括号中 10 3子程序 3 子程序调用举例 1SubMain 2HouseCalc99800 431003CallHouseCalc 380950 49500 4CircleArea 4 5CircleArea46Message7EndSub SubHouseCalc priceAsSingle wageAsSingle If3 wage 0 85 priceThenMsgBox 你的薪水不能承担房价 ElseMsgBox 你的薪金足以承担房价 EndIfEndSub SubCircleArea RasSingle MsgBox3 14 R REndsub SubMessage MsgBox 这是一个无参子程序 EndSub 调用 10 4自定义函数 1函数结构 Function函数名 p1 p2 p3 AsType 函数代码 函数名 表达式EndFunction 10 4自定义函数 2 定义函数的注意事项 函数由Function和EndFunction语句所包含起来的VBA语句 Function函数和Sub子程序很类似 但函数有一个返回值 Function函数必须通过表达式调用 如果一个Function函数没有参数 它的Function语句必须包含一个空的圆括号 在函数体中 函数名至少被赋值一次 函数开头行的 AsType 用于指定函数值的返回值类型 如果省掉该定义 被视为Variant类型 10 4自定义函数 3 例10 3 编写一个计算圆面积的简单函数 圆半径作为函数参数 FunctionCircleArea rAsSingle AsSingleCircleArea 3 14 r rEndFunction 10 4自定义函数 4 函数调用函数只能在表达式中调用 在Excel中 函数至少有以下3种调用方式 在Sub子程序中调用函数在其他函数中调用函数在Excel工作表单元格的公式中调用函数 10 4自定义函数 5 自定义函数案例假设有一个学生成绩表如图所示 现要计算其中的综合成绩 在本例中 综合成绩的计算方法为 综合成绩 考试科目 0 7 考查科目 0 3 编写计算综合成绩的函数ss 10 4自定义函数 计算综合成绩的自定义函数Functionss ks1 ks2 kc1 kc2 s c ss ks1 ks2 s kc1 kc2 cEndFunction该函数定义出来之后 可在编写它的任何工作表中调用 与调用Excel内置函数无任何差别 比如 在上图的G5中输入公式 ss C5 D5 E5 F5 E 2 E 3 然后把该公式向下填充复制到最后一位同学的 综合成绩 单元格 这样就可计算出所有同学的综合成绩 10 5VBA选择结构 1 VBA程序执行的方式在一般情况下 一个VBA的Sub子程序和Function函数的执行都是从程序代码的第一个语句行开始 逐行运行程序代码中的语句 直到遇到EndSub或EndFunction来结束整个程序的执行 有些时候 需要程序代码按一定的条件执行 当条件成立的时候 执行一部分程序代码 条件不成立的时候执行另外一部分程序代码 这种功能需要用VBA的选择结构来实现 10 5VBA选择结构 2 VBA条件语句的语法 形式1 If条件Then 语句组1 Else语句组2 例如 Ifsalary 1500Thenrate 0 1Elserate 0 05如果salary的值为2000 则rate 0 1如果salary的值为100 则rate 0 05 10 5VBA选择结构 形式2 If条件Then语句组1 Else语句组2 EndIf Functionabc a b Ifa bThent aa bb tEndIfabc aEndFunction 例如 10 5VBA选择结构 形式3 If条件1Then语句组1ElseIf条件2Then语句组2 ElseIf条件nThen语句组nElse语句组n 1EndIf 10 5VBA选择结构 3 条件函数案例某汽车出租公司可为顾客提供运送货物的业务 根据货物的重量及路程可对运费进行适当的优惠 设运费F 单位为元 重量P 单位为吨 路程S 公里 及优惠系数 D 之间的关系式为 F P S W 1 D 优惠系数D与路程远近的关系如下 编写计算折扣的函数 10 5VBA选择结构 计算折扣的函数Functiond s Ifs 1000Thend 0 1ElseIfs 750Thend 0 07ElseIfs 500Thend 0 05ElseIfs 250Thend 0 02Elsed 0EndIfEndFunction 10 5VBA选择结构 调用自定义函数计算 C2 D2 E2 1 d E2 10 5VBA选择结构 4分情况选择语句SelectCase语句的语法结构如下 SelectCase测试表达式Case表达式1语句组1Case表达式2语句组2 CaseElse语句组nEndSelect 10 5VBA选择结构 Select案例某学校的职工人事数据存在Excel工作表中 如图所示 现要按职称提升每位职工的工资 各种职称的工资增长情况如下 教授150 副教授130 讲师100 助教80 高级工程师150 工程师140 助工90 用select语句编写计算增加工资的函数 10 5VBA选择结构 编写的Seclect函数 FunctionAddSalary 职称 职称作为一个参数SelectCase职称Case 教授 高级工程师 AddSalary 150Case 副教授 AddSalary 130Case 讲师 AddSalary 100Case 助教 AddSalary 80Case 工程师 AddSalary 140Case 助工 AddSalary 90EndSelectEndFunction 10 6VBA循环结构 在计算机中 一些被重复执行的语句是通过循环来完成的 1 For Next循环结构 Forcounter startToend step步长 循环语句1循环语句2循环语句3 循环语句nNext counter 10 6VBA循环结构 For循环的工作过程如下 1 计算出start end和s的值 这三项都可以是变量 表达式或常数 并把start的计算结果值赋给循环控制变量x 2 比较x与end的计算结果 如果x的值超过了end的计算结果就停止循环 执行Next后面的语句 否则 执行第3步 3 执行循环语句组中的语句 4 将x中的值增加一个步长值s 然后执行第2步 10 6VBA循环结构 For循环案例某公司职工档案数据保存在Excel工作表中 如下图所示 该公司共有1234名职工 每月要从工资表中扣除一定的住房公积金 假设住房公积金按以下的百分比扣除 编写计算公积金比例的宏程序 10 6VBA循环结构 Functioncountrate salaryAsDouble 这个函数计算公积金的百分比DimrateAsDoubleIfsalary 2000Thenrate 0 1ElseIfsalary 1500Thenrate 0 07ElseIfsalary 1200Thenrate 0 05ElseIfsalary 1000Thenrate 0 02ElseIfsalary 800Thenrate 0 01Elserate 0EndIfcountrate rateEndFunction 数函的例比金积公算计 10 6VBA循环结构 计算所有职工工积金的宏 SubCountData Fori 3To1236 从工作表的第3行开始计算 第3行中的是第1位职工工资r countrate Cells i 2 计算出第i位职工的公积金比例Cells i 3 r 将第i位职工的公积金比例填入本行的第3列中Cells i 3 Style Percent 将公积金比例的格式设置为百分比Cells i 4 r Cells i 2 计算第1位职工的公积金并填入本行的第4列中Cells i 5 Cells i 2 Cells i 4 计算第i位职工的应发工资并填入第5列中NextEndSub 10 6VBA循环结构 调用宏计算公积金 10 6VBA循环结构 2While Wend循环结构在事先知道循环次数时 用For循环方便 如果事先并不知道循环次数 但知道执行或结束循环的条件 则用Do Loop循环或While Wend循环 While循环的语法结构如下 While判定条件语句1语句2 Wend 10 6VBA循环结构 3Do Loop循环结构 第一种结构为 DoWhile判定条件 Loop 第二种结构为 Do LoopWhile判定条件 第三种结构为 DoUntil判定条件 Loop 第四种结构为 Do LoopUntil判定条件 10 6VBA循环结构 案例在上节的For循环实例中 假设并不知道有多少位职工 在其他条件都相同的条件下 现用While Wend循环结构来计算每位职工的住房公积金比例 应交公积金和实际发放工资 改写的函数如下 10 6VBA循环结构 用While循环改定的函数 SubCountDataB i 3 第1位职工在第3行WhileNot IsEmpty Cells i 2 从工作表的第3行开始计算r countrate Cells i 2 计算出第i位职工的公积金比例Cells i 3 r 把第i位职工的公积金比例填入本行的第3列中Cells i 3 Style Percent 把公积金比例的格式设置为百分比Cells i 4 r Cells i 2 计算第1位职工的公积金并填入本行的第4列中Cells i 5 Cells i 2 Cells i 4 计算第i位职工的应发工资并填入本行第5列中i i 1 计算下一位职工的公积金WendEndSub 10 6VBA循环结构 用do循环改写的函数 SubCountDataC i 3Dor countrate Cells i 2 Cells i 3 rCells i 3 Style Percent Cells i 4 r Cells i 2 Cells i 5 Cells i 2 Cells i 4 i i 1LoopUntil IsEmpty Cells i 2 EndSub 10 6VBA循环结构 4数组在循环中的应用循环的主要功能是进行VBA代码的重复执行 数组的主要功能是存储大量的数据 在计算机应用中 数组和循环相结合能够解决许多实际问题 例如大批量数据的排序 统计 检索等功能 案例 10 6VBA循环结构 某学校每年对新生都要进行一次英语入学测试 以了解每位学生的英语能力 考试完毕后 要统计每个分数段的人数 并根据学生的成绩将他们分为A B C 若干个班 第1 25名同学进入A班 第26 50名同学进入B班 其余的以此类推 该校每年招收的人数都有细小的差别 假设学生的英语测试成绩如下页图示 编写一个宏完成以下功能 自动统计出各分数段的人数 并将统计结果保存在一个新的工作表中 对所有的考试成绩进行排序 根据排名对学生进行分班 每个班25名学生 每个班保存在一个新的工作表中 10 6VBA循环结构 成绩表本例程序代码较多 不便PPT演示 可直接查看Excel中的宏 10 7模块与变量的作用域 VBA变量的作用域变量的作用域是指变量的有效范围 局部变量在VBA宏或函数内部定义的变量 只能在定义它的函数或宏范围内可用 这种变量称局部变量 模块级变量如果一个变量在模块的开始部分定义 且不包含在任何宏或函数体内 则在本模块内的所有宏或函数中都可以使用它 全局变量在应用程序的各个模块之间都可共用的变量 10 7模块与变量的作用域 2 变量的定义方式宏或函数的私有变量 可直接在宏或函数体内定义 Dim变量名表As数据类型在模块的声明段中用Dim语句声明一个模块级变量 Dim变量名表As数据类型在模块的声明段中 或模块内 使用Public语句 而非Dim语句来声明一个公共变量 声明方式如下 Public变量名表As数据类型在宏或函数的名称前也可以加上Public关键字 这样的宏或函数就是全局宏 全局函数 例如 PublicMa Ya TaAsInteger 10 7模块与变量的作用域 3 变量类型的案例有一个教师的档案表 部分数据 如图所示 编写宏将其中的男教师和女教师分别保存在新工作表中 10 7模块与变量的作用域 插入两模块 在模块1中的程序如下 PublicN 声明一个全局变量用于保存教师总人数Publictea 100 5 AsVariant 声明一个全局数组保存教师的所有数据 SubreadData Dimi jAsInteger 定义两个只能在本宏内部分使用的局部变量Worksheets 教师总表 Activate 激活教师工作表N 1WhileNotIsEmpty Cells N 1 如果教师工作表中的第一列数据非空 就将Fori 1To5 该行的数据读入全局数组中tea N i Cells N i NextiN n 1 读入一个教师的数据后 教师的人数增加一个WendEndSub 10 7模块与变量的作用域 分离出男教师的宏 Dimi j kAsIntegerSubman Sheets Add 插入一个工作表 在其中保存男教师的数据ActiveSheet Name 男教师 修改工作表的名称为 男教师 Forj 1To5Cells 1 j tea 1 j 读入第一行数据即表头到二维数组的第一行Nextjk 2 k统计男教师的人数 Fori 2ToN 从数组的第2行开始分析数据Iftea i 3 男 Then 如果数组的第3列中的数据为 男 Forj 1To5Cells k j tea i j 将 男 教师的数据从数组读入工作表Nextjk k 1EndIfNextiEndSub 10 7模块与变量的作用域 制定调用宏的工作表 10 8VBA自动宏 1 概述所谓自动宏就是指在某一特定时间自动执行的宏程序ExcelVBA提供了两个非常有用的自动运行宏 一个是宏Auto Open 另一个是宏Auto Close Auto Open宏 在打开它所在的工作簿时 它就会自动运行 Auto Close会在关闭它所在的工作薄时自动运行 这两个宏常被用来完成一些自动化工作 如打开工作薄时启动自定义菜单 打开必备的工作薄或工作表 或初始化一些数据 如此等等 关闭工作薄时 完成一些结束工作 如保存数据 删除不必要的工作表等 10 8VBA自动宏 案例 Auto openSubauto open 打开工作薄时自动运行的宏Dimi jAsInteger 定义两个只能在本宏内部分使用的局部变量Worksheets 教师档案 Activate 激活教师工作表n 1WhileNotIsEmpty Cells n 1 如果教师工作表中的第一列数据非空 就把 该行的数据读入全局数组中Fori 1To5teacher n i Cells n i