Fetch the maximum value from an array

Question

I have an array that looks like this:

Dim values(1 To 3) As String

values(1) = Sheets("risk_cat_2").Cells(4, 6).Value
values(2) = Sheets("risk_cat_2").Cells(5, 6).Value
values(3) = Sheets("risk_cat_2").Cells(6, 6).Value

What I would like to do now is get the maximum value from all the values in string. Is there an easy way in VBA to fetch the max value from an array?

Answer 1

Is there an easy way in VBA to fetch the max value from an array?

Yes - if the values are numeric. You can use WorksheetFunction.Max in VBA.

For strings - this won't work.

Sub Test2()
    Dim arr(1 To 3) As Long

    arr(1) = 100
    arr(2) = 200
    arr(3) = 300

    Debug.Print WorksheetFunction.Max(arr)

End Sub

Answer 2

Simple loop would do the trick

Dim Count As Integer, maxVal As Long
maxVal = Values(1)
For Count = 2 to UBound(values)
    If Values(Count) > maxVal Then
        maxVal = Values(Count)
    End If
Next Count