How to fix VBA errors in Excel?

Even users who have much experience with VBA code in Excel sometimes have errors, errors can still occur normally, the following article will guide you all the ways to fix VBA errors in Excel. .

VBA in Excel allows users to automate tasks with repetitive tasks, greatly improving work performance. However, errors during use VBA in Excel is inevitable. Read along with the article All frequencies of how to fix VBA errors in Excel below of to learn how to fix VBA errors.

Table of Contents:

1. Types of VBA errors in Excel

1.1. Syntax error (Syntax Error)

1.2. Compile Error

1.3. Run-time Error

1.4. Error Logical Error

2. How to fix VBA errors in Excel

2.1. Method 1: Use Debug to find compilation errors / syntax errors

2.2. Method 2: Configure error settings (Handled and Unhandled errors)

3. Handling VBA errors with the On Error commands

4. The properties and methods of the Err Object object

5. Summary

1. Types of VBA errors in Excel

Before diving into the ways and steps to fix VBA errors, firstly read along with to learn some common types of VBA errors in Excel.

There are 4 types of Excel VBA errors, including:

– Syntax error (Syntax Error).
– Compile Error (Compile Error).
– Run-time Error.
– Logical Error.

1.1 Syntax Error

As the name implies, Syntax Error occurs when VBA detects an error or syntax error in the code.

For example, for example, if you forget a part of the required command / syntax, then a compile error is displayed on the screen.

In the code below, after pressing Enter after the second command line, the screen displays a compile error message. The cause of the error is a command IF there must be an order Then to accompany, however in this code the Then command is missing.

How to fix errors in Excel

Note: When entering the code in Excel VBA, it will check every single statement as soon as we press Enter. If VBA detects a command or part of the syntax is missing, it will immediately display a message on the screen so we can know where the cause is.

To ensure the syntax error is displayed whenever an error or error occurs, make sure you have enabled it Autosyntax. To do this, click Tools, Then click Options. In the Options dialog box, ensure the option Auto Syntax Check has been activated.

How to fix errors in Excel 2

In case if the Auto Syntax Check option is disabled, turned off, VBA will still display the line with the error syntax but not the error message dialog box.

1.2 Compile Error

Compile errors occur when a certain component is missing to run the code.

For example, when running the code below on the screen will display an error message. The reason is command use IF Then without command End If.

Syntax errors are also a type of compilation error. Syntax errors occur as soon as we press Enter and VBA detects a missing part or syntax. A compilation error also occurs when VBA does not find something when entering the code, but occurs when the code is compiled or executed.

How to fix errors in Excel 3

VBA checks each command line once we are entering the code as a syntax error mark as soon as it detects that something is wrong and when we press Enter. Conversely, compilation errors are only determined when VBA analyzes the entire code.

Here are some common situations where compilation errors are common:

– Use the command IF without command End IF.

– Use the command For with next.

– Use the command Select without command End Select.

– Do not declare variables (only works when the Option Explicit option is enabled).

– Calling a function does not exist (or the wrong parameter).

Note: When adding options Option Explicit, we will be required to declare all variables before running the code. If there is an undeclared variable, VBA will display an error message.

1.3 Run-time Error

Run-time error occurs while the code is running.

For example, if running the code used to open an Excel workbook but the workbook is not available (deleted or renamed), the code will return a run-time error message.

How to fix Excel errors in Excel 4

When a run-time error occurs, the code will stop running and an error message will be displayed on the screen explaining the cause of the error.

By clicking Debug button to highlight the part of the code that is causing the error.

How to fix errors in Excel 5

After fixing the errors, you can click Run button on the toolbar or press pHey F5 to continue running the code. Or can click End button to get rid of the code.

Important note: In case if clicked End button on the dialog box, it will stop at the error code. All previous lines of code will be executed.

1.4 Logical Error

Logical errors do not cause the code to stop, but may return false results. This is also the most difficult type of errors to overcome. The compiler does not mark these errors and we have to do it manually.

There are several ways to fix logical errors in Excel:

– Insert Message Box in locations where code and values ​​/ data are highlighted.

– Instead of running the entire code at once, we run the code line by line. To do this, click anywhere in the code and press the F8 key to execute each line of code. This allows us to easily identify errors in each line of code.

2. How to fix VBA errors in Excel

Method 1: Use Debug to find compilation errors / syntax errors

The ideal solution is to compile the code before running. To compile the code, click the option Debug on the toolbar, then click Compile VBAProject.

How to fix Excel errors in Excel 6

When compiling the VBA project, it will go over the code and identify errors (if any).

In case, if an error is detected, VBA will display an error message dialog box, after correcting the error, we will have to run the compiler again to find other errors (if any).

If the code has no errors, the Compile VBAProject option will be grayed out.

Note: Code compilation only detects syntax errors and compilation errors, cannot detect run-time errors.

Method 2: Configure error settings (Handled and Unhandled errors)

Also before we start executing the code, we should check the Excel VBA settings.

Access the VBA toolbar, then click Tools =>Options.

On the Options dialog box, click General tab, guaranteed options Break on Unhandled Errors in Error Trapping chosen.

– Option Break on All Errors: stop executing code on all types of errors, even if you have applied techniques to handle these errors.

– Option Break in Class Module: Stop executing code on all unresolved errors (Unhandled), and if using objects like Userforms, it will also break these objects and mark the exact line that caused the error.

– Option Break on Unhandled Errors: Stop code only for errors not processed. This is the default setting to ensure that unresolved errors are not displayed on notifications. If using objects such as Userforms, this option does not mark the line that causes an error in the object, but only marks the line referring to that object.

Note: If you work with objects like Userforms, you can change this setting to Break on Class Modules. You can also use the second option, which will display the specific line in the object that is causing the error.

3. Handling VBA errors with the On Error commands

See more ways handle VBA errors by using Error commands here

4. Learn the properties and methods of the Err Object (Err object)

See the article to find out Err Object attribute and method here.

5. Summary

Here is a summary of some ways to handle VBA errors in Excel:

1. Use the command On Error Go [Label] at the top of the code to make sure any errors occur are handled.

2. Use only commands On Error Resume Next when you know for sure errors can occur. In the case if the command is used with errors, the command will ignore the error and the code will continue to run. Alternatively we can use a combination of commands On Error Resume Next with Err.Raise if you want to ignore specific error types.

3. When using an error handler, be sure to use Exit Sub before dealing with it. This is to ensure the error code handler is only executed when an error occurs, otherwise it will always be executed.

4. Use multiple handlers to trap different types of errors.

The above article has just taught you all about how to fix VBA errors in Excel. If you have any questions or questions, you can leave your comments in the comments section below.
In addition, you can learn about how to open VBA in Excel with specific instructions on Taimienphi, how Open VBA in Excel It is also quite simple, you can do it easily to serve your programming jobs.


Add a Comment

Your email address will not be published. Required fields are marked *