Error Handling Methods in Excel VBA

You can download the workbook used in this video from the link below:
https://www.nomorelinesla.com/wp-content/uploads/2022/04/Excel-VBA-Error-Handling-Examples.zip

Error-handling is one of the most useful aspects of VBA, not just in Excel but also in other applications such as Outlook and Access. Error-handling allows you to capture and potentially correct an issue with your VBA code.

Anyone who has programmed in VBA has seen the Microsoft Visual Basic window appear when an error occurs executing code. For example, if your code selects a workbook that does not exist within the workbook collection, an Error 9 "Subscript Out of Range" error will appear in a message box. Without error-handling, this message box fatally terminates the subroutines/functions currently running.

Error-handling allows you to set aside code that help identify or fix a potential issue that can halt a critical process from executing. This is especially important for reliable automated reporting.

The first error handling option is called "On Error Resume Next". Think of the "On Error" line of code as a stoplight that instructs Excel on what to do when the next fatal error occurs. It does not apply to any preceding code. "On Error Resume Next" tells Excel to continue running the next step of the subroutine/function. As expected, this has benefits and determents and thought go into selecting the correct method of error-handling.

The next error-handling option is called "On Error GoTo". You can give a section of your code a heading, i.e., "On Error GoTo NextSection". "NextSection" with a colon (i.e. NextSection:) would then need to be present within your code. The "On Error GoTo" and your section heading would then divert your code to the "NextSection:" part of your code. It is recommended that you put your "On Error GoTo" error handling at the end of your code to prevent your error handling from running accidentially. A way of doing this is to type "Exit Sub" preceding your error-handling heading.

Lastly the final error-handling option is called "On Error GoTo 0". The main different between "On Error GoTo 0" and "On Error GoTo 'Heading'" is "On Error GoTo 0" is the off-switch to any On Errors currently enabled within the subroutine. Any code following "On Error GoTo 0" will throw a fatal error if an execution error occurs, nullifying any error-handling currently active.

Note: when the subroutine finishes, the error-handling deactivates.