Covariance, VBA, Loops

Hi there,

I am close to being as rookie as it comes wrt VBA programming although I more or less know how to read VBA codes.

I want to write a code for the covariance between to returns (X and Y). I am aware that such a function already exists but have nonetheless determined to program such a one as i want to learn VBA.

What i specifically want the function to include:
- Dynamic arrays
- All-in-one formula that only needs as input the returns for the assets.

Below i have pasted what I have written so far but I am stuck with this and do not know what to write next.

Could someone please advice on this? Thanks a million!

PapaBear

Option Explicit

Function CoV(ReturnsX(), ReturnsY())
Dim ExpRx, ExpRy As Double
Dim ReturnsX(), ReturnsY() As Double
Dim i As Integer

ReDim ReturnsX(n)
ReDim ReturnsY(n)

ExpRx = Sum(ReturnsX(n) / Count(ReturnsX(n)))
ExpRy = Sum(ReturnsX(n) / Count(ReturnsX(n)))

DevX = 0: DevY = 0: DevXY = 0
For i = 0 To n
DevX = ReturnsX(i) - ExpRx: DevY = ReturnsY(i) - ExpRy
DevXY = DevXY + (DevX * DevY)
Next i

CoV = DevXY

End Function