认识VBA及其应用-课件

第24章VBA1ppt课件24-1认识VBA及其应用VisualBasicforApplication

又称VBA，是一种程序语言，可随着不同的应用软件透过对应的语法来设计，从Office2000开始，Office间应用软件的VBA即可互通了。VBA应用很广，小则应用它来设计一个Excel没提供的函数，大则可以设计成帮不会用Excel界面的人做成自动化的信息系统，如会计信息系统等，本书此章节的目的，是要让使用者对Excel的VBA有一初步概念。1ppt课件24-2VBA程序语言基本结构VBA是一种物件导向程序语言，跟一般语言一样，其基本架构为Sub函数或程序名称()‘定义(宣告)函数或程序 宣告1

宣告2 .....

陈述式1

陈述式2 .....EndSub1ppt课件VBA程序语言基本结构-续VBA的程序码是由许多陈述式集合而成，包含宣告其实也是一种陈述式，陈述式会执行VBA的动作，每一个正确的指令，都会产生对应的动作，例如引发物件启动、计算或结束程序等。陈述式约分为4类：(1)宣告陈述式(2)指定陈述式及设定物件属性(3)条件及循环陈述式(4)执行物件的方法。刚开始时，我们可藉由前一章所录的巨集产生的VBA成式码来学习各种用法，对初学者效果不错哦！先看一个例子感受一下！下面的Sub程序用Min工作表函数来求出指定储存格范围中的最小值。首先，将变量myRange

宣告为Range物件，然后将其设定为Sheet1中的A1:C10范围。再将另一变量answer指定为将Min函数套用于myRange

