It’s easier to fix Microsoft’s VBA code if you understand what the basic error messages mean.
Even experts inadvertently introduce errors into their VBA code. Most of us see some errors more than others, and knowing what these mean helps to fix them quickly. In this tutorial, I will introduce you to the four most common VBA mistakes. You will learn what they mean and how to fix them.
UNDERSTAND: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)
I’m using Microsoft 365 on Windows 10 64-bit systems, but most of these errors can appear on almost any version. None of the web apps support VBA.
How to distinguish VBA error types in Microsoft 365 applications
You will encounter three types of errors when testing your VBA code: Runtime, syntax, and compilation. The VBA runtime error occurs during execution and includes the following errors:
- Invalid reference: Your code contains a reference to a variable or object that does not exist.
- Invalid data: Your code is trying to refer to data that does not exist.
- Divide by 0: Your code tries to divide by 0.
You can deal with these errors by fixing the code or allowing the code to run as is and using error handling to deal with them.
VBA syntax errors occur due to typos, typos, and errors in the statement itself, such as not including all required arguments.
The VBA compilation error occurs when VBA cannot compile the code. Compilation turns the source code into executable instructions that you can’t see.
Now let’s look at the most common VBA mistakes.
How to fix error 13 in VBA
Probably the most common error is Runtime Error 13: Type Mismatch. You will see this error when your code tries to specify a value that does not match the data type of the variable or argument.
For example, suppose you declare a variable as an integer and then try to convert that variable to a text string. In this case, VBA will return the mismatch error shown in Picture A. Click Debug and VBA will highlight the line causing the error, as shown in Figure BUG.
Picture A
Figure BUG
Fixing this runtime error is usually easy, as is the case with this simple example. Developers often use the variable name i to denote the Integer data type, so this error will be obvious. On the other hand, most properties return a specific data type. If the variable does not match the data type of that property, the line will return an error.
If the error doesn’t occur to you, try declaring the variable as a Variant – if that works, a little more research will help you determine the exact data type the property requires.
How to fix syntax errors in VBA
VBA often exposes typos and typos as you type them so they’re easy to correct. On the other hand, some are harder to find, but VBA will try to help.
SIZE shows a compile error – a basic syntax error. I forgot to declare the variable i, so VBA marks it and shows an error. It’s easy to identify the error when VBA actually highlights the error variable.
SIZE
The solution is to add a declaration statement:
Dim i As Integer
You must fix the syntax error for your code to run. With experience, they will be easy to spot. VBA shows these kinds of syntax errors when it tries to compile the code.
How to fix general compilation errors in VBA
The compilation error occurs before the code actually runs. It happens in that nanosecond between the moment you call the code and VBA tries to execute it. If VBA cannot compile the code, you will see a message similar to the one in Visualization.
Visualization
There’s nothing wrong with the individual line, but VBA can’t complete the If statement because it’s incomplete. The If statement requires something followed by the Then keyword – if the condition is true, what does the code do? That information is not there.
You must fix the compilation error before you can run the code.
How to fix runtime error 9 in VBA
This error usually occurs when you request a value that does not exist in the context. For example, let’s say you are working with an array of five values and you require a sixth value. It does not exist and VBA will run this error as shown in Figure E.
Figure E
The error description is very helpful, and when you click Debug, VBA will select the line to correct the error. Immediately, you know that you have requested a value that does not exist in the array. From there, it’s usually easy to spot the error.
There are many more types of errors, but these four are some of the most common errors that almost everyone encounters. Now that you know what causes these errors, you will find it easy to resolve them.
