CODEWITHSUNDEEP
excelvba
rtemen
rtemen

Reputation: 147

Excel VBA Application.iferror() is not handling a div/0 error

I have a very simple UDF program. I have one line in it, that divides two numbers. I am needing to have the IFERROR return a value when it gets an error. In my program, the Testrange(1) contains 90, and testrange(4) contains 0. My UDF crashes and returns a Value error.

Here is my code.

Function myTestRange(TestRange As Range, myvol As Integer, myType As String) As Variant

    myTestRange = Application.IfError(TestRange(1) / TestRange(4), 4567)

End Function

I was looking at Microsoft's page on the IFERROR function and it seems to list that a div/0 is one of the errors that it handles.

Thanks for any help on this. Rich

Upvotes: 0

Views: 3759

Answers (2)

Gary's Student
Gary's Student

Reputation: 96773

The fundamental problem is that VBA will trap the error itself before the function gets called. For example:

Sub qwerty()
    x = Application.WorksheetFunction.IfError((0 / 0), "no good")
    MsgBox x
End Sub

will die even before the function gets called, so the MsgBox never gets displayed. Even:

Sub qwerty2()
    On Error Resume Next
        x = Application.WorksheetFunction.IfError((0 / 0), "no good")
    On Error GoTo 0
    MsgBox x
End Sub

runs to completion because the OnError statement disables VBA's preemptive error handling, but IfError() does not return anything.

You need something like:

Sub ytrewq()
    a = 2
    b = 7
    On Error Resume Next
         If IsError(b / a) Then
           x = "no good"
        Else
            x = b / a
        End If
    On Error GoTo 0
    MsgBox x
End Sub

which returns valid information even if both a and b are zero.

Upvotes: 2

Dy.Lee
Dy.Lee

Reputation: 7567

What about using if statements.

Function myTestRange(TestRange As Range, myvol As Integer, myType As String) As Variant
    If tesrange(4) = 0 Then
        myTestRange = 4567
    Else
        myTestRange = TestRange(1) / TestRange(4)
    End If
    'myTestRange = Application.IfError(TestRange(1) / TestRange(4), 4567)

End Function

Upvotes: 1

Related Questions