複数列に自由にキーを設定するVBA。Sort条件設定のコード

vbanarabiretukieyecatch

エクセルVBA 並び替えで複数の列に対応できるようにVBAコードを設計します。
コードエラーを起こし易い部分ですので、そのエラー対応も考慮しておきます。

こんにちは　じゅんぱ店長　(＠junpa33)　です。

VBAを使って、エクセルのセルデータの並び替えを行うツールを作成しています。

今回から並び替えのキー項目を扱うVBAコードについて説明をしていきます。

並び替えのVBAコードの本体部分の内容となります。

説明する並び替えのVBAコードについてですが、Sortオブジェクトを使用した内容となります。以前からあるSortメソッドでは並び替え機能の面で劣っているからです。

当然、エクセル標準の並び替えツールも2007以降はSortオブジェクトに移行しています。

並び替えマクロ　記事階層

この記事番号は6番です。

複数の列キーの並び替えに使用する設定項目と注意

並び替えを行なうための設定に必要な条件はたくさんあります。

詳しくは、こちらの記事を参考にしてください。

vbasorteyecatch データの並び替え VBA新旧のSortを実データで実証

条件を省略できるものは省略して、ここでのVBAコード作成に必要なことを列記すると、このようになります。

  1. 　並び替え条件を一旦クリアすること
  2. 　並び替えの列キーを指定すること
  3. 　並び順を指定すること
  4. 　ユーザー設定リストを利用すること
  5. 　文字列と数値の並び替え区別をすること
  6. 　並び替え範囲を指定すること
  7. 　先頭行の種別を指定すること
  8. 　並び替えを実行を指示すること

この中で列キーについての条件に直接的にかかわってくるのが、②と③と④と⑤になります。

複数の列に並び替えを適用する場合は、記述順でキーが優先されることになります。

また同時に、複数記述が原因で発生するエラーにも対応しておかなければなりません。

キー項目を使用して並び替えを行なう、このVBAコード部分は、一番、エラー発生が起こりやすい部分にもなっています。

並び替え条件を変数化して指定している今回の場合は特に、

条件が「空」「空白」「0」「Nothing」など、条件が何もない状態で動かしたときには、そのままでは高確率でエラーストップが起こります。

今回作成しているコード文は、列並び替えを3列まで可能にしていますが、それを1列だけの並び替えとして実行すると、「2列目3列目がない」のでエラーが発生して停止してしまいます。

回避するためには、On Errorのコードでエラー停止を防止することになりますが、するとエラーの状態が見えなくなってしまいます。

利用者が、並び替え条件を入力して実行ボタンをクリックしても、「ウンともスンとも」言わない状態で、並び替えが全く出来ないことも起こりえます。

ですのでこの対策として、設計側として、何のエラーが発生しているかをMsgBoxなどで利用者側に伝える必要があるということです。

目次へ戻る

列キーでの並び替えに直接かかわるVBAコード

列キーの並び替えを指定する部分のVBAコードはこちらです。

ユーザー設定リストについては詳細の説明は、別記事で改めて行います。

列キーで並び替え
VBA
        On Error Resume Next
            ws.Sort.SortFields.Clear
            If CusOdr1 = "ON" Then
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddA), _
                Order:=Stream1, CustomOrder:="""" & CusOdr1V & """", _
                        DataOption:=xlSortNormal
            Else
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddA), _
                Order:=Stream1
            End If
            If CusOdr2 = "ON" Then
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddB), _
                Order:=Stream2, CustomOrder:="""" & CusOdr2V & """", _
                        DataOption:=xlSortNormal
            Else
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddB), _
                Order:=Stream2
            End If
            If CusOdr3 = "ON" Then
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddC), _
                Order:=Stream3, CustomOrder:="""" & CusOdr3V & """", _
                        DataOption:=xlSortNormal
            Else
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddC), _
                Order:=Stream3
            End If
  • 　On Error Resume Next は必ず必要です。指定する列キーを3番目まで全て指定せずに並び替え実行すると指定していない部分のVBAコードでエラーが発生します。（2つ指定なら3つ目のコードでエラーが発生）　そのエラー発生を受け流しする必要があります。
  • 　If CusOdr = ” ON ” Then は「ユーザー設定リストを使用する」場合に実行されるコードを指定しています。
  • 　CustomOrder:=”””” & CusOdr V & “””” は外部のユーザー定義リストから取得した文字列データを指定しています。
  • 　ws.Range(KeyAdd) でユーザーフォーム「ソートナビゲーター」で利用者から取得したキー列を指定しています。
  • 　Order:= Stream はユーザーフォーム「ソートナビゲーター」で利用者から取得した並び替え情報の昇順・降順を指定しています。

基本豆知識　参考まで

ここまで、読まれて「あれ？」と思われた方もおられると思います。

実は、Range(KeyAdd)という記述方法はあまり行わない表記方法です。

KeyAddはユーザーフォームで取得した絶対参照のセル番地です。

何かと扱いづらい絶対参照ですが、相対参照と同様に、Rangeプロパティは因数として値をそのまま受け付けてくれます。

要は例えば「Range(“$B$15”)」の記述方法でも「セルB15」を認識してくれるということです。

ですので絶対参照から行列を取り出したい場合は、シンプルに

行の場合・・・Range(“$B$15”).Row　 　’　15

列の場合・・・Range(“$B$15”).Column　’　2

でOKということです。

あまり解説書や検索で調べても載っていませんので、参考までに

目次へ戻る

列キー並び替えのためのVBAコード全体

列キー並び替えの全コードです。

  • 　Clear　最初に並び替え条件を白紙に戻します。
  • 　SSRange　はユーザーフォーム「ソートナビゲーター」で利用者が選んだ並べ替え範囲パターンです。
  • 　Head　は並び順を指定しています。
  • 　Apply　は並び替えの実行するメソッドです。
VBA
Sub Sort実行()
        On Error Resume Next
            ws.Sort.SortFields.Clear
            If CusOdr1 = "ON" Then
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddA), _
                Order:=Stream1, CustomOrder:="""" & CusOdr1V & """", _
                        DataOption:=xlSortNormal
            Else
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddA), _
                Order:=Stream1
            End If
            If CusOdr2 = "ON" Then
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddB), _
                Order:=Stream2, CustomOrder:="""" & CusOdr2V & """", _
                        DataOption:=xlSortNormal
            Else
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddB), _
                Order:=Stream2
            End If
            If CusOdr3 = "ON" Then
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddC), _
                Order:=Stream3, CustomOrder:="""" & CusOdr3V & """", _
                        DataOption:=xlSortNormal
            Else
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddC), _
                Order:=Stream3
            End If
        With ws.Sort
            .SetRange SSRange
            .Header = Head
            .Apply
        End With
        Cells(SSRange.Rows.Count, SSRange.Columns.Count). _
            Offset(1, 1).Select
        MsgBox "並び替えが完了しました。"
        With ソートナビゲーター
            .並び替え列1セル.Value = ""
            .並び替え列2セル.Value = ""
            .並び替え列3セル.Value = ""
        End With
        On Error GoTo 0
End Sub
目次へ戻る

並び替えを複数の列に適用するVBAコードのまとめ

エクセルの標準の並び替えツールは、列の並び替えを複数列、条件に加えても全く気になりませんが、

今回作成しているような並び替えツールをVBAで作る場合、使用できるキー項目の数を想定して準備しておかなければなりません。

設計として、列キーを3つまで指定できるようにしています。

例えば、キー１は「都道府県」、キー2は「市区」キー3は「町村」というイメージです。

そして、列キーをフルに使用しない場合、コード処理が上位から順に進んで行って、

使用しない列キーの並び替えコードが実行されるところでエラーが発生します。

このエラー停止を行わないようにするVBAを組むことが必要です。また同時に、見えなくなったエラー原因を表示できる仕組みも必要になります。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです.

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

＜記事内容についての告知＞VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください 万一データ破損等の損害が発生しても当方では責任は負いません。
目次へ戻る

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

af_banner01

Dstyle web

dstyleweb_logo
dstyle_320x50-min

前の記事

vbanarabihanieyecatch
セル範囲内の空白行や結合にも対応したSortオブジェクトVBA

次の記事

複数の背景色キーを指定できるVBAコードの組み立て
vbanarabiirokieyecatch