Home Source code How to handle the four most common VBA errors in Microsoft 365 Apps

How to handle the four most common VBA errors in Microsoft 365 Apps

0

Correcting Microsoft VBA code is easier if you understand the meaning of basic error messages.

Image: Patrick Cheatham/Adobe Stock

Even experts inadvertently introduce errors into their VBA code. Most of us see certain errors more than others, and knowing what those errors mean helps to fix them quickly. In this tutorial, I’ll introduce you to four of the most common VBA errors. You will learn what they mean and how to correct them.

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 applications support VBA.

How to Discern Types of VBA Errors in Microsoft 365 Apps

You will encounter three types of errors when testing your VBA code: execution, syntax, and compilation. VBA runtime errors occur during 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 doesn’t exist.
  • Division by 0: Your code is trying to divide by zero.

You can handle 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 misspellings, typos, and errors in the statement itself, such as not including all required arguments.

VBA compilation errors occur when VBA cannot compile code. Compilation translates source code into executable instructions that you cannot see.

Now let’s look at the most common VBA 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 data type of the variable or argument.

For example, 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 is giving the error as shown in Figure B.

Figure A

This error occurs when you define a variable using the wrong data type.

Figure B

VBA highlights the wrong line.

Fixing 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 this property, the line will return an error.

If the error isn’t obvious to you, try declaring the variable as a Variant – if that works, a little more research will help you determine the exact data type required by the property.

How to fix a syntax error in VBA

VBA usually exposes typos and misspellings as you type them so they’re easy to correct. On the other hand, some are harder to find, but VBA tries to help.

Figure C displays a compilation error — a basic syntax error. I forgot to declare variable i, so VBA highlights that variable and shows the error. It’s easy to determine the error when VBA highlights the erroneous variable.

Figure C

You must declare variables if Option Explicit is enabled.

The solution is to add a declaration statement:

Dim i As Integer

You must fix syntax errors for your code to run. With experience, they will be easy to spot. VBA exposes these kinds of syntax errors when it tries to compile code.

How to Fix a General Compilation Error in VBA

Compilation errors occur before the code actually executes. It happens in that nanosecond between when you call the code and VBA tries to run it. If VBA cannot compile the code, you will see a message similar to Figure D.

Figure D

VBA cannot compile this procedure because it does not have all the information it needs to execute the If statement.

There is nothing wrong with the individual 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? This information is not there.

You must fix compilation errors 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, suppose you are working with an array of five values ​​and you request the sixth. It does not exist and VBA will run this error as shown in Figure E.

Figure E

This code requests an array member that does not exist.

The error description is helpful, and when you click Debug, VBA selects the wrong line. Immediately you know that you requested a value that does not exist in the array. From there, it’s usually easy to spot the error.

There are many other types of errors, but these four are some of the most common errors that almost everyone encounters. Now that you know what is causing these errors, you should find it easy to troubleshoot.