Excel宏与VBA解析

前言若想使那些枯燥反复的工作变得高效、准确而自动化，请认真学习本章的内容。若想建立自已的办公自动化数据管理系统，请认真学习本章的内容。若想成为一个真正的Excel专家，不但要学习本章的内容，而且还不够！本章学习目标1、了解Excel宏的基本知识2、掌握宏的录制、编写和运行方法3、了解VBA程序的基本知识4、掌握VBA的基本数据类型5、掌握VBA宏与函数的编写和调用方法6、掌握VBA条件、循环程序的设计方法7、掌握VBA窗体的设计方法8、掌握VBA的对话框和菜单程序的设计方法9、了解用VBA和EXCEL相结合开发应用程序的方法10.1Excel宏1.宏的概念宏是用户用VBA程序设计语言编写或录制的程序，其中保存有一系列Excel的命令，可以被多次重复使用。宏可以自动执行复杂的任务，减少完成任务所需的步骤。VBA即VisualBasicforApplications，它是VisualBasic的一个派生体，它有针对性地对VisualBasic进行了优化和设置。两者的主要区别在于：VisualBasic开发的应用程序可以独立在Windows系统中运行，而用VBA开发的程序只能在提供它的应用程序中运行。在Excel中，可以用Excel提供的宏录制工具录制宏程序，也可以使用它提供的“VisualBasic编辑器”直接编写宏。10.1Excel宏2、录制宏宏录制器是Excel提供的一种软件工具，它能够将用户的操作过程记录下来，并自动将所记录的操作转换成为VBA程序代码。对于经常重复进行的操作过程，可以通过宏录制器将它记录下来，当需要再次进行这些操作时，只需要运行录制的宏，Excel就能自动完成这些重复的操作。说明：当录制宏的工作开始后，所有的操作步骤都将被记录在宏中，所以应尽量减少不必要的或错误的操作，如果在录制宏时出现失误，更正失误的操作也会记录在宏中。10.1Excel宏录制宏的案例建立10班的学生档案表，档案表的结构如下图所示。录制一个能够建立这种档案表结构的宏。10.1Excel宏建立学生档案宏1.选择“工具”|“宏”|“录制新宏”菜单项，在弹出的下示对话框中输入宏名字“学生档案”2.单击“确定”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'学生档案Macro3'宏由dk录制，时间:2004-7-184'快捷键:Ctrl+s5Range(A1).Select6ActiveCell.FormulaR1C1=2001级一班学生档案7Range(A2).Select8ActiveCell.FormulaR1C1=学号9Range(B2).Select10ActiveCell.FormulaR1C1=姓名11Range(C2).Select12ActiveCell.FormulaR1C1=班级13Range(D2).Select14ActiveCell.FormulaR1C1=性别15Range(E2).Select16ActiveCell.FormulaR1C1=籍贯17Range(F2).Select18ActiveCell.FormulaR1C1=寝室10.1Excel宏19Range(G2).Select20ActiveCell.FormulaR1C1=电话号码21Range(A1:G1).Select22WithSelection23.HorizontalAlignment=xlCenter24.VerticalAlignment=xlBottom25.WrapText=False26.Orientation=027.AddIndent=False28.ShrinkToFit=False29EndWith30Selection.Merge31EndSub10.1Excel宏5、宏的结构Sub宏名()命令代码1命令代码2……EndSub6、With的结构WithSelection……EndWith10.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+b5A=3A在本程序段中有3个不同的值，这就是变！10.2VBA程序设计基础VBA数值数据类型数据类型存储空间数据范围Byte1字节0~255Boolean2字节True或falseInteger2字节-32768~32767Long4字节-2147483648~2147483647Single4字节负数：-3.402823E38~-1.401298E-45正数：1.401298E-45~3.402823E38Double8字节负数：1.79769313486232E308~-4.94065645841247E-324正数：4.94065645841247E-324~1.79769313486232E308Currency8字节-922337203685477.5808~922337203685477.5807Date8字节0100年1月1日到9999年12月31日Object4字节任何对象引用String字符长度分为定长和可变长度两种，可变长可达0～20亿个字符，定长最多65536个字符Variant(数字)16字节任何数字，最大达到double的数值范围Variant(字符)22+字符串长度约0～20亿10.2VBA程序设计基础字符串类型在VBA中有两种类型的字符串，变长与定长的字符串。Dims1AsString//变长类型Dims2AsString*10//定长类型s1=dddkdks2=d1234567890sssss10.2VBA程序设计基础布尔类型布尔类型是比较运算或逻辑运算的结果值，它只有两个取值：True和False。True是比较结果为真时的值，False是比较结果为假时的值。DimAAsBooleanDimBAsBooleanA=35B=True10.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数组访问方法7810.2VBA程序设计基础对象、属性和方法计算机程序设计中的对象是从现实世界中抽象出来的，它与现实世界中的对象具有相同的含义。对象具有属性和方法两种特性。年龄：30学历：硕士身高：173cm体重：69公斤说自己的年龄学习授课……李立属性方法对象属性语法规则李立.年龄=32李立.体重=70方法的调用也要按这种语法规则李立.学习李立.授课Excel对象示例10.3子程序1.子程序的两种结构子程序是VBA的最小程序单位，它必须独立存在，但在一个子程序中可以调用另外一个子程序。它有两种形式，第一种没有参数，第二种有参数Sub子程序名……子程序代码……EndSubSub子程序名(p1,p2,p3…)……子程序代码……EndSub10.3子程序2、子程序的调用形式1）直接调用直接调用子程序名，如果有参数，则在子程序后面直接写上调用参数；2）用Call命令调用在Call命令的后面写上了程序的名字，如果子程序有参数，则必须将参数写在括号中。10.3子程序子程序调用举例1SubMain()2HouseCalc99800,431003CallHouseCalc(380950,49500)4CircleArea(4)5CircleArea46Message7EndSubSubHouseCalc(priceAsSingle,wageAsSingle)If3*wage=0.85*priceThenMsgBox你的薪水不能承担房价！ElseMsgBox你的薪金足以承担房价！EndIfEndSubSubCircleArea(RasSingle)MsgBox3.14*R*REndsubSubMessage()MsgBox这是一个无参子程序EndSub调用10.4自定义函数1函数结构Function函数名([p1,p2,p3,…])[AsType]……函数代码……函数名=表达式EndFunction10.4自定义函数2、定义函数的注意事项①函数由Function和EndFunction语句所包含起来的VBA语句。②Function函数和Sub子程序很类似，但函数有一个返回值。Function函数必须通过表达式调用。③如果一个Function函数没有参数，它的Function语句必须包含一个空的圆括号。④在函数体中，函数名至少被赋值一次。⑤函数开头行的[AsType]用于指定函数值的返回值类型，如果省掉该定义，被视为Variant类型。10.4自定义函数3、【例10-1】编写一个计算圆面积的简单函数，圆半径作为函数参数。FunctionCircleArea(rAsSingle)AsSingleCircleArea=3.14*r*rEndFu