资源描述
第五章VBA编程基础,一、VBA的基本概念VBA-VisualBasicforApplication是微软Office集成办公软件的内置编程语言，它是新一代标准宏语言。VBA寄生于Office应用程序,是Office的重要组件。,利用VBA可以实现如下功能：使重复的任务自动化将Office作为开发平台，进行应用软件开发对数据进行复杂的分析和操作,5.1VBA概述,VBA与VB的主要区别：,VBA是基于VisualBasicforWindows发展而来VBA是VisualBasic的子集，VBA与VB在结构上十分相似，VBAVB。,VB用于创建标准的应用程序，VBA是使已有的应用程序(Office)自动化。VB具有自己的开发环境，VBA寄生于已有的应用程序(Office)。VB开发出的应用程序可以是可执行文件(exe文件)，VBA开发的程序必须依赖于它的父应用程序(Office)。,5.1VBA概述,二、利用宏来编写VBA程序宏(Macro)是一组VBA语句，可以理解为一个程序段，或一个子程序。在MSOffice2003中，宏可以直接编写也可以通过录制形成。,5.1VBA概述,录制宏：将一系列操作过程记录下来并由系统自动转换为VBA语句,这是目前最简单的编程方法。,1.宏安全性宏病毒：利用宏来传播和感染的一种计算机病毒。为了防止这种病毒，Office软件提供一种安全保护机制。宏安全性：工具/宏/安全性,5.1VBA概述,2.宏的录制与保存录制宏有两种方法：启用宏录制器来录制宏在VB编辑器中输入代码创建宏,5.1VBA概述,例题5-1录制一个简单的宏，其功能是将当前单元格背景置成蓝色。,操作步骤启动Excel,选定一个单元格工具/宏/录制新宏输入宏名填充颜色,单击确定按钮格式工具栏中选择填充颜色按钮蓝色单击停止录制按钮,结束宏录制过程,宏的执行：选择一个单元格，选择工具/宏/宏，在对话框中选择填充颜色，单击执行按钮。,5.1VBA概述,3.宏代码的分析与编辑工具/宏/宏填充颜色编辑VBA编辑器窗口。,Sub填充颜色()填充颜色Macro宏由wzq录制，时间:2011-5-23WithSelection.Interior.ColorIndex=5.Pattern=xlSolidEndWithEndSub,5.1VBA概述,代码分析,5.1VBA概述,代码编辑：,Sub填充颜色()Selection.Interior.ColorIndex=5EndSub,Sub填充颜色()WithSelection.Interior.ColorIndex=5EndWithEndSub,Sub填充颜色()Range(A5).SelectSelection.Interior.ColorIndex=5EndSub,5.1VBA概述,Sub填充颜色()Range(A1).SelectSelection.Interior.ColorIndex=1Range(A2).SelectSelection.Interior.ColorIndex=2Range(A3).SelectSelection.Interior.ColorIndex=3Range(A4).SelectSelection.Interior.ColorIndex=4Range(A5).SelectSelection.Interior.ColorIndex=5Range(A6).SelectSelection.Interior.ColorIndex=6Range(A7).SelectSelection.Interior.ColorIndex=7EndSub,5.1VBA概述,Sub填充颜色()Fork=1To7c=A&kRange(c).SelectSelection.Interior.ColorIndex=kNextEndSub,5.1VBA概述,循环控制语句ForNext的语法形式：For循环变量=初值To终值Step步长Next循环变量,功能：循环语句执行时，首先给循环变量置初值，如果循环变量的值没有超过终值，则执行循环体，到Next时把步长加到循环变量中，若没有超过终值再循环，直到循环变量的值超过终值时才结束循环。,循环嵌套：可以将一个ForNext循环放置在另一个ForNext循环中，每个循环中要使用不同的循环变量名。,5.1VBA概述,例如：ForI=1To9ForJ=1To9NextJNextI,许多过程可录制宏来完成，但录制的宏不具备判断或循环功能，人机交互能力差。宏的录制、编辑、运行等VisualBasic编辑器,三、执行宏的其他方式工具/宏/宏选择宏名执行给宏指定快捷键执行快捷键1.用按钮执行宏窗体工具栏单击所需控件画出一矩形自动显示指定宏对话框选择所需宏的名称确定将控件标题直接改为宏的名称,5.1VBA概述,2.用工具栏按钮执行宏工具/自定义自定义对话框命令选项卡类别列表框/宏命令列表框将自定义按钮拖到任意一个工具栏上右击该按钮/指定宏选择所需宏名单击关闭按钮关闭自定义对话框,5.1VBA概述,若要从工具栏中删除自定义按钮，选择工具/自定义显示自定义对话框，用鼠标将按钮从工具栏中拖出。,例题5-2编制一个简单的VBA控制台程序，程序运行后输出“Hello,World!”,Sub第1个VBA程序()MsgBoxHello,World!,vbOKOnly,VBA控制台程序EndSub,操作步骤启动Excel,工具/宏/宏输入宏名第1个VBA程序,单击创建按钮模块编辑窗口,5.1VBA概述,录制宏编写简单的VBA程序。通过对简单程序的分析，了解一些编程的基本概念和几条语句的功能。进一步开发Excel的功能,编写各种满足用户需要的程序。掌握VBA的语法、变量数据类型、运算符,5.2变量和运算符,一、变量和数据类型1.变量变量：用于临时保存数据。,5.2变量和运算符,例题5-3在宏代码中使用变量。带姓名的Hello程序，VisualBasic编辑器SubHello()s_name=InputBox(请输入您的名字：)MsgBoxHello,&s_name&！EndSub,2.变量的数据类型变量的数据类型决定变量允许保存何种类型的数据。,5.2变量和运算符,5.2变量和运算符,进行程序设计时，应选择占据存储空间尽量小的变量类型来保存所需要的数据。但选择变量类型时留有一定余地也是必要的。,3.声明变量变量在使用前，最好进行声明，即定义变量的数据类型，这样可以提高程序的可读性和节省存储空间。若变量不经声明直接使用，系统会自动将变量定义为Variant类型。,5.2变量和运算符,通常使用Dim语句来声明变量，如DimstrNameAsString(本过程使用)PublicstrNameAsString(本工程所有过程使用),变量命名规则：变量名必须以字母开始并且只能包含字母、数字和某些特定的字符最大长度为255个字符。,5.2变量和运算符,可以在一个语句中声明几个变量，如DimintXAsInteger,intYAsInteger,intZAsInteger变量intX,intY,intZ被声明为Integer类型,DimintX,intY,intZAsInteger变量intX和intY被声明为Variant，intZ被声明为Integer,4.常量变量用来存储动态数据，静态数据可用常量来表示，如圆周率用PI表示。要声明常量并设置常量的值，需要使用Const语句，如ConstPIAsSingle=3.1415926,5.2变量和运算符,常量通常用全大写字母以区别于变量。常量声明后，就不能对它赋予新值。,二、运算符,5.2变量和运算符,运算符优先级别(由高到低)：括号指数一元减乘法和除法整除求余加法和减法连接比较逻辑(Not,And,Or),例如：196=319mod6=132=9,5.2变量和运算符,又如：A=10B=8C=6设置变量初值MyCheck=ABAndBC返回TrueMyCheck=Not(AB)返回False,再如：MyStr=“Hello”&”World”返回HwlloWorldMyStr=“Check”&123返回Check123MyNumber=“34”+6返回40MyNumber=“34”+”6”返回346,例题5-4百钱买百鸡问题。假设公鸡每只5元，母鸡每只3元，小鸡3只1元。要求用100元钱买100只鸡，问公鸡、母鸡和小鸡可各买多少只？,5.2变量和运算符,分析：设公鸡数、母鸡数和小鸡数分别为x,y,z,则可列出下列方程组。,这里有3个未知数，2个方程式，说明有多个解。可以用穷举法。,编程：工具/宏/宏宏对话框输入宏名“百钱百鸡”创建按钮进入VBA编辑环境。,5.2变量和运算符,Sub百钱百鸡()Forx=0To19Fory=0To33z=100-x-yIf5*x+3*y+z/3=100Theng=g&公鸡&x&,母鸡&y&,小鸡&z&Chr(10)EndIfNextyNextxMsgBox(g)EndSub,Chr(10)函数：将ASCII码10转换为控制字符-回车符。,分支语句ifthen的语法形式：ifthenelseendif,5.2变量和运算符,功能：条件是一个关系表达式或逻辑表达式，若其值为真，则执行then后面的语句组；若条件的值为假，执行else后面的语句组，然后继续执行下一个语句。,分支语句ifthen的另一语法形式：ifthenelseifthenelseendif,5.2变量和运算符,若条件1表达式为真，则执行紧接在then后面的语句组1；若条件1表达式为假，则检测下一个elseif或执行else后面的语句组n，然后继续执行下一个语句。,VBA是面向对象的编程语言和开发工具,编程时常用到对象、属性、事件和方法。一、对象对象：客观世界中的任何实体。,5.3面向对象程序设计,从软件的角度看，对象是将数据和操作过程结合在一起的数据结构，或是具有属性和方法的集合体。属性：用来表示对象的状态；方法：描述对象行为的过程。,Windows：窗口、菜单、文本框、按钮对象对象有大小，可容纳其他对象容器对象,对象是VBA程序的基础，几乎所有操作都与对象有关。Excel的工作簿、工作表和单元格都是对象。VBA中绝大多数对象具有可视性，即有能看得见的直观属性，如大小、颜色和位置等。,5.3面向对象程序设计,二、属性属性：对象的性质，如大小、位置、颜色和字体等。,5.3面向对象程序设计,每个对象都有若干个属性，每个属性都有一个预先设置的默认值，多数不需修改，只有少数属性要修改。同一对象应用在不同的地方，要修改的属性也不同。,对于属性的修改，可拖动鼠标改变其位置和大小，也可在属性窗口中修改，还可在程序中进行修改。,例如，给Sheet1工作表的A8单元格填充蓝色。Sheets(Sheet1).Range(A8).Interior.ColorIndex=5,三、事件事件：由系统预先定义并由系统或用户发出的动作。,5.3面向对象程序设计,事件可由系统引发，比如对象生成时，系统就引发一个Initialize事件；事件也可由用户引发，比如按钮被单击，对象被拖动、被改变大小，都会引发相应的事件。,事件的行为是由相应的代码决定的。当软件运行时某一事件发生，对应的代码就被执行，完成相应的动作若事件不发生，这段代码就不会被执行。,四、方法方法：对象可以执行的动作。通常方法带有参数，以限定执行动作的方式。,5.3面向对象程序设计,例如，打印活动工作表的1-2页1份。ActiveWindow.SelectedSheets.PrintOut1,2,1,面向对象程序设计就是要设计一个个对象，将这些对象用某种方式联系起来构成系统，即软件系统。每个对象需要设计属性，并针对需要的事件编写程序代码，也就是使用系统提供的语句、命令、函数事件和方法等。,例题5-5在Excel中实现定时提醒。在Office的Application对象中有个方法叫OnTime，用来触发一个程序在特定时刻运行。特定的时刻可以是某个日期的某个时间，也可以是相对于某个时刻的时间值。,5.3面向对象程序设计,操作过程：在Excel中选择工具/宏/宏命令，在宏对话框中输入宏名ds，然后单击创建按钮，进行VBA编辑环境。,5.3面向对象程序设计,Subds()Application.OnTimeTimeValue(21:00:00),my_msgEndSubSubmy_msg()MsgBox现在是21点,您有个约会!,vbInformation,提醒EndSub,一、工程/模块/过程每个VBA应用程序都存在一个工程中,而工程可分为若干个对象、窗体和模块等。例如，录制宏时，若原来不存在模块，Office就自动创建一个模块。,5.4过程,VBA编辑环境：工具/宏/VisualBasic编辑器，Alt+F11工程任务窗格：视图/工程资源管理器标准工具栏工程资源管理器按钮添加项目：插入用户窗体、模块、类模块双击项目：查看或编辑程序代码,VBA编辑器中的工程和代码界面：,5.4过程,模块：定义若干个过程，过程可以是子程序、函数和属性等过程。,5.4过程,子程序过程：只执行一个或多个操作，而不返回数值。函数过程：通常要返回一个值属性过程：由一系列语句组成用来为窗体、标准模块以及类模块创建属性。宏录制方法：得到子程序过程而得不到函数或属性过程。,二、过程的创建每个过程都可以完成特定的任务，利用过程可将复杂任务细分为若干个简单部分，使软件模块化，便于维护和管理。,5.4过程,过程创建方法一：直接输入代码。打开要编写过程的模块；输入Sub、Function或Property，分别创建子程序过程、函数过程或属性过程，系统会自动加上EndSub、EndFunction或EndProperty语句；在其中输入过程的代码。,过程创建方法二：用插入过程对话框。打开要编写过程的模块；插入/过程添加过程对话框。,5.4过程,例题5-6创建一个显示消息框的过程。,5.4过程,工具/宏/VisualBasic编辑器打开VBA编辑窗口在工具栏上单击工程资源管理器按钮，VBA编辑器左侧可以看到工程窗格插入/模块将一个模块添加到工程中插入/过程添加过程对话框输入过程名称“显示消息框”，选择子程序在代码窗口中直接输入或修改过程,PublicSub显示消息框()MsgBox这是一个测试用的过程EndSub,例题5-7在Excel工作表中设计一个表格，用不同方法测试给定的年份是闰年还是平年,5.5应用示例,1.用VBA函数的实现方法进入VBA编辑环境,在当前工程中插入一个模块，编写自定义函数rn，代码是：,5.5应用示例,Functionrn(n)rn=平年IfnMod400=0Thenrn=闰年IfnMod4=0AndnMod1000Thenrn=闰年EndFunction,自定义函数的形式参数n为待测试的年份；条件:若n能被400整除，函数的返回值为闰年；若n能被4整除但不能被100整除，则返回闰年。,2.用公式一实现的方法,5.5应用示例,A2能被400整除：MOD(A2,400)=0A2能被4整数：MOD(A2,4)=0A2不能被100整数：MOD(A2,100)100,=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)0),MOD(A2,400)=0),闰年,平年),规则：对于一个年份值,若它能被400整除，或者能被4整除但不能被100整除，就是闰年，否则为平年,3.用公式二实现的方法,5.5应用示例,=IF(MONTH(DATE(A2,2,29)=2,闰年,平年),DATE(A2,2,29)=2闰年DATE(A2,2,29)=3平年,例题5-8由身份证号求性别、生日、年龄和地址等信息。,5.5应用示例,1.身份证号与户口所在地对照表,5.5应用示例,A-代码B-行政区名将A列的数据转换为文本型选中A列数据/分列菜单文本分列向导对话框2次单击下一步步骤之3选择文本/完成,2.工作表界面设计,5.5应用示例,身份证号列单元格格式要设置数字作为文本处理输入若干个姓名和身份证号增加表格的标题,3.使用Excel函数由身份证号求性别：,5.5应用示例,性别标识位编码规则：18位身份证号的倒数第2位性别标识位的数值为奇数-男性，偶数-女性,MOD函数：求出性别标志除以2的余数余数为1-男，余数为0-女,由身份证号求出生日期：,5.5应用示例,在身份证中，从第7位开始的8位数字代表出生的年月日,MID函数：取出B3单元格中第7位开始的8位数字TEXT函数：将数值转换为特定格式的文本,由身份证号求当前年龄：,5.5应用示例,YEAR(NOW()函数：求出系统的当前年份MID函数：直接返回身份证号中4位出生年份值,由身份证号求户口所在地：,5.5应用示例,插入/名称/定义定义名称对话框/设置引用位置,XX0000-省/直辖市XXXX00-地级市XXXXXX-县/县级市(区),F3得到每个身份证号对应的地址；VLOOKUP函数：在代码对照表的A和B列分别查找名称ideids/idl的值，返回B列对应的行政区名，再将它们拼接成一个字符串。,4.使用VBA自定义函数由身份证号求性别：,5.5应用示例,自定义函数：形参id为身份证号返回值为该居民的性别信息。Val(string)函数：将数值字符串转换成数值。性别标志位：18位倒数第2位奇数-男偶数-女,由身份证号求出生日期：,5.5应用示例,自定义函数：形参id为身份证号，返回值为该居民的出生日期。Trim函数：删除字符串首部和尾部的空格。Mid函数：取出对应的出生年月日的位置。,由身份证号求年龄：,5.5应用示例,函数的形参id为身份证号，返回值为该居民的当前年龄18位由系统日期的年份减去出生年份得到当前年龄,由身份证号求户口所在地：,5.5应用示例,函数的形参id为身份证号，返回值为对应的户口所在地变量id2/id4/id6省、直辖市/地级市/县级市、区Find方法：查找省、地级市和县的编号，将对应的行政区名拼接在一起，形成一个字符串返回。,调用自定义函数：C3=sex(B3)D3=bd(B3)E3=age(B3)F3=addr(B3),5.5应用示例,练习题：累加和阶乘输入学生成绩输出等级求最大公约数求圆的周长和面积,5-59,本章小结,1.宏安全性2.宏的录制(顺序结构)3.变量、运算符及其优先级别4.分支语句ifthen及流程图5.循环语句fornext及流程图6.对象、属性、事件、方法7.工程、模块、过程及类型8.典型示例(润年、身份证号信息),
展开阅读全文