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

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

標準操作

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

ソートのキーで「昇順」を選択する
ソートのキーで「昇順」を選択する

まずはマクロの記録から

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

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

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

コードの解説

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

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

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

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

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

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

構造化参照

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

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

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

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

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オブジェクトを指定する
SortFieldオブジェクトのKeyプロパティにはRangeオブジェクトを指定する

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

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

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

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

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

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

まとめ

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

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

One Reply to “EXCEL VBA でテーブルのソートを記録する”

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください