VBAでツール開発 2023
ポイント
- コードはariawaseを使ってエクスポートして管理
- コードはざっとVSCodeで書いて、Excelで動作確認
- GitHub Copilotを使ったので定型的なコードはさっと書けた
- 今回データ取り込み、CSV出力機能を実装したがダミーデータでもいいので一連の流れを一通り動かせるようにすると進捗が出るように思った。
ポイント詳細
コード管理
以前と違い、Rubberduckがいろいろな機能を提供していたけど、コードのインポートで上書きしてくれなかったりでまごついたのでariawaseを使った。ariawaseはルートフォルダに ちょっとしたプログラムを置いて、その下に、binフォルダを作り、そこにxlsmファイルを置く。プログラムを呼び出して、decompileすると、srcフォルダにxlsmファイル名のフォルダが作成されて、VBAのコード、ユーザーフォームのコードがエクスポートされる。詳しくは、以下のリンクを参照。
GitHub Copilotなどの利用
今回GitHub Copilotを使いながらコードを書いたが、過去にある程度書いたことがある人にとっては便利という印象。一番最後の入力行までループを回すとか、繰り返しセルにアクセスして、といったコードも保管してくれた。ただ、2023年6月時点では信じきれないので、要注意。ファイル操作するコードが提案されたけど、Excelで動かしてみたら動かなかった。
今回ariawaseでコードをエクスポートした。このツールではシートに紐づくVBAはdcmという拡張子でエクスポートされる。GitHub Copilotはbasファイルだけサジェストしてくれるので、xxx.dcm.basと拡張子を書き換えてコード補完を利用した。
また、サジェストされないコードについてはChatGPTで質問してみたが、そこまで当てにならない印象だった。セルのメモをプログラムで追加するコードをかけるか質問したら、できないと言われたが、ググったらけっこうあっさり見つかった。
VBAのコード全般について
VBAはできる限りExcelの機能を全面的に利用する方がよさそうに感じた。また、VBAもできるだけシートに書くようにして、共通モジュールは避ける方がよさそう。具体的には下に書くが、VBAはExcelファイルにアクセスするので、スコープを狭くするよう意識的に設計するのがよさそう。
例えば、データの受け渡しは名前の定義を利用する、クラスの初期化は呼び出し側で行う、VBAはできるだけ、シートのマクロとして定義する、できるだけ関数に寄せる、といったところが注意点だと思う。
また、変数のスコープがどうも思っていたのと違って、いくつかの変数宣言の直後に明示的に初期化をして、値が残っているように見える現象を回避した。これはもう少しちゃんと仕様を確認したほうが良いと思う。
今回のはまりどころ
今回はとある国の地域情報を入力するために、郡プルダウンと村プルダウンとで連動するプルダウンを作成した。郡、村その他情報を入力するシート、県を選択する設定シート、県・郡・村のマスタデータシートを持つExcelファイルを考えた。
設定シートで最初に県を選択すると、入力シートの郡プルダウンでその県にある郡が選択候補になり、郡プルダウンを選択すると、その郡にある村が選択候補になるという仕様。具体的にはこのサイトを参照。
www.pc-koubou.jpこのサイトでは入力規則と名前の定義の組み合わせるというやり方を紹介しているが、これはデータを作り込まないといけないと思い却下した。
そこで、セルの入力規則を動的に設定するプログラムを書いたが、入力規則が255文字までという制限にひっかかり代替案を探すことになった。最終的には、上記のサイトのやり方とVBAを使った折衷案となった。名前の規則をVBAで定義することで、データの作り込みを回避した。
名前の定義を利用すると、名前の定義の中で探せばよいので、親子関係などがあるデータでも複雑にならなかった。
参考URL
ariawaseツールについての説明
連動プルダウンの作成
Rubberduckについて
