The On Error Statement: To Use or Not To Use

Managing coding errors can be a time consuming and complex task in VBA. Unlike many modern programming languages, VBA lacks native functions that help avoid common errors, sometimes requiring the use of the On Error statement. While the On Error statement can seemingly make a procedure work, it does not resolve the error in your code.

Overview

Error handling is a common topic discussed across VBA forums and blogs. Within VBA, there are generally two approaches to managing errors.

  1. The On Error statement: a simple statement which can be set to ignore errors in a procedure.
  2. Avoid errors all-together: build functions and procedures to manage situations which are error-prone.

Obviously the second option is what all VBA developers should strive for — a full-proof way of handling errors is ensuring that they do not exist in the first place. However, there are many circumstances where avoiding an error may require complex coding, taking up time when writing code, and using more system resources when running. With the On Error statement, implementation is quick and easy (only requiring one or two lines of code), and is very fast and efficient from a performance perspective.

Regardless of its ease of use, I feel that the On Error statement is overused. While it can solve most VBA challenges, the statement does not actually eliminate the error from your code. Overuse could potential backfire: it may create difficult-to-read spaghetti code, and Excel’s settings could cause the statement to be ignored all together.

My Approach

Here are a few common situations where I feel that On Error is overused.

  • Collection handling: determining if an element exists within a collection, such as checking if a worksheet with a specific name exists.
  • Data validation: checking to see if a user has entered a correct data format, or if data within a specific cell is valid.
  • Infinite loops: resolving coding errors which may not property handle a Do While or Do Until loop.
  • Object handling: determining if a single object exists in memory.

Why I Avoid On Error Statements

If an On Error statement is being used in your code, that means an error is present in your code. While the On Error statement will frequently “do the job” and permit your code to work with a specific error, it will not fully eliminate the error.

Error handling behavior can be changed in the VBA Options menu.
Error handling behavior can be changed in the VBA Options menu.

It’s also very important to note that a global setting within the VBA editor can cause Excel to ignore all error handling statements. Within the “General” tab of the VBA Options menu (Tools > Options), it’s possible to turn off all error handling. This can be done by selecting “Break on All Errors”. Once a user selects this option, the setting will persist until the user goes in and reverts back to an old setting. This generally will not be an issue with a casual macro user, but it’s still a risk if a VBA developer has the “Break on All Errors” turned on.

Finally, it’s important to keep in mind that the On Error statement is a type of GoTo statement, which use in any modern programming language is generally frowned upon. While it’s acceptable in small doses, overuse of GoTo statements can make your code confusing and unreadable.

Alternatives to On Error

In the following sections, I provide effective alternatives to common situations where I see On Error statements overused. Each example provides a comparison of a common On Error approach against a viable alternative that avoids On Error statements.

Collection Searches

Collection searches can determine if a specific element exists within a collection. This search can be based on an element name, or if specific conditions are met within an element. Collection searches invoke frequent use of the On Error statement. The following example explores typical usage of an On Error solution, as well as an elegant solution using a For Each loop.

On Error Solution

This procedure attempts to activate a worksheet named “I Exist”. If the sheet name exists, the On Error statement is not used and displays the message “Clippy speaks the truth!” If the sheet “I Exist” does not exist, the On Error statement is used and displays the message “That’s a lie!”

Elegant Solution

The two procedures below present a more elegant solution. The Find_Worksheet subroutine accomplishes the same task as the On Error solution by using a simple If statement. The If statement calls the user defined function Worksheets_Search (function script is in the following coding box), which uses a For Each loop to search for a sheet name. If the sheet name exists within the collection, a Boolean value of “True” is returned.

While the Worksheets_Search function allows us to avoid an On Error statement, it does come at some cost. Notably, if the workbook contains hundreds of worksheets, there would be a noticeable lag within the test. While it’s rare to have a workbook with hundreds of worksheets, this approach would be identical for any other type of Excel or VBA collections, which can sometimes contain thousands of elements. In those situations, it may be more reasonable to opt for the On Error solution to improve the user experience.

Data Validation

Excel offers a number of data validation tools. This includes Excel’s built-in data validation tool, validation using formulas, and validation with VBA. Even with these options, I still see the On Error statement used for data validation, specifically to manage “Type Mismatch” errors. The following presents a validation solution using On Error, as well as an elegant solution an If statement.

On Error Solution

In this example, the value contained within cell A1 of the active sheet will be assigned to Integer variable doesItCount. If no error handling took place, a “Type Mismatch” error would occur. Therefore, if alpha characters or numbers outside of an Integer‘s bounds are within cell A1, the On Error statement will jump to the GoTo tag ErrorHandler.

Elegant Solution

The On Error statement is easily avoidable when it comes to data validation. In the example below, the bounds of the Integer data type are used within an If statement to ensure a “Type Mismatch” or “Overflow” error do not occur.

Infinite Loops

There are various situations where infinite loops may either purposefully, or mistakenly lead to VBA errors. In most cases, this is done as a control to eventually exit the loop.

On Error Solution

In this example, the Integer variable loopyLoop is a counter within an infinite Do While loop. This will obviously run into an overflow error once it reaches an Integer‘s upper limit value: 32768. At this point, the On Error statement will force a skip to the ErrorHandler line.

Elegant Solution

For the elegant solution, we simply include a conditional within the loop to force an exit once variable loopyLoop reaches its maximum value. This is effectively the same approach used in the data validation example, but is nested within a loop.

Object Handling

There may be situations where you need to test if an Excel or VBA object exist. This may include shapes, ActiveX controls, or custom objects derived from classes.

On Error Solution

If we attempt to access an object that does not exist, we receive an “Object variable or With block variable not set” error. This error can be managed by an On Error statement, such as the following.

Elegant Solution

As an alternative to the On Error statement, a conditional can test if an object exists simply by using the Is Nothing operator.

What Do You Think?

What’s your take on this topic? Feel free to comment below about situations where an On Error statement is the only viable solution. Likewise, also let me know if there are unique techniques you use to avoid On Error.

Other Notes

While this post advocates for seeking out alternatives to the On Error statement, there are many situations where it is unavoidable. Just to name a few, you will frequently need to resort to On Error when using recursive functions, searching through massive collections, or converting international date formats. Regardless, whenever you are tempted to use On Error, take a few minutes to determine if a safer alternative exists.

Leave a Reply