Excelで表に対して、VBAでカスタムなソートを実行したい

Question

前提・実現したいこと

Excelで並べ替えをしたい。

列1	列2	列3
100	A0001-1	あ
100	A0002-1	お
100	A0011-1	か
100	B0001-1	き
100	B0001-1-1	き
101	A0001-1	い
101	A0001-2	え
101	A0001-12	か

というような感じで並べ変えをしたい。

第１ソートキーは列１で第２ソートキーは列２です。
ただし、ソートはハイフン区切りで数値化できるものは数値化してソートです。

標準機能のソートでは、実現できないカスタムなソートを
どのように実現すればいいのかという質問です。

プログラミング言語によっては、カスタムなCompare関数を定義して
ソートを実行するとかいう感じです。

ソート用の比較関数は、以下のようなものを作成済みと考えてください。

Public Function myCompare(a1 As String, a2 As String, b1 As String, b2 As String) As Long
    ' aとbの比較を行って、aの方が小さい場合は-1、同じ場合は0、aの方が大きい場合は1を返す
    ' 実装は略
End Function

また、ソート処理を実装するマクロとソート処理対象のエクセルは別ファイルを想定しています。

どのような実装を行えば、これが実現できるのか検討しています。
何かいい方法があれば教えてください。

試してみようとしていること

今考えているマクロの内容は

１．選択範囲の表の最終列のもう一つ右の列に、順位を数値で設定
２．順位の列でソート
３．順位の列を削除

という感じなのを考えています。

補足情報（FW/ツールのバージョンなど）

Office 365

追記

ソート用の列を追加するという上述の案の方針で
以下のような実装をしてみました。
このままではソートのアルゴリズムが遅いので、早いソートのアルゴリズムにすれば
少しは早くなりました。

Public Sub CallSort(rRange As Range)
    Dim tSheet As Worksheet
    Set tSheet = rRange.Parent
    Dim tRange As Range
    Set tRange = rRange
    
    tRange.Columns(tRange.Columns.Count + 1).Insert Shift:=xlToRight

    Dim tCol1 As Range
    Dim tCol2 As Range
    Set tCol1 = tRange.Columns(1)
    Set tCol2 = tRange.Columns(2)
    
    Dim tV1 As Variant
    Dim tV2 As Variant
    
    Dim tR1 As Long
    Dim tR2 As Long
    Dim tC1 As Long
    Dim tC2 As Long
    
    
    tR1 = tRange.Row
    tR2 = tR1 + tRange.Rows.Count - 1
    tC1 = tRange.Column
    tC2 = tC1 + tRange.Columns.Count
    
    Set tRange = tSheet.Range(tSheet.Cells(tR1, tC1), tSheet.Cells(tR2, tC2))
            
    tV1 = tCol1.Value
    tV2 = tCol2.Value
    
    ReDim tOrder(1 To UBound(tV1, 1))
    ReDim tOrder2(1 To UBound(tV1, 1))
    For tR = 1 To UBound(tV1, 1)
        tOrder(tR) = tR
    Next
    
    MySort tOrder, tV1, tV2

    For tR = 1 To UBound(tV1, 1)
        tOrder2(tOrder(tR)) = tR
    Next
    
    tRange.Columns(tRange.Columns.Count).Value = WorksheetFunction.Transpose(tOrder2)
    
    tSheet.Sort.SortFields.Clear
    tSheet.Sort.SortFields.Add2 Key:=tRange.Columns(tRange.Columns.Count), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With tSheet.Sort
        .SetRange tRange
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    tRange.Columns(tRange.Columns.Count).Delete Shift:=xlToLeft

End Sub

Public Sub MySort(r_AryRow As Variant, r_V1 As Variant, r_V2 As Variant)
    Dim i As Long
    Dim j As Long
    Dim tTmp As Variant
    For i = UBound(r_AryRow) To LBound(r_AryRow) + 1 Step -1
        For j = LBound(r_AryRow) To i - 1
            Dim rtn As Long
            rtn = Compare(r_V1(r_AryRow(j), 1), r_V2(r_AryRow(j), 1), r_V1(r_AryRow(j + 1), 1), r_V2(r_AryRow(j + 1), 1))
            If rtn > 0 Then
                tTmp = r_AryRow(j)
                r_AryRow(j) = r_AryRow(j + 1)
                r_AryRow(j + 1) = tTmp
            End If
        Next
    Next
End Sub

Answer 1

作業列を使ってそこでソートするという考え方でいいと思います。

いろいろな方法が考えられますが、列２をエクセルの「区切り位置」機能で分割して作業列に出力して、ソートするという方法で作成してみました。

マクロ実装ブックの標準モジュール

Sub MySort(Tbl As Range)
    Dim tmpTbl  As Range
    Set tmpTbl = Tbl.Resize(Tbl.Rows.Count, 5)

    '2列目をハイフンで分割して4列,5列に出力
    tmpTbl.Columns(2).TextToColumns _
            Destination:=tmpTbl.Columns(4), Other:=True, OtherChar:="-", _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 9))
    
    '1列,4列,5列を基準にソート
    With tmpTbl.Parent.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=tmpTbl.Columns(1), Order:=xlAscending
        .SortFields.Add2 Key:=tmpTbl.Columns(4), Order:=xlAscending
        .SortFields.Add2 Key:=tmpTbl.Columns(5), Order:=xlAscending
        .SetRange Range("A1:E9")
        .Header = xlYes
        .Apply
    End With
    '作業列(4,5列)を削除
    tmpTbl.Columns("D:E").Clear
End Sub

上記マクロの呼び出し例

MySort Workbooks("ソート対象ブック").Sheets(1).Cells(1,1).Currentregion

Comments

[xail2222]

回答ありがとうございます。
やはり、ソート用の列を追加するという方針になるでしょうか。

ただ、実際の比較のルールがもう少し複雑なのです。
そのため自作の比較関数を使って、ソートをしたかったので
その実装例を考えてみました。

ソートのアルゴリズムを自前で書かないといけないというのが
今時じゃないなと思ったりしています。

[hatena19]

残念ながら、VBAは今時の言語ではないですからね。(10年以上前に更新が止まっている)

検索すれば、VBAでのバブルソースとかクイックソートのサンプルコードは見つかるので、それの比較部分を自作の比較関数にすれば実装はできると思います。

作業列をうまく使えば複雑なソートでは可能だとは思います。
どちらが楽かは、プログラマーの経験、スキルによると思います。

[hatena19]

データーベース的観点でみると、ソートの条件が複雑になるというのは、データの正規化ができていないということになります。データを取得、入力した時点で列を分割しておく（正規化しておく）という発想も、処理のシンプル化、高速化に有効だと思います。

[xail2222]

ソートのアルゴリズムは、ヒープソートにしました。
サンプルとして受領した２万件のデータで１５秒だったので
まぁ。運用は可能かなと感じました。

ソートの条件を詳しくヒアリングしていくと、ハイフン区切りが何個あるのか不明だったり
ハイフン区切りで区切った後も、99AA11と言うようなデータがあるようで
そこも数値、数値以外で、区切って要素ずつで比較する必要があるということがわかりました。
あと甲乙の順番も出来たらいいな。とか

正確に、どのような内容が入っているのかはわからないけれど
上記のルールでソートしてほしい。という要望でしたので
自作の比較関数以外では、私には出来ませんでした。