How to handle VBA’s four most common errors in Microsoft 365 apps


Correcting Microsoft VBA code is easier if you understand what the basic error messages mean.

Visual Basic code listing
Image: PatrickCheatham/Adobe Stock

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

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

Figure B

VBA highlights the erring line.

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

You must declare variables if Option Explicit is enabled.

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

VBA can’t compile this procedure because it doesn’t have all the information it needs to execute the If statement.

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

This code asks for an array member that doesn’t exist.

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.



Source link