Excel的使用第10章.ppt

第10章宏与VBA,前言,若想使那些枯燥反复的工作变得高效、准确而自动化，请认真学习本章的内容。若想建立自已的办公自动化数据管理系统，请认真学习本章的内容。若想成为一个真正的Excel专家，不但要学习本章的内容，而且还不够！,本章学习目标,1、了解Excel宏的基本知识2、掌握宏的录制、编写和运行方法3、了解VBA程序的基本知识4、掌握VBA的基本数据类型5、掌握VBA宏与函数的编写和调用方法6、掌握VBA条件、循环程序的设计方法7、掌握VBA窗体的设计方法8、掌握VBA的对话框和菜单程序的设计方法9、了解用VBA和EXCEL相结合开发应用程序的方法,10.1Excel宏,宏的概念宏是用户用VBA程序设计语言编写或录制的程序，其中保存有一系列Excel的命令，可以被多次重复使用。宏可以自动执行复杂的任务，减少完成任务所需的步骤。VBA即VisualBasicforApplications，它是VisualBasic的一个派生体，它有针对性地对VisualBasic进行了优化和设置。两者的主要区别在于：VisualBasic开发的应用程序可以独立在Windows系统中运行，而用VBA开发的程序只能在提供它的应用程序中运行。在Excel中，可以用Excel提供的宏录制工具录制宏程序，也可以使用它提供的“VisualBasic编辑器”直接编写宏。,10.1Excel宏,2、录制宏宏录制器是Excel提供的一种软件工具，它能够将用户的操作过程记录下来，并自动将所记录的操作转换成为VBA程序代码。对于经常重复进行的操作过程，可以通过宏录制器将它记录下来，当需要再次进行这些操作时，只需要运行录制的宏，Excel就能自动完成这些重复的操作。说明：当录制宏的工作开始后，所有的操作步骤都将被记录在宏中，所以应尽量减少不必要的或错误的操作，如果在录制宏时出现失误，更正失误的操作也会记录在宏中。,10.1Excel宏,录制宏的案例建立10班的学生档案表，档案表的结构如下图所示。录制一个能够建立这种档案表结构的宏。,10.1Excel宏,建立学生档案宏选择“工具”|“宏”|“录制新宏”菜单项，在弹出的下示对话框中输入宏名字“学生档案”单击“确定”,10.1Excel宏,3.输入表格内容（1）单击A1单元格，在其中输入“2001级学生档案”。（2）单击A2单元格，在其中输入“学号”。（3）单击B2单元格，在其中输入“姓名”。（4）单击C2单元格，在其中输入“班级”。（5）单击D2单元格，在其中输入“性别”。（6）单击E2单元格，在其中输入“籍贯”。（7）单击F2单元格，在其中输入“寝室”。（8）单击G2单元格，在其中输入“电话号码”。（9）选择A1:G1单元格区域，然后单击工具条中的跨列居中按钮。（10）单击“停止”记录制工具条中的停止按钮。（11）保存该工作簿为“学籍档案.xls”。,10.1Excel宏,4.停止录制，查看录制的宏选择“工具”|“宏”|“停止录制”选择“工具”|“宏”|“宏”菜单项，会显示“宏”对话框。选中其中的“学生档案”，单击“编辑”。,10.1Excel宏,宏代码1Sub学生档案()2学生档案Macro3宏由dk录制，时间:2004-7-184快捷键: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命令代码2EndSub,6、With的结构WithSelectionEndWith,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）变量变量是在程序运行期间其值可以发生变化的数据。例如：,1DimA,BAsInteger2A=13B=24A=4+bA=3A在本程序段中有3个不同的值，这就是变！,10.2VBA程序设计基础,VBA数值数据类型,10.2VBA程序设计基础,字符串类型在VBA中有两种类型的字符串，变长与定长的字符串。,Dims1AsString/变长类型Dims2AsString*10/定长类型s1=dddkdks2=d1234567890sssss,10.2VBA程序设计基础,布尔类型布尔类型是比较运算或逻辑运算的结果值，它只有两个取值：True和False。True是比较结果为真时的值，False是比较结果为假时的值。,DimAAsBooleanDimBAsBooleanA=35B=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=stringtypear=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.3子程序,子程序的两种结构子程序是VBA的最小程序单位，它必须独立存在，但在一个子程序中可以调用另外一个子程序。它有两种形式，第一种没有参数，第二种有参数,Sub子程序名子程序代码EndSub,Sub子程序名(p1,p2,p3)子程序代码EndSub,10.3子程序,2、子程序的调用形式1）直接调用直接调用子程序名，如果有参数，则在子程序后面直接写上调用参数；2）用Call命令调用在Call命令的后面写上了程序的名字，如果子程序有参数，则必须将参数写在括号中。,10.3子程序,子程序调用举例,1SubMain()2HouseCalc99800,431003CallHouseCalc(380950,49500)4CircleArea(4)5CircleArea46Message7EndSub,SubHouseCalc(priceAsSingle,wageAsSingle)If3*wage1500Thenrate=0.1Elserate=0.05,10.5VBA选择结构,形式2,If条件Then语句组1Else语句组2EndIf,Functionabc(a,b)IfabThent=aa=bb=tEndIfabc=aEndFunction,例如,10.5VBA选择结构,形式3,If条件1Then语句组1ElseIf条件2Then语句组2ElseIf条件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选择结构,调用自定义函数计算,10.5VBA选择结构,4分情况选择语句SelectCase语句的语法结构如下。,SelectCase测试表达式Case表达式1语句组1Case表达式2语句组2CaseElse语句组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、ForNext循环结构,Forcounter=startToendstep步长循环语句1循环语句2循环语句3循环语句nNextcounter,10.6VBA循环结构,For循环案例某公司职工档案数据保存在Excel工作表中，如下图所示。该公司共有1234名职工，每月要从工资表中扣除一定的住房公积金，假设住房公积金按以下的百分比扣除。编写计算公积金比例的宏程序。,10.6VBA循环结构,Functioncountrate(salaryAsDouble)这个函数计算公积金的百分比DimrateAsDoubleIfsalary2000Thenrate=0.1ElseIfsalary1500Thenrate=0.07ElseIfsalary1200Thenrate=0.05ElseIfsalary1000Thenrate=0.02ElseIfsalary800Thenrate=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循环结构,2WhileWend循环结构在事先知道循环次数时，用For循环方便。如果事先并不知道循环次数，但知道执行或结束循环的条件，则用DoLoop循环或WhileWend循环。While循环的语法结构如下,While判定条件语句1语句2Wend,10.6VBA循环结构,3DoLoop循环结构,第一种结构为：DoWhile判定条件Loop,第二种结构为：DoLoopWhile判定条件,第三种结构为：DoUntil判定条件Loop,第四种结构为：DoLoopUntil判定条件,10.6VBA循环结构,案例在上节的For循环实例中，假设并不知道有多少位职工，在其他条件都相同的条件下，现用WhileWend循环结构来计算每位职工的住房公积金比例、应交公积金和实际发放工资。改写的函数如下,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=PercentCells(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若干个班，第125名同学进入A班，第2650名同学进入B班，其余的以此类推。该校每年招收的人数都有细小的差别。假设学生的英语测试成绩如下页图示。编写一个宏完成以下功能。自动统计出各分数段的人数，并将统计结果保存在一个新的工作表中。对所有的考试成绩进行排序。根据排名对学生进行分班，每个班25名学生，每个班保存在一个新的工作表中。,10.6VBA循环结构,成绩表本例程序代码较多，不便PPT演示，可直接查看Excel中的宏。,10.7模块与变量的作用域,VBA变量的作用域变量的作用域是指变量的有效范围。局部变量在VBA宏或函数内部定义的变量，只能在定义它的函数或宏范围内可用，这种变量称为局部变量。模块级变量如果一个变量在模块的开始部分定义，且不包含在任何宏或函数体内，则在本模块内的所有宏或函数中都可以使用它。全局变量在整个应用程序的各个模块之间都可共用的变量。,10.7模块与变量的作用域,2、变量的定义方式宏或函数的私有变量，可直接在宏或函数体内定义。在模块的声明段中使用Dim语句声明一个模块级变量。在模块的声明段中（或模块内），使用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=2k统计男教师的人数Fori=2ToN从数组的第2行开始分析数据Iftea(i,3)=男Then如果数组的第3列中的数据为“男”Forj=1To5Cells(k,j)=tea(i,j)将“男”教师的数据从数组读入工作表Nextjk=k+1EndIfNextiEndSub,10.7模块与变量的作用域,制定调用宏的工作表,10.8窗体及其应用,窗体的概念窗体是VBA的一种控件，可以用来显示Excel工作表中的数据，也可以用来设计用户输入数据的界面。窗体必须与宏程序结合使用，通过宏可以将窗体与Excel的工作表、自定义菜单、按钮或图形控件结合在一起。,10.8窗体及其应用,窗体设计案例为上例的教师管理工作薄建立窗体，该窗体可用于显示、修改及输入“教师总表”中的数据。窗体的样式如下图所示。,10.8窗体及其应用,2、建立窗体的版面（1）打开“教师管理宏”工作薄，进入其VBA编程环境。（2）选择VBA的“插入”|“用户窗体”，会见到图示的设计界面。,10.8窗体及其应用,窗体设计工具箱,这是绝大多数Wndows编程工具都支持的工具编程工具，一定要掌握其用法！,10.8窗体及其应用,向窗体添加工具箱中的控件工具,属性对话框中重要的设计工具，它标准而通用，通过它能够轻松地修改与设置窗体中各控件的大小、颜色、名字等。,在窗体中添加一标签控件，然后通过属性对话框修改该控件的背景色、标题内容和字体大小,10.8窗体及其应用,向窗体中添加标签、编辑框和命令按钮等控件，调整窗体大小和控件的位置，设置各控件的字体、字型、前景和背景等属性，如样图所示。,10.8窗体及其应用,3、为命令按钮编写事件程序双击要编写事件程序的控件，会见下示编程界面,1、选择要编写事件程序的对象,2、选择事件,3、输入程序代码,10.8窗体及其应用,部分窗体控件的事件程序窗体初始化事件程序,PrivateSubUserForm_Initialize()Worksheets(“教师总表”).Activate激活教师工作表n=2第1次显示工作表“教师总表”的第2行数据display调用显示宏，将工作表中的数据显示在窗体的文本框中EndSub,PrivateSub上一位_Click()Ifn2Thenn=n1工作表行号减1，Calldisplay将工作表的第n行显示在文本框中EndSub,单击“上一位”命令按钮时执行的程序代码,10.8窗体及其应用,Display过程代码,Subdisplay()TextBox1.Value=Cells(n,1)将工作表的第1列中的数据显示在TextBox1文本框中TextBox2.Value=Cells(n,2)TextBox3.Value=Cells(n,3)TextBox4.Value=Cells(n,4)TextBox5.Value=Cells(n,5)TextBox6.Value=Cells(n,6)TextBox7.Value=Cells(n,7)TextBox8.Value=Cells(n,8)EndSub,10.8窗体及其应用,单击退出按钮时执行的程序代码,PrivateSub退出_Click()teacher.HideEndSub,PrivateSub新教师_Click()WhileNot(IsEmpty(Cells(n,1)定位到工作表的最后一行n=n+1WenddisplayEndSub,单击“新教师”按钮时执行的程序代码,10.8窗体及其应用,建立调用窗体的控件,10.9对话框操作,1MsgBox函数功能MsgBox对话框函数可用于显示一些简单的信息，可以用该函数向用户显示一些提示信息。函数用法MsgBox(prompt,buttons,title,helpfile,context)其中，prompt是一个字符串，显示在对话框中的消息。buttons是一个常数，指定显示按钮的数目及形式，使用的图标样式。title表示对话框的标题。,10.9对话框操作,buttons参数的常数值,10.9对话框操作,对话框的图标常数,10.9对话框操作,MessageBox函数应用事例一,SubMsg1()MsgBox第一次使用对话框a=Sin(35)b=MsgBox(a,vbOKCancel)EndSub,运行结果,10.9对话框操作,MessageBox函数应用事例二,SubMsg2()DimMsg,Style,Title,Response,MyStringMsg=警告，系统有严重错误！+Chr(13)&Chr(10)_+想继续进行下面的操作?Style=vbYesNo+vbCritical定义按钮。同时显示按钮、图标Title=警告定义标题Response=MsgBox(Msg,Style,Title)调用MsgBox函数IfResponse=vbYesThen用户按下“是”MyString=Yes完成某操作Else用户按下“否”MyString=No完成某操作EndIfEndSub,10.9对话框操作,2InputBox函数功能InputBox函数可以接收用户的输入信息，而且可以将输入信息写入Excel工作表中。用法InputBox(prompt,title,default,xpos,ypos,helpfile,context)其中prompt是出现在输入对话框中的提示信息title是输入对话框的标题xpos，ypos指定输入文本框中InputBox中的坐标位置Helpfile、context指定该对话框的帮助信息。,10.9对话框操作,Input函数应用举例,Subss()Worksheets(Sheet1).ActivateDimMessage,Titl1,Default,MyValueMessage=请输入用户名设置提示信息Title=用户名设置标题Default=张三英设置默认值MyValue=InputBox(Message,Title,Default)用变量作参数调用InputBox函数pass=InputBox(请输入密码，密码)直接调用InputBox函数输入用户密码Ifpass=111111ThenCells(1,1)=用户名Cells(1,2)=密码Cells(2,1)=MyValueCells(2,2)=passElseMsgBox(密码不正确，你无权进入本系统)EndIfEndSub,10.9对话框操作,Ss函数的运行结果该函数运行时将显示下示两个对话框，将根据用户输入进行用户名与用户密码的判定，然后将结果写入工作表中。,10.10自定义菜单及自定义工具,1菜单简介菜单和工具栏可用于执行Excel命令。菜单显示了一系列命令。大多数菜单位于菜单栏中，菜单栏常在屏幕的顶部。在菜单中有各种不同的标记。,暂不可用的菜单,将弹出对话框的菜单,具有相级菜单,下面还有菜单项,10.10自定义菜单及自定义工具,2修改系统菜单Excel的菜单和工具栏可以随时修改系统菜单，如删除其中从不使用的菜单项，增加需要的菜单项，或者在系统菜单中添加自定义菜单项。修改方法是选择“工具”|“自定义”菜单项，在弹出下示对话框后，就可以进行各种修改了。,10.10自定义菜单及自定义工具,3在系统菜单中添加自定义菜单修改的方法参考教材,10.10自定义菜单及自定义工具,4用VBA创建自定义菜单认识菜单的各组成部分,10.10自定义菜单及自定义工具,建立VBA自定义菜单的步骤，以建立教师管理工作薄的菜单为例1）建立菜单栏MenuBars.Add教师管理2）在菜单栏中建立菜单MenuBars(教师管理).Menus.Add学生管理(&S)MenuBars(教师管理).Menus.Add教师管理(&T)MenuBars(教师管理).Menus.Add成绩管理(&M)MenuBars(教师管理).Menus.Add查询(&P)MenuBars(教师管理).Menus.Add学籍管理(&D)MenuBars(教师管理).Menus.Add系统维护(&U)MenuBars(教师管理).Menus.Add帮助系统(&H),10.10自定义菜单及自定义工具,3）建立菜单项，并指定各菜单项的运行宏设置“学生管理”中的每个菜单项MenuBars(教师管理).Menus(学生管理(&S).MenuItems.Add新生入学,xsMenuBars(教师管理).Menus(学生管理(&S).MenuItems.Add降级,jjMenuBars(教师管理).Menus(学生管理(&S).MenuItems.Add-MenuBars(教师管理).Menus(学生管理(&S).MenuItems.Add处分,cfMenuBars(教师管理).Menus(学生管理(&S).MenuItems.Add毕业,byMenuBars(教师管理).Menus(学生管理(&S).MenuItems.Add退出系统,quit“设置“教师管理”中的每个菜单项MenuBars(教师管理).Menus(教师管理(&T).MenuItems.Add男教师,manMenuBars(教师管理).Menus(教师管理(&T).MenuItems.Add女教师,womanMenuBars(教师管理).Menus(教师管理(&T).MenuItems.Add-MenuBars(教师管理).Menus(教师管理(&T).MenuItems.Add讲师,jsMenuBars(教师管理).Menus(教师管理(&T).MenuItems.Add教授,jsoMenuBars(教师管理).Men