VBA Functions for Max/Min of a short list of Integers?

[Rufus Clupea]

I'm sure this has come up before (multiple times), and I did search via google (as recommended) but all I could find was functions that would determine Max/Min from a Range of Cells, and that's not what I need. If someone could even point me to a pertinent thread, that'd be great (but please read on ).

I wrote a couple of simple Functions for returning the Greater of/Lesser of 2 numbers:

'   RETURNS GREATER OF 2 NUMBERS
Function GreaterOf(x As Long, y As Long)

    If x > y Then GreaterOf = x Else GreaterOf = y
    
End Function 'GreaterOf(x, y)


'   RETURNS LESSER OF 2 NUMBERS
Function LesserOf(x As Long, y As Long)

    If x < y Then LesserOf = x Else LesserOf = y
    
End Function 'LesserOf(x, y)

I realize they're not particularly "elegant" , but they get the job done (I don't need to know which is greater/less; I just need the value). What I need now is a couple of similar functions that will return the Greater of/Lesser of a short list (up to 5) of Integers, but with more than 2 arguments being optional(?), and not involving/referencing a Worksheet Range (just passing them as arguments).

I spoze something like:

Function GreatestOf(a As Long, b As Long, c As Long, d As Long, e As Long)

End Function

would work (using 0s for some of the arguments), but I'm at a bit of a loss as to what goes in-between Function and End Function other than a rather large group of If-Thens or a Select Case. Again, I'm thinking this has come up before—I just can't FIND it.

A single dual-purpose function passing Max or Min as the initial argument would also be fine, e.g:

Function Max_Min_Of(MaxMin As String, a As Long, b As Long, [c As Long], [d As Long], [e As Long])

End Function

Again, all I need returned is the single Value.

TIA

 

[Rufus Clupea]

Here are two functions that adapts automatically to the number of arguments (from 2 to 60) you supply to them...

Function GreaterOf(ParamArray Nums() As Variant) As Double
  GreaterOf = Evaluate("MAX(" & Join(Nums, ",") & ")")
End Function

Function LesserOf(ParamArray Nums() As Variant) As Double
  LesserOf = Evaluate("MIN(" & Join(Nums, ",") & ")")
End Function

Thank you, Rick.

Your suggestion looks (to me) very similar to Eric's(?)

Likewise, much for me to look up/digest!

Is the purpose of these Variant declarations so that any/different kind(s) of variables can be evaluated by the functions (not much experience with/using Variant variables)?

 

[Rufus Clupea]

As far as I can remember, there is a 60 argument limit to the number of arguments a function or sub can have (not likely to be a problem for the OP though).

Correct; not a problem at present.

I recall reading something about 30 arguments (I still had the page open) here:
https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.min
but that was for that method. Subs/Functions may be different.

Ah, here it is for procedures...
https://docs.microsoft.com/en-us/of...erence/user-interface-help/too-many-arguments

 

[Rick Rothstein]

Thank you, Rick.

Your suggestion looks (to me) very similar to Eric's(?)

Likewise, much for me to look up/digest!

Is the purpose of these Variant declarations so that any/different kind(s) of variables can be evaluated by the functions (not much experience with/using Variant variables)?

ParamArrays must always be Variants (I think because you can use them to recieve any type of data). You actually do not need to explicitly declare them as Variant as they will default to that if the declaration is omitted.

You might be interested in this modification to the code I posted as it combines both functions into a single one-liner function. You simply pass either "Min" or "Max" (the letter casing does not matter) in as the first argument followed by your list of values...

Function MinMax(MinOrMax As String, ParamArray Nums() As Variant) As Double
  MinMax = Evaluate(MinOrMax & "(" & Join(Nums, ",") & ")")
End Function

 

[Eric W]

Rick, thanks for adding some clarifying comments. There is a 60-parameter limit which is worth mentioning. Also, when I said "No commas necessary", I should have said "No extra commas necessary", meaning that something like

=GreatestOf(1,2,,,,,,)

would not be required.

Rufus, Rick's version is superior to mine in at least one way: If you use a formula like:

=GreatestOf(1,A1)

and A1 is empty, mine will cause an error, and Rick's won't. Whether or not this is a issue for you I don't know. But it does show how minor seeming differences can have a significant effect.

 

[DanteAmor]

My comments

Thank you.

• My understanding of the VBE is that it is not case sensitive, so what is the purpose of changing MaxMin to lower case (I had to look that one up too)?

