Excel VBA Error Handling Best Practice

In this article, we are going to discuss the best practice for Excel VBA error handling. One will need advanced knowledge of Excel to understand the discussion. You will need to know about the Excel VBA, enabling the Developer tab, the On Error Resume Next statement, and the Select Case statement to comprehend everything from this article. You can use any version of Excel to apply the best practices from this article.

Download Practice Workbook

You can download the Excel file from the following link.

Problem Overview

The codes are provided inside the module of the Excel file. You can press Alt+F11 to bring up the VBA window, and from there you can access the codes. These are the best practices for handling any VBA error in Excel:

• Use the “On Error GoTo” statement at the beginning. From this point forward, all errors will be shown and fixed.
• When utilizing error handlers, do so following the “Exit Sub”. So, it will only run if there is an error. The code will run faster and use less memory as a result.
• Apply unique handlers for different error types. This will ensure any error on the code is properly identified.

• Insert the “On Error Resume Next” statement for the possible errors. If you use it with unanticipated errors, it will skip those. So, it will be difficult to debug. Therefore, it is best to identify the errors and then use this statement to ignore errors. Moreover, use the On Error GoTo 0 for other unknown errors.
  • We can see from the next image that the first “divided by 0” is disregarded but the second one is not.

• Write the errors to a file to track the previous VBA errors. The following code creates a history of the past errors with time. After executing this code, you will get a text file containing the error messages.

• Set the Error Tapping settings to “Break in Class Module” to stop the code at the line that causes the error. However, any customer or user can change this setting. So you need to select this setting before executing the code.

The following animated image shows how to do this.

Conclusion

Thank you for reading this article. We hope you have learned about the best practice for Excel VBA error handling. You can find more articles similar to this on the ExcelDemy website. If you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!