1 / 35

Exploring Microsoft Excel 2002

Exploring Microsoft Excel 2002. Chapter 10 Extending VBA: Processing Worksheets and Workbooks By Robert T. Grauer Maryann Barber. Objectives (1 of 2). Describe the Visual Basic Dir function Describe the Visual Basic Len function Describe the term error trapping

haven
Télécharger la présentation

Exploring Microsoft Excel 2002

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exploring Microsoft Excel 2002 Chapter 10 Extending VBA: Processing Worksheets and Workbooks By Robert T. Grauer Maryann Barber Exploring Microsoft Excel 2002 Chapter 10

  2. Objectives (1 of 2) • Describe the Visual Basic Dir function • Describe the Visual Basic Len function • Describe the term error trapping • explain how the On Error and Exit Sub statements are used with error trapping • Use the Forms toolbar to add a command button to execute a procedure Exploring Microsoft Excel 2002 Chapter 10

  3. Objectives (2 of 2) • Use the For…Next statement to process all of the worksheets in a specific workbook • Write the VBA statements to change the color of a worksheet tab • Explain the philosophy of “divide and conquer” in developing an application • explain how to call one VBA procedure from another Exploring Microsoft Excel 2002 Chapter 10

  4. Overview • Chapter introduces additional VBA statements that extend the capabilities of Excel applications • process all the worksheets in a workbook • process all the workbooks in a folder • Learn how to anticipate and trap errors • “trap” errors so that an error does not cause your application to “crash” • display useful error messages Exploring Microsoft Excel 2002 Chapter 10

  5. Characteristics of a Good Application • allows an end user to accomplish things he or she could not do through Excel • i.e., processing many workbooks and/or many worksheets at once • consists of many small, manageable tasks • the application “calls” the tasks • makes programming, testing and debugging much easier Exploring Microsoft Excel 2002 Chapter 10

  6. The Expense Summary Application • Define the requirements • Consolidate many Excel expense report workbooks into a single workbook • Review and approve each employee’s expense report • Summarize each employee’s expenses and approval status in a summary worksheet • Accomplish all this without having to copy and paste or link to individual workbooks Exploring Microsoft Excel 2002 Chapter 10

  7. The Expense Summary Application Employee Expense Statements copied from various workbooks Each tab represents a different employee and different workbook Exploring Microsoft Excel 2002 Chapter 10

  8. The Summary Workbook Approval status Expenses for all employees Multiple worksheets Figure 10.1(b) The Expense Summary Application Exploring Microsoft Excel 2002 Chapter 10

  9. A Quick VBA Review • MsgBox statement displays information • InputBox function prompts the user for information, then stores that information • Dim statement is used to declare (define) a variable • indicates name of variable and type • If…Then…Else enables decision making • Loops allow a series of statements to be executed repeatedly • For…Next statement • Do Until and Do While statements Exploring Microsoft Excel 2002 Chapter 10

  10. Processing Workbooks • Dir function returns the name of the first file that matches a specified character string • string should be the name of the folder you are searching • Len function returns the number of characters in a variable • If the value of Len = 0, this indicates an empty string Exploring Microsoft Excel 2002 Chapter 10

  11. Processing Workbooks • Putting them together: • Dir returns the name of the next workbook in the specified folder. • When there are no more workbooks in the folder, then Len(Dir) = 0 and the loop terminates • Provide an error trap rather than allowing the program to “crash” • If the folder is empty or does not exist: • Give the user a meaningful error message • Exit the procedure Exploring Microsoft Excel 2002 Chapter 10

  12. The Dir and Len Functions Dir function returns name of the first workbook in this folder Public Sub OpenAllWorkbooks() Dim strWorkbookName strWorkbookName = Dir("C:\Exploring Excel\Expense Statements\*.xls") If Len(strWorkbookName) > 0 Then Do While Len(strWorkbookName) > 0 Workbooks.Open Filename:= _ "C:\Exploring Excel\Expense Statements\" & strWorkbookName Sheets(1).Select Sheets(1).Copy After:=Workbooks("Expense Summary Solution.xls").Sheets(1) Workbooks(strWorkbookName).Close strWorkbookName = Dir Loop Else MsgBox "Error - The folder C:\Exploring Excel\Expense Statements " _ & "does not exist and/or there are no workbooks in the folder. " _ & "Please check the folder name and then rerun the macro.", _ vbCritical, ApplicationTitle End If End Sub Len function returns the number of characters in the name of the workbook Loop these statements as long as there are workbooks in this folder (as long as Len(strWorkbookName)>0) Error trapping in case the folder is not there and/or contains no workbooks Figure 10.1(b) The Expense Summary Application

  13. Hands-On Exercise 1 • Objective: Run a VBA procedure to combine worksheets from multiple workbooks into a single workbook • Open the Expense Statement Workbook • Understanding the Workbook • The Expense Statements Folder • Run the Open All Workbooks Procedure • Debugging • The Expense Summary Workbook Exploring Microsoft Excel 2002 Chapter 10

  14. Error Trapping • A good application anticipates (traps) runtime errors • runtime errors occur while a procedure is being executed • syntax errors occur before a procedure is executed • On Error statement • transfers control to a special error-handling section of the procedure • Error-handling section often contains a useful error message and an Exit Sub statement to end the procedure Exploring Microsoft Excel 2002 Chapter 10

  15. An Error Trap On Error statement Error-handling section Exploring Microsoft Excel 2002 Chapter 10

  16. Hands-On Exercise 2 • Objective: Develop a VBA procedure to display a specific worksheet within a workbook; include the appropriate error-trapping statements to display a meaningful message if the worksheet does not exist. • Start the Macro Recorder • Modify the Procedure • Test the Procedure • Add the Error Handling • Create a Command Button • Test the Command Button Exploring Microsoft Excel 2002 Chapter 10

  17. Processing Worksheets in a Workbook • Develop twoprocedures: • one to examine individual worksheets in order to determine if expenses are approved or need to be reviewed • one to copy the total expense from each worksheet to a summary worksheet • Use pseudocode to develop the logic for each procedure Exploring Microsoft Excel 2002 Chapter 10

  18. Using PseudocodeProcedure: Process Worksheets • Input limit for automatic approval • For each worksheet in the workbook: • If this is an employee worksheet: • Unprotect the worksheet • If expenses <= limit • Approve expenses and set worksheet tab to blue • Else • Review expenses and set worksheet tab to red • End If • Protect the worksheet • End If • Next worksheet Exploring Microsoft Excel 2002 Chapter 10

  19. Review Employee Expenses Input Box prompts user; response stored in memory For…Next loop executes until all worksheets have been processed Exploring Microsoft Excel 2002 Chapter 10

  20. Using PseudocodeProcedure: Summary Worksheet • Select the cell for the first employee • For each worksheet: • If this is an employee worksheet: • Store the name of the worksheet • Select the active cell in the summary worksheet • Reference cell D4 in the employee worksheet • Move one column to the right (on summary) • Reference total expenses in the employee worksheet • Move one column to the right (on summary) Exploring Microsoft Excel 2002 Chapter 10

  21. Summary Worksheet Pseudocode (2 of 2) • Reference the approval status in the employee worksheet • Move down one row and two columns to the left (for the next employee) • Insert a blank row to update the Sum function • End if • Next Worksheet • Delete extra blank row after all employees have been processed • Sort the employees in alphabetical order Exploring Microsoft Excel 2002 Chapter 10

  22. Create Summary Worksheet For…Next loop copies values from worksheets to Summary worksheet and inserts a blank line Use Offsets to move to cells Exploring Microsoft Excel 2002 Chapter 10

  23. Hands-on Exercise 3 • Objective: Create two procedures to process the worksheets in a workbook • Start the Macro Recorder • Loops and Decision Making • Complete the Procedure • Step Through a Procedure • Check Your Progress • Complete the Procedure • Test the Completed Procedure • Print the Cell Formulas Exploring Microsoft Excel 2002 Chapter 10

  24. A Better Summary Workbook • Create a procedure that calls the other three procedures • Procedures have a scope: • Public procedures are available to any procedure in any module • Private procedures are available only to other procedures in the same module • Consider what happens if a procedure is unsuccessfully executed • If the first procedure is unsuccessful, there is no point in running the other procedures • Use the InputBox() function to allow the user to enter the folder where the workbooks are located Exploring Microsoft Excel 2002 Chapter 10

  25. Improved Summary Workbook InputBox function allows user to enter the folder with the worksheets; response stored in variable strPathName If the first procedure is successful, enter the loop; otherwise, give the error message Message Box provides a useful error message Exploring Microsoft Excel 2002 Chapter 10

  26. User Inputs Name of Folder InputBox caption provides an example of how to enter a path. This reduces the chance of a user error Exploring Microsoft Excel 2002 Chapter 10

  27. Helpful Error Messages Meaningful error message appears if the user typed an invalid folder Exploring Microsoft Excel 2002 Chapter 10

  28. Hands-on Exercise 4 • Objective: Use a modified version of the Expense Summary workbook with additional flexibility and automation • Open the Better Workbook • Event Procedures • Test the Create Summary Workbook Procedure • Find an Employee’s Worksheet • Exit the Application Exploring Microsoft Excel 2002 Chapter 10

  29. Summary (1 of 2) • Use VBA statements that do not have equivalent Excel menu commands • Examples include InputBox, If/Then/Else, and For…Next • Allows the user to interact with the application • Provides greater processing capability and make application appear to have intelligence • Visual Basic Dir function locates files in a specified folder • Visual Basic Len function returns the length of a character string Exploring Microsoft Excel 2002 Chapter 10

  30. Summary (2 of 2) • Write procedures that call smaller procedures • Easier to code and debug • Smaller procedures can be reused in other procedures • Scope of a procedure refers to its availability or use by another procedure • Anticipate and trap errors • Provide meaningful error messages • If procedure is a function, return a value that indicates the procedure failed Exploring Microsoft Excel 2002 Chapter 10

  31. Finding the Month and Day Weekday() function returns the weekday; VLookup turns the value (4) into the day (Wednesday) Month() function returns the month; VLookup turns the value (3) into the month (March) Figure 10.11 Finding the Month and Day (Exercise 1) Exploring Microsoft Excel 2002 Chapter 10

  32. A Puzzle For You Figure 10.12 A Puzzle for You (Exercise 2) Exploring Microsoft Excel 2002 Chapter 10

  33. The Better Summary Workbook Figure 10.16 Print the VBA Procedures in Microsoft Word (Exercise 6) Exploring Microsoft Excel 2002 Chapter 10

  34. The Grade Book Application Figure 10.19 Locate a Student (Exercise 9) Exploring Microsoft Excel 2002 Chapter 10

  35. The Excel Object Model Figure 10.20(b) The Worksheet Object (Exercise 10) Exploring Microsoft Excel 2002 Chapter 10

More Related