720 likes | 868 Views
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,
E N D
Week 6 – ABC By AurinoDjamaris Bakrie School of Management
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
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
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.
Error Handling • Anticipate this error and handle it. Example : Sub EnterSquareRoot () Error Handling 1 Error Handling 2
Handling Errors Another Way • adding an On Error statement to trap all errors and then checking to see whether the InputBox was cancelled.
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.
What Error and Err Number Trap Error Treat Error accordingly
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.
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
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
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
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.
the Debugger • set a breakpoint in your VBA code
Using the Immediate window • display the VBE’s Immediate window pressing Ctrl+G. • for finding the current value of any variable in your program.
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.
VBA Programming Examples • Working with ranges • Changing Excel settings • Working with charts • Speeding up your VBA code
Working with ranges • Copying a range • more efficiently
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
Selecting to the end of a row or column (cont) • Selecting a row or column • Moving a range • Looping through a range efficiently
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
Prompting for a cell value use VBA’s InputBox function to get a value from the user. or
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
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
Working with Charts • Modifying the chart type • Looping through the ChartObjects collection
Working with Charts (cont) • Modifying chart properties
Working with Charts (cont) • Applying chart formatting
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.
Using the With-End With structure • Use • Rather than
USER INTERFACES • Simple Dialog Boxes • UserForm Basics • UserForm Controls • UserForm Techniques and Tricks • Accessing Your Macros Through the User Interface
Simple Dialog Boxes • The MsgBox function • The InputBox function • The GetOpenFileName method • The GetSaveAsFileName method
MsgBox • MsgBox(prompt[, buttons][, title])
InputBox • InputBox(prompt[, title][, default])
GetOpenFileName • object.GetOpenFilename([fileFilter], [filterIndex], [title], [buttonText], [multiSelect])
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.
Getting a folder name allows the user to select a directory.
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.
Inserting a new UserForm • 1. Activate the VBE by pressing Alt+F11. • 2. Select the workbook in the Project window. • 3. Choose Insert➪UserForm.
Adding controls to a UserForm • You use the tools in the Toolbox to add controls to UserForm.
Changing properties for a UserForm control • Properties for controls include the following: • Name • Width • Height • Value • Caption
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.
Displaying a UserForm • The macro that displays the dialog box must be in a VBA module — not in the Code window for the UserForm.
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.
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.
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.