By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,034 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,034 IT Pros & Developers. It's quick & easy.

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

P: 3
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.


Expand|Select|Wrap|Line Numbers
  1.  
  2. Module 3:
  3.  
  4. Option Explicit
  5.  
  6. Public Sub CalcStats()
  7.  
  8.     'Declarations
  9.     Dim vCorrel As Double
  10.     Dim vArrVal() As Double
  11.     Dim vCount1 As Long
  12.     Dim vCount2 As Long
  13.     Dim vCount3 As Long
  14.  
  15.     'Allocate memory
  16.     ReDim vArrVal(25)
  17.  
  18.     'Read the data
  19.     For vCount1 = 1 To 25
  20.         Let vArrVal(vCount1) = ActiveSheet.Range("B" & vCount1 + 1).Value
  21.     Next vCount1
  22.  
  23.     For vCount2 = 1 To 25
  24.         Let vArrVal(vCount2) = ActiveSheet.Range("C" & vCount2 + 1).Value
  25.     Next vCount2
  26.  
  27.     For vCount3 = 1 To 25
  28.         Let vArrVal(vCount3) = ActiveSheet.Range("D" & vCount3 + 1).Value
  29.     Next vCount3
  30.  
  31.     'Call function to calculate Correlation
  32.     Let vCorrel = CalcCorrel(vArrVal(), 25)
  33.  
  34.     'Message Box
  35.     MsgBox (vCorrel)
  36.  
  37.  
  38. End Sub
  39.  
  40.  
  41. Public Function CalcCorrel(pArrVal() As Double, _
  42.                            pNumOfObs As Long) As Double
  43.  
  44.     'Declarations
  45.     Dim vSum As Double
  46.     Dim vSumX As Double
  47.     Dim vSumY As Double
  48.     Dim vSum3 As Double
  49.     Dim vMeanX As Double
  50.     Dim vMeanY As Double
  51.     Dim vStdDevX As Double
  52.     Dim vStdDevY As Double
  53.     Dim vSumSq As Double
  54.     Dim vCorrel As Double
  55.     Dim vCount1 As Long
  56.     Dim vCount2 As Long
  57.     Dim vCount3 As Long
  58.  
  59.     'Initialize sum to zero
  60.     vSum = 0
  61.  
  62.     'Return
  63.     Let vMeanX = CalcMeanX(pArrVal(), pNumOfObs)
  64.     Let vMeanY = CalcMeanY(pArrVal(), pNumOfObs)
  65.  
  66.     'Return
  67.     Let vStdDevX = CalcStdDevX(pArrVal(), pNumOfObs)
  68.     Let vStdDevY = CalcStdDevY(pArrVal(), pNumOfObs)
  69.  
  70.     'Calculating sums
  71.     For vCount1 = 1 To pNumOfObs
  72.         Let vSumX = vSumX + pArrVal(vCount1)
  73.     Next vCount1
  74.  
  75.     For vCount2 = 1 To pNumOfObs
  76.         Let vSumY = vSumY + pArrVal(vCount2)
  77.     Next vCount2
  78.  
  79.     For vCount3 = 1 To pNumOfObs
  80.         Let vSum3 = vSum3 + pArrVal(vCount3)
  81.     Next vCount3
  82.  
  83.     'Calculating Correlation
  84.     Let CalcCorrel = ((vSum3) - ((vSumX * vSumY) / pNumOfObs)) / (Sqr((vSumX ^ 2 - ((vSumX) ^ 2 / pNumOfObs)) * (vSumY ^ 2 - ((vSumY) ^ 2 / pNumOfObs))))
  85.  
  86. End Function
  87.  
  88.  
  89.  

- Achal
Jul 12 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.