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]

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

This one crossed with my last post !

"Cells"? I hope that also means arguments, as there aren't any Worksheet cells involved in this part of my project; I'd have to add some code and figure out someplace on a Sheet to stash the arguments. Not impossible, but I'd hoped to do this just with code.

 

[Rufus Clupea]

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.

Difference in ExcelVBA versions? (Just a guess) I'm working in Excel 2016/VBA 7.1
Looks like I have a few things to try...

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[DanteAmor]

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

Beautiful

 

[Rufus Clupea]

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

Still something I'm not getting here... Copied the code to my Functions module (actually, I tried both versions), and as a test, put
MsgBox MinMax(Min, 13, 14, 15)
at the end of the UserForm_Initialize() Sub

I get a "Variable not defined" error, highlighting Min.
Maybe I've been coding/up too long...?

 

[Marcelo Branco]

Maybe...

MsgBox MinMax("Min", 13, 14, 15)

M.

 

[Rick Rothstein]

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

Yes, that worked for me too! Odd indeed. I always thought a ParamArray was just a normal array, which in almost all respects, it is. But for some reason, it cannot be passed into a WorksheetFunction function (at least the Max function as I did not try any others).

 

[Rick Rothstein]

Maybe...

MsgBox MinMax("Min", 13, 14, 15)

Just to follow up on Marcelo's comment... if you look at the data type I designated the MinOrMax argument as, you will see it is a String... when you pass text constants into a String or Variant argument, you have to quote it just like you would if you were assigning such a text constant to a variable declared as String or Variant.

 

[Rufus Clupea]

Yup, that did it!

Thank you both. Still new enough to VBA that I haven't caught on to all the nuances yet.
To paraphrase GM, "Not your Commodore's BASIC"

Egads... just realized how badly that dates me. Mebbe I shoulda paraphrased Rocket Raccoon and said, "I haven't yet worked out the minutiae of the syntax."

 

[Rick Rothstein]

To paraphrase GM, "Not your Commodore's BASIC"

Egads... just realized how badly that dates me.

I think I am more "dated" than you... it was TI-BASIC for me. My first computer was the original TI-99/4 (not the 4A which came later) which I bought in 1981. That was a great machine graphic-wise for its time. I wrote and sold articles to COMPUTE! magazine back in 1983/1984 and one of the articles shows off the graphic superiority available from TI-BASIC. This is a link to that article...

http://archive.org/stream/1983-10-compute-magazine/Compute_Issue_041_1983_Oct#page/n103/mode/2up

It is for a Number #15 puzzle simulator and "starts" on a insert article (to the main article) by me (interesting story behind that if anyone is interested)... scroll the pages backward (left pointing arrow, bottom of screen) to see the graphics for the other computer simulations... mine is on the first page of the article (you'll recognize it when you see it).