信息时代的管理信息系统Module_M课件

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 built-in 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

4、VBA?Create a VBA macro to format and print a month-end sales reportExecute the macro with a single commandTrigger Excel to automatically perform many time-consuming 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

5、 to 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

6、 new worksheet functions Creating complete, macro-driven 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 co

7、mputer 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

8、 another 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

9、 100 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 hier

10、archyExcel: 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 Basi

11、c macrosYou 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 a

12、nd the 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

13、the 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:Create a new wor

14、kbook in ExcelPress Alt+F11 to activate the VBESelect the projects name in the Project Explorer windowChoose Insert and then Module or you can use the shortcut, by using the right-mouse click, choosing Insert, and then Module Removing a VBA ModuleTo remove a VBA module from a project, follow these s

15、teps:Select the modules name in the Project Explorer windowChoose 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 instruct

16、ions 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:Entering the code directly by typing itUsing

17、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 to m

18、ake your code easier to read Entering Code DirectlyEntering Code DirectlyUsing the Macro RecorderActivate a worksheet in the workbookChoose Tools, then Macro, and then Record New MacroExcel displays its Record Macro dialog boxClick OK to accept the defaultsExcel automatically inserts a new VBA modul

19、e into the projectFrom this point on, Excel converts your actions into VBA code - while recording, Excel displays the word Recording in the status bar Using the Macro RecorderExcel displays a miniature floating toolbar that contains two toolbar buttons: Stop Recording and Relative Reference Choose T

20、ools, then OptionsClick the View tabRemove the check mark from the Gridlines optionClick OK to close the dialog boxClick the Stop Recording button Using the Macro RecorderRelative Reference ButtonStop Recording ButtonUsing the Macro RecorderUsing the Macro RecorderTry the macro:Activate a worksheet

21、that has gridlines displayedChoose Tools, then Macro, and then choose Macros, or press Alt+F8Select Macro1Click the Run buttonExcel executes the macro, and the gridlines disappear Using the Macro RecorderExecute the chosen macroRemove the chosen macroUsing the Macro RecorderAnother way to execute a

22、macro is to press its shortcut keyChoose Tools, then Macro, and then MacrosSelect the Macro1 Sub procedure name from the list boxClick the Options buttonClick the Shortcut Key option and enter a letter in the box labeled Ctrl + Click OK to close the Macro Options dialog box Using the Macro RecorderR

23、emove gridlinesVBA BUILDING BLOCKSThere are many ways to write a macro using Excel VBAWrite or record macros using modules or proceduresDevelop user-defined functions Code ModulesAll macros reside in code modules like the one on the right of the VBE windowThere are two types of code modules Standard

24、 modulesClass modules ProceduresIn VBA, macros are referred to as proceduresThere are two types of proceduresSub procedures Function proceduresThe macro recorder can only produce Sub procedures Sub ProceduresSub procedures (sometimes referred to as subroutines) start with the keyword Sub followed by

25、 the name of the procedure and opening and closing parenthesesThe end of a Sub procedure is marked by the keywords End Sub Sub ProceduresSub MonthNames()Range(“B1”).SelectActiveCell.FormulaR1C1 = “Jan”Range (“C1”).SelectActiveCell.FormulaR1C1 = “Feb”.End SubFunction ProceduresExcel has hundreds of b

26、uilt-in worksheet Function ProceduresChoose Insert, and then the Function command to see a list of those functionsIf the function you need is not already in Excel, you can write your own user defined function (or UDF) using VBA Function ProceduresA function procedure example:Function CentigradeToFah

27、renheit(Centigrade)CentigradeToFahrenheit = Centigrade * 9 /5 + 32End Function ELEMENTS OF VBA PROGRAMMINGVBA uses many elements common to all programming languages, such as: CommentsVariablesConstantsData types CommentsA comment is the simplest type of VBAVBA ignores these statementsExample:Sub Com

28、mentsExample() This procedure is a demonstrationx = 0 x represents zero The next line of code will display the resultMsgBox x End Sub Variables and ConstantsVBAs main purpose is to manipulate dataVBA stores the data in your computers memorySome data, such as worksheet ranges, reside in objects and o

29、ther data are stored in variables or constants VariablesVariable name of a storage locationExamplesx = 1 (assigns the value of 1 to the 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/2

30、004# (DateStarted is a date variable)ConstantsA variables value may (and usually does) change while your procedure is executingA constant is a named element whose value doesnt changeExample:Const NumQuarters As Integer = 4 Const Rate = .0725Const Period = 12 Const ModName As String = “Budget Macros”

31、 Data TypesData types are the manner in which data types are stored in memory - for example, as integers, real numbers, or stringsExamplesBooleanIntegerCurrencyDateStringStringsExcel and VBA can work with both numbers and textText is often referred to as a stringThere are two types of strings in VBA

32、:Fixed-length strings are declared with a specified number of characters (the maximum length is about 65,526 characters)Variable-length 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 t

33、he dates Assignment StatementsAn assignment statement is a VBA statement that assigns the result of an expression to a variable or an objectExamples:x = 1x = x + 1x = (y * 2) / (z * 2) HouseCost = 375000 FileOpen = True Range(“TheYear”).Value = 2005 OperatorsThe precedence order for operators in VBA

34、 is exactly the same as in Excel formulasExponentiation has the highest precedenceMultiplication and division come nextThen addition and subtraction OperatorsArithmetic operatorsAdd (+), subtract (-), multiply (*), divide (/), exponentiate (), string concatenate (&)Logical operatorsNot, And, Or, XoR

35、 (exclusion), Eqv (equivalence), Imp (implication)DECISIONS, DECISIONS, DECISIONSVBA is a structured languageIt offers standard structured decision constructs such as:If-Then and Select Case structuresFor-Next, Do-Until, and Do-While loops The If-Then StructureExecutes one or more statements based o

36、n some conditionFormatIf condition Then statements Else statementsExamplesIf Quantity = 75 Then Discount = 0.25If Quantity 10 Then Discount = 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 block of statements or code numerous timesThere are several looping