VBA for Variance-Covariance Matrix

Function VarCov(rng As Range) As Variant
   
    Dim i As Integer
    Dim j As Integer
    Dim colnum As Integer
    Dim matrix() As Double
   
    colnum = rng.Columns.Count
    ReDim matrix(colnum - 1, colnum - 1)
       
    For i = 1 To colnum
        For j = 1 To colnum
            matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j))
        Next j
    Next i

    VarCov = matrix

End Function

 

* Download the excel file *