Excel VBA function returning #VALUE

[wpennajr]

HI all,

I'm new to this forum so forgive me if my question has been answered before, but I searched the FAQ and I didn't find an answer.

I'm creating an Excel function to perform a statistical test. Basically, the input is a range of cells containing my data set. Then I create an array with the data in the range, sort the array in ascending order and then perform the statistical calculations. I saved the code as an add-in. However I'm getting #VALUE when I try to use the function.

I checked all the calculation in a macro, comparing to calculations made in the spreadsheet and they are correct, so I assume it is some issue about how to create a custom function. I hope anyone can shed some light.

Thank you in advance.

Function Normtest(sel As Range)
    Dim x() As Double, z() As Double, Sx() As Double, Fx() As Double, T1() As Double
    Dim n As Integer, i As Integer, j As Integer
    Dim Firstz As Double, Lastz As Double, strTemp As Double
    Dim AvgNorm As Double, StDevNorm As Double, h As Double
    Dim t As Double, dn As Double, Tcrit As Double
   
    'Read data and count the number of data points
    Set sel = Application.Selection
    n = sel.Rows.Count
   
    'Calculate the x-values array average and standard deviation
    AvgNorm = Application.WorksheetFunction.Average(sel)
    StDevNorm = Application.WorksheetFunction.StDev(sel)
   
    'Create the x-values array and the normalized x-values array
    ReDim x(1 To n)
    ReDim z(1 To n)
    For i = 1 To n
        x(i) = sel.Cells(i).Value
        z(i) = (x(i) - AvgNorm) / StDevNorm
    Next i
       
    'Sort the normalized x-values array in ascending order
    Firstz = LBound(z)
    Lastz = UBound(z)
    For i = Firstz To Lastz - 1
        For j = i + 1 To Lastz
            If z(i) > z(j) Then
                strTemp = z(i)
                z(i) = z(j)
                z(j) = strTemp
            End If
        Next j
    Next i
   
    'Calculate the Sx-values array, Fx-values array and the Liliefors statistical test value
    ReDim Sx(1 To n)
    ReDim Fx(1 To n)
    ReDim T1(1 To n)
    t = 0
    h = 1 / n
    For i = 1 To n
        If i = 1 Then Sx(i) = h Else Sx(i) = Sx(i - 1) + h
        Fx(i) = Application.WorksheetFunction.NormDist(z(i), 0, 1, True)
        'Calculate the Lilliefors test statistic value
        T1(i) = Abs(Fx(i) - Sx(i))
        If T1(i) > t Then t = T1(i)
    Next i
   
    'Calculate the Lilliefors statistical test critical value for alpha = 0.05
    dn = Sqr(n) - 0.01 + 0.83 / Sqr(n)
    Tcrit = 0.895 / dn                                  'need to check this calculation in the book
   
    'Returns the Lilliefors test statistical value
    Normtest = t
   
End Function

 

[GWteB]

Hi & welcome to MrExcel.

Try deleting this line

Set sel = Application.Selection

since the variable "sel" is used as an argument to your function:

Book1
	A	B	C	D	E	F
1	1	2	3	4	5	6
2	4	5	6	7	8	9
3	2	2	2	2	2	2
4
5
6	0.661477
Sheet1

Cell Formulas
Range	Formula
A6	A6	=Normtest(A1:F3)

 

[wpennajr]

It worked! Thanks GWteB, for the quick and precise answer.
Regards

 

[GWteB]

Glad to help and thanks for the feedback