UDF Error - Structure or Datatype?

[Juggler_IN]

I am trying to generate Pearson's Correlation Matrix; and subsequently Spearman's.

When I use the Excel's in-built Correl function, I get the output. But, when I use my custom function for correl, I get a VALUE# error. That is, if I replace "Application.WorksheetFunction.Correl" with "PEARSONRHO" I do not get an output.

Functions are:

Public Function PEARSONMAT(ByVal rng As Range) As Variant

    Dim outRslt() As Double, i As Long, j As Long, iCols As Long


    iCols = rng.Columns.Count
    ReDim outRslt(iCols - 1, iCols - 1)


    For i = 1 To iCols
        For j = 1 To iCols
            outRslt(i - 1, j - 1) = Application.WorksheetFunction.Correl(rng.Columns(i), rng.Columns(j))
        Next j
    Next i


    PEARSONMAT = outRslt


End Function

Public Function PEARSONRHO(ByVal x As Variant, ByVal y As Variant) As Variant


    Dim sx As Double, sy As Double
    Dim s1 As Double, s2 As Double, s3 As Double
    Dim k As Long


    sx = 0
    sy = 0
    s1 = 0
    s2 = 0
    s3 = 0


    For k = 1 To x.Rows.Count
        sx = x(k) + sx
        sy = y(k) + sy
    Next


    sx = sx / x.Rows.Count
    sy = sy / y.Rows.Count


    For k = 1 To x.Rows.Count
        s1 = s1 + (x(k) - sx) * (y(k) - sy)
        s2 = s2 + (x(k) - sx) ^ 2
        s3 = s3 + (y(k) - sy) ^ 2
    Next


    PEARSONRHO = s1 / Sqr(s2 * s3)


End Function

 

[Norie]

Application.WorksheetFunction is only for built-in Excel worksheet functions.

 

[Juggler_IN]

Yes, I am aware of the same. When I replace the whole of "Application.WorksheetFunction.Correl" with PEARSONRHO I get an error. PEARSONRHO on a standalone basis works with x,y data.

 

[Norie]

So where/when do you get the error?

 

[Juggler_IN]

Let me explain, I have the following data:

a	b	c	d
44.00	89.47	44.61	11.37
44.00	85.84	54.30	8.65
38.00	89.02	49.87	11.95
40.00	75.98	45.68	11.95
44.00	81.42	39.44	13.08
44.00	73.03	50.54	10.13
45.00	66.45	44.75	11.12
54.00	83.12	51.86	10.33
51.00	69.63	40.84	10.95
48.00	91.63	46.77	10.25
57.00	73.37	39.41	12.63
52.00	76.32	45.44	9.63
51.00	67.25	45.12	11.08
51.00	73.71	45.79	10.47

<colgroup><col span="4"></colgroup><tbody>
</tbody>

With "Application.WorksheetFunction.Correl" in PEARSONMAT I get the following output from PEARSONMAT as an Array formula:

a	b	c	d
1.000000	-0.357957	-0.305628	-0.129392
-0.357957	1.000000	0.383502	-0.095889
-0.305628	0.383502	1.000000	-0.706011
-0.129392	-0.095889	-0.706011	1.000000

<colgroup><col span="4"></colgroup><tbody>
</tbody>

But when I replace the .Correl with PEARSONRHO, I get error:

a	b	c	d
#VALUE!	#VALUE!	#VALUE!	#VALUE!
#VALUE!	#VALUE!	#VALUE!	#VALUE!
#VALUE!	#VALUE!	#VALUE!	#VALUE!
#VALUE!	#VALUE!	#VALUE!	#VALUE!

<colgroup><col span="4"></colgroup><tbody>
</tbody>

Why is this happening?

 

[Juggler_IN]

The modified function, which is giving error, is:

Public Function PEARSONMAT(ByVal rng As Range) As Variant

    Dim outRslt() As Double, i As Long, j As Long, iCols As Long


    iCols = rng.Columns.Count
    ReDim outRslt(iCols - 1, iCols - 1)


    For i = 1 To iCols
        For j = 1 To iCols
            outRslt(i - 1, j - 1) = PEARSONRHO(rng.Columns(i), rng.Columns(j))
        Next j
    Next i


    PEARSONMAT = outRslt

End Function

 

[Norie]

The PEARSONRHO function fails here.

    For k = 1 To x.Rows.Count
        sx = x(k) + sx
        sy = y(k) + sy
    Next

Not 100% sure why but code stops execution on this line.

sx = x(k) + sx

If I step through the code and stop at the line immediately before the above line and then enter this,

? x(k)

in the Immediate Window I get a type mismatch error.

What should x(k) return?

 

[Domenic]

When rng.Columns(i) is passed to PEARSONRHO, it receives a Range object. However, since it receives a column, you'll need to refer to the Cells property of the Range object. Also, since it refers to a worksheet range, the reference is a two dimensional one. So, for example, you should do it like this...

sx = x.cells(k,1).value + sx

 

[Norie]

Try this.

Public Function PEARSONRHO(ByVal x As Variant, ByVal y As Variant) As Variant
Dim sx As Double, sy As Double
Dim s1 As Double, s2 As Double, s3 As Double
Dim k As Long

    For k = 1 To x.Rows.Count
        sx = x.Cells(k, 1).Value + sx
        sy = y.Cells(k, 1) + sy
    Next

    sx = sx / x.Rows.Count
    sy = sy / y.Rows.Count

    For k = 1 To x.Rows.Count
        s1 = s1 + (x.Cells(k, 1).Value - sx) * (y.Cells(k, 1).Value - sy)
        s2 = s2 + (x.Cells(k, 1).Value - sx) ^ 2
        s3 = s3 + (y.Cells(k, 1).Value - sy) ^ 2
    Next

    PEARSONRHO = s1 / Sqr(s2 * s3)

End Function

PS You could probable avoid having to use Cells if you passed arrays to PEARSONRHO rather than ranges.

 

[Juggler_IN]

This works