You can use this function to return the Max Value, Like wise you can update the function to return Min Value.
Function to Return Max Value in an Array
Public Function getArrMaxValueIndex(ByVal arr)
Dim ix, ixMax
ixMax = 0
For ix = 1 To UBound(arr)
If ( arr(ixMax) < arr(ix) ) Then
ixMax = ix
End If
Next
getArrMaxValueIndex = ixMax
End Function
'Define array and index for max entry
Dim arr, ixMax
'Initialize the array
arr = Array(4, 1, 3, 6, 7, 23,567,34,44)
'Get the index of the max value
ixMax = getArrMaxValueIndex(arr)
'Print result
MsgBox "Max value: " & arr(ixMax) & " was found at " & ixMax & " index."
Cheers,
Anoj Atapattu