VBA sort Two-digit array

Question

I want to sort below Two-digit array by VBA code

A 1
B 2
A 1
C 3

or below:

1 A
2 B
1 A
3 C

I have tried to sort them by Dictionary, but, Dictionary is not allowed to insert duplate key.

Is there any want to sort above array by number 1,2,3

What kind of array? How have you declared it? Does this asnwer your question: [Sorting a multidimensionnal array in VBA](https://stackoverflow.com/questions/4873182/sorting-a-multidimensionnal-array-in-vba) — Maciej Los, Jun 17 '21 at 13:52
@MaciejLos I have not declared, I just have such data, I want to order them, and I think I need array. If there is any way to achieve without array, that's ok. — Edward, Jun 17 '21 at 13:55
You can use [Range.Sort](https://learn.microsoft.com/en-us/office/vba/api/excel.range.sort) method. — Maciej Los, Jun 17 '21 at 13:57
Take a look at my first comment. There's a link to similar question and they say that the best way (the easiest one) is to use Excel sheet. ;) — Maciej Los, Jun 17 '21 at 18:27

score 0 · Answer 1 · answered May 25 '22 at 16:49

I made this some time ago, it might help.

Function ArraySorter(ByVal RecArray As Variant, Optional ByVal RefCol As Integer = 0) As Variant
Dim Menor As String
Dim NewArray() As Variant
Dim i As Double, j As Double
Dim menorIndex As Double
Dim NewArrayIndex() As Double
Dim UsedIndex() As Double
ReDim NewArrayIndex(UBound(RecArray, 2))
ReDim NewArray(UBound(RecArray), UBound(RecArray, 2))
For i = 0 To UBound(NewArrayIndex)
    NewArrayIndex(i) = -1
Next i
UsedIndex = NewArrayIndex
For i = 0 To UBound(RecArray, 2)
    Menor = ""
    menorIndex = -1
    For j = 0 To UBound(RecArray, 2)
        If UsedIndex(j) = -1 Then
            If Menor = "" Then
                Menor = RecArray(RefCol, j)
                menorIndex = j
            Else
                If RecArray(RefCol, j) < Menor Then
                    Menor = RecArray(RefCol, j)
                    menorIndex = j
                End If
            End If
        End If
    Next j
    UsedIndex(menorIndex) = 1
    NewArrayIndex(i) = menorIndex
Next i
For i = 0 To UBound(NewArrayIndex)
    For j = 0 To UBound(NewArray)
        NewArray(j, i) = RecArray(j, NewArrayIndex(i))
    Next j
Next i
ArraySorter = NewArray
End Function

If you have something like:

Function testArraySorter()
Dim myArr() As Variant

ReDim myArr(1, 3)

myArr(0, 0) = "A"
myArr(0, 1) = "B"
myArr(0, 2) = "A"
myArr(0, 3) = "C"

myArr(1, 0) = 1
myArr(1, 1) = 2
myArr(1, 2) = 1
myArr(1, 3) = 3

myArr = ArraySorter(myArr)

For i = 0 To UBound(myArr, 2)
    Debug.Print myArr(0, i), myArr(1, i)
Next i

End Function

you'll get this in your immediate verification :

A 1 
A 1 
B 2 
C 3

If you need to sort based in two or more columns, you could add a dummy column into your array, concatenate the criteria columns into it and then set this dummy column as RefCol: myArr = ArraySorter(myArr, addedColNumberHere).

Hope this helps.

VBA sort Two-digit array

1 Answers1