Excel VBA简明教程.doc

VBA的简明教程1.VBA简介VBA为Visual Basic For Application的简称，在Microsoft Office中包括Excel、Word、PowerPoint、Access、Visio均提供VBA程序的功能。VBA的使用大致上与Visual Basic类似，在VBA中可使用Office软件的功能，在VBA程序中可操控Office软件。就程序撰写而言，资料的输入、编辑、打印等I/O动作可由应用Office软件的功能，因而程序撰写可更专注于程序逻辑思考而省下I/O功能撰写的时间。在Excel中宏所录者即是为VBA的子程序(Subroutine)的格式储存，宏的程序代码的功能限制在于其为直接录制操作过程，所以录制所得的子程序不能传入变量值或传出变量值，且录制之宏功能以可于EXCEL的工作表或图表中操作之功能为限。欲使用不能直接于Excel中操作之进阶功能则需使用VBA程序。VBA的程序可分为子程序(Subroutine)与函数(Function)两种，其功能与Visual Basic程序语言中的子程序及函数相同。在语法上VBA与Visual Basic程序语言相同，而且VBA也有IDE的程序编写工具。两者之间主要的差别是VBA以配合Office应用为主，Visual Basic程序语言则为软件开发环境，除了VBA的发展功能外尚可用于发展Windows的一般执行文件、Windows系统组件及开发应用软件系统。概略而言，宏可相当于VBA语言中之无参数之子程序(Subroutine)，而且VBA中的函数(Function)可在Excel工作表中以自订函数形式叫用。2.VBA程序语法本小节将简单介绍VBA的基本语法，限于篇幅进阶的语法及对象使用将不在本文中说明。读者若要精研VBA的语法可参阅参考文献中所介绍的书籍。一、基本功能叙述index1=1index1=index1+1If index1 10 Then index1=10value1=0.01: alpha=0.85*index1VBA的语法与Visual Basic大多数均是相同的，下列这几行正确的VBA叙述您可以在一列中键入一个以上的叙述，只要以冒号(:)将他们分开即可。如上述的第四列述叙。如同BASIC的语法定义，VBA中是变量、关键词和函数、子程序名称等均不分大小写的，例如INDEX、index、Index三者均指同一个变量连续行VBA连续字符符号是底线( _ )，当程序执行到某一列是以底线符号结尾时，他会假设有一个连续的行，并将下一行视为目前叙述的一部份。太长的叙述不易阅读，在这种情形您可以采用VBA连续列字符，例如以下讯息列就比常用窗口宽度还长:Response = MsgBox(Do you want to continue ?, vbYesNo + vbCritical + vbDefaultButton2, MsgBox Demonstration, DEMO.HLP, Ctxt = 1000)如果程序行包含很长的列，比较好的方法是加一空格符和底线符号在该行结束的地方 ，并在下一列继续此一列叙述。如下所示Response = MsgBox(Do you want to continue ?, vbYesNo + vbCritical + _vbDefaultButton2, MsgBox Demonstration, DEMO.HLP, Ctxt = 1000)连续行的最末列的最后面不需要加上底线符号，而且底线符号之后也不可以再加上任何文字或数字，批注也不可以。批注批注列可让程序易被看懂，又如果要让程序中的某一列不执行，只要将这一列标示为批注即可。VBA中的批注列符号为单引号( )，若是一整列都要作为批注，也可在这列以REM叙述开头。下面前两列都是批注，第三列则是在后半段加上REM叙述作为批注，第四列则是在后半段加上单引号( )标示为批注:REM 批注列示范This is a remark demoMyStr1 = Hello : Rem 将批注加在陈述式之后，用冒号隔开。MyStr2 = Goodbye 此亦为批注，且不需要冒号。常数与变量常数在程序中只包含固定的数值，您不能在程序进行中更动他，常数以关键词Const始，例如下一列将beta定义为常数，其值为0.85:Const beta=0.85VBA中已有预先定义了某些常数，例如在MsgBox函数中vbYesNo代表常数值4。详细的常数定义可在VBA的编辑器中取得说明。变量是指到程序可以储存值的内存位置的代表名称。变量与常数不同的是变量可在程序执行期间改变其值。在VBA中变量可有不同的内容型态，例如:Byte、Boolean、Integer、Long、Currency、Decimal、Single、Double、Date、String、Object、Variant ，其中预设的变量型态是Variant。以下三列是变量的使用范例:price=45 数值(integer)变量Item=”水果” 文字(string)变量Set xlApp = CreateObject(excel.application) 对象(object)变量Option Explicit叙述这一叙述通知VBA编译器找出未宣告即使用的数数。在预设的情况下VBA允许变量不经宣告即可使用，但这也造成程序除错上的某些困难。您也可在程序的第一行使用Option Explicit叙述，可强制程序在使用变量前先需宣告，这样有避免拼错字母造成程序上逻辑错误的问题。Dim叙述您可以使用Dim在程序或函数中明确的定义变量或数组变量。若您使用了Option Explicit叙述，则您;所使用的变量皆需使用Dim先行宣告才可使用。以下三列皆是正确的变量宣告Dim text 宣告了一个变量Dim price,Value,sum 同时宣告两个以上的变量Dim Item(100) 宣告了101个元素的数组。VBA亦可使用数组，数组的第一个元素预设为0，所以上述最后一列中宣告了Item(0)至Item(100)共计101个变量。Public与Private关键词Public与Private关键词宣告变量用来限制变量的有效范围，使用 Public 陈述式宣告的变量，可以在所有模块里使用 (除非使用 Option Private Module)；变量有效范围为它们所在的项目里。使用 Public 陈述式来宣告变量的资料型态。举例来说，下列的陈述式宣告一个变量为 Integer：Public NumberOfEmployees As Integer您也可用 Public 陈述式来宣告变量的对象型态。下列的陈述式宣告一个变量为工作表的新执行个体。Public X As New WorksheetPublic 陈述式无法在对象类别 模块里宣告固定长度的字符串变量。Private 用以宣告变量只适用于宣告它们的模块中。Private 陈述式也可以用来宣告变量的资料型态。举例来说，下列的陈述式宣告变量为 Integer：Private NumberOfEmployees As Integer您也可使用 Private 陈述式加上空白括号来宣告动态数组。宣告之后，在程序里使用 ReDim 陈述式来定义数组的维数和元素。变量的大小已由 Private、Public或 Dim 陈述式所明确指定，若重新宣告数组变量的维数，则会发生错误。变量名称如同在Visual Basic中一般，一个变量名称最多可使用255个字符，变量名称的命名规则如下:1.变量的第一个字符需英文字母。例如 Person1为有效变量名，23men则是不合规则的变量名称。2.变量名称不可包含空白、点、，逗号或其它特殊字符。3.变量名称不可为关键词，例如for、next、while、public等运算子程序中使用的运算子摘要为下表当一个表达式中有数个动作发生时，各部分的动作会依预定优先级完成，而此顺序就是运算子的优先级。当表达式中有超过一种运算子时，会先执行算术运算子，其次是比较运算子，而后才是逻辑运算子。比较运算子的优先级全都是相同的，也就是说依照出现的顺序由左到右依序执行。而算术和逻辑运算子则必须依照下列的优先级，依序执行。下表是四个种类的运算子汇总表，各运算子优先级由上而下排列:流程控制结构IfThenElseIf.EndIf在VBA中可使用If.then.ElseIf.EndIF来控制循环与条件判断式，If陈述式的结构如下:If condition ThenstatementsElseIf condition-n Thenelseifstatements.ElseelsestatementsEnd IfIf.Then.Else 陈述式有区块格式及单行格式两种格式，以下范例是 If .Then.Else 的用法。Dim Number, Digits, MyStringNumber = 53 设定变量起始值。If Number 10 Then Digits = 1ElseIf Number 100 Then 若判断结果为 True，则执行下一行陈述式。 Digits = 2Else Digits = 3End If假如判别式 较为简单，您也以使用单行格式语法，以下是单行格式的正确语句:If Digits = 1 Then MyString = One Else MyString = More than oneSelect Case使用If.Then.Else的判别式每次只有一个判别分支，SelectCase则一次可以有数个分支，根据判别变量的值，您可以执行其中一个程序区块，以下是正确的SelectCase程序代码Dim NumberNumber = 8 设定变量初值。Select Case Number 判断 Number 的值。Case 1 To 5 Number 的值在 1 到 5 之间，是非排它性的。 Debug.Print Between 1 and 5 下一个 Case 子句是本范例中唯一判断值为 True 的子句。Case 6, 7, 8 Number 的值在 6 到 8 之间。 Debug.Print Between 6 and 8Case 9 To 10 Number 的值为 9 或 10。Debug.Print Greater than 8Case Else 其它数值。 Debug.Print Not between 1 and 10End Select上述范例中包含了Case Else程序区块，这一区块是在以上所有条件皆不成立时可以被执行。DoLoopDoLoop判别式有许多衍生语法，概观而言可分为先执行再判别及先判别再执行两种，前者保证循环至少执行一次，后者则可在条件不符时完全跳过这一程序区块不执行。DoLoop的语法结构如下:Do While | Until conditionstatementsExit DostatementsLoop或者您可以使用底下这个语法：DostatementsExit DostatementsLoop While | Until condition以下是VBA说明文件中的一个范例程序，本范例示范如何使用 Do.Loop 陈述式。内层的 Do.Loop 陈述式在执行到第 10 次时将旗标值设成 False，并使用 Exit Do 陈述式来强制离开内层循环。外层循环则在检查到旗标值为 False 时，马上结束。Dim Check, CounterCheck = True: Counter = 0 设定变量初始值。Do 外层循环。 Do While Counter 20 内层循环。 Counter = Counter + 1 计数器加一。 If Counter = 10 Then 如果条件成立。 Check = False 将旗标值设成 False。 Exit Do 离开内层循环。 End If LoopLoop Until Check = False 结束外层循环。ForNext这一语法用来以特定次数来重复执行一段陈述式。语法结构如下:For counter = start To end Step stepstatementsExit ForstatementsNext counter以下是VBA说明文件中的一个范例，本范例使用 For.Next 陈述式来建立一个字符串，其内容为由 0 到 9 的十个数字所组成的字符串，每个字符串之间用空格隔开。外层循环使用一个变量当作循环计数器，其计数方式是由大到小递减:Dim Words, Chars, MyStringFor Words = 10 To 1 Step -1 执行 10 次。 For Chars = 0 To 9 执行 10 次。 MyString = MyString & Chars 将数字加入到字符串中。 Next Chars Increment counter MyString = MyString & 加入一个空格。Next WordsForEachNextFor Each.Next 陈述式用来对集合对象里所有成员进行遂项处理，在电子表格的VBA程序选写过程中常会遇到不能预知成员个数，所以应用For Next判陈述式较为困难，此时可应用ForEachNext，以下是这一语法的说明:For Each element In groupstatementsExit ForstatementsNext element以下是在VBA说明文件中的一个范例，这一范例示范如何应用此一陈叙述式:Dim Found, MyObject, MyCollectionFound = False 设定变量初始值。For Each MyObject In MyCollection 对集合对象里的成员作一次循环执行。 If MyObject.Text = Hello Then 如果 Text 属性值等于Hello。 Found = True 将变量 Found 的值设成 True。 Exit For 离开循环。 End IfNext一、基本功能函数与程序Visual Basic相同，VBA中亦可使用函数(Function)与程序(Subroutine)，当只有一个参数要传回给呼叫的程序时，您可使用函数。若有超过一个以上的参数要回传给呼叫程序时，则以使用程序较为恰当。VBA中的函数可包括内建函数及使用者自订的函数。使用者自订函数的格式如下:Public | Private | Friend Static Function name (arglist) As typestatementsname = expressionExit Functionstatementsname = expressionEnd Function结果值必须在函数主体中指定给函数名称。以下为Function的使用范例，这一函数输入一整数val1的阶层的数值，并将结果以函数名称传回:Function fact(val1 As Integer) fact = 1 For i = val1 To 1 Step -1 fact = fact * i Next iEnd FunctionVBA也支持Exit Function叙述，倘若这个叙述在函数中被执行，则函数会终止并将控制权还给呼叫的模块。您也可以使用下面的语法来宣告程序:Private | Public Static Sub name (arglist)statementsExit SubstatementsEnd Sub程序与函数的主要不同是程序不会将结果以程序名称传回，要与程序交换资料您可使用参数串行将资料传数程序中运算，完成运算后亦可用参数将数据传回，或者您可以宣告全域变量并在程序中改变他的值的方式来与程序交换资料。在VBA中假如宏就是一种没有参数的程序，若是您自订程序而无参数，则VBA也会将他视为宏。以下程序可以用来测试阶层函数fact，由于这一程序没有参数，所以在入后您由Excel中的选单选取 工具(T)/宏(M)/宏(M)时，在宏的选择列表中也会显示这个宏。Sub testfact() fact1 = fact(3) MsgBox fact1End SubVBA中也有内建函数，例如三角函数:sin(x)、cos(x)、tan(x)等。您可参阅VBA的上说明来取得所有可用的内建函数表列。而在初学Visual Basic时最先教习的信息方块MsgBox()函数及输入对答框Input()函数也都在表列之中。以下是使用Input()与MsgBox()函数来计算梯形式:1加到n的范例:Sub trapezoid() Message = 请输入一整数值 设定提示讯息 Title = 梯形公式:1+2+3.+N 设定标题 显示标题及讯息 N = InputBox(Message, Title) For i = 1 To N value1 = value1 + i Next iresponse = MsgBox(1+2+3+.+ & N & 的值为 & value1, vbOKOnly, 梯形公式计算结果)End Sub在Excel中设计VBA程序的最方便处是VBA程序中可以使用Excel的现有对象，有了Excel对象，您设计VBA程序就可使用Excel电子表格的表列输入、计算与打印的功能，比起Visual Basic中设计程序方便许多。下一节就是一个使用Excel对象设计VBA程序的范例。3.VBA程序设计在VBA中设计本范例与在Visual Basic 程序语言中设计程序大致上相同，其主要差别是VBA仍然是附属在Excel中执行， 而Visual Basic 则可编译为可单独执行的独立程序。因此VBA的设计应本于Excel的优点，尽量在VBA中使用Excel的对象。本书中假设读者已具有Visual Basic程序语言的设计基础，因此对于 Visual Basic 语言将不予详述。以下将着重于Visual Basic 程序中使用Excel的对象及程序与Excel交互作用的部份作说明。如同Visual Basic，在VBA中也可以设计自订窗体Form，设计方式与Visual Basic程序语言完全相同。Excel中亦附有Visual Basic for Application程序整合编辑器以供设计VBA窗体及撰写程序。开启Visual Basic 编辑器方法可在Excel中选取选单 工具(T)/宏(M)/Visual Basic 编辑器。以下列例题说明VBA程序的设计与应用：例题1.6.1撰写一VBA程序以计算数学式 ，当输入k值后可算得X的值。解:(1)设计资料输入窗体：首先激活Excel，由下拉选单中选取 工具(T)/宏(M)/Visual Basic 编辑器。可进入如图1.1中所示之VBA编辑器画面。画面中左上角为项目总管，左下为属性窗口，中央为编辑窗口，接着由下拉选单中选取 插入(I)/自订窗体(U) 此时编辑窗口中将会产生一个自订窗体，此一自订窗体的预设名称为UserForm1，默认值也同时将左方之工具箱将同时显现，参考下图。图 1.1 窗体及对象在图1.6.1中所使用的对象有Label1、Label2、Label3、TextBox1、TextBox2、TextBox3、Command1、Command2、Command3，其位置参考上图。Label对象用以显示画面上的文字说明，TextBox对象用以作为输入字段，Command对象则用以作为指令按钮。Label、TextBox、Command等对象均可由工具箱中拉出至窗体中的适当位置，并可调整至适当大小及更改显示文字。位置、大小、显示文字等项在Visual Basic中称为属性(Property)，Visual Basic编辑器中附有属性窗口以供修改对象的属性。(2)键入程序代码以鼠标对着图1.1窗体窗口连续按左键两下可进入Visual Basic程序代码编辑窗口。参考图1.1, Visual Basic 程序设计的思考方式是设计一段事件激活程序代码, 当按下确定按钮这一事件发生时即执行这段程序代码来达成所欲完成的工作。而这段程序代码该是一段子程序(Subroutine)，这段子程序也必需有适当的的名称以作为与确定按钮连结的识别。因为图1.1中确定按钮的名称为CommandButton1 ，所以对应于此一按钮被按下时的事件子程序就是CommandButton1_Click()了。在图1.1中对着确定按钮以鼠标左键连续按两下可进入对确定按钮的子程序所在列。当确定按钮被按下时所要做的工作是先取得使用者在TextBox1中所键入的值 ， 令此值为a，接着以ForNext指令依梯形式由1算到a累加起来 ，最后将所算的成果颢示于TextBox2中。以上所述这些动作写成程序代码就如下所示。Private Sub CommandButton1_Click()a = Val(TextBox1.Text)b = 0#i = 0#For i = 1 To a b = i * (i + 1) / 2NextTextBox2.Text = Str(b)End SubPrivate Sub CommandButton2_Click() TextBox1.Text = “” TextBox2.Text = “” TextBox1.SetFocusEnd SubPrivate Sub CommandButton3_Click() EndEnd Sub同理 ，清除按钮所要完成的工作是将TextBox1及TextBox2中的值清为零 ，所以上表中这段程序代码是将TextBox1.text及TextBox2.text指定为零。结束按钮工作是将程序结束 ，所以这段程序代码只要写一个End指令将程序结束即可。(3)建立执行程序画面参考图1.1;中的Visual Basic 编辑器中的项目总管窗口，在VBAProject中的预设对象类别有Microsoft Excel 对象，此对象类别下包含有 Sheet、Chart、ThisWorksheet等对象，当使用者插入窗体(UserForm)之后则出现第二个对象类别-窗体，假如使用者另依1.5中所示录制宏时则在项目总管中另将出现另一对象类别-模块。当使用者在开启Excel电子表格之后选取下拉选单工具(T)/宏(M)/宏(M)，其中所显示的宏名称列表会将对象类别模块中的所有子程序(Sub)名称显示在表中。至于所有的函数(Function)以及属于Sheet、Chart、ThisWorkbook、UserForm等对象中的子程序则不会显示在宏名称列表中。由上所述，欲执行上述所设计的窗体UserForm1 可在模块中写一段子程序(sub)来激活UserForm1窗体，此一子程序则可由Excel电子表格之下拉选单工具(T)/宏(M)/宏(M)来激活。另一种激活UserForm1的方法是在Excel的电子表格中建立铵钮对象，以此铵钮对象的事件驱动子程序来激活UserForm1。Excel在电子表格的工具列中有提供了”控制工具箱” 这一工具列，以便使用者在电子表格中直接建立各项对象。图1.3即采用此一方式。参考下图，由于必需键入方程式及文字说明，图1.3以Word打好了题目说明，再将其插入Excel电子表格中，并在Excel的电子表格中的下拉选单选取 检视(V)/工具列(T)/按制工具箱 来激活按制工具箱。 利用控制工具箱的协助在电子表格中可轻易的建立指令按钮执行数学式，按下 按钮则此一电子表格将进入” 设计模式”，在电子表格的设计模式下由控制工具箱中按一下指令按钮 之后则在电子表格中拉出一个CommandButton，如同(2)中所示变更此一按钮属性及键入以下程序代码以便将指令按钮用激活UserForm1。激活UserForm1的程序代码只要应用UserForm1中的Show方法即可办到，全部的程序代码请参考下表。图1.3 执行程序画面Private Sub CommandButton1_Click()UserForm1.ShowEnd Sub(4)执行程序完成以上的设计部骤之后再按一下按制工具箱工具列中的 按钮可使电子表格结束设计模式，此时按下 按钮可执行程序，执行画面如图1.3所示。在Excel中宏或程序的激活可分为(1)激活Excel时自动激活程序。(2)开启xls档案时自动激活程序。(3)设计激活指定VBA程序的按钮.(4) 按选单 工具(T)/宏(M)/ 宏(M)/选取宏名称/执行(R)等几种方法。(1)激活Excel时自动激活程序：Excel 在激活时会自动读取在XLStart 目录内的所有附文件名为xls的电子表格，若Office在安装时是以预设的目录路径安装，则Excel的激活目录完整路径为”C:Program FilesMicrosoft OfficeOfficeXLStart”。Excel对于每一使用者亦支持自订激活档案，此一激活档案路径指向在个人Profiles目录内的”Application DataMicrosoftExcelXLSTART”。举例而言，假定笔者登入Windows