max min VBA

[Donjt81]

How do I get maximum of two numbers in VBA

lets say I have
var1 = 2
var2 = 30

I want to compare the 2 and get the maximum

so maybe something like this. (but i know this doesnt work)

result = max(var1, var2)

 

[Von Pookie]

One way to find the max value:

result = WorksheetFunction.Max(var1, var2)

Edit:

You can find the min value this way as well.

result = WorksheetFunction.Min(var1, var2)

 

[wiz329]

I realize this is a very old thread, but I'm curious -- what is the fastest way to do this? I will be finding the minimum of ~20 number several million times, so I'm looking for the most efficient way to do this.

 

[TimFoley]

Just to keep raising the dead...

I found that the standard approach WorksheetFunction was a bit inefficient so I wrote a simple replacement function for it:

Function Min(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
   Min = minValue
End Function

What do i mean by inefficient? Processing an array of 60,000 entries using the Levenshtein Distance algorithm to compare a string in the array to another, I cut the time per loop in half.

 

[TimFoley]

Just to be complete, for Max, just reverse the inequality comparitor:

If Value > minValue Then minValue = Value

 

[JenniferMurphy]

Just to be complete, for Max, just reverse the inequality comparitor:

If Value > minValue Then minValue = Value

Thanks for taking the time to fill in a long standing hole left by the geniuses at Microsoft.

I'm sure you meant to change minValue to maxValue

Here's the complete code for both functions:

Function Min(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
   Min = minValue
End Function

Function Max(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
   Max = maxValue
End Function

 

[Juggler_IN]

How to apply these functions to a Range?

 

[Prasad K]

How to apply these functions to a Range?

Try This
to apply this =minMax(A1:A10,"min") and hit Enter
=minMax(A1:A10,"max") and hit Enter

Function minMax(ByVal rRange As Range, MinOrMax As String) As Double
Dim dMin As Double
Dim dMax As Double
Dim lLastRow As Long
Dim ws1 As Worksheet

Set ws1 = ActiveWorkbook.ActiveSheet
lLastRow = ws1.Cells(rRange.Row, rRange.Column).End(xlDown).Row

dMin = ws1.Cells(rRange.Row, rRange.Column).Value
dMax = dMin

For Each cell In rRange.Cells
    If cell.Value < dMin Then dMin = cell.Value
    If cell.Value > dMax Then dMax = cell.Value
Next cell

    If InStr(1, MinOrMax, "min") = 1 Then
        minMax = dMin
    Else
        minMax = dMax
    End If

End Function