Forum Discussion

Excel's avatar
Excel
Iron Contributor
Sep 24, 2022

Question related to VBA code

Hello Everyone, 

 

I tried to write a Macro which calculate the Covariance of two ranges of cells. 

 

When i run the macro, it does not happen...

 

How can I solve it?

 

Please help

 

Here is a attached file

Macros and VBA
    • Excel's avatar
      Excel
      Iron Contributor
      Sep 24, 2022
      Sir, can we do with the help of VBA code ?
      • Nothing_Left_to_Lose's avatar
        Nothing_Left_to_Lose
        Brass Contributor
        Sep 25, 2022

        Excel 

         

        Re: "Sir, can we do with the help of VBA code ?"

        Following code is for a function entered into a worksheet cell.

        Watch out for the forum wrapping lines.
        See image below.
        '---
        Public Function COMPARESERIES(RngOne As Excel.Range, RngTwo As Excel.Range) As Variant
        'Nothing Left to Lose - September2022
        On Error GoTo NoVariance
        If RngOne.Cells.Count <> RngTwo.Cells.Count Then
        VBA.MsgBox "Both sets of data must be the same size. ", vbInformation, "COMPARE SERIES"
        COMPARESERIES = VBA.CVErr(xlErrNA)
        Exit Function
        ElseIf RngOne.Cells.Count <= 5 Then
        COMPARESERIES = WorksheetFunction.Covariance_S(RngOne, RngTwo) 'sample
        Else
        COMPARESERIES = WorksheetFunction.Covariance_P(RngOne, RngTwo) 'population
        End If
        Exit Function

         

        NoVariance:
        VBA.MsgBox Err.Number & " - " & Err.Description
        COMPARESERIES = VBA.CVErr(xlErrNA)
        End Function

        '---

         

         

         

        '---

        Nothing Left to Lose

        https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

        (free excel programs)

Resources