Module function not working.

[Randy U]

I have a module that I have made a custom function in. It calculates the Standard Deviation for row level values.
I use 6 variables in the Sub Procedure on the form.
If I use 1, 2, 3 or 4 variables it returns a value that looks like a "Scientific" data type "ie: 5E-07"
If I use 5 or 6 variables it works perfectly except for one thing----if all the variables are the same which should give me an answer of 0.00000 I get an error in a line of the code in the macro. This part is giving me problems:

If n > 0 Then 'only applies if points available
RStDevP = Sqr((n * dblSumOfSq - dblSum * dblSum) / n / n)
Else
RStDevP = Null
End If

Can anyone look at it and help me with what is wrong?
Thanks in advance!

Function RStDevP(ParamArray FieldValues()) As Variant
Dim dblSum As Double, dblSumOfSq As Double
Dim n As Long
Dim varArg As Variant
For Each varArg In FieldValues
If IsNumeric(varArg) Then
dblSum = dblSum + varArg
dblSumOfSq = dblSumOfSq + varArg * varArg
n = n + 1
End If
Next
If n > 0 Then 'only applies if points available
RStDevP = Sqr((n * dblSumOfSq - dblSum * dblSum) / n / n)
Else
RStDevP = Null
End If

End Function

 

[Surjer]

What datatype is RStDevP ???

I am pretty sure thats where your problem is...

instead of setting it to null try setting it like this instead.

RStDevP = ""

 

[Randy U]

The Data type is Double

 

[Randy U]

It's specifically this part that is giving the following error:

"Invalid Procedure Call or Argument"

RStDevP = Sqr((n * dblSumOfSq - dblSum * dblSum) / n / n)

 

[Randy U]

One more thing....
If all 6 values are the same and they are =< 2 as a whole number or decimal like 0.5 then I get the error.
If the values are all 4 as a whole number then I get the error.
If the values are all the same except for the above numbers then i get that funky scientific date type value.

Again it works fine if the numbers are different.