《信息时代的管理信息系统ModuleM课件》由会员分享，可在线阅读，更多相关《信息时代的管理信息系统ModuleM课件（67页珍藏版）》请在金锄头文库上搜索。
1、Extended Learning Module MProgramming in Excel with VBAINTRODUCTIONVBA, which stands for Visual Basic for Applications, is a programming language developed by MicrosoftExcel, along with the other members of Microsoft Office, includes the VBA language INTRODUCTIONExcel VBA is a programming applicatio
2、n that allows Visual Basic code to customize your Excel applicationsUnits of VBA code are often referred to as macros INTRODUCTIONOne advantage of Excel VBA is the macro recorderThe macro recorder is a software tool that will let you record a sequence of commands in Excel and save them as a macro WH
3、Y VBA?VBA is a programming language, but it also serves as a macro languageA macro language is a programming language that includes builtin commands that mimic the functionality available from menus and dialog boxes within an applicationA macro is a set of actions recorded or written by a user WHY V
4、BA?Create a VBA macro to format and print a monthend sales reportExecute the macro with a single commandTrigger Excel to automatically perform many timeconsuming tasks WHY VBA?If you are able to perform an operation manually, you can use the macro recorder to capture that operationYou can use VBA to
5、 create your own worksheet functions WHY VBA?Common uses for VBA macros:Inserting text Automating a task Automating repetitive tasksCreating a custom command WHY VBA?Common uses for VBA macros:Creating a custom toolbar buttonCreating a custom menu command Creating a simplified front endDeveloping ne
6、w worksheet functions Creating complete, macrodriven applications VBA in a NutshellYou perform actions in VBA by writing (or recording) code in a VBA macroYou view and edit VBA macros using the Visual Basic Editor (VBE) VBA in a NutshellA VBA macro consists of Sub proceduresA Sub procedure is comput
7、er code that performs some action on or with objects VBA in a NutshellSub procedure example:Sub Demo()Sum = 1 + 1MsgBox “The answer is ” & Sum End Sub VBA in a NutshellA VBA macro can also have Function proceduresA Function procedure is a VBA macro that returns a single valueYou can call it from ano
8、ther VBA macro or even use it as a function in a worksheet formula VBA in a NutshellFunction example:Function AddTwo(arg1, arg2) AddTwo = arg1 + arg2 End Function VBA in a NutshellVBA manipulates objectsAn object in VBA is an item available for you to control in your codeExcel provides more than 100
9、 objects that you can manipulate VBA in a NutshellYou can assign values to variablesA variable is a place to store a piece of informationYou can use variables in your VBA macro to store such things as values, text, or property settings VBA in a NutshellVBA FUNCTIONEXAMPLEObjectArranged in a hierarch
10、yExcel: Workbook: WorksheetApplication.Workbooks(“Book1.xls”).Worksheets(“Sheet1”)MethodsMethod is an action such as ClearContentsWorksheets(“Sheet1”).Range(“A1”).ClearContentsTHE VISUAL BASIC EDITORThe Visual Basic Editor (VBE) is a separate application where you write and edit your Visual Basic ma
11、crosYou cant run the VBE separately; Excel must be running in order for the VBE to operate THE VISUAL BASIC EDITORThe quickest way to activate the VBE is to press Alt+F11 when Excel is activeTo return to Excel, press Alt+F11 againAlt+F11 acts as a toggle between the Excel application interface and t
12、he VBE You can also activate the VBE by using the menus within ExcelChoose Tools, then Macro, and then choose Visual Basic Editor The VBE ToolsMenu BarToolbarProject Explorer WindowCode WindowThe Properties WindowThe Immediate Window The VBE ToolsWorking with the Project ExplorerWhen working in the
13、VBE, each Excel workbook thats open is a projectA project is a collection of objects arranged as an outlineExpand a project by clicking the plus sign (+) To contract a project click the minus sign () Adding a New VBA ModuleFollow these steps to add a new VBA module to a project:1.Create a new workbo
14、ok in Excel2.Press Alt+F11 to activate the VBE3.Select the projects name in the Project Explorer window4.Choose Insert and then Module or you can use the shortcut, by using the rightmouse click, choosing Insert, and then Module Removing a VBA ModuleTo remove a VBA module from a project, follow these
15、 steps:1.Select the modules name in the Project Explorer window2.Choose File, and then Remove ModuleName Creating a ModuleIn general, a VBA module can hold several types of code:Sub procedures A set of programming instructions that performs some action Function procedures A set of programming instru
16、ctions that returns a single valueDeclarations One or more information statements that you provide to VBA VBA Module CodeBefore you can do anything meaningful, you must have some VBA code in the VBA moduleYou can get VBA code into a VBA macro in two ways:1.Entering the code directly by typing it2.Us
17、ing the Excel macro recorder to record your actions and convert them to VBA code Entering Code DirectlyYou can type code directly into the moduleYou can select, copy, cut, paste, and do other things to the textWhen you are entering your code directly, you use the Tab key to indent some of the lines
18、to make your code easier to read Entering Code DirectlyEntering Code DirectlyUsing the Macro Recorder1.Activate a worksheet in the workbook2.Choose Tools, then Macro, and then Record New Macro3.Excel displays its Record Macro dialog box4.Click OK to accept the defaults5.Excel automatically inserts a
19、 new VBA module into the project6.From this point on, Excel converts your actions into VBA code while recording, Excel displays the word Recording in the status bar Using the Macro Recorder7.Excel displays a miniature floating toolbar that contains two toolbar buttons: Stop Recording and Relative Re
20、ference 8.Choose Tools, then Options9.Click the View tab10.Remove the check mark from the Gridlines option11.Click OK to close the dialog box12.Click the Stop Recording button Using the Macro RecorderRelative Relative Reference Reference ButtonButtonStop Stop Recording Recording ButtonButtonUsing th
21、e Macro RecorderUsing the Macro RecorderTry the macro:1.Activate a worksheet that has gridlines displayed2.Choose Tools, then Macro, and then choose Macros, or press Alt+F83.Select Macro14.Click the Run button5.Excel executes the macro, and the gridlines disappear Using the Macro RecorderExecute the
22、 Execute the chosen macrochosen macroRemove the Remove the chosen macrochosen macroUsing the Macro RecorderAnother way to execute a macro is to press its shortcut key1.Choose Tools, then Macro, and then Macros2.Select the Macro1 Sub procedure name from the list box3.Click the Options button4.Click t
23、he Shortcut Key option and enter a letter in the box labeled Ctrl + 5.Click OK to close the Macro Options dialog box Using the Macro RecorderRemove Remove gridlinesgridlinesVBA BUILDING BLOCKSThere are many ways to write a macro using Excel VBAWrite or record macros using modules or proceduresDevelo
24、p userdefined functions Code ModulesAll macros reside in code modules like the one on the right of the VBE windowThere are two types of code modules 1.Standard modules2.Class modules ProceduresIn VBA, macros are referred to as proceduresThere are two types of procedures1.Sub procedures 2.Function pr
25、oceduresThe macro recorder can only produce Sub procedures Sub ProceduresSub procedures (sometimes referred to as subroutines) start with the keyword Sub followed by the name of the procedure and opening and closing parenthesesThe end of a Sub procedure is marked by the keywords End Sub Sub Procedur
26、esSub MonthNames()Range(“B1”).SelectActiveCell.FormulaR1C1 = “Jan”Range (“C1”).SelectActiveCell.FormulaR1C1 = “Feb”.End SubFunction ProceduresExcel has hundreds of builtin worksheet Function ProceduresChoose Insert, and then the Function command to see a list of those functionsIf the function you ne
27、ed is not already in Excel, you can write your own user defined function (or UDF) using VBA Function ProceduresA function procedure example:Function CentigradeToFahrenheit(Centigrade)CentigradeToFahrenheit = Centigrade * 9 /5 + 32End Function ELEMENTS OF VBA PROGRAMMINGVBA uses many elements common
28、to all programming languages, such as: CommentsVariablesConstantsData types CommentsA comment is the simplest type of VBAVBA ignores these statementsExample:Sub CommentsExample() This procedure is a demonstrationx = 0 x represents zero The next line of code will display the resultMsgBox x End Sub Va
29、riables and ConstantsVBAs main purpose is to manipulate dataVBA stores the data in your computers memorySome data, such as worksheet ranges, reside in objects and other data are stored in variables or constants VariablesVariable name of a storage locationExamplesx = 1 (assigns the value of 1 to the
30、variable x)UserName = “Amy Phillips” (UserName is a string variable that can hold text)x = x + 1 (adds 1 to x and stores it in the variable x)DateStarted = #12/20/2004# (DateStarted is a date variable)ConstantsA variables value may (and usually does) change while your procedure is executingA constan
31、t is a named element whose value doesnt changeExample:Const NumQuarters As Integer = 4 Const Rate = .0725Const Period = 12 Const ModName As String = “Budget Macros” Data TypesData types are the manner in which data types are stored in memory for example, as integers, real numbers, or stringsExamples
32、BooleanIntegerCurrencyDateStringStringsExcel and VBA can work with both numbers and textText is often referred to as a stringThere are two types of strings in VBA:1.Fixed-length strings are declared with a specified number of characters (the maximum length is about 65,526 characters)2.Variable-lengt
33、h strings theoretically can hold as many as 2 billion characters DatesTo store dates, use the Date data typeIf you do, you will be able to perform calculations with the dates Assignment StatementsAn assignment statement is a VBA statement that assigns the result of an expression to a variable or an
34、objectExamples:x = 1x = x + 1x = (y * 2) / (z * 2) HouseCost = 375000 FileOpen = True Range(“TheYear”).Value = 2005 OperatorsThe precedence order for operators in VBA is exactly the same as in Excel formulasExponentiation has the highest precedenceMultiplication and division come nextThen addition a
35、nd subtraction OperatorsArithmetic operatorsAdd (+), subtract (), multiply (*), divide (/), exponentiate (), string concatenate (&)Logical operatorsNot, And, Or, XoR (exclusion), Eqv (equivalence), Imp (implication)DECISIONS, DECISIONS, DECISIONSVBA is a structured languageIt offers standard structu
36、red decision constructs such as:IfThen and Select Case structuresForNext, DoUntil, and DoWhile loops The IfThen StructureExecutes one or more statements based on some conditionFormatIf condition Then statements Else statementsExamplesIf Quantity = 75 Then Discount = 0.25If Quantity 10 Then Discount
37、= 0.1 Else Discount = 0.0The Select Case StructureUseful for decisions involving three or more optionsOften used for decisions that involve evaluating a number in a range (e.g., between 1 and 100, between 101 and 200, and so on)See example on page M.21 LoopingThe term looping refers to repeating a b
38、lock of statements or code numerous timesThere are several looping statements to choose from:The ForNext loopThe DoWhile loopThe DoUntil loop The ForNext LoopThe looping is controlled by a counter variable, which starts at one value and stops at another valueExample:Sub FillRange()Dim Count As Integ
39、er For Count = 1 To 100 ActiveCell.Offset(Count 1, 0) = Rnd Next CountEnd Sub DoWhile LoopA DoWhile loop continues until a specified condition is metExample:Sub DoWhileExample()Do While ActiveCell.Value Empty ActiveCell.Value = ActiveCell.Value * 2 ActiveCell.Offset(1, 0).SelectLoopEnd Sub DoUntil L
40、oopIn a DoUntil loop, the macro executes the loop until the condition is trueExample:Sub DoUntilExample()Do Until IsEmpty (ActiveCell.Value) ActiveCell.Value = ActiveCell.Value * 2 ActiveCell.Offset(l, 0).SelectLoopEnd Sub WRAP IT UPWRAP IT UP1.Open a new workbook2.Create a worksheet 3.Press Alt+F11
41、 to activate the VBE4.Click the new workbooks name in the Project Explorer window5.Choose Insert, then Module to insert a VBA module into the project WRAP IT UP6.Type the following code into the Code windowType this Type this codecodeWRAP IT UP7.Return to the Excel spreadsheet, make cell C10 the active cell and type in the formula =InvoiceAmount(A10, B10)8.Copy the formula to the remaining cells WRAP IT UP
