Bài giảng Management information systems - Module M: Programming in Excel with VBA

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 application 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 WHY 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 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 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 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 computer code that performs some action on or with objects VBA in a NutshellSub procedure example: Sub Demo() Sum = 1 + 1 MsgBox “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 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 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 hierarchyExcel: 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 macrosYou can't 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 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 the VBE, each Excel workbook that's 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 workbook in ExcelPress Alt+F11 to activate the VBESelect the project's 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 steps:Select the module's 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 instructions 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 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 make 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 module 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 con­tains two toolbar buttons: Stop Recording and Relative Reference Choose Tools, 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 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 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 RecorderRemove 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 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 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”).Select ActiveCell.FormulaR1C1 = “Jan” Range (“C1”).Select ActiveCell.FormulaR1C1 = “Feb” . . .End SubFunction ProceduresExcel has hundreds of built-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 CentigradeToFahrenheit(Centigrade) CentigradeToFahrenheit = Centigrade * 9 /5 + 32 End 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 CommentsExample() ‘ This procedure is a demonstration x = 0 ‘ x represents zero ‘ The next line of code will display the result MsgBox x End Sub Variables and ConstantsVBA's main purpose is to manipulate dataVBA stores the data in your computer's 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 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 variable's value may (and usually does) change while your procedure is executingA constant is a named element whose value doesn't changeExample: Const NumQuarters As Integer = 4 Const Rate = .0725 Const 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 stringsExamplesBooleanIntegerCurrencyDateStringStringsExcel and VBA can work with both numbers and textText is often referred to as a stringThere are two types of strings in VBA: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 the dates Assignment StatementsAn assignment statement is a VBA statement that assigns the result of an expression to a variable or an objectExamples: x = 1 x = x + 1 x = (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 and 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 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 on some conditionFormatIf condition Then statements [Else statements]Examples If Quantity >= 75 Then Discount = 0.25 If 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 statements to choose from:The For-Next loopThe Do-While loopThe Do-Until loop The For-Next LoopThe looping is controlled by a counter variable, which starts at one value and stops at another valueExample: Sub FillRange() Dim Count As Integer For Count = 1 To 100 ActiveCell.Offset(Count - 1, 0) = Rnd Next Count End Sub Do-While LoopA Do-While loop continues until a specified condition is metExample: Sub DoWhileExample() Do While ActiveCell.Value Empty ActiveCell.Value = ActiveCell.Value * 2 ActiveCell.Offset(1, 0).Select Loop End Sub Do-Until LoopIn a Do-Until 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).Select Loop End Sub WRAP IT UPWRAP IT UPOpen a new workbookCreate a worksheet Press Alt+F11 to activate the VBEClick the new workbook's name in the Project Explorer windowChoose Insert, then Module to insert a VBA module into the project WRAP IT UPType the following code into the Code windowType this codeWRAP IT UPReturn to the Excel spreadsheet, make cell C10 the active cell and type in the formula =InvoiceAmount(A10, B10)Copy the formula to the remaining cells WRAP IT UP