IfError

[tiredofit]

Normally I would use On Error but I want to find out why this doesn't work:

In column A, put some values in cell A1 to A6, ensuring at least one value is zero.

Dim MyArray() As Variant

MyArray() = Cells(1, 1).CurrentRegion.Value

Dim i, j

For i = 1 to 6

    j = Application.WorksheetFunction.IfError(1 / MyArray(i,1), 0)

Next i

When a value of 0 is encountered, I expected the IfError to return 0 but instead it shows an error message.

What is wrong?

Thanks

 

[shg]

Because the division is performed in VBA, not by the IfError function.

This works:

    j = Evaluate("IfError(1 / " & MyArray(i, 1) & ", 0)")

 

[tiredofit]

Thanks.

BTW, do you know the difference between:

IfError
If(Iserror)
IsError

 

[Joe4]

BTW, do you know the difference between:

IfError
If(Iserror)
IsError

This is where simple Google searches of these Excel functions can come in handy:

IfError - used to indicate what to return in the case that the formula returns an error (see: [/COLOR]https://www.techonthenet.com/excel/formulas/iferror.php[COLOR=#333333]
If(Iserror) - does the same thing as IfError, but used in older versions of Excel that do not have IfError (prior to Excel 2007)
IsError - simply returns a boolean value telling you if the formula returns an error or not (see: [/COLOR]https://www.techonthenet.com/excel/formulas/iserror.php[COLOR=#333333])

 

[tiredofit]

Thanks


Would you say proper error handling with On Error is preferable over all of these?

 

[Joe4]

Would you say proper error handling with On Error is preferable over all of these?

It is probably dictated by the situation.

Personally, if I was trying to handle an error in VBA, I would probably use VBA error handling (On Error).
If I was placing a formula on the worksheet, then I would use one of the other Excel formula options.

 

[Fluff]

Another option would be to check if the array is zero first like

For i = 1 To 6
   If MyArray(i, 1) = 0 Then
      j = 0
   Else
      j = 1 / MyArray(i, 1)
   End If
   Debug.Print j
Next i