ExcelVBA开发技术.ppt

Excel VBA开发技术,王洪涛：MSN:手机：,Excel VBA开发技术,课程安排第一课 开发入门（2学时）第二课 语言基础（2学时）第三课 常用技巧（2学时）第四课 实例开发（3学时）第五课 客户深度培训（3学时）,Excel VBA开发技术,VBA 开发的两个利器：1）帮助文件2）录制宏,Excel VBA开发技术,第一课 开发入门（2学时）目标：理解Excel VBA工作原理，掌握开发流程，培养对VBA的兴趣。,Excel VBA开发技术,VBA和Excel的关系VBA可以称作EXCEL的“遥控器”.VBA是什么？它是一种面向对象的自动化编程语言,它可以使常用的程序自动化,可以创建自定义的解决方案.此外,如果你愿意,还可以将EXCEL用做开发平台实现应用程序.,Excel VBA开发技术,VBA的优缺点1,VBA是一种通用程序语言,可以共享在Excel,Word,Access等应用程序。2，提供大量函数供用户使用，完成工作表函数所不能完成的动作和操作。3，必须以Excel,Word等应用程序为载体，无法编译成可执行文件，在一定程度上束缚了VBA的应用。,Excel VBA开发技术,VBA的主要功能 使重复的任务自动化.限制工作表某种数据的范围 对数据进行复杂的操作和分析.VBA和EXCEL的交互性1，对象的事件触发机制（文档VBA）2，代码对对象的精确控制（VBA文档）例1：工作表双击,Excel VBA开发技术,VBA工程VBA工程和Excel的对应关系实例分析一个最基本VBA工程VBA编辑器介绍VBA工程中文档对象模块的添加、移出、导出方法代码窗口和窗体设计窗口切换,Excel VBA开发技术,代码窗口的结构（对象列表框、过程列表框、程序分隔线、全模块视图和过程视图按钮）代码窗口的特征（自动调整关键字、属性和方法的大小写、自动显示成员列表、自动显示参数信息）添加代码注释的方法和必要性 或者Rem长代码语句换行的方法*一条语句可以多行书写，以空格加下划线 _ 来标识下行为续行；Sheets(1).Copy _Destination:=Sheets(2),Excel VBA开发技术,VBA代码的运行，测试VBA代码调试技巧建立一个HELLO WORLD的示例，演示开发流程,Excel VBA开发技术,从宏开始学习VBA什么是宏 宏是在Excel中可以重复执行的一系列操作。强大的录制宏功能是帮助学习VBA的好方法执行宏的方法宏快捷键将覆盖原设置(例002)演示:宏录制，执行（回放）的过程,Excel VBA开发技术,Excel文档结构介绍对象的概念:对象(object)是一些相关的变量和方法的软件集。Excel工作簿对象 workbookExcel工作表对象 worksheetExcel单元格对象 rangeExcel选择集对象(selection,range,cells之间的关系),Excel VBA开发技术,VBA中单元格区域的引用方式在使用VBA进行编程时，我们通常需要频繁地引用单元格区域，然后再使用相应的属性和方法对区域进行操作。所谓单元格区域，指的是单个的单元格、或者是由多个单元格组成的区域、或者是整行、整列等。,Excel VBA开发技术,单个单元格引用例:可以使用下面列举的任一方式对当前工作表中的单元格(D3)进行引用(1)Range(“d3”)(2)d3(不推荐)(3)Cells(3,4)(4)Cells(3,“d”)(5)Range(“d4”).Offset(-1)Range(“e3”).Offset(,-1)Range(“A1”).Offset(2,3)(6)若D3为当前单元格，则可使用：ActiveCell(8)Cells(4,4).Offset(-1)(例 单元格引用),Excel VBA开发技术,多单元格引用例:可以使用下面列举的任一方式对当前工作表中单元格区域B2:D6进行引用(1)Range(“B2:D6”)(2)Range(“B2”,“D6”)(3)Range(Cells(2,2),Cells(6,4)(4)Range(“B2”).Resize(5,3)(5)Range(“A1:C5”).Offset(1,1)(6)若单元格B2为当前单元格，则可使用语句：Range(ActiveCell,ActiveCell.Offset(4,2)(7)若单元格D6为当前单元格，则可使用语句：Range(“B2”,ActiveCell)不连续的引用Range(F23,E5:F6,J15:J17,J20),Excel VBA开发技术,引用当前工作表中活动单元格或指定单元格所在的区域(当前区域)例：(1)ActiveCell.CurrentRegion，表示活动单元格所在的当前区域。(2)Range(D5).CurrentRegion，表示单元格D5所在的当前区域。当前区域是指周围由空行或空列所围成的区域。具体地说，当前区域即活动单元格所在的矩形区域，该矩形区域的每一行和每一列中至少包含有一个数据，其周围是空行和空列,Excel VBA开发技术,Excel VBA开发技术,引用当前工作表中已使用的区域例如：(1)Activesheet.UsedRange，表示当前工作表中已使用的区域。(2)Worksheets(sheet1).UsedRange，表示工作表sheet1中已使用的区域。与CurrentRegion属性不同的是，该属性代表工作表中已使用的单元格区域，包括显示为空行，但已进行过格式的单元格区域。,Excel VBA开发技术,引用当前工作表中的整行或整列(1)Range(“C:C”).Select，表示选择C列 Range(“C:E”).Select，表示选择C列至E列。(2)Range(“1:1”).Select，表示选择第一行。Range(“1:3”).Select，表示选择第1行至第3行。(3)Range(“C:C”).EntireColumn，表示C列；Range(“D1”).EntireColumn，表示D列。同样的方式，也可以选择整行。,Excel VBA开发技术,使用变量实现对当前工作表中不确定单元格区域的引用有时，我们需要在代码中依次获取工作表中特定区域内的单元格，这通常可以采取下面的几种方式：(1)Range(“A”&i)(2)Range(“A”&i&“:C”&i)(3)Cells(i,1)(4)Cells(i,j)其中，i、j为变量，在循环语句中指定i和j的范围后，依次获取相应单元格。,Excel VBA开发技术,宏(vba工程)的数字签名安装数字签名(office 共享)首先要安装Office XP/2003里的“Office共享功能|VBA工程的数字签名”，然后在C:Program FilesMicrocoft OfficeOffice10里找到Selfcert.exe，运行，得到数字签名。VBE-工具-数字签名 如何使用数字签名使带宏的文档打开时不再提示是否启用宏的对话框,Excel VBA开发技术,使用Excel的终极目的-数据处理 VBA数据处理方法介绍在什么情况下我们选用VBA解决方案？,Excel VBA开发技术,了解控件的概念(可选)内置的窗体控件介绍第三方控件的介绍使用第三方控件会给我们带来什么便利实例演示用第三方控制件播放音乐的Excel文档,Excel VBA开发技术,总结课程要点VBA开发的一般流程单元格区域引用方法使用变量对单元格区域引用,Excel VBA开发技术,第二课 语言基础（2学时）目标：掌握与VBA有密切关系的Excel函数，掌握VBA常见函数，掌握常用语句，掌握程序的三种结构，熟悉过程、事件、对象、属性、方法、调用的概念，熟悉自定义函数的方法,Excel VBA开发技术,书写规范1）VBA不区分标识符的字母大小写，一律认为是小写字母；2）一行可以书写多条语句，各语句之间以冒号:分开；3）一条语句可以多行书写，以空格加下划线 _ 来标识下行为续行；4）标识符最好能简洁明了，不造成歧义。,Excel VBA开发技术,常量:系统常量:xlDown;vbYesNoCancel等自定义常量:用Const定义，且定义时赋值，程序中不能改变值，Const Pi=3.1415926 as integer,Excel VBA开发技术,变量:1)VBA允许使用未定义的变量，默认是变体变量Variant。2）在模块通用说明部份，加入 Option Explicit 语句可以强迫用户进行变量定义。3）变量定义语句及变量作用域 Dim 变量 as 类型 定义为局部变量，如 Dim xyz as integer Private 变量 as 类型 定义为私有变量，如 Private xyz as byte Public 变量 as 类型 定义为公有变量，如 Public xyz as singleGlobal 变量 as 类型 定义为全局变量，如 Globlal xyz as date Static 变量 as 类型 定义为静态变量，如 Static xyz as double 一般变量作用域的原则是，那部份定义就在那部份起作用，模块中定义则在该模块那作用。,Excel VBA开发技术,数组:是包含相同数据类型的一组变量的集合，对数组中的单个变量引用通过数组索引下标进行。在内存中表现为一个连续的内存块，必须用Global或Dim语句来事先定义。定义规则如下：Dim 数组名(成员数目)as type;如dim temp(10)as string。二维数组是按行列排列，如XYZ(行，列)。除了以上固定数组外，VBA还有一种功能强大的动态数组，定义时无大小维数声明；在程序中再利用Redim语句来重新改变数组大小，原来数组内容可以通过加preserve关键字来保留。如下例：Dim array1()as double:Redim array1(5):array1(3)=250:Redim preserve array1(5,10),Excel VBA开发技术,数据类型把握文本型、日期型、数字型等之间的区别,Excel VBA开发技术,运算符:是代表VB某种运算功能的符号。赋值运算符=数学运算符&、+(字符连接符)、+(加)、-（减）、Mod（取余）、（整除）、*（乘）、/（除）、-（负号）、（指数）逻辑运算符Not（非）、And（与）、Or（或）、Xor（异或）、Eqv（相等）关系运算符=（相同）、（不等）、（大于）、=（不小于）、=（不大于）、Like(字符串比较)、Is(对象比较)字符串运算符(&),Excel VBA开发技术,常用的Excel工作表函数:可以使用Application.WorksheetFunction调用SUM,SUMIF,COUNT,COUNTIF,SUMPRODOUCT,MAX,MINDATE,NOW,TODAY,WEEKDAYINT,MOD,RAND,ROUNDCOLUMN,COLUMNS,ROW,ROWS,MATCH,VLOOKUPASC,CHAR,EXACT,FIND,SEARCH,SUBSTITE,REPLACE,LEFT,RIGHT,MID,TEXT,VALUE,TRIMAND,OR,NOT,IFISBLANK,ISERR,ISERROR,ISTEXT,ISNONTEXT,ISNUMBER,Excel VBA开发技术,常用的VBA函数测试函数:IsNumeric,IsDate,IsEmpty,IsError,IsNull数学函数:Int,Fix,Sgn,Rnd字符串函数:Trim,Ltrim,Rtrim,Len,Left,Right,Mid,Ucase,Lcase,Asc,Chr转换函数:CCur,CDate,Cint,CStr,Str,Val日期函数:Now,Date,Datediff,Second,Minute,Hour,Day,Month,Year,Weekday其他：IIf,Excel VBA开发技术,一、测试函数IsNumeric(x)是否为数字,返回Boolean结果，True or FalseIsDate(x)是否是日期,返回Boolean结果，True or FalseIsEmpty（x）是否为Empty,返回Boolean结果，True or FalseIsArray(x)指出变量是否为一个数组。IsError(expression)指出表达式是否为一个错误值IsNull(expression)指出表达式是否不包含任何有效数据(Null)。IsObject(identifier)指出标识符是否表示对象变量,Excel VBA开发技术,二数学函数Sin(X)、Cos(X)、Tan(X)、Atan(x)三角函数，单位为弧度Log(x)返回x的自然对数Exp(x)返回 exAbs(x)返回绝对值Int(number)、Fix(number)都返回参数的整数部分，区别：Int 将-8.4 转换成-9，而 Fix 将-8.4 转换成-8Sgn(number)返回一个 Variant(Integer)，指出参数的正负号Sqr(number)返回一个 Double，指定参数的平方根VarType(varname)返回一个 Integer，指出变量的子类型Rnd（x）返回0-1之间的单精度数据，x为随机种子,Excel VBA开发技术,三字符串函数Trim(string)去掉string左右两端空白Ltrim(string)去掉string左端空白Rtrim(string)去掉string右端空白Len(string)计算string长度Left(string,x)取string左段x个字符组成的字符串Right(string,x)取string右段x个字符组成的字符串Mid(string,start,x)取string从start位开始的x个字符组成的字符串Ucase(string)转换为大写Lcase(string)转换为小写Space(x)返回x个空白的字符串Asc(string)返回一个 integer，代表字符串中首字母的字符代码Chr(charcode)返回 string,其中包含有与指定的字符代码相关的字符,Excel VBA开发技术,四转换函数CBool(expression)转换为Boolean型CByte(expression)转换为Byte型CCur(expression)转换为Currency型CDate(expression)转换为Date型CDbl(expression)转换为Double型CDec(expression)转换为Decemal型CInt(expression)转换为Integer型CLng(expression)转换为Long型CSng(expression)转换为Single型CStr(expression)转换为String型CVar(expression)转换为Variant型Val(string)转换为数据型Str(number)转换为String,Excel VBA开发技术,五时间函数 Now 返回一个 Variant(Date)，根据计算机系统设置的日期和时间来指定日期和时间。Date 返回包含系统日期的 Variant(Date)。Time 返回一个指明当前系统时间的 Variant(Date)。Timer 返回一个 Single，代表从午夜开始到现在经过的秒数。TimeSerial(hour,minute,second)返回一个 Variant(Date)，包含具有具体时、分、秒的时间。DateDiff(interval,date1,date2,firstdayofweek,firstweekofyear)返回 Variant(Long)的值，表示两个指定日期间的时间间隔数目Second(time)返回一个 Variant(Integer)，其值为 0 到 59 之间的整数，表示一分钟之中的某个秒Minute(time)返回一个 Variant(Integer)，其值为 0 到 59 之间的整数，表示一小时中的某分钟Hour(time)返回一个 Variant(Integer)，其值为 0 到 23 之间的整数，表示一天之中的某一钟点Day(date)返回一个 Variant(Integer)，其值为 1 到 31 之间的整数，表示一个月中的某一日Month(date)返回一个 Variant(Integer)，其值为 1 到 12 之间的整数，表示一年中的某月Year(date)返回 Variant(Integer)，包含表示年份的整数。Weekday(date,firstdayofweek)返回一个 Variant(Integer)，包含一个整数，代表某个日期是星期几,Excel VBA开发技术,自定义过程 Sub 过程名(参数列表)过程体End sub,Excel VBA开发技术,过程程序:通过代码完成一个任务.它和事件程序得主要区别是它没有明确得服务对象,但可以被其他程序调用。可分为以下两种：子程序过程：运行后不能返回值函数过程程序：运行后可以返回值事件过程：当某一特定事件发生时才执行得程序，如选取单元格，双击单元格等都可作为独立的事件。事件程序有指定的对象。,Excel VBA开发技术,VBA语句赋值语句错误跳转语句 on error goto,发生错误时转移 on error resume next,忽略出错语句With语句需要设置一个对象的多个属性值时使用，避免对象名重复多次。With 对象。属性1 属性值1。属性2 属性值2End with,Excel VBA开发技术,程序的三种结构顺序结构分支结构if thenelseelseifend ifselect case循环结构for nextdo while loop,Excel VBA开发技术,对象的事件 工作薄 打开；关闭；改变等等工作表 激活；改变；选择改变,Excel VBA开发技术,对象的属性、方法,Excel VBA开发技术,调用过程，子过程调用自定义函数,Excel VBA开发技术,课程要点重点函数的熟练应用程序的三种结构,Excel VBA开发技术,第三课 常用技巧（2学时）目标：掌握VBA控制工作簿，工作表，单元格的方法，熟悉对话框控制，熟悉用户窗体的设计方法，了解窗口，菜单和工具栏的定制。,Excel VBA开发技术,工作簿的控制(workbooks)新建工作簿打开已有的工作簿判断一个工作簿是否已打开保存工作簿另存工作簿另存工作簿并加上密码关闭工作簿并保存工作簿常见事件及应用,Excel VBA开发技术,工作表的控制(worksheet)插入多个工作表获得工作表的名称，为工作表命名插入工作表前判断同名工作表是否已存在选择工作表：可以选择多个工作表激活工作表:只能激活一个工作表隐藏工作表,Excel VBA开发技术,复制工作表移动工作表获得工作表的个数，sheets集合和worksheets集合的区别Sheets集合代表当前工作簿中的所有工作表,包括图表工作表、对话框工作表和宏表。Worksheets集合仅代表当前工作簿中的所有工作表。获得当前工作表的序列号工作表的保护删除工作表，屏蔽删除确认对话框的方法工作表行，列控制：隐藏，行高、列宽控制Range(a1).EntireRow.Hidden=True Range(“a1”).EntireRow.RowHeight 20,Excel VBA开发技术,单元格输入输出禁止编辑部分单元格单元格的引用方式单元格删除（移动方向控制，整行整列删除）单元格信息清除（全部，格式，内容，批注）,Excel VBA开发技术,对话框控制Msgbox对话框介绍Msgbox标题、提示及按钮的定制程序响应Msgbox按钮的动作input对话框内置文件对话框为保证程序运行的流畅性，进行必要的对话框屏蔽,Excel VBA开发技术,用户窗体设计窗体的基本构成及控制（标题栏，名称，背景色等）窗体常用事件（打开，关闭）控件工具箱介绍控件共用属性（名称，尺寸，位置，文本提示，是否可操作，是否可见）控件共用事件（获得焦点，失去焦点，鼠标点击、按下、松开、经过事件）常用控件（标签，文本框，密码框，按钮，列表框，组合框，选项，图象控件）使用第三方控件,Excel VBA开发技术,窗口、菜单及工具栏控制窗口对象结构分析控制窗口（更改程序标题，更改状态栏，菜单栏、工具栏的隐藏，滚动条的隐藏，工作表标签隐藏）菜单栏、工具栏对象的介绍屏蔽菜单栏、工具栏的方法屏蔽右键菜单屏蔽快捷键菜单栏、工具栏的自定义自定义菜单栏、工具栏需要注意在文档关闭后及时恢复,Excel VBA开发技术,课程要点各种技术的综合协调应用,Excel VBA开发技术,第四课 实例开发（3学时）目标：掌握完整VBA程序的开发全过程，独立开发中小型办公系统，并用VBA来解决日常工作中遇到的重复操作以及需要智能化处理的问题。,Excel VBA开发技术,完整实例：施工成本核算表功能分析设计数据源表的字段设计统计表用VBA规范输入，控制输入表格用VBA自动连续打印一个月内的每日成本汇总表用VBA自动生成某一类支出的柱状图,Excel VBA开发技术,实例拓展介绍本实例标准开发流程介绍程序开发需求至上的原则理解数据处理的重点是标准化输入和多样化输出挖掘数据统计的深层价值，理解数据分析对决策的指导意义,Excel VBA开发技术,思考题本例数据只有支出部分，请在本例程基础上开发可以处理收支双向的表格,Excel VBA开发技术,第五课 客户深度培训（3学时）目标：站在客户的立场，根据客户需求设计并实施完全个性化培训内容，帮助客户解决日常工作中遇到的问题并提供在今后工作中解决问题的思路和方法。,