Even experts inadvertently introduce errors into their VBA code. Most of us see some errors more than others, and knowing what these errors mean helps correct them quickly. In this tutorial, I’ll introduce you to four of VBA’s most common errors. You’ll learn what they mean and how to fix them.
SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)
I’m using Microsoft 365 on a Windows 10 64-bit system, but most of these errors can be present in almost any version. None of the web apps support VBA.
How to discern the types of VBA errors in Microsoft 365 apps
You’ll run into three types of errors when testing your VBA code: Runtime, syntax and compile. VBA runtime errors occur during execution and include the following mistakes:
- Invalid reference: Your code contains a reference to a variable or object that doesn’t exist.
- Invalid data: Your code is trying to reference data that doesn’t exist.
- Division by 0: Your code attempts to divide by zero.
You can handle these errors by correcting the code or allowing the code to run as is and using error-handling to deal with them.
VBA syntax errors occur due to misspellings, typos and mistakes within the statement itself, such as not including all required arguments.
VBA compile errors occur when VBA can’t compile the code. Compiling translates the source code into executable instructions that you can’t see.
Now let’s look at VBA’s most common errors.
How to fix Error 13 in VBA
Perhaps the most common error of all is Runtime Error 13: Type Mismatch. You’ll see this error when your code tries to assign a value that doesn’t match the variable or argument’s data type.
For instance, let’s suppose you declare a variable as an integer and then try to pass that variable a text string. In this case, VBA will return the mismatch error shown in Figure A. Click Debug and VBA will highlight the line that’s throwing the error, as shown in Figure B.
Figure A
Figure B
Correcting this runtime error is often easy, as is the case with this simple example. Developers often use the variable name i to denote an Integer data type, so this error should be obvious. On the other hand, most properties return a specific data type. If the variable doesn’t match that property’s data type, the line will return an error.
If the mistake doesn’t jump right out at you, try declaring the variable as a Variant — if that works, a bit more research will help you determine the exact data type the property requires.
How to fix a syntax error in VBA
VBA usually exposes typos and misspellings as you enter them so they’re easy to fix. On the other hand, some are more difficult to find, but VBA tries to help.
Figure C shows a compile error — a basic syntax mistake. I forgot to declare the i variable, so VBA highlights that variable and displays the error. It’s easy to determine the mistake when VBA actually highlights the erring variable.
Figure C
The solution is to add a declaration statement:
Dim i As Integer
You must correct syntax errors for your code to run. With experience, they’ll be easy to spot. VBA exposes these types of syntax errors when it attempts to compile the code.
How to fix a general compile error in VBA
Compile errors occur before the code actually runs. It happens in that nanosecond between the time you call the code and VBA tries to execute it. If VBA can’t compile the code, you’ll see a message similar to the one in Figure D.
Figure D
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 to follow the Then keyword — if the condition is true, what does the code do? That information isn’t there.
You must fix compile errors before you can run the code.
How to fix runtime error 9 in VBA
This error usually occurs when you ask for a value that doesn’t exist within context. For instance, suppose you’re working with an array of five values, and you ask for the sixth. It doesn’t exist and VBA will run this error as shown in Figure E.
Figure E
The error description is helpful and when you click Debug, VBA will select the erring line. At once, you know that you’ve asked for a value that doesn’t 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 everybody runs into. Now that you know what causes these errors, you should find it easy to resolve them.
