1 / 72

Week 6 – ABC

Week 6 – ABC. By Aurino Djamaris Bakrie School of Management. Error handling, Debugging and User Interface within Excel . Error handling Techniques Debugging creating dialog boxes, userform basics, using userform controls, and implement some techniques and tricks,

manchu
Download Presentation

Week 6 – ABC

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. Week 6 – ABC By AurinoDjamaris Bakrie School of Management

  2. Error handling, Debugging and User Interface within Excel • Error handling Techniques • Debugging • creating dialog boxes, • userformbasics, • using userform controls, and • implement some techniques and tricks, • using macro through user interface

  3. Error-Handling Techniques • Identifying errors • Doing something about the errors that occur • Recovering from errors • Creating intentional errors (Yes, sometimes an error can be a good thing.) write code that avoids displaying Excel’s error messages as much as possible

  4. Excel Error Messages • Excel responds with the message shown below, indicating that your code generated a run-time error Excel displays this error message when the Procedure attempts to Calculate the square root of a negative number.

  5. Error Handling • Anticipate this error and handle it. Example : Sub EnterSquareRoot () Error Handling 1 Error Handling 2

  6. Handling Errors Another Way • adding an On Error statement to trap all errors and then checking to see whether the InputBox was cancelled.

  7. On Error Problem? Using an On Error statement in your VBA code causes Excel to bypass its built-in error handling and use your own error-handling code.

  8. On Error Statements

  9. Resuming on Error

  10. Example: Resume after an error occurs

  11. What Error and Err Number Trap Error Treat Error accordingly

  12. An Intentional Error The Macro1 procedure (which must be in the same project as WorkbookOpen) calls the WorkbookOpen function and passes the workbook name (Prices.xlsx) as an argument.

  13. Bug Extermination Techniques • Defining a bug and why you should squash it • Recognizing types of program bugs you may encounter • Using techniques for debugging your code • Using the VBA built-in debugging tools

  14. The bugs categories • Logic flaws in your code. • Incorrect context bugs • Extreme-case bugs • Wrong data type bugs • Wrong version bugs • Beyond-your-control bugs

  15. Identifying Bugs • An error message like this often means that your VBA code contains a bug. • The best debugging approach is thorough testing, under a variety of real-life conditions

  16. Debugging Techniques Four most common methods for debugging Excel VBA code: • Examining the code: taking a close look at your code • Inserting MsgBox functions at various locations in your code: • MsgBoxLoopIndex & “ “ & CellCount • MsgBoxLoopIndex & vbNewLine & CellCount & vbNewLine & MyVal • MsgBoxActiveSheet.Name & “ “ & TypeName(ActiveSheet) • Inserting Debug.Printstatements • Debug.PrintLoopIndex, CellCount, MyVal • Using the Excel built-in debugging tools: • Excel includes a set of debugging tools that can help you correct problems in your VBA code.

  17. the Debugger • set a breakpoint in your VBA code

  18. Using the Immediate window • display the VBE’s Immediate window  pressing Ctrl+G. • for finding the current value of any variable in your program.

  19. Bug Reduction Tips • Use an Option Explicit statement at the beginning of your modules. • Format your code with indentation. • Be careful with the On Error Resume Next statement. • Use lots of comments. Nothing is more frustrating than revisiting code • Keep your Sub and Function procedures simple. • Use the macro recorder to help identify properties and methods. • Understand Excel’s debugger.

  20. VBA Programming Examples • Working with ranges • Changing Excel settings • Working with charts • Speeding up your VBA code

  21. Working with ranges • Copying a range • more efficiently

  22. Copying a variable-sized range or

  23. Selecting to the end of a row or column • use the CurrentRegion property to select an entire block of cells. • End method takes one argument  constants use: • xlUp • xlDown • xlToLeft • xlToRight

  24. Selecting to the end of a row or column (cont) • Selecting a row or column • Moving a range • Looping through a range efficiently

  25. Skip Blanks examples processing only the nonblank cells using the SpecialCells method. the selection’s subset that consists of cells with constants and the selection’s subset that consists of cells with formulas

  26. Prompting for a cell value use VBA’s InputBox function to get a value from the user. or

  27. Other selection methods Determining the selection type displays a message and exits the procedure if the current selection is not a Range object: Identifying a multiple selection determine whether the user made a multiple selection

  28. Changing Excel Settings • Changing Boolean settings • Changing non-Boolean settings the Not operator to effectively toggle the page break display toggles the calculation mode between manual and automatic

  29. Working with Charts • Modifying the chart type • Looping through the ChartObjects collection

  30. Working with Charts (cont) • Modifying chart properties

  31. Working with Charts (cont) • Applying chart formatting

  32. VBA Speed Tips • Turning off screen updating • Application.ScreenUpdating = False / True • Turning off automatic calculation • Application.Calculation = xlCalculationManual / xlCalculationAutomatic • Eliminating those pesky alert messages • Application.DisplayAlerts = False /True • Simplifying object references • defining this object variable, then define the value rather than defining this object variable and the value in single line • Declaring variable types • use the data type that requires the smallest number of bytes yet can still handle all the data assigned to it.

  33. Using the With-End With structure • Use • Rather than

  34. USER INTERFACES • Simple Dialog Boxes • UserForm Basics • UserForm Controls • UserForm Techniques and Tricks • Accessing Your Macros Through the User Interface

  35. Simple Dialog Boxes • The MsgBox function • The InputBox function • The GetOpenFileName method • The GetSaveAsFileName method

  36. MsgBox • MsgBox(prompt[, buttons][, title])

  37. InputBox • InputBox(prompt[, title][, default])

  38. GetOpenFileName • object.GetOpenFilename([fileFilter], [filterIndex], [title], [buttonText], [multiSelect])

  39. Selecting Multiple File

  40. GetSaveAsFileName • object.GetSaveAsFilename([initialFilename], [fileFilter],[filterIndex], [title], [buttonText]) The Excel GetSaveAsFilename method works just like the GetOpenFilename method, but it displays the Excel Save As dialog box rather than its Open dialog box. The GetSaveAsFilename method gets a path and filename from the user but doesn’t do anything with it.

  41. Getting a folder name allows the user to select a directory.

  42. UserFormBasics • Creating UserForms • Determine how the dialog box will be used • Press Alt+F11 to activate the VBE and insert a new UserForm object. • Add controls to the UserForm. • Use the Properties window to modify the properties for the controls or for the UserForm itself. • Write event-handler procedures for the controls (for example, a macro that executes when the user clicks a button in the dialog box). • Write a procedure (stored in a VBA module) that displays the dialog box to the user.

  43. Inserting a new UserForm • 1. Activate the VBE by pressing Alt+F11. • 2. Select the workbook in the Project window. • 3. Choose Insert➪UserForm.

  44. Adding controls to a UserForm • You use the tools in the Toolbox to add controls to UserForm.

  45. Changing properties for a UserForm control • Properties for controls include the following: • Name • Width • Height • Value • Caption

  46. Viewing the UserForm Code window • Every UserForm object has a Code module that holds the VBA code (the event-handler procedures) executed when the user works with the dialog box. To view the Code module, press F7. The Code window is empty until you add some procedures. Press Shift+F7 to return to the dialog box.

  47. Displaying a UserForm • The macro that displays the dialog box must be in a VBA module — not in the Code window for the UserForm.

  48. Using information from a UserForm • The VBE provides a name for each control you add to a UserForm. The control’s name corresponds to its Name property. Use this name to refer to a particular control in your code.

  49. A UserFormExample • Creating the UserForm • Press Alt+F11 to activate the VBE. • If multiple projects are in the Project window, select the project that corresponds to the workbook you’re using. • Choose Insert➪UserForm. • Press F4 to display the Properties window. • In the Properties window, change the dialog box’s Caption property to Change Case. • The dialog box is a bit too large, so you may want to click it and use the handles to make it smaller.

  50. Adding the CommandButtons • Make sure that the toolbox is displayed. If it isn’t, choose View➪Toolbox. • If the Properties window isn’t visible, press F4 to display it. • In the toolbox, drag a CommandButton into the dialog box to create a button. • Make sure that the CommandButton is selected. Then activate the Properties window and change the following properties: • Add a second CommandButton object to the UserForm and change thefollowingproperties: • Adjust the size and position of the controls so your dialog box looks something like Figure 16-5.

More Related