CODEWITHSUNDEEP
vbaexcel
Frits Verstraten
Frits Verstraten

Reputation: 2179

Fetch the maximum value from an array

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?

Upvotes: 10

Views: 79717

Answers (5)

ragoertz
ragoertz

Reputation: 13

This will find the maximum value in an x-dimensional array (or even a range (e.g., debug.print ArrayMax(Range("A1:C4"))), and will work with either numerical or string values:

Sub CreateArray()
    Dim a(5, 5, 5)
    Dim i As Integer, j As Integer, k As Integer

    For i = 0 To 5
        For j = 0 To 5
            For k = 0 To 5
                a(i, j, k) = (i + 1) * (j + 1) * (k + 1) & "a"
            Next k
        Next j
    Next i
    
    Debug.Print ArrayMax(a)
End Sub

Function ArrayMax(a)
    Dim e As Variant

    'Get first value
    For Each e In a
        ArrayMax = e
        Exit For
    Next e
    
    For Each e In a
        ArrayMax = IIf(e > ArrayMax, e, ArrayMax)
    Next e
End Function

Upvotes: 0

Ans
Ans

Reputation: 1234

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

Upvotes: 0

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

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

Upvotes: 17

Ralph
Ralph

Reputation: 9434

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.

Upvotes: 3

RGA
RGA

Reputation: 2607

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

Upvotes: 5

Related Questions