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

 

[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?

Isn't that the exact behavior you want?

What you describe above is exactly the behavior you would get from the Sum function for example. So what you have is a UDF that conforms exactly to standard worksheet function behavior, no?

And @Boswell has already fixed the problem raised by @jstola. That was to change to Variant Type for item1 and item2...

 

[Joseph Lo]

Thanks Boswell. That code does exactly what I need to do.

Thanks also to everyone who chip in to help me. Appreciated.