Access获取10字段第二高值VBA代码报错求助：类型不匹配

Hey there! Since you're new to VBA but can follow logical steps, let's work through your two main issues: the "type mismatch" error and the 0-value edge cases.

1. Fixing the "Type Mismatch" Error on If Not (Not tmpArray) Then #

That line is an older VBA trick to check if a dynamic array is initialized, but it's unreliable for certain data types (like non-Variant arrays) or if the array wasn't set up correctly. It triggers a type mismatch because VBA can't evaluate Not tmpArray when the array isn't properly declared or initialized.

Replace that line with a safer, more explicit check using error handling to verify if the array has been allocated memory:

Dim arrayIsValid As Boolean
On Error Resume Next
' Try to get the upper bound of the array—if it fails, the array isn't initialized
arrayIsValid = (UBound(tmpArray) >= LBound(tmpArray))
On Error GoTo 0 ' Reset error handling

If arrayIsValid Then
    ' Your sorting/second highest logic goes here
End If

This method works consistently across different array types and avoids the type mismatch entirely.

2. Fixing 0-Value Edge Cases (First Field = 0 or All Fields = 0) #

Your original two-sort logic likely fails here because it doesn't account for duplicate values or 0 being a valid value. Let's adjust the logic to:

• Sort the array in descending order (easier to spot top values)
• Skip duplicate maximum values to find the next highest (even if all values are 0)

Here's the full revised function that handles these cases:

Function GetSecondHighest(ParamArray fieldValues() As Variant) As Double
    Dim tmpArray As Variant
    Dim i As Integer, j As Integer
    Dim temp As Double
    Dim maxVal As Double
    Dim secondMax As Double
    Dim arrayIsValid As Boolean
    
    ' Copy input values to temporary array
    tmpArray = fieldValues
    
    ' Check if array is valid (fixes type mismatch error)
    On Error Resume Next
    arrayIsValid = (UBound(tmpArray) >= LBound(tmpArray))
    On Error GoTo 0
    
    If arrayIsValid Then
        ' Sort array in DESCENDING order
        For i = LBound(tmpArray) To UBound(tmpArray) - 1
            For j = i + 1 To UBound(tmpArray)
                If tmpArray(i) < tmpArray(j) Then
                    temp = tmpArray(i)
                    tmpArray(i) = tmpArray(j)
                    tmpArray(j) = temp
                End If
            Next j
        Next i
        
        ' Set initial max value
        maxVal = tmpArray(LBound(tmpArray))
        secondMax = maxVal ' Default to max if all values are identical
        
        ' Find the first value lower than the max (our second highest)
        For i = LBound(tmpArray) + 1 To UBound(tmpArray)
            If tmpArray(i) < maxVal Then
                secondMax = tmpArray(i)
                Exit For ' Stop once we find the target value
            End If
        Next i
        
        GetSecondHighest = secondMax
    Else
        ' Handle empty input (adjust return value if needed)
        GetSecondHighest = 0
    End If
End Function

How This Fixes the 0-Value Issues: #

• All fields are 0: The loop sets maxVal to 0, and since all subsequent values match, secondMax stays 0 (correct behavior).
• First field is 0 but others are higher: The descending sort pushes the highest values to the front, so we'll skip leading 0s (if they aren't the max) and find the second highest correctly.

Testing the Function #

You can test it with these cases to verify:

• GetSecondHighest(0, 5, 3, 8, 2) → Returns 5 (max is 8)
• GetSecondHighest(0, 0, 0, 0) → Returns 0
• GetSecondHighest(10, 10, 5, 10) → Returns 5 (skips duplicate max values)
• GetSecondHighest(0, 1, 0, 3) → Returns 1 (max is 3)

内容的提问来源于stack exchange，提问作者liamthequietman