Correcting Microsoft VBA code is easier if you understand what the basic error messages mean.
Even experts accidentally introduce errors in their VBA code. Most of us see some errors more than others, and knowing what these errors mean will help you correct them quickly. In this tutorial, I’ll introduce you to four of the most common mistakes in VBA. You learn what they mean and how to solve them.
TO SEE: Windows, Linux, and Mac Commands Everyone Should Know (Free PDF) (TechRepublic)
I use 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.
Differentiate the types of VBA errors in Microsoft 365 apps
You will encounter three types of errors when testing your VBA code: runtime, syntax, and compile. VBA runtime errors occur at runtime and include 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 reference data that does not exist.
- Divide by 0: Your code tries to divide by zero.
You can handle these errors by correcting the code or making the code work as it is and using error handling to fix them.
VBA syntax errors occur due to misspellings, typos, and errors in the statement itself, such as not including all required arguments.
VBA compile errors occur when VBA is unable to compile the code. Compile translates the source code into executable instructions that you can’t see.
Now let’s look at the most common errors of VBA.
How to fix error 13 in VBA
Perhaps the most common error is Runtime Error 13: Type Mismatch. You see this error when your code tries to assign a value that does not match the data type of the variable or argument.
For example, let’s assume you declare a variable as an integer and then try to pass that variable a string of text. In this case, VBA returns the mismatch error shown in Image A† Click Debug and VBA will highlight the line causing the error as shown in Figure B†
Image 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 does not match the data type of that property, the rule returns an error.
If the error doesn’t immediately come to mind, 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 a syntax error in VBA
VBA usually exposes typos and misspellings as you enter them so they are easy to correct. On the other hand, some are harder to find, but VBA tries to help.
Figure C shows a compile error — a fundamental syntax error. I forgot to declare the variable i, so VBA will highlight that variable and display the error. It’s easy to determine the error when VBA actually highlights the erroneous variable.
Figure C
The solution is to add a declaration statement:
Dim i As Integer
You must correct syntax errors before your code can run. With experience they are easy to spot. VBA exposes these kinds of syntax errors when it tries to compile the code.
How to solve a general compile error in VBA?
Compilation errors occur before the code is actually executed. It happens in that nanosecond between when you call the code and VBA tries to run it. If VBA can’t compile the code, you will see a message similar to that in Figure D†
Figure D
There is nothing wrong with the single line, but VBA cannot complete the If statement because it is incomplete. The If statement requires something to follow the Then keyword – if the condition is true, what does the code do? That information is not 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 does not exist within the context. For example, suppose you are working with an array of five values and you ask for the sixth. It does not exist and VBA will output this error as shown in Digits E†
Digits E
The error description is helpful and when you click Debug, VBA selects the erroneous line. You immediately know that you have requested a value that is not in the array. From there it is usually easy to spot the error.
There are many more types of mistakes, but these four are some of the most common mistakes that almost everyone encounters. Now that you know what causes these errors, it should be easy to fix them.
