Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Question related to VBA code

Iron Contributor

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

3 Replies






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



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"
Exit Function
ElseIf RngOne.Cells.Count <= 5 Then
COMPARESERIES = WorksheetFunction.Covariance_S(RngOne, RngTwo) 'sample
COMPARESERIES = WorksheetFunction.Covariance_P(RngOne, RngTwo) 'population
End If
Exit Function


VBA.MsgBox Err.Number & " - " & Err.Description
End Function







Nothing Left to Lose!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)