今日はExcelの入力規則のリスト選択の使いづらさを改善するアドインを作ってみたので紹介します。
今回作ったもの
読者の皆さんはExcelで入力規則のリストって使ってますか?
これ、使いにくくないですか?
今回作成したアドインを使うことで、既存のブックに一切の手を加えることなくリスト選択が楽になります!
このアドインの仕様
以前作成した セル結合禁止アドイン や 作業グループ禁止アドイン と同様に「Excelアドイン」です。
ダウンロード
プログラムはxlam
形式のアドインファイルに含まれているので、どんなブックであろうと共通のマクロを動作させることが出来ます。
アドインの実行は必要な時だけダブルクリックで立ち上げても良いですし、アドインフォルダにインストールして常駐するようにしても大丈夫です。
※ファイルのプロパティより、「許可する」にチェックを入れないと開けない場合があるようです。
ソースコードは自由に改変していただいても構いませんが、使用は全て自己責任でお願いします。
再配布も自由ですが、常識的な範囲内でお願いします。
使用風景
アドインを起動するとリボンに下図のようなタブが増えます。
「監視開始」をクリックすると、Excel全体に対してダブルクリックイベントを監視します。
アドイン起動とともに自動的に監視を開始するので、通常は押す必要がありません。
「監視停止」かマクロを中断したときのみ、改めて「監視開始」を押す必要があります。
ダブルクリックイベントを検知した時、該当セルに入力規則のリストが登録されている場合のみ選択フォームが表示されるようになります。
機能一覧
- ダブルクリックで起動
- 入力規則リストの入ったセルをダブルクリックしたときのみ、このフォームが起動します。
- 少しマクロをイジれば右クリック起動や、セル選択時起動も簡単にできます。
- ボタンを押したらフォームが起動というプログラム変更も容易です。
- ダブルクリックで決定
- 操作の手間を減らすため、ダブルクリックで決定です。OKボタンは設けてません。
- 一応、Enterキーでも確定できます。
- ESCでキャンセル
- ESCを押した瞬間に窓が閉じます。
- ×を押した場合もキャンセルです。
- インクリメンタルサーチに対応
- 上の入力ボックスに文字を入力すると、リアルタイムに選択肢が減っていきます。
- 部分一致・完全一致検索に対応
- 比較方法は
値 Like "*" & 検索文字列 & "*"
なので、文法違反するとERROR
になります。 - 正規表現、前方、後方、AND、ORへの対応はしてません。
- 比較方法は
- 択一選択、複数選択に対応
- ただし複数選択はプログラムを一言だけ修正が必要です。
- 気が向いたら設定画面で選べるようにする予定。
- 入力済みの値を選択した状態で表示
- 簡単に作ると直前に入っていた値を考慮した表示が出来ませんが、このマクロは入力済みの値を検索して選択状態にした上で表示します。
- 入力済みデータをカンマ区切りで分解して複数選択状態の復元も対応しています。
既知の不具合とか
- カンマが含まれたデータには対応していません。
- モードレスフォームには対応してません。
- 設定画面は枠だけ用意してありますが未完成です。作って公開するかも未定です。
- VBAのリストボックスは、マウスのホイールスクロールができません。なんとかしたいです。 アイテムをクリックしたまま上下に動かすと勝手にスクロールするので、ちょっと早すぎて使いづらいですがそれでしのいでください。
追記
リストボックスのスクロールに対応するコードが見つかりました。
一応動きましたがAPIを駆使しているので、よく動作確認をする必要がありそうです。
配布物には導入しませんので、必要な人は挑戦してみてください。
プログラム
要となる ValidationListSelector.cls
だけ説明しておきます。
いつものアドインと同じように、WithEvents
でExcel.Application
を監視します。
今回はダブルクリックなので、app_SheetBeforeDoubleClick
としました。
Public WithEvents app As Excel.Application Private Sub app_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Call OpenValidationList(Sh, Target, Cancel) End Sub
イベントプロシージャの中に本文を書くと、後々の拡張に支障が出るので切り出しました。
これで、右クリックに対応したいときは、app_SheetBeforeRightClick
からこれを呼ぶだけです。
Sub OpenValidationList(ByVal Sh As Object, ByVal Target As Range, ByRef Cancel As Boolean) ~ End Sub
該当のセルが目的のセルか判定するためにガード節を設けます。
最初は↓を書いたのですが・・・
Dim rngVali As Range Application.EnableEvents = False On Error Resume Next Set rngVali = Target.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 Application.EnableEvents = True If rngVali Is Nothing Then Exit Sub If Intersect(Target, rngVali) Is Nothing Then Exit Sub If Target.Validation.Type <> XlDVType.xlValidateList Then Exit Sub Dim Formula: Formula = Target.Validation.Formula1 If Formula = "" Then Exit Sub
On Error
が必要になってしまったので、丸投げにすることにしました。
On Error Resume Next If Target.Validation.Type <> XlDVType.xlValidateList Then Exit Sub Dim Formula: Formula = Target.Validation.Formula1 If Formula = "" Then Exit Sub On Error GoTo 0
入力規則のリストの数式には、外部を参照する数式と、コンマ区切り定数の2パターンがあるため、頭文字がイコールかどうかで判定しました。
数式の場合は、Evaluateで計算すれば1列の二次元配列が返るのでTransposeして一次元配列化して取得です。
'入力リストの選択項目を取得 Dim list As Variant If Formula Like "=*" Then list = Application.Evaluate(Formula) list = WorksheetFunction.Transpose(list) Else list = Split(Formula, ",") End If
フォームの実行は、標準メソッドのShow
は使用しません。
コンストラクタが無く、起動時に引数を与える事ができないからです。
代わりにOpenForm
メソッドを実装しました。
(宣言部)
Rem 引数を指定してフォームを表示する Rem Rem @param arr_listitems リストに表示する全アイテム配列 Rem @param arr_defaultvalues 起動時に選択状態にするアイテム配列 Rem @parem can_multiselect 複数選択の可否 Rem Rem @return As Variant(0 to n) 選択されていたアイテム配列 Rem キャンセル時:Null Public Function OpenForm(arr_listitems, arr_defaultvalues, can_multiselect As Boolean) As Variant ・ ・ Me.Show '←モーダルフォームではここでVBAが止まる ・ OpenForm = Me.Result'←戻り値の設定 ・ End Function
こんな感じで使います。
(呼び出し部)
'初期状態で選択する値 Dim def As Variant def = Split(Target.Value, ",") 'フォームの実行と結果の取得 Dim Result Result = ListSelectorForm.OpenForm(list, def, False) 'キャンセルされたのでなければ、結果をコンマ区切りで連結してセルに入力 If Not IsNull(Result) Then Target.Value = Join(Result, ",") End If
MsgBoxと同じような感じで使えるので、コードがスッキリして綺麗ですよね?(我ながら綺麗に分離できました)
インクリメンタルサーチの部分は省略します。
ハマったこととか
ユーザーフォームのデザイナを開いたままデバッグするとVBAがリセットされる
デバッグしている最中に、ユーザーフォームのデザイナを開くとVBAがリセットされるのは、使ったことのある方なら誰でもご存知かと思います。
この仕様が災いしてか、デザイナを開いたままマクロを実行して(自動的にデザイナが非表示になり)DoubleClickイベントなどを起こすと、マクロが完結してExcelに処理が戻ると同時にデザイナが再表示されVBAのリセットがかかります。
このため、今回のようなアドイン開発ではイベント監視のためのクラスインスタンスが初期化されて何度か苦労しました。
しかも、時々オートメーションエラーが出てExcelもクラッシュしました。
↓詳細
#VBA でグローバル変数が初期化される原因
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2020年4月11日
VBEにてフォームウィンドウを表示した瞬間に、VBAのリセットがかかります。
例えばアドインのクラスモジュールで、グローバルイベントをフックするようにしているとき、VBEでフォームを開いたままイベントを起動すると、完結ごとにリセットされてハマります
SpecialCellsメソッドはSelectionChangeイベントを発生させる
入力規則の判定部分でバッサリ消したところで、SpecialCellsメソッド
を使用していたのですが、SelectionChangeイベント
が発生することが判明し、何度かExcelがクラッシュしました。
機能からは予測しづらいのですが、そういうものなようです。
対処法は上記のようにApplication.EnableEvents = False
とするしかなさそうです。
↓詳細
#ExcelVBA で Cells.SpecialCellsメソッドは、副作用を起こすので利用には注意しなければならない。
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2020年4月12日
なんと、実行したと同時にSelectionChangeイベントが発生するのである!!!
例えば、以下のようにSelectionChangeイベントでSpecialCellsを実行すると実行時エラー又はExcelがクラッシュする。 pic.twitter.com/fFefI35jcl
まとめ
このアドインを使えば、誰にも迷惑をかけずに自分だけが快適にリスト選択ができるようになります。
これでもう、あの使いづらいリストとはオサラバです!!!
快適なExcel生活を送りましょう!
以上
何か御座いましたらコメント欄、またはTwitterからどうぞ♪
それではまた来週♪ ちゅんちゅん(・8・)