PDA

View Full Version : Solved: Producing Error



chrisjones
08-04-2010, 02:18 AM
Several Excel built in functions produce specific errors for certain scenarios. I would like to do the same thing, I have written a short example where if you enter a negative or positive number it should display the rasied errror msg in the cell. However it just shows "#VALUE" ??


Public Function test(ByVal i As Integer) As Integer
If i < 0 Then
'test = 0
Err.Raise Number:=vbObjectError + 1, Source:="test method", Description:="i cannot be negative"
ElseIf i > 0 Then
Err.Raise Number:=vbObjectError + 2, Source:="test method", Description:="i cannot be positive"
Else
test = 1
End If
End Function

Thanks

Chris

Simon Lloyd
08-04-2010, 02:40 AM
Hi Chris, a function can only return a value., you would be better off using data validation>Whole number then set your parameters and error messages.

Bob Phillips
08-04-2010, 02:52 AM
The standard way



Public Function test(ByVal i As Integer) As Integer
If i < 0 Or i > 0 Then
test = CVErr(xlErrValue)
ElseIf i > 0 Then
test = 1
End If
End Function

chrisjones
08-04-2010, 03:08 AM
xld when I try this, if "i" the cell still remains "#VALUE" even when I try to use


test = CVErr(xlErrNum)
test = CVErr(xlErrNA)
test = CVErr(xlErrNull)
test = CVErr(xlErrDiv0)

Why is this, as many excell functions will output "#NULL" , "#!Div0" etc however I still cant do it

Bob Phillips
08-04-2010, 03:14 AM
The UDF was a poor example, this might show it better



Public Function test(ByVal i As Double) As Variant
If i < 0 Or i > 10 Then
test = CVErr(xlErrValue)
ElseIf i = Int(i) Then
test = 1
End If
End Function

chrisjones
08-04-2010, 03:20 AM
return type as variant, worked. Thanks!