在排序宏中使用VBA函数

Question

在排序宏中使用VBA函数

3

我维护一个包含多个列数据的Excel电子表格，可以通过各种方式进行排序以满足每个用户的需求。我使用宏设置了一些按钮在工作表上用于执行各种排序。

一个用户要求能够按照字母数字组合列进行排序，但是只按照数字进行排序。这些数据是飞机呼号，包含1到3个字母和1到5个数字。用户希望按照航班号码进行排序，而不考虑注册字母。

我发现了一个名为"num()"的函数可以实现此目的。我希望在不改变列中数据的情况下使用此函数。以下是我想要的示例：

Sub sortscenarionum()
'
' sortscenarionum Macro
' Sort Aircraft by FLIGHT NUMBER then RPO TIME
'
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "N11:N159"), SortOn:=num("N11:N159"), Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "I11:I159"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("B11:N159")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    SendKeys "{ESC}"
End Sub

出现“类型不匹配”错误。我也尝试了SortOn:=num(xlSortValues)，但结果仍然是负面的。将该函数移入宏本身没有问题，但我不知道该如何实现。以下是该函数的内容：

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

- Stuart K. Smith

你确定 num 总是返回一个值吗？在排序之前添加一行 Debug.Print num，看看它返回了什么。 - dwirony

num仅适用于一个单元格，而不是一系列单元格。我认为您需要将num的输出添加到另一列，并按该列排序。 - Scott Craner

4

在我看来，你正在将一个字符串传递给 num，但是函数期望的是一个范围。 - Brian M Stafford

同意@ScottCraner的建议 - 添加一个额外的列，只包含航班号码而不带航空公司前缀。将该列从用户视图中隐藏，以免他们看到它或被它搞混。通过宏对该列进行排序。 - FreeMan

NUM函数在其所需的情况下表现得非常完美。唯一的问题是它运行速度较慢。但随着下面答案的揭示，我发现我不需要这个函数。虽然我确实考虑过添加额外的列，但由于此工作簿中有200多页，所以这将花费一些时间。 - Stuart K. Smith

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user4039065 · Accepted Answer

添加一个用作辅助的列；填充新列并按新列排序；删除新列。

Sub sortscenarionum()
    With ActiveWorkbook.ActiveSheet
        .Columns("O").Insert
        With .Range(.Cells(11, "B"), .Cells(.Rows.Count, "N").End(xlUp).Offset(0, 1))
            .Columns(.Columns.Count).Formula = "=numsOnly(N11)"
            .Columns(.Columns.Count).Value = .Columns(.Columns.Count).Value
            .Sort Key1:=.Columns(.Columns.Count), Order1:=xlAscending, _
                  Key2:=.Columns(8), Order2:=xlAscending, _
                  Orientation:=xlTopToBottom, Header:=xlNo
        End With
        .Columns("O").Delete
    End With
End Sub

Function numsOnly(str As String)    
    'with rgx as static, it only has to be created once
    Static rgx As Object
    If rgx Is Nothing Then
        Set rgx = CreateObject("VBScript.RegExp")
    End If
    numsOnly = vbNullString

    With rgx
        .Global = True
        .MultiLine = False
        .Pattern = "[0-9]{1,5}$"
        If .test(str) Then
            numsOnly = CLng(.Execute(str)(0))
        End If
    End With
End Function