Help with CVErr Error Trapping in a function

[Joseph Lo]

Hi all,

I am having a lot of trouble to trap the #Value in a function call.

Here is a sample code that I tried and it is not working. Could anyone please be kind enough to take a look?

if item1 and item2 are decimal values, the function will return the correct value.
if item1 or item2 is a string, the function will return the #value which is what I wanted to trap....and I don't know how.

Thanks

Function CCB(item1 As Double, item2 As Double) As Variant


Dim test As Variant
test = item1 * item2


If IsError(test) Then


    Select Case test
    Case CVErr(xlErrValue) '#Value Error
        CCB = "Error in input"
    End Select
Else
    CCB = item1 * item2
End If




End Function

 

[Boswell]

This is not exactly what you asked for, but may achieve what you are looking for.

Function CCB(item1 As Variant, item2 As Variant) As Variant

    If Not IsNumeric(item1) Or Not IsNumeric(item2) Then
        CCB = "Input must be Numeric"
        Exit Function
    End If
    
    CCB = item1 * item2
    
End Function

I changed the inputs to variants because I was testing as a worksheet function and inputting ranges that had doubles/strings in them. Since we need the items to be numeric I explicitly test if they are numeric rather than trying to raise an error.

 

[Joseph Lo]

Thanks Boswell for your feedback and I can see that you have tested the numerical value of item1 and item2.

Is there anyway that you can incorporate the CVErr routine into your example?

There are other instances where I need to return errors such as #N/A, #Value, etc and I would like to know how that works.

Thanks again

 

[Cool blue]

You are on the right track @Joseph Lo, check out this link

 

[Joseph Lo]

Thanks Cool Blue. Interestingly that's the exact same site that I visited when I am writing up my codes but for the life of me I just can't get my code to run by modifying them to suit.

I can understand what Cpearson is trying to do but I don't know how to apply it to my own codes....any help please?

 

[Cool blue]

You need to use the CVErr function to create a Variant that you pass back as the result of your UDF.

Function CCB(item1 As Variant, item2 As Variant) As Variant
     If Not (IsNumeric(item1) And IsNumeric(item2)) Then
         CCB = CVErr(xlErrValue)
     Else
        CCB = item1 * item2
    End If
End Function

 

[Joseph Lo]

Thanks Cool Blue.

I use your code and the function CCB still return a #VALUE error if either item1 and item2 is a string....if item1 and item2 is a numerical value, then the function works but not otherwise.

What should I amend?

 

[jsotola]

here is your code with a stop command

try it with numerical values, and you will see that the code runs (just press F5 to continue )

try it with string value, and you will find that the code does not get called

the #VALUE! error comes from excel, not from your code

excel never calls your code because your input data type (string) does not match the data type that the function expects (double)

couple of questions remain .. what exactly are you trying to do? why are trying to use strings?

Function CCB(item1 As Double, item2 As Double) As Variant




    Dim test As Variant
    test = item1 * item2
    
[COLOR=#ff0000]Stop[/COLOR]

    If IsError(test) Then
    
    
        Select Case test
        Case CVErr(xlErrValue) '#Value Error
            CCB = "Error in input"
        End Select
    Else
        CCB = item1 * item2
    End If
    
    
    


End Function

 

[Joseph Lo]

Thanks jsotola. U have made a very good point. I should be more careful with the data type I assign to the inputs/variables. I will update my code.

That said, I would still like to pursue the CVErr and would really like to learn how to use this properly.

 

[Boswell]

If all you want is for the function to return the #VALUE error when a string is passed in then this is all you need:

Function CCB(item1 As Variant, item2 As Variant) As Variant
    CCB = item1 * item2
End Function

There is no need to do any additional testing because excel will return the #VALUE error by default if your function tries to multiply a string. But from your original post - my understanding is that you are trying to trap the #VALUE error so you can do something other than return #VALUE. The only option I know of that can help with this is to use Error statements combined with the error number. When you multiply a string with VBA it returns a 'Type Mismatch Error' so you can do something like the following:

Function CCB(item1 As Variant, item2 As Variant) As Variant
    
    On Error GoTo trap
    
    CCB = item1 * item2

    Exit Function

trap:

    Select Case Err.Number
        Case 13 'type mismatch
            CCB = "do stuff"
        Case Else
            CCB = "do other stuff"
    End Select
            
End Function

But this is could be accomplished easier by explicitly testing that the correct types were passed into the function using isnumeric. A thorough discussion of error handling can be found here: Error Trapping with Visual Basic for Applications