ＶＢＡ ユーザーフォーム リストボックスのソート

Question

平素は皆様にご教授いただき、まことにありがとうございます。
リストボックス内で、時間で昇順にソートをかけたいと思い、サイトを巡回して似たようなものを見つけたので、少し変えて作ってみました。
結果として、エラーは出ずに、昇順ソートしてくれていますが、、以下のような問題に直面しています、、画像もお付けいたします。
１、2列目から1列目に寄ってしまう
２、ソートすると、1桁時刻と2桁時刻をわけてしまう
という感じです。
1次元配列（専門用語の理解とか使い方間違ってると思います。。。以下同じ）でなくて、2次元配　
列で放り込む方法ってあるんでしょうか、、
よろしければ、ご教授いただければ幸いです。。

Private Sub CommandButton12_Click()
　Dim column1 As String
　 Dim column2 As String
　　 Dim i As Long

With CreateObject("System.Collections.ArrayList")
For i = 0 To ListBox1.ListCount - 1
column1 = ListBox1.List(i, 0)
column2 = ListBox1.List(i, 1)

.Add column2

Next i

.Sort

ListBox1.Clear

For i = 0 To .Count - 1

ListBox1.AddItem .Item(i)

Next i
.Clear
End With

End Sub

add colimn2を入れてみましたが、見事に時刻と数字が一直線に並びました、
おそらくですが、、時刻が文字列なので、数値に変えて格納して、2列で格納出来たら解決するのかなと、、
よろしければ、ご教授いただけませんでしょうか、よろしくお願いいたします。

Comments

[meg_]

「8:20」ではなく「08:20」にしてはどうでしょうか？

[kohan]

ご回答ありがとうございます。あ、、そうですね、、、、やってみたいと思います。。

あと、2列で格納できるかどうかですが、、この点については解決可能なのでしょうか？
arrylistには、1次元でしか格納できないのでしょうか、、

[meg_]

「2列で格納」の意味がちょっと分かりませんでした。（”何を”2列にして”何に”格納するんでしょう？）

[kohan]

あ、失礼いたしました。
リストボックスにある
1列目　「氏名」のデータ
2列目の「時刻」のデータ
を
ArrayList に格納できるかということです、、

With CreateObject("System.Collections.ArrayList")
For i = 0 To ListBox1.ListCount - 1
column1 = ListBox1.List(i, 0)
column2 = ListBox1.List(i, 1)
.Add column1
.Add column2
とすると、ソートをかけると、結果として、1列縦隊で出力するので、なにか他に格納の方法があれば、、、ということでの質問です。

[meg_]

tmpが2次元配列だとすると、ListBox1.List = tmp で初期化できるようです。

[kohan]

すいません、、たびたびです、、構文としては、どう書けばいいのでしょうか、、よろしければ、教えていただけませんか、

[meg_]

下記リンクに具体例があります。（最後の方）
http://officetanaka.net/excel/vba/tips/tips158.htm

[kohan]

情報をいただきまして、まことにありがとうございます。
temp とはそういうことなのですね、、といいつつ、まだ理解できていませんが、、

Answer 1

System.Collections.ArrayList は1列しか格納できません。

時刻と氏名を文字列として連結して格納することになります。
文字列でのソートは先頭からが基準になりますので、
時刻が前にくるようにして、時刻の桁数はそろえて格納します。

Private Sub UserForm_Initialize()
    With Me.ListBox1
        .ColumnCount = 2
        .AddItem "AAA"
        .List(.ListCount - 1, 1) = "10:00"
        .AddItem "BBB"
        .List(.ListCount - 1, 1) = "08:20"
        .AddItem "CCC"
        .List(.ListCount - 1, 1) = "09:00"
        .AddItem "DDD"
        .List(.ListCount - 1, 1) = "09:05"
        .AddItem "EEE"
        .List(.ListCount - 1, 1) = "09:55"
    End With
End Sub

Private Sub CommandButton12_Click()
    Dim column1 As String
    Dim column2 As String
    Dim i As Long

    With CreateObject("System.Collections.ArrayList")
        For i = 0 To ListBox1.ListCount - 1
            column1 = ListBox1.List(i, 0)
            column2 = ListBox1.List(i, 1)
            .Add column2 & ";" & column1 '"10:00;AAA" という文字列で格納。
        Next i

        .Sort

        Dim ary
        For i = 0 To .Count - 1
            ary = Split(.Item(i), ";") '; で配列に分割
            Debug.Print Join(ary)
            ListBox1.List(i, 0) = ary(1)
            ListBox1.List(i, 1) = ary(0)
        Next i
        .Clear
    End With

End Sub

別案

エクセルの機能を活用しましょう。
まずシート上に見出しとデータを入力します。
入力したセル範囲を選択して「テーブルとして書式設定」でテーブル化します。

リボンの「数式」→「名前の管理」をクリックすると表の範囲が「テーブル1」という名前が自動でついているのが確認できます。ここでテーブル名は内容を表す分かり安いものにしておいてもいいでしょう。
シート名は「TableSheet」としておきます。

リストボックスのRowSourceプロパティにこのテーブル名を設定しておくとテーブルと関連付けられてテーブルのデータが表示されてます。
テーブルを並べ替えたり、フィルターをかけるとリストボックスにも反映されます。

Private Sub UserForm_Initialize()
    With Me.ListBox1
        .ColumnCount = 2
        .ColumnHeads = True
        .RowSource = "TableSheet!テーブル1"
    End With
End Sub

Private Sub CommandButton1_Click()
    With Worksheets("TableSheet")
        .Range("TableSheet").Sort _
                Key1:=.Range("テーブル1[時刻]"), _
                Order1:=xlAscending, _
                Header:=xlYes
    End With
End Sub

Comments

[kohan]

更には、このような別の構文まで用意してくださり、まことにありがとうございます。
テーブルにして、こういう方法もあるのですね、、ぜひ、吸収させていただきます。

Answer 2

作業用のワークシート[SortWork]を追加して、次のコードでいかがでしょうか。

Private Sub CommandButton12_Click()
    Dim wshSWK As Worksheet
    Dim lngListCount As Long
    
    Set wshSWK = Worksheets("SortWork")
    lngListCount = ListBox1.ListCount
    
    wshSWK.Range("A1").CurrentRegion.ClearContents
    wshSWK.Range("A1:B" & lngListCount) = ListBox1.List
    wshSWK.Range("C1:C" & lngListCount).FormulaR1C1 = "=RC[-2]"
    wshSWK.Range("D1:D" & lngListCount).FormulaR1C1 = "=TEXT(RC[-2],""h:mm"")"
    
    With wshSWK.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("B1")
        .SetRange Range("A1").CurrentRegion
        .Apply
    End With
        
    ListBox1.List = wshSWK.Range("C1:D" & lngListCount).Value
    
    Set wshSWK = Nothing
    
End Sub