Solved: IsError Function

[vassili]

i've been stuck on this for a good hour. i'm trying to figure out why the "num" function won't return "invalid" when i use the "IsError" function to try to catch a math error.

like if i try to evluate "1/0" or "string1/string2"

[vba]Sub getPrice()

'...some code
Range("A1").Formula = "1"
Range("B1").Formula = "1b"
Range("C1").Formula = "0"
Range("A3").Formula = "=Num(A1,B1,C1)"

End Sub
Function Num(qty As Range, uPrice As Range, pcs As Range)

Dim err As Boolean

err = IsError(Val(qty) * Val(uPrice) / Val(pcs))

If err Then
Num = "Invalid"
Else
Num = (Val(qty)) * Val(uPrice) / Val(pcs))

End If


End Function[/vba]

[daniel_d_n_r]

first off i can see you declared err as boolean, 0,1 -- true ,false
if err will always return false as you have used it

below is an extracrt from the Microsoft VBA Excel help file,its very helpful

----------------------------------------------------
Dim ErrorNumber

For ErrorNumber = 61 To 64 ' Loop through values 61 - 64.

Debug.Print Error(ErrorNumber) ' Print error to Immediate window.

Next ErrorNumber

[rory]

Hi,
Firstly, you shouldn't use err because that is the name of the built-in error object.
Secondly, your division by zero error occurs before the IsError function runs so you get a run-time error and your UDF returns the #Value error. You could use this:
[VBA]Function Num(qty As Range, uPrice As Range, pcs As Range)

On Error Resume Next
Num = Val(qty) * Val(uPrice) / Val(pcs)

If err.Number <> 0 Then Num = "Invalid"

End Function
[/VBA]

Regards,
Rory

[vassili]

cool guys, thanks.