今回はExcel VBAでAutoFilterオブジェクトを取得する方法について調査した結果を報告します。
この記事は
最終的に「確実にワークシートのオートフィルタオブジェクトを取得できる関数」を作り出すのが目的です。
高い品質のコードを書くためには、「状況によって挙動が変わるコード」を無くさなければなりません。
そして、Excel VBAはアプリケーションの中で動くプログラムという特性上、「状況によって挙動が変わるコード」が非常に多いという特徴があります。
例えば
- アクティブウィンドウ、ブック、シート、セルによって変化するもの
- コードを記載しているモジュールによって変化するもの
- パソコンの環境によって変化するもの
などがありますが、今回は「アクティブセルによって変化するAutoFilterオブジェクト」の問題を解消する関数の開発に向けて、試行錯誤した結果をまとめました。
AutoFilterオブジェクトの取得方法
AutoFilterオブジェクトを取得できるプロパティは2つ存在します。
テーブル専用のオートフィルタの取得法
一つがListObject.AutoFilterです。ActiveSheet.ListObjects(1)は「アクティブシート上の1番目のテーブルオブジェクトのオートフィルタを取得する」という、名前通りの挙動をします。
Sub Sample_ListObjectAutoFilter() Dim af As Excel.AutoFilter Set af = ActiveSheet.ListObjects(1).AutoFilter Debug.Print af.Range.Address End Sub
普通のオートフィルタの取得法
もう一つが、一番オーソドックスな方法である、Worksheet.AutoFilterです。
Sub Sample_SheetOrListObjectAutoFilter() Dim af As Excel.AutoFilter Set af = ActiveSheet.AutoFilter Debug.Print af.Range.Address End Sub
ところが、このコードこそが「状況によって挙動が変わる」恐ろしいプログラムなのです。
状況によって挙動が変わるコード
ActiveSheet.AutoFilterには問題があり、シートのアクティブセルの位置によってWorksheet.AutoFilterが返る場合とActiveCell.ListObject.AutoFilterが返る場合があります。
アクティブセルで参照先が変わるだけでも十分に厄介な性質ではありますが、本当に困るのは確実にワークシートのオートフィルタに到達できる方法が存在しないということです。
そこで、現場で求められているのが、「確実にワークシートのオートフィルタオブジェクトを取得できる関数」になります。
とは言え、アクティブセルを一旦テーブル外に出さないことには取得できないのですから、アクティブセルの変更や元の状態に戻す処理など、あまり操作したくない部位まで触らなければなりません。
これを上手に関数化するのが、今回の課題となります。
確実にワークシートのオートフィルタオブジェクトを取得できる関数
流れとしては
- オートフィルタの欲しいシートを関数に渡す
- シートのアクティブセルを、テーブル以外のセルに変更する
- オートフィルタオブジェクトを取得する
- 元のセルに戻す
- 結果を返す
のはずなんですが、色々な下準備が必要となるため、最終的にソースコードはこうなりました。
ソースコード
考慮すべきこと
ソースコード中で現れていますが、汎用的な関数を作る際には考慮すべき事項が大量にあります。
- イベント発生の設定保持、無効化、復元
- SheetChange
- SelectionChange
- アクティブオブジェクトの保持、切り替え、復元
- Workbook
- Worksheet
- Cell or Shape
- 対象シートの表示状態の保持、切り替え、復元
- シート上のアクティブセルの保持、切り替え、復元
思いついた限りで、これだけのことをしなければなりません。
実地運用を続けていたら、他にも出てくることでしょう。
（ブックの保護とかどうなるのかな・・・）
余談
オートフィルタを使っていると、プロパティで混乱することがよくある。
設定解除と、設定済みの確認で、オブジェクトツリーが違うのだ。慣れるしか無い。
オートフィルタの有無
Worksheet.AutoFilterMode = True / False ListObject.ShowAutoFilter = True / False
オートフィルタの表示確認
ListObject.ShowAutoFilterDropDown = True / False
オートフィルタの設定・解除
Range.AutoFilter
オートフィルタの設定されたセル
AutoFilter.Range
テーブルの場合のセル
ListObject.DataBodyRange
参考資料
TL1
https://twitter.com/KotorinChunChun/status/1249889353553154048?s=20
TL2
https://twitter.com/KotorinChunChun/status/1272889048609312776?s=20
まとめ
これで完成とはならないかもしれませんが、大抵の場合は安心してシートのオートフィルタが取得出来るようになりました。
この関数を使用したツールの紹介は、機会があればそのうちに。
以上
何か御座いましたらコメント欄、またはTwitterからどうぞ♪
それではまた来週♪ ちゅんちゅん(・8・)
