What are the Types of Errors Occurred In Excel VBA

VBA Compile Errors,Runtime Errors,Logical Errors
There are 3 types of VBA errors that you may encounter when executing your macro.

1.      Compilation Errors

2.      Runtime Errors

3.      Logical Errors(“Bugs”)

These VBA error types are discussed below.
------------------------------------------------------------------------------------------------------------------
Compile Errors :
Compile Errors are recognized by the VBA compiler as being illegal and they are highlighted as errors before your macro even starts to run.

If a Compile Error is an incorrectly formatted line of VBA code, then  the Vba Editor will immediately detect and highlight this, as soon as you attempt to move your cursor away from the specific line of code.

Alternatively, a compile error may be detected at the time you attempt to run your macro but before execution has started.

To find compilation errors, we use Debug->Compile VBA Project from the Visual Basic menu.
When you select Debug - >Compile , VBA displays the first error it comes across.
When this error is fixed, you can run Compile again and VBA will then find the next error.
Debug->Compile will also include syntax errors in it’s search which is very useful.
If there are no errors left and you run Debug - >Compile , it may appear that nothing happened. However, “Compile” will be grayed out in the Debug menu. This means your application has no compilation errors at the current time

Examples:

Compile Error indicating Data Type Mismatch used in Loop:

Compile Error indicating For without Next used in Loop:

Compile Error indicating Ambiguous Name used for Macro:

------------------------------------------------------------------------------------------------------------------

Runtime Errors:

Runtime errors occur during the execution of your code, and cause the code to stop running. This type of VBA error is also generally easy to fix, as you will be given details of the nature of the error, and shown the location where the code has stopped running.

Example:

If your code attempts to divide by zero, you will be presented with a message box, which states "Run-time error '11': Division by zero".

Sub DivbyZero

Dim x as integer

Dim y as integer

Dim z as integer

x=0

y=12

z= y/x

End Sub

Depending on the structure of your VBA project, the line of code that generated the VBA Error to be highlighted in your VBA Editor so that you can debug the code.

A list of the run time error codes is supplied on the Microsoft Help & Support Website ,and Some of the more common VBA error messages are shown in the table below.

5	-	Invalid Procedure Call
7	-	Out of Memory
9	-	Subscript Out of Range (this error arises if you attempt to access elements of an array outside of the defined array size - eg. if you define an array indexed from 1 to 10, then attempt to access entry no. 11)
11	-	Division by Zero
13	-	Type Mismatch (this error arises when you attempt to assign the wrong type of value to a variable - eg. define i as an integer, then attempt to assign the string "text" to i)
53	-	File Not Found (occurs when attempting to open a file)

Examples:

Runtime Error Indicating File Not Found:

Runtime Error Indicating Range Class Fail:

Runtime Error Indicating Path/File Access:

------------------------------------------------------------------------------------------------------------------
Logical Errors:
Logical Errors, also known as 'bugs', occur during the execution of the VBA code, and allow the code to continue to run to completion. However, the 'bug' may cause the macro to perform unexpected actions or return an incorrect result .
These errors are the most difficult to detect and fix, as there is no way that the VBA compiler can identify and 'point to' the error, in the way that it does for compile and runtime errors.
These types of errors generally occurs by missing the logic in the code designed.
We can identify these errors by observing each step(By pressing ‘F8’) of a Macro running.

Thanks,Tamatam