1 / 35

Error Handling

Error Handling. The Three Types of Errors. Syntax error : an error in the form of your program that prevents it from running. Also called a compiler error in VBA Run-time error : an error that arises at run time and makes your program terminate abnormally (crash)

elu
Télécharger la présentation

Error Handling

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. Error Handling

  2. The Three Types of Errors • Syntax error: an error in the form of your program that prevents it from running. Also called a compiler error in VBA • Run-time error: an error that arises at run time and makes your program terminate abnormally (crash) • Logic error: an error in which your program appears to run normally but produces an answer that is not correct

  3. Type 1: Syntax Errors • These errors are caught by the VBA environment, which also attempts to suggest fixes • They can be very annoying, but since you can’t run your program with one of these errors in place, they are actually not that much of a problem once you have programmed a bit • The built-in Help can be very useful in diagnosing and correcting these

  4. Type 2: Logic Errors • These are the most insidious, because your program appears to run normally • In fact, it does exactly what you told it to, only there is a logic error so what you told it to do is not actually what you wanted • The only defense is thorough testing. You need to develop a good set of tests BEFORE you start coding. You should also test complex spreadsheets even if there is no code involved

  5. The Infamous Disclaimer • Have you ever read the fine print on the agreements you must authorize before you download software? • Most commercial software has at least a few “bugs”, including logic errors • The disclaimer says you can’t sue the company for damages if you relied on a faulty answer from their software

  6. Why? • In the software business, the theory is that the first product of a given type to market grabs the big market share • So software firms may put their product out before it is thoroughly tested • Since this strategy has worked well in the past, it is hard to blame them too much, though it is unprofessional

  7. Type 3: Run-time Errors • Run time errors happen when the program encounters an unexpected condition that it can’t handle • Division by zero, needing a file that’s not there, or user error entering a value, are common causes of runtime errors • A well-written program should recover gracefully from errors, ideally allowing the user another chance to enter their data, say, if that was the cause of the problem

  8. Testing for Errors • In addition to your normal use cases, you should also develop cases for all the user mistakes or other error conditions you can think of • For each of these error cases, you should have one or more tests • Your program should recover gracefully in each case; if it crashes, you need to figure out a way to avoid this

  9. Code to Handle Errors (Example 1) • We’ve already seen some error-handling examples in our code for reading from a file: '*** get the name of the file to use fName = Application.GetOpenFilename() If fName = False Then'user cancelled Exit Sub End If • This jumps out of the subroutine if the user leaves a blank entry or otherwise fails to enter a file name. Ideally there would be a message.

  10. Code to Handle Errors (Example 2) On Error GoToEndMacro: 'bail out if there is an error … EndMacro: On Error GoTo 0 'just quit if there's an error • This code uses the GoTo construct to jump to the end of the program, and then out, if there is an error • The first GoTo uses a user-defined label as a target. The second one uses a system label

  11. GoTo • In the “olden days” of programming, there were no structures like If-Then-Else or Do-While • If you didn’t want your program to just execute in a straight line, you used an If and a GoTo: If <condition> Then GoTo<label> • You can write a program that does what you need it to do with just the If and GoToand some labels, but unless you are a very good and careful programmer, it can be very hard to understand

  12. GoTo Considered Harmful • Starting in the late 1960’s, a big and largely successful effort was made to replace the need for GoTo with structured statements like If-Then-Else, For-Next, and Do-While • The one place where you still often need to jump out of the orderly code execution is when an error occurs • Hence, although GoTo is usually avoided, in handling run-time errors it may often be the best way to go

  13. Other Approaches: Exit • Before looking at the GoTo <label> approach, we consider some alternatives • In our first example, we tested the return value of the GetOpenFilename function; if it returns False, we exit the subroutine. This exit is similar to a GoTo in that we are just jumping out of the middle of a subroutine to wherever it was called from. We could also give a message to the user before exiting, using a message box

  14. Other Approaches: Defaults • One common cause of errors is where the user doesn’t enter a required value in a field such as a text box, or enters an unacceptable value • You can set a text box to a default value in the UserForm_Initialize procedure to help prevent such errors. Many shopping sites initialize the quantity box to 0 or 1 for this reason (and also because 1 is the most common value in most cases)

  15. Other Approaches: Limit Choices • Instead of having the users type something in a text box, you can have them choose a value from a drop-down menu • In this way you limit the choices to correct ones • You often see this approach on shopping sites where you choose your state or the expiration year of your credit card from a drop-down list

  16. Other Approaches: Scrutiny • Another way to avoid run-time errors caused by unexpected inputs, such as non-numbers where a number was expected, is to write code that will scrutinize the input string carefully before trying to convert it into a number, and ask the user to re-enter a faulty value • This requires sophisticated use of the string manipulation functions

  17. On Error GoTo • Information for this section was found at http://www.cpearson.com/excel/errorhandling.htm • There are three forms of On Error GoTo, which we will consider next

  18. On Error GoTo0 • The simplest form of the On Error GoTo is On Error GoTo0 • This makes VBA display a standard run time error message box; you can also enter code in debug mode • Basically this is the same behavior you get if you have no error handler at all, so you should try to do better if you have the time

  19. On Error Resume Next • The next option is to use On Error Resume Next • With this statement, you are telling VBA that if a run-time error occurs, it should just ignore it and execute the next line • The problem here is that there may be unintended consequences as a result of the error (like a missing value that never got set), so just continuing as if nothing happened could be a bad idea

  20. On Error GoTo<label> • The third option is to have VBA jump to a label where you try to correct the error, or at least exit gracefully • For example, our file reading program goes to a place at the end of the subroutine where any open file is closed before exiting • A typical structure is to have the ErrorHandler label at the end of the code, with an Exit Sub just before it to exit normally if there were no errors

  21. Example Structure Sub Example () On Error GoToErrorHandler <normal code> Exit Sub ErrorHandler: Msgbox(“A fatal error has occurred”) End Sub

  22. Resume • Instead of exiting your subprocedure after going to the error label, you might want to resume executing it. The Resume statement allows you to do this • There are three forms of resume: • Resume • Resume Next • Resume <label>

  23. Plain Resume • This tells VBA to go back to the line that caused the error and try again • Obviously, this means you have to do something to fix the error before going back! • Part of the problem here is that your program needs to know what kind of error occurred • When there is an error, VBA sets a property called Err.Number to communicate the type of error

  24. Error Numbers • VBA provides an error description to go with each error number • These can be extremely valuable when you are debugging your code • The next page shows a sample code structure, based on an example found at http://www.ozgrid.com/VBA/ExcelVBAErrors.htm

  25. Showing the Error Description Sub Example() On Error GoToErrorHandler <normal code> Exit Sub ‘avoid error handler if no error ErrorHandler: MsgBoxErr.Number & “ “ & Err.Description End Sub

  26. Resume Next • With Resume Next, the program starts executing at the next line after where the error occurred • As with the previous case, your program should do something to diagnose and fix the error before resuming

  27. Resume <label> • Here the program resumes execution at the line with the specified label • This allows you to skip a chunk of code where things might be problematic • Of course you should compensate for the skipped code, if needed, by setting variable values or otherwise patching things up after the error

  28. An Illustration of Checking • The application RealEstateErrorHandler shows how to use string functions to scrutinize user input for errors. • It also illustrates checking that an option has been chosen in a list box or a set of option buttons • We’ll look at how each of these is done

  29. Checking Listboxes '*** get the agent and neighborhood from listboxes '*** exit if either is null If(lstAgents.ListIndex = -1) Or _ (lstNeighborhoods.ListIndex = -1) Then MsgBox ("You must select an agent and a neighborhood") Exit Sub End If

  30. Checking Option Buttons FunctionCheckCommission() As Boolean '*** check that an option has been chosen IfoptSolo.Value = True Or optShared.Value = True Then CheckCommission = True Else 'error, no commission chosen CheckCommission = False End If End Function

  31. Checking the Price: Declarations ConstDIGITS As String = "0123456789" Dim testStrAs String DimtestStrLengthAs Integer DimBadCharFoundAs Boolean Dimj As Integer'loop control DimjCharAs String ‘jth character in the input string

  32. Checking the Price: Initial Steps testStr= txtPrice.Text PriceOK = True 'get ready to check each character in the test string 'each character should be a digit testStr = Trim(inputStr) testStrLength = Len(testStr) 'Take care of the empty input string case IftestStrLength = 0 Then PriceOK = False Exit Function End If

  33. Checking the Price: Main Loop j = 1 'index of first character in testStr BadCharFound = False 'stop as soon as a bad character is found Do While (j <= testStrLength) And (NotBadCharFound) jChar = Mid(testStr, j, 1) If InStr(DIGITS, jChar) = 0 Then BadCharFound = True End If j = j + 1 Loop If BadCharFoundThen PriceOK = False End If

  34. About Checking • Checking is much more work than just jumping to a label if there is an error • But it is worth it to give the user detailed messages and allow for a graceful recovery if an error happens

  35. Sources • Error Handling in VBA, on the Pearson Software Consulting Services site, www.cpearson.com/excel/errorhandling.htm • Appendix C in Walkenbach has a list of all the VBA error codes and their descriptions

More Related