后的结果。最后在讯息方块中显示answer的值。SubUseFunction() DimmyRangeAsRange SetmyRange=Worksheets("Sheet1").Range("A1:C10") answer=Application.WorksheetFunction.Min(myRange) MsgBoxanswerEndSub1ppt课件VBA操作环境Excel提供了一个很好的VBA操作环境，来让我们写程序，我们现在就来看看！Step1：执行[工具/巨集/VisualBasic编辑器]或按「Alt+F11」快速键。1ppt课件VBA操作环境-续Step2：进入VBA操作环境视窗后，会看到左上方的「项目视窗」，左下的「属性视窗」，以及右边的「程序视窗」，这时你可以透过「视窗工作列」切换到Excel环境或再切换到VBA环境，也可以透过检视把其他工具列叫出来。1ppt课件VBA操作环境-续Step3：在[项目视窗]上，选取项目名称(预设为VBProject)，按右键，叫出快显功能表，选择此项目的属性。1ppt课件VBA操作环境-续Step4：开启[项目属性]交谈窗，将项目名称改成「star」(原为VBProjecct)，也可以用密码保护我们的项目，不让别人看到项目内容，如程序。1ppt课件VBA操作环境-续Step5：VBA的物件导向式语法，有非常多的方法及属性，其中各有不同的参数，不用强记，可以在属性或方法的字上(例如RGB)按右键，叫出快显功能表，选择「列出属性或方法」。1ppt课件VBA操作环境-续Step6：就可直接跳到最接近的属性或方法项下，按一下，就会出现相关的在线说明，可以复制说明档上面的程序，直接拿过来用哦！1ppt课件VBA操作环境-续Step6：就可直接跳到最接近的属性或方法项下，按一下，就会出现相关的在线说明，可以复制说明档上面的程序，直接拿过来用哦！1ppt课件VBA操作环境-续Step7：输入方法或属性时，若有参数，输入「(」时会出现参数指引说明，跟我们在Excel中的资料编辑列上用函数是不是很像！1ppt课件VBA操作环境-续Step8：输入程序时请用小写英文即可，它会自动帮你转成大小写，以及用颜色区分，蓝色为VBA与法的保留字或关键字，不可拼错，黑色为程序的变量、宣告或陈述，而绿色则是注解。如果我输入「ifx1=40」按[Enter]键，立刻会告诉你错误讯息，并告诉你怎么做，而且用红色的字标示出错误的那一列，是不是很方便，刚开始只要没出现红色的字，表示程序没有大错，就可以执行了。1ppt课件VBA操作环境-续Step9：我们先来看一个设定字型格式变化的程序，都在每一行后面有说明注解(注解以’为开始)。1ppt课件VBA基本结构-程序或函数一般我们用VBA最多的就是程序或函数了，程序是一连串可执行的指令结合在一起，例如前一章所录制的巨集，主要目的是让多个重复的动作在一起执行，减少我们操作的时间；而函数则是根据使用者传入的参数，做一些运算后传回一个结果的值，像我们在第6章及第9章用的函数就是这样，我们赶快来试试看！Step1：执行[插入/程序]。Step2：在[新增程序]交谈窗，输入名称为「leapyear」，型态为「Sub」表示为一个「程序」程序。Step3：按确定后，自动在程序视窗加入了一段定义程序的宣告与结束指令，我们可以开始依据需要在这个程序中间加入我们需要的程序指令了！1ppt课件VBA基本结构-程序或函数Step4：假设写好了，想试试看如何执行，这里我们以上一节改变字型格式的程序「font20b」来示范，到Excel中，选取B2储存格，按「Alt+F8」，开启巨集交谈窗，选取一个巨集名称来[执行]。Step5：若没有错误，就可以看到程序执行的结果，你也可以选取一个储存格范围来试试看！1ppt课件VBA基本结构-程序或函数Step4：假设写好了，想试试看如何执行，这里我们以上一节改变字型格式的程序「font20b」来示范，到Excel中，选取B2储存格，按「Alt+F8」，开启巨集交谈窗，选取一个巨集名称来[执行]。Step5：若没有错误，就可以看到程序执行的结果，你也可以选取一个储存格范围来试试看！1ppt课件VBA基本结构-程序或函数Step6：现在我们在来看看「函数」程序是怎么做跟怎么执行的？在[新增程序]交谈窗，输入名称为「leapyear1」，型态为「Function」表示为一个「函数」程序。在同一模块下，程序或函数名称都不可重复。Step7：按确定后，一样自动在程序视窗加入了一段定义函数的宣告与结束指令，所不同的是程序的关键字是Sub，而函数是Function，同样都以End来结束程序。Step8：虽然透过[插入/程序]产生了一小小段程序，我们还是可以在程序视窗直接修改它哦！也可以直接复制一段程序或函数来修改，这里我们就把「leapyear1」改为「slength」，我们想利用勾股定理来计算三角形的斜边，而无论是程序或函数都不一定要传入参数，但宣告程序或函数后的「()」左右括号一定要存在，在这里我们需要三角型的两边，所以我们就在括号中定义了两个传入的参数x1及y1，资料型态都是整数。1ppt课件VBA基本结构-程序或函数Step9：做好了，要如何执行？选取任一储存格(这里我们选A1)，按一般工具列上的[插入函数]钮，在[插入函数]交谈窗中，选取类别为「使用者定义」类别，按[确定]。Step10：出现[插入函数]交谈窗，这里只定义一个函数slength，就选择它了，按确定。1ppt课件VBA基本结构-程序或函数Step11：出现[函数自变量]交谈窗，可以直接输入值或使用摺叠钮来选取其他储存格资料，我们输入3与4后，已可看到结果为5。Step12：已将计算结果放到A1储存格。1ppt课件VBA基本结构-常数、变量与资料型态通常会使用Dim

陈述式来宣告变量。Dim陈述式的语法为：Dim变量名称[as资料型态]... ([]内的表示可省略)在Dim陈述式的语法中，Dim是必备的关键字。而唯一必备的元素是变量名称。变量可以宣告成下列资料型态中的一者：Boolean、Byte、Integer、Long、Currency、Single、Double、Date、String(可变长度字符串)、String*length(固定长度字符串)、Object或Variant。如果您未指定资料型态，则Variant资料型态会被指定成预设型态。在宣告陈述式中，您不一定要提供变量的资料型态，若省略资料型态的则会将变量设成Variant型态。1ppt课件常数、变量与资料型态变量的命名规则：(1)必须以字母为开头。(2)不可以包含句号、关键字及型态宣告字符。(3)不能超过255个字。(4)如果这个陈述式出现在程序中，则此变量只可以在此程序中被使用。如果这个陈述式出现在模块中的宣告区段，则此变量可以被此模块中所有的程序所使用，但是不能被同一项目中不同的模块所含程序来使用。为了使变量可被项目中所有的程序所使用，则在变量名称前加上

Public

陈述式。1ppt课件常数、变量与资料型态宣告常数我们使用Const陈述式宣告部份常用到的文数字设为常数。例如：'常数的预设状态是Private。ConstMyVar=459'宣告Public常数。PublicConstMyString="HELP"'在同一行里宣告多个常数。ConstMyStr="Hello",MyDoubleAsDouble=3.4567宣告变量Dim x1asInteger,y2asObjectSubslength()1ppt课件VBA基本结构-物件式导向语法-物件物件导向程序中，有四个很重要的元件，(1)物件(2)属性(3)方法及(4)事件，而当我们把物件相关的属性，执行的方法，引发的事件，依物件导向语法组合在一起就是所谓的指令或陈述式。物件

Excel中常见的物件有按钮、储存格范围、工作表等等，在程序中为了使用方便，将某些同型态的物件集合在一起，称为物件集，其关键字为英文的复数型态，如Worksheets，例如想在ch24.xls的sheet1工作表中的A1储存格放入数值5，可以这样写：

Workbooks(“ch24.xls”).Worksheets(“sheet1”).Range(“A1”).Value=51ppt课件VBA基本结构-物件式导向语法-物件我们可以在VBA环境的一般工具列上按浏览物件，可看到有相当多的物件类型。1ppt课件VBA基本结构-物件式导向语法-属性属性:各种不同的物件，各有其特定的性质，我们称之为属性，通常我们利用属性来控制物件的外观，如设定储存格的字型、颜色等，我们常用指定陈述是来达成。

物件.属性=设定值 或变量=物件.属性Step1：例如在物件Font上，按右键，执行快显功能表中的[说明]。1ppt课件VBA基本结构-物件式导向语法-属性Step2：会开启Font的在线说明，亦会介绍其相关的属性、方法及事件等，样可以复制到我们的程序视窗。1ppt课件VBA基本结构-物件式导向语法-属性Step3：如果在Color属性按右键，执行说明。Step4：出现了Color属性的详细说明。1ppt课件VBA基本结构-物件式导向语法-属性一些中要属性的用法：(1)Range属性可用Range(arg)传回代表单个储存格或储存格范围的Range物件，其中arg

为范围名称。以下范例将储存格A1中的值指派给储存格A5。Worksheets("Sheet1").Range("A5").Value=Worksheets("Sheet1").Range("A1").Value以下范例清除范围Criteria中的内容。Worksheets(1).Range("Criteria").ClearContents(2)Cells属性可用Cells(row,column)传回单个储存格，其中row为列索引，column为栏索引。以下范例将储存格A1的值设定为24。Worksheets(1).Cells(1,1).Value=24以下范例设定储存格A2的公式。ActiveSheet.Cells(2,1).Formula="=Sum(B1:B5)"1ppt课件VBA基本结构-物件式导向语法-属性(3)Offset属性可用Offset(row,column)传回相对于另一范围在指定位移处的范围，其中row和column为列位移和栏位移。以下范例选取位于目前选定范围左上角储存格下三列，右一栏处的储存格。由于无法选取不在使用中工作表上的储存格，故必须先启动工作表。Worksheets("Sheet1").Activate'Can'tselectunlessthesheetisactiveSelection.Offset(3,1).Range("A1").Select技巧：用With快速设定属性With陈述式可以让您的程序执行的更快速并且帮助您避免重复的键入程序码。下面的范例将某一范围的储存格都填入30，字型使用黑体格式，并将储存格的内部颜色设定成黄色。SubFormatRange()WithWorksheets("Sheet1").Range("A1:C10").Value=30.Font.Bold=True.Interior.Color=RGB(255,255,0)EndWithEndSub1ppt课件VBA基本结构-物件式导向语法-方法方法 除了用属性来改变物件外，还可以使用方法来要求物件做一些动作，参下图，可以看到Sheets成员中前半部的属性都为名词，而后半部的方法都为动词。1ppt课件VBA基本结构-物件式导向语法-事件事件是指要执行或驱动某一物件执行一些动作所发出的讯息，如按一下鼠标，按一下键盘或开启活页簿，另外我们也可以透过我们写的程序码来驱动或导致某些事件的发生。如下，我们可以看物件可以有哪些事件。CommandButton1(按钮)物件，有Click(按一下)，DbClick(按二下)...等等事件。1ppt课件VBA基本结构-物件式导向语法-事件工作表上的常用事件如下。当启动工作表、使用者变更工作表上的储存格、或枢纽分析表变更时，即会发生工作表级的事件。当启动活页簿、工作表、图表、或内嵌图表时发生Activate事件。ActivateBeforeDoubleClickBeforeRightClickCalculateChangeDeactivateFollowHyperlinkPivotTableUpdateSelectionChange1ppt课件程序的控制结构-使用If...Then...Else陈述式

If...Then...Else陈述式会根据条件式的值，而执行指定的陈述式或一个区块的陈述式。If...Then...Else陈述式可以依据您的需要而做多阶层的巢状执行。然而，为了可读性您可能会使用SelectCase陈述式而不使用多阶层的巢状If...Then...Else语法。下列的范例展示了单行语法，并省略了Else关键字：SubFixDate()myDate=#2/13/95#IfmyDate<NowThenmyDate=NowEndSub1ppt课件程序的控制结构-使用If...Then...Else陈述式

If...Then...Else陈述式会根据条件式的值，而执行指定的陈述式或一个区块的陈述式。If...Then...Else陈述式可以依据您的需要而做多阶层的巢状执行。然而，为了可读性您可能会使用SelectCase陈述式而不使用多阶层的巢状If...Then...Else语法。下列的范例展示了单行语法，并省略了Else关键字：SubFixDate()myDate=#2/13/95#IfmyDate<NowThenmyDate=NowEndSub1ppt课件程序的控制结构-使用If...Then...Else陈述式

若要执行多行程式码的话，您必须使用多行的语法，而此语法须包含EndIf

陈述式，如同下面范例所示：SubAlertUser(valueasLong)Ifvalue=0ThenAlertLabel.ForeColor="Red"AlertLabel.Font.Bold=TrueAlertLabel.Font.Italic=TrueEndIfEndSub1ppt课件程序的控制结构-使用If...Then...Else陈述式

当条件式为True，执行某些陈述式；条件式为False，执行其余的陈述式使用If...Then...Else陈述式可以定义两个可执行的陈述式区块：其中一个区块会在条件式为True时执行；而另一个区块会在条件式为False时执行。SubAlertUser(valueasLong)Ifvalue=0ThenAlertLabel.ForeColor=vbRedAlertLabel.Font.Bold=TrueAlertLabel.Font.Italic=TrueElseAlertLabel.Forecolor=vbBlackAlertLabel.Font.Bold=FalseAlertLabel.Font.Italic=FalseEndIfEndSub1ppt课件程序的控制结构-使用If...Then...Else陈述式

当第一个条件式为False时，测试第二个条件式当第一个条件式为False时，您可以在If...Then...Else陈述式中加上ElseIf

陈述式来测试第二个条件式。举例而言，下列的函数程序根据工作分类来计算奖金。如何所有If和ElseIf

陈述式中条件式都是False，则会执行紧接在Else陈述式之后的陈述式。FunctionBonus(performance,salary)Ifperformance=1ThenBonus=salary*0.1ElseIfperformance=2ThenBonus=salary*0.09ElseIfperformance=3ThenBonus=salary*0.07ElseBonus=0EndIfEndFunction1ppt课件程序的控制结构-使用SelectCase陈述式

将一个表达式与数个不同的值比较时，可以使用SelectCase陈述式来取代在If...Then...Else陈述式中使用ElseIf。If...Then...Else陈述式会计算每个ElseIf

陈述式中的表达式，而SelectCase陈述式只会在控制结构的顶端计算一次表达式。在下面的范例中，SelectCase陈述式会计算传送给此程序的自变量performance。请注意，每一个Case陈述式可以含有一个以上的值，一段范围的值，或是一个组合值以及比较运算子。选择性的CaseElse陈述式只有在自变量值无法与SelectCase中所有的Case陈述式相符合时才会执行。FunctionBonus(performance,salary)SelectCaseperformanceCase1Bonus=salary*0.1Case2,3Bonus=salary*0.09Case4To6Bonus=salary*0.07CaseIs>8Bonus=100CaseElseBonus=0EndSelectEndFunction1ppt课件程序的控制结构-使用Do...Loop陈述式

您可以使用Do...Loop陈述式去执行一个区块的陈述式，而它所用掉的时间是不确定的。当条件式为True或直到条件式变成True之前，此陈述式会一直重复。（１）重复陈述式直到条件式为True当使用While关键字去检查Do...Loop陈述式中的条件式时，可以有两种不同的方式。您可以在进入循环之前检查条件式，也可以在循环至少执行一次之后才检查条件式。在下面的ChkFirstWhile

程序中，是在进入循环之前检查条件式。如果将myNum

的值由20替换成9，则在循环中的陈述式将永远不会执行。在ChkLastWhile

程序中，在条件式变成False之前循环中的陈述式会一直执行。SubChkFirstWhile()counter=0myNum=20DoWhilemyNum>10myNum=myNum-1counter=counter+1LoopMsgBox"Theloopmade"&counter&"repetitions."EndSub1ppt课件程序的控制结构-使用Do...Loop陈述式

（２）从Do...Loop陈述式中离开您可以使用ExitDo陈述式来离开Do...Loop陈述式。举例而言，为了离开无穷循环，可以将ExitDo陈述式放在If...Then...Else陈述式或是SelectCase陈述式中的True陈述式区块。如果条件式为False，则循环会像平常一样执行。可以按下ESC或CTRL+BREAK来终止无穷循环。1ppt课件程序的控制结构-使用For...Next陈述式

您可以使用For...Next陈述式去执行一个区块的陈述式，而它所用掉的时间是特定的。For循环使用一个计数变量，当反覆的执行循环时它的值会增加或减少。下面的程序会让计算机发出哔声50次。For陈述式会指定计数变量x的开始与结束值。Next陈述式会将计数变量的值加1。SubBeeps()Forx=1To50BeepNextxEndSub1ppt课件程序的控制结构-使用For...Next陈述式

使用Step关键字，可以增加或减少计数变量的值，并且增减值可由您指定。在下面的范例中，计数变量j会在循环重复时加上2。当循环结束时，total的值为2、4、6、8和10的总合。SubTwosTotal()Forj=2To10Step2total=total+jNextjMsgBox"Thetotalis"&totalEndSub为了减少计数变量的值，您可以使用负的Step值。在Next陈述式后面可以不用加上计数变量的名称。在For...Next陈述式中您可以在计数变量到达它的结束值之前，使用ExitFor陈述式来离开。举例而言，当错误发生时可以将ExitFor陈述式放在If...Then...Else陈述式或是SelectCase陈述式中的True陈述式区块，它是专门用来检查此错误的。如果没有错误发生，If...Then...Else陈述式的值为False，则循环会像预期一样的执行。

1ppt课件24-3VBA应用简例-以程序来判断星座我们以输入日期中的「月」和「日」的资料来判断，因为是程序，必须以选取的储存格的日期来做，输入如下程序，并将结果放在此储存格的右一格。Step1：输入程序后，都没有红色警戒(错误语法)，先存档。1ppt课件24-3VBA应用简例-以程序来判断星座Step2：在A3储存格输入「1990/5/2」，执行看看。Step3：糟了个糕，有错误，按[侦错]钮。1ppt课件24-3VBA应用简例-以程序来判断星座Step4：原来是最后一行打错了，应该是ActiveCell。1ppt课件24-3VBA应用简例-以程序来判断星座Step4：原来是最后一行打错了，应该是ActiveCell。1ppt课件24-3VBA应用简例-以程序来判断星座Step5：修改完毕，继续[执行]。1ppt课件24-3VBA应用简例-以程序来判断星座Step6：果然在B3储存格显示了「金牛座」。Step7：如果有人不清楚此程序的功能，想试试看，但在A4输入了「abc」。Step8：执行结果为「型态不符合」的错误，按[侦错]钮。1ppt课件24-3VBA应用简例-以程序来判断星座Step9：因为year这个函数必须使用日期型态资料为自变量。1ppt课件24-3VBA应用简例-以程序来判断星座Step10：所以在用year函数前，加入先判断是否为有效日期，不是则出现警告讯息，结束程序。1ppt课件24-3VBA应用简例-以程序来判断星座Step11：因为已经执行到这里，更正错误后继续执行也会一直出现错误，所以我们按[重新设定]，从头再执行一次。Step12：结果如下，出现日期错误讯息。1ppt课件24-3VBA应用简例-以函数来判断闰年判断闰年的方法如下：公元年，被4整除？no->不是闰年

yes，被100整除？-no->是闰年 yes，被400整除？no->不是闰年 yes->是闰年Step1：如果我们关闭了程序视窗，只要在[项目视窗]上按[检视程序码]即可。1ppt课件24-3VBA应用简例-以函数来判断闰年Step2：插入一函数，并输入一参数为日期型态，取参数的「年」资料，按判断闰年的逻辑来写出下列程序码，跟上一题一样，先检查是否为有效日期。1ppt课件24-3VBA应用简例-以函数来判断闰年Step3：在A5输入「2001/1/1」，选取