VBAを使って表のデータを検索するシステムを作ってました。検索システム自体は(当初の必要機能は)完成したので、今後万が一VBAを書くことになったときのためにメモを残しておきます。
VBAは初めて触ったので、変な実装になっている可能性があります。
目次
1.やりたいこと
同じブック内の表から学年と科目を入れると教えられる講師の講師番号、名前、電話番号を表示したい。
検索フォームは次の画像のようになった。
2.成果物
3.システムの大枠の参考にしたサイト
4.VBAをVSCodeで編集したい
VBE(Visual Basic Editor)は使いにくいです。VSCodeに慣れてしまった体からしたら耐えられないです。ということで、VBAをVSCodeで編集します。
4-1.参考サイト
4-2.準備するもの
- vbac.wsf
- Excel(予めモジュールなどを挿入しておく)
- VSCode
4-3.vbac.wsfをダウンロード
下記のレポジトリからダウンロードします。
vbac.wsf以外は使わないので消しても大丈夫です。
4-4.指定の階層構造を作る
4-3でダウンロードしたvbac.wsfとExcelファイルを次のような階層構造にします。
Folder
├ bin
│ └ Excel file
└ vbac.wsf
4-5.Excelのセキュリティ設定の変更
Excelのオプションからトラストセンター→トラストセンターの設定→マクロの設定→VBAプロジェククオブジェクトモデルへのアクセスを信頼するにチェックを入れる。
4-6.オブジェクトを書き出す
PowerShellやコマンドプロンプトで、4-4で作ったvbac.wsfがあるディレクトリに移動します。
そのディレクトリ内で次のコマンドを実行します。
cscript vbac.wsf decombine
コマンドの実行後、4-4で作った階層構造が次のように変化します。
Folder
├ bin
│ └ Excel file
├ src
│ └ Folder2
│ ├ Module1.bas
│ ├ Sheet1.dcm
│ ├ UserForm1.frm
│ └ UserForm1.frx
└ vbac.wsf
書き出されるファイルは一例であり、VBEで予め作成していたモジュールやユーザフォーム、クラスによって変わります。
注意ですが、必ず予めモジュールなどをVBE上で作成してください。そうでない場合何も書き出されません。
4-7.VSCodeにVBAを書くための拡張機能をインストール
VSCode拡張機能からVBAと検索して出てきたものから選べばいいです。
VSCode VBA
シンタックスハイライトとかをしてくれます。
vba-snippets
スニペットをいくつか追加してくれます。
vscode-vba-icons
vba関連のアイコンを追加してくれます。
4-8. ファイルをVBEに取り込む
4-6と同じディレクトリで次のコマンドを実行します。
cscript vbac.wsf combine
4-9.デバッグはVBEで行う
VBEでデバッグを行います。
結局VBE上で書き直してデバッグしてを繰り返すので、だんだんオブジェクトを書き出すのがめんどくさくなります。
意外とVBEはデバッグ機能に関しては優秀。
5.ユーザフォームの作成
VBEから作れます。GUIで作れるので簡単。変なところをダブルクリックすると、そのオブジェクトに対するソースコードを記述するウィンドウが開くのが厄介。ダブルクリックに自信がない場合はオブジェクトのプロパティからラベルなども編集できるので、そっちのほうがいいかもしれない。
また、オブジェクトをコピペできるが、それぞれに勝手に番号が振られる(ComboBox1のように)ので、バグを防ぐために上から番号順に同じオブジェクトは並べたほうがいい。
6.コード全体
6-1. UserForm1.frm
6-2. Sheet1.dcm
※Gistのシンタックスハイライトが拡張子.dcmでは効かないため、Gistに登録したファイル名の拡張子を標準モジュールである.basに変更しています。
7.書いてるときに躓いたところ
If文の書き方やFor文の書き方などVBAの基本的な文法は省略しています。
ただ、私はIf文でEnd Ifを忘れたり、For文でNextを忘れたりしてめっちゃバグらせました。気をつけよう。
7-1.表の最終行、最終列の取得
lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
lastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
Ctrl+矢印と同じ操作をして最終行/列のセルを取得するようです。RPAみたいで面白いと思いました。
7-2.Forループからの脱出
Exit For
break文ないのかよって思いました。
7-3.標準モジュールとブックモジュールとシートモジュール
- 標準モジュール: アクティブブックのアクティブシートを参照
- ブックモジュール: アクティブブックのアクティブシートを参照
- シートモジュール: シートモジュールが記述されているシートを参照
7-4.ユーザフォームの初期化
Initializeイベントを用います。
7-5.コンボボックスのイベント
色々あります。今回はチェンジイベントのみ使っています。
Private Sub ComboBox_Change()
"イベントが発生したときに行いたい処理を記述"
End Sub
ComboBoxの部分は、コンボボックスが複数ある場合には末尾に数字がつくので、処理を行いたいコンボボックスの名前を確認しておきましょう。
7-6.コンボボックスを連動させたい
上からリストを選択していくと、上のコンボボックスの内容に応じて次のコンボボックスの中身が変更されるようにしたい。実装は6-2を見てもらうので、やり方とロジックの説明を書きます。
- コンボボックスに入れたいデータの表を作ります。
- 各コンボボックスを先程作った表を使って初期化します。ただし、表には重複する部分があるのでDictionaryを使って重複を弾きます。
- 一番上のコンボボックスで表示されるリストは初期化されたまま使います。
- 一番上のコンボボックスの要素を選択したあと、その要素と表を比較して一致してる部分のリストを作成します。
- そのリストをチェンジイベントを用いて2番目のコンボボックスに適用します。
- 2番目のコンボボックスの要素を選択後、3番目のコンボボックスについて4,5と同じことをします。
8.VBAを書いた感想
できれば二度と書きたくない。GUIを簡単に作れるのは利点だが、PySimpleGUIを使った後だと逆にめんどくさい。
いい経験にはなったのが救い。