Office TANAKA - Excel VBA Tips[Excel 2007のSortオブジェクト]

Add2メソッドについて(2019年1月追記)

2018年10月18日にExcel 2016は機能拡張されて、バージョン1810(ビルド 11001.20074)になりました。このとき実装された機能拡張のひとつに、"新しいデータ型"があります。追加されたのは「株式型」と「地理型」です。両者の型に関しては、下記Microsoftのページをご覧ください。

Excel データ型: 株価と地理

この「株式型」と「地理型」の追加に伴って、セルの並べ替えで条件を指定するSortFieldsコレクションに、新しくAdd2メソッドが追加されました。ちなみに、いままではAddメソッドでした。両者の働きに大きな違いはありませんが、Add2メソッドは、新しい「株式型」と「地理型」のサブフィールドを並べ替えることが可能です。Add2メソッドをヘルプで調べると、次のように記載されています。

この API には、地理やストックなどのデータ型からサブフィールドをソートするためのサポートが含まれています。データ型での並べ替えが不要な場合は、 Add メソッドを使用することもできます。

日本語に翻訳すると、次のような意味です。

Add2メソッドでは「株式型」と「地理型」のサブフィールドを並べ替えることができます。サブフィールドの並べ替えをしないのなら、今までのAddメソッドで十分です。ユーザーのみなさまに何の告知もせず、急に新しいメソッドを追加することでご迷惑をおかけしました。たいへん申し訳ありません。

重要なことは、ここからです。

機能拡張によって「株式型」と「地理型」が使用できるExcel 2016で"並べ替え"の操作をマクロ記録すると、Add2メソッドが記録されます。しかし、Excel 2013以前のバージョンや、Excel 2016であっても、機能拡張されていないExcel 2016では、Add2メソッドが存在しません。したがって、Add2メソッドを使ったマクロはエラーになります。

もし、並べ替えのマクロを、異なるバージョンのExcelで実行するようなときは、マクロ記録で記録されるAdd2メソッドではなく、Addメソッドを使ってください。"2"を消すだけでいいです。なお、本コンテンツは、Excel 2010の時代に作成しましたので、以下の解説はすべてAddメソッドで記述しています。

新しくなった並べ替え

セルを並べ替えるとき、Excel 2003まではRangeオブジェクトのSortメソッドを使いました。たとえば、次のように。

Sub Sample1()
    ''C列を基準に昇順で並べ替えます
    Range("A1:C10").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes
End Sub

同じ操作を、Excel 2007でマクロ記録すると、次のようなコードが記録されます。

Sub Macro1()
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:C10")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Excel 2007では、新しくSortオブジェクトが新設されました。また、Sortオブジェクト内にSortFieldオブジェクトというのもあります。ザックリ言うと、Sortオブジェクトは「並べ替えに関する」オブジェクトで、SortFieldオブジェクトは「並べ替えの条件に関する」オブジェクトです。手順としては、

まず、並べ替えの条件を指定する → SortFieldオブジェクト
次に、その条件で並べ替えを実行する → Sortオブジェクト

という感じです。マクロ記録で記録されたコードは、次のようになっています。

SortFieldオブジェクトに並べ替えの条件を指定する

SortFieldオブジェクトから見ていきましょう。まず、マクロ記録されたコードを、少し編集します。並べ替えたいデータは[Sheet1]に入力してありますが、これはアクティブシートです。そこで、次のようにします。

