How do I use CORREL in VBA?

[User Name]

I've got a column of data in excel. Say A1:A10. If I want to find the lag1 autocorrelation of this data my understanding is that I can use:

CORREL(A1:A9,A2:A10)

I'd like to use this function (or an alternative) in VBA to find the autocorrelation within an array of data. What sort of notation should I use?

 

[repairman615]

Hello,

I have not used the CORREL() before.

Try this:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CorrelWSFunct()<br><br>MsgBox Application.WorksheetFunction.Correl(Range("A1:A9"), Range("A2:A10"))<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

THe msgbox is used as an example.

-Jeff

 

[shg]

Is there a broader objective than this single formula?