Types of Errors in VBA for Excel
In general, there are three broad categories of errors that you will encounter after running the code.
Types of Errors in VBA for Excel
So far, we have only covered articles on how you can write your own code in VBA. We write the code using the VBA editor and run the code, which automates the repetitive task based on a single click.
If you are still unsure what VBA or Visual Basics for Application is, let us repeat one more time - it is a human-readable programming language that lets you automate simple and complex tasks by asking Excel to do a series of actions.
However, a scenario may arise wherein the code may not run successfully. For example, it may break, throw an error, and, as expected, wouldn’t return the final result.
Errors are common and are integral to building a robust macro. If you have written a code and haven’t faced a single error, there are two possibilities:
- You are a Level 100 Excel Wizard
- You did not dig deep enough while building the macro
While building the macro, you must understand all the intricacies and the behavior the data might show once the code is run.
For example, sometimes, there is a possibility of data getting completely wiped, the code keeps running in a loop, or the same data might even get pasted everywhere on the spreadsheet.
In this article, we will see different types of errors you will usually encounter while writing a VBA code and executing them in the spreadsheet.
Key Takeaways
- You will encounter three different types of errors in Excel - syntax, compile, and runtime.
- Syntax errors result from using incorrect syntax in the VBA editor.
- Compile errors occur when the code ‘may’ be correct but inappropriately placed or compiled.
- Finally, the runtime error occurs when the macro crashes due to its inability to find a certain syntax or indefinite loop.
- All these errors can be handled by referring to the Microsoft Help Center or a website like StackOverflow.
- Alternatively, you can use the ‘On Error..’ syntax to overlook most VBA errors.
- There are three variations of On Error - 1) On Error Resume Next 2) On Error Goto 0 3) On Error Goto ‘Text.’
- However, reviewing the code to correct the syntax errors is always advisable, as they may be the easiest to fix.
Categories of VBA Errors In VBA For Excel
In general, there are three broad categories of errors that you will encounter after running the code. They are:
1. Syntax errors
These types of errors are usually encountered when a certain line of code is not written correctly.
This is Excel’s way of suggesting that the particular line of code may be incorrect. In such a case, you need to find the syntax error and re-type it in the VBA editor.
Suppose we have the code as illustrated below:
In the example above, we see that we have used the IF statements in the VBA code but haven’t followed through with the ‘then’ statement. Thus, in this case, Excel returns the error as illustrated below:
Even though the header says compile error, you see the underlying reason that it is a ‘syntax error.’ Thus, you need to review the syntax to understand what went wrong in the code.
So how do you even identify the syntax errors?
The easiest way is to enable the ‘Auto Check Syntax’ from the options menu. This will automatically highlight the syntax errors while you are writing the code.
To enable the ‘Auto Syntax Check,’ click on Tools > Options in the VBA editor box, which opens the dialog box as illustrated below:
Thus, you will get the syntax errors in the VBA editor as illustrated below:
Next, you can make all the necessary changes now in the VBA editor.
Even after identifying the syntax error, if you are unable to rectify the error, then you can always check out websites like StackOverflow, which gives you in-depth information on how to solve such issues.
2. Compile errors
The errors that occur due to the compilation of entire code fall under this category. The code might be correct, but the sequence in which the code is written might be wrong.
The errors due to indentations can also be included in this category of errors. For example, suppose you have the VBA code as illustrated below:
We see that we haven’t used the End If block after using the If…Then syntax. As a result, the VBA editor shows the below error:
Some errors are easily identifiable and can be worked on immediately. However, it may get a bit difficult if you have used multiple If blocks in the macro code.
3. Runtime errors
Indefinite loops usually fall into this category. The code ‘may’ be correct, but an error may occur when executing the code due to some variable assumption or inability to find a particular value.
For example, consider the example below:
Here, the entire code seems to be okay; only the error here is in the line of code Worksheets(“Sheet1”).Range(“D2”).Past where we have used the syntax as ‘Past’ instead of ‘Paste.’
Thus, Excel enters into a loop to find the syntax in its database. Still, when it’s unable to find it, it returns the run-time error as illustrated below:
We have a description as ‘Object doesn't support this property or method,’ which we discussed earlier. The two objects, ‘worksheet’ and ‘range,’ could not fund the property ‘Past,’ and hence we get the run-time error.
Getting additional help on the VBA errors
We know that these error dialog boxes are not enough to get the accurate help needed to work on these errors.
What else can you do?
Microsoft has built a perfect product and a ‘how to’ guide on every issue you might encounter while working in Excel. All you need to do is visit Microsoft’s help Center and look for the exact problem that you are facing.
Alternatively, you can also directly google the issue that you are facing. For example, if it’s related to the If...End If block, then look for it and open the Stackoverflow related link to find the solution.
You can even add your queries here or look for the reference codes, which can be further tweaked and used in your code.
However, ensure that you do not upload organizational ‘crucial information’ along with the code since anyone can view what you have posted on the website.
Believe me; if there’s something that has helped me over the years in writing macros, then it was reviewing my code. These solutions are offered over the website, and thinking about how I could improve or ultimately avoid them while creating the macro.
Error Handling in VBA
Alright, now you know the types of errors and what you can do if you encounter one of those in Excel.
But there should also be an opportunity to address them in the code itself, right?
Who would want to review the code because a single variable value messes up the code every time?
In fact, some of the errors are just unavoidable, and thus, you need to tweak your code so that it can overlook the error and move on to the next value.
This is where the ‘On Error’ syntax comes into play.
You can use the ‘On Error’ syntax in Excel in several ways:
a. On Error Resume Next
On Error Resume Next is usually used when you want to ignore the errors and move on to the next value.
This is used in combination with loops such that if a particular value is missing in the dataset, it will move on to the next value and form the mentioned operation.
For example, suppose we have the code as illustrated below:
Initially, in our spreadsheet, we have the value ‘hello’ in cell B2. We have written a code that checks if the cell in column B is empty and then assigns a value between 1 to 1000 based on a loop.
If the cell is not empty, it will break the code and return an error. However, we have used the ‘On Error Resume Next’ syntax, which will ignore the text string in cell B2 and go ahead with numbering the cells in column B.
When we run the code by pressing the F5 key, we get the result:
b. On Error Goto 0
On Error Goto 0 syntax is used when you want to revert the error checking back to its factory setting, i.e., it will try to identify all the errors in the code and highlight them to the user.
When you use the On Error Resume Next, it usually ignores all the errors and moves on to the next one.
Although some of those might be intentional skips, not all of them require such special attention.
In such a case, you can use the ‘On Error Goto 0’ syntax, which will revert the error-checking status back to normal.
What we are saying is that ‘On Error Goto 0’ is usually used in conjunction with ‘On Error Resume Next’ to give better results for the VBA code.
Suppose we have the code as illustrated below:
Also, we have the values in the spreadsheet below:
The idea is that only the value in cell B2 is intentional; every other text string or non-empty cell in column B will be considered an error value. Thus, when we run the code, it will only skip the value in cell B2. On pressing the F5 key, we get the result:
We had included the MsgBox syntax to get the result in the form of a message box. If we are more specific in the code, we can even return the cell with an error value.
c. On Error Goto ‘Text’
On Error Goto ‘Text’ is used when you want to skip a significant chunk of code and jump on to the next section. There could be various reasons for it. For example, you want to evaluate a value based on criteria B rather than criteria A.
The reasons could be innumerable, which are only explainable when you are writing the code.
What matters is what you want the code to do next, which directly jumps to the ‘text’ value.
Now, the ‘text’ here corresponds to any text value that can be used in the code. This would change based on what code you are writing in the VBA editor.
The text could vary from ‘error’ to ‘jump here’ - anything that you can think of as long as it's acceptable by Excel.
For example, suppose you have the code as illustrated below:
The spreadsheet has the value ‘Hello’ in cell B2.
We have written the code in such a way that if cell B2 is empty, the cell will have the value ‘Hello.’
However, we already have that text string in cell B2. In such a case, there’s a possibility that Excel might return an error.
Instead, we have used the ‘On Error Goto Text:’ syntax that directly jumps to the optional line of code that we want to execute.
This way, Excel directly jumps to Cells(3,2).Value = “How are you?” in cell B3.
Thus, each of these ‘On Error’ has a different purpose and must be used accordingly in the macro.
or Want to Sign up with your social account?