Here is case sensitive, I'll explain, if you send the argument "MAX" or "Max" or "max", in all cases it converts them to "max" and compares it to "max"

• Why declare c, d, & e as Variants instead of Long?

If you do not send the arguments c, d, e but declare them as Long, then the value is 0.
In the case of the Min function, if you send 2, 4 the valid function 2,4,0,0,0 then the minimum value is 0, that is why it is Variant, because it can be a number and you can also omit the argument.

• As I asked Fluff, would you mind elucidating your preference of WorksheetFunction over Application?

I just like it

• I'm not following the logic of the If-Then statements; it appears (to me) that your example would return all 5 values instead of just the highest (or I may be reading that incorrectly)?

If you do not send the arguments c, d, e but declare them as Long, then the value is 0.
In the case of the Min function, if you send 2, 4 the valid function 2,4,0,0,0 then the minimum value is 0, that is why it is Variant, because it can be a number and you can also omit the argument.


Thanks again

 

[Marcelo Branco]

Interesting... I always thought Application could be used as a shortcut to WorksheetFunction, but that appears not to be the case for the Max function. Your code worked, but when I tried it with Application instead of WorksheetFunction, it errored out with an "Invalid ParamArray use" message (which is why I posted my "Evaluate" solution in Message#8). Live and learn.

But, curiously enough, this worked for me

Function GreatestOf(ParamArray SubArr() As Variant) As Double
    Dim myarr As Variant
    myarr = SubArr
    GreatestOf = Application.Max(myarr)
End Function

M.

 

[Rick Rothstein]

I recall reading something about 30 arguments (I still had the page open) here:
https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.min
but that was for that method. Subs/Functions may be different.

The 30 argument limit is for the WorksheetFunction functions. I might be wrong about the 60 argument limit for a ParamArray... I just tried my function with 70 arguments and it worked fine. Maybe the 60 argument limit I am remembering (I'll have to look that up when I get a chance) applies to arguments to a normal function or sub, but that a ParamArray can take whatever you throw at it. If so, Eric might have been right when he said the number of arguments are unlimited (I'm sure there is a limit of some kind, just nowhere near 60 apparently).

 

[DanteAmor]

ParamArrays must always be Variants (I think because you can use them to recieve any type of data). You actually do not need to explicitly declare them as Variant as they will default to that if the declaration is omitted.

You might be interested in this modification to the code I posted as it combines both functions into a single one-liner function. You simply pass either "Min" or "Max" (the letter casing does not matter) in as the first argument followed by your list of values...

Function MinMax(MinOrMax As String, ParamArray Nums() As Variant) As Double
  MinMax = Evaluate(MinOrMax & "(" & Join(Nums, ",") & ")")
End Function

Hi Rick, This function, like the previous one, does not review the empty values ​​and assumes them as 0, so with positive values ​​greater than 0, if an argument is empty the result is 0 and it does not present the minimum value of the values ​​placed. If this is not a problem for the OP.

 

[Rick Rothstein]

Hi Rick, This function, like the previous one, does not review the empty values ​​and assumes them as 0, so with positive values ​​greater than 0, if an argument is empty the result is 0 and it does not present the minimum value of the values ​​placed. If this is not a problem for the OP.

I did not worry about that as Excel's MIN function operates the same way. However, it is relatively easy to fix the function to ignore blank cells (or, with the method I used, cells containing nothing but space characters) and still keep it as a one-liner.

Function MinMax(MinOrMax As String, ParamArray Nums() As Variant) As Double
  MinMax = Evaluate(MinOrMax & "(" & Replace(Application.Trim(Join(Nums, " ")), " ", ",") & ")")
End Function

 

[Rufus Clupea]

Hi Rick, This function, like the previous one, does not review the empty values ​​and assumes them as 0, so with positive values ​​greater than 0, if an argument is empty the result is 0 and it does not present the minimum value of the values ​​placed. If this is not a problem for the OP.

As it happens, this could be a problem. The values I'm working with are Integers >= 0 And <= 20, so in a Min situation, I think I would have found the problem (because of what got returned), and replaced the zeros with 20s. I think this would work for me, but it's something to keep in mind when testing, and knowing that empty commas (or omitted parameters?) would return zeros is helpful.

To ANYONE, is there a further mod to Rick's latest suggestion that would solve this issue?

Thanks folks—I learn so much here. There's only so much I get from books & web examples, I don't do well with online videos, and there are currently no continuing ed. classes in VBA offered in my area; no-one to ask for clarifications/what-ifs... I love this place!