概要
過去記事で紹介した「ココナラ作業管理ツール」の機能の一部を、実際のVBAコードではどのように実装しているのか、技術解説を行う。
機能おさらい
紹介するのは、状態の列をダブルクリックすると、「状態」を選択入力できるユーザーフォームが隣のセルに起動して、ボタンを選択して入力ができるようになるもの。
Excelの標準機能であるドロップダウンリストを利用する方法もあるのだが、ドロップダウンリストだと
- リストの動的変更がやりづらい
- シートの表示倍率に応じて文字サイズも変化してしまう
- Alt+↓で表示できるけど、ちょっとめんどい
- 選択対象の色分けが出来ない
- リストが多い場合を全体が表示されないので選ぶのが大変
などのデメリットがある。
こんな既存機能に頼っていたらココナラではほかの出品者とは差別化ができない。
技術解説
このような機能のユーザーフォームは次のようなコンセプトで設計してある。
- 選択対象のリストは自由に設定できる。
- 文字サイズも設定できる
- コマンドボタンの色も設定できる
- コマンドボタンの配置は縦横の数を指定できる
- コマンドボタンのサイズ（高さ、幅）を指定できる
- コマンドボタンの個数に応じてユーザーフォームのサイズは自動的に調整される。
- ユーザーフォームのプロパティとして入力対象のセル範囲を指定し、入力範囲外のセルを選択しているときにボタンを押して入力しようとしてもセルには値が入力されない。
- コマンドボタンをダブルクリックすると入力されてユーザーフォームも消える
- アドイン（xlam）に設置しておいて、新しいマクロ付ブックにすぐ実装することができる。
おもに必要とする技術的要素をまとめる
- クラスモジュールによるWithEvents構文→コマンドボタンの動的配置
- Property Set,Get,Let関数によるプロパティ設定→ユーザーフォームに任意のプロパティを設定する
- ユーザーフォームの表示位置を操作する
コード全体
「案件処理2」シートのワークシートモジュールのコードは以下の通り。
ダブルクリックがトリガーとなるWorksheet_BeforeDoubleClickプロシージャと、セル値変更がトリガーとなるWorksheet_Changeプロシージャの2つ。
各機能は別々のプロシージャ「Event_**」として個別にサブルーティンとしてまとめてあり、可読性、メンテナンス性を上げている。「Event_**」は別の標準モジュールに記述してある。
今回紹介する状態入力の機能は赤線を引いた部分となる。
「Event_状態入力」のコードは次の通り。
CellAreaにダブルクリックで実行されるセル範囲を参照する。セル範囲が含まれるかどうかはIntersect関数で判定する。
GetNameCellは名前定義のセルを取得するためのプロシージャ。
コードのリンク→Enum_名前定義 · GitHub
このコードは自動生成するようにしてある。
わざわざこのようなものを用意する理由として、名前定義のセルにおいて次の画像のように入力候補（インテリセンス）で選べれるようにするためである。
またコードにもあるように、名前定義のセルアドレスもすぐ確認ができるようになっている。
Get_フォルダ件数は「案件処理2」のテーブルに含まれる対象フォルダ（案件）の件数である。基準セル[Cell]から最終行セル取得[EndCell]と、そのセル範囲[CellArea]のセルの個数から計算する。
GetEndCell→GetEndCell · GitHub
Cancel=True以下で選択入力のユーザーフォームの設定と起動を行っている。
詳細は以降で解説する。
ちなみに、「Cancel=True」は引数で受け取ったCancelをTrueにすることで、ダブルクリックした時の操作（セル編集モード）をキャンセルするようにしている。これはダブルクリックしたセル[Target]が対象セル範囲[CellArea]に含まれるので処理を実行するためである。
ユーザーフォームとクラスモジュールのコード
使用しているユーザーフォームとクラスモジュールのコードを先に載せておく。
ユーザーフォーム（frm汎用入力規則）はコマンドボタンを配置する土台で、配置するコマンドボタンはクラスモジュール（clsButton）にてそれぞれ機能を設定する目的。
- ユーザーフォーム：frm汎用入力規則 · GitHub
- クラスモジュール：clsButton · GitHub
ちなみに用意しているユーザーフォームは「frm汎用入力規則」としている。ユーザーフォームには特段着色はしていない。（下画像）
ユーザーフォームの設定
状態一覧、状態色一覧の取得
Get__状態一覧は入力対象のリストを一次元配列として取得する部分。*1
「設定」シートに一覧が入力してあり、ここから情報を取得してきている。
この一覧は後から追加、消去、変更が可能になる。
GetCellArea：GetCellArea · GitHub *2
GetArray2DFromCell：GetArray2DFromCell · GitHub*3
TransposeN1toArray1D：TransposeN1toArray1D · GitHub*4
Get__状態色一覧は入力対象のリストの色を、セルの塗潰色から取得する。
キャプションの設定
ユーザーフォームのキャプションをプロパティから設定する
入力セル範囲の設定
ユーザーフォームのCellAreaプロパティに入力セル範囲を設定する
CellAreaプロパティはユーザーフォームにおいて下記のように記述することで追加することができる。
ユーザーフォームにおいてコマンドボタンを動的に作成する際に、それぞれのコマンドボタンにもCellAreaプロパティを設定する。
各コマンドボタンにもユーザーフォームと同様にCellAreaプロパティを追加している。
各コマンドボタンにおける「クリック」「ダブルクリック」「Enterキー押下」での「選択セルに値入力」の動作を記述する。
入力一覧、コマンドボタン個数、サイズ、文字サイズ、色の設定
入力一覧、コマンドボタン個数、サイズ、文字サイズ、色は「frm汎用入力規則」のSettingメソッドで一気に設定する。
ちなみにCellAreaは別で設定する理由は、CellAreaを未設定なら入力範囲を限定しないなど「設定しない場合がある」ことを考慮した設計である。
「frm汎用入力規則」のSettingメソッドでは上記の各種設定を引数で受け取って、frm汎用入力規則のPrivate変数に格納する処理を行い、「リストからボタン設定」を実行している。
「リストからボタン設定」ではPrivate変数に格納した値をもとに、コマンドボタンを配置と機能設定を行っている。詳細の処理はコメントに記載しているので割愛する。
コマンドボタンの配置には「MakeCommandBtns」という汎用プロシージャを利用している。
引数で渡したユーザーフォーム上に指定縦横個数のコマンドボタンをサイズやギャップを指定して整列させて複数配置することができる。
「リストからボタン設定」内で実行している「フォームサイズ設定」は配置したコマンドボタンの縦横の個数に応じてユーザーフォームのサイズを調整する処理である。
10,20などの数字は実際は微調整での数字である。
ダブルクリックしたセルの右隣にユーザーフォームを配置
ユーザーフォームをダブルクリックしたセルの右隣すぐに配置するには下記画像の2行で処理を行っている。
- ユーザーフォームをモーダレスで表示する
- 「SetFromNearCell」プロシージャを実行する
「SetFromNearCell」プロシージャは下記リンクのコードである。
セルの座標（ドキュメント座標）からスクリーン座標、スクリーン座標からユーザーフォーム座標に2回変換を行ったりとかなり複雑な処理を行っている。これはワークシートの表示サイズや、スクリーンの解像度なども考慮した処理になっているのでAPIやらなんやらいろいろ利用する必要がある。
ちなみに、mac版ExcelではAPIが使えないので、この部分はエラーになるのでコメントアウトして納品するようにしている。
アドイン（xlam）に設置しておいて、新しいマクロ付ブックにすぐ実装することができる。
これらの実装は、ユーザーフォーム「frm汎用入力規則」とクラスモジュール「clsButton」があればすぐできる。
そのためには開発用アドインに常時設置しておくことで、プロジェクトウィンドウにおいてクリック＆ドラッグでいつでも複製できるようにしておく。
複製した後は、入力セル範囲、キャプション、入力一覧などの一連の設定を行う記述が必要になるが、それらの記述はユーザーフォームのコードの冒頭にコメントアウトしてテンプレートとして置いてある。これですぐに使い方を思い出すことができる。
ちなみにテンプレートは2つあり、1つ目は上記で紹介した「セルへの値入力」。2つ目は「ユーザーフォームで受け取った値を、変数に格納」である。こちらは頑張って解読してください。
広告
ココナラにてExcelVBA関連のツール開発を承っております。
累計対応実績352件（2023.6.16時点）
