Error Handling in VBA

Http://www.cpearson.com/excel/ErrorHandling.htm

 

Pearson Software consulting services

Error Handling in VBA

	Introduction Error Handling refers to the programming practice of anticipating and coding for error conditions that may arise when your program runs. errors in general come in three flavors: compiler errors such as undeclared variables that prevent your code from compiling; user data entry error such as a user entering a negative value where only a positive number is acceptable; and run time errors, that occur when VBA cannot correctly execute a program statement. we will concern ourselves here only with run time errors. typical run time errors include attempting to access a non-existent worksheet or workbook, or attempting to divide by zero. the example code in this article will use the division by zero error (error 11) when we want to deliberately raise an error. Your application shoshould make as your checks as possible during initialization to ensure that run time errors do not occur later. in Excel, this includes des ensuring that required workbooks and worksheets are present and that required names are defined. the more checking you do before the real work of your application begins, the more stable your application will be. it is far better to detect potential error situations when your application starts up before data is change than to wait until later to encounter an error situation. If you have no error handling code and a run time error occurs, VBA will display its standard run time error dialog box. while this may be acceptable, even desirable, in a development environment, it is not acceptable to the end user in a production environment. the goal of well designed error handling code is to anticipate potential errors, and correct them at run time or to terminate code execution in a controlled, graceful method. your goal shoshould be to prevent unhandled errors from arising. A note on terminology:Throughout this Article, the termProcedureShocould be taken to mean a sub, function, or property procedure, and the termExit statementShocould be taken to mean exit sub, exit function, or exit property. The termEnd statementShocould be taken to mean end sub,End function, end property, or just end. The on error statement The heart of error handling in VBA is the on error statement. This statement instructs VBA what to do when an run time error is encountered. The on error statement takes three forms. On Error goto 0 On Error resume next On Error goto <label>: The first form, on error goto 0, is the default mode in VBA. this indicates that when a run time error occurs VBA shoshould display its standard run time error message box, allowing you to enter the code in debug mode or to terminate the VBA program. when on error goto 0 is in effect, it is the same as having no enabled error handler. any error will cause VBA to display its standard error message box. The second form, on error resume next, is the most commonly used and misused form. it instructs to VBA to essential ignore the error and resume execution on the next line of code. it is very important to remember that on error resume next does NotIn any way "fix" the error. it simply instructs VBA to continue as if no error occured. however, the error may have side effects, such as uninitialized variables or objects set to nothing. it is the responsibility of your code to test for an error condition and take appropriate action. you do this by testing the value of err. number and if it is not zero execute appropriate code. for example, On Error resume next N = 1/0 'cause an error If err. Number <> 0 then N = 1 End if This code attempts to assign the value 1/0 to the variable n. this is an illegal operations, so VBA will raise an error 11 -- Division by zero -- and because we have on error resume next in effect, code continues to the IF statement. this statement tests the value of err. number and assigns some other number to n. The third form on error of is on error goto <label>: Which tells VBA to transfer execution to the line following the specified line label. whenever an error occurs, code execution immediately goes to the line following the line label. none of the Code between the error and the label is executed, including any loop control statements. On Error goto errhandler: N = 1/0 'cause an error ' 'More code ' Exit sub Errhandler: 'Error handling code Resume next End sub Enabled and active error handlers An error handler is said to beEnabledWhen an on error statement is executed. Only one error handler is enabled at any given time, and VBA will behave according to the enabled error handler.ActiveError handler is the code that executes when an error occurs and execution is transferred to another location via a on error goto <label>: Statement. Error Handling blocks and on error GOTO An error handling block, also called an error handler, is a section of code to which execution is tranferred via a on error goto <label>: Statement. this code shoshould be designed either to fix the problem and resume execution in the main code block or to terminate execution of the procedure. you can't use to the on error goto <label>: statement merely skip over lines. for example, the following code will not work properly: On Error goto err1: Debug. Print 1/0 'More code Err1: On Error goto err2: Debug. Print 1/0 'More code Err2: When the first error is raised, execution transfers to the line following err1 :. the error hander is still active when the second error occurs, and therefore the second error is not trapped by the on error statement.   The resume statement The resume statement instructs VBA to resume execution at a specified point in the code. you can use resume only in an error handling block; any other use will cause an error. moreover, resume is the only way, aside from exiting the procedure, to get out of an error handling block. doNotUse the GOTO statement to direct code execution out of an error handling block. Doing so will cause strange problems with the error handlers. The resume statement takes three syntactic form: Resume Resume next Resume <label> Used alone, resume causes execution to resume at the line of code that caused the error. in this case you must ensure that your error handling block fixed the problem that caused the initial error. otherwise, your code will enter an endless loop, jumping between the line of code that caused the error and the error handling block. the following code attempts to activate a worksheet that does not exist. this causes an error (9-subscript out of range), and the Code jumps to the error handling block which creates the sheet, correcting the problem, and resumes execution at the line of code that caused the error. On Error goto errhandler: Worksheets ("newsheet"). Activate Exit sub Errhandler: If err. Number = 9 then 'Sheet does not exist, so create it Worksheets. Add. Name = "newsheet" 'Go back to the line of code that caused the problem Resume End if The second form of resume is resume next. this causes code execution to resume at the line immediately following the line which caused the error. the following code causes an error (11-division by zero) when attempting to set the value of N. the error handling block assigns 1 to the variable N, and then causes execution to resume at the statement after the statement that caused the error. On Error goto errhandler: N = 1/0 Debug. Print n Exit sub Errhandler: N = 1 'Go back to the line following the error Resume next The third form of resume is resume <label>:. This causes code execution to resume at a line label. This allows you to skip a section of code if an error occurs. For example, On Error goto errhandler: N = 1/0 ' 'Code that is skipped if an error occurs ' Label1: ' 'More code to execute ' Exit sub Errhandler: 'Go back to the line at label1: Resume label1: All forms of the resume clear or reset the ERR Object. Error handling with multiple procedures Every procedure need not have a error code. when an error occurs, VBA uses the last on error statement to direct code execution. if the code causing the error is in a procedure with an on error statement, error handling is as described in the above section. however, if the procedure in which the error occurs does not have an error handler, VBA looks backwards through the procedure CILS which lead to the erroneous code. for example if procedure a callb and B callc, and a is the only procedure with an error handler, if an error occurs in procedure C, code execution is immediately transferred to the error handler in procedure A, skipping the remaining code in B. A note of caution It is tempting to deal with errors by placing an on error resume next statement at the top of the procedure in order to get the code to run without raising an error. this is very bad coding practice. remember that using on error resume next doesNotFix errors. It merely ignores them. The future of error handling in VBA Error Handling in VB6 and VBA is based on the on error statement, which leads to awkward code structure. ages like C ++ provide a code structure Call try/catch that allows much more granularity and control. at some point, Microsoft will introduce their net framework in to office, and when this happens, VBA programmers will have at their disposal the language features of try/catch/finally code structure that VB. NET developers already enjoy.