-
Solved: IsError Function
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]
-
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
-
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
-
