排序Collection对象时触发Run-time error 438：对象不支持该属性或方法

错误原因 #

VBA的Collection对象没有内置的Sort方法，你调用combinations.Sort属于调用了该对象不存在的方法，因此触发Run-time error 438（对象不支持该属性或方法）。

解决方案 #

以下是几种可行的解决方式：

方案1：转存到数组后排序（最常用，无需额外引用）

把Collection中的元素转移到数组，手动实现排序逻辑，再将排序后的内容输出：

Sub GenerateNumberCombinations()
    Dim numbers As Variant
    Dim combinations As Collection
    Dim i As Long, j As Long, k As Long
    Dim row As Long
    Dim arr() As String
    Dim idx As Long

    numbers = Array(1, 4, 7, 0, 3, 9)
    Set combinations = New Collection

    ' 生成所有三位组合并加入Collection
    For i = LBound(numbers) To UBound(numbers)
        For j = LBound(numbers) To UBound(numbers)
            For k = LBound(numbers) To UBound(numbers)
                combinations.Add numbers(i) & numbers(j) & numbers(k)
            Next k
        Next j
    Next i

    ' 将Collection转存到数组
    ReDim arr(1 To combinations.Count)
    For idx = 1 To combinations.Count
        arr(idx) = combinations(idx)
    Next idx

    ' 对数组进行升序排序
    SortArray arr

    ' 输出排序后的结果
    row = 2
    For idx = LBound(arr) To UBound(arr)
        Cells(row, 1).Value = arr(idx)
        row = row + 1
    Next idx
End Sub

' 辅助排序函数（冒泡排序）
Sub SortArray(arr As Variant)
    Dim i As Long, j As Long
    Dim temp As String
    For i = LBound(arr) To UBound(arr) - 1
        For j = i + 1 To UBound(arr)
            If arr(i) > arr(j) Then
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j
    Next i
End Sub

方案2：使用SortedList对象（自动排序）

利用System.Collections.SortedList对象实现自动排序插入，无需手动处理排序逻辑：

Sub GenerateNumberCombinations_SortedList()
    Dim numbers As Variant
    Dim sortedCombos As Object ' 后期绑定SortedList
    Dim i As Long, j As Long, k As Long
    Dim row As Long
    Dim combo As String

    numbers = Array(1, 4, 7, 0, 3, 9)
    Set sortedCombos = CreateObject("System.Collections.SortedList")

    ' 生成组合并加入SortedList（自动按键升序排列）
    For i = LBound(numbers) To UBound(numbers)
        For j = LBound(numbers) To UBound(numbers)
            For k = LBound(numbers) To UBound(numbers)
                combo = numbers(i) & numbers(j) & numbers(k)
                ' 可选：避免重复组合，若允许重复可直接调用Add
                If Not sortedCombos.ContainsKey(combo) Then
                    sortedCombos.Add combo, combo
                End If
            Next k
        Next j
    Next i

    ' 输出排序后的结果
    row = 2
    For Each combo In sortedCombos.Keys
        Cells(row, 1).Value = combo
        row = row + 1
    Next combo
End Sub

方案3：插入时直接维护有序Collection

在生成组合时，直接将元素插入到Collection的合适位置，保持集合始终有序：

Sub GenerateNumberCombinations_OrderedCollection()
    Dim numbers As Variant
    Dim combinations As Collection
    Dim i As Long, j As Long, k As Long
    Dim row As Long
    Dim combo As String
    Dim idx As Long

    numbers = Array(1, 4, 7, 0, 3, 9)
    Set combinations = New Collection

    ' 生成组合并插入到有序位置
    For i = LBound(numbers) To UBound(numbers)
        For j = LBound(numbers) To UBound(numbers)
            For k = LBound(numbers) To UBound(numbers)
                combo = numbers(i) & numbers(j) & numbers(k)
                ' 找到比当前组合大的第一个元素位置，插入到其前面
                For idx = 1 To combinations.Count
                    If combo < combinations(idx) Then
                        combinations.Add combo, Before:=idx
                        GoTo SkipToNextCombo
                    End If
                Next idx
                ' 如果组合比所有元素都大，添加到末尾
                combinations.Add combo
SkipToNextCombo:
            Next k
        Next j
    Next i

    ' 输出结果
    row = 2
    For Each combo In combinations
        Cells(row, 1).Value = combo
        row = row + 1
    Next combo
End Sub

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