Tuesday, 2 October 2012

What are Types of Errors Occurred In Excel VBA

Compile Errors,Runtime Errors,Logical Errors Occurred In VBA 
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 recognised 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.


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.
For 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


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 runtime 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.

Invalid Procedure Call
Out of Memory
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)
Division by Zero
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)
File Not Found
(occurs when attempting to open a file)


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 obeserving each step(By pressing ‘F8’) of a Macro running.


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts