Worksheetfunction.IFERROR

[Roncondor]

Hi

I want it to work just like it does in regular Excel - but it always gives me an error message (see the attached picture)

I am enclosing a VERY simple sample macro (see below)

When I run it, I get a "division by zero" instead of "X" becoming a 0

This seems simple, but I cannot figure it out

THANK YOU!

Sub TestIferror()

Dim X As Integer

X = WorksheetFunction.IfError(1 / 0, 0)

End Sub

 

[kweaver]

I believe you need the error situation to be in a cell, say, A1 is =some_value/0 which means A1 contains the divide by 0 error.
Then, WorksheetFunction.IfError(Range("A1"),0) should work.

 

[Eric W]

The problem is that VBA evaluates the 1/0 before it does the rest of the line, and when it gets the error it doesn't even try to finish the rest of the line. There are a couple ways to do this. You can use EVALUATE like this:

x = Evaluate("IFERROR(1/0,0)")

or you can use some more VBA specific type coding:

    On Error Resume Next
    x = 0
    x = 1 / 0

In this case, you set x to the default value. Then perform the calculation. If that creates an error it just goes to the next line leaving the default value intact.

 

[Roncondor]

I believe you need the error situation to be in a cell, say, A1 is =some_value/0 which means A1 contains the divide by 0 error.
Then, WorksheetFunction.IfError(Range("A1"),0) should work.

Thanks - but that limitation of Excel defeats the purpose - as the whole reason that VBA has worksheet.functions is to avoid using cells.

In the end, for my actual macro, I have a cell in the spreadsheet that performs my calculation within which I have the IFERROR, and VBA checks that cell

 

[Roncondor]

The problem is that VBA evaluates the 1/0 before it does the rest of the line, and when it gets the error it doesn't even try to finish the rest of the line. There are a couple ways to do this. You can use EVALUATE like this:

x = Evaluate("IFERROR(1/0,0)")

or you can use some more VBA specific type coding:

    On Error Resume Next
    x = 0
    x = 1 / 0

In this case, you set x to the default value. Then perform the calculation. If that creates an error it just goes to the next line leaving the default value intact.

Thank you for the response

Your first answer probably would work

The problem with the second is that I need to check the result to determine what I would do = not just continue with the macro

 

[Eric W]

This feeds into a much larger topic of error handling. Instead of the On Error Resume Next, you could have an On Error Goto ErrorHandler: where you do something, then use a Resume Label01: to get back to the main code. Or use the Err method like this:

    On Error Resume Next
    x = 0
    x = 1 / 0
    If Err.Number > 0 Then
        Debug.Print "Error code:  " & Err.Number
        Debug.Print "Description: " & Err.Description
    End If

It just depends on your macro.