How to handle syntax, runtime or display errors in VBA?

Different types of VBA errors and how to handle the errors.

1. Syntax - your current code line is written incorrectly.

2. Compile - your code lines are analyzed as a whole and often a code line is missing.

3. Runtime - you are thrown an error during runtime that stops your code and informs the user.

Syntax Error

• Often a keyword or argument that is misspelled.

• Punctuation is incorrect. For example, when you omit optional arguments positionally, you must substitute a comma (,) as a placeholder for the omitted argument.

• A procedure isn't defined. often just misspelled.

Example of omitted argument. Missing 'Then' which completes the if statement.

Compile Error

Common compilation errors could be:

• Missing IF statement completion.

• Missing For or Do Loop completion.

• Missing declaration for variables.

• Missing reference package. (for example Outlook Object Library for sending emails.)

Runtime Error

Common compilation errors could be:

• Subscript out of range - trying to access elements outside an defined loop.

• Can't find file - trying to import a file. Often incorrect filename that is specified.

• Division by 0 - logical error.

• Out of memory error - for example copy pasting data without clearing your memory.

  • (Application.CutCopyMode = False to clear)

• Missmatch error - attempting to add data of different type to the variable declaration.​

How to handle errors.

I will keep this section fairly brief as I believe most errors can be encapsulated in a simple if statement hence avoiding any error handling whatsoever. Often using an 'Exit Do' or 'Exit For' in my Exit Loop page will suffice. However, sometimes it is necessary to use error handlings in order to catch errors instead so that the script can continue.

• On Error Resume Next - Ignores the error and continues to run the code.

  • Whilst it is an option to just ignore the error, I recommend to avoid this option.​

• On Error Goto MyErrorHandler - Goes to MyErrorHandler (you can choose your own name) when an error occurs. This allows us to handle the error.

Below you will find an example using the "On Error GoTo statement". In this case an error is raised and the code is jumping directly to the code line saying "ErrorHandler:". By doing this it is "jumping over" the msgbox line with "Hello Analyst!". This code line is not being executed and the code is instead displaying the line "please fix your code. An error occurred.