VBA：基于另一个数组排序数组 [英] VBA: Sort array based on another array

问题描述

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

  Dim Name as Variant，Street as Variant 
 Name = array（B，C，D，A，E）
 Street = array 1，2，3，4，5）
  

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

 （A，B，C，D，E）
  

< >然后我需要排序 Street 数组，所以它得到相应的顺序，即

 （4，1，2，3，5）
  

在VBA中最有效的方法是什么？

请注意：我知道如何在VBA中正常排序一个单独的数组，我只是寻找一些基于另一个数组排序数组的答案。谢谢。

解决方案

这似乎是你想要的：

<$ p
$ b名称=数组（B，C，D，A， E）
Street = Array（1，2，3，4，5）

调用sort2（Name（），Street

每个街道
MsgBox s
下一步s
结束子


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 =（嗨 - 低+ 1）\ 2
尽管J> 0
对于I =低至高 - J
如果键（I）>键（I + J）然后
Temp =键（I）
键（I）=键（I + J）
键（I + J）= Temp
Temp =其他（I）
其他（I）=其他（I + J）
其他（I + J）= Temp
结束If
下一个I
对于I =嗨 - J到低步-1
如果键（I）>键（I + J）然后
Temp =键（I）
键（I）=键（I + J）
键（I + J）= Temp
Temp =其他（I）
其他（I）=其他（I + J）
其他（I + J）=临时
结束如果
下一个I
J = J \ 2
循环
End Sub

编辑＃1：

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

  Temp = other2（I）
 other2（I）= other2（I + J）
 other2（I + J）= Temp 
  

在排序例程中的位置。

I have several very long arrays which needs to be sorted alphabetically based on user action, but for simplicity I'll use the following example:

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

After the user clicks the sorting button, the Name array now has the following order

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

and I then need to sort the Street array, so it gets the corresponding order, i.e.

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

What is the most efficient way to do this in VBA?

Please notice: I know how to sort an individual array normally in VBA, I'm only looking for answers which involve sorting an array based on another array. Thanks.

解决方案

This appears to do what you want:

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

EDIT#1:

To add more arrays to the mix, just include them in the header and insert more lines like:

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

in both places in the sort routine.

这篇关于VBA：基于另一个数组排序数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！