arrays - VBA:根据另一个数组对数组进行排序

Question

我有几个非常长的数组，需要根据用户操作按字母顺序排序，但为了简单起见，我将使用以下示例:

Dim Name as Variant, Street as Variant
Name = array("B", "C", "D", "A", "E")
Street = array("1", "2", "3", "4", "5")

用户单击排序按钮后，Name 数组现在具有以下顺序

("A", "B", "C", "D", "E")

然后我需要对 Street 数组进行排序，以便它获得相应的顺序，即

("4", "1", "2", "3", "5")

在 VBA 中执行此操作最有效的方法是什么？

请注意:我知道如何在 VBA 中正常对单个数组进行排序，我只是在寻找涉及基于另一个数组对数组进行排序的答案。谢谢。

Answer 1

这似乎可以满足您的要求:

Sub MAIN()
    Dim Name(), Street()
    Name = Array("B", "C", "D", "A", "E")
    Street = Array("1", "2", "3", "4", "5")

    Call sort2(Name(), Street())

    For Each s In Street
        MsgBox s
    Next s
End Sub


Sub sort2(key() As Variant, other() As Variant)
Dim I As Long, J As Long, Low As Long
Dim Hi As Long, Temp As Variant
    Low = LBound(key)
    Hi = UBound(key)

    J = (Hi - Low + 1) \ 2
    Do While J > 0
        For I = Low To Hi - J
          If key(I) > key(I + J) Then
            Temp = key(I)
            key(I) = key(I + J)
            key(I + J) = Temp
            Temp = other(I)
            other(I) = other(I + J)
            other(I + J) = Temp
          End If
        Next I
        For I = Hi - J To Low Step -1
          If key(I) > key(I + J) Then
            Temp = key(I)
            key(I) = key(I + J)
            key(I + J) = Temp
            Temp = other(I)
            other(I) = other(I + J)
            other(I + J) = Temp
          End If
        Next I
        J = J \ 2
    Loop
End Sub

编辑#1:

要添加更多数组，只需将它们包含在标题中并插入更多行，例如:

            Temp = other2(I)
            other2(I) = other2(I + J)
            other2(I + J) = Temp

在排序例程中的两个位置。