Does VBA have Native Functions for MIN, MAX

[Jeffrey Mahoney]

I'm dabbling more into the VBA code for Outlook recently. I have external systems that send me emails with updates for services and notifications. The format of these emails are atrocious.

What I've noticed is that VBA doesn't have a built MIN and MAX function. Since I work primarily in Excel, I'm used to using Application.Min(). Outlook doesn't have that.

Does anybody find it strange that such a simple function doesn't exist? Am I looking at this wrong? Since early days of BASIC those were available. Comparing two variables with IF statements isn't too bad, but start adding 3 or more variables and now the IF structure gets kinda difficult.

 

[lrobbo314]

How about this

Function jMax(ParamArray Vals() As Variant)
Dim hi As Variant: hi = 0
For i = LBound(Vals) To UBound(Vals)
    If Vals(i) > hi Then hi = Vals(i)
Next i
jMax = hi
End Function

Sub test()
Debug.Print jMax(234, 456, 265, 785, 345)
End Sub

 

[Jeffrey Mahoney]

That is highly functional, allowing people to NOT have to add the values into arrays.

Still though: for simplicity sake MS, why'd you leave those out!! ?

Stepping down off my soapbox.

 

[Jaafar Tribak]

That is highly functional, allowing people to NOT have to add the values into arrays.

Still though: for simplicity sake MS, why'd you leave those out!! ?

Stepping down off my soapbox.

I have always found the lack of such basic and common functions in vb6\vba weird and ridiculous.

 

[Eric W]

If you are only comparing 2 values, you can use IIf:

m = IIf(a > b, a, b)

Otherwise something like lrobbo's function should work. (Although I'd suggest changing hi = 0 to hi = Vals(0) to handle the case where they're all negative.)

But I agree it's ridiculous not to have Max or Min functions. It's frustrating to get halfway through a project, then realize you have to take time out from your main effort to write a function that should have been there in the first place.

 

[mikerickson]

I also find it odd that VBA doesn't contain a basic Min or Max function.
But I do note that the worksheet function MAX takes an unspecified number of arguments and that each of those arguments can be a range, an array or a single value.
That flexibility in what arguments are accepted is very rare in VBA. (not so in worksheet functions, eg. SUM)

That said, I still find it odd that there isn't a VBA function that is the equivalent of Eric W's one liner.

 

[Jon Peltier]

Surprised that nobody has bothered to suggest WorksheetFunction.Max and WorksheetFunction.Min. Not really "native", but close enough.

 

[Jaafar Tribak]

Surprised that nobody has bothered to suggest WorksheetFunction.Max and WorksheetFunction.Min. Not really "native", but close enough.

That won't work in other vba hosts such as outlook as mentioned by the OP.

 

[Rick Rothstein]

Here is another set of one-liners (uses no function calls)...

Max = -A * (A >= B) - B * (B > A)

Min = -A * (A <= B) - B * (B < A)

Note: The single use of an equal sign inside only one the logical expressions is intentional (it protects against both values being the same).

 

[Jon Peltier]

Here is another set of one-liners (uses no function calls)...

Max = -A * (A >= B) - B * (B > A)

Min = -A * (A <= B) - B * (B < A)

Note: The single use of an equal sign inside only one the logical expressions is intentional (it protects against both values being the same).

But these are rather obscure, and hard to follow. The Worksheet.Function calls are somewhat intuitive.