在Excel排序宏中调用VBA自定义num函数遇类型不匹配，求解决

First, let's break down why you're hitting that type mismatch error: the SortOn parameter in Excel's Sort object only accepts built-in enumeration values like xlSortOnValues or xlSortOnCellColor—you can't pass the result of your custom num() function directly here. Excel's Sort tool doesn't support using custom functions as a sort criteria out of the box, so we need to adjust our approach.

Here are two solid solutions that let you sort by the numeric part of the call sign without modifying your original data:

Solution 1: Use a Temporary Helper Column (Most Straightforward) #

This method inserts a temporary column to store the extracted numeric parts using your existing num() function, sorts based on that column, then deletes the helper column—no permanent changes to your original data.

Updated Macro Code #

Sub sortscenarionum()
    ' Sort Aircraft by FLIGHT NUMBER (numeric part) then RPO TIME
    Dim ws As Worksheet
    Dim tempCol As Integer
    Dim lastRow As Long
    
    Set ws = ActiveWorkbook.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "N").End(xlUp).Row ' Get last row with data in column N
    
    ' Insert temporary helper column (column O in this example—adjust if needed)
    tempCol = 15
    ws.Columns(tempCol).Insert
    
    ' Populate helper column with numeric parts using your num() function
    ws.Range(ws.Cells(11, tempCol), ws.Cells(lastRow, tempCol)).Formula = "=num(N11)"
    ws.Calculate ' Ensure formulas finish calculating
    
    ' Set up sort criteria
    ws.Sort.SortFields.Clear
    ' First sort key: helper column (numeric part of call sign)
    ws.Sort.SortFields.Add Key:=ws.Range(ws.Cells(11, tempCol), ws.Cells(lastRow, tempCol)), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ' Second sort key: RPO TIME (column I)
    ws.Sort.SortFields.Add Key:=ws.Range("I11:I" & lastRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    ' Execute sort
    With ws.Sort
        .SetRange ws.Range("B11:N" & lastRow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ' Clean up: delete temporary helper column
    ws.Columns(tempCol).Delete
    
    SendKeys "{ESC}"
End Sub

' Keep your existing num() function intact
Function num(rng As Range) As String
    Dim n As Integer
    For n = 1 To Len(rng)
        If Mid(rng, n, 1) Like "[0-9]" Then
            num = num & Mid(rng, n, 1)
        End If
    Next n
End Function

Why This Works #

• Leverages your already-written num() function, so you don't have to rewrite logic.
• The temporary column is invisible to your workflow (it's deleted right after sorting).
• Easy to debug and adjust if your call sign format changes later.

Solution 2: Integrate Numeric Extraction Directly into VBA (No Helper Column) #

If you prefer not to modify the worksheet structure at all, you can embed the numeric extraction logic directly into the macro using arrays. This keeps all processing in memory, no worksheet changes needed.

Macro Code with Integrated Logic #

Sub sortscenarionum_nohelper()
    ' Sort Aircraft by FLIGHT NUMBER (numeric part) then RPO TIME - no helper column
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim dataArr As Variant
    Dim sortKeys As Variant
    Dim i As Long, j As Long
    Dim tempData As Variant
    Dim tempNumKey As String
    Dim tempTimeKey As Double
    
    Set ws = ActiveWorkbook.ActiveSheet
    Set dataRange = ws.Range("B11:N159") ' Your target data range
    dataArr = dataRange.Value ' Load data into an array for fast processing
    
    ' Create array to store sort keys: (1) numeric part of call sign, (2) RPO TIME value
    ReDim sortKeys(1 To UBound(dataArr, 1), 1 To 2)
    
    ' Populate sort keys for each row
    For i = 1 To UBound(dataArr, 1)
        ' Extract numeric part from column N (12th column in the data range, since B=1)
        sortKeys(i, 1) = ExtractNumeric(dataArr(i, 12))
        ' Get RPO TIME value from column I (8th column in the data range)
        sortKeys(i, 2) = dataArr(i, 8)
    Next i
    
    ' Bubble sort (swap to quicksort for large datasets)
    For i = 1 To UBound(dataArr, 1) - 1
        For j = i + 1 To UBound(dataArr, 1)
            ' Sort first by numeric part, then by RPO TIME
            If sortKeys(i, 1) > sortKeys(j, 1) Or _
               (sortKeys(i, 1) = sortKeys(j, 1) And sortKeys(i, 2) > sortKeys(j, 2)) Then
                ' Swap data rows
                tempData = dataArr(i, :)
                dataArr(i, :) = dataArr(j, :)
                dataArr(j, :) = tempData
                ' Swap corresponding sort keys
                tempNumKey = sortKeys(i, 1)
                tempTimeKey = sortKeys(i, 2)
                sortKeys(i, 1) = sortKeys(j, 1)
                sortKeys(i, 2) = sortKeys(j, 2)
                sortKeys(j, 1) = tempNumKey
                sortKeys(j, 2) = tempTimeKey
            End If
        Next j
    Next i
    
    ' Write sorted data back to the worksheet
    dataRange.Value = dataArr
    
    SendKeys "{ESC}"
End Sub

' Embedded version of your num() function for VBA-only use
Function ExtractNumeric(inputStr As String) As String
    Dim n As Integer
    For n = 1 To Len(inputStr)
        If Mid(inputStr, n, 1) Like "[0-9]" Then
            ExtractNumeric = ExtractNumeric & Mid(inputStr, n, 1)
        End If
    Next n
End Function

Notes for This Approach #

• If you're working with large datasets (hundreds/thousands of rows), replace the bubble sort with a more efficient algorithm like quicksort to speed things up.
• All processing happens in memory, so it won't clutter your worksheet with temporary columns.

Quick Tips #

• Test either macro on a copy of your data first to avoid accidental changes.
• If your call signs ever include non-alphanumeric characters, adjust the ExtractNumeric/num() function's logic to handle those cases.

内容的提问来源于stack exchange，提问作者Stuart K. Smith