カテゴリー: ExcelVBA

コードコピー

Sub zzz配列並替(ByRef zzh指定配列 As Variant, Optional zzh降順 As Boolean = False)
    Application.ScreenUpdating = False
    Dim zzループ用 As Variant, zz配列要素数 As Long
    Dim zzCell As Range: Set zzCell = Workbooks.Add.Worksheets(1).Range("A1")
    For Each zzループ用 In zzh指定配列
        zzCell = zzループ用
        Set zzCell = zzCell.Offset(1, 0)
    Next zzループ用
    Set zzCell = zzCell.Offset(-1, 0)
    If zzh降順 = False Then
        Range(zzCell.Parent.Range("A1"), zzCell).Sort zzCell, xlAscending
    Else
        Range(zzCell.Parent.Range("A1"), zzCell).Sort zzCell, xlDescending
    End If
    zz配列要素数 = LBound(zzh指定配列)
    For Each zzループ用 In Range(zzCell.Parent.Range("A1"), zzCell)
        zzh指定配列(zz配列要素数) = zzループ用
        zz配列要素数 = zz配列要素数 + 1
    Next zzループ用
    zzCell.Parent.Parent.Close False
    Set zzCell = Nothing
    Application.ScreenUpdating = True
End Sub

配列データはそのままでは並び替えを行えません
一度分解して、配列を再作成する必要があります
配列を昇順・降順の指定をして並び替えを実行する関数です

この関数はワークシートのソート機能を利用しています
ワークシートを新規作成してそこにデータを一度出力して並び替えをしています
なので、ワークシートを作成する処理時間が余分にはかかりますが、なんせデータ型への対応が柔軟なので汎用処理に向いています

この関数を使用する際に注意点があります
この関数では配列データをそのままセルに入力して取得しなおすだけの処理でありデータの型を認識していません
そのため、文字列データとしてある数値の場合に頭文字の0が抜けることがあります
「0123」→「123」という形に振り替えられます
これはExcelの仕様上の部分でもありますが、型を事前に取得して文字列として扱えば対応は可能です
もし、そういったデータを扱う際は少し改修して使用する必要があります
これに関しては、あまり遭遇しなかったので必要性が高そうなら改修コードを掲載しようとは思います

「zzh指定配列」は、並び替えを実行したい配列データを指定します
必須項目でここに指定した配列を加工します

「zzh降順」は、並び替えの順序の指定です、省略可能で省略時はFalseになります
Falseの場合は昇順に、Trueの場合は降順に並び替えされます

コードの使用方法

コードコピー

Call zzz配列並替(セル配列)

SubプロシージャなのでCallステートメントを使用して処理を呼び出します
その際引数には指定配列を指定します、2つ目の引数は省略しているのでこのコードの場合は配列は昇順に並び替えが実行されます

加工を行う前の数値で作成された配列データです
これを関数を使用して並び替えを行います

上記のコードを実行して配列データが昇順に並び替えられた状態のデータです
昇順なので数字の小さいものから順番に並んでいることが確認できます

ただ、これだけならわざわざワークシート機能を利用しなくても出来ます
そのほうが遥かに高速に処理できます
それは数値を加工するからです、これを文字列も含むとなるとやっかいです
まして同じ関数内にそれを組み込むとなると大変です

画像を確認してください、特に3列目の型の部分です
Valiant型ですが、内部的にはバラバラの型になっていることが分かります

これを数値の並び替えでは対応できませんし、数値専用・文字列専用の関数を別に作成するのも使用するのも面倒なのでワークシート機能を利用するのが結果楽になります

この画像は関数を使用して昇順に並び替えた状態のデータです
型ごとに昇順に並んでいることが確認できます

これは単にワークシートのソート機能を1回実行しているだけで可能です
この並び替え処理をVBAで書いたら、いったいどれだけのコードになるのか分かりません
Microsoftの開発者さんの凄まじさが良くわかります

関数の2つ目の引数にTrueを指定して降順に並び替えた状態です
こちらも型ごとにしっかり並び変わっていることが確認できます

コードコピー

Call zzz配列並替(セル配列, True)

こんな感じで2つ目の引数は省略せずにTrueを指定してください

こんな無茶苦茶な配列データなんか、ねえよ！ってのも聞こえてきますが、というか自分自身がなにより思いますが、このソート機能の凄まじい処理能力のためなら、少しの処理時間は犠牲にしてもええんちゃいますか？だめですか？

自分の環境では配列データ数が1000で0.2秒ほど、10000で0.5秒ほどです
処理がこれだけではなく組み込んで、他の処理もあることを考えても実用レベルかなとは思います

コード解説

Sub zzz配列並替(ByRef zzh指定配列 As Variant, Optional zzh降順 As Boolean = False)

～～　中略　～～

End Sub

関数のプロシージャ範囲です
この関数は引数の配列データを加工する関数なのでSubプロシージャで作成しています

引数は2つあります

「ByRef zzh指定配列 As Variant」は、関数で加工を行う配列の引数です
この配列データを加工するのでByRefキーワードを使用しています
これによりこの関数で行った加工を呼び出し元に戻すことが出来ます
また配列データなのでValiant型です

