Can you identify the maximum value in an array?

[Brad Patterson]

Dim Arr(5) as integer

Arr(2) = 99
Arr(3) = 101

How do I then find out what the maximum value is, and what position it¹s at
in this type of array ­ i.e. for the above, the maximum is 101, at position
3.

I¹ve had success with

Application.WorksheetFunction.Large(Arr, 1)

But still can¹t get the position of that number.

I¹m attempting this with an array that is >1000 in size if that¹s going to
be a problem ...

Thanks,

Brad.)

 

[Alan Beban]

Application.Match(Application.Max(Arr),Arr) or

Application.Match(Application.Large(Arr,1),Arr)

Alan Beban

 

[Pete McCosh]

Brad,
there's probably a simpler way, but you could loop through
the array testing all the values. This code works fine,
although you'll have to modify it if your highest value is
potentially below zero and if there are two or more equal
highest values it'll only identify the first, but I'm sure
you can cross that bridge when you come to it.

Sub BiggestInArray()

Dim TestArray(1000) As Integer, HighestValue As Integer,
HighestPos As Integer
HighestValue = 0

TestArray(1) = 3
TestArray(54) = 46
TestArray(897) = 24

For x = LBound(TestArray) To UBound(TestArray)

If TestArray(x) > HighestValue Then
HighestValue = TestArray(x)
HighestPos = x
End If

Next x

MsgBox ("The highest value in the array is " &
HighestValue & " at position " & HighestPos & ".")

End Sub

Cheers, Pete

 

[Patrick Molloy]

WorksheetFunction.Match(max, arr, False)

Example
Sub test()

Dim ar(1 To 5) As Long
Dim i As Long
Dim max As Long
For i = 1 To 5
ar(i) = Int(Rnd * 1000)
Next
max = Application.WorksheetFunction.max(ar)
Debug.Print max; " @ "; _
WorksheetFunction.Match(max, ar, False)

End Sub


Patrick Molloy
Microsoft Excel MVP

 

[Dana DeLouis]

Just out of educational curiosity... On an array with 1,000 numbers, I
found that using Max was a little faster than Large by about 20-30% .

 

[Brad Patterson]

This works very well. Just when I think there isn¹t a function to do what I
want ... There it is. ...

Thanks again,

Brad.)