EXCEL VBA に至る前の段階としてマクロの記録がある.いわゆる「表モード」をそのまま記録したものである.ソートはこれまで .Add メソッドが中心であったが,最近になって .Add2 メソッドが追加された.それに伴い,引数 Key の Range オブジェクトの記述が若干変わった.
目次
標準操作
ワークシート市区町村にはすでにテーブル1が挿入されている前提である.テーブルのタイトルをプルダウンするとソートとフィルターが出現する.「昇順」を選択してマクロの記録を終了する.

まずはマクロの記録から
記録されたコードを掲載する.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 |
記録されたコードを書き直す
このままではコードが冗長で読みにくい.そのため変数をいくつか宣言してコードを書き直す.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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 と似ている気もする.
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 オブジェクトには構造化参照が使える.
[] の間にあるコンマ (,) は参照演算子の一つであり,論理和を示す.コロン (:) は範囲演算子であり,隣接する複数の列に含まれる全てのセルを示す.空白 ( ) は論理積を示す.

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

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

まとめ
マクロ記録からオブジェクトブラウザーの検索に至るまで,EXCEL のテーブルのソート機能を VBA 側から俯瞰した.単なるセル範囲のソートとは異なり,テーブルはリレーショナルデータベースとの親和性が非常に高いことが分かった.
ソート機能も進化し続けており,新しいデータ型に対応したメソッドが追加されていることが分かった.
“EXCEL VBA でテーブルのソートを記録する” への1件の返信