「Optional zzh降順 As Boolean = False」は、関数で使用する並び替え順序の指定を行う引数です
この引数はOptionalキーワードを使用していますので省略可能です
省略した場合はFalseが指定されます
Falseを指定した場合は昇順に、Trueを指定した場合は降順に並び替えます
フラグなのでBoolean型です

    Application.ScreenUpdating = False

この関数はすでに触れましたが、ワークブックを新規作成します
その処理上どうしてもその新規作成したものにフォーカスが移ってしまうので、画面遷移が発生しますので、処理速度向上のためにここで画面遷移を無効にします

    Dim zzループ用 As Variant, zz配列要素数 As Long

使用する変数の宣言箇所です、使用する変数は3つあります

「zzループ用 As Variant」は、指定した配列データをForループを実行する際に代入させる変数です
配列を代入させるのでValiant型になっています

「zz配列要素数 As Long」は、配列の要素数を指定する際の整数変数です
並び替えを実行した後に、配列を更新する際に使用します

    Dim zzCell As Range: Set zzCell = Workbooks.Add.Worksheets(1).Range("A1")

3つ目の変数は、宣言と同時に代入しているので1行が長くなっています

「zzCell As Range」は、新規作成したワークブックのA1セルを代入する変数です
このセルに配列データを入力していきます
新規作成した時点でのフォーカス移動は仕方ありませんが、ここで変数に代入させておくことで選択をしなくてもコードが分かり易くなるので変数を利用します

その後の後半部分が、ワークブックを新規作成してそのシートとセルを一括で指定しています
Addメソッドで完結してから、セルを代入させることもできますが、その場合ブックの指定がActivebookオブジェクトやらを使用する必要があるので、この1行が理解できるならこの方がスマートなコードになります

    For Each zzループ用 In zzh指定配列
        zzCell = zzループ用
        Set zzCell = zzCell.Offset(1, 0)
    Next zzループ用

指定した配列データを1要素ずつ全てをループしています
なので多次元配列であっても対応は可能ですが、ここから1次元配列として生まれ変わってしまいます

「zzCell = zzループ用」の箇所でセルに配列データを入力しています

「Set zzCell = zzCell.Offset(1, 0)」は1つ下のセルにセル変数を移動させています
これで改行を行っているような形です

    Set zzCell = zzCell.Offset(-1, 0)

ループが終了したら、セル変数を1つ上に移動させます
というのも、ここまででデータ全てを入力したあと改行しているので、データからはみ出した空白のセルが参照されている状態です

この後でデータの最下部を必要とするので、そこにセル変数を移動させておく必要があります

    If zzh降順 = False Then
        Range(zzCell.Parent.Range("A1"), zzCell).Sort zzCell, xlAscending
    Else
        Range(zzCell.Parent.Range("A1"), zzCell).Sort zzCell, xlDescending
    End If

ここで並び替えを実行しています
ワークシートのソート機能を実行しているだけです

「If zzh降順 = False Then」の部分で分かるように、引数の変数の指定によって処理を分岐させています

処理自体は上記にあるようにソートをかけているだけです
その際、セル範囲を指定して実行していますが、他にデータがある訳でもないのでどっちでもええっちゃええですね

    zz配列要素数 = LBound(zzh指定配列)

配列の下限数を取得しています

配列の個数は変わらないので要素数を再定義する必要はありません
ですが下限値は0とは限らないため、元々の下限値に合わせておく必要があります
下限値が合えば個数が変わらないため上限値も同時に確定するので、そちらは特に取得する必要はありません

    For Each zzループ用 In Range(zzCell.Parent.Range("A1"), zzCell)
        zzh指定配列(zz配列要素数) = zzループ用
        zz配列要素数 = zz配列要素数 + 1
    Next zzループ用

この箇所では並び替えを実行したセル範囲を1セルずつループにより元配列データに上書きをしていきます

その際の要素数の指定は先ほど取得していた下限値から始めていきます
元の配列データの中身に上書きしたら、要素数を更新してループを行います

    zzCell.Parent.Parent.Close False

配列の加工が終了したら新規作成したワークブックを閉じます

ここまでではブックの取得は行っていません、セルの取得のみです
しかしそのセルは対象のブック内のオブジェクトです

「Parent」プロパティを使用することで上位のオブジェクトを指定できます
ここでは2回連続で指定されています
これは、1回目のParentがワークシートになります、2回目がその上位のオブジェクトとなりワークブックが指定されます

このプロパティをもう1回連結すると、Excelを指定することができます

ワークブックのCloseメソッドを実行してブックを閉じますが、セルを編集しているため保存確認が表示されてしまうのでそれを回避するために、メソッドの引数にFalseを指定します
この指定を行うと、ブックが保存済みのステータスになるので終了時に確認が表示されなくなります

    Set zzCell = Nothing
    Application.ScreenUpdating = True
End Sub

最後にセル変数の解放と画面遷移を無効にしていた設定を元の有効な設定に戻します

少し回りくどいかもしれませんが、数値でも文字列でも対応できる柔軟さを考えるとこんな感じでExcelの機能を有効利用した方がええよね～