Extended Learning Module M - Programming in Excel with VBA
EXTENDED LEARNING MODULE M
PROGRAMMING IN EXCEL WITH VBA
JUMP TO THE SUPPORT YOU WANT ? Lecture Outline
? Modules, Projects, and Data Files ? Slide Reviews
? Assignments and Exercises
CONTACT INFORMATION: Stephen Haag (shaag@du.edu)
STUDENT LEARNING OUTCOMES 1. 2. 3. 4. 5. 6. 7.
Explain the value of using VBA with Excel. Define a macro.
Build a simple macro using a Sub procedure and a Function procedure. Describe an object.
Explain the difference between a comment, a variable, and a constant. List the various Visual Basic Application data types and operators.
Describe and build a macro that uses If-Then-Else, For-Next, Do-Until, Do-While, and Select Case Structures.
MODULE SUMMARY This Extended Learning Module teaches your students how to use VBA and write programming structures in Excel.
The primary sections of this module include: 1. Why VBA?
2. The Visual Basic Editor 3. VBA Building Blocks
4. Elements of VBA Programming 5. Decisions, Decisions, Decisions 6. Wrap It Up
Mod M-1
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
LECTURE OUTLINE
INTRODUCTION (p. M.2)
WHY VBA? (p. M.2) 1. VBA in a Nutshell
THE VISUAL BASIC EDITOR (p. M.5) 1. The VBE Tools
2. Working with the Project Explorer 3. VBA Module Code
VBA BUILDING BLOCKS (p. M.13) 1. Code Modules 2. Procedures
ELEMENTS OF VBA PROGRAMMING (p. M.15) 1. Comments
2. Variables and Constants 3. Data Types
4. Assignment Statements 5. Operators
DECISIONS, DECISIONS, DECISIONS (p. M.20) 1. The If-Then Structure 2. The Select Case Structure 3. Looping
WRAP IT UP (p. M.24)
END OF MODULE (p. M.27)
1. Summary: Student Learning Outcomes Revisited 2. Key Terms and Concepts 3. Assignments and Exercises
Back to Jump List
Mod M-2
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
MODULES, PROJECTS, AND DATA FILES
There is no specific Group Projects associated with this chapter. However, many of the Group Projects that use Excel can easily be modified to utilize VBA.
DATA FILES
There are several data files associated with this chapter. ? XLMM_SalesTax.xls ? XLMM_Shipping.xls ? XLMM_Grades.xls ? XLMM_CubeRoot.xls
Back to Jump List
Mod M-3
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
? ? These are the Student Learning Outcomes for the module. Use them as a road map to inform your students of what you will be covering. At the end of the module is a summary of each SLIDE 2 ? ? ? ? SLIDE 3 These are the Student Learning Outcomes for the module. Use them as a road map to inform your students of what you will be covering. At the end of the module is a summary of each. ? ? SLIDE 4 This slide presents the organization for the module. It identifies the major sections and learning outcomes associated with each. ? ? ? SLIDE 5 This slide formally defines what VBA is. VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft. Make sure students are aware that VBA can be used with many other applications, not just Excel. ? ? SLIDE 6 This slide continues to define the uses of VBA. Ask students if they have ever used macros before, and if so ask them what they used the macro to do. Mod M-4
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
? ? This slide defines what the macro recorder is. The macro recorder is a software tool that will let you record a sequence of commands in Excel and save them as a macro. SLIDE 7 ? This slide begins a series of slides discussing why we use VBA. (Student Learning Outcome #1) ? A macro language is a programming language that includes built-in commands that mimic the functionality available from menus and dialog boxes within an application. ? A macro is a set of actions recorded or written by a user. (Student Learning Outcome #2) ? ? This slide suggests one use of why we create macros. Have students give examples, not mentioned in the text, of how they would use a macro. SLIDE 8 SLIDE 9 ? This slide continues our discussion on the reasons we use VBA. ? If you are able to perform an operation manually, you can use the macro recorder to capture that operation. ? You can use VBA to create your own worksheet functions, if you have a complex calculation that you use frequently that is not included in the set of standard Excel functions. ? This slide lists the common uses for VBA macros. o Inserting text o Automating a task o Automating repetitive tasks o Creating a custom command SLIDE 10 SLIDE 11 Mod M-5
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
? This slide continues the discussion on the common uses for VBA macros. o Creating a custom toolbar button o Creating a custom menu command o Creating a simplified front end o Developing new worksheet functions o Creating complete, macro-driven applications This slide starts a series of slides on what the VBE is. You view and edit VBA macros using the Visual Basic Editor (VBE). SLIDE 12 ? ? SLIDE 13 ? ? SLIDE 14 This slide talks about Sub procedures. A Sub procedure is computer code that performs some action on or with objects. ? ? This slide illustrates an example of a Sub procedure. (Student Learning Outcome #3) Here is an example called Demo: Sub Demo() Sum = 1 + 1 MsgBox “The answer is ” & Sum End Sub SLIDE 15 SLIDE 16 ? This slide talks about Function procedures. ? A Function procedure is a VBA macro that returns a single value. ? You can call it from another VBA macro or even use it as a function in a worksheet formula. Mod M-6
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
SLIDE 17 ? This slide illustrates an example of a Function procedure. ? Function procedure example: Function AddTwo(arg1, arg2) AddTwo = arg1 + arg2 End Function SLIDE 18 ? ? ? ? This slide talks about objects. (Student Learning Outcome #4) An object in VBA is an item available for you to control in your code. Excel provides more than 100 objects that you can manipulate. Examples of objects include a workbook, a worksheet, a cell range, a chart, and a shape. SLIDE 19 ? This slide talks about variables. ? A variable is a place to store a piece of information. ? You can use variables in a VBA macro to store such things as values, text, or property settings. ? This slide illustrates Figure M.1 on page M.4, VBA added terminology and examples. SLIDE 20 ? ? SLIDE 21 This slide introduces the Visual Basic Editor (VBE). The Visual Basic Editor (VBE) is a separate application where you write and edit your Visual Basic macros. ? You can't run the VBE separately; Excel must be running in order for the VBE to operate. Mod M-7
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
SLIDE 22 ? This slide discusses the ways to activate the VBE. ? Use Alt+F11 to toggle between Excel and the VBE ? Or from the menu bar, choose Tools, Macro, and then Visual Basic Editor SLIDE 24 ? This slide lists the various VBE tools. ? VBE has even more parts than are shown in Figure M.2, but for the sake of simplicity, this module will only talk about what is currently visible. ? The tools mentioned in this slide include: o Menu Bar o Toolbar o Project Explorer Window o Code Window o The Properties Window o The Immediate Window ? This slide illustrates Figure M.2, the VBE toolbar. ? Again, remind students that their toolbar may be slightly different, but what this Figure displays the typical icons/functions. SLIDE 23 ? ? ? ? ? This slide discusses the Project Explorer. When you're working in the VBE, each Excel workbook that's open is a project. Think of a project as a collection of objects arranged as an outline. To expand a project, click the plus sign (+) at the left of the project's name in the Project Explorer window. To contract a project click the minus sign (-) to the left of a project's name. This slide discusses how to add a new VBA module. 1. Create a new workbook in Excel 2. Press Alt+F11 to activate the VBE 3. Select the project's name in the Project Explorer window 4. Choose Insert and then Module or you can use the shortcut, by using the right-mouse click, choosing Insert, and then Module SLIDE 25 ? SLIDE 26 Mod M-8
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
? SLIDE 27 This slide discusses how to delete a VBA module. o Select the module's name in the Project Explorer window. o Choose File, and then Remove ModuleName (where ModuleName is the name of the module). ? This slide discusses creating a module. ? VBA module can hold several types of code: o Sub procedures - A set of programming instructions that performs some action. o Function procedures - A set of programming instructions that returns a single value. o Declarations - One or more information statements that you provide to VBA. ? ? ? This slide discusses VBA module code. Before you can do anything meaningful, you must have some VBA code in the VBA module. You can get VBA code into a VBA macro in two ways: 1. Entering the code directly by typing it. 2. Using the Excel macro recorder to record your actions and convert them to VBA code. This slide shows discusses how the enter VBA code directly in a module. Students can type code directly into the module. They can select, copy, cut, paste, and do other things to the text. Use the Tab key to indent some of the lines to make the code easier to read. SLIDE 29 SLIDE 28 ? ? ? ? SLIDE 30 ? This slide illustrates Figure M.3 on page M.8, the code window. SLIDE 31 Mod M-9
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
SLIDE 32 ? This slide illustrates Figure M.4 on page M.9, the run code executed. ? VBE may make some adjustments to the text you enter, and may not look identical to the figure. ? This slide discusses using the macro recorder. 1. Activate a worksheet in the workbook. 2. Choose Tools, then Macro, and then Record New Macro. 3. Excel displays its Record Macro dialog box. 4. Click OK to accept the defaults. 5. Excel automatically inserts a new VBA module into the project that corresponds to the active workbook. 6. From this point on, Excel converts your actions into VBA code - while recording, Excel displays the word Recording in the status bar. ? This slide continues the discussion using the macro recorder. 7. Excel displays a miniature floating toolbar that contains two toolbar buttons: Stop Recording and Relative Reference. 8. Choose Tools, then Options. Excel displays its Options dialog box. 9. Click the View tab. 10. Remove the check mark from the Gridlines option. ? If the worksheet you're using has no gridlines, put a check mark next to the Gridlines option. 11. Click OK to close the dialog box. 12. Click the Stop Recording button on the miniature toolbar - Excel stops recording your actions. ? This slide illustrates Figure M.5 on page M.10, recording message and stop recording button. SLIDE 35 SLIDE 34 SLIDE 33 ? This slide illustrates Figure M.6 on page M.11, module1 macro code. SLIDE 36 Mod M-10
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
? This slide continues the discussion on using the macro recorder. 1. Activate a worksheet that has gridlines displayed. 2. Choose Tools, then Macro, and then choose Macros, or press Alt+F8. 3. Select Macro1. 4. Click the Run button. 5. Excel executes the macro, and the gridlines disappear. SLIDE 37 ? This slide illustrates Figure M.7 on page M.11, the macro dialog box. SLIDE 38 ? This slide continues the discussion on using the macro recorder. ? Here is another way to execute a macro. 1. Choose Tools, then Macro, and then Macros. 2. Select the Macro1 Sub procedure name (that was created in the previous step) from the list box. 3. Click the Options button. 4. Click the Shortcut Key option and enter a letter in the box labeled Ctrl +. 5. Click OK to close the Macro Options dialog box. ? This slide illustrates Figure M.8 on page M.12, display gridlines macro. SLIDE 40 SLIDE 39 SLIDE 41 ? This slide starts the discussion on the various ways to write a macro using Excel VBA. ? You can write or record macros using modules or procedures or they can develop user-defined functions.
Mod M-11
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
? This slide discusses code modules. ? All macros reside in code modules like the one on the right of the VBE window. ? There are two types of code modules: 1. Standard modules 2. Class modules ? You can use class modules to create your own objects (which is beyond the scope of this module). ? This slide discusses procedures. ? In VBA, macros are referred to as procedures. ? There are two types of procedures: 1. Sub procedures 2. Function procedures ? The macro recorder can only produce Sub procedures. SLIDE 42 SLIDE 43 ? This slide discusses Sub procedures. ? Sub procedures (sometimes referred to as subroutines) start with the keyword Sub followed by the name of the procedure and opening and closing parentheses. ? The end of a Sub procedure is marked by the keywords End Sub. ? The code within the Sub procedure is indented to make it stand out from the start and end of the Sub procedure, so that the code is easier to read. ? This slide discusses a Sub procedure example. ? Have students look at the code in MonthNames Sub procedure (page M.14), they will see that cells are being selected and then the month names are assigned to the active cell formula. SLIDE 45 SLIDE 44 ? This slide discusses Function procedures. ? Excel has hundreds of built-in worksheet Function procedures that you can use in cell formulas. ? You can select an empty worksheet cell and choose the Insert, and then the Function command to see a list of those functions. o If the function that is need is not already in Excel, you can write their own user defined function (or UDF) using VBA. SLIDE 46
Mod M-12
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
? This slide discusses function procedures. ? Unlike manual operations, UDFs cannot be recorded. ? You will have to write UDFs from scratch using a standard module in the VBE. ? If necessary, you can insert a standard module by right-clicking in the Project Explorer window and choosing Insert, then Module. ? SLIDE 47 ? This slide starts a discussion on the common elements of VBA programming. (Student Learning Outcome #5) ? VBA uses many elements common to all programming languages, such as: o Comments o Variables o Constants o Data types ? ? ? ? ? This slide discusses comments. A comment is the simplest type of VBA statement. Begin a comment with a single quote (‘). VBA ignores any text that follows an apostrophe in a line of code. Because VBA ignores these statements, they can consist of anything. SLIDE 49 SLIDE 48 SLIDE 50 ? ? ? This slide discusses variables and constants. VBA's main purpose is to manipulate data. VBA stores the data in a computer's memory, where some data, such as worksheet ranges, reside in objects and other data are stored in variables or constants that are created. SLIDE 51 ? ? ? ? ?
This slide discusses variables. A variable is the name of a storage location. Make sure students know that there are lots of flexibility in naming variables. Remind students to make the variable names as descriptive as possible. Variable names can be no longer than 254 characters (although it is not recommended to use more than 20 characters because they become hard to read). Mod M-13
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
? ? This slide discusses constants. A variable's value may (and usually does) change while your procedure is executing. A constant is a named element whose value doesn't change. Using constants in place of hard-coded values or strings (i.e. something other than a value) is an excellent programming practice since it's better to declare the value as a constant and refer to its name rather than the value. This slide discusses data types. (Student Learning Outcome #6) Data types are the manner in which data types are stored in memory - for example, as integers, real numbers, or strings. Figure M.9 on page M.17, VBA built-in data types. SLIDE 52 ? ? ? ? ? SLIDE 53 ? ? ? ? ? ? ? ? ? ? ? ? This slide discusses strings. Excel and VBA can work with both numbers and text. Text is often referred to as a string. There are two types of strings in VBA: 1. Fixed-length strings 2. Variable-length strings Make students aware that so far in this module they have been creating variables simply by using them. This is referred to as implicit variable declaration. Most computer languages require us to employ explicit variable declaration. This means that you must define the names of all the variables you are going to use, before you use them in your code. VBA allows both types of declaration. This slide discusses dates. Although you can use a string variable to store dates, it is recommended that you use the Date data type. If you do, you will be able to perform calculations with the dates. A variable defined as a date can hold dates ranging from January 1, 0100 to December 31, 9999. SLIDE 55 SLIDE 54 SLIDE 56 ? This slide discusses assignment statements. ? An assignment statement is a VBA statement that assigns the result of an expression to a variable or an object.
Mod M-14
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
? This slide discusses operators. ? The precedence order for operators in VBA is exactly the same as in Excel formulas. ? Exponentiation has the highest precedence. ? Multiplication and division come next, followed by addition and subtraction. ? Use parentheses to change the natural precedence order, making whatever's sandwiched in parentheses come before any operator. ? This slide illustrates Figure M.10 page M.19, VBA operators. SLIDE 57 SLIDE 58 ? ? ? This slide starts a discussion how to use VBA to make decisions. (Student Learning Outcome #7) VBA is a structured language. VBA offers standard structured decision constructs such as: o If-Then and Select Case structures o For-Next, Do-Until, and Do-While loops SLIDE 59 SLIDE 60 ? This slide discusses the If-Then structure. ? The If-Then statement is VBA's most important control structure. ? Each If-Then statement in this Sub procedure is executed and the value for Discount can change as the statements are executed. ? The procedure ultimately displays the correct value for Discount. SLIDE 61 ? This slide discusses the Select Case structure. ? The Select Case structure is useful for decisions involving three or more options. ? Any number of statements can follow each Case statement, and they all are executed if the case is true. Mod M-15
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
? ? This slide displays looping. The term looping refers to repeating a block of statements or code numerous times. You may know how many times your macro needs to loop, or variables used in your programs may determine this. There are several looping statements to choose from: o The For-Next loop o The Do-While loop o The Do-Until loop This slide discusses the For-Next loop. The looping is controlled by a counter variable, which starts at one value and stops at another value. Count (the loop counter variable) starts with a value of 1 and increases by 1 each time through the loop. Because a Step value is not specified, VBA uses the default value (which is 1). The Offset method uses the value of Count as an argument. The first time through the loop, the procedure enters a number into the active cell offset by zero rows. The second time through (Count = 2), the procedure enters a number into the active cell offset by one row (Count -1), and so on. This slide discusses the Do-While loop. A Do-While loop continues until a specified condition is met. This procedure uses the active cell as a starting point and then travels down the column, multiplying each cell's value by 2. The loop continues until the procedure encounters an empty cell. SLIDE 62 ? ? ? ? ? SLIDE 63 ? ? ? ? ? ? ? ? SLIDE 64 ? ? ? SLIDE 65 This slide discusses the Do-Until loop. In a Do-Until loop, the macro executes the loop until the condition is true This example is the same one presented for the Do-While loop (on the previous slide) but recoded to use a Do-Until loop. SLIDE 66 ? This slide starts a discussion on how to put all the concepts together from the earlier sections in this module. ? Figure M.11 on page M.24 illustrates a VBA example. Mod M-16
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
? This slide has students build a VBA worksheet from scratch. 1. Open a new workbook 2. Create a worksheet 3. Press Alt+F11 to activate the VBE 4. Click the new workbook's name in the Project Explorer window 5. Choose Insert, then Module to insert a VBA module into the project ? If you want, have students download the solution, by connecting to the Web site that supports this text (http://www.mhhe.com/haag select XLM/M) and select the file called XLMM_DiscountSales.xls. ? This slide continues with building a VBA worksheet from scratch. 6. Type the following code into the Code window ? Figure M.12 on page M.25 illustrates the VBA code. SLIDE 67 SLIDE 68 ? This slide continues with building a VBA worksheet from scratch. 7. 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 ? If all your variables and functions are correct, the worksheet should look like the one of the following slide SLIDE 69 ? This slide continues with building a VBA worksheet from scratch. ? Figure M.13 illustrates the final figures from the macro. SLIDE 70
Back to Jump List
Mod M-17
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
ASSIGNMENTS & EXERCISES (p. M.28)
1. Automating Repetitive Tasks. Once a week you have to develop a new worksheet for your department head that inserts enrollment data. More specifically most of the tasks that you perform in creating the worksheet are very repetitive since the structure of the worksheet is always the same. You want to automate the steps that are repetitious. Using the Macro Recorder, create a macro that types six month names as three letter abbreviations, “Jan” to “Jun,” across the top of a worksheet, starting in cell B1. Make each abbreviate bold, italics, and centered within each cell. Call the macro MonthNames and assign the macro the shortcut key Ctrl+Shift+M. Save the workbook as MonthNames.xls. Open a new worksheet, and press Ctrl+Shift+M.
DISCUSSION: ? Start with an empty worksheet with cell A1 selected.
? If you like to work with toolbars, use View, Toolbars to select and display the Visual Basic toolbar.
? Press the Record Macro button, with the red dot, to start the recorder.
? If you prefer, start the recorder with Tools, Macro, Record New Macro . . . from the Worksheet menu bar.
? In the Macro name: box, replace the default entry, such as Macro1, with MonthNames. ? In the Shortcut key box, type in M (upper case).
? When you have filled in the Record Macro dialog box, click the OK button. ? You should now click cell B1 and type in “Jan” and fill in the rest of the cells.
? Stop the recorder by pressing the Stop Recording button on the Stop Recording toolbar.
Mod M-18
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
2. Calculate Tax Values. You are part of a programming team developing point-of-scale terminal software, but first you want to create a prototype of the logic this software needs to perform. For your prototype, you decide to create a macro function in Excel that will calculate the sales tax (4.9%) from the data file, XLMM_SalesTax.xls.
DISCUSSION ? Open the workbook XLMM_SalesTax.xls . ? Press Alt+F11 to activate the VBE.
? Click the workbook’s name in the Project Explorer window. ? Choose Insert, Module to insert a VBA module into the project. ? Type the following code into the module: Function Tax(Sale) As Double
If Sale > 0 Then Tax = 0.049 * Sale Else Tax = 0 End Function
? Save and close the VBE window.
? In cell D5, enter the following formula: =Tax(C5)
? Copy the formula down to the remaining cells. ? In cell E5, enter the following formula: =D5+C5
? Copy the formula down to the remaining cells.
Mod M-19
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
3. Determine Shipping Charges. Trans-Port Inc., a distribution company located in Denver, Colorado, needs some assistance in computing the shipping charges for freight. The shipping charge is calculated by the total weight of the shipment. Any shipment with a total weight of 500 or over is computed by taking the total weight and multiplying it by $1.00. Any shipment with a total weight of 100 pounds or more, but less than 500 pounds is calculated by taking the total weight and multiplying it by .50. Anything shipped below 100 pounds is assessed a flat fee of $100. The owner of Trans-Port, Inc., Jake Plummer, has asked you to assist him in creating a macro function that will automatically assign shipping charges.
DISCUSSION ? Open the workbook XLMM_Shipping.xls . ? Press Alt+F11 to activate the VBE.
? Click the workbook’s name in the Project Explorer window. ? Choose Insert, Module to insert a VBA module into the project. ? Type the following code into the module: Function Shipping(Weight) As Double 'Calculate the discount percentage If Weight >= 500 Then Shipping = Weight * 1 ElseIf Weight >= 100 Then Shipping = Weight * 0.5 Else
Shipping = 100 End If
End Function
? Save and close the VBE window.
? In cell D2, enter the following formula: =Shipping(C2)
? Copy the formula down to the remaining cells.
Mod M-20
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module M - Programming in Excel with VBA
4. Assess the Letter Grade. You are a Teaching Assistant for the Information Technology department at your school. One of the professors, Dr. Hans Hultgren, has asked you to review a grading spreadsheet for him and write a macro function that will take the numerical score of each student and assign a letter grade. The grading scale is as follows: Numerical Range Letter Grade
90-100 A 80-89 B 70-79 C 65-69 D < 65 F
Dr. Hultgren has suggested that you write a macro that uses a Select Case statement. He has provided you with some mocked-up data, XLMM_Grades.xls, to use as a prototype, since giving you “real” grades is considered unethical.
DISCUSSION ? Open the workbook XLMM_Grades.xls. ? Press Alt+F11 to activate the VBE.
? Click the workbook’s name in the Project Explorer window. ? Choose Insert, Module to insert a VBA module into the project. ? Type the following code into the module: Function AssignGrade(StudScore As Single) Select Case StudScore Case 90 To 100
AssignGrade = \ Case 80 To 89
AssignGrade = \ Case 70 To 79
AssignGrade = \ Case 65 To 69
AssignGrade = \ Case Else
AssignGrade = \ End Select End Function
? Save and close the VBE window.
? In cell C2, enter the following formula: =AssignGrade(B2)
? Copy the formula down to the remaining cells.
Back to Jump List
Mod M-21
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
信息时代的管理系统 - 英文原版教学手册 - Module - M - 图文
