業務エンジニアの攻撃は最大の防御ブログ

業務エンジニアのブログ。業務システム大好き。フレームワーク開発も好き。

Excel VBAのブラックボックス化を避ける設計の工夫

はじめに

現場のExcelVBAはEUCとか野良マクロとか呼ばれ、ブラックボックス化の温床のように言われる事があります。

この記事ではExcel VBAのブラックボックス化を避けるための設計・実装上の工夫について自分なりの見解をまとめます。

Excel VBAを業務で使う方にとって少しでも参考になると嬉しいです。

内容についてフィードバックをいただると更に嬉しいです。

私のVBA経験

自身の背景を書いた方が皆様の文脈に適用しやすいと思うので、私個人の経験を書きます（興味ない方は本節は読み飛ばしてください）。

私は前職で４年間、製造業の会社の社内SEとして仕事してきました。

人数の少ない部署だったので、自社システムの開発から社内のサーバー管理やアカウント管理など幅広く行ってきました。

社内ではたびたびExcel業務の改善の相談を受ける事があり、必要に応じてVBAの設計開発も行いました。

この期間に6つのVBAプログラムを開発し、そのうち3つは私の退職時にも現役で後任者に引き継ぎましつた（他は2つ基幹システム変更に伴い役割を全う。1つは残念ながらお蔵入り）。

それ以前にもVBAの経験がない訳では無いですが、社内SEとして直接ユーザーからフィードバックを受けた経験を経てExcel VBAを自信を持って使えるようになった気がしています。

Excelでゲームを作るとかWin32APIを呼び出すといった凝った事はできないので上級者とは言えませんが、一応数時間～数日で業務利用に耐える品質のアウトプットは出せるというレベル感です。

ブラックボックス化を避ける工夫①まずはVBA以外のExcelの機能で事足りないかと考える

「マクロを組んでほしい」と依頼されても「VBAで実装する」という先入観を持たずに、他の手段がないかを考えます。

マクロを書かずともvlookup関数、ピボットテーブル、条件付き書式などで事足りる事がよくあります。

例えば、元シートから列の配置を変更したシートを出力したいだけであれば、普通にセル参照するだけで実現できます。

特定の条件にあったセルに色をつけるのは条件付き書式で充分です。

集計をVBAで自動化する場合も、自力でループを書いて一時変数を使って集計するよりも、ピボットテーブルの機能をVBAから呼び出す方がシンプルかもしれません。

既に業務で使われてるマクロについても「これVBAでやる意味ないよね？」と思ったら、別の方法を案内して廃止を促す事も検討したいところです。

ブラックボックス化を避ける工夫②パラメータや変換表をシート上に記載する(コードに直書きしない)

パラメータや変換表といった変更の発生しやすい項目は、Excelシート上に記載してVBAから取得するようにするのが良いです。

例えば月次で変換処理を行うようなマクロは、以下の画像のようにユーザーがシート上でパラメータを変更できるできるようにしておけば、処理の都度プログラムを修正する必要がありません。

また「アウトプットのフォーマットには部署コードを出力したいが、インプットのフォーマットには部署名の項目しかない」というケースのいて、プログラム中の配列に対応表を書くのは悪手だと思います。

以下のようなシートを作成して参照するようにすれば、マスタの変更時に業務担当者がシートを直接修正できるので手離れの良いマクロになります。

↓私が影響を受けたと思われるツイート

ブラックボックス化を避ける工夫③手動でも検算できるようにする

VBAのマクロを実行する担当者が、必ずしもVBAも知識があってデバッグできるとは限りません。

でも、少なくとも表面に出ているExcelシートの内容は理解できれば担当部署を調整すれば、仕様変更やバグ修正をVBAプログラマに依頼する時もスムーズです。

例えば

  • 原価計算表の計算を自動化するマクロは経理部担当者
  • 生産管理表から作業依頼を生成するマクロは生産管理担当者
  • 勤務表から給与計算向けの連携データを出力するマクロは人事担当者

というように、各担当者が分かるシートになっているかを確認するのが良いです。

万が一エラーが出た時にVBAプログラマが不在だったとしても、業務を止めずにひとまずは手計算で乗り切れる体制にしておくのが理想です。

担当者が業務内容もよく分からず、機械的に毎月マクロを実行しているような状況だと、完全にブラックボックス化します。

ブラックボックス化を避ける工夫④計算の過程をシートに出力する（元データを直接変更しない）

元データのシートを直接変更するようなマクロを書くと、いざというときにデバッグできません。

手動でバックアップをとれば良いですが人間なので忘れる事もあります。

そうすると、保守担当者がバグを埋め込むのを怖がって変更を躊躇するようになります。

これに対し、元データの内容は変更せず、計算経過と最終計算結果は別シート（もしくは別ファイル）に出力するように設計すれば、処理が置いやすくなります。

格好良い言葉で言い換えると、べき等性のある設計が望ましいです。

最後に

当たり前ですがExcel VBAに限らず、体制や引き継ぎをミスればブラックボックス化します。

なのでブラックボックス化は主としてマネジメントの問題だと思います。

とはいえ上述してきたように、VBAの設計・実装担当者がブラックボックス化を防ぐために工夫をする事は可能です。

今後も一エンジニアとして、また一業務担当者としてExel VBAと上手く付き合っていきたいと思います。

参考書籍

パーフェクト Excel VBA

Excel VBAのオブジェクトや文法について体系的にを学ぶにはこの本が最高だと思います。

Excel以外のVBAを使用する時にも参考になります。

会計ソフトのすき間を埋める 経理のExcel仕事術

経理業務に適用するにあたってはこの本を参考にしました。

いちばんやさしいPowerPoint VBAの教本 人気講師が教える資料作りに役立つパワポマクロの基本 「いちばんやさしい教本」シリーズ

余談ですが、今入手可能な唯一のPower Point VBA本がこちらです。 Excel VBAの本は大型書店の棚が1つ埋まるほど無数にあるのに、Power PointのVBAの本は数少ないのですね。

一度だけPowerPointVBAを書いたのは貴重な経験でした。