How to pass UDF with ParamArray to a Range?

Juggler_IN

Board Regular
Joined
Nov 19, 2014
Messages
218
Platform
Windows
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)
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,397
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
35,397
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,625
Messages
5,512,472
Members
408,899
Latest member
cve60069

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top