Как найти максимальное значение массива vba

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?

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

answered Jun 13, 2016 at 12:11

The easiest way to retrieve the maximum (I can think of) is iterating through the array and comparing the values. The following two functions do just that:

Option Explicit

Public Sub InitialValues()

Dim strValues(1 To 3) As String

strValues(1) = 3
strValues(2) = "af"
strValues(3) = 6

Debug.Print GetMaxString(strValues)
Debug.Print GetMaxNumber(strValues)

End Sub

Public Function GetMaxString(ByRef strValues() As String) As String

Dim i As Long

For i = LBound(strValues) To UBound(strValues)
    If GetMaxString < strValues(i) Then GetMaxString = strValues(i)
Next i

End Function

Public Function GetMaxNumber(ByRef strValues() As String) As Double

Dim i As Long

For i = LBound(strValues) To UBound(strValues)
    If IsNumeric(strValues(i)) Then
        If CDbl(strValues(i)) > GetMaxNumber Then GetMaxNumber = CDbl(strValues(i))
    End If
Next i

End Function

Note, that each time a string (text) array is passed to the function. Yet, one function is comparing strings (text) while the other is comparing numbers. The outcome is quite different!

The first function (comparing text) will return (with the above sample data) af as the maximum, while the second function will only consider numbers and therefore returns 6 as the maximum.

answered Jun 13, 2016 at 12:22

Solution for Collection.

Sub testColl()
    Dim tempColl As Collection
    Set tempColl = New Collection
    tempColl.Add 57
    tempColl.Add 10
    tempColl.Add 15
    tempColl.Add 100
    tempColl.Add 8


    Debug.Print largestNumber(tempColl, 2)  'prints 57
End Sub

Function largestNumber(inputColl As Collection, indexMax As Long)
        Dim element As Variant
        Dim result As Double
        result = 0

        Dim i As Long
        Dim previousMax As Double

        For i = 1 To indexMax
            For Each element In inputColl
                If i > 1 And element > result And element < previousMax Then
                    result = element
                ElseIf i = 1 And element > result Then
                    result = element
                End If
            Next

            previousMax = result
            result = 0
        Next

        largestNumber = previousMax
End Function

answered Oct 27, 2017 at 12:21