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

 

[Fluff]

How about

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

   GreatestOf = Application.max(a, b, c, d, e)
End Function

 

[DanteAmor]

Try this:

Function [COLOR=#0000ff]Max_Min_Of[/COLOR](MaxMin As String, a As Long, b As Long, Optional c As Variant, Optional d As Variant, Optional e As Variant)
    If LCase(MaxMin) = "max" Then
        Max_Min_Of = WorksheetFunction.Max(a, b, c, d, e)
    Else
        If IsMissing(c) Then c = WorksheetFunction.Max(a, b, c, d, e)
        If IsMissing(d) Then d = WorksheetFunction.Max(a, b, c, d, e)
        If IsMissing(e) Then e = WorksheetFunction.Max(a, b, c, d, e)
        Max_Min_Of = WorksheetFunction.Min(a, b, c, d, e)
    End If
End Function

 

[Rufus Clupea]

How about

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

   GreatestOf = Application.max(a, b, c, d, e)
End Function

Thank you.

In looking this up, I see that there are also WorksheetFunction.Max (and .Min) methods.
Some sources seem to prefer one over the other (Application vs. WorksheetFunction). Do you have a preference, and if so, may I ask why/your reasoning?

In your example, would I specify zeros in the "optional" arguments (a, b, 0, 0, 0), just commas (a, b,,,) or just (a, b)?

 

[Fluff]

You would just specify the arguments you want to pass, like
=GreatestOf(5,10,11,99)
You can use application, or worksheetfunction, or even application.worksheetfunction
I tend to use the former, because in some instances it can give you better control.
For instance if you use Application.Match, you can test for an error (if nothing matches), which you cannot do with the other two methods.

 

[Rufus Clupea]

Try this:

Function [COLOR=#0000ff]Max_Min_Of[/COLOR](MaxMin As String, a As Long, b As Long, Optional c As Variant, Optional d As Variant, Optional e As Variant)
    If LCase(MaxMin) = "max" Then
        Max_Min_Of = WorksheetFunction.Max(a, b, c, d, e)
    Else
        If IsMissing(c) Then c = WorksheetFunction.Max(a, b, c, d, e)
        If IsMissing(d) Then d = WorksheetFunction.Max(a, b, c, d, e)
        If IsMissing(e) Then e = WorksheetFunction.Max(a, b, c, d, e)
        Max_Min_Of = WorksheetFunction.Min(a, b, c, d, e)
    End If
End Function

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)?
• Why declare c, d, & e as Variants instead of Long?
• As I asked Fluff, would you mind elucidating your preference of WorksheetFunction over Application?
• 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)?

Thanks again

 

[Eric W]

You could also use something like this:

Function GreatestOf(ParamArray SubArr() As Variant) As Double
    GreatestOf = WorksheetFunction.Max(SubArr)
End Function

which would work for an unlimited number of parameters, no commas necessary.

=GreatestOf(1,2)

=GreatestOf(1,2,3,4,5,6,7,8)

=GreatestOf(A1,B2,C3,PI(),8,9)

 

[Rick Rothstein]

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

 

[Rufus Clupea]

You could also use something like this:

Function GreatestOf(ParamArray SubArr() As Variant) As Double
    GreatestOf = WorksheetFunction.Max(SubArr)
End Function

which would work for an unlimited number of parameters, no commas necessary.

=GreatestOf(1,2)

=GreatestOf(1,2,3,4,5,6,7,8)

=GreatestOf(A1,B2,C3,PI(),8,9)

Thanks Eric,

That one may take me a bit longer to digest...
Right now, my project is only dealing with small Integers, but for some future project...

To help me understand what you're doing, what's the purpose of the Variant declaration?

 

[Rick Rothstein]

Function GreatestOf(ParamArray SubArr() As Variant) As Double
    GreatestOf = WorksheetFunction.Max(SubArr)
End Function

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.

which would work for an unlimited number of parameters

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).

no commas necessary.

Not sure what this comment applied to as the arguments to a ParamArray must be separated by commas.