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.
Problem Overview
The codes are provided inside the module of the Excel file. You can press Alt+F11 to bring up the Excel VBA window, and from there you can access the codes. You need to enable the developer tab first. 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.
Download Practice Workbook
You can download the Excel file from the following link.
Conclusion
Thank you for reading this article. We hope you have learned about the best practices for Excel VBA error handling. 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!
