excel 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: excel vba max function

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

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

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

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