vba 从数组中获取最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37789475/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fetch the maximum value from an array
提问by Frits Verstraten
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?
我现在想做的是从字符串中的所有值中获取最大值。VBA 中是否有一种简单的方法可以从数组中获取最大值？
回答by Robin Mackenzie
Is there an easy way in VBA to fetch the max value from an array?
VBA 中是否有一种简单的方法可以从数组中获取最大值？
Yes - if the values are numeric. You can use WorksheetFunction.Maxin VBA.
是 - 如果值是数字。您可以WorksheetFunction.Max在 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
回答by Ralph
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) afas the maximum, while the second function will only consider numbers and therefore returns 6as the maximum.
第一个函数（比较文本）将返回（使用上面的示例数据）af作为最大值，而第二个函数将只考虑数字，因此返回6最大值。
回答by RGA
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
回答by Ans
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
