Would anyone be able to help me with the correlation code for VBA?

[bigmanutdfan7]

I have to find the correlation by coding in the actual formula in the visual basic editor and not by calling it using the excel correlation function. I am getting the proper mean and standard deviation, however the correlation is wrong. Is there somethin wrong with my formula or the way I am coding things?

The following code is Module 3 of the code for correlation: Module 1 finds the mean, variance, and std deviation of X-values, and Module 2 does the same for Y-values. Module 3 was where I called on the functions of the other 2 models and got the wrong correlation. All help would be greatly appreciated.

 
 Module 3:
 
Option Explicit
 
Public Sub CalcStats()
 
    'Declarations
    Dim vCorrel As Double
    Dim vArrVal() As Double
    Dim vCount1 As Long
    Dim vCount2 As Long
    Dim vCount3 As Long
 
    'Allocate memory
    ReDim vArrVal(25)
 
    'Read the data
    For vCount1 = 1 To 25
        Let vArrVal(vCount1) = ActiveSheet.Range("B" & vCount1 + 1).Value
    Next vCount1
 
    For vCount2 = 1 To 25
        Let vArrVal(vCount2) = ActiveSheet.Range("C" & vCount2 + 1).Value
    Next vCount2
 
    For vCount3 = 1 To 25
        Let vArrVal(vCount3) = ActiveSheet.Range("D" & vCount3 + 1).Value
    Next vCount3
 
    'Call function to calculate Correlation
    Let vCorrel = CalcCorrel(vArrVal(), 25)
 
    'Message Box
    MsgBox (vCorrel)
 
 
End Sub
 
 
Public Function CalcCorrel(pArrVal() As Double, _
                           pNumOfObs As Long) As Double
 
    'Declarations
    Dim vSum As Double
    Dim vSumX As Double
    Dim vSumY As Double
    Dim vSum3 As Double
    Dim vMeanX As Double
    Dim vMeanY As Double
    Dim vStdDevX As Double
    Dim vStdDevY As Double
    Dim vSumSq As Double
    Dim vCorrel As Double
    Dim vCount1 As Long
    Dim vCount2 As Long
    Dim vCount3 As Long
 
    'Initialize sum to zero
    vSum = 0
 
    'Return
    Let vMeanX = CalcMeanX(pArrVal(), pNumOfObs)
    Let vMeanY = CalcMeanY(pArrVal(), pNumOfObs)
 
    'Return
    Let vStdDevX = CalcStdDevX(pArrVal(), pNumOfObs)
    Let vStdDevY = CalcStdDevY(pArrVal(), pNumOfObs)
 
    'Calculating sums
    For vCount1 = 1 To pNumOfObs
        Let vSumX = vSumX + pArrVal(vCount1)
    Next vCount1
 
    For vCount2 = 1 To pNumOfObs
        Let vSumY = vSumY + pArrVal(vCount2)
    Next vCount2
 
    For vCount3 = 1 To pNumOfObs
        Let vSum3 = vSum3 + pArrVal(vCount3)
    Next vCount3
 
    'Calculating Correlation
    Let CalcCorrel = ((vSum3) - ((vSumX * vSumY) / pNumOfObs)) / (Sqr((vSumX ^ 2 - ((vSumX) ^ 2 / pNumOfObs)) * (vSumY ^ 2 - ((vSumY) ^ 2 / pNumOfObs))))
 
End Function
 
 
 

- Achal