How to implement the COVAR formula in VBA?

Question

I've been calculating the variance of two stocks and it requires the covariance to finish the calculation. But I want to use VBA to implement the formula.

Range G4:G25 has the data of the returns of Microsoft and Range H4:H25 has the data of the returns Apple Inc. and Range I4:I25 has the data of the returns Facebook and Range J4:J25 has the data of the returns Exxon.

I have named Range G4:G25 as Range1 and Range H4:H25 as Range2 and I4:I25 as Range3 and J4:J25 as Range4

This is my code below

Dim data1 As Integer
Dim data2 As Integer

If ComboBox1 = "microsoft" Then data1 = Range("Range1").Select
If ComboBox1 = "facebook" Then data1 = Range("Range3").Select
If ComboBox2 = "apple" Then data2 = Range("Range2").Select
If ComboBox2 = "exxon" Then data2 = Range("Range4").Select
Range("G32").Formula = "=COVAR(Range(data1),Range(data2))"

I am not getting any result with this Covariance formula. I am getting the "#NAME?" error in cell G32. Any help here would be greatly appreciated. Thanks in advance

Answer 1

Try in this way, please:

Sub testCovarFunction()
  Dim data1 As Range, data2 As Range

    If ComboBox1 = "microsoft" Then
        Set data1 = Range("Range1")
    ElseIf ComboBox1 = "facebook" Then
        Set data1 = Range("Range3")
    End If

    If ComboBox2 = "apple" Then
        Set data2 = Range("Range2")
    ElseIf ComboBox2 = "exxon" Then
        Set data2 = Range("Range4")
    End If
    Range("G32").Formula = "=COVAR(" & data1.Address & "," & data2.Address & ")"
End Sub

If you need everything in VBA, try this code, please:

Sub testCovarFunctionBis()
  Dim data1 As Variant, data2 As Variant

    If ComboBox1 = "microsoft" Then
        data1 = Range("Range1").value
    ElseIf ComboBox1 = "facebook" Then
        data1 = Range("Range3").value
    End If

    If ComboBox2 = "apple" Then
        data2 = Range("Range2").value
    ElseIf ComboBox2 = "exxon" Then
        data2 = Range("Range4").value
    End If
    Range("G32").value = WorksheetFunction.Covar(data1, data2)
End Sub