Understanding Error Handling Techniques in VBA Programming
Error handling is crucial in VBA programming to catch and manage runtime errors effectively. This article explores various error handling techniques such as On Error GoTo statement to prevent program crashes and enhance the reliability of applications. By using structured error handling, developers can address unexpected user inputs and ensure smoother execution of their VBA code.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
ITEC397 Macro Coding 08 ERRORS AND ERROR FUNCTION
Error Handling 2 We already have seen type of errors and how to debug our programs. Especially the run time errors and logical errors are hard to be found and estimated on design time. No matter how carefully you design and code for all conditions, there is always a user who does something that you never thought of and effectively breaks the set of rules that you have written. So, in order to develop more reliable application, we have to use error handling in our code.
Error Handling 3 Any run time error will stop execution of your program. The user will be very unhappy and will lose a great deal of faith in your application. The error needs to be trapped, and an appropriate action needs to be taken from within the VBA code (Debugging). Error handling techniques may are also be used for capturing runtime errors that can halt the running program.
Error Handling 4 On Error GoTo statement This command is used like exception handling command of C, C++, C#, Java, JavaScrip (try catch ) Try this simple example without a disk in drive D. Sub Test_Error() temp = Dir("d:\*.*") End Sub This will produce an error message saying that the D drive is not ready. In normal terms, your program has crashed and will not work any further until this error is resolved.
Error Handling 5 On Error GoTo statement You can place a simple error-trapping routine as follows: Sub Test_Error() On Error GoTo err_handler temp = Dir("d:\*.*") Exit Sub err_handler: MsgBox "The D drive is not ready" & " " & Err.Description End Sub The first line sets up a routine to jump to when an error occurs using the On Error statement.
Error Handling 6 It points to err_handler, which is a label just below the Exit Sub line further down that will deal with any error condition. The purpose of a label is to define a section of your code you can jump to by using a GoTo statement. The line to read the D: drive is the same as before, and then there is an Exit Sub line, because if all is well you do not want the code continuing into the err_handler routine. If an error happens at any point after the On Error line, the code execution jumps to err_handler and displays a message box that says drive D is not ready.
Error Handling 7 However, you may have noticed that the code execution jumps to err_handler when any error occurs, not just the drive not ready error. An error could occur because you made a mistake in typing this code in. This could have unfortunate consequences in your code. Fortunately, you can interrogate the error to find out what went wrong. You can also use the Err object to give the description of the error and to concatenate it into your message so that it also says Drive not ready.
Error Handling 8 You do this using the Err function. This will return the number associated with the runtime error that happened. Sub Test_Error() On Error GoTo err_handler temp = Dir("d:\*.*") Exit Sub err_handler: If Err.Number = 71 Then MsgBox "The D drive is not ready" Else MsgBox "An error occurred" End If Resume End Sub
Error Handling 9 You can also add the statement Next to Resume. This will skip over the statement that created the error and therefore ignore the error. Sub Test_Error() On Error Resume Next temp = Dir("d:\*.*") End Sub If there is no disk in drive D, the code will still run perfectly because of On Error Resume Next It skips over the line of code creating the error. Resume Next can be useful for dealing with errors as they occur, but it can make debugging code very difficult.
Error Handling 10 In a later stage of your program, you may have incorrect or nonexistent data being produced due to the fact that an error condition earlier was ignored. You can end up with a program that appears to run okay, but in fact does nothing because it has some hidden bugs or incomplete data. This is because every time an error is encountered, the execution just skips over it. This can give a false impression of what is actually happening.
Error Handling 11 So if you do use On Error Resume Next, make sure that you check all inputs and outputs to the code to ensure that everything is working as it should be. Make sure that your On Error Resume Next statement cannot cover up an error in a read from a spreadsheet cell or from a file. This could cause disaster in your program because the On Error Resume Next statement would make it appear to work perfectly.
Implications of Error Trapping 12 When you use an On Error statement in your code, that error trap remains throughout the procedure unless it is disabled. As you just saw, you can set up a routine to check whether the D: drive has a disk in it and take action accordingly. However, it is important to turn this off once the possibility of the error has taken place. If you do not do this, then all subsequent errors within that procedure will use the same error-handling routine.
Implications of Error Trapping 13 This gives extremely confusing results to the user, as the error message is likely to be totally meaningless in relation to the error generated. A subsequent error could relate to division by zero, but the error message will come up saying Drive D not ready. If On Error Resume Next has been used and not switched off, all sorts of errors could be taking place without the user being aware of them. You disable the error-trapping routine as follows: On Error Resume Next On Error GoTo 0
Implications of Error Trapping 14 On Error Resume Next On Error GoTo 0 The On Error Resume Next statement that you saw previously ignores all errors. The On Error GoTo 0 cancels any error handling and allows all errors to be displayed as they normally would. This cancels out the On Error Resume Next and puts everything back to normal error handling.
Generating Your Own Errors 15 In order to detect and recover logical errors; at the beginning you may define your rules and anything else as errors. Then you can handle these with the same way in the error handling systems. Example: if the legal working age should be anything between 18 to 65, you may define any age less them 18 and greater then 65 is a user defined error. To generate a run-time error you can use the Raise method Err Object Err.Raise(Number [, Source] [, Description])
Generating Your Own Errors 16 Err.Raise(Number [, Source] [, Description]) Number Long, identifying the type of the error. Visual Basic errors range from 0 to 65535 The range 0 - 512 is reserved for system errors The range 513 - 65535 is available for user defined errors If (w_age<18) Or (w_age>65) Err.Raise 1500, , Working age have to be (18-65) Source String If this is not specified then the programming ID of the current project is used. Description This the string that describes the error. If the error can be mapped to a Visual Basic run-time error the string corresponding to the Number is used. If there is no Visual Basic error corresponding to the number then "Application-defined or object-defined error" message is used.
Generating Your Own Errors 17 Sometimes it is useful when you are testing your own applications or when you want to treat a particular condition as being equivalent to a runtime error. You can generate an error in your code with the Error statement: Sub Test_Error() Error 71 End Sub This simulates the Drive not ready error. You could also use Err.Raise(71) to do this. In addition, you can regenerate the current error by using the following: Error Err The purpose of this is to raise the Drive not ready error message as if the drive really was not ready because this is the current error found. If another error message is then created, this becomes the current error.
