资源描述
第五章 VBA编程基础,一、VBA的基本概念 VBA-Visual Basic for Application是微软Office集成办公软件的内置编程语言，它是新一代标准宏语言。 VBA寄生于Office应用程序,是Office的重要组件。,5.1 VBA概述,VBA与VB的主要区别：,VBA是基于Visual Basic for Windows发展而来VBA是Visual Basic的子集，VBA与VB在结构上十分相似，VBAVB。,VB用于创建标准的应用程序，VBA是使已有的应用程序(Office)自动化。 VB具有自己的开发环境，VBA寄生于已有的应用程序(Office)。 VB开发出的应用程序可以是可执行文件(exe文件)，VBA开发的程序必须依赖于它的父应用程序(Office)。,5.1 VBA概述,二、利用宏来编写VBA程序 宏(Macro)是一组VBA语句，可以理解为一个程序段，或一个子程序。 在MS Office 2003中，宏可以直接编写也可以通过录制形成。,5.1 VBA概述,录制宏：将一系列操作过程记录下来并由系统自动转换为VBA语句,这是目前最简单的编程方法。,1.宏安全性 宏病毒：利用宏来传播和感染的一种计算机病毒。为了防止这种病毒，Office软件提供一种安全保护机制。 宏安全性：工具/宏/安全性,5.1 VBA概述,2.宏的录制与保存 录制宏有两种方法： 启用宏录制器来录制宏 在VB编辑器中输入代码创建宏,5.1 VBA概述,例题5-1录制一个简单的宏，其功能是将当前单元格背景置成蓝色。,操作步骤 启动Excel,选定一个单元格 工具/宏/录制新宏 输入宏名填充颜色,单击确定按钮 格式工具栏中选择填充颜色按钮蓝色 单击停止录制按钮,结束宏录制过程,宏的执行：选择一个单元格，选择工具/宏/宏，在对话框中选择填充颜色，单击执行按钮。,5.1 VBA概述,3.宏代码的分析与编辑 工具/宏/宏填充颜色编辑VBA编辑器窗口。,Sub 填充颜色() 填充颜色 Macro 宏由wzq录制，时间: 2011-5-23 With Selection.Interior .ColorIndex = 5 .Pattern = xlSolid End With End Sub,5.1 VBA概述,代码分析,5.1 VBA概述,代码编辑：,Sub 填充颜色() Selection.Interior.ColorIndex = 5 End Sub,Sub 填充颜色() With Selection.Interior .ColorIndex = 5 End With End Sub,Sub 填充颜色() Range(“A5“).Select Selection.Interior.ColorIndex = 5 End Sub,5.1 VBA概述,Sub 填充颜色() Range(“A1“).Select Selection.Interior.ColorIndex = 1 Range(“A2“).Select Selection.Interior.ColorIndex = 2 Range(“A3“).Select Selection.Interior.ColorIndex = 3 Range(“A4“).Select Selection.Interior.ColorIndex = 4 Range(“A5“).Select Selection.Interior.ColorIndex = 5 Range(“A6“).Select Selection.Interior.ColorIndex = 6 Range(“A7“).Select Selection.Interior.ColorIndex = 7 End Sub,5.1 VBA概述,Sub 填充颜色() For k = 1 To 7 c = “A“ & k Range(c).Select Selection.Interior .ColorIndex = k Next End Sub,5.1 VBA概述,循环控制语句ForNext的语法形式： For 循环变量=初值 To 终值 Step 步长 Next 循环变量,功能：循环语句执行时，首先给循环变量置初值，如果循环变量的值没有超过终值，则执行循环体，到Next时把步长加到循环变量中，若没有超过终值再循环，直到循环变量的值超过终值时才结束循环。,循环嵌套：可以将一个ForNext循环放置在另一个ForNext循环中，每个循环中要使用不同的循环变量名。,5.1 VBA概述,例如：For I=1 To 9 For J=1 To 9 Next J Next I,许多过程可录制宏来完成，但录制的宏不具备判断或循环功能，人机交互能力差。 宏的录制、编辑、运行等Visual Basic编辑器,三、执行宏的其他方式 工具/宏/宏选择宏名执行 给宏指定快捷键执行快捷键 1.用按钮执行宏 窗体工具栏单击所需控件 画出一矩形自动显示指定宏对话框 选择所需宏的名称确定 将控件标题直接改为宏的名称,5.1 VBA概述,2.用工具栏按钮执行宏 工具/自定义自定义对话框 命令选项卡类别列表框/宏 命令列表框将自定义按钮拖到任意一个工具栏上 右击该按钮/指定宏选择所需宏名 单击关闭按钮关闭自定义对话框,5.1 VBA概述,若要从工具栏中删除自定义按钮，选择工具/自定义显示自定义对话框，用鼠标将按钮从工具栏中拖出。,例题5-2编制一个简单的VBA控制台程序，程序运行后输出“Hello, World!”,Sub 第1个VBA程序() MsgBox “Hello, World!“, vbOKOnly, “VBA控制台程序“ End Sub,操作步骤 启动Excel,工具/宏/宏 输入宏名第1个VBA程序,单击创建按钮 模块编辑窗口,5.1 VBA概述,录制宏编写简单的VBA程序。 通过对简单程序的分析，了解一些编程的基本概念和几条语句的功能。 进一步开发Excel的功能,编写各种满足用户需要的程序。 掌握VBA的语法、变量 数据类型、运算符,5.2 变量和运算符,一、变量和数据类型 1.变量 变量：用于临时保存数据。,5.2 变量和运算符,例题5-3在宏代码中使用变量。 带姓名的Hello程序，Visual Basic编辑器 Sub Hello() s_name = InputBox(“请输入您的名字：“) MsgBox “Hello,“ & s_name & “！“ End Sub,2.变量的数据类型 变量的数据类型决定变量允许保存何种类型的数据。,5.2 变量和运算符,5.2 变量和运算符,进行程序设计时，应选择占据存储空间尽量小的变量类型来保存所需要的数据。但选择变量类型时留有一定余地也是必要的。,3.声明变量 变量在使用前，最好进行声明，即定义变量的数据类型，这样可以提高程序的可读性和节省存储空间。 若变量不经声明直接使用，系统会自动将变量定义为Variant类型。,5.2 变量和运算符,通常使用Dim语句来声明变量，如 Dim strName As String (本过程使用) Public strName As String (本工程所有过程使用),变量命名规则：变量名必须以字母开始并且只能包含字母、数字和某些特定的字符最大长度为255个字符。,5.2 变量和运算符,可以在一个语句中声明几个变量，如 Dim intX As Integer, intY As Integer, intZ As Integer 变量intX,intY,intZ被声明为Integer类型,Dim intX,intY,intZ As Integer 变量intX和intY被声明为Variant，intZ被声明为Integer,4.常量 变量用来存储动态数据，静态数据可用常量来表示，如圆周率用PI表示。 要声明常量并设置常量的值，需要使用Const语句，如 Const PI As Single=3.1415926,5.2 变量和运算符,常量通常用全大写字母以区别于变量。常量声明后，就不能对它赋予新值。,二、运算符,5.2 变量和运算符,运算符优先级别(由高到低)： 括号指数一元减乘法和除法整除求余加法和减法连接比较逻辑(Not,And,Or),例如：196=3 19 mod 6=1 32=9,5.2 变量和运算符,又如：A=10 B=8 C=6 设置变量初值 MyCheck=AB And BC 返回True MyCheck=Not(AB) 返回False,再如：MyStr=“Hello”&”World” 返回Hwllo World MyStr=“Check”& 123 返回Check 123 MyNumber=“34”+6 返回40 MyNumber=“34”+”6” 返回346,例题5-4百钱买百鸡问题。 假设公鸡每只5元，母鸡每只3元，小鸡3只1元。要求用100元钱买100只鸡，问公鸡、母鸡和小鸡可各买多少只？,5.2 变量和运算符,分析：设公鸡数、母鸡数和小鸡数分别为x,y,z, 则可列出下列方程组。,这里有3个未知数，2个方程式，说明有多个解。可以用穷举法。,编程：工具/宏/宏宏对话框输入宏名“百钱百鸡”创建按钮进入VBA编辑环境。,5.2 变量和运算符,Sub 百钱百鸡() For x = 0 To 19 For y = 0 To 33 z = 100 - x - y If 5 * x + 3 * y + z / 3 = 100 Then g = g & “公鸡“ & x & “,母鸡“ & y & “,小鸡“ & z & Chr(10) End If Next y Next x MsgBox (g) End Sub,Chr(10)函数：将ASCII码10转换为控制字符-回车符。,分支语句ifthen的语法形式： if then else end if,5.2 变量和运算符,功能：条件是一个关系表达式或逻辑表达式，若其值为真，则执行then后面的语句组；若条件的值为假，执行else后面的语句组，然后继续执行下一个语句。,分支语句ifthen的另一语法形式： if then elseif then else end if,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.PrintOut 1,2,1,面向对象程序设计就是要设计一个个对象，将这些对象用某种方式联系起来构成系统，即软件系统。 每个对象需要设计属性，并针对需要的事件编写程序代码，也就是使用系统提供的语句、命令、函数事件和方法等。,例题5-5在Excel中实现定时提醒。 在Office的Application对象中有个方法叫OnTime，用来触发一个程序在特定时刻运行。特定的时刻可以是某个日期的某个时间，也可以是相对于某个时刻的时间值。,5.3 面向对象程序设计,操作过程： 在Excel中选择工具/宏/宏命令， 在宏对话框中输入宏名ds，然后单击创建按钮，进行VBA编辑环境。,5.3 面向对象程序设计,Sub ds() Application.OnTime TimeValue(“21:00:00“), “my_msg“ End Sub Sub my_msg() MsgBox “现在是21点, 您有个约会!“, vbInformation, “提醒“ End Sub,一、工程/模块/过程 每个VBA应用程序都存在一个工程中,而工程可分为若干个对象、窗体和模块等。 例如，录制宏时，若原来不存在模块，Office就自动创建一个模块。,5.4 过程,VBA编辑环境：工具/宏/Visual Basic编辑器，Alt+F11 工程任务窗格：视图/工程资源管理器 标准工具栏工程资源管理器按钮 添加项目：插入用户窗体、模块、类模块 双击项目：查看或编辑程序代码,VBA编辑器中的工程和代码界面：,5.4 过程,模块：定义若干个过程，过程可以是子程序、函数和属性等过程。,5.4 过程,子程序过程：只执行一个或多个操作，而不返回数值。 函数过程：通常要返回一个值 属性过程：由一系列语句组成用来为窗体、标准模块以及类模块创建属性。 宏录制方法：得到子程序过程而得不到函数或属性过程。,二、过程的创建 每个过程都可以完成特定的任务，利用过程可将复杂任务细分为若干个简单部分，使软件模块化，便于维护和管理。,5.4 过程,过程创建方法一：直接输入代码。 打开要编写过程的模块； 输入Sub、Function或Property，分别创建子程序过程、函数过程或属性过程，系统会自动加上End Sub、End Function或End Property语句； 在其中输入过程的代码。,过程创建方法二：用插入过程对话框。 打开要编写过程的模块； 插入/过程添加过程对话框。,5.4 过程,例题5-6创建一个显示消息框的过程。,5.4 过程,工具/宏/Visual Basic编辑器打开VBA编辑窗口 在工具栏上单击工程资源管理器按钮，VBA编辑器左侧可以看到工程窗格 插入/模块将一个模块添加到工程中 插入/过程添加过程对话框 输入过程名称“显示消息框”，选择子程序 在代码窗口中直接输入或修改过程,Public Sub 显示消息框() MsgBox “这是一个测试用的过程“ End Sub,例题5-7在Excel工作表中设计一个表格，用不同方法测试给定的年份是闰年还是平年,5.5 应用示例,1.用VBA函数的实现方法 进入VBA编辑环境,在当前工程中插入一个模块，编写自定义函数rn，代码是：,5.5 应用示例,Function rn(n) rn = “平年“ If n Mod 400 = 0 Then rn = “闰年“ If n Mod 4 = 0 And n Mod 100 0 Then rn = “闰年“ End Function,自定义函数的形式参数n为待测试的年份； 条件:若n能被400整除，函数的返回值为闰年； 若n能被4整除但不能被100整除，则返回闰年。,2.用公式一实现的方法,5.5 应用示例,A2能被400整除：MOD(A2,400)=0 A2能被4整数：MOD(A2,4)=0 A2不能被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列分别查找名称ide ids/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.典型示例(润年、身份证号信息),
展开阅读全文