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

@Excel 

 

 

Nothing_Left_to_Lose_0-1664031104990.png

 

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

@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_0-1664073157103.png

 

 

'---

Nothing Left to Lose

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

(free excel programs)