How to pass UDF with ParamArray to a Range?

[Juggler_IN]

I have two functions from one of the threads in MrExcel forum.

Function Mini(ParamArray values() As Variant) As Variant
   Dim minValue, Value As Variant
   minValue = values(0)
   For Each Value In values
       If Value < minValue Then minValue = Value
   Next
   Mini = minValue
End Function

Function Maxi(ParamArray values() As Variant) As Variant
   Dim maxValue, Value As Variant
   maxValue = values(0)
   For Each Value In values
       If Value > maxValue Then maxValue = Value
   Next
   Maxi = maxValue 
End Function

https://www.mrexcel.com/forum/excel-questions/132404-max-min-vba.html

How do i use this in a formula? say, =Maxi(A1:A9)

 

[RoryA]

You don't need a paramarray argument if that's all you're going to pass to it. Use a simple Range or Variant.

 

[Juggler_IN]

If i enter =Mini(A1:A9) I get the VALUE error.

 

[RoryA]

Yes. As I said, you don't need a ParamArray argument if you're just going to pass one range. If you insist on using the ParamArray, you'll need two loops - one through that and one through all the values/cells within each item returned from the ParamArray.