我有一个看起来像这样的数组:
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
我现在想做的是从字符串中获取所有值中的最大值。在VBA中有一种简单的方法可以从数组中获取最大值吗?
在VBA中,如果要从数组中获取最大值,是否有一种简单的方法?
是的 - 如果值是数字类型的,则可以在VBA中使用 WorksheetFunction.Max
。
对于字符串类型的值- 这种方法不适用。
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
WorksheetFunction.Max(Range("E4:E6").Value)
? - shahkalpesh简单的循环就可以解决问题
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
我能想到的最简单的方法是遍历数组并比较值来检索最大值。以下两个函数就是这样做的:
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
集合的解决方案。
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
debug.print ArrayMax(Range("A1:C4"))
),并且可以处理数字或字符串值: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
Double
作为数组元素类型吗? - Bathsheba