ExcelVBA开发技术.ppt

ExcelVBA开发技术,王洪涛：whtMSN:wht1314手机ExcelVBA开发技术,课程安排第一课开发入门（2学时）第二课语言基础（2学时）第三课常用技巧（2学时）第四课实例开发（3学时）第五课客户深度培训（3学时）,ExcelVBA开发技术,VBA开发的两个利器：1）帮助文件2）录制宏,ExcelVBA开发技术,第一课开发入门（2学时）目标：理解ExcelVBA工作原理，掌握开发流程，培养对VBA的兴趣。,ExcelVBA开发技术,VBA和Excel的关系VBA可以称作EXCEL的“遥控器”.VBA是什么？它是一种面向对象的自动化编程语言,它可以使常用的程序自动化,可以创建自定义的解决方案.此外,如果你愿意,还可以将EXCEL用做开发平台实现应用程序.,ExcelVBA开发技术,VBA的优缺点1,VBA是一种通用程序语言,可以共享在Excel,Word,Access等应用程序。2，提供大量函数供用户使用，完成工作表函数所不能完成的动作和操作。3，必须以Excel,Word等应用程序为载体，无法编译成可执行文件，在一定程度上束缚了VBA的应用。,ExcelVBA开发技术,VBA的主要功能使重复的任务自动化.限制工作表某种数据的范围对数据进行复杂的操作和分析.VBA和EXCEL的交互性1，对象的事件触发机制（文档VBA）2，代码对对象的精确控制（VBA文档）例1：工作表双击,ExcelVBA开发技术,VBA工程VBA工程和Excel的对应关系实例分析一个最基本VBA工程VBA编辑器介绍VBA工程中文档对象模块的添加、移出、导出方法代码窗口和窗体设计窗口切换,ExcelVBA开发技术,代码窗口的结构（对象列表框、过程列表框、程序分隔线、全模块视图和过程视图按钮）代码窗口的特征（自动调整关键字、属性和方法的大小写、自动显示成员列表、自动显示参数信息）添加代码注释的方法和必要性或者Rem长代码语句换行的方法*一条语句可以多行书写，以空格加下划线_来标识下行为续行；Sheets(1).Copy_Destination:=Sheets(2),ExcelVBA开发技术,VBA代码的运行，测试VBA代码调试技巧建立一个HELLOWORLD的示例，演示开发流程,ExcelVBA开发技术,从宏开始学习VBA什么是宏宏是在Excel中可以重复执行的一系列操作。强大的录制宏功能是帮助学习VBA的好方法执行宏的方法宏快捷键将覆盖原设置(例002)演示:宏录制，执行（回放）的过程,ExcelVBA开发技术,Excel文档结构介绍对象的概念:对象(object)是一些相关的变量和方法的软件集。Excel工作簿对象workbookExcel工作表对象worksheetExcel单元格对象rangeExcel选择集对象(selection,range,cells之间的关系),ExcelVBA开发技术,VBA中单元格区域的引用方式在使用VBA进行编程时，我们通常需要频繁地引用单元格区域，然后再使用相应的属性和方法对区域进行操作。所谓单元格区域，指的是单个的单元格、或者是由多个单元格组成的区域、或者是整行、整列等。,ExcelVBA开发技术,单个单元格引用例:可以使用下面列举的任一方式对当前工作表中的单元格(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)(例单元格引用),ExcelVBA开发技术,多单元格引用例:可以使用下面列举的任一方式对当前工作表中单元格区域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),ExcelVBA开发技术,引用当前工作表中活动单元格或指定单元格所在的区域(当前区域)例：(1)ActiveCell.CurrentRegion，表示活动单元格所在的当前区域。(2)Range(D5).CurrentRegion，表示单元格D5所在的当前区域。当前区域是指周围由空行或空列所围成的区域。具体地说，当前区域即活动单元格所在的矩形区域，该矩形区域的每一行和每一列中至少包含有一个数据，其周围是空行和空列,ExcelVBA开发技术,ExcelVBA开发技术,引用当前工作表中已使用的区域例如：(1)Activesheet.UsedRange，表示当前工作表中已使用的区域。(2)Worksheets(sheet1).UsedRange，表示工作表sheet1中已使用的区域。与CurrentRegion属性不同的是，该属性代表工作表中已使用的单元格区域，包括显示为空行，但已进行过格式的单元格区域。,ExcelVBA开发技术,引用当前工作表中的整行或整列(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列。同样的方式，也可以选择整行。,ExcelVBA开发技术,使用变量实现对当前工作表中不确定单元格区域的引用有时，我们需要在代码中依次获取工作表中特定区域内的单元格，这通常可以采取下面的几种方式：(1)Range(“A”vbYesNoCancel等自定义常量:用Const定义，且定义时赋值，程序中不能改变值，ConstPi=3.1415926asinteger,ExcelVBA开发技术,变量:1)VBA允许使用未定义的变量，默认是变体变量Variant。2）在模块通用说明部份，加入OptionExplicit语句可以强迫用户进行变量定义。3）变量定义语句及变量作用域Dim变量as类型定义为局部变量，如DimxyzasintegerPrivate变量as类型定义为私有变量，如PrivatexyzasbytePublic变量as类型定义为公有变量，如PublicxyzassingleGlobal变量as类型定义为全局变量，如GloblalxyzasdateStatic变量as类型定义为静态变量，如Staticxyzasdouble一般变量作用域的原则是，那部份定义就在那部份起作用，模块中定义则在该模块那作用。,ExcelVBA开发技术,数组:是包含相同数据类型的一组变量的集合，对数组中的单个变量引用通过数组索引下标进行。在内存中表现为一个连续的内存块，必须用Global或Dim语句来事先定义。定义规则如下：Dim数组名(成员数目)astype;如dimtemp(10)asstring。二维数组是按行列排列，如XYZ(行，列)。除了以上固定数组外，VBA还有一种功能强大的动态数组，定义时无大小维数声明；在程序中再利用Redim语句来重新改变数组大小，原来数组内容可以通过加preserve关键字来保留。如下例：Dimarray1()asdouble:Redimarray1(5):array1(3)=250:Redimpreservearray1(5,10),ExcelVBA开发技术,数据类型把握文本型、日期型、数字型等之间的区别,ExcelVBA开发技术,运算符:是代表VB某种运算功能的符号。赋值运算符=数学运算符&、+(字符连接符)、+(加)、-（减）、Mod（取余）、（整除）、*（乘）、/（除）、-（负号）、（指数）逻辑运算符Not（非）、And（与）、Or（或）、Xor（异或）、Eqv（相等）关系运算符=（相同）、（不等）、（大于）、=（不小于）、=（不大于）、Like(字符串比较)、Is(对象比较)字符串运算符(&),ExcelVBA开发技术,常用的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,ExcelVBA开发技术,常用的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,ExcelVBA开发技术,一、测试函数IsNumeric(x)是否为数字,返回Boolean结果，TrueorFalseIsDate(x)是否是日期,返回Boolean结果，TrueorFalseIsEmpty（x）是否为Empty,返回Boolean结果，TrueorFalseIsArray(x)指出变量是否为一个数组。IsError(expression)指出表达式是否为一个错误值IsNull(expression)指出表达式是否不包含任何有效数据(Null)。IsObject(identifier)指出标识符是否表示对象变量,ExcelVBA开发技术,二数学函数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为随机种子,ExcelVBA开发技术,三字符串函数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,其中包含有与指定的字符代码相关的字符,ExcelVBA开发技术,四转换函数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,ExcelVBA开发技术,五时间函数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)，包含一个整数，代表某个日期是星期几,ExcelVBA开发技术,自定义过程Sub过程名(参数列表)过程体Endsub,ExcelVBA开发技术,过程程序:通过代码完成一个任务.它和事件程序得主要区别是它没有明确得服务对象,但可以被其他程序调用。可分为以下两种：子程序过程：运行后不能返回值函数过程程序：运行后可以返回值事件过程：当某一特定事件发生时才执行得程序，如选取单元格，双击单元格等都可作为独立的事件。事件程序有指定的对象。,ExcelVBA开发技术,VBA语句赋值语句错误跳转语句onerrorgoto,发生错误时转移onerrorresumenext,忽略出错语句With语句需要设置一个对象的多个属性值时使用，避免对象名重复多次。With对象。属性1属性值1。属性2属性值2Endwith,ExcelVBA开发技术,程序的三种结构顺序结构分支结构ifthenelseelseifendifselectcase循环结构fornextdowhileloop,ExcelVBA开发技术,对象的事件工作薄打开；关闭；改变等等工作表激活；改变；选择改变,ExcelVBA开发技术,对象的属性、方法,ExcelVBA开发技术,调用过程，子过程调用自定义函数,ExcelVBA开发技术,课程要点重点函数的熟练应用程序的三种结构,ExcelVBA开发技术,第三课常用技巧（2学时）目标：掌握VBA控制工作簿，工作表，单元格的方法，熟悉对话框控制，熟悉用户窗体的设计方法，了解窗口，菜单和工具栏的定制。,ExcelVBA开发技术,工作簿的控制(workbooks)新建工作簿打开已有的工作簿判断一个工作簿是否已打开保存工作簿另存工作簿另存工作簿并加上密码关闭工作簿并保存工作簿常见事件及应用,ExcelVBA开发技术,工作表的控制(worksheet)插入多个工作表获得工作表的名称，为工作表命名插入工作表前判断同名工作表是否已存在选择工作表：可以选择多个工作表激活工作表:只能激活一个工作表隐藏工作表,ExcelVBA开发技术,复制工作表移动工作表获得工作表的个数，sheets集合和worksheets集合的区别Sheets集合代表当前工作簿中的所有工作表,包括图表工作表、对话框工作表和宏表。Worksheets集合仅代表当前工作簿中的所有工作表。获得当前工作表的序列号工作表的保护删除工作表，屏蔽删除确认对话框的方法工作表行，列控制：隐藏，行高、列宽控制Range(a1).EntireRow.Hidden=TrueRange(“a1”).EntireRow.RowHeight20,ExcelVBA开发技术,单元格输入输出禁止编辑部分单元格单元格的引用方式单元格删除（移动方向控制，整行整列删除）单元格信息清除（全部，格式，内容，批注）,ExcelVBA开发技术,对话框控制Msgbox对话框介绍Msgbox标题、提示及按钮的定制程序响应Msgbo