miauのブログ

はてなダイアリー「miauの避難所」をはてなブログに移行しました

Excel VBA 前置き

先日引き継いだばかりのシステムですが、年次のデータ更新作業があるようで。.xls で受け取ったデータを SQL に加工して流しこむような作業なので、VBA メインでやってみました。

で、よく考えたら VBA の Tips とか全然ブログに書いたことがなかったので、この機会にいろいろ書いておこうかと。長くなったのでエントリをいくつかに分けますが、まずは前置きの

  • コーディング tips(コーディング規約なんかも)
  • VBA のバージョン管理

といった部分について。

Excel の自動化に何を使うか?という話

Excel マクロは VBA が野暮ったいのとセキュリティまわりの警告が面倒なのとで、普段はどちらかというと外部スクリプトから OLE 経由で Excel を呼び出すことが多いです。

  • プロジェクトで Perl/Ruby あたりを使っているなら OLE で Excel 操作
    • Python だと補完が聞くと聞いたので、今後個人利用は Python 中心でいこうと思ってる
  • もしその辺りの言語を使わない案件なら Windows 標準でインストールされている VBScript/JScript で作成
    • JScript のほうが正規表現とか気軽に使えて好みだけど、Excel でマクロを記録→移植するパターンが多いなら VBScript のほうが楽かも

という感じで。

それでも今回 VBA でやったのは、

  • スポット作業なので、毎年ヘルプ要員にお願いすることになりそう
    • PerlRuby の知識がなくても使えるものがいい
  • 送られてくるデータファイル名が不定で、「データファイルを開いた状態で F8 でマクロ実行する」という手順にしてしまいたかった
    • OLE でも ActiveWorkbook とか使えるけど、Excelインスタンスが複数立ち上がってるとめんどくさい

という理由です。

まあ今回お手伝いしていただいた方は VBA 書けないとのことで、ワークシート関数で SQL を組み立ててもらった部分もあるんですけど、式の途中にコメントも残せないし可読性低いので、基本的にプログラムで書きたいですね。

コーディングの tips 的な話

コーディング規約

VBA のコーディング規約、ちゃんとあったんですね・・・今回見つけました。

ちょっと見てみましたが、VB のコーディング規約と違って、定数が全部大文字だったりしていい感じです。

今まではこの存在を知らなかったので、VBVBScript のコーディング規約に従ってました。

紹介しておいてなんですが、次からのエントリで書いてるコードは周りのコードに引きずられて、この規約に従っていなかったりします。

個人的な癖

良否はともかく、こうやってますよという話です。

  • ActiveX オブジェクトを使う際は、参照設定は使わずに CreateObject() する
    • JavaScript やら VBScript に慣れて ActiveX オブジェクトの生成に抵抗なくなってきたし、コードをコピペしてそのまま使えたほうが便利なので
    • 可読性は下がるし実行速度面でもたぶん不利だけど、移植性重視ということで
  • 変数宣言(Dim)は関数の先頭に並べずに、他の一般的な言語の習慣に従い「使う場所になるべく近い場所で宣言」する
  • プロシージャ単位で単体テストしやすいように、アクティブなブック/シートに対して処理を実行する引数なしのプロシージャを書く
    • 逆に「途中でユーザが操作しても処理がおかしくならないように、引数でブック/シートを受け取る」「処理はなるべくクラスに書く」というのを心がけてた時期もあるけど、今はテスタビリティ重視でこうやってる(どうすべきかまだ迷い中)
久々に VB 系使うと忘れてる部分
  • 可変長配列は配列を Dim/ReDim するのでなく Collection を使う
  • 連想配列は Scripting.Dictionary を CreateObject する
  • 正規表現VBScript.RegExp を CreateObject する
    • Like 演算子で済ませられるものは済ます

VBA のバージョン管理

SourceSafe との連携機能があったような気もしますが、今回は SVN を使っているので、

でエクスポートした .bas ファイル& .cls ファイルも一緒にコミットしています。