How to pass UDF with ParamArray to a Range?

Juggler_IN

Board Regular
Joined
Nov 19, 2014
Messages
154
I have two functions from one of the threads in MrExcel forum.

Code:
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
HTML:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,148
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You don't need a paramarray argument if that's all you're going to pass to it. Use a simple Range or Variant.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,148
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

Forum statistics

Threads
1,077,827
Messages
5,336,612
Members
399,092
Latest member
jbwatkins

Some videos you may like

This Week's Hot Topics

Top