vba min max code example

Example 1: excel vba min function

'VBA function to find the minimum value from a list:

Function Min(ParamArray vals())
    Dim i&
    If UBound(vals) > -1 Then
        Min = vals(0)
        For i = 1 To UBound(vals)
            If vals(i) < Min Then Min = vals(i)
        Next
    End If
End Function

'------------------------------------------------------------------------------

MsgBox Min(7, 2, 0, 5.9, 8, 4.75)		'<--dsiplays: 0
    
    
'Note: the MIN() worksheet function is also available but requires the 
'slow call to the worksheet calcuation engine:
      
MsgBox WorksheetFunction.Min(7, 2, 0, 5.9, 8, 4.75)

Example 2: vba min

Sub TestMe()
    Dim v(9), i As Byte
    For i = 0 To 9
        v(i) = Int(50 * Rnd) + 1
    Next i
    MsgBox Application.WorksheetFunction.Min(v)
    MsgBox Application.WorksheetFunction.Max(v)
    ' Shorter:  Application.Min(v)    Application.Max(v)
End Sub