Best way to validity check UDF parameters?

[JenniferMurphy]

What's the best way to handle invalid data passed to UDFs?

I have a number of UDFs that do unique and sometimes complex calculations on the data passed to them, usually data contained in other cells. I usually define these parameters as numeric (Double, Long). But if I do and there is text in any of the referenced cells, Excel returns a Value error and the UDF never gets called. Or, if the cell is empty, Excel puts zero in that parameter, which is not what I want.

Here's an example. This UDF calculates the Z Score.

Function ZScore(pValue As Double, pMean As Double, pStdDev As Double _
              , Optional pHiLo As Boolean = True) As Double

' If std dev is zero, return 0; else calculate Z Score
If pStdDev = 0 Then                   'If std dev = 0,
  ZScore = 0                            'Return 0
Else                                  'Else
  ZScore = (pValue - pMean) / pStdDev   'Calculate the ZScore
  If Not pHiLo Then ZScore = -ZScore    'If range order is reversed, reverse ZScore
End If

End Function

Here's an example of it in use.

Kitchen Scrap Bins.xlsx
	B	C	D	E	F
2	Mean	$27.14		4.6
3	Std Dev	10.669		0.19024
4
5	Price	Price Z	Rtg	Rtg Z	Z Sum
6	$25.00	+0.20	n/a	#VALUE!	#VALUE!
7	$23.99	+0.30	4.8	+1.28	+1.57
8	$13.95	+1.24	4.6	+0.23	+1.46
9	$21.95	+0.49	4.7	+0.75	+1.24
10	$35.12	-0.75	4.6	+0.23	-0.52
11	$15.00	+1.14	4.2	-1.88	-0.74
12	$38.29	-1.04	4.5	-0.30	-1.35
13	$45.99	-1.77	4.5	-0.30	-2.07
14	$25.00	+0.20		-23.95	-23.75
MrExcel

Cell Formulas
Range	Formula
C2	C2	=AVERAGE(MrExcel[Price])
C3	C3	=STDEV.S(MrExcel[Price])
E2	E2	=AVERAGE(MrExcel[Rtg])
E3	E3	=STDEV.S(MrExcel[Rtg])
E6:E14	E6	=ZScore([@Rtg],@Mean,@StdDev)
F6:F14	F6	=[@[Price Z]]+[@[Rtg Z]]
C6:C14	C6	=ZScore([@Price],@Mean,@StdDev,FALSE)

The Rtg in D6 is not a number. The UDF never gets called.

The Rtg in D14 is empty. Excel passes a zero to the UDF.

Is the correct solution to define all parameters as Variant and then test the type in the UDF?

If so, can someone post some sample code to do that?

Thanks

 

[Leo Skywalker]

Your variables are defined as double, when your function receives nothing (blank), the default value (zero) is put in place, if it receives some othe data as text or error, the function will return an error (usually value error)

The correct solution would be to make sure the function will always receive a correct parameter, with data validation for example, preventing the input of text.
Defining some problematic parameters as Variant will also work. You didn't say what you expect the function to do when a text or empty cell is received.

As an example, I changed your code a little, parameter value type changed to Range for pValue:

Function ZScore(pValue As Range, pMean As Double, pStdDev As Double _
              , Optional pHiLo As Boolean = True) As Double
If Not WorksheetFunction.IsNumber(pValue) Then ZScore = 0: Exit Function 'If not numeric, just zero and out
' If std dev is zero, return 0; else calculate Z Score
If pStdDev = 0 Then                   'If std dev = 0,
  ZScore = 0                            'Return 0
Else                                  'Else
  ZScore = (pValue - pMean) / pStdDev   'Calculate the ZScore
  If Not pHiLo Then ZScore = -ZScore    'If range order is reversed, reverse ZScore
End If
    
End Function

 

[JenniferMurphy]

The correct solution would be to make sure the function will always receive a correct parameter, with data validation for example, preventing the input of text.

Uh... That was the point of my original post. The question is how to do that? If I define the parameter as numeric, the function never gets control, so it can't do any data validation.

Defining some problematic parameters as Variant will also work.

Is that a less desirable solution than as a Range? What are the tradeoffs? (see below)

You didn't say what you expect the function to do when a text or empty cell is received.

It depends on the function. Some might return a value error. Others might assign a default value. Still others might exclude that parameter from the calculation. I am looking for a general solution that will allow the function to decide what to do.

As an example, I changed your code a little, parameter value type changed to Range for pValue:

Function ZScore(pValue As Range, pMean As Double, pStdDev As Double _
              , Optional pHiLo As Boolean = True) As Double
If Not WorksheetFunction.IsNumber(pValue) Then ZScore = 0: Exit Function 'If not numeric, just zero and out
' If std dev is zero, return 0; else calculate Z Score
If pStdDev = 0 Then                   'If std dev = 0,
  ZScore = 0                            'Return 0
Else                                  'Else
  ZScore = (pValue - pMean) / pStdDev   'Calculate the ZScore
  If Not pHiLo Then ZScore = -ZScore    'If range order is reversed, reverse ZScore
End If
 
End Function

That's a solution I would not have thought of. What are the advantages of Range over Variant:

Function ZScore(pValue As Variant, pMean As Variant, pStdDev As Variant _
              , Optional pHiLo As Boolean = True) As Double

If Not WorksheetFunction.IsNumber(pValue) Then
  ZScore = CVErr(xlErrValue): Exit Function: End If

' If std dev is zero, return 0; else calculate Z Score
If pStdDev = 0 Then                   'If std dev = 0,
  ZScore = 0                            'Return 0
Else                                  'Els
  ZScore = (pValue - pMean) / pStdDev   'Calculate the ZScore
  If Not pHiLo Then ZScore = -ZScore    'If range order is reversed, reverse ZScore
End If

End Function

 

[Leo Skywalker]

Variant is not restrictive, it could receive a value directly written in the formula, while range would always expect you to have a cell reference.

So:
=ZScore(4.6,$E$2,$E$3)
=ZScore("any text",$E$2,$E$3)

Would return a value error if expecting a range; but would return 0.23, which is the ZScore calculated with 4.6 in the first case; and 0 in the second case.

Also, your line:

 ZScore = CVErr(xlErrValue): Exit Function: End If

Will always return only a Value error, as your Function return is defined as Double, it can't return any specific error, only value error.
If you want your function to return other type of error, your function return would have to be defined as Variant

 

[JenniferMurphy]

Variant is not restrictive, it could receive a value directly written in the formula, while range would always expect you to have a cell reference.

So:
=ZScore(4.6,$E$2,$E$3)
=ZScore("any text",$E$2,$E$3)

Would return a value error if expecting a range; but would return 0.23, which is the ZScore calculated with 4.6 in the first case; and 0 in the second case.

That's an interesting distinction thatI was not aware of. Thanks for pointing that out. I'll have to play with those two options a bit...

Also, your line:

 ZScore = CVErr(xlErrValue): Exit Function: End If

Will always return only a Value error, as your Function return is defined as Double, it can't return any specific error, only value error.
If you want your function to return other type of error, your function return would have to be defined as Variant

Yes, my mistake.

 

[Leo Skywalker]

Glad I could help!