Excel VBA 前置き
先日引き継いだばかりのシステムですが、年次のデータ更新作業があるようで。.xls で受け取ったデータを SQL に加工して流しこむような作業なので、VBA メインでやってみました。
で、よく考えたら VBA の Tips とか全然ブログに書いたことがなかったので、この機会にいろいろ書いておこうかと。長くなったのでエントリをいくつかに分けますが、まずは前置きの
- コーディング tips(コーディング規約なんかも)
- VBA のバージョン管理
といった部分について。
Excel の自動化に何を使うか?という話
Excel マクロは VBA が野暮ったいのとセキュリティまわりの警告が面倒なのとで、普段はどちらかというと外部スクリプトから OLE 経由で Excel を呼び出すことが多いです。
- プロジェクトで Perl/Ruby あたりを使っているなら OLE で Excel 操作
- もしその辺りの言語を使わない案件なら Windows 標準でインストールされている VBScript/JScript で作成
という感じで。
それでも今回 VBA でやったのは、
- スポット作業なので、毎年ヘルプ要員にお願いすることになりそう
- 送られてくるデータファイル名が不定で、「データファイルを開いた状態で F8 でマクロ実行する」という手順にしてしまいたかった
という理由です。
まあ今回お手伝いしていただいた方は VBA 書けないとのことで、ワークシート関数で SQL を組み立ててもらった部分もあるんですけど、式の途中にコメントも残せないし可読性低いので、基本的にプログラムで書きたいですね。
コーディングの tips 的な話
コーディング規約
VBA のコーディング規約、ちゃんとあったんですね・・・今回見つけました。
ちょっと見てみましたが、VB のコーディング規約と違って、定数が全部大文字だったりしていい感じです。
今まではこの存在を知らなかったので、VB や VBScript のコーディング規約に従ってました。
紹介しておいてなんですが、次からのエントリで書いてるコードは周りのコードに引きずられて、この規約に従っていなかったりします。
個人的な癖
良否はともかく、こうやってますよという話です。
- ActiveX オブジェクトを使う際は、参照設定は使わずに CreateObject() する
- JavaScript やら VBScript に慣れて ActiveX オブジェクトの生成に抵抗なくなってきたし、コードをコピペしてそのまま使えたほうが便利なので
- 可読性は下がるし実行速度面でもたぶん不利だけど、移植性重視ということで
- 変数宣言(Dim)は関数の先頭に並べずに、他の一般的な言語の習慣に従い「使う場所になるべく近い場所で宣言」する
- プロシージャ単位で単体テストしやすいように、アクティブなブック/シートに対して処理を実行する引数なしのプロシージャを書く
- 逆に「途中でユーザが操作しても処理がおかしくならないように、引数でブック/シートを受け取る」「処理はなるべくクラスに書く」というのを心がけてた時期もあるけど、今はテスタビリティ重視でこうやってる(どうすべきかまだ迷い中)