Vba error types in Excel

There are two types of VBA error that you may encounter when executing an Excel macro. These are:

1.Compile Error

2.Runtime Errors

Compile errors occur at two instances:

1.While typing in the editor, when there is a syntax error, then the VBA editor will immediately highlight this, either by popping up a message box or

by highlighting the error in red, depending on the setting of the Auto Syntax Check option.

2.During the compiling of program, but before the execution. This arises in cases where the syntax is correct, but the values are not found.

The following examples illustrates this:


  1. Sub compile_error1()
  2. Dim i As Integer
  3. Sheets("unknown").Range("A1") = CStr(i)
  4. End Sub

In the above example, there is no sheet called "unknown" and hence it results in an error as shown below:

excel vba error types

Case :2

excel vba error types

Note that, the above error rises within the editor while typing, highlighting the line with Red forecolor.

Runtime ErrorS

Runtime errors are the one which occurs during the runtime or while the program is executing, after successful compilation, but just before finishing.

One most common used example of this type is "Divide by Zero", which occurs, when a number is divided by 0.

The following example demonstrates this:

excel vba error types


You can find similar Excel Questions and Answer hereunder

1) How to enable or disable macros execution or loading in Excel

2) How to use the scenario manager in Excel

3) How can I identify the cells that are dependent on a particular cell?

4) Is there a way I can average a range of numbers even if there is an error value in range?

5) How can I get row count of filtered data?

6) How to concatenate strings in vba in Excel

7) Vlookup to return multiple values in same cell in Excel

8) How can I turn off Alerts using VBA?

9) I have a cell with nested formulas that returns an error. In Excel, how can I step through the formulas to help with debugging?

10) What are the main cell objects in VBA


Here the previous and next chapter