EXCEL VBA でテーブルのソートを記録する

　EXCEL VBA に至る前の段階としてマクロの記録がある．いわゆる「表モード」をそのまま記録したものである．ソートはこれまで .Add メソッドが中心であったが，最近になって .Add2 メソッドが追加された．それに伴い，引数 Key の Range オブジェクトの記述が若干変わった．

1 標準操作
2 まずはマクロの記録から
3 記録されたコードを書き直す
4 コードの解説
5 特殊項目指定子[#ALL] とは？
6 構造化参照
7 テーブル指定子 = 特殊項目指定子 + 列指定子
8 EXCELのテーブルはリレーショナルデータベースとの橋渡し
9 特殊項目指定子とListObjectオブジェクトの類似点
10 オブジェクトブラウザーで Sort オブジェクトを俯瞰する
11 SortField オブジェクト
12 まとめ
13 共有:
14 関連

標準操作

　ワークシート市区町村にはすでにテーブル１が挿入されている前提である．テーブルのタイトルをプルダウンするとソートとフィルターが出現する．「昇順」を選択してマクロの記録を終了する．

まずはマクロの記録から

　記録されたコードを掲載する．

Option Explicit

Sub Macro1()
    ActiveWorkbook.Worksheets("市区町村").ListObjects("テーブル1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("市区町村").ListObjects("テーブル1").Sort.SortFields.Add2 _
        Key:=Range("テーブル1[[#All],[都道府県・市区町村コード]]"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("市区町村").ListObjects("テーブル1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Option Explicit

Sub Macro1()

ActiveWorkbook.Worksheets("市区町村").ListObjects("テーブル1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("市区町村").ListObjects("テーブル1").Sort.SortFields.Add2 _

Key:=Range("テーブル1[[#All],[都道府県・市区町村コード]]"), SortOn:=xlSortOnValues, Order _

:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("市区町村").ListObjects("テーブル1").Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub

記録されたコードを書き直す

　このままではコードが冗長で読みにくい．そのため変数をいくつか宣言してコードを書き直す．

Sub SortVBA()
Dim mySht   As Worksheet
Dim mySort  As Sort

Set mySht = Worksheets("市区町村")
Set mySort = mySht.ListObjects.Item("テーブル1").Sort

With mySort
    .SortFields.Clear
    .SortFields.Add2 _
        Key:=Range("テーブル1[[#All],[都道府県・市区町村コード]]"), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal

    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Sub SortVBA()

Dim mySht As Worksheet

Dim mySort As Sort

Set mySht = Worksheets("市区町村")

Set mySort = mySht.ListObjects.Item("テーブル1").Sort

With mySort

.SortFields.Clear

.SortFields.Add2 _

Key:=Range("テーブル1[[#All],[都道府県・市区町村コード]]"), _

SortOn:=xlSortOnValues, _

Order:=xlAscending, _

DataOption:=xlSortNormal

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub

コードの解説

　9 行目で保存されている SortField オブジェクトを破棄し，10 行目で新しく SortField オブジェクトを追加している．これは SortField オブジェクトがソートの規則を保持しており，これまでのソートの履歴を記憶しているからである．

　11 行目から 14 行目では，追加された SortField オブジェクトにソートの規則を記述している．最も重要なのは Key と Order である．

　16 行目から 19 行目までは今は気にしなくていい．最後に 20 行目でソートを適用している．

特殊項目指定子[#ALL] とは？

　Add2 メソッドは EXCEL 2016 から実装された機能であり，株価と地理情報でソートできるようになっている．ジオコーディングが関係するなら積極的に採用すべきメソッドである．

　最初，Key の項目に付加された [#ALL] の意味が分からなかった．Range オブジェクト関連だろうと当たりをつけて検索すると Office Tanaka の記事「テーブルの部位を特定する」がヒットした．これは構造化参照というものの一部らしい．

構造化参照

　構造化参照とはExcel テーブルでの構造化参照の使い方によると「テーブル名と列名の組み合わせ」のことであり，「テーブルにデータを追加したりテーブルからデータを削除したりすると，構造化参照の名前が変化」する．それにより「膨大な情報量のブック内のテーブルを簡単に見つけることができ」る．

　自分なりに解釈すると，テーブル内のデータを，セル参照という具体的な手続き型のレベルから，より抽象的な集合指向のレベルに引き上げた，というものであろう．SQL と似ている気もする．

SELECT  *  FROM [テーブル1]  ORDER BY  [市区町村コード]

1	SELECT * FROM [テーブル1] ORDER BY [市区町村コード]

テーブル指定子 = 特殊項目指定子 + 列指定子

　構造化参照では列見出しを使った「列指定子」と，テーブル内で任意の範囲を表す「特殊項目指定子」を使用できる．これら二つの指定子を合わせて「テーブル指定子」と呼ぶ．このあたりは EXCEL 2007 で実装された機能である．

テーブル名[[特殊項目指定子],[列指定子]]

1	テーブル名[[特殊項目指定子],[列指定子]]

EXCELのテーブルはリレーショナルデータベースとの橋渡し

　思い返すと，テーブル内で数式を記述している際にも構造化参照が使われていたため，入力が楽だった気がする（数式オートコンプリート）．してみると，ListObject オブジェクトは EXCEL 内にリレーショナルデータベースを持ち込もうとしているのかも知れない．

　田中亨は『ここは「そういうものなんだ」と飲み込んで「そうか違うのか」と納得するところ』と書いているが，この違和感は手続き型言語と集合指向言語の思想の違いに由来する．

特殊項目指定子とListObjectオブジェクトの類似点

　特殊項目指定子と ListObject オブジェクトには類似点が多い．

特殊項目指定子	参照先	ListObject
#All	テーブル全体	.Range
#Data	データ行のみ	.DataBodyRange
#Headers	見出し行のみ	.HeaderRowRange
#Totals	合計行のみ	.TotalRowRange
@[ColumnName]	数式と同じ行のセル	.ListColumns

　両者はほぼ同じものであると田中亨は述べているが，どちらの抽象度が高いのかは今はよく分からない．

オブジェクトブラウザーで Sort オブジェクトを俯瞰する

　オブジェクトブラウザーから Sort オブジェクトを検索すると，2 件のメソッドと 9 件のプロパティが出てくる．

CLASS	MEMBER	ICON
Sort	Application	Property
	Apply	Method
	Creator	Property
	Header	Property
	MatchCase	Property
	Orientation	Property
	Parent	Property
	Rng	Property
	SetRange	Method
	SortFields	Property
	SortMethod	Property

　この中で特に重要なプロパティは SortFields プロパティである．複数形になっており，SortField オブジェクトのコレクションであろうと推測される．そこでさらにオブジェクトブラウザーで検索する．

SortField オブジェクト

　SortField オブジェクトには 3 件のメソッドと 11 件のプロパティがある．

CLASS	MEMBER	ICON
SortField	Application	Property
	Creator	Property
	CustomOrder	Property
	DataOption	Property
	Delete	Method
	Key	Property
	ModifyKey	Method
	Order	Property
	Parent	Property
	Priority	Property
	SetIcon	Method
	SortOn	Property
	SortOnValue	Property
	SubField	Property

　この中で重要なプロパティは Key および Order である．そこでそれぞれをオブジェクトブラウザーでさらに掘り下げる．

Key プロパティには Range オブジェクトを指定する

　先程のサンプルコードでは Key:=Range("テーブル1[[#All],[都道府県・市区町村コード]]") というふうに Range オブジェクトを指定した．Range オブジェクトには構造化参照が使える．

　[] の間にあるコンマ (,) は参照演算子の一つであり，論理和を示す．コロン (:) は範囲演算子であり，隣接する複数の列に含まれる全てのセルを示す．空白 ( ) は論理積を示す．

SortFieldオブジェクトのKeyプロパティにはRangeオブジェクトを指定する

Order プロパティには定数を指定する

　SortField のメンバーである Order プロパティは XlSortOrder というクラスに該当する．この語句をさらに検索する．

定数 XlSortOrder には昇順 (XlAscending) と降順 (XlDescending) がある

　定数 XlSortOrder には昇順 (XlAscending = 1) と降順 (XlDescending = 2) があることが分かる．

まとめ

　マクロ記録からオブジェクトブラウザーの検索に至るまで，EXCEL のテーブルのソート機能を VBA 側から俯瞰した．単なるセル範囲のソートとは異なり，テーブルはリレーショナルデータベースとの親和性が非常に高いことが分かった．

　ソート機能も進化し続けており，新しいデータ型に対応したメソッドが追加されていることが分かった．