VBA Correlation

[mikedjp]

Hi there,
I’m new to Excel VBA. I am trying to make a program which calculates the correlation (could be rsq or anything else – just trying to understand how it all works) between two variables. I want to use a loop so that the program can deal with any number of lines. My problem is that I don’t know how to get the cells into an array. Any help is greatly appreciated

Sub correl()

Dim ans As Integer
Dim x As Variant
Dim y As Variant
Dim xA As Double
Dim yA As Double

Range("a1").Activate
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Offset(-1, 0).Activate

x = ActiveCell.Address

Range("b1").Activate
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Offset(-1, 0).Activate

y = ActiveCell.Address

xA = Array(Range("a1:x"))
yA = Array(Range("a1:y"))

'ans = Application.WorksheetFunction.Correl(xA, yA)
' MsgBox ans

End Sub

 

[pgc01]

Hi mikedjp
Welcome to the board

Assuming the data is in columns A:B and starts in row 2:

Sub correl()

Dim ans As Double
Dim rRngA As Range
Dim rRngB As Range

Set rRngA = Range("A2", Range("A2").End(xlDown))
Set rRngB = rRngA.Offset(, 1)

ans = Application.WorksheetFunction.correl(rRngA, rRngB)
MsgBox ans

End Sub

You can test it for ex. with (I used the worksheet formula to test the result):

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >A</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >D</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >E</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >F</tr></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >1</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:center" style="border-width: 1px;border-color:#888888" >X</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:center" style="border-width: 1px;border-color:#888888" >Y</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >2</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >3</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >12</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Correl</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >0.961054</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >3</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >4</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >13</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >4</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >5</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >16</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >5</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >7</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >17</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >6</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >7</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >18</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >7</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td colspan=7 style="background:#9CF; padding-left:1em" > [Book1]Sheet2</td></tr></table>



<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF " >Addr</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF " >Formula</td></tr><tr><td colspan=2 style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF " >[Book1]Sheet2</td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:center" >E2</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" > =CORREL(A2:A6,B2:B6) </td></tr></table>

Hope this helps
PGC

 

[mikedjp]

Hi PGC,
Thanks that works! Can you please explain to me what 'Set' does and where else I would use that.

Thanks again!

 

[pgc01]

Hi

I'm glad this is what you wanted.

You have to use Set when you do an assignment to an object variable (like in the example, rRngA and rRngB are variables that represent objects type Range).

You can check the vba help for a detailed explanation.

Kind regards
PGC

 

[mikedjp]

Hi,
Alright, well thanks again for your help!

Have a good day.

 

[KarenPVS]

Hello,

Could you tell me in the example above, how you would use a 90 cell long range rather than using all cells below A2 for your range?
I am pulling 5 years of data and only wish to calculate a 3-month average.

Thanks