IfError VBA
Users can handle errors in VBA using the Excel IfError function.
This Function must be accessed through Worksheet Function.
Worksheet Function is the method of function class that helps you to access a lot of Standard Excel worksheet functions. All the Standard formulas in Excel start with (=) equal sign. Similarly, WorksheetFunction is a word that should be used to access the worksheet formula in VBA coding.
The above example will give an output value of Range “A1” If the value is an error it will give “0” as output.
VBA Error Handling in a Loop
The best way to error handle within a Loop is by using On Error Resume Next along with Err.Number to detect if an error has occurred,
Note: Remember to use Err.Clear to clear the error after each occurrence.
Below is the code that is used to divide two numbers( Column A by Column B) and output the result into Column C.
The result will be 0 if there’s an error.
Sub test()
Dim cell As Range
On Error Resume Next
For each cell in Range( “A1: A10”)
‘Set cell value
Cell.offset(0,2).Value = cell.value/cell.offset(0,1).Value
‘If Cell.Value is Error then Default to 0
If Err.Number <>0 Then
cell.offset (0,2).Value =0
Err.Clear
End if
Next
End sub
VBA Error Trapping
VBA Error Trapping is just another term for VBA Error Handling
VBA Error Handling
In a VBA code, there may be some errors like syntax errors, compilation errors, or runtime errors so we need to handle these errors. Suppose there is a code of 200 lines and the code has an error it’s very difficult to find an error in the code of 200 lines so it’s better to handle the error where we are expecting some error in our code. There are many error handling methods in VBA which we will discuss in this article but before that, we will discuss types of error.
Contact Us