2013-08-09-Excel-编程入门

HelloWork!ExcelVBA编程入门太平洋寿险北分信息技术部1•我是熟练的Excel使用者。•我能运用大部分Excel公式。•但是感觉有些事情还是太麻烦了！！•超复杂的公式！•重复的劳动！•所以我在想有没有“正确的办法”来处理？•现在我已经准备好接受点挑战了！本节课程的预期听众2为了•进入新知识领域。•掌握一种思维方式。•了解计算机的运行原理。•真正“驯服”计算机。•建立与IT人的沟通平台。你将能够•开发出自己的函数来！•制作自己的常用工具！你将从本课程上学到的3•VBA：VisualBasicforApplication•用Basic语言为微软的Office软件开发扩展功能的一种技术。•学会了VBA，你可以为Excel，Word，PowerPoint，Access等办公软件开发你要的新功能。啥叫VBA•激活“开发工具”菜单•Office2007–左上角的office按钮Excel选项–常用在功能区显示“开发工具”选项卡。•Office2013–左上角的“文件”菜单选项–自定义功能区自定义功能区，勾选“开发工具”开启Excel的VBA编程功能5认识一下VBA的开发环境6项目区。每一个Excel文件都是一个项目。属性区。当前选中的对象的属性显示与调整程序代码编写区域。工具栏带着问题出发吧！7HelloWorld!所有编程书里的第一个程序。在Excel的A1格里写下“HelloWorld”.SubHello()setws=ThisWorkbook.ActiveSheetws.Cells(1,1).Value=HelloWorld!EndSub•需求：输入一个数值，得到它的绝对值。接受第一个真正的挑战！求出绝对值——自己的abs函数9•认识一下流程图吧！•圆角矩形：开始或结束•方框：一个处理过程•菱形：判断画个解决思路的流程图10输出负nN0吗？输入一个数n输出n结束开始YesNoFunctionMyABS(NAsDouble)AsDoubleIfN0ThenMyABS=NExitFunctionElseMyABS=-NExitFunctionEndIfEndFunction把流程图翻成程序11输出负nN0吗？输入一个数n输出n结束开始YesNo运行我们自定义函数！——好幸福！！12需求：输入一个数值n，计算1+2+3+……n等多少？稍微复杂一点点的挑战哦！计算阶加——循环处理13画个解决思路的流程图14输入一个N开始准备一个存放结果的变量S，初始为0计数器从1到N开始循环准备一个计数器变量I，初始为1每次把I值累加进S变量里循环结束end返回S做为结果FunctionDect(NAsInteger)AsIntegerDimsAsIntegers=0ForI=1ToNStep1s=s+INextDect=sEndFunction把流程图翻译成程序15输入一个N开始准备一个存放结果的变量S，初始为0计数器从1到N开始循环准备一个计数器变量I，初始为1每次把I值累加进S变量里循环结束end返回S做为结果看看运行成果，好有成就感呀！运行成果16•变量是什么？•和代数里的变量是似类的。就像是一个有名字的盒子。里面可以放入任何东西，比如一个数据，一串文字。•程序利用盒子的名字来索引使用内容。•命名•1）字母打头，由字母、数字和下划线组成，如A987b_23Abc•2）字符长度小于40，（Excel2002以上中文版等，可以用汉字且长度可达254个字符）•3）不能与VB保留字重名，如public,private,dim,goto,next,with,integer,single等现在我们会使用变量了数据类型•数据类型类型标识符字节•字符串型String$字符长度(0-65400)•字节型Byte无1•布尔型Boolean无2•整数型Integer%2•长整数型Long&4•单精度型Single!4•双精度型Double#8•日期型Date无8公元100/1/1-9999/12/31•货币型Currency@8•小数点型Decimal无14•变体型Variant无以上任意类型，可变•对象型Object无417•ForEach…Next•枚举范围内的每个值进行循环•ForEachrInrange1……Next•Dowhile…Loop•当条件成立时循环•DoWhileI100•……•Loop•Dountil…Loop•循环到条件成立为止•DoUntilI=100•……•Loop还有几种循环写法Do…Loop{while|until}•先至少走一次，在结束时判断Do……LoopwhileI100while…wend•与dowhile功能相同WhilenotI=100……wend18•需求：•输入一个基数金额，以及晚交的天数。•处罚规则：–晚1天罚10%–晚2到5天30%–晚6到10天60%–晚10天以上100%•计算出应该交的罚金。分天罚款！——条件分支19FunctionGetFine(cAmountAsCurrency,iDaysAsInteger)AsCurrencySelectCaseiDaysCase1GetFine=cAmount*0.1Case2To5GetFine=cAmount*0.3Case6To10GetFine=cAmount*0.6CaseElseGetFine=cAmountEndSelectEndFunction代码20两个输入参数呢！•Choose函数•Choose(索引号，待选值1，待选值2,……)•例如：r=Choose(iDays,0.1,0.3,0.3,0.3,0.3,0.6,0.6,0.6,0.6,0.6)If(IsNull(r))ThenGetFine=cAmountElseGetFine=cAmount*rEndIf•Switch函数•switch(判断1，值1，判断2，值2，……)•例如r=Switch(iDays=1,0.1,_iDays=2AndiDays=5,0.3,_iDays=6AndiDays=10,0.6)If(IsNull(r))ThenGetFine=cAmountElseGetFine=r*cAmount条件分支也有几种写法21需求：给一个生日，计算给定的一个日期时是几周岁。思考题：计算周岁22FunctionCalcAge(dtBirthdayAsDate,dtCalcDateAsDate)AsIntegerDimiYearAsIntegeriYear=Year(dtCalcDate)-Year(dtBirthday)IfMonth(dtCalcDate)Month(dtBirthday)Then‘如比生日的月份小，年份减一。iYear=iYear-1ElseIfMonth(dtCalcDate)=Month(dtBirthday)Then'如比与生日的月分相同IfDay(dtCalcDate)Day(dtBirthday)Then'如比生日的日子小，年份减一。iYear=iYear-1EndIfEndIfEndIfCalcAge=iYearEndFunction答案23——网上找来的代码FunctionIsIDOK(IDNumberAsString)AsBooleanDimSumM&DimstrTemp$IsIDOK=False'将英文转换成大写IDNumber=UCase(Trim(IDNumber))'先看前N位，不是数字就退出IfNotIsNumeric(Left(IDNumber,17))ThenExitFunction''再看前6位，不在数据库退出。数据库仅作参考'IfSheet2.Range(A:A).Find(Left(IDNumber,6),lookat:=xlWhole)IsNothingThenExitFunction'分别对待15位和18位,SelectCaseLen(IDNumber)'15位看日期Case15strTemp=Mid(IDNumber,7,6)IfstrTemp=Format(DateSerial(Left(strTemp,2),Mid(strTemp,3,2),Right(strTemp,2)),yymmdd)ThenIsIDOK=TrueExitFunctionEndIf'18位看校验码Case18'看校验码是否符合范围IfNotIDNumberLike*[0-9X]ThenExitFunction'看日期strTemp=Mid(IDNumber,7,8)IfstrTempFormat(DateSerial(Left(strTemp,4),Mid(strTemp,5,2),Right(strTemp,2)),yyyymmdd)ThenExitFunctionSumM=0'看校验码是否正确Fori=1To17D=CInt(Mid(IDNumber,i,1))SelectCasei'根据位数不同，求和Case1,11SumM=SumM+D*7Case2,12SumM=SumM+D*9Case3,13SumM=SumM+D*10Case4,14SumM=SumM+D*5Case5,15SumM=SumM+D*8Case6,16SumM=SumM+D*4Case7,17SumM=SumM+D*2Case8SumM=SumM+D*1Case9SumM=SumM+D*6Case10SumM=SumM+D*3EndSelectNextdc=Mid(10X98765432,(SumMMod11)+1,1)IfRight(IDNumber,1)=dcThenIsIDOK=TrueExitFunctionEndIfCaseElseIsIDOK=FalseExitFunctionEndSelectEndFunction检查身份证是否合法？24•把代码复制到自己的工作表代码模块里，你就可以在表格里使用这个函数了！•有时也需要稍微改动一下，以便于符合需要。现在你知道如何使用网络代码了吧！25需求：•设置一个录入模板，以便于输入数据。•对身份证、手机等进行检查，结果填到后面列上。•身份证正确时，从身份证号里提取客户的生日、性别，填在对应的列中。通过此示例，掌握如何直接处理工作表里的数据。检查表里的数据——宏的开发26基础概念•函数只能处理数据、返回数据。•要进入一项操作性工作，需要用宏，即子程序。•宏（子程序）就是一段可以多次调用的程序。•在Excel里，宏可以通过定义快捷键、菜单、工具栏按钮的方法来调用。•宏不必有返回值。格式Sub宏名(参数as类型,…)……EndSub宏（子程序）的概念27啥叫对象？•对象就是一个东西。•这个东西一般属于某个种类。–（如张三是人类的一员）•对象有各种属性–（如姓名、性别、年龄、身高、体重）•对象可以做事儿–（学名“方法”。如吃、喝、上班）利用Excel对象系统来操作数据28Workbook类：•指工作簿，即Excel文件。对象•ThisWorkBook对象:程序所在的工作簿。•ActiveWorkBook对象：目前正被激活的工作簿。•WorkBooks集合:所有打开的工作簿。属性•Name工作簿名称。•Sheets集合：文件中的所有工作表。•Charts集合：所有的图表。方法•Activate(激活)，Close(关闭)•Save,SaveAs(保存)Excel常用类型与对象29WorkSheet类：•指一个工作表。对象•ThisWorkSheet对象:程序所在的工作表。•ActiveWorkSheet对象：目前正被激活的工作表。•WorkSheets集合:当前工作簿里的所有工作表。属性•Name,Cells(所有单元格),Columns(所有列),Rows(所有行),Range(范围)……方法•Activate(激活),Copy,Delete,PrintOut,PrintPreviewExcel常用类型与对象30Range类：•表示工作表（不仅仅是当前正在使用