Sub Macro1()
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("C2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End Sub

SortFieldsコレクションは、SortFieldオブジェクトの集合体です。SortFieldオブジェクトは、1つの「並べ替え条件」に該当します。つまり、次のような関係です。もし3つの条件を指定したなら

新しい並べ替え条件(SortFieldオブジェクト)を設定する前に、それまでの並べ替え条件をクリアします。

    ActiveSheet.Sort.SortFields.Clear

クリアしないと、それまでの並べ替え条件に、新しい条件が追加されます。

次に、新しい並べ替え条件を指定します。ここで指定しているのは「C列を基準に昇順」という条件です。見やすくするため、引数の指定ごとに改行します。

    ActiveSheet.Sort.SortFields.Add _
        Key:=Range("C2"), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal

並べ替え条件として指定できる引数は次の通りです。

引数名	必須/省略	説明
Key	必須	並べ替えの基準セル
SortOn	省略可	並べ替えのタイプ
Order	省略可	並べ替えの順序
CustomOrder	省略可	ユーザー設定の並べ替え基準
DataOption	省略可	数値と文字列の並べ替え基準

引数「SortOn」は、Excel 2007で追加された引数です。何で並べ替えるかを指定します。引数「SortOn」には次の定数を指定できます。

定数	数値	意味	既定値
xlSortOnValues	0	セル内のデータで並べ替え	←これ
xlSortOnCellColor	1	セルの背景色で並べ替え
xlSortOnFontColor	2	セルの文字色で並べ替え
xlSortOnIcon	3	条件付き書式のアイコンで並べ替え

引数「SortOn」を省略すると、定数xlSortOnValues(数値で並べ替え)が指定されたものとみなします。

引数「Order」は、並べ替えの昇順/降順を指定します。引数「Order」には次の定数を指定できます。

定数	数値	意味	既定値
xlAscending	1	昇順	←これ
xlDescending	2	降順

引数「CustomOrder」は、ユーザー設定の並べ替え基準を使うとき、その基準を指定します。これは、Excel 2003までの引数「OrderCustom」と似ていますが、さらに便利になっています。

たとえばExcel 2003で、独自の基準でデータを並べ替えたいときは、次のようにします。ここでは「東京, 神奈川, 千葉」という並べ替え基準を指定するとします。

1.まず、ユーザー設定リストに「東京, 神奈川, 千葉」を登録します

2.データの並べ替えを実行します

3.[オプション]を開いて、1.で登録したユーザー設定リストを選択します

4.指定した基準で並べ替えが行われます

この操作をマクロ記録すると、次のようなコードが記録されます。

Sub Macro1()
    Range("A1:B10").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=13, MatchCase:=False, Orientation:=xlTopToBottom, _
        SortMethod:=xlPinYin, DataOption1:=xlSortNormal
End Sub

このように、Excel 2003まででユーザー設定の並べ替え基準を使うときは、まずユーザー設定リストに登録して、引数OrderCustomには「ユーザー設定リストの何番目」と指定します。もちろん、ユーザー設定リストに登録されていない基準で並べ替えることはできません。

対して、Excel 2007の引数「CustomOrder」は、独自の並べ替え基準を文字列で指定します。

    ActiveSheet.Sort.SortFields.Add _
        Key:=Range("A2"), _
        Order:=xlAscending, _
        CustomOrder:="東京,神奈川,千葉", _
        DataOption:=xlSortNormal

ユーザー設定リストに登録する必要はありません。並べ替えを実行するたびに、自由な並べ替え基準を指定できます。

引数「DataOption」は、並べ替えの基準となるデータに、数値と文字列が混在しているときの並べ替え方法を指定します。引数「DataOption」には次の定数を指定できます。

定数	数値	意味	既定値
xlSortNormal	0	数値と文字列を別々に並べ替えます	←これ
xlSortTextAsNumbers	1	文字列を数値とみなして並べ替えます

まぁ、これを指定することは少ないでしょうね。たとえば、同じ列に、数値の「100」「200」と文字列の「'100」「'200」が入力されているとき、この列を並べ替えた結果を「100」「200」「'100」「'200」とするのか、それとも「100」「'100」「200」「'200」とするのかを決める設定です。そもそも、Excelにこういう設定があるということすら、あまり知られていません。下のダイアログボックス、見たことないでしょ？

最も簡素な並べ替え基準

並べ替えの基準となるSortFieldオブジェクトを追加するとき、以下の引数を指定できますが

引数名	必須/省略	説明
Key	必須	並べ替えの基準セル
SortOn	省略可	並べ替えのタイプ
Order	省略可	並べ替えの順序
CustomOrder	省略可	ユーザー設定の並べ替え順序
DataOption	省略可	数値と文字列の並べ替え基準

もし、セルの背景色や条件付き書式のアイコンなどではなく、セル内のデータを基準に並べ替えるのなら、引数SortOnは省略できます。また、データを昇順で並べ替えるのなら、引数Orderも省略可能です。さらに、ユーザー設定の並べ替え基準を使わない、データ内に数値と文字列は混在していない、というのなら、引数CustomOrderと引数DataOptionも省略できます。

    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("C2")

あるいは、

    With ActiveSheet.Sort.SortFields
        .Clear
        .Add Key:=Range("C2")
    End With

もちろん、必要な設定は引数に指定してくださいね。

セルの色やアイコンで並べ替える >> 次ページ

Excel 2007のSortオブジェクト

Add2メソッドについて(2019年1月 追記)

新しくなった並べ替え

SortFieldオブジェクトに並べ替えの条件を指定する

最も簡素な並べ替え基準

Add2メソッドについて(2019年1月追記)