Results 1 to 4 of 4

Thread: How to pass UDF with ParamArray to a Range?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to pass UDF with ParamArray to a Range?

    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 Code:
    https://www.mrexcel.com/forum/excel-questions/132404-max-min-vba.html
    How do i use this in a formula? say, =Maxi(A1:A9)

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How to pass UDF with ParamArray to a Range?

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

  3. #3
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to pass UDF with ParamArray to a Range?

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

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How to pass UDF with ParamArray to a Range?

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •