ぜぜ日記

ブログです

メンテナブルVBA

こんにちは。これはSpreadsheets/Excel Advent Calendar 2018の8日目の記事です。 adventar.org

小説スプレッドシートがよすぎます。

また、昨日はつくりおきアドベントカレンダーに寄稿しました。15分でパスタをつくる話です。 tsukurioki.hatenablog.com

さて、VBAという言語があります。VisualBasicMicrosoftアプリケーション向けにしたもので、おもにExcelAccessを操作するのに使われています。

昨今、RPAというマクロっぽいツールが流行っています。 人件費の高騰や人材難に対応するために業務をロボットにやらせよう、という発想のもので、うまくつかえば省人化につながり有用なのですが、安易な導入は業務のブラックボックス化を招き、長期的なコスト増につながる可能性もあります。

本当はちゃんと業務システムを組む必要があるのですが、まともにシステム化するにはお金も人も足りない。 パッケージや既存のサービスを利用しようにも合うものがなかったり、組織が合わせられなかったり、選定能力もなかったりする。

そんななんかでいびつな業務を処理し続けないといけなくて疲弊する組織というのもまああるわけです。 ここから身を守るための道具として、貧者の業務システム、ExcelVBAを考えてみたいと思います。

ExcelVBA、つかったことありますか? Excelがあれば気軽にはじめられるので、エンジニアのひとも道具箱にいれておくといろんな現場で便利なことがあるかもしれません。 自分は未検証ですが最近はMacOSでもきちんと動くようです。 そして、重要なのが、非エンジニアでも取り組みやすく、理解しやすいこと。

このエントリでは、そんなVBAを知ってもらうのと、VBA初心者が業務を助けるアプリを書くときの注意点を書いておきます。 文法などは書いていないので、適宜ググってください。

ただ、自分も3か所くらいの現場での、引き継いでメンテナンスした経験・ゼロから業務を分析してアプリを書いた経験などから書いているだけなので、偏ったかん考えなども含まれるかもしれません。
そういう意味では、ベストプラクティスではなくそれを探すためのたたき台のひとつと受け取ってもらえればと思います。
コメント・ご質問などお待ちしております。

ExcelVBAの特徴

プログラミング経験のないひとでもとっつきやすい。というのが重要な特徴です。 具体的には
・操作をマクロとして記録でき、そのコードを見たり変更できる。そのため、記法を調べなくてもコードができて、それを手直しすることができる。ただし、低パフォーマンスなコードにはなりがちなので注意。
・すぐに有用な動作を書くことができる。たとえば全シート名を取得するコードだったり、データを複雑な方法で集計するとか。
これは、よくあるプログラミング言語で、条件式と繰り返しを学んでも、それがどう役に立つのかわからずモチベーションを保ちにくいと比べ、小さく成功体験をつくれて、条件式と繰り返しの重要さを学びやすい。
これによってプログラミングの楽しさを見つけて自信をもってほかのプログラミングに手を出していく人もいる。
これは、初心者がいきなりRuby on Railsを学び始めて、気にすることが多すぎるあまり意味を理解せずコピペになりがちなのとの違いでもあるかも。

・データ(表)とコードが密結合になる
このため、そのままデータをもったファイルにマクロも書くと、そのファイルをコピーするとコードをもったファイルも増えていくため、アプリの配布とか、バージョン管理が極めて難しい(マクロだけエクスポートしてバージョン管理することもできなくはないが、煩雑)。
とくに、オブジェクト指向で書けるんだけれど、そのコードの置き場が難しい。複数ファイルで共有するのはできなくはないけれど、危ない。

・独自エディタ
行数表示させるのが難しかったりで好きなエディタをカスタマイズしている上級者には厳しいと思う。ただし、リアルタイムで構文チェックしてくれたり、自動インデントとか大文字小文字変換とかしてくれたり、ブレークポイントをつけてデバッグを容易にできるので初心者にとってはとっつきやすそう。変数の中を表示させつづけられるウォッチウィンドウも便利。

ユーザインタフェースが簡単に作れる
コードではなくドラッグアンドドロップで(wysiwygに)ボタンやフォームをつくれる。便利。

・印刷とかも簡単
帳票が簡単につくれます。しかも、テンプレートをExcelで簡単につくれるし、帳票をつくったあとに、ちょっと文字を変えたり、サイズを変更したりとが簡単にできるのはほかの帳票システムにはあまりない(もちろん帳票システムとしては、記録を管理しにくいとか重要な欠点はある)


・・
・・・
つまり、非エンジニアでも簡単に動くものがかけて楽しいけれど、そのソフトウェア設計の未経験さと、VBAのデータと蜜結合しやすいことからメンテナンス性が低くなりやすい。ということです。
現場で、なんらかの業務でExcelで手作業でなにかしないことは多々あり、短期的には業務が楽になっても、人員移動などで人が変わるとすぐブラックボックスになってしまいます。

こういうことを避けるため、おもに非エンジニア向けにちょっとした注意を書いてみます。

1. 書き捨てられる規模にとどめる

巨大なVBAをつくるのは地獄への始まり。いざとなれば1日でつくれる規模を心がける。

2.バリデーションする(入力をチェックする)

ユーザはあらゆる情報をセルに書き込むことができるし、Excelでエラーがあることもある。
数字を期待するところでは、セルに値が存在することを確認し、isNumericでバリデーションして行数と注意文を出力するべき。セルの入力規則もうまく使いたい。
メンバーがマクロをつかっていて、エラーが出てもあなたのところに来る必要がないようにしたい。

3.他ファイル参照時は相対パスで動くようにする

ときには、あるExcelファイルは別のファイルを参照することも多い。
そのときは、非本番データでテストできるようにしたりやユーザがポータブルに使えるようにするため、相対パスのみ、できれば、マクロを記述したファイルと同一ディレクトリにあるようにするべき。
複数ディレクトリにまたがるときは、そのファイルのパスはExcelファイル内の設定に記述できるようにする。

4.サブルーチン名・変数名は日本語にする。とわかりやすいと思う。

実際に業務で使う用語をそのまま日本語で使うとメンテナンスを引き継ぎやすい。DDDでのユビキタス言語ですね。
コード中にコメントはかなり丁寧目に書きましょう。

5.できるだけVBA内で処理せずExcelの式を使う。

VBA内で詳細な計算をするのではなく、そのデータの移動とかだけにとどめて、計算は表上での式ですると、エラーを発見しやすい。

6.VBAマクロのファイルとデータをもったファイルを分離する

データをもつファイルは、別のロジックの含まれるマクロを読み取り専用で開いて起動するくらいにとどめ、ロジックの書かれたファイルはユーザが開かないようにする。
ファイルサーバに置かれたものをユーザが使っていてエラーがでたとき、修正するためにファイルを閉じてもらう必要があるのはだるい。
なるべくソースだけでバージョン管理したい。
ただ、ショートカットで使うユーティリティ的なルーチンは別かな(複雑な優先度でのソートを数種類使い分けるとか)。

7.知っていてほしい概念

Dictionary・・・連想配列。ハッシュです。便利。ソートはないので、各自でソートを実装する必要があります(https://qiita.com/daik/items/682743bb8bcd8b5f0689
Variant・・・二次元のExcelのデータを取り込むのにつかいましょう。二次元配列的に扱えます。Excel上のデータに都度アクセスするより高速。
データの取得(Webクエリとか)・・・これはVBAとは違うのですが、DBMSのデータやWebページからデータをとってきてExcelのシート上に展開することが簡単にできます。うまく使えば、弥生シリーズ(内部にSQLサーバを使っている)とか、社内Webシステムのデータを、マスタとして取得して処理できます。たいへん便利。

8.上司同僚にはことあるごとにリスクとコストを伝える

はい。なんとなく自分が楽をするためにシステムを書いてきたんだとは思いますが、いつしかあなたのシステムはビジネスの一部になって、組織と深くつながってしまっていると思います。
システムがある以前の苦労を知っている方は、あなたに敬意を持ち続けると思いますが、それ以降に組織に加わったメンバーはそうではありません。
そのシステムがあることを当然に思ってしまうかもしれません。そうなると、そのシステムのメンテナンスに適切なコストが払われず、不健全なものになります。
それが有用なものであれば、ちゃんと業務として扱ってもらい、評価してもらいましょう。
そして引き継ぐための準備をしてもらいましょう。
そのためには、上司にはことあるごとにあなたが払ったコストを伝え、それが業務に蜜結合しているのであれば、不具合があれば業務に影響がある、そして、システムには不具合は避けられないということを伝えましょう。

9.参考になるサイト。

今回は実装の詳細とかコーディング自体については触れませんでしたが、やりたいことを言語化できればだいたいググって見つけることができる、気がする。

だいたい下記3サイトが相対的にまともでわかりやすい記事多めなのでググったときに優先的にみるとよさそう。
Office TANAKA Excel VBAテクニック カテゴリー:即効テクニック|Excel VBAを学ぶならmoug Excel VBA入門 | UX MILKVBAは公式も検索性が悪いし、エラーメッセージも日本語ででるため、英語の資源にアクセスしにくい・・・)

以上、それではハッピーVBA