Types of Errors in VBA for Excel
Learn how to deal with and resolve VBA errors in Excel
Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
Types of Errors in VBA for Excel
Users will often encounter different types of errors in VBA. Below, we offer several strategies for dealing with and resolving them. We will go through techniques you can use to interrogate your code during testing, as well as methods of catching errors at runtime.
Testing and tracking down different types of errors in VBA code can be time-consuming and frustrating. It’s not uncommon to hear programmers say that well over 30% of their time is spent on testing.
Over time, you’ll get better at avoiding errors, but it’s unlikely you’ll ever write perfect code, first-time. By learning how to avoid, deal with, and track down VBA errors, you’ll save yourself a ton of time in the future.
Types of VBA Errors
To make things easier, we’ve categorized the types of coding errors into three groups. For each group, we’ll explore some examples, and then discuss how you might investigate and resolve them.
1. Syntax errors – A specific line of code is not written correctly
2. Compile errors – Issues that happen when putting together lines of code, though the individual lines of code seem to make sense
3. Runtime errors – When the code is usually correct in principle, but an action taken by the user or the data being used leads to unexpected errors.
What are Syntax Errors?
Syntax errors are VBA’s way of telling you if your code makes sense, at the most basic of levels. We can make a simple comparison to the rules of writing a sentence in English:
- A subject is required.
- A verb is required.
- A capital letter is required at the beginning.
- Punctuation is required at the end.
- Objects, adjectives, and other grammatical features are optional.
Jane found 18 errors in her VBA code.
In a similar manner, VBA imposes certain rules over what is required in certain situations. For example, when initiating a basic conditional IF statement in your code, you must replicate the following syntax rules for the first line of an IF statement;
- It must begin with If
- Next must be a logical test, such as x>10
- Finally, it must end with the word then
If NumberErrors>20 then
If you break the above rules of English or VBA, your grammar (English) or syntax (VBA) is considered incorrect.
How to Identify a Syntax Error
The VBA editor highlights syntax errors in red, allowing you to easily identify and resolve them. In addition, if you try to run your code or start a new line, you’ll be presented with the syntax error message below.
Basic syntax error:
Syntax error message box:
To make sure the checks occur as you’re typing, enable the “Auto Syntax Check” in the Options menu.
How to Resolve Syntax Errors
Syntax errors represent mistakes in the way a specific line of your code is written. They must be fixed before you can run your code. With experience, the red text will be all you need to spot the error. But if you’re still stuck, just search online for “if statement syntax vba” or whichever variation suits your needs, and you’ll find plenty of examples of the specific line you’re trying to write.
What are Compile Errors?
Compile errors refer to a wider group of VBA errors, which include syntax errors. Compile errors also identify problems with your code when considered as a whole. The syntax of each individual line may be correct, but when put together, the lines of your code don’t make sense. Compile errors are highlighted when you compile or run your code.
How to Identify a Compile Error
When you run your VBA code, compile errors will be presented in a VBA dialog box as per the examples below.
Alternatively, if your project is long or complex and involves multiple routines, it can be helpful to compile your code before you run it. It prevents situations where half of your code runs successfully and then an error pops up. You can compile your VBA code by clicking Debug Menu → Compile VBA Project.
Example 1
A compile error for a missing “End if” part of an IF statement. Every individual line in the code is correct, but together, they don’t represent a complete IF statement.
Example 2
In this example, the VBA compile process has detected a syntax error, highlighted in red. VBA indicates that the code below is missing a closing bracket on the function.
Example 3
Compile errors are common when using Option Explicit and occur when a variable has not been explicitly defined. With Option Explicit activated, a Dim statement is required to declare all variables before they can be used in your code.
How to Resolve Compile Errors
VBA compile errors will prevent affected routines from running. Until you fix them, your code cannot be interpreted correctly by VBA. The error message boxes often provide more helpful advice than with syntax errors.
If it’s not immediately obvious to you what the issue is from the error message, try using Microsoft’s Help Center to get an idea of exactly how your statement or function should be written. Here’s an example of the “Block if without end if” error message on the Microsoft website.
What are Runtime Errors?
Once you’ve fixed syntax errors and compile errors, you’re ready to start testing or running your code properly to see how well it executes your desired actions or calculations. We call the period of testing, or live execution of code, “runtime.”
Runtime errors cannot be detected by simply looking at the code; they are a result of your code interacting with the specific inputs or data at that time.
Runtime errors are often caused by unexpected data being passed to the VBA code, mismatching data types, dividing by unexpected zeros, and defined cell ranges not being available. Runtime errors are also the most varied and complex to track down and fix.
How to Identify Runtime Errors
The VBA Debug function highlights the problem code in yellow and gives us a small clue as to what is causing the problem.
Example 1
While the code below simply divides one number by the other, there are some instances where it won’t be possible. For example, if cell A1 = 2 and cell A2 = 0, it’s not possible to divide by zero. The runtime error box includes a debug button, which highlights the problem code.
Example 2
In this example, I created an array to collect the names of n companies in my worksheet. I then tried to reference the sixth item, which may or may not exist at run time. VBA presents a runtime error message and allows me to press Debug to investigate the issue.
Once I press Debug, VBA highlights the line of code that is causing the problem. Now it’s down to me to use the “Subscript out of range” message to work out what’s going wrong. The subscript out of range error generally means that your code is good in principle, but that the specific item you’re looking for can’t be found. Most commonly, it will happen when referencing cells or ranges in Excel, as well as arrays in your VBA code.
To make sure the Debug function is enabled, in the Options > General > Error Trapping menu, the default state should be to “Break on Unhandled Errors.” Leave the option checked.
How to Resolve Runtime Errors
Since runtime errors are varied and complex in nature, the solutions are also varied. The two general methods to reduce runtime errors are:
Error Trapping
It’s a good idea to put some thought into what possible scenarios your code might encounter. Even better is testing your code on practice data or with real users. It will allow you to recognize such scenarios in your VBA, with code branching into different outcomes, depending on if errors exist.
Example 1
Here, the ‘On Error Resume Next’ line forces the code to continue, even if an error is detected. We then use the If Err > 0 test to create a warning message, only if the error exists. The ‘On Error Goto 0’ resets VBA to its default state.
Example 2
Here, the code is calculating income per day, based on a monthly income, and a defined number of days. If the number of days is not provided, we skip to a defined point in our code called “NoDaysInMonthProvided:,” where we use the average number of days in a month as a base assumption. While it is simplistic, it shows clearly how to trap an error and skip to a different point in your code.
Clear User Instructions
Clear documentation and worksheet labels will ensure that your users interact with your model in the way that you intended. It will help prevent them when inputting text, instead of numbers, or leaving zeros that may later impact your code.
Summary
There are three key types of errors in VBA that can affect your code in different ways. It’s important to activate VBA’s error-detecting options, such as the debugger and auto syntax checking. They will help you to establish the location and nature of the errors.
More Resources
CFI is the official provider of the global Commercial Banking & Credit Analyst (CBCA)™ certification program, designed to help anyone become a world-class financial analyst. To keep advancing your career, the additional resources below will be useful:
