This tutorial is an introduction to the most basic forms of VBA error handling. We’ll show you how to handle predictable errors using GoTo, how to properly implement and place error handling code, and how to turn handlers on and off.
The importance of error handling cannot be understated, especially for production code that goes out to clients. Each error you handle is one less coworker to annoy you when they break your code. If that’s not motivation to practice good error handling techniques, I don’t know what is!
- Do You Need to Error Handle?
- Types of Errors
- Turn on the Handler with
On Error - Two Main Methods
- Set Up the Error Handling Code
- Turn Off the Error Handler with
GoTo 0 - Placement of the Handler
- An Alternative Error Handling Approach
- Conclusion
Do You Need to Error Handle?
Yes. But as with most things in life, the real answer is more nuanced than that.
Do you need to error handle for a quick script you threw together to automate something once? Probably not. If you’re scripting something for yourself, you need to weigh whether or not error handling is necessary. Moreover, you have access to your own code, so you can diagnose and debug on the fly.
On the other hand, error handling is vital for any code that you send to other people, especially if you have no contact with those people (so you cannot be on-site to fix it) or you’re selling the code. No one wants to pay for code that constantly throws errors. That’s a surefire way to receive negative reviews and no more revenue.
You can reduce the time you spend developing such error handling by maintaining a list of common error handlers. Errors like divide by zero can often easily be rectified with a user prompt or, though dangerous, a pre-coded revaluation of the variable. A warning to the user is useful and informative, so create an error-handler template and add it to every program.
Throughout the next few months, we’re going to publish a couple more tutorials on error handling: one for properly using Resume Next and another for using Err object and cascading. To get notified when these are published, subscribe using the form below:
Make powerful macros with our free VBA Developer Kit It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
Types of Errors
There are three types of errors in coding:
- Syntax
- Logical
- Runtime
Syntax errors occur because of unfinished lines of code. If you’re using a modern VBA editor, you’ll know right away when a syntax error has occurred. The editor will tell you immediately if there’s a problem and highlight the offending line in red:
Missing closing parenthesis syntax error in VBE
Logical errors are algorithmic. These are flaws in your thinking. If you want to calculate the interest for 4 months based on an annual rate, you need to write something like
partial_interest = (months_elapsed / 12) * annual_rateBut if you write
partial_interest = (months_elapsed * 12) / annual_ratethen you’ll have a logical error. These are harder to detect since they’re often caused by errors your own logic.
Runtime errors occur when the code starts to run. Generally these occur because a variable or object is not as expected. Somewhere in the code a variable assignment breaks the logic. Perhaps a user input zero for
students_per_class = num_students / available_roomsAn error like this can only occur at runtime and only because some inputs are invalid for the logic. This kind of error is the topic of this tutorial and our next few tutorials. When unhandled, these errors stop your code and start the visual basic debugger. You’re probably familiar with runtime error dialog boxes. They look like this:
Turn on the Handler with On Error
By default, there is no error handling in VBA. Runtime errors halt execution and an error message pops up to the user. This is normal behavior and VBA even allows you raise your own custom runtime error messages. Computers cannot think, so they cannot handle errors without instructions.
In a section of code that may admit errors - this is particularly common when dealing with variables and objects that have been assigned values by users - you must turn on error handling if you want any preemptive handling at all.
To turn on the handler, all you need do is include the On Error statement. What comes after the On Error words depends on which type of error handling you’d like to implement. We’ll dive into this in a few seconds.
A bit of terminology: an error handler catches an error and directs execution to code that will rectify the problem (or ignore the problem). An exception is the error instance itself, such as a particular Divide by Zero attempt.
Two Main Methods
There are two main ways to deal with errors in VBA:
- skipping runtime errors altogether using
Resume Next - explicitly handling the errors with
GoToand a custom error handler
Skipping with Resume Next
Skipping errors with Resume Next is a risky practice, because it basically erases part of your algorithmic logic. On Error Resume Next tells VBA to simply skip to the next line of code when an error occurs. If the next line relies on the previous one and you still have the error handler turned on, VBA will skip the new line, too. And so part of your algorithm disappears into nothingness. You are ignoring the problem.
Next month’s article will discuss this in more detail.
Handling with Custom Code and GoTo
You can also handle the error directly by turning on a specific handler with the GoTo qualifier. This qualifier tells VBA to jump to a specific section further down in the code block. Which handler you turn on is specified by the label, as in GoTo [label], where the label is just a line of code with that name. Let’s walk through an example.
Set Up Error Handling Code
Let’s say you want print the output of some code into a sheet, and the user can choose which sheet to place it on. If you were being robust you might create a userform with a dropdown menu listing all of the sheets in the workbook, an option to add new ones, and then either jump to the sheet or create the new sheet.
But maybe you’re feeling lazy or have a short deadline, so you handle it with errors:
Sub output_to_sheet()
sheet_name = InputBox("Enter the name of the sheet on which to place output")
On Error GoTo noSheetHandler 'turn on the noSheetHandler handler
Sheets(sheet_name).Activate
On Error GoTo 0 'turn off the noSheetHandler handler
'code to fill in the sheet
Exit Sub
noSheetHandler:
MsgBox ("No such sheet! Try again")
'maybe some other code to handle the error differently
'Resume Next 'uncomment this line to return to the line after the one which threw the error
End SubYou don’t turn on the error handling until the line
This example simply ends the subroutine, and the user will have to run it again. It would be wise to perform all the calculations in another subroutine then call
To return to the main section of code after the error handler completes, use Resume Next, which returns execution to the line after the one that threw the error. Note the handler On Error GoTo 0.
Turn Off the Error Handler with GoTo 0
GoTo 0 simply turns off the currently enabled handler (
Remember to turn off a handler once it is no longer relevant. If you don’t turn it off, a different error that occurs later would automatically try to go to your old label. This old label might be an irrelevant handler to the new error encountered. That makes diagnosing downstream problems very frustrating.
Placement of the Handler
It’s important that error handling code is placed within the subroutine. For that reason, your label (here End Sub statement.
However, the VBA compiler cannot distinguish what is error handling code. You can, if desired, use labels in other ways. For example, you could do something like this (not recommended for your own sanity):
If amount > balance Then GoTo balance_lines
'other code
'other code
'other code
balance_lines:
'balance codeThis is not error handling code at all, but to the VBA compiler it’s all just code. Thus, if you don’t want to run error code every time a subroutine runs, you must place it after an Exit Sub statement. Every error handler should come before the End Sub but after the Exit Sub statement.
An Alternative Error Handling Approach
You can deal with errors by using On Error, but it can quickly become confusing if you’re using this method for many different types of errors and have many labeled handlers.
An alternative method is to check for valid inputs as early as you can to reduce the number of downstream error handling situations that will arise. For example, in the interest example above, it would be possible to use IF statements to check if a user inputs zero and rectify the issue before any runtime error was raised. This is preferred way to handle errors. Error prevention is always better than error mitigation.
Checking input variables like this can reduce the amount of error handling you need to write using GoTo. Line jumping with GoTo can give you quite the headache when your code is hundreds of lines long and you’re struggling to debug.
Conclusion
This was a very basic overview of error handling in VBA using On Error GoTo [label]. Using the label allows you to create custom code to handle expected errors that only runs if an error is raised in its section of code.
You now know how to turn on error handling, how to guide execution flow to error handling code, and how to turn off the handler with GoTo 0. Next month we’ll publish an article on properly using the dual-role Resume Next, which is another very popular way to handle errors.
Check inputs as often as possible in the code to reduce the number of potential errors in the first place. Checking with IF statements, forcing strings to integers, and similar techniques can help prevent errors without resorting to handling actual runtime errors.
To make sure you don’t miss our next couple error handling tutorials, subscribe using the form below and share this article on Twitter and Facebook.
