Debugging and Error Handling

1
Debugging andError Handling

• 11-09-05

2
Debugging

• Syntax Errors
• Usually easy to spot and fix
• Runtime Errors
• Harder to spot and fix
• Div by zero
• Logic Errors
• Hardest to find and repair
• Frequently you dont know you have a problem

3
Error Dialog Box

• Three Options
• Help usually not very helpful
• End doesnt solve the problem
• Debug
• Move the cursor over variables and see their
  current values
• Make adjustments to the program
• Continue execution
• Best to interpret run-time errors with caution

4
Debugger

• Toolbar
• Start, Pause, Stop
• Toggle breakpoints
• Data tips
• Yellow arrow
• Stop statement
• Step
• Into
• Over
• Out

5
Debugging Windows

• Immediate
• Print (27 398) 414
• Drag text from the code window
• Print variable
• Debug.print

6
Debugger Windows

• Locals
• Look at variable
• Change their value
• Click twice, not a double click
• Default shows only variables in current routine

7
Debugging Windows

• Watch
• Essential the same as local but you pick which
  variables to include
• Adding watch expressions
• Dragging the selection to the watch window
• Right-clicking the selection
• Debug ? Add Watch
• Quick Watch Button

8
Error Handling

• Prevent VBA from dealing with run-time errors
• Add error-handler to each procedure
• VBA transfers execution to the error-handler
• Code in handler figures out what to do next

9
Writing the code

• On error statement
• At beginning of procedure
• Tells VBA where to find error-handler
• Exit sub or exit function
• At end of normal code
• Error handler generally comes at the end of a
  procedure

10
Code Example 1

• Private Sub ErrorHandlerDemo()
• On Error GoTo errorhandler
• ' do the normal sub-routine stuff here
• ' stop the procedure here if no error has
  occurred
• Exit Sub
• errorhandler
• ' error handling code goes here
• End Sub

11
Other Forms of the On-Error Statement

• On error go to 0
• Turns off any previous on error goto label
  statements
• Clears the Err object
• Use it at a point in your procedure after a
  previous on-error goto label
• Turns off your error handling and returns control
  to VBA

12
Other Forms of the On-Error Statement

• On error resume next
• Causes VBA to ignore the line that caused the
  error
• Continue execution with the next line of code
• Primarily used when your code uses objects from
  the host application

13
Exit statement

• An error handler is part and parcel of the
  procedure in which it appears
• Use exit statement to prevent the error handler
  from running except when it is suppose to
• Exit sub or exit function

14
Writing the Error Handler

• To minimize coding hassles place the
  error-handling code at the end of the procedure
• Requirements
• A label showing where it starts
• Code to determine the type of error
• Code to deal with the error
• Optionally, a statement to resume execution of
  the main part of the procedure

15
Labels

• Pointer to a certain local in the procedure
• Single word followed by a colon

16
Getting Information about the Error

• Err Object
• Available in every VBA program
• VBA stores info about the most recent error here
• Your code can inspect the properties of the
  object
• Number property
• Can assign this to a variable or use it directly
  in your code

17
Dealing with an Error

• Error handlers normally perform the following
• Inform the user that something has gone wrong and
  ask for instructions via MsgBox or custom form
• Try again to get valid data from a source that
  was unavailable earlier or try an alternate
  source
• Change an errant value to a valid one, recording
  in a file or via a MsgBox that data had to be
  changed by brute force

18
Error Numbers

• Effective use of the number property requires
  that you know what each error number means
• Find info for most common errors in the trappable
  errors topic of the VBA help system
• If your code cant deal with a particular error
  you can still avoid the standard VBA
  run-time-error message by using the Err objects
  description property

19
Resuming Execution

• What to do when error-handler is do
• Pick up where the program left off
• Transfer program execution back to the procedure
  that called the faulty procedure where the error
  occurred

20
Resume statements

• Resume on a line by itself
• Transfers control back to statement that caused
  the error
• Use if the error-handler fixed the problem
• Resume next
• Skip the statement that caused the problem and
  continue execution
• Resume label
• Transfer control to the point indicated by label

21
Final Reminder

• Test
• Test again
• Test some more