返回混合数据类型范围内的最大值

Question

我的Excel工作表行单元格包含数字、字母和错误（例如#REF！）等数据类型的混合集合。我想把这个范围传递给一个函数，它只找到最大值，忽略字母和错误。我的代码：

Public Function getRangeMax(passedRange As range) As Single
'validate entries in passed range as numbers or skip
'return max from validated numbers
Dim i As Integer, arryLength As Integer
Dim arry()
getRangeMax = 0
arry() = passedRange
arryLength = UBound(arry) - LBound(arry)
For i = 0 To arryLength
    On Error Resume Next
    If arry(i).value > getRangeMax Then
        getRangeMax = arry(i).value
    End If
Next i
'getRangeMax = Application.Max(passedRange)
End Function

函数调用为：=getRangeMax（C35:I35）。该特定小区范围内的数据为：dB，456.00，#REF！，12.我希望代码忽略dB和#REF！条目，并返回最大数字，在本例中为456.00。相反，函数返回0.00。我做错了什么？

Answer 1

您的代码包含多个错误，所有错误都被始终活动的On Error Resume Next 隐藏

以下是您的代码重构以修复错误，并使用另一种类型检查

Public Function getRangeMax(passedRange As Range) As Double
'validate entries in passed range as numbers or skip
'return max from validated numbers
    Dim i As Long, j As Long
    Dim item As Variant
    Dim arry()
    getRangeMax = 0
    arry() = passedRange
    For i = 1 To UBound(arry, 1)
    For j = 1 To UBound(arry, 2)
        item = arry(i, j)
        If VarType(item) = vbDouble Then
            If item > getRangeMax Then
                getRangeMax = item
            End If
        End If
    Next j, i
End Function

注意：
使用VarType测试要处理的有效项将导致忽略任何为Text但看起来像数字的单元格
使用IsNummeric测试要处理的有效项目将导致任何为Text但看起来像数字的单元格转换为数字并包含在测试中。

Answer 2

试试这个：

Public Function Rng_Max_Get(rTrg As Range) As Double
Dim aTrg As Variant, vItm As Variant, blTrg As Boolean
    aTrg = rTrg.Value2
    For Each vItm In aTrg
        If IsNumeric(vItm) Then
            If vItm <> Empty Then
                If blTrg Then
                    If vItm > Rng_Max_Get Then Rng_Max_Get = vItm
                Else
                    blTrg = True
                    Rng_Max_Get = vItm
    End If: End If: End If: Next
End Function

Answer 3

由于输入的是矩形范围，因此可以使用.Rows.Count和.Columns.Count，而不是从UBound派生。试试这个：

Option Explicit
Function getRangeMax(passedRange As Range) As Double
    'validate entries in passed range as numbers or skip
    'return max from validated numbers
    Dim lRows As Long, lCols As Long, lR As Long, lC As Long
    Dim uValue As Double, uMax As Double
    Dim arry() As Variant
    arry = passedRange
    lRows = passedRange.Rows.Count
    lCols = passedRange.Columns.Count
    uMax = 0
    For lR = 1 To lRows
        For lC = 1 To lCols
            If IsNumeric(arry(lR, lC)) Then
                uValue = CDbl(arry(lR, lC))
                If uValue > uMax Then uMax = uValue
            End If
        Next
    Next
    getRangeMax = uMax